-- ==========================================
-- Switchboard Core — 007 Workflows (SQLite)
-- ==========================================
-- v0.3.0: persona_id + history_mode dropped (chat vestiges).
--         transition_rules → stage_config, stage_mode modernized.
--         Added: audience, stage_type, starlark_hook, branch_rules.

CREATE TABLE IF NOT EXISTS workflows (
    id           TEXT PRIMARY KEY,
    team_id      TEXT REFERENCES teams(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    slug         TEXT NOT NULL,
    description  TEXT NOT NULL DEFAULT '',
    branding     TEXT NOT NULL DEFAULT '{}',
    entry_mode   TEXT NOT NULL DEFAULT 'public_link'
                     CHECK (entry_mode IN ('public_link', 'team_only')),
    is_active    INTEGER NOT NULL DEFAULT 0,
    version      INTEGER NOT NULL DEFAULT 1,
    on_complete  TEXT,
    retention    TEXT NOT NULL DEFAULT '{"mode": "archive"}',
    webhook_url    TEXT,
    webhook_secret TEXT,
    staleness_timeout_hours INTEGER,
    created_by   TEXT NOT NULL REFERENCES users(id),
    created_at   TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_workflows_team_slug
    ON workflows(team_id, slug);
CREATE INDEX IF NOT EXISTS idx_workflows_active ON workflows(is_active);

CREATE TRIGGER IF NOT EXISTS workflows_updated_at AFTER UPDATE ON workflows
    FOR EACH ROW WHEN NEW.updated_at = OLD.updated_at
    BEGIN UPDATE workflows SET updated_at = datetime('now') WHERE id = NEW.id; END;

CREATE TABLE IF NOT EXISTS workflow_stages (
    id                 TEXT PRIMARY KEY,
    workflow_id        TEXT NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    ordinal            INTEGER NOT NULL,
    name               TEXT NOT NULL,
    assignment_team_id TEXT REFERENCES teams(id) ON DELETE SET NULL,
    form_template      TEXT NOT NULL DEFAULT '{}',
    stage_mode         TEXT NOT NULL DEFAULT 'form'
                           CHECK (stage_mode IN ('form', 'review', 'delegated', 'automated')),
    audience           TEXT NOT NULL DEFAULT 'team'
                           CHECK (audience IN ('team', 'public', 'system')),
    stage_type         TEXT NOT NULL DEFAULT 'simple'
                           CHECK (stage_type IN ('simple', 'dynamic', 'automated')),
    auto_transition    INTEGER NOT NULL DEFAULT 0,
    stage_config       TEXT NOT NULL DEFAULT '{}',
    branch_rules       TEXT NOT NULL DEFAULT '[]',
    starlark_hook      TEXT,
    surface_pkg_id     TEXT REFERENCES packages(id) ON DELETE SET NULL,
    sla_seconds        INTEGER,
    created_at         TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_workflow_stages_workflow
    ON workflow_stages(workflow_id, ordinal);

CREATE TABLE IF NOT EXISTS workflow_versions (
    id              TEXT PRIMARY KEY,
    workflow_id     TEXT NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    version_number  INTEGER NOT NULL,
    snapshot        TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE (workflow_id, version_number)
);

CREATE INDEX IF NOT EXISTS idx_workflow_versions_workflow
    ON workflow_versions(workflow_id, version_number);

-- ── Workflow Instances (v0.3.1) ──────────────

CREATE TABLE IF NOT EXISTS workflow_instances (
    id                TEXT PRIMARY KEY,
    workflow_id       TEXT NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    workflow_version  INTEGER NOT NULL,
    current_stage     TEXT NOT NULL,
    stage_data        TEXT NOT NULL DEFAULT '{}',
    status            TEXT NOT NULL DEFAULT 'active'
                          CHECK (status IN ('active', 'completed', 'cancelled', 'stale', 'error')),
    started_by        TEXT NOT NULL,
    entry_token       TEXT UNIQUE,
    metadata          TEXT NOT NULL DEFAULT '{}',
    stage_entered_at  TEXT NOT NULL DEFAULT (datetime('now')),
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_workflow_instances_workflow_status
    ON workflow_instances(workflow_id, status);
CREATE INDEX IF NOT EXISTS idx_workflow_instances_active
    ON workflow_instances(status);

CREATE TRIGGER IF NOT EXISTS workflow_instances_updated_at AFTER UPDATE ON workflow_instances
    FOR EACH ROW WHEN NEW.updated_at = OLD.updated_at
    BEGIN UPDATE workflow_instances SET updated_at = datetime('now') WHERE id = NEW.id; END;

-- ── Workflow Assignments (v0.3.1) ────────────

CREATE TABLE IF NOT EXISTS workflow_assignments (
    id            TEXT PRIMARY KEY,
    instance_id   TEXT NOT NULL REFERENCES workflow_instances(id) ON DELETE CASCADE,
    stage         TEXT NOT NULL,
    team_id       TEXT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    assigned_to   TEXT REFERENCES users(id) ON DELETE SET NULL,
    status        TEXT NOT NULL DEFAULT 'unassigned'
                      CHECK (status IN ('unassigned', 'claimed', 'completed', 'cancelled')),
    review_data   TEXT NOT NULL DEFAULT '{}',
    claimed_at    TEXT,
    completed_at  TEXT,
    created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_workflow_assignments_instance_stage
    ON workflow_assignments(instance_id, stage);
CREATE INDEX IF NOT EXISTS idx_workflow_assignments_team_status
    ON workflow_assignments(team_id, status);
CREATE INDEX IF NOT EXISTS idx_workflow_assignments_assigned
    ON workflow_assignments(assigned_to);

-- ── Workflow Signoffs (v0.3.4) ────────────────

CREATE TABLE IF NOT EXISTS workflow_signoffs (
    id            TEXT PRIMARY KEY,
    instance_id   TEXT NOT NULL REFERENCES workflow_instances(id) ON DELETE CASCADE,
    stage         TEXT NOT NULL,
    user_id       TEXT NOT NULL,
    decision      TEXT NOT NULL CHECK (decision IN ('approve', 'reject')),
    comment       TEXT NOT NULL DEFAULT '',
    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(instance_id, stage, user_id)
);

CREATE INDEX IF NOT EXISTS idx_workflow_signoffs_instance_stage
    ON workflow_signoffs(instance_id, stage);
