-- =============================================================================
-- GeoLynx Task Workflow System — DDL
-- Run after geolynx_ddl.sql. Safe to run on a fresh tasks schema.
-- See task_system_plan.md for full design notes.
-- =============================================================================

-- =============================================================================
-- CONFIGURATION LAYER (admin-managed)
-- =============================================================================

-- Configurable task statuses.
-- is_blocking = true  → task is on hold / blocked (does not propagate completion)
-- is_terminal = true  → task is done; unblocks dependent tasks
CREATE TABLE IF NOT EXISTS tasks.task_statuses (
    status_id     serial PRIMARY KEY,
    status_name   varchar(100) NOT NULL,
    status_colour varchar(7),                     -- hex colour e.g. '#198754'
    is_blocking   boolean NOT NULL DEFAULT false,
    is_terminal   boolean NOT NULL DEFAULT false,
    display_order integer,
    is_active     boolean NOT NULL DEFAULT true,
    created_at    timestamp without time zone DEFAULT now(),
    created_by    bigint REFERENCES users.users(id)
);

-- Seed statuses
INSERT INTO tasks.task_statuses (status_name, status_colour, is_blocking, is_terminal, display_order) VALUES
    ('Not Started', '#6c757d', false, false, 1),
    ('In Progress', '#0d6efd', false, false, 2),
    ('On Hold',     '#ffc107', true,  false, 3),
    ('Blocked',     '#dc3545', true,  false, 4),
    ('Complete',    '#198754', false, true,  5),
    ('Cancelled',   '#adb5bd', false, true,  6)
ON CONFLICT DO NOTHING;


-- Reusable task definitions.
CREATE TABLE IF NOT EXISTS tasks.task_templates (
    template_id      serial PRIMARY KEY,
    name             varchar(255) NOT NULL,
    description      text,
    default_sla_days integer,
    is_active        boolean NOT NULL DEFAULT true,
    created_at       timestamp without time zone DEFAULT now(),
    created_by       bigint REFERENCES users.users(id),
    modified_at      timestamp without time zone DEFAULT now(),
    modified_by      bigint REFERENCES users.users(id)
);


-- Workflow definitions.
-- applies_to controls which item type the workflow can be attached to.
CREATE TABLE IF NOT EXISTS tasks.workflows (
    workflow_id  serial PRIMARY KEY,
    name         varchar(255) NOT NULL,
    description  text,
    applies_to   varchar(20) NOT NULL DEFAULT 'project', -- 'project' | 'account' | 'stocklist'
    is_active    boolean NOT NULL DEFAULT true,
    created_at   timestamp without time zone DEFAULT now(),
    created_by   bigint REFERENCES users.users(id),
    modified_at  timestamp without time zone DEFAULT now(),
    modified_by  bigint REFERENCES users.users(id)
);


-- Tasks within a workflow, with optional per-instance overrides of the template defaults.
CREATE TABLE IF NOT EXISTS tasks.workflow_tasks (
    workflow_task_id   serial PRIMARY KEY,
    workflow_id        integer NOT NULL REFERENCES tasks.workflows(workflow_id) ON DELETE CASCADE,
    template_id        integer NOT NULL REFERENCES tasks.task_templates(template_id),
    sequence_order     integer NOT NULL DEFAULT 0,
    custom_name        varchar(255),    -- overrides template name if set
    custom_description text,            -- overrides template description if set
    sla_days           integer,         -- overrides template default_sla_days if set
    created_at         timestamp without time zone DEFAULT now(),
    created_by         bigint REFERENCES users.users(id),
    UNIQUE (workflow_id, template_id, sequence_order)
);


-- Prerequisite links between workflow tasks.
-- workflow_task_id cannot start until prerequisite_task_id reaches a terminal status.
CREATE TABLE IF NOT EXISTS tasks.task_dependencies (
    dependency_id        serial PRIMARY KEY,
    workflow_task_id     integer NOT NULL REFERENCES tasks.workflow_tasks(workflow_task_id) ON DELETE CASCADE,
    prerequisite_task_id integer NOT NULL REFERENCES tasks.workflow_tasks(workflow_task_id) ON DELETE CASCADE,
    UNIQUE (workflow_task_id, prerequisite_task_id),
    CHECK (workflow_task_id != prerequisite_task_id)
);


-- =============================================================================
-- INSTANCE LAYER (operational)
-- =============================================================================

-- A workflow applied to a specific item. Groups all resulting task instances.
CREATE TABLE IF NOT EXISTS tasks.workflow_instances (
    workflow_instance_id bigserial PRIMARY KEY,
    workflow_id          integer NOT NULL REFERENCES tasks.workflows(workflow_id),
    item_type            varchar(20) NOT NULL,  -- 'project' | 'account' | 'stocklist'
    item_id              integer NOT NULL,
    started_at           timestamp without time zone DEFAULT now(),
    started_by           bigint REFERENCES users.users(id),
    completed_at         timestamp without time zone,
    is_active            boolean NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_workflow_instances_item
    ON tasks.workflow_instances(item_type, item_id);


-- One operational task record per workflow task per workflow run.
-- available_at is set by the availability check function once all prerequisites are terminal.
-- sla_due_date = available_at + sla_days (from workflow_task or template).
-- blocking_task_id links to an ad-hoc task created to unblock this one.
CREATE TABLE IF NOT EXISTS tasks.task_instances (
    task_instance_id     bigserial PRIMARY KEY,
    workflow_instance_id bigint  NOT NULL REFERENCES tasks.workflow_instances(workflow_instance_id) ON DELETE CASCADE,
    workflow_task_id     integer NOT NULL REFERENCES tasks.workflow_tasks(workflow_task_id),
    status_id            integer NOT NULL REFERENCES tasks.task_statuses(status_id),
    assigned_to          bigint  REFERENCES users.users(id),
    available_at         timestamp without time zone,
    sla_due_date         timestamp without time zone,
    ecd                  date,
    started_at           timestamp without time zone,
    completed_at         timestamp without time zone,
    blocking_task_id     bigint  REFERENCES tasks.task_instances(task_instance_id),
    created_at           timestamp without time zone DEFAULT now(),
    created_by           bigint  REFERENCES users.users(id),
    modified_at          timestamp without time zone DEFAULT now(),
    modified_by          bigint  REFERENCES users.users(id)
);

CREATE INDEX IF NOT EXISTS idx_task_instances_workflow
    ON tasks.task_instances(workflow_instance_id);
CREATE INDEX IF NOT EXISTS idx_task_instances_assigned
    ON tasks.task_instances(assigned_to);
CREATE INDEX IF NOT EXISTS idx_task_instances_status
    ON tasks.task_instances(status_id);


-- Append-only comment trail on task instances.
CREATE TABLE IF NOT EXISTS tasks.task_instance_comments (
    comment_id       bigserial PRIMARY KEY,
    task_instance_id bigint NOT NULL REFERENCES tasks.task_instances(task_instance_id) ON DELETE CASCADE,
    comment_text     text   NOT NULL,
    created_at       timestamp without time zone DEFAULT now(),
    created_by       bigint NOT NULL REFERENCES users.users(id)
);

CREATE INDEX IF NOT EXISTS idx_task_comments_instance
    ON tasks.task_instance_comments(task_instance_id);
