CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    root_path TEXT NOT NULL UNIQUE,
    default_branch TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS workspaces (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    root_path TEXT NOT NULL UNIQUE,
    base_branch TEXT NOT NULL,
    worktree_branch TEXT NOT NULL UNIQUE,
    runtime_backend TEXT NOT NULL,
    runtime_ref TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_workspaces_project ON workspaces(project_id, status, slug);

CREATE TABLE IF NOT EXISTS roles (
    name TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    is_enabled INTEGER NOT NULL DEFAULT 1,
    is_builtin INTEGER NOT NULL DEFAULT 1,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS role_prompts (
    id TEXT PRIMARY KEY,
    role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
    workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
    prompt_kind TEXT NOT NULL,
    content_markdown TEXT NOT NULL,
    version INTEGER NOT NULL,
    updated_by TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_prompts_global
ON role_prompts(role_name, prompt_kind)
WHERE workspace_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_prompts_workspace
ON role_prompts(role_name, workspace_id, prompt_kind)
WHERE workspace_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_role_prompts_role ON role_prompts(role_name, prompt_kind, workspace_id);

CREATE TABLE IF NOT EXISTS role_configs (
    id TEXT PRIMARY KEY,
    role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
    workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
    model TEXT NOT NULL DEFAULT '',
    model_provider TEXT NOT NULL DEFAULT '',
    provider_name TEXT NOT NULL DEFAULT '',
    provider_base_url TEXT NOT NULL DEFAULT '',
    provider_wire_api TEXT NOT NULL DEFAULT '',
    reasoning_effort TEXT NOT NULL DEFAULT '',
    plan_model TEXT NOT NULL DEFAULT '',
    plan_reasoning_effort TEXT NOT NULL DEFAULT '',
    disable_response_storage INTEGER NOT NULL DEFAULT 0,
    shell_env_inherit TEXT NOT NULL DEFAULT 'core',
    shell_env_overrides_json TEXT NOT NULL DEFAULT '{}',
    extra_config_json TEXT NOT NULL DEFAULT '{}',
    version INTEGER NOT NULL,
    updated_by TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_configs_global
ON role_configs(role_name)
WHERE workspace_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_configs_workspace
ON role_configs(role_name, workspace_id)
WHERE workspace_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_role_configs_role ON role_configs(role_name, workspace_id);

CREATE TABLE IF NOT EXISTS skills (
    id TEXT PRIMARY KEY,
    skill_key TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    source_type TEXT NOT NULL,
    content_markdown TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL,
    version INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_skills_status ON skills(status, name);

CREATE TABLE IF NOT EXISTS role_skill_bindings (
    id TEXT PRIMARY KEY,
    role_name TEXT NOT NULL REFERENCES roles(name) ON DELETE CASCADE,
    workspace_id TEXT REFERENCES workspaces(id) ON DELETE CASCADE,
    skill_id TEXT NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    sort_order INTEGER NOT NULL DEFAULT 0,
    config_json TEXT NOT NULL DEFAULT '{}',
    version INTEGER NOT NULL,
    updated_by TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_skill_bindings_global
ON role_skill_bindings(role_name, skill_id)
WHERE workspace_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_role_skill_bindings_workspace
ON role_skill_bindings(role_name, workspace_id, skill_id)
WHERE workspace_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_role_skill_bindings_role ON role_skill_bindings(role_name, workspace_id, sort_order);

CREATE TABLE IF NOT EXISTS config_change_logs (
    id TEXT PRIMARY KEY,
    config_domain TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    workspace_id TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
    change_action TEXT NOT NULL,
    before_json TEXT NOT NULL DEFAULT '{}',
    after_json TEXT NOT NULL DEFAULT '{}',
    changed_by TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_config_change_logs_domain ON config_change_logs(config_domain, created_at);
CREATE INDEX IF NOT EXISTS idx_config_change_logs_workspace ON config_change_logs(workspace_id, created_at);

CREATE TABLE IF NOT EXISTS topics (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    slug TEXT NOT NULL,
    title TEXT NOT NULL,
    space TEXT NOT NULL,
    status TEXT NOT NULL,
    source_topic_id TEXT REFERENCES topics(id) ON DELETE SET NULL,
    owner_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
    summary TEXT NOT NULL DEFAULT '',
    meta_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    closed_at TEXT,
    UNIQUE(workspace_id, slug)
);

CREATE INDEX IF NOT EXISTS idx_topics_workspace_space ON topics(workspace_id, space, updated_at);

CREATE TABLE IF NOT EXISTS topic_documents (
    id TEXT PRIMARY KEY,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    kind TEXT NOT NULL,
    content_markdown TEXT NOT NULL,
    version INTEGER NOT NULL,
    updated_by_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(topic_id, kind)
);

CREATE TABLE IF NOT EXISTS messages (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    from_role_name TEXT NOT NULL REFERENCES roles(name),
    to_expr TEXT NOT NULL,
    type TEXT NOT NULL,
    stage TEXT NOT NULL,
    round INTEGER,
    reply_to_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
    body_markdown TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_messages_workspace_topic ON messages(workspace_id, topic_id, created_at);
CREATE INDEX IF NOT EXISTS idx_messages_reply_to ON messages(reply_to_message_id, created_at);

CREATE TABLE IF NOT EXISTS message_deliveries (
    message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    recipient_role_name TEXT NOT NULL REFERENCES roles(name),
    state TEXT NOT NULL,
    delivered_at TEXT NOT NULL,
    read_at TEXT,
    archived_at TEXT,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (message_id, recipient_role_name)
);

CREATE INDEX IF NOT EXISTS idx_message_deliveries_role ON message_deliveries(recipient_role_name, state, updated_at);

CREATE TABLE IF NOT EXISTS role_threads (
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    role_name TEXT NOT NULL REFERENCES roles(name),
    thread_id TEXT NOT NULL,
    last_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
    created_at TEXT NOT NULL,
    last_used_at TEXT NOT NULL,
    PRIMARY KEY (workspace_id, topic_id, role_name)
);

CREATE INDEX IF NOT EXISTS idx_role_threads_role ON role_threads(role_name, last_used_at);

CREATE TABLE IF NOT EXISTS requirements (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    body_markdown TEXT NOT NULL,
    status TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_by_role_name TEXT NOT NULL REFERENCES roles(name),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    completed_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_requirements_workspace_status ON requirements(workspace_id, status, priority, created_at);
CREATE INDEX IF NOT EXISTS idx_requirements_topic ON requirements(topic_id, created_at);

CREATE TABLE IF NOT EXISTS discovery_rounds (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    phase TEXT NOT NULL,
    request_document_id TEXT REFERENCES topic_documents(id) ON DELETE SET NULL,
    result_document_id TEXT REFERENCES topic_documents(id) ON DELETE SET NULL,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_discovery_rounds_topic ON discovery_rounds(topic_id, started_at);

CREATE TABLE IF NOT EXISTS discovery_candidates (
    id TEXT PRIMARY KEY,
    round_id TEXT NOT NULL REFERENCES discovery_rounds(id) ON DELETE CASCADE,
    proposer_role_name TEXT NOT NULL REFERENCES roles(name),
    status TEXT NOT NULL,
    title TEXT NOT NULL,
    problem TEXT NOT NULL DEFAULT '',
    evidence TEXT NOT NULL DEFAULT '',
    proposal TEXT NOT NULL DEFAULT '',
    expected_impact TEXT NOT NULL DEFAULT '',
    risk TEXT NOT NULL DEFAULT '',
    how_to_verify TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_discovery_candidates_round ON discovery_candidates(round_id, status, updated_at);

CREATE TABLE IF NOT EXISTS discovery_votes (
    id TEXT PRIMARY KEY,
    round_id TEXT NOT NULL REFERENCES discovery_rounds(id) ON DELETE CASCADE,
    candidate_id TEXT NOT NULL REFERENCES discovery_candidates(id) ON DELETE CASCADE,
    voter_role_name TEXT NOT NULL REFERENCES roles(name),
    vote TEXT NOT NULL,
    reason TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_discovery_votes_unique ON discovery_votes(candidate_id, voter_role_name);
CREATE INDEX IF NOT EXISTS idx_discovery_votes_round ON discovery_votes(round_id, created_at);

CREATE TABLE IF NOT EXISTS workflow_runs (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    role_name TEXT NOT NULL REFERENCES roles(name),
    stage TEXT NOT NULL,
    mode TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL,
    request_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
    thread_id TEXT NOT NULL DEFAULT '',
    prior_thread_id TEXT NOT NULL DEFAULT '',
    config_snapshot_json TEXT NOT NULL DEFAULT '{}',
    command_json TEXT NOT NULL DEFAULT '[]',
    reply_message_id TEXT REFERENCES messages(id) ON DELETE SET NULL,
    exit_code INTEGER NOT NULL DEFAULT 0,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    error_message TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_workflow_runs_topic ON workflow_runs(topic_id, started_at);
CREATE INDEX IF NOT EXISTS idx_workflow_runs_role ON workflow_runs(role_name, status, started_at);

CREATE TABLE IF NOT EXISTS workflow_run_logs (
    run_id TEXT NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
    seq INTEGER NOT NULL,
    stream TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT NOT NULL,
    PRIMARY KEY (run_id, seq)
);

CREATE TABLE IF NOT EXISTS merge_requests (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    topic_id TEXT NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    workflow_run_id TEXT REFERENCES workflow_runs(id) ON DELETE SET NULL,
    requested_by_role_name TEXT REFERENCES roles(name) ON DELETE SET NULL,
    target_branch TEXT NOT NULL,
    status TEXT NOT NULL,
    summary TEXT NOT NULL DEFAULT '',
    files_changed INTEGER NOT NULL DEFAULT 0,
    insertions INTEGER NOT NULL DEFAULT 0,
    deletions INTEGER NOT NULL DEFAULT 0,
    changed_files_json TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL,
    merged_at TEXT,
    error_message TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_merge_requests_topic ON merge_requests(topic_id, created_at);
