-- Muse AI 模块初始 Schema（PostgreSQL 16）
-- 影响范围：AI 任务、候选、智能体、Prompt、质量策略、工具授权。

CREATE TABLE muse_ai_generation (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_id BIGINT,
    chapter_id BIGINT,
    block_id BIGINT,
    agent_id BIGINT,
    agent_version_id BIGINT,
    task_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'queued',
    idempotency_key VARCHAR(200) NOT NULL,
    input_snapshot JSONB,
    output_summary JSONB,
    candidate_id BIGINT,
    error_code VARCHAR(50),
    error_message VARCHAR(500),
    retry_count INT NOT NULL DEFAULT 0,
    started_at TIMESTAMP,
    finished_at TIMESTAMP,
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_ai_generation_idem UNIQUE (tenant_id, idempotency_key)
);

CREATE INDEX idx_muse_ai_gen_work_status ON muse_ai_generation(tenant_id, work_id, status, create_time);
CREATE INDEX idx_muse_ai_gen_block ON muse_ai_generation(tenant_id, block_id);
CREATE TRIGGER trg_muse_ai_generation_updated_at BEFORE UPDATE ON muse_ai_generation FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_ai_suggestion (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_id BIGINT NOT NULL,
    chapter_id BIGINT,
    block_id BIGINT,
    generation_id BIGINT NOT NULL,
    candidate_type VARCHAR(50) NOT NULL DEFAULT 'suggestion',
    content_snapshot JSONB,
    agent_id BIGINT,
    agent_name VARCHAR(200),
    agent_version VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    source_revision INT,
    source_hash VARCHAR(100),
    diff_summary JSONB,
    quality_scores JSONB,
    quality_result_id BIGINT,
    source_snapshot_id BIGINT,
    authorization_snapshot_id BIGINT,
    source_status VARCHAR(30),
    source_action_policy VARCHAR(30),
    decision_archive_id BIGINT,
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_muse_ai_suggestion_work_status ON muse_ai_suggestion(tenant_id, work_id, status, create_time);
CREATE INDEX idx_muse_ai_suggestion_gen ON muse_ai_suggestion(tenant_id, generation_id);
CREATE UNIQUE INDEX uk_muse_ai_suggestion_command ON muse_ai_suggestion(tenant_id, command_id) WHERE command_id IS NOT NULL;
CREATE TRIGGER trg_muse_ai_suggestion_updated_at BEFORE UPDATE ON muse_ai_suggestion FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_agent (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    agent_key VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description VARCHAR(500),
    agent_type VARCHAR(20) NOT NULL DEFAULT 'system',
    owner_user_id BIGINT,
    prompt_key VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    current_version_id BIGINT,
    slot_bindings JSONB,
    category VARCHAR(50),
    tags JSONB,
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_agent_key UNIQUE (tenant_id, agent_key)
);

CREATE INDEX idx_muse_agent_type_status ON muse_agent(tenant_id, agent_type, status);
CREATE TRIGGER trg_muse_agent_updated_at BEFORE UPDATE ON muse_agent FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_agent_version (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    agent_id BIGINT NOT NULL,
    version VARCHAR(50) NOT NULL,
    config JSONB,
    change_note VARCHAR(500),
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_agent_version_agent_ver UNIQUE (tenant_id, agent_id, version)
);

CREATE INDEX idx_muse_agent_version_agent ON muse_agent_version(tenant_id, agent_id);
CREATE TRIGGER trg_muse_agent_version_updated_at BEFORE UPDATE ON muse_agent_version FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_agent_slot_binding (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_id BIGINT NOT NULL,
    slot_key VARCHAR(100) NOT NULL,
    agent_id BIGINT NOT NULL,
    agent_version VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    revision INT NOT NULL DEFAULT 1,
    authorization_snapshot_id BIGINT,
    source_snapshot_id BIGINT,
    fallback_agent_id BIGINT,
    binding_source VARCHAR(50),
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_muse_agent_slot_binding_work ON muse_agent_slot_binding(tenant_id, work_id, slot_key, status);
CREATE UNIQUE INDEX uk_muse_agent_slot_binding_command ON muse_agent_slot_binding(tenant_id, command_id) WHERE command_id IS NOT NULL;
CREATE TRIGGER trg_muse_agent_slot_binding_updated_at BEFORE UPDATE ON muse_agent_slot_binding FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_prompt (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    prompt_key VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description VARCHAR(500),
    content TEXT,
    active_version VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    category VARCHAR(50),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_prompt_key UNIQUE (tenant_id, prompt_key)
);

CREATE TRIGGER trg_muse_prompt_updated_at BEFORE UPDATE ON muse_prompt FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_prompt_version (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    prompt_id BIGINT NOT NULL,
    prompt_key VARCHAR(100) NOT NULL,
    version VARCHAR(50) NOT NULL,
    content TEXT,
    variables JSONB,
    change_note VARCHAR(500),
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_prompt_version_key_ver UNIQUE (tenant_id, prompt_key, version)
);

CREATE INDEX idx_muse_prompt_version_prompt ON muse_prompt_version(tenant_id, prompt_id);
CREATE TRIGGER trg_muse_prompt_version_updated_at BEFORE UPDATE ON muse_prompt_version FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_quality_policy (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    policy_key VARCHAR(100) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description VARCHAR(500),
    active_version VARCHAR(50),
    task_types JSONB,
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_quality_policy_key UNIQUE (tenant_id, policy_key)
);

CREATE TRIGGER trg_muse_quality_policy_updated_at BEFORE UPDATE ON muse_quality_policy FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_quality_policy_version (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    policy_id BIGINT NOT NULL,
    policy_key VARCHAR(100) NOT NULL,
    version_no INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    active_flag BOOLEAN NOT NULL DEFAULT FALSE,
    effective_scope JSONB,
    metric_contract_snapshot JSONB,
    threshold_snapshot JSONB,
    rewrite_policy_snapshot JSONB,
    rollback_from_version_id BIGINT,
    rollback_to_version_id BIGINT,
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_quality_policy_ver UNIQUE (tenant_id, policy_key, version_no)
);

CREATE INDEX idx_muse_quality_policy_ver_pid ON muse_quality_policy_version(tenant_id, policy_id);
CREATE TRIGGER trg_muse_quality_policy_ver_updated_at BEFORE UPDATE ON muse_quality_policy_version FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE muse_tool_grant (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    grant_key VARCHAR(100) NOT NULL,
    agent_id BIGINT NOT NULL,
    tool_name VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL,
    purpose VARCHAR(200),
    authority_owner VARCHAR(50) NOT NULL DEFAULT 'governance',
    scope JSONB,
    budget JSONB,
    input_source_policy JSONB,
    output_target_policy JSONB,
    egress_policy JSONB,
    outbound_policy JSONB,
    audit_requirement JSONB,
    approval_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    approved_by VARCHAR(64),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    command_id VARCHAR(120),
    creator VARCHAR(64) NOT NULL DEFAULT '',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updater VARCHAR(64) NOT NULL DEFAULT '',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT uk_muse_tool_grant_key UNIQUE (tenant_id, grant_key)
);

CREATE INDEX idx_muse_tool_grant_agent ON muse_tool_grant(tenant_id, agent_id);
CREATE UNIQUE INDEX uk_muse_tool_grant_command ON muse_tool_grant(tenant_id, command_id) WHERE command_id IS NOT NULL;
CREATE TRIGGER trg_muse_tool_grant_updated_at BEFORE UPDATE ON muse_tool_grant FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();