-- P17：助教客户归属与任务生成引擎 — 数据库变更
-- 依赖：biz.coach_tasks 表已存在（P4 创建）
-- 回滚：见文件末尾 ROLLBACK 注释块

BEGIN;

-- ═══════════════════════════════════════════════════════════
-- 1. 新增任务状态枚举值
-- ═══════════════════════════════════════════════════════════

-- 检查 coach_tasks.status 是否使用 enum 类型
-- 现有值: active, inactive, completed, abandoned
-- 新增: transferred（已转移）, pending_review（待人工审核）
-- 注意: 如果 status 是 VARCHAR 类型则无需 ALTER TYPE，直接使用即可
-- 以下 DO 块兼容 enum 和 varchar 两种情况

DO $$
BEGIN
    -- 尝试添加 enum 值（如果 status 列使用 enum 类型）
    BEGIN
        EXECUTE 'ALTER TYPE task_status ADD VALUE IF NOT EXISTS ''transferred''';
    EXCEPTION WHEN undefined_object THEN
        -- status 列不是 enum 类型，跳过
        NULL;
    END;
    BEGIN
        EXECUTE 'ALTER TYPE task_status ADD VALUE IF NOT EXISTS ''pending_review''';
    EXCEPTION WHEN undefined_object THEN
        NULL;
    END;
END $$;

-- ═══════════════════════════════════════════════════════════
-- 2. coach_tasks 表新增转移追踪字段
-- ═══════════════════════════════════════════════════════════

ALTER TABLE biz.coach_tasks
    ADD COLUMN IF NOT EXISTS transfer_count   INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS transferred_from BIGINT,
    ADD COLUMN IF NOT EXISTS transferred_at   TIMESTAMPTZ;

-- transferred_from 外键（指向自身，记录转移来源任务）
-- 使用 DO 块避免重复添加约束
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_coach_tasks_transferred_from'
          AND table_schema = 'biz'
          AND table_name = 'coach_tasks'
    ) THEN
        ALTER TABLE biz.coach_tasks
            ADD CONSTRAINT fk_coach_tasks_transferred_from
            FOREIGN KEY (transferred_from) REFERENCES biz.coach_tasks(id);
    END IF;
END $$;

COMMENT ON COLUMN biz.coach_tasks.transfer_count IS '该客户在此任务链上的累计转移次数';
COMMENT ON COLUMN biz.coach_tasks.transferred_from IS '转移来源任务 ID（指向原助教的任务）';
COMMENT ON COLUMN biz.coach_tasks.transferred_at IS '转移发生时间';

-- ═══════════════════════════════════════════════════════════
-- 3. 新增表：biz.cfg_task_generator_params（任务引擎参数配置）
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS biz.cfg_task_generator_params (
    id          BIGSERIAL PRIMARY KEY,
    site_id     BIGINT,              -- NULL 表示全局默认值
    param_key   VARCHAR(64) NOT NULL,
    param_value NUMERIC    NOT NULL,
    description TEXT,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (site_id, param_key)
);

COMMENT ON TABLE biz.cfg_task_generator_params IS 'P17 任务引擎参数配置，支持全局默认 + 门店级覆盖';
COMMENT ON COLUMN biz.cfg_task_generator_params.site_id IS 'NULL=全局默认，非NULL=门店级覆盖';

-- ═══════════════════════════════════════════════════════════
-- 4. 新增表：biz.coach_task_transfer_log（客户转移日志）
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS biz.coach_task_transfer_log (
    id                BIGSERIAL PRIMARY KEY,
    site_id           BIGINT    NOT NULL,
    member_id         BIGINT    NOT NULL,
    from_assistant_id BIGINT    NOT NULL,
    to_assistant_id   BIGINT    NOT NULL,
    from_task_id      BIGINT    NOT NULL REFERENCES biz.coach_tasks(id),
    to_task_id        BIGINT             REFERENCES biz.coach_tasks(id),
    transfer_reason   TEXT,
    guard_checks      JSONB,     -- 三重保护检查结果快照
    transfer_score    NUMERIC,   -- 转移候选得分
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE biz.coach_task_transfer_log IS 'P17 客户转移日志，记录每次转移的完整上下文';

-- 索引：按门店+时间查询转移日志
CREATE INDEX IF NOT EXISTS idx_transfer_log_site_created
    ON biz.coach_task_transfer_log (site_id, created_at DESC);

-- 索引：按客户查询转移历史
CREATE INDEX IF NOT EXISTS idx_transfer_log_member
    ON biz.coach_task_transfer_log (member_id, created_at DESC);

-- ═══════════════════════════════════════════════════════════
-- 5. 插入全局默认参数（P17 第 5 节定义）
-- ═══════════════════════════════════════════════════════════

INSERT INTO biz.cfg_task_generator_params (site_id, param_key, param_value, description)
VALUES
    (NULL, 'high_priority_recall_threshold', 7.0,  'max(WBI,NCI) 超过此值生成高优先召回'),
    (NULL, 'priority_recall_threshold',      5.0,  'max(WBI,NCI) 超过此值生成优先召回'),
    (NULL, 'rs_min_for_relationship',        1.0,  'RS ≤ 此值不生成关系构建'),
    (NULL, 'rs_max_for_relationship',        6.0,  'RS ≥ 此值不生成关系构建'),
    (NULL, 'consecutive_recall_fail_cycles', 3,    '连续失败多少轮触发客户转移'),
    (NULL, 'min_wbi_for_transfer',           5.0,  'WBI 低于此值不触发转移'),
    (NULL, 'guard_assistant_coverage_ratio', 0.5,  '绑定率低于此值禁用转移'),
    (NULL, 'guard_new_assistant_days',       10,   '新助教入驻保护天数'),
    (NULL, 'transfer_score_w_rs',            0.5,  '转移候选排序：RS 权重'),
    (NULL, 'transfer_score_w_ms',            0.3,  '转移候选排序：MS 权重'),
    (NULL, 'transfer_score_w_ml',            0.2,  '转移候选排序：ML 权重'),
    (NULL, 'max_transfer_count',             2,    '单客户最大累计转移次数'),
    (NULL, 'follow_up_visit_retention_hours', 48,  '回访任务最低保留时长（小时）')
ON CONFLICT (site_id, param_key) DO NOTHING;

COMMIT;

-- ═══════════════════════════════════════════════════════════
-- ROLLBACK（手动执行，不在事务内）
-- ═══════════════════════════════════════════════════════════
-- DELETE FROM biz.cfg_task_generator_params WHERE site_id IS NULL;
-- DROP TABLE IF EXISTS biz.coach_task_transfer_log;
-- DROP TABLE IF EXISTS biz.cfg_task_generator_params;
-- ALTER TABLE biz.coach_tasks DROP COLUMN IF EXISTS transfer_count;
-- ALTER TABLE biz.coach_tasks DROP COLUMN IF EXISTS transferred_from;
-- ALTER TABLE biz.coach_tasks DROP COLUMN IF EXISTS transferred_at;
-- 注意：enum 值一旦添加无法直接删除，需重建类型
