-- =============================================================================
-- 迁移：P14 — AI 模块改造（DashScope 迁移 + 调度器完善）
-- 日期：2026-03-22
-- 关联 SPEC：P14-ai-dashscope-migration
-- 需求：14.1, 14.2, 14.3, 14.4, 14.5, 14.6, 14.7
-- 说明：
--   1. 新建 biz.ai_run_logs（AI 运行记录，含 3 个索引）
--   2. 新建 biz.ai_trigger_jobs（调度运行记录，含 3 个索引，含去重部分索引）
--   3. ai_conversations 新增 session_id 字段（百炼会话 ID）
--   4. ai_cache 新增 status 字段 + CHECK 约束
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. 新建 biz.ai_run_logs（AI 运行记录）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.ai_run_logs (
    id              BIGSERIAL PRIMARY KEY,
    site_id         BIGINT NOT NULL,
    app_type        VARCHAR(30) NOT NULL,
    trigger_type    VARCHAR(20) NOT NULL,
    member_id       BIGINT,
    request_prompt  TEXT,
    response_text   TEXT,
    tokens_used     INTEGER DEFAULT 0,
    latency_ms      INTEGER,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    error_message   TEXT,
    session_id      VARCHAR(100),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at     TIMESTAMPTZ
);

COMMENT ON TABLE biz.ai_run_logs IS 'AI 运行记录：每次 DashScope Application API 调用的详细日志';
COMMENT ON COLUMN biz.ai_run_logs.app_type IS '应用类型：app1_chat / app2_finance / app3_clue / ... / app8_consolidate';
COMMENT ON COLUMN biz.ai_run_logs.trigger_type IS '触发类型：user / scheduled / event / forced';
COMMENT ON COLUMN biz.ai_run_logs.request_prompt IS '请求 prompt（截断前 2000 字符）';
COMMENT ON COLUMN biz.ai_run_logs.status IS '状态：pending / running / success / failed / timeout / budget_exceeded';
COMMENT ON COLUMN biz.ai_run_logs.session_id IS '百炼 session_id（仅 App1 使用）';

CREATE INDEX IF NOT EXISTS idx_ai_run_logs_site_app
    ON biz.ai_run_logs (site_id, app_type);
CREATE INDEX IF NOT EXISTS idx_ai_run_logs_created
    ON biz.ai_run_logs (created_at);
CREATE INDEX IF NOT EXISTS idx_ai_run_logs_status
    ON biz.ai_run_logs (status);

-- ---------------------------------------------------------------------------
-- 2. 新建 biz.ai_trigger_jobs（调度运行记录）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.ai_trigger_jobs (
    id              BIGSERIAL PRIMARY KEY,
    site_id         BIGINT NOT NULL,
    event_type      VARCHAR(30) NOT NULL,
    connector_type  VARCHAR(30) DEFAULT 'feiqiu',
    member_id       BIGINT,
    payload         JSONB,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    is_forced       BOOLEAN DEFAULT false,
    app_chain       VARCHAR(100),
    started_at      TIMESTAMPTZ,
    finished_at     TIMESTAMPTZ,
    error_message   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE biz.ai_trigger_jobs IS '调度运行记录：每次 AI 事件触发的编排执行记录';
COMMENT ON COLUMN biz.ai_trigger_jobs.event_type IS '事件类型：consumption / dws_completed / note_created / task_assigned';
COMMENT ON COLUMN biz.ai_trigger_jobs.status IS '状态：pending / running / completed / failed / skipped_duplicate / budget_exceeded';
COMMENT ON COLUMN biz.ai_trigger_jobs.is_forced IS '是否强制执行（跳过去重检查）';
COMMENT ON COLUMN biz.ai_trigger_jobs.app_chain IS '调用链描述，如 app3→app8→app7';

CREATE INDEX IF NOT EXISTS idx_ai_trigger_jobs_site
    ON biz.ai_trigger_jobs (site_id, event_type);
CREATE INDEX IF NOT EXISTS idx_ai_trigger_jobs_dedup
    ON biz.ai_trigger_jobs (event_type, member_id, site_id, created_at)
    WHERE status NOT IN ('skipped_duplicate');
CREATE INDEX IF NOT EXISTS idx_ai_trigger_jobs_status
    ON biz.ai_trigger_jobs (status);

-- ---------------------------------------------------------------------------
-- 3. ai_conversations 新增 session_id 字段
-- ---------------------------------------------------------------------------
ALTER TABLE biz.ai_conversations
    ADD COLUMN IF NOT EXISTS session_id VARCHAR(100);

COMMENT ON COLUMN biz.ai_conversations.session_id
    IS '百炼 session_id，格式 conv_{conversation_id}_{created_timestamp}，仅 App1 使用';

-- ---------------------------------------------------------------------------
-- 4. ai_cache 新增 status 字段 + CHECK 约束
-- ---------------------------------------------------------------------------
ALTER TABLE biz.ai_cache
    ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'valid';

-- CHECK 约束需要先检查是否已存在（IF NOT EXISTS 不适用于约束）
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'chk_ai_cache_status'
          AND conrelid = 'biz.ai_cache'::regclass
    ) THEN
        ALTER TABLE biz.ai_cache
            ADD CONSTRAINT chk_ai_cache_status
            CHECK (status IN ('valid', 'expired', 'invalidated', 'generating'));
    END IF;
END $$;

COMMENT ON COLUMN biz.ai_cache.status
    IS '缓存状态：valid（有效）/ expired（已过期）/ invalidated（手动失效）/ generating（生成中）';

-- =============================================================================
-- 回滚脚本（逆序执行）
-- =============================================================================
-- ALTER TABLE biz.ai_cache DROP CONSTRAINT IF EXISTS chk_ai_cache_status;
-- ALTER TABLE biz.ai_cache DROP COLUMN IF EXISTS status;
-- ALTER TABLE biz.ai_conversations DROP COLUMN IF EXISTS session_id;
-- DROP TABLE IF EXISTS biz.ai_trigger_jobs;
-- DROP TABLE IF EXISTS biz.ai_run_logs;

-- =============================================================================
-- 验证 SQL
-- =============================================================================
-- 1) ai_run_logs 表存在且字段正确
-- SELECT column_name, data_type, character_maximum_length
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'ai_run_logs'
-- ORDER BY ordinal_position;
-- 预期：14 行

-- 2) ai_trigger_jobs 表存在且字段正确
-- SELECT column_name, data_type, character_maximum_length
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'ai_trigger_jobs'
-- ORDER BY ordinal_position;
-- 预期：13 行

-- 3) ai_run_logs 索引
-- SELECT indexname FROM pg_indexes
-- WHERE schemaname = 'biz' AND tablename = 'ai_run_logs';
-- 预期：4 行（含 PK）

-- 4) ai_trigger_jobs 索引（含去重部分索引）
-- SELECT indexname FROM pg_indexes
-- WHERE schemaname = 'biz' AND tablename = 'ai_trigger_jobs';
-- 预期：4 行（含 PK）

-- 5) ai_conversations.session_id 字段
-- SELECT column_name, data_type, character_maximum_length
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'ai_conversations'
--   AND column_name = 'session_id';
-- 预期：1 行，varchar(100)

-- 6) ai_cache.status 字段 + CHECK 约束
-- SELECT column_name, data_type, column_default
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'ai_cache'
--   AND column_name = 'status';
-- 预期：1 行，varchar(20)，default 'valid'

-- SELECT conname FROM pg_constraint
-- WHERE conrelid = 'biz.ai_cache'::regclass AND conname = 'chk_ai_cache_status';
-- 预期：1 行
