-- 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 true,                                         -- 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_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_number SMALLINT NOT NULL,                                              -- orders the tasks in a workflow stage
    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);

-- GIN trigram indexing for 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 REFERENCES crm_teams(team_id),                        -- 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)
      ON DELETE CASCADE,                                                        -- 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
    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
    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)
      ON DELETE CASCADE                                                        -- the 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
    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)
      ON DELETE CASCADE,                                                        -- link to the stage metadata
    status SMALLINT NOT NULL DEFAULT 0,                                         -- task status references static.json.crmTaskStatus
    task_id UUID PRIMARY KEY,                                                   -- the unique ID of this assigned task
    task_name VARCHAR(100) NOT NULL,                                            -- the task name
    task_notes TEXT,                                                            -- optional notes
    task_number SMALLINT NOT NULL,                                              -- orders the tasks in a workflow stage
    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)
      ON DELETE CASCADE                                                         -- the workflow ID
);

-- 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/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
    UNIQUE (prereq, task_id),
    CONSTRAINT chk_task_cannot_depend_on_itself CHECK (prereq <> task_id)       -- the task cannot depend on itself
);

-- 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);

CREATE TABLE crm_campaign_groups(
    active BOOLEAN DEFAULT true,                                                -- can new campaigns use this group?
    group_id UUID PRIMARY KEY,                                                  -- the unique group ID
    group_name VARCHAR(100) UNIQUE NOT NULL,                                    -- the unique group name
    locked SMALLINT DEFAULT 0,                                                 -- 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 campaign
);


-- 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_group UUID NOT NULL REFERENCES crm_campaign_groups(group_id),      -- all campaigns belong to a group
    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
    team_id UUID NOT NULL REFERENCES crm_teams(team_id),                        -- the team running this campaign,
    updated BIGINT NOT NULL,                                                    -- epoch timestamp of last update
    updated_by UUID NOT NULL,                                                   -- user ID who last updated this campaign
    workflow_id UUID NOT NULL 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 false,                                      -- if the campaign stage is currently active (only one at a time)
    auto_advance BOOLEAN NOT NULL DEFAULT true,                                 -- whether this stage auto-advances when criteria are met
    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
);

-- 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,                                                            -- 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
);

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

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

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

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

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

-- Filter only active tasks
CREATE INDEX idx_campaign_tasks_active ON crm_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);