-- ==========================================
-- Switchboard Core — 007 Workflows
-- ==========================================
-- Workflow definitions, stages, version snapshots.
-- 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           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    team_id      UUID REFERENCES teams(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    slug         TEXT NOT NULL,
    description  TEXT NOT NULL DEFAULT '',
    branding     JSONB NOT NULL DEFAULT '{}',
    entry_mode   TEXT NOT NULL DEFAULT 'public_link'
                     CHECK (entry_mode IN ('public_link', 'team_only')),
    is_active    BOOLEAN NOT NULL DEFAULT false,
    version      INTEGER NOT NULL DEFAULT 1,
    on_complete  JSONB,
    retention    JSONB NOT NULL DEFAULT '{"mode": "archive"}',
    webhook_url    TEXT,
    webhook_secret TEXT,
    staleness_timeout_hours INTEGER,
    created_by   UUID NOT NULL REFERENCES users(id),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_workflows_team_slug
    ON workflows(team_id, slug) WHERE team_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_workflows_global_slug
    ON workflows(slug) WHERE team_id IS NULL;
CREATE INDEX IF NOT EXISTS idx_workflows_active
    ON workflows(is_active) WHERE is_active = true;

DROP TRIGGER IF EXISTS workflows_updated_at ON workflows;
CREATE TRIGGER workflows_updated_at BEFORE UPDATE ON workflows
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Workflow Stages ─────────────────────────

CREATE TABLE IF NOT EXISTS workflow_stages (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id        UUID NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    ordinal            INTEGER NOT NULL,
    name               TEXT NOT NULL,
    assignment_team_id UUID REFERENCES teams(id) ON DELETE SET NULL,
    form_template      JSONB 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    BOOLEAN NOT NULL DEFAULT false,
    stage_config       JSONB NOT NULL DEFAULT '{}',
    branch_rules       JSONB NOT NULL DEFAULT '[]',
    starlark_hook      TEXT,
    surface_pkg_id     TEXT REFERENCES packages(id) ON DELETE SET NULL,
    sla_seconds        INTEGER,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- ── Workflow Versions ───────────────────────

CREATE TABLE IF NOT EXISTS workflow_versions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id     UUID NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    version_number  INTEGER NOT NULL,
    snapshot        JSONB NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (workflow_id, version_number)
);

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

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

CREATE TABLE IF NOT EXISTS workflow_instances (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id       UUID NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    workflow_version  INTEGER NOT NULL,
    current_stage     TEXT NOT NULL,
    stage_data        JSONB 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,
    metadata          JSONB NOT NULL DEFAULT '{}',
    stage_entered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT 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) WHERE status = 'active';
CREATE UNIQUE INDEX IF NOT EXISTS idx_workflow_instances_token
    ON workflow_instances(entry_token) WHERE entry_token IS NOT NULL;

DROP TRIGGER IF EXISTS workflow_instances_updated_at ON workflow_instances;
CREATE TRIGGER workflow_instances_updated_at BEFORE UPDATE ON workflow_instances
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

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

CREATE TABLE IF NOT EXISTS workflow_assignments (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    instance_id   UUID NOT NULL REFERENCES workflow_instances(id) ON DELETE CASCADE,
    stage         TEXT NOT NULL,
    team_id       UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    assigned_to   UUID REFERENCES users(id) ON DELETE SET NULL,
    status        TEXT NOT NULL DEFAULT 'unassigned'
                      CHECK (status IN ('unassigned', 'claimed', 'completed', 'cancelled')),
    review_data   JSONB NOT NULL DEFAULT '{}',
    claimed_at    TIMESTAMPTZ,
    completed_at  TIMESTAMPTZ,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT 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) WHERE assigned_to IS NOT NULL;

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

CREATE TABLE IF NOT EXISTS workflow_signoffs (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    instance_id   UUID 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    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(instance_id, stage, user_id)
);

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