-- 001_init.sql — Dialectic v2 schema (greenfield, replaces Python v1).
-- See /home/hzhang/arch/DIALECTIC-V2-DESIGN.md for the design.

-- Verdict schemas — declared at topic-creation time, judge produces output matching.
CREATE TABLE verdict_schemas (
    id          VARCHAR(64)  NOT NULL PRIMARY KEY,
    description TEXT         NOT NULL,
    shape_json  JSON         NOT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed v1 schemas.
INSERT INTO verdict_schemas (id, description, shape_json) VALUES
    ('binary', 'pro|con|draw with confidence + key reasoning', JSON_OBJECT('decision', 'pro|con|draw', 'confidence', 'number 0..1', 'key_reasoning', 'string')),
    ('claim-resolution', 'analyze-intel contested-cluster resolution', JSON_OBJECT('verdict', 'resolved-toward-A|resolved-toward-B|irreducibly-contested', 'winning_claim', 'string', 'dissenting_points', 'array of string', 'confidence', 'number 0..1')),
    ('policy-recommendation', 'recommended action with alternatives and risks', JSON_OBJECT('recommended_action', 'string', 'alternatives', 'array of string', 'conditions_for_alternatives', 'array of string', 'risks_noted', 'array of string')),
    ('free-form', 'unstructured summary escape hatch', JSON_OBJECT('summary', 'string'));

-- Topics (议题) — the unit of debate.
CREATE TABLE topics (
    id                       CHAR(36)     NOT NULL PRIMARY KEY,
    title                    VARCHAR(255) NOT NULL,
    summary                  TEXT         NOT NULL,
    visibility               ENUM('public','private') NOT NULL DEFAULT 'private',
    verdict_schema_id        VARCHAR(64)  NOT NULL,
    status                   ENUM('created','signup_open','signup_closed','debating','completed','cancelled') NOT NULL DEFAULT 'created',
    -- Lifecycle timestamps (per section 3 of design doc)
    signup_open_at           TIMESTAMP    NOT NULL,
    signup_close_at          TIMESTAMP    NOT NULL,
    debate_start_at          TIMESTAMP    NOT NULL,
    debate_end_at            TIMESTAMP    NOT NULL,
    -- Audit
    creator_user_id          CHAR(36)     NOT NULL,
    visibility_changed_by    CHAR(36)     NULL,
    visibility_changed_at    TIMESTAMP    NULL,
    cancelled_reason         VARCHAR(255) NULL,
    created_at               TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_topics_status (status, signup_open_at),
    INDEX idx_topics_visibility (visibility, created_at),
    CONSTRAINT fk_topics_schema FOREIGN KEY (verdict_schema_id) REFERENCES verdict_schemas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Signups: an agent volunteers for one or more camps on a topic.
-- willing_camps is a JSON array of camp names (subset of {pro, con, judge}).
-- (agent_id, topic_id) is unique — re-signup updates willing_camps.
CREATE TABLE signups (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    topic_id        CHAR(36)     NOT NULL,
    agent_id        VARCHAR(64)  NOT NULL,
    willing_camps   JSON         NOT NULL,
    -- Pre-validation result captured at signup time (plugin verifies the
    -- agent has an on_call slot covering the debate window; backend
    -- records what the agent told it for audit).
    pre_validated   BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_signups (topic_id, agent_id),
    CONSTRAINT fk_signups_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Camps: the post-allocation assignment. One row per (topic, camp) with
-- the locked-in agent. Written by camp-allocation algorithm at
-- signup_close_at — immutable afterwards (no drop-out / replacement in v1).
CREATE TABLE camps (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    topic_id        CHAR(36)     NOT NULL,
    camp            ENUM('pro','con','judge') NOT NULL,
    agent_id        VARCHAR(64)  NOT NULL,
    allocated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_camps (topic_id, camp),
    INDEX idx_camps_agent (agent_id),
    CONSTRAINT fk_camps_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rounds: chronological partition of arguments. Each topic has N rounds
-- (typically 3-5), round 0 is the opening. Round transitions are driven
-- by the orchestrator on a schedule (or all-participants-posted).
CREATE TABLE rounds (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    topic_id        CHAR(36)     NOT NULL,
    round_no        INT          NOT NULL,
    opened_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at       TIMESTAMP    NULL,
    UNIQUE KEY uq_rounds (topic_id, round_no),
    CONSTRAINT fk_rounds_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arguments: an individual contribution within a round by a camp's agent.
-- For pro/con these are claims/rebuttals — for judge these are clarifying
-- questions (judge is silent observer in v1 except for clarifications).
CREATE TABLE arguments (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    topic_id        CHAR(36)     NOT NULL,
    round_id        CHAR(36)     NOT NULL,
    camp            ENUM('pro','con','judge') NOT NULL,
    agent_id        VARCHAR(64)  NOT NULL,
    content         MEDIUMTEXT   NOT NULL,
    posted_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_arguments_round (round_id, posted_at),
    INDEX idx_arguments_topic (topic_id, posted_at),
    CONSTRAINT fk_arguments_round FOREIGN KEY (round_id) REFERENCES rounds(id) ON DELETE CASCADE,
    CONSTRAINT fk_arguments_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Verdicts: judge's structured output, one per topic (one verdict per
-- debate). verdict_json shape matches the topic's verdict_schema_id.
CREATE TABLE verdicts (
    id                 CHAR(36)     NOT NULL PRIMARY KEY,
    topic_id           CHAR(36)     NOT NULL UNIQUE,
    judge_agent_id     VARCHAR(64)  NOT NULL,
    verdict_json       JSON         NOT NULL,
    rationale          TEXT         NOT NULL,
    -- Token cost trail for accounting (Phase 1: not enforced; Phase N: budget gate)
    tokens_input       INT          NOT NULL DEFAULT 0,
    tokens_output      INT          NOT NULL DEFAULT 0,
    produced_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_verdicts_topic FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Agent API keys: provisioned per agent at recruitment time. Stored as
-- sha256(pepper || raw) — pepper rotation invalidates all keys.
CREATE TABLE agent_keys (
    agent_id       VARCHAR(64)  NOT NULL PRIMARY KEY,
    key_hash       CHAR(64)     NOT NULL UNIQUE,
    issued_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at   TIMESTAMP    NULL,
    revoked_at     TIMESTAMP    NULL,
    INDEX idx_agent_keys_hash (key_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- System keys: out-of-band credentials for non-agent callers (e.g. the
-- analyze-intel workflow running via a system identity that creates
-- topics on behalf of the analyzing agent). Also stored as hash.
CREATE TABLE system_keys (
    name           VARCHAR(64)  NOT NULL PRIMARY KEY,
    key_hash       CHAR(64)     NOT NULL UNIQUE,
    description    TEXT         NULL,
    issued_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at     TIMESTAMP    NULL,
    INDEX idx_system_keys_hash (key_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;