Database tables

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- COA (register) accounts

CREATE TABLE accounts(
    account_id UUID NOT NULL,
    account_name TEXT NOT NULL, -- account name
    account_number NUMERIC(7,3) NOT NULL, -- this is unique within a single company
    account_type NUMERIC(2,0) NOT NULL, -- see accountd.json for defined types
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL, -- the company that owns this account
    starting_value NUMERIC(12,2) DEFAULT 0, -- any value accounts for valuation before data starts
    summary_account BOOLEAN DEFAULT false, -- this is a summary account (non-posting)
    CONSTRAINT accounts_pkey PRIMARY KEY (company_id, account_number),
    CONSTRAINT accounts_account_id_key UNIQUE (account_id)
);


-- Adhoc comments

CREATE TABLE adhoc_comments (
    comment_body TEXT NOT NULL, -- the comments text
    comment_id UUID NOT NULL, -- the comment ID
    comment_title TEXT, -- the comment title
    created BIGINT NOT NULL, -- Unix timestamp
    last_updated BIGINT NOT NULL, -- Unix timestamp
    owner UUID NOT NULL, -- comment owner
    topic_id UUID NOT NULL, -- topic ID
    CONSTRAINT adhoc_comments_pkey PRIMARY KEY (comment_id)
);


-- Adhoc followups

CREATE TABLE adhoc_followups (
    assigned UUID NOT NULL, -- who is assigned to take care of this
    comments TEXT, -- optional comments
    completed BOOLEAN DEFAULT false, -- is this done?
    followup_id UUID NOT NULL, -- unique ID
    followup_time BIGINT NOT NULL, -- Unix timestamp
    last_updated BIGINT NOT NULL, -- Unix timestamp
    last_updated_by UUID NOT NULL, -- who updated this last?
    topic_id UUID NOT NULL, -- related Adhoc topic ID
    CONSTRAINT adhoc_followups_pkey PRIMARY KEY (followup_id)
);


-- Adhoc topics

CREATE TABLE adhoc_topics (
    active        BOOLEAN       DEFAULT true, -- is the topic active
    created       BIGINT NOT NULL, -- Unix timestamp
    flagged       SMALLINT  DEFAULT 0, -- is the topic flagged
    last_updated  BIGINT NOT NULL, -- Unix timestamp
    locked        SMALLINT, -- users at a lower level cannot change records here
    owner         UUID          NOT NULL, -- user ID
    topic_id      UUID          NOT NULL, -- topic ID
    topic_title   TEXT          NOT NULL, -- topic title
    updated_by    UUID          NOT NULL, -- who updated this last?
    CONSTRAINT adhoc_topics_topic_id_key UNIQUE (topic_id)
);

-- Alarms

CREATE TABLE alarms (
    alarm_id UUID NOT NULL, -- unique ID
    alarm_text TEXT NOT NULL, -- alarm notes
    date DATE NOT NULL, -- creation date
    item_id UUID NOT NULL, -- the connected item
    user_id UUID NOT NULL, -- who created this alarm
    CONSTRAINT alarms_pkey PRIMARY KEY (alarm_id)
);

-- Check payees

CREATE TABLE check_payees (
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL, -- who owns (can use) this payee
    payee_name CHARACTER VARYING(100) NOT NULL,
    CONSTRAINT check_payees_pkey PRIMARY KEY (company_id, payee_name)
);


-- Check templates

CREATE TABLE check_templates (
    active BOOLEAN DEFAULT true,
    amount NUMERIC(11,2),
    check_number CHARACTER VARYING(30),
    check_type SMALLINT NOT NULL,
    company_id UUID,
    memo CHARACTER VARYING(60),
    payee_name CHARACTER VARYING(100) NOT NULL,
    payment_account UUID,
    source_account UUID,
    tags TEXT,
    template_id UUID,
    template_name CHARACTER VARYING(60) NOT NULL,
    template_notes TEXT,
    CONSTRAINT check_templates_pkey PRIMARY KEY (template_id)
);


-- Checks

CREATE TABLE checks (
    check_id UUID NOT NULL,
    check_number CHARACTER VARYING(30) NOT NULL,
    company_id UUID NOT NULL,
    date DATE NOT NULL,
    locked SMALLINT DEFAULT 0,
    memo CHARACTER VARYING(60),
    payee CHARACTER VARYING(100) NOT NULL,
    type SMALLINT NOT NULL,
    CONSTRAINT checks_pkey PRIMARY KEY (check_id)
);

-- Sales commission records

CREATE TABLE commissions (
    commission_id UUID NOT NULL,
    cost_basis NUMERIC(11,2),
    expense_id UUID NOT NULL,
    item_id UUID NOT NULL,
    lock SMALLINT DEFAULT 0,
    notes TEXT,
    percentage NUMERIC(4,2) NOT NULL,
    sku CHARACTER VARYING(60),
    user_id UUID NOT NULL,
    CONSTRAINT commissions_pkey PRIMARY KEY (commission_id),
    CONSTRAINT unique_user_item_combination UNIQUE (user_id, item_id)
);


-- Company settings are defined on a per-company basis

CREATE TABLE companies(
    accountd BOOLEAN DEFAULT false, -- enables accounting tools
    active BOOLEAN DEFAULT true,
    address TEXT,
    commission_expenses UUID, -- default posting account for commission expenses
    company_id UUID NOT NULL,
    company_name TEXT NOT NULL,
    contact_info TEXT,
    gone_location UUID, -- used in inventory to define when items are gone
    healthica UUID, -- the API key used to access healthica.com
    inventory_costs UUID, -- default posting account for inventory costs
    logo_path TEXT, -- URL for the company logo
    notes TEXT, -- private notes (optional)
    overhead UUID, -- default posting account for overhead expenses
    quote_expiration NUMERIC(3,0), -- # days a quote is allowed to "live"
    refunds UUID, -- default posting account for sales refunds
    restricted BOOLEAN, -- company_access record is required
    returns_due NUMERIC(3,0) DEFAULT 30, -- # days to return a core
    service_costs UUID, -- default posting account for service costs
    servicd BOOLEAN DEFAULT false, -- enables service tools
    stockd BOOLEAN DEFAULT false, -- enables sales/inventory tools
    tagline TEXT, -- optional slogan line
    undeposited UUID -- default undeposited funds account
);


-- Company access determines which users can see which companies

CREATE TABLE company_access(
    company_id UUID NOT NULL,
    user_id UUID NOT NULL,
    CONSTRAINT unique_company_access_record UNIQUE (company_id, user_id)
);

-- Contacts

CREATE TABLE contacts (
    accountd BOOLEAN DEFAULT true,
    active BOOLEAN DEFAULT true,
    contact_id UUID NOT NULL,
    email CHARACTER VARYING(100),
    full_name CHARACTER VARYING(100) NOT NULL,
    notes TEXT,
    phone CHARACTER VARYING(30),
    servicd BOOLEAN DEFAULT true,
    sms CHARACTER VARYING(30),
    stockd BOOLEAN DEFAULT true,
    terms_default UUID,
    CONSTRAINT contacts_pkey PRIMARY KEY (contact_id),
    CONSTRAINT name_email UNIQUE (email, full_name)
); 


-- Cost source expense type defaults

CREATE TABLE cost_source_expense_types (
    company_id UUID NOT NULL,
    expense_type UUID NOT NULL,
    CONSTRAINT unique_company_source_expense_type UNIQUE (company_id, expense_type)
);


-- Cost source tags

CREATE TABLE cost_source_tags (
    company_id UUID NOT NULL,
    tag_id UUID NOT NULL,
    CONSTRAINT unique_company_source_tag UNIQUE (company_id, tag_id)
);


-- Cost sources

CREATE TABLE cost_sources (
    allow_analysis BOOLEAN DEFAULT true, -- this source will appear in analysis
    allow_costs BOOLEAN DEFAULT true, -- the cost inputs can be changed
    allow_items BOOLEAN DEFAULT true, -- the connected items can be changed
    company_id UUID NOT NULL, -- the company that owns the source
    description TEXT, -- optional description 
    locked SMALLINT DEFAULT 0,
    source_id UUID NOT NULL,
    source_name TEXT NOT NULL,
    source_type SMALLINT, -- 1 = source expense, 2 = source job, 3 = source group
    updated BIGINT,
    CONSTRAINT cost_sources_pkey PRIMARY KEY (source_id)
);


-- credit cards

CREATE TABLE credit_cards (
    active           BOOLEAN DEFAULT true,
    card_id          UUID    NOT NULL,
    card_name        TEXT    NOT NULL,
    company_id       UUID    NOT NULL,
    notes            TEXT,
    posting_account  UUID    NOT NULL,
    CONSTRAINT credit_cards_pkey PRIMARY KEY (card_id)
);


-- credit memos

CREATE TABLE credit_memos (
    amount NUMERIC(11,2) NOT NULL,
    date DATE NOT NULL,
    locked SMALLINT DEFAULT 0,
    memo_id UUID NOT NULL,
    posting_account UUID NOT NULL,
    reason TEXT NOT NULL,
    CONSTRAINT credit_memos_pkey PRIMARY KEY (memo_id)
);


-- CRM stage types and templates

CREATE TABLE crm_stage_types (
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    open BOOLEAN NOT NULL DEFAULT false,                                        -- Can stages be moved into this type (group)      
    type_id UUID PRIMARY KEY,                                                   -- Unique identifier for the stage type
    type_name VARCHAR(100) UNIQUE NOT NULL,                                     -- Human-readable type name    
    type_notes TEXT,                                                            -- Optional notes or checklist for the stage type
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL                                                    -- User ID who last updated this stage type
);

CREATE TABLE crm_stage_templates (
    active BOOLEAN NOT NULL DEFAULT true,                                       -- If the template is currently active
    auto_advance BOOLEAN NOT NULL DEFAULT true,                                 -- Whether this stage auto-advances when criteria are met
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    stage_label VARCHAR(100) NOT NULL,                                          -- display label for the stage
    stage_name VARCHAR(100) NOT NULL,                                           -- internal stage identifier (e.g., 'proposal_sent')
    stage_notes TEXT,                                                           -- optional notes for the stage
    stage_type UUID NOT NULL,                                                   -- foreign key to a stage type or category
    template_id UUID PRIMARY KEY,                                               -- unique identifier for the template
    template_name VARCHAR(100) UNIQUE NOT NULL,                                 -- human-readable template name
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this template

    CONSTRAINT fk_stage_type
      FOREIGN KEY (stage_type)
      REFERENCES crm_stage_types (type_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

-- For tracking stage templates changes
CREATE INDEX idx_stage_templates_updated_by ON crm_stage_templates(updated_by);

-- For filtering stage templates by active
CREATE INDEX idx_stage_templates_active ON crm_stage_templates(active);

-- For filtering stage templates by stage type
CREATE INDEX idx_stage_templates_stage_type ON crm_stage_templates(stage_type);

CREATE TABLE crm_prebuilt_stages (
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the stage is currently active
    auto_advance BOOLEAN NOT NULL DEFAULT true,                                 -- whether this stage auto-advances when criteria are met
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    stage_id UUID PRIMARY KEY,                                                  -- the stage ID
    stage_label VARCHAR(100) NOT NULL,                                          -- display label for the stage
    stage_name VARCHAR(100) NOT NULL,                                           -- internal stage identifier (e.g., 'proposal_sent')
    stage_notes TEXT,                                                           -- optional description
    stage_type UUID NOT NULL,                                                   -- foreign key to a stage type or category
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this template

    CONSTRAINT fk_stage_type
      FOREIGN KEY (stage_type)
      REFERENCES crm_stage_types (type_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

-- For filtering/joining by stage type
CREATE INDEX idx_prebuilt_stage_type ON crm_prebuilt_stages(stage_type);

-- For filtering on active status
CREATE INDEX idx_prebuilt_stage_active ON crm_prebuilt_stages(active);

-- For filtering on who updated the prebuilt stage last
CREATE INDEX idx_prebuilt_stage_updated_by ON crm_prebuilt_stages(updated_by);


-- CRM task templates are starting templates which are cloned into prebuilt stages.

CREATE TABLE crm_task_templates (
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the stage task is currently active
    description TEXT,                                                           -- optional additional information
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    optional BOOLEAN DEFAULT false,                                             -- is this task mandatory to advance the stage?
    task_id UUID PRIMARY KEY,                                                   -- the stage task ID
    task_name VARCHAR(100) NOT NULL,                                            -- the stage task name.
    task_notes TEXT,                                                            -- optional notes
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL                                                    -- user ID who last updated this template
);

-- For filtering on who updated the task template last
CREATE INDEX idx_task_templates_updated_by ON crm_task_templates(updated_by);

-- For filtering on active status
CREATE INDEX idx_task_templates_active ON crm_task_templates(active);


-- Prebuilt stage tasks are clonable task records used when creating workflows.

CREATE TABLE crm_prebuilt_stage_tasks (
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the stage task is currently active
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    optional BOOLEAN DEFAULT false,                                             -- is this task mandatory to advance the stage?
    stage_id UUID NOT NULL,                                                     -- task is p/o this prebuilt stage
    task_id UUID PRIMARY KEY,                                                   -- the stage task ID
    task_name VARCHAR(100) NOT NULL,                                            -- the stage task name
    task_notes TEXT,                                                            -- optional notes
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this template

    CONSTRAINT fk_prebuilt_stage_id
      FOREIGN KEY (stage_id)
      REFERENCES crm_prebuilt_stages (stage_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

-- For filtering/grouping rebuilt tasks by stage
CREATE INDEX idx_prebuilt_stage_tasks_stage_id ON crm_prebuilt_stage_tasks(stage_id);

-- b-tree efficient indexing (fuzzy search support)
CREATE INDEX idx_stage_templates_stage_label_trgm ON crm_stage_templates USING gin (stage_label gin_trgm_ops);
CREATE INDEX idx_stage_templates_stage_name_trgm ON crm_stage_templates USING gin (stage_name gin_trgm_ops);
CREATE INDEX idx_stage_templates_template_name_trgm ON crm_stage_templates USING gin (template_name gin_trgm_ops);


-- CRM teams, roles and team members

CREATE TABLE crm_team_roles(
    description TEXT,                                                           -- optional description
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    role_id UUID PRIMARY KEY,                                                   -- unique ID for the team role
    role_name VARCHAR(100) UNIQUE NOT NULL,                                     -- the role name
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL                                                    -- user ID who last updated this team role
);

-- Filtering by who last updated
CREATE INDEX idx_team_roles_updated_by ON crm_team_roles(updated_by);

CREATE TABLE crm_teams(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the team is currently active
    description TEXT,                                                           -- optional description
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    team_id UUID PRIMARY KEY,                                                   -- unique identifier for the team
    team_name VARCHAR(100) UNIQUE NOT NULL,                                     -- unique workflow name
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL                                                    -- user ID who last updated this team
);

-- Filtering by who last updated
CREATE INDEX idx_teams_updated_by ON crm_teams(updated_by);

-- Filtering by active teams
CREATE INDEX idx_teams_active ON crm_teams(active);

CREATE TABLE crm_team_members(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the team member is currently active
    description TEXT,                                                           -- optional description
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    member_id UUID PRIMARY KEY,                                                 -- record identifier
    role UUID NOT NULL REFERENCES crm_team_roles(role_id),                      -- links to crm_team_roles
    team_id UUID NOT NULL,                                                      -- the sale team
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this workflow
    user_id UUID NOT NULL,                                                      -- the actual user ID
    UNIQUE (team_id, user_id)
);

-- For fast lookups by team
CREATE INDEX idx_team_members_team_id ON crm_team_members(team_id);

-- For fast lookups by user
CREATE INDEX idx_team_members_user_id ON crm_team_members(user_id);

-- For filtering by updater
CREATE INDEX idx_team_members_updated_by ON crm_team_members(updated_by);

-- For filtering by role
CREATE INDEX idx_team_members_role ON crm_team_members(role);


-- CRM workflows (from this point on, CRM templates can cascade changes)

CREATE TABLE crm_workflows(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the workflow is currently active
    description TEXT,                                                           -- optional description
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this workflow
    workflow_id UUID PRIMARY KEY,                                               -- unique indentifier for the workflow
    workflow_name VARCHAR(100) UNIQUE NOT NULL                                  -- unique workflow name
);

-- Fast retrieval by who updated
CREATE INDEX idx_workflows_updated_by ON crm_workflows(updated_by);

-- Filtering for active workflows
CREATE INDEX idx_workflows_active ON crm_workflows(active);

-- Which teams can use the workflow in a project?

CREATE TABLE crm_workflow_access(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the workflow access is currently active
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    team_id UUID NOT NULL REFERENCES crm_teams(team_id),                        -- teams authorized to use this workflow
    workflow_id UUID NOT NULL REFERENCES crm_workflows(workflow_id),            -- the workflow ID
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this access record
    UNIQUE (team_id, workflow_id)
);

-- Allow lookups by workflow
CREATE INDEX idx_workflow_access_by_workflow ON crm_workflow_access(workflow_id);

-- Allow lookups by updater
CREATE INDEX idx_workflow_access_updated_by ON crm_workflow_access(updated_by);

-- Filtering by active status
CREATE INDEX idx_workflow_access_active ON crm_workflow_access(active);


-- Stages (in position order) within the workflow.

CREATE TABLE crm_workflow_stages(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the workflow stage is currently available to funnels
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    prebuilt_id UUID NOT NULL REFERENCES crm_prebuilt_stages(stage_id),         -- the prebuilt stage that was cloned here
    stage_id UUID PRIMARY KEY,                                                  -- the stage ID
    stage_label VARCHAR(100) NOT NULL,                                          -- display label for the stage
    stage_name VARCHAR(100) NOT NULL,                                           -- internal stage identifier (e.g., 'proposal_sent')
    stage_notes TEXT,                                                           -- optional description
    stage_number SMALLINT NOT NULL,                                             -- orders the stages in a workflow
    stage_status SMALLINT NOT NULL DEFAULT 0,                                   -- stage status references static.json.crmStageStatus
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this workflow
    workflow_id UUID NOT NULL REFERENCES crm_workflows(workflow_id),            -- the workflow ID
    UNIQUE (stage_label, workflow_id),
    UNIQUE (stage_name, workflow_id),
    UNIQUE (stage_number, workflow_id)
);

-- Lookup or join stages by workflow
CREATE INDEX idx_workflow_stages_workflow_id ON crm_workflow_stages(workflow_id);

-- Lookup or audit trail
CREATE INDEX idx_workflow_stages_updated_by ON crm_workflow_stages(updated_by);

-- Filtering by active flag
CREATE INDEX idx_workflow_stages_active ON crm_workflow_stages(active);

-- Filtering/sorting by stage number within workflow
CREATE INDEX idx_workflow_stages_number ON crm_workflow_stages(workflow_id, stage_number);


-- Tasks within a CRM workflow are based on templates from crm_prebuilt_stage_tasks.

CREATE TABLE crm_workflow_stage_tasks(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the workflow task is currently active
    assigned UUID NOT NULL REFERENCES crm_team_members(member_id),              -- the team member assigned to this task
    assigned_time BIGINT NOT NULL,                                              -- epoch format date when this was assigned
    locked SMALLINT,                                                            -- governs whether a user can modify the record
    optional BOOLEAN DEFAULT false,                                             -- is this task mandatory to advance the stage?
    prebuilt_id UUID NOT NULL REFERENCES crm_prebuilt_stage_tasks(task_id),     -- the prebuilt stage that was cloned here
    stage_id UUID NOT NULL REFERENCES crm_workflow_stages(stage_id),            -- link to the stage metadata
    status SMALLINT NOT NULL DEFAULT 1,                                         -- task status references static.json.crmTaskStatus
    task_due BIGINT NOT NULL,                                                   -- epoch format date when this is due
    task_id UUID PRIMARY KEY,                                                   -- the unique ID of this assigned task
    task_notes TEXT,                                                            -- optional notes
    task_number SMALLINT NOT NULL,                                              -- orders the tasks in a workflow stage
    task_name VARCHAR(100) NOT NULL,                                            -- the task name
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this workflow
    workflow_id UUID NOT NULL REFERENCES crm_workflows(workflow_id),            -- the workflow ID
    UNIQUE (task_number)
);

-- Lookup tasks by workflow
CREATE INDEX idx_workflow_stage_tasks_workflow_id ON crm_workflow_stage_tasks(workflow_id);

-- Lookup tasks within a stage
CREATE INDEX idx_workflow_stage_tasks_stage_id ON crm_workflow_stage_tasks(stage_id);

-- Lookup tasks by assignee
CREATE INDEX idx_workflow_stage_tasks_assigned ON crm_workflow_stage_tasks(assigned);

-- Lookup tasks by due date
CREATE INDEX idx_workflow_stage_tasks_due ON crm_workflow_stage_tasks(task_due);

-- Lookup/update audit
CREATE INDEX idx_workflow_stage_tasks_updated_by ON crm_workflow_stage_tasks(updated_by);

-- Filtering on active tasks
CREATE INDEX idx_workflow_stage_tasks_active ON crm_workflow_stage_tasks(active);


-- CRM workflow tasks may be disabled until prequisite tasks are completed or marked as ignored (if optional).

CREATE TABLE crm_workflow_task_prerequisites(
    prereq UUID NOT NULL REFERENCES crm_workflow_stage_tasks(task_id),          -- the unique ID of the prequisite task
    task_id UUID NOT NULL REFERENCES crm_workflow_stage_tasks(task_id),         -- the unique ID of the master task
    CONSTRAINT chk_task_cannot_depend_on_itself CHECK (prereq <> task_id),      -- the task cannot depend on itself
    UNIQUE (prereq, task_id)
);

-- For fast lookup of prereqs for a given task
CREATE INDEX idx_prereqs_by_task ON crm_workflow_task_prerequisites(task_id);

-- For reverse lookup (which tasks are gated by a prereq)
CREATE INDEX idx_prereqs_by_prereq ON crm_workflow_task_prerequisites(prereq);


-- CRM campaigns are created 1:1 for a single contact or customer ID.

CREATE TABLE crm_campaigns(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the campaign is currently active
    campaign_id UUID PRIMARY KEY,                                               -- unique indentifier for the campaign
    campaign_name VARCHAR(100) UNIQUE NOT NULL,                                 -- unique campaign name
    description TEXT,                                                           -- optional description
    locked SMALLINT DEFAULT(0),                                                 -- governs whether a user can modify the record
    target_id UUID NOT NULL REFERENCES contacts(contact_id),                    -- target must exist in contacts
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL                                                    -- user ID who last updated this campaign
    workflow_id REFERENCES crm_workflows(workflow_id)                           -- workflow template used as source
);

-- Filter or sort by who last updated
CREATE INDEX idx_campaigns_updated_by ON crm_campaigns(updated_by);

-- Active campaigns will be filtered often
CREATE INDEX idx_campaigns_active ON crm_campaigns(active);

-- Full text searching/filtering support
CREATE INDEX idx_campaigns_name_trgm ON crm_campaigns USING gin (campaign_name gin_trgm_ops);

-- Lookup by contact id
CREATE INDEX idx_campaigns_target_id ON crm_campaigns(target_id);


-- Stages (in position order) within the campaign.

CREATE TABLE crm_campaign_stages(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the campaign stage is currently active (only one at a time)
    locked SMALLINT DEFAULT(0),                                                 -- governs whether a user can modify the record
    campaign_id UUID NOT NULL REFERENCES crm_campaigns(campaign_id)             -- link to the campaign metadata
        ON DELETE CASCADE,
    prebuilt_id UUID NOT NULL REFERENCES crm_prebuilt_stages(stage_id),         -- the prebuilt stage that was cloned here (used to get tasks)
    stage_id UUID PRIMARY KEY,                                                  -- the unique ID of the stage
    stage_label VARCHAR(100) NOT NULL,                                          -- display label for the stage
    stage_manager UUID NOT NULL REFERENCES crm_team_members(member_id),         -- the team member responsible for this stage
    stage_name VARCHAR(100) NOT NULL,                                           -- internal stage identifier (e.g., 'proposal_sent')
    stage_notes TEXT,                                                           -- optional description
    stage_number SMALLINT NOT NULL,                                             -- orders the stages in a campaign
    stage_status SMALLINT NOT NULL DEFAULT(0),                                  -- stage status references static.json.crmStageStatus
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this campaign
    UNIQUE (stage_number, campaign_id)
);

-- Find stages by campaign
CREATE INDEX idx_campaign_stages_campaign_id ON crm_campaign_stages(campaign_id);

-- Who last updated
CREATE INDEX idx_campaign_stages_updated_by ON crm_campaign_stages(updated_by);

-- Filter stages by manager
CREATE INDEX idx_campaign_stages_stage_manager ON crm_campaign_stages(stage_manager);

-- Support for stage filtering (e.g. "active", "in progress", etc.)
CREATE INDEX idx_campaign_stages_status ON crm_campaign_stages(stage_status);

-- Filter by active stages only
CREATE INDEX idx_campaign_stages_active ON crm_campaign_stages(active);


-- Tasks within a CRM campaign.

CREATE TABLE crm_campaign_tasks(
    active BOOLEAN NOT NULL DEFAULT true,                                       -- if the campaign task is currently active
    assigned UUID NOT NULL REFERENCES crm_team_members(member_id),              -- the team member assigned to this task
    assigned_time BIGINT NOT NULL,                                              -- epoch format date when this was assigned
    locked SMALLINT DEFAULT(0),                                                 -- governs whether a user can modify the record
    optional BOOLEAN DEFAULT false,                                             -- is this task mandatory to advance the stage?
    prebuilt_id UUID NOT NULL REFERENCES crm_prebuilt_stage_tasks(task_id),     -- the prebuilt task that was the source for this record
    stage_id UUID NOT NULL REFERENCES crm_campaign_stages(stage_id)             -- link to the stage metadata
        ON DELETE CASCADE,
    task_due BIGINT NOT NULL,                                                   -- epoch format date when this is due
    task_id UUID PRIMARY KEY,                                                   -- the unique ID of this assigned task
    task_number SMALLINT NOT NULL,                                              -- orders the tasks in a workflow stage
    task_name VARCHAR(100) NOT NULL,                                            -- the task name
    task_notes TEXT,                                                            -- optional notes
    task_status SMALLINT NOT NULL DEFAULT 1,                                    -- task status references static.json.crmTaskStatus
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this workflow
    UNIQUE (stage_id, task_number)
);

-- Filter by assigned team member
CREATE INDEX idx_campaign_tasks_assigned ON crm_campaign_campaign_tasks(assigned);

-- Filter by parent stage
CREATE INDEX idx_campaign_tasks_stage_id ON crm_campaign_campaign_tasks(stage_id);

-- Filter by due date (e.g., for reminders/dashboards)
CREATE INDEX idx_campaign_tasks_due ON crm_campaign_campaign_tasks(task_due);

-- Filter by task status
CREATE INDEX idx_campaign_tasks_status ON crm_campaign_campaign_tasks(status);

-- Who updated the task
CREATE INDEX idx_campaign_tasks_updated_by ON crm_campaign_campaign_tasks(updated_by);

-- Filter only active tasks
CREATE INDEX idx_campaign_tasks_active ON crm_campaign_campaign_tasks(active);


-- Campaign task prerequisites.

CREATE TABLE crm_campaign_task_prerequisites (
    prereq UUID NOT NULL REFERENCES crm_campaign_tasks(task_id),                -- prerequisite task
    task_id UUID NOT NULL REFERENCES crm_campaign_tasks(task_id),               -- dependent task
    UNIQUE (prereq, task_id),
    CHECK (prereq <> task_id)                                                   -- disallow a task depending on itself
);

-- Filter on task ID and prerequisites
CREATE INDEX idx_campaign_task_prereqs_by_task ON crm_campaign_task_prerequisites(task_id);
CREATE INDEX idx_campaign_task_prereqs_by_prereq ON crm_campaign_task_prerequisites(prereq);


-- customer:company connections (sharing a customer)

CREATE TABLE customer_company (
    company_id UUID NOT NULL,
    customer_id UUID NOT NULL,
    CONSTRAINT customer_company_pkey PRIMARY KEY (company_id, customer_id)
);


-- customer contacts (sharing a contact with multiple customers)

CREATE TABLE customer_contacts (
    accountd BOOLEAN DEFAULT true,
    accountd_to BOOLEAN DEFAULT false,
    accountd_cc BOOLEAN DEFAULT false,
    contact_id UUID NOT NULL,
    customer_id UUID NOT NULL,
    location TEXT,
    servicd BOOLEAN DEFAULT true,
    servicd_to BOOLEAN DEFAULT false,
    servicd_cc BOOLEAN DEFAULT false,
    stockd BOOLEAN DEFAULT true,
    stockd_to BOOLEAN DEFAULT false,
    stockd_cc BOOLEAN DEFAULT false,
    CONSTRAINT customer_contacts_pkey PRIMARY KEY (contact_id, customer_id)
);


-- customer email defaults

CREATE TABLE customer_email_defaults (
    accountd BOOLEAN DEFAULT true,
    contact_id UUID NOT NULL,
    customer_id UUID NOT NULL,
    level SMALLINT NOT NULL DEFAULT 1,
    location TEXT,
    servicd BOOLEAN DEFAULT true,
    stockd BOOLEAN DEFAULT true,
    CONSTRAINT customer_email_defaults_pkey PRIMARY KEY (contact_id, customer_id)
);


-- customer accounts

CREATE TABLE customers (
    account_number CHARACTER VARYING(30),
    accountd BOOLEAN,
    active BOOLEAN,
    company_id UUID,
    customer_id UUID,
    customer_name CHARACTER VARYING(60),
    notes TEXT,
    sales_tax BOOLEAN,
    servicd BOOLEAN,
    stockd BOOLEAN,
    terms_default UUID,
    CONSTRAINT customers_pkey PRIMARY KEY (customer_id)
);


-- documents

CREATE TABLE documents (
    caption TEXT NOT NULL,
    company_id UUID NOT NULL,
    created BIGINT NOT NULL,
    description TEXT,
    document_id UUID NOT NULL,
    file_name TEXT NOT NULL,
    file_size BIGINT,
    file_type CHARACTER VARYING(30) NOT NULL,
    public BOOLEAN DEFAULT false,
    CONSTRAINT documents_pkey PRIMARY KEY (document_id)
);


-- email log contacts

CREATE TABLE email_log_contacts (
    contact_id UUID NOT NULL,
    email_id UUID NOT NULL,
    main BOOLEAN NOT NULL,
    CONSTRAINT email_log_contacts_pkey PRIMARY KEY (contact_id, email_id)
);


-- email log metadata

CREATE TABLE email_log_metadata (
    attachment BOOLEAN DEFAULT false,
    email_id UUID NOT NULL,
    email_text TEXT NOT NULL,
    item_id UUID NOT NULL,
    item_type NUMERIC(2,0) NOT NULL,
    reply_to TEXT NOT NULL,
    sender TEXT NOT NULL,
    sent BIGINT NOT NULL,
    subject TEXT NOT NULL,
    CONSTRAINT email_log_metadata_pkey PRIMARY KEY (email_id)
);


-- email schedule records

CREATE TABLE email_schedule (
    attachment BOOLEAN DEFAULT false,
    cc_contacts TEXT,
    company_id UUID NOT NULL,
    draft BOOLEAN DEFAULT false,
    email_id UUID NOT NULL,
    email_text TEXT NOT NULL,
    item_id UUID NOT NULL,
    item_type NUMERIC(2,0) NOT NULL,
    reply_to TEXT,
    send BIGINT NOT NULL,
    signature UUID,
    subject TEXT NOT NULL,
    to_contacts TEXT,
    CONSTRAINT email_schedule_pkey PRIMARY KEY (email_id)
);


-- email signatures

CREATE TABLE email_signatures (
    html BOOLEAN DEFAULT false,
    locked SMALLINT,
    owner UUID NOT NULL,
    private BOOLEAN DEFAULT true,
    signature_id UUID NOT NULL,
    signature_name CHARACTER VARYING(100) NOT NULL,
    signature_text TEXT NOT NULL,
    CONSTRAINT email_signatures_pkey PRIMARY KEY (signature_id),
    CONSTRAINT email_signatures_signature_name_key UNIQUE (signature_name)
);


-- expense templates

CREATE TABLE expense_templates (
    active BOOLEAN DEFAULT true,
    amount NUMERIC(11,2) NOT NULL DEFAULT 0,
    company_id UUID NOT NULL,
    credit_card UUID,
    default_user UUID NOT NULL,
    description TEXT,
    expense_type UUID NOT NULL,
    expires BIGINT,
    interval SMALLINT,
    locked SMALLINT NOT NULL DEFAULT(0),
    next_time BIGINT,
    posting_account UUID,
    recurring BOOLEAN DEFAULT false,
    starts BIGINT,
    template_id UUID NOT NULL,
    template_name CHARACTER VARYING(100) NOT NULL,
    vendor_id UUID,
    CONSTRAINT expense_templates_pkey PRIMARY KEY (template_id)
);


-- expense types

CREATE TABLE expense_types (
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    gaap NUMERIC(5,0),
    posts_to UUID NOT NULL,
    type_id UUID NOT NULL,
    type_name TEXT NOT NULL,
    CONSTRAINT expense_types_pkey PRIMARY KEY (type_id),
    CONSTRAINT expense_types_company_id_type_name_key UNIQUE (company_id, type_name)
);


-- expenses

CREATE TABLE expenses (
    amount NUMERIC(11,2) NOT NULL DEFAULT 0,
    company_id UUID NOT NULL,
    credit_card UUID,
    date DATE NOT NULL,
    description TEXT,
    expense_id UUID NOT NULL,
    expense_number NUMERIC(7,0) NOT NULL,
    expense_type UUID NOT NULL,
    locked SMALLINT,
    pending BOOLEAN DEFAULT false,
    posting_account UUID NOT NULL,
    user_id UUID NOT NULL,
    vendor_id UUID,
    CONSTRAINT expenses_pkey PRIMARY KEY (expense_id),
    CONSTRAINT expenses_expense_number_key UNIQUE (expense_number)
);


-- external access

CREATE TABLE external_access (
    access_name TEXT NOT NULL UNIQUE,
    company_id UUID NOT NULL,
    external_client UUID NOT NULL,
    token TEXT UNIQUE NOT NULL,
    group_id UUID NOT NULL,
    user_id UUID NOT NULL,
    CONSTRAINT external_access_key UNIQUE (access_name, company_id, user_id)
);


-- what item classes can access this field group?

CREATE TABLE fields_access(
  category        NUMERIC(2,0) NOT NULL,                  -- grant access to this field group (for inventory, quotes, ...in the item classes)
  field_group     UUID NOT NULL,                          -- the field group
  locked          SMALLINT NOT NULL DEFAULT(1),       -- level required to change it
  UNIQUE(category, field_group)
);


-- recording actual custom fields entries

CREATE TABLE fields_data(
  boolean_value   BOOLEAN,                                -- only for field type 3
  field_id        UUID NOT NULL,                          -- must exist in fields_metadata.field_id (the custom type)
  field_type      SMALLINT NOT NULL,                  -- must match fields_metadata.field_type (the custom type's field type)
  number_value    NUMERIC(14,3),                          -- only for field type 2
  target_id       UUID NOT NULL,                          -- the item (in the real world) using this field and value
  text_value      TEXT,                                   -- only for field type 1
  uuid_value      UUID,                                   -- only for field type 4
  value_id        UUID NOT NULL,                          -- must exist in fields_values for the supplied field_id

  -- Constraint 1: Ensure only one value column is populated
  CONSTRAINT only_one_value CHECK (
    (boolean_value IS NOT NULL)::int +
    (number_value IS NOT NULL)::int +
    (text_value IS NOT NULL)::int +
    (uuid_value IS NOT NULL)::int = 1
  ),

  -- Constraint 2: Prevent duplicate (field_id, target_id, value)
  CONSTRAINT unique_value_per_item UNIQUE (
    field_id, target_id, boolean_value, number_value, text_value, uuid_value
  ),

  -- Constraint 3: Prevent multiple value types for the same (field_id, target_id)
  CONSTRAINT unique_type_per_target UNIQUE (
    field_id, target_id, field_type, value_id
  )
  
);


-- custom field groups

CREATE TABLE fields_groups(
  active          BOOLEAN NOT NULL DEFAULT(true),         -- is this group available for use?
  field_group     UUID NOT NULL,                          -- unique ID for this group of 1 or more fields,
  group_name      TEXT NOT NULL,                          -- the group name (i.e. "models")
  locked          SMALLINT NOT NULL DEFAULT(1),       -- level required to change it
  updated         BIGINT NOT NULL,                 -- last update
  updated_by      UUID NOT NULL                           -- who updated it
);


-- custom fields metadata (within groups)

CREATE TABLE fields_metadata(
  active          BOOLEAN NOT NULL DEFAULT(true),         -- is this field available for use?
  default_value   UUID,                                   -- select a value from field_values as the default
  field_group     UUID NOT NULL,                          -- the field group
  field_id        UUID NOT NULL,                          -- unique ID for this type of field
  field_name      TEXT UNIQUE NOT NULL,                   -- unique field name
  field_type      SMALLINT NOT NULL,                  -- 1 = text, 2 = number, 3 = boolean, 4 = UUID (1/2 are text, 3 is checkbox, 4 is selector)
  gui_type        SMALLINT NOT NULL DEFAULT(1),       -- 1 = selector, 2 = checkbox, 3 = text, 4 = radio
  locked          SMALLINT NOT NULL DEFAULT(1),       -- level required to change it.
  placeholder     TEXT,                                   -- when this set, the field should show a placeholder (select/text only)
  updated         BIGINT NOT NULL,                 -- last update
  updated_by      UUID NOT NULL                           -- who updated it
);


-- custom fields values (presets) for a defined field

CREATE TABLE fields_values(
  active          BOOLEAN NOT NULL DEFAULT(true),         -- is this field available for use (it can be retired for new items but retained for display)
  field_id        UUID NOT NULL,                          -- which field is this a value for?
  field_value     TEXT NOT NULL,                          -- how is this option labeled?
  locked          SMALLINT NOT NULL DEFAULT(1),       -- level required to change it
  position        NUMERIC(2,0) NOT NULL,                  -- order of options within this value set (1 field_id, multiple options)
  updated         BIGINT NOT NULL,                 -- last update
  updated_by      UUID NOT NULL,                          -- who updated it,
  value_id        UUID NOT NULL,                          -- unique ID for this field value
  UNIQUE(field_id,field_value)
);


-- history records

CREATE TABLE history (
    event NUMERIC(3,0),
    event_time BIGINT NOT NULL,
    event_id UUID NOT NULL,
    item_id UUID NOT NULL,
    item_type NUMERIC(2,0) NOT NULL,
    notes TEXT,
    user_id UUID NOT NULL,
    CONSTRAINT history_pkey PRIMARY KEY (event_id)
);
CREATE INDEX history_item_id_idx ON history (item_id);
CREATE INDEX history_item_id_type_idx ON history (item_id, item_type);
CREATE INDEX history_event_time_idx ON history (event_time);

-- inventory items

CREATE TABLE inventory_items (
    category SMALLINT DEFAULT 1,
    company_id UUID,
    condition NUMERIC(2,0) NOT NULL,
    exchange_item UUID,
    item_id UUID NOT NULL,
    item_name TEXT NOT NULL,
    item_number NUMERIC(7,0) NOT NULL,
    leader UUID,
    location UUID NOT NULL,
    quantity NUMERIC(4,0) NOT NULL,
    reserved BOOLEAN DEFAULT false,
    sell BOOLEAN DEFAULT true,
    serial_number CHARACTER VARYING(50),
    sku CHARACTER VARYING(60) NOT NULL,
    source_id UUID,
    stocked BIGINT,
    verified BIGINT,
    CONSTRAINT inventory_items_pkey PRIMARY KEY (item_id),
    CONSTRAINT inventory_items_item_number_key UNIQUE (item_number)
);


-- invoice items

CREATE TABLE invoice_items (
    description TEXT NOT NULL,
    exchange_required BOOLEAN DEFAULT false,
    invoice_id UUID NOT NULL,
    invoice_item UUID NOT NULL,
    line_item UUID NOT NULL,
    notes TEXT,
    position NUMERIC(3,0) NOT NULL,
    posting_account UUID NOT NULL,
    quantity NUMERIC(8,2) NOT NULL,
    rate1 NUMERIC(11,2) NOT NULL,
    rate2 NUMERIC(11,2) NOT NULL,
    CONSTRAINT invoice_items_pkey PRIMARY KEY (invoice_item)
);


-- invoices

CREATE TABLE invoices (
    collectible BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    created BIGINT,
    customer_id UUID NOT NULL,
    customer_notes TEXT,
    customer_po CHARACTER VARYING(60),
    invoice_date DATE NOT NULL,
    invoice_due DATE NOT NULL,
    invoice_id UUID NOT NULL,
    invoice_number NUMERIC(8,2) NOT NULL,
    locked SMALLINT NOT NULL DEFAULT 0,
    non_posting BOOLEAN DEFAULT false,
    order_id UUID,
    updated BIGINT,
    CONSTRAINT invoices_pkey PRIMARY KEY (invoice_id),
    CONSTRAINT invoices_invoice_number_key UNIQUE (invoice_number)
);

-- Job info records

CREATE TABLE job_info (
    active BOOLEAN DEFAULT true,
    billable BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    customer_id UUID NOT NULL,
    customer_location UUID,
    customer_po CHARACTER VARYING(60),
    job_id UUID,
    job_notes TEXT,
    job_state NUMERIC(2,0) DEFAULT 1,
    job_number NUMERIC(5,0),
    locked SMALLINT,
    posting_account UUID NOT NULL,
    CONSTRAINT job_info_pkey PRIMARY KEY (job_id),
    CONSTRAINT job_info_job_number_key UNIQUE (job_number)
);

-- Job item records

CREATE TABLE job_items (
    billable BOOLEAN DEFAULT true,
    cost NUMERIC(8,2),
    description TEXT NOT NULL,
    inventory_id UUID,
    item_id UUID NOT NULL,
    notes TEXT,
    ticket_id UUID NOT NULL,
    CONSTRAINT job_items_pkey PRIMARY KEY (item_id)
);


-- Job tickets

CREATE TABLE job_tickets (
    arrived BIGINT,
    assigned_user UUID NOT NULL,
    booked BIGINT NOT NULL,
    billable_expenses NUMERIC(11,2),
    billable_labor_1 NUMERIC(4,1),
    billable_labor_2 NUMERIC(4,1),
    billable_labor_3 NUMERIC(4,1),
    billable_travel_1 NUMERIC(4,1),
    billable_travel_2 NUMERIC(4,1),
    billable_travel_3 NUMERIC(4,1),
    fixed BIGINT,
    finished BIGINT,
    job_id UUID NOT NULL,
    job_status NUMERIC(2,0) NOT NULL DEFAULT 1,
    labor_hours NUMERIC(4,2),
    locked SMALLINT,
    private_notes TEXT,
    reason TEXT NOT NULL,
    ticket_id UUID NOT NULL,
    travel_hours NUMERIC(4,2),
    work_performed TEXT,
    CONSTRAINT job_tickets_pkey PRIMARY KEY (ticket_id)
);


-- Sales line items

CREATE TABLE line_items (
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    description TEXT,
    item_id UUID NOT NULL,
    item_name TEXT NOT NULL,
    posting_account UUID NOT NULL,
    CONSTRAINT line_items_pkey PRIMARY KEY (item_id)
);

-- Links

CREATE TABLE links (
    active BOOLEAN DEFAULT true,
    item_company_id UUID NOT NULL,
    item_id UUID NOT NULL,
    link_company_id UUID NOT NULL,
    link_id UUID NOT NULL,
    link_item_id UUID NOT NULL,
    link_type NUMERIC(2,0) NOT NULL,
    timestamp BIGINT,
    CONSTRAINT links_pkey PRIMARY KEY (link_id),
    CONSTRAINT unique_item_link UNIQUE (item_id, link_item_id)
);


-- Messages

CREATE TABLE messages (
    content TEXT NOT NULL,
    message_id UUID NOT NULL,
    sent_by UUID NOT NULL,
    sent_time BIGINT NOT NULL,
    target UUID NOT NULL,
    CONSTRAINT messages_pkey PRIMARY KEY (message_id)
);

-- Notes

CREATE TABLE notes (
    author UUID NOT NULL,
    created CHARACTER VARYING(10) NOT NULL,
    note_id UUID NOT NULL,
    notes TEXT NOT NULL,
    updated CHARACTER VARYING(10) NOT NULL,
    updated_by UUID NOT NULL,
    CONSTRAINT notes_pkey PRIMARY KEY (note_id)
);


-- Note connections

CREATE TABLE notes_connections (
    connection_id UUID NOT NULL,
    connection_type NUMERIC(2,0) NOT NULL,
    note_id UUID NOT NULL,
    CONSTRAINT notes_connections_pkey PRIMARY KEY (connection_id, note_id)
);

-- Sales order items

CREATE TABLE order_items (
    billable BOOLEAN DEFAULT true,
    created BIGINT,
    description TEXT,
    inventory_item UUID,
    line_item UUID,
    order_id UUID,
    order_item_id UUID NOT NULL,
    quantity NUMERIC(5,0),
    quote_item_id UUID,
    rate1 NUMERIC(11,2),
    rate2 NUMERIC(11,2),
    sku CHARACTER VARYING(60),
    updated BIGINT,
    CONSTRAINT order_items_pkey PRIMARY KEY (order_item_id)
);


-- Sales orders 

CREATE TABLE orders (
    additional_billing BOOLEAN DEFAULT false,
    billable BOOLEAN DEFAULT true,
    billed BOOLEAN DEFAULT false,
    company_id UUID NOT NULL,
    customer_po CHARACTER VARYING(30),
    created BIGINT NOT NULL,
    customer_id UUID NOT NULL,
    locked SMALLINT,
    order_date DATE,
    order_id UUID NOT NULL,
    order_label VARCHAR(30),
    order_name TEXT NOT NULL,
    order_number NUMERIC(6,0) NOT NULL UNIQUE,
    order_status NUMERIC(2,0) NOT NULL,
    quote_id UUID,
    updated BIGINT NOT NULL,
    user_id UUID NOT NULL
);


-- Payments

CREATE TABLE payments (
    created BIGINT NOT NULL,
    company_id UUID NOT NULL,
    deposit_account UUID NOT NULL,
    locked SMALLINT DEFAULT 0,
    payment_date DATE NOT NULL,
    payment_id UUID NOT NULL,
    updated BIGINT NOT NULL,
    CONSTRAINT payments_pkey PRIMARY KEY (payment_id)
);


-- Sales quote items

CREATE TABLE quote_items (
    description TEXT,
    item_id UUID,
    position NUMERIC(3,0),
    quantity NUMERIC(5,2),
    quote_id UUID,
    rate1 NUMERIC(9,2),
    rate2 NUMERIC(9,2),
    sku CHARACTER VARYING(30),
    CONSTRAINT quote_items_pkey PRIMARY KEY (quote_id, item_id)
);


-- Sales quotes metadata

CREATE TABLE quotes (
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    created BIGINT NOT NULL,
    expires BIGINT NOT NULL,
    locked SMALLINT,
    prepared_for UUID NOT NULL,
    quote_id UUID NOT NULL,
    quote_number NUMERIC(6,0) NOT NULL,
    rating SMALLINT,
    terms TEXT,
    updated BIGINT NOT NULL,
    CONSTRAINT unique_quote_id_quote_number UNIQUE (quote_id, quote_number),
    CONSTRAINT quotes_pkey PRIMARY KEY (quote_id)
);


-- Reconciliation records

CREATE TABLE reconciled (
    account UUID NOT NULL,
    date DATE NOT NULL,
    statement NUMERIC(11,2),
    CONSTRAINT reconciled_pkey PRIMARY KEY (account, date)
);


-- Sales requests

CREATE TABLE requests (
    company_id UUID NOT NULL,
    contact_id UUID,
    customer_id UUID,
    external BOOLEAN DEFAULT false,
    healthica_id UUID,
    item_name CHARACTER VARYING(100) NOT NULL,
    manufacturer UUID,
    modality UUID,
    request_id UUID NOT NULL,
    sku CHARACTER VARYING(60) NOT NULL,
    status NUMERIC(2,0) NOT NULL,
    timestamp BIGINT NOT NULL,
    type NUMERIC(2,0) NOT NULL,
    CONSTRAINT requests_pkey PRIMARY KEY (request_id)
);


-- Return items (RMAs)

CREATE TABLE return_items (
    created BIGINT NOT NULL,
    due DATE NOT NULL,
    item_status SMALLINT NOT NULL DEFAULT 1,
    order_id UUID NOT NULL,
    original_item UUID,
    return_id UUID NOT NULL,
    returned_item UUID,
    updated BIGINT NOT NULL,
    CONSTRAINT return_items_pkey PRIMARY KEY (return_id),
    CONSTRAINT return_items_original_item_order_id_key UNIQUE (original_item, order_id)
);


-- Return records (RMAs)

CREATE TABLE returns (
    open boolean NOT NULL DEFAULT true,
    order_id UUID NOT NULL,
    CONSTRAINT returns_order_id_key UNIQUE (order_id)
);


-- SKU policies

CREATE OR REPLACE FUNCTION clean_alternates(text)
RETURNS text[] LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT ARRAY(
    SELECT trim(s)
    FROM unnest(string_to_array($1, '||')) s
  );
$$;

CREATE TABLE sku_policies (
    alternates text,
    GENERATED ALWAYS AS (clean_alternates(alternates)) STORED,
    cost_floor BIGINT NOT NULL,
    deprecated BOOLEAN DEFAULT false,
    exchange NUMERIC(10,2) NOT NULL,
    notes TEXT,
    outright NUMERIC(10,2) NOT NULL,
    policy_id UUID NOT NULL,
    reviewed BIGINT NOT NULL,
    sku TEXT NOT NULL,
    sku_name TEXT NOT NULL,
    CONSTRAINT sku_policies_pkey PRIMARY KEY (policy_id),
    CONSTRAINT sku_policies_sku_key UNIQUE (sku)
);


-- Stocking locations

CREATE TABLE stocking_locations (
    active BOOLEAN DEFAULT true,
    location_id UUID NOT NULL,
    location_name CHARACTER VARYING(60) NOT NULL,
    location_type SMALLINT NOT NULL DEFAULT 1,
    CONSTRAINT stocking_locations_pkey PRIMARY KEY (location_id),
    CONSTRAINT stocking_locations_location_id_key UNIQUE (location_id)
);


-- Tag connections

CREATE TABLE tag_connections (
    class NUMERIC(2,0) NOT NULL,
    item_id UUID NOT NULL,
    tag_id UUID NOT NULL,
    CONSTRAINT tag_connections_pkey PRIMARY KEY (class, item_id, tag_id)
);


-- Tags

CREATE TABLE tags (
    active BOOLEAN DEFAULT true,
    notes TEXT,
    tag_id UUID NOT NULL,
    tag_text CHARACTER VARYING(30) NOT NULL,
    CONSTRAINT tags_pkey PRIMARY KEY (tag_id)
);


-- Terms and conditions

CREATE TABLE terms (
    active BOOLEAN DEFAULT true,
    terms_id UUID NOT NULL,
    terms_title TEXT NOT NULL,
    terms_text TEXT NOT NULL,
    CONSTRAINT terms_pkey PRIMARY KEY (terms_id),
    CONSTRAINT terms_terms_title_key UNIQUE (terms_title)
);


-- Test bypass records

CREATE TABLE test_bypass (
    company_id UUID NOT NULL,
    item_id UUID NOT NULL,
    item_type NUMERIC(2,0) NOT NULL,
    test_id NUMERIC(3,0) NOT NULL,
    user_id UUID NOT NULL,
    CONSTRAINT test_bypass_pkey PRIMARY KEY (item_id, test_id)
);


-- Transactions lines

CREATE TABLE transaction_lines (
    account UUID NOT NULL,
    amount NUMERIC(11,2) NOT NULL,
    cleared BOOLEAN DEFAULT false,
    debit BOOLEAN DEFAULT false,
    pointer UUID,
    statement DATE,
    transaction_number NUMERIC(6,0) NOT NULL
);


-- Transaction metadata

CREATE TABLE transaction_metadata (
    category SMALLINT NOT NULL DEFAULT 1,
    cogs BOOLEAN DEFAULT false,
    date DATE NOT NULL,
    date2 DATE NOT NULL,
    locked SMALLINT DEFAULT 0,
    reconciled BOOLEAN DEFAULT false,
    transaction_id UUID,
    transaction_number NUMERIC(6,0),
    type NUMERIC(2,0),
    CONSTRAINT transaction_metadata_category_check CHECK (category >= 1 AND category <= 5),
    CONSTRAINT transaction_metadata_transaction_number_check CHECK (transaction_number > 0)
);


-- User settings

CREATE TABLE users (
    adhoc_updated NUMERIC(10, 0) DEFAULT 0, -- the last time Adhoc was updated by anyone
    adhoc_viewed BIGINT DEFAULT 0, -- last time this user opened Adhoc
    commissions UUID, -- some users will have a designated commissions clearing account
    email_signature UUID, -- current email signature
    timezone CHARACTER VARYING(30) NOT NULL DEFAULT 'US/Pacific',
    user_id UUID NOT NULL, -- must match the ID assigned by Services API
    CONSTRAINT users_user_id_key UNIQUE (user_id)
);


-- Vendor-company connections

CREATE TABLE vendor_company (
    company_id UUID NOT NULL,
    vendor_id UUID NOT NULL,
    CONSTRAINT vendor_company_pkey PRIMARY KEY (company_id, vendor_id)
);


-- Vendor-contact connections

CREATE TABLE vendor_contacts (
    accountd BOOLEAN DEFAULT true,
    accountd_to BOOLEAN DEFAULT false,
    accountd_cc BOOLEAN DEFAULT false,
    contact_id UUID NOT NULL,
    servicd BOOLEAN DEFAULT true,
    servicd_to BOOLEAN DEFAULT false,
    servicd_cc BOOLEAN DEFAULT false,
    stockd BOOLEAN DEFAULT true,
    stockd_to BOOLEAN DEFAULT false,
    stockd_cc BOOLEAN DEFAULT false,
    vendor_id UUID NOT NULL,
    CONSTRAINT vendor_contacts_pkey PRIMARY KEY (contact_id, vendor_id)
);


-- Vendors

CREATE TABLE vendors(
    accountd BOOLEAN DEFAULT true,
    active BOOLEAN DEFAULT true,
    company_id UUID NOT NULL,
    net_due NUMERIC(3,0),
    notes TEXT,
    servicd BOOLEAN DEFAULT true,
    stockd BOOLEAN DEFAULT true,
    vendor_id UUID NOT NULL,
    vendor_name TEXT NOT NULL,
    CONSTRAINT vendors_pkey PRIMARY KEY (vendor_id)
);

-- Reusable view to allow searches with or without company access records

CREATE VIEW accessible_companies AS
SELECT
  c.company_id,
  c.company_name,
  c.restricted,
  ca.user_id
FROM
  companies c
LEFT JOIN
  company_access ca ON c.company_id = ca.company_id;