-- 迁移：为 3 张指数表添加 stat_date 列，改为日快照模式
-- 目的：支持历史指数回溯和任务推演
-- 回滚：见文件末尾

BEGIN;

-- ============================================================
-- 1a. dws.dws_member_assistant_relation_index
-- ============================================================

ALTER TABLE dws.dws_member_assistant_relation_index
  ADD COLUMN stat_date DATE;

UPDATE dws.dws_member_assistant_relation_index
  SET stat_date = (calc_time AT TIME ZONE 'Asia/Shanghai')::date;

ALTER TABLE dws.dws_member_assistant_relation_index
  ALTER COLUMN stat_date SET NOT NULL,
  ALTER COLUMN stat_date SET DEFAULT CURRENT_DATE;

-- 改唯一约束：(site_id, member_id, assistant_id) → (site_id, member_id, assistant_id, stat_date)
ALTER TABLE dws.dws_member_assistant_relation_index
  DROP CONSTRAINT uk_dws_member_assistant_relation_index;
ALTER TABLE dws.dws_member_assistant_relation_index
  ADD CONSTRAINT uk_dws_member_assistant_relation_index
  UNIQUE (site_id, member_id, assistant_id, stat_date);

-- 索引优化：去掉 calc_time 索引，加 stat_date 索引
DROP INDEX IF EXISTS dws.idx_dws_relation_calc_time;
CREATE INDEX idx_dws_relation_stat_date
  ON dws.dws_member_assistant_relation_index (site_id, stat_date);

-- ============================================================
-- 1b. dws.dws_member_winback_index
-- ============================================================

ALTER TABLE dws.dws_member_winback_index
  ADD COLUMN stat_date DATE;

UPDATE dws.dws_member_winback_index
  SET stat_date = (calc_time AT TIME ZONE 'Asia/Shanghai')::date;

ALTER TABLE dws.dws_member_winback_index
  ALTER COLUMN stat_date SET NOT NULL,
  ALTER COLUMN stat_date SET DEFAULT CURRENT_DATE;

-- 改唯一约束：(site_id, member_id) → (site_id, member_id, stat_date)
ALTER TABLE dws.dws_member_winback_index
  DROP CONSTRAINT uk_dws_member_winback;
ALTER TABLE dws.dws_member_winback_index
  ADD CONSTRAINT uk_dws_member_winback
  UNIQUE (site_id, member_id, stat_date);

CREATE INDEX idx_dws_winback_stat_date
  ON dws.dws_member_winback_index (site_id, stat_date);

-- ============================================================
-- 1c. dws.dws_member_newconv_index
-- ============================================================

ALTER TABLE dws.dws_member_newconv_index
  ADD COLUMN stat_date DATE;

UPDATE dws.dws_member_newconv_index
  SET stat_date = (calc_time AT TIME ZONE 'Asia/Shanghai')::date;

ALTER TABLE dws.dws_member_newconv_index
  ALTER COLUMN stat_date SET NOT NULL,
  ALTER COLUMN stat_date SET DEFAULT CURRENT_DATE;

-- 改唯一约束：(site_id, member_id) → (site_id, member_id, stat_date)
ALTER TABLE dws.dws_member_newconv_index
  DROP CONSTRAINT uk_dws_member_newconv;
ALTER TABLE dws.dws_member_newconv_index
  ADD CONSTRAINT uk_dws_member_newconv
  UNIQUE (site_id, member_id, stat_date);

CREATE INDEX idx_dws_newconv_stat_date
  ON dws.dws_member_newconv_index (site_id, stat_date);

-- ============================================================
-- 2. 视图改造（DISTINCT ON 取最新快照，下游零改动）
-- ============================================================

-- 2a. app.v_dws_member_assistant_relation_index
CREATE OR REPLACE VIEW app.v_dws_member_assistant_relation_index AS
SELECT DISTINCT ON (member_id, assistant_id)
    relation_id, site_id, tenant_id, member_id, assistant_id,
    session_count, total_duration_minutes, basic_session_count, incentive_session_count,
    days_since_last_session,
    rs_f, rs_d, rs_r, rs_raw, rs_display,
    os_share, os_label, os_rank,
    ms_f_short, ms_f_long, ms_raw, ms_display,
    ml_order_count, ml_allocated_amount, ml_raw, ml_display,
    calc_time, created_at, updated_at,
    recall_created_total, recall_completed_total,
    follow_up_created_total, follow_up_completed_total,
    total_created, total_completed,
    stat_date
FROM dws.dws_member_assistant_relation_index
WHERE site_id = current_setting('app.current_site_id')::bigint
ORDER BY member_id, assistant_id, stat_date DESC;

-- 2b. app.v_dws_member_winback_index
CREATE OR REPLACE VIEW app.v_dws_member_winback_index AS
SELECT DISTINCT ON (member_id)
    winback_id, site_id, tenant_id, member_id,
    status, segment,
    member_create_time, first_visit_time, last_visit_time, last_recharge_time,
    t_v, t_r, t_a,
    visits_14d, visits_30d, visits_60d, visits_total,
    spend_30d, spend_180d, sv_balance, recharge_60d_amt,
    interval_count,
    overdue_old, drop_old, recharge_old, value_old,
    raw_score, display_score,
    last_wechat_touch_time, calc_time, calc_version,
    created_at, updated_at,
    overdue_cdf_p, ideal_interval_days, ideal_next_visit_date,
    stat_date
FROM dws.dws_member_winback_index
WHERE site_id = current_setting('app.current_site_id')::bigint
ORDER BY member_id, stat_date DESC;

-- 2c. app.v_dws_member_newconv_index
CREATE OR REPLACE VIEW app.v_dws_member_newconv_index AS
SELECT DISTINCT ON (member_id)
    newconv_id, site_id, tenant_id, member_id,
    status, segment,
    member_create_time, first_visit_time, last_visit_time, last_recharge_time,
    t_v, t_r, t_a,
    visits_14d, visits_30d, visits_60d, visits_total,
    spend_30d, spend_180d, sv_balance, recharge_60d_amt,
    interval_count,
    need_new, salvage_new, recharge_new, value_new, welcome_new,
    raw_score_welcome, raw_score_convert, raw_score,
    display_score_welcome, display_score_convert, display_score,
    last_wechat_touch_time, calc_time, calc_version,
    created_at, updated_at,
    stat_date
FROM dws.dws_member_newconv_index
WHERE site_id = current_setting('app.current_site_id')::bigint
ORDER BY member_id, stat_date DESC;

-- 2d. dws.v_member_recall_priority（UNION ALL 两支先取最新快照）
CREATE OR REPLACE VIEW dws.v_member_recall_priority AS
SELECT w.site_id, w.tenant_id, w.member_id,
    'WBI'::varchar(10) AS index_type,
    w.status, w.segment,
    w.member_create_time, w.first_visit_time, w.last_visit_time, w.last_recharge_time,
    w.t_v, w.t_r, w.t_a,
    w.visits_14d, w.visits_30d, w.visits_60d, w.visits_total,
    w.spend_30d, w.spend_180d, w.sv_balance, w.recharge_60d_amt,
    NULL::numeric(10,4) AS need_new,
    NULL::numeric(10,4) AS salvage_new,
    NULL::numeric(10,4) AS recharge_new,
    NULL::numeric(10,4) AS value_new,
    NULL::numeric(10,4) AS welcome_new,
    NULL::numeric(14,6) AS raw_score_welcome,
    NULL::numeric(14,6) AS raw_score_convert,
    w.raw_score,
    NULL::numeric(4,2) AS display_score_welcome,
    NULL::numeric(4,2) AS display_score_convert,
    w.display_score,
    w.last_wechat_touch_time,
    w.calc_time
FROM (
    SELECT DISTINCT ON (site_id, member_id) *
    FROM dws.dws_member_winback_index
    ORDER BY site_id, member_id, stat_date DESC
) w
UNION ALL
SELECT n.site_id, n.tenant_id, n.member_id,
    'NCI'::varchar(10) AS index_type,
    n.status, n.segment,
    n.member_create_time, n.first_visit_time, n.last_visit_time, n.last_recharge_time,
    n.t_v, n.t_r, n.t_a,
    n.visits_14d, n.visits_30d, n.visits_60d, n.visits_total,
    n.spend_30d, n.spend_180d, n.sv_balance, n.recharge_60d_amt,
    n.need_new, n.salvage_new, n.recharge_new, n.value_new, n.welcome_new,
    n.raw_score_welcome, n.raw_score_convert, n.raw_score,
    n.display_score_welcome, n.display_score_convert, n.display_score,
    n.last_wechat_touch_time,
    n.calc_time
FROM (
    SELECT DISTINCT ON (site_id, member_id) *
    FROM dws.dws_member_newconv_index
    ORDER BY site_id, member_id, stat_date DESC
) n;

COMMIT;

-- ============================================================
-- 回滚脚本（按需手动执行，不在 BEGIN/COMMIT 内）
-- ============================================================
-- 删除历史快照只保留最新
-- DELETE FROM dws.dws_member_assistant_relation_index WHERE stat_date != (SELECT MAX(stat_date) FROM dws.dws_member_assistant_relation_index);
-- DELETE FROM dws.dws_member_winback_index WHERE stat_date != (SELECT MAX(stat_date) FROM dws.dws_member_winback_index);
-- DELETE FROM dws.dws_member_newconv_index WHERE stat_date != (SELECT MAX(stat_date) FROM dws.dws_member_newconv_index);

-- 恢复原唯一约束
-- ALTER TABLE dws.dws_member_assistant_relation_index DROP CONSTRAINT uk_dws_member_assistant_relation_index;
-- ALTER TABLE dws.dws_member_assistant_relation_index ADD CONSTRAINT uk_dws_member_assistant_relation_index UNIQUE (site_id, member_id, assistant_id);
-- ALTER TABLE dws.dws_member_winback_index DROP CONSTRAINT uk_dws_member_winback;
-- ALTER TABLE dws.dws_member_winback_index ADD CONSTRAINT uk_dws_member_winback UNIQUE (site_id, member_id);
-- ALTER TABLE dws.dws_member_newconv_index DROP CONSTRAINT uk_dws_member_newconv;
-- ALTER TABLE dws.dws_member_newconv_index ADD CONSTRAINT uk_dws_member_newconv UNIQUE (site_id, member_id);

-- 删除 stat_date 列
-- ALTER TABLE dws.dws_member_assistant_relation_index DROP COLUMN stat_date;
-- ALTER TABLE dws.dws_member_winback_index DROP COLUMN stat_date;
-- ALTER TABLE dws.dws_member_newconv_index DROP COLUMN stat_date;

-- 恢复原视图（使用 gen_consolidated_ddl.py 重新生成）
