-- ============================================================================
-- 渠道商维度迁移：授权码/批次/用户设备加 channelid，统计表 stats_global_day 把
-- channel_id 并进复合主键。
--
-- 适用库：
--   A. console 主库（Supabase / 平台公共库）—— brand / channel / production_batch /
--      license_* / stats_global_day 都在这里；
--   B. 各应用业务库 —— userdevice / user 两张表（渠道归属随绑定固化到用户身上）。
--   两段分别标了 [A] / [B]，按库执行对应段落即可（跑错库的段落会因表不存在而报错停下）。
--
-- 幂等：全部使用 IF NOT EXISTS / 条件判断，可重复执行。
-- 事务：整段一个事务；出错即回滚。
--
-- ⚠️ stats_global_day 改主键需要短暂停写窗口：执行前先停 console（统计消费端，唯一写库方），
--    执行完再起。业务侧 analyze 不写这张表，其累计在本地 Redis，窗口期不丢数据——
--    console 起来后由下一轮快照（绝对值覆盖）自动对账。
-- ============================================================================
\set ON_ERROR_STOP on
BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- [A] console 主库
-- ─────────────────────────────────────────────────────────────────────────────

-- A1. 渠道商表（与 pb.DBChannel 一致；console 启动时也会自动建，这里显式建保证顺序）
CREATE TABLE IF NOT EXISTS channel (
  id            varchar(16)  PRIMARY KEY,
  brandid       bigint       NOT NULL DEFAULT 0,
  name          text         NOT NULL DEFAULT '',
  contact       text         NOT NULL DEFAULT '',
  contactmails  text         NOT NULL DEFAULT '',
  remark        text         NOT NULL DEFAULT '',
  sharerate     integer      NOT NULL DEFAULT 0,
  status        integer      NOT NULL DEFAULT 0,
  applyaccount  text         NOT NULL DEFAULT '',
  auditaccount  text         NOT NULL DEFAULT '',
  auditremark   text         NOT NULL DEFAULT '',
  audittime     bigint       NOT NULL DEFAULT 0,
  createtime    bigint       NOT NULL DEFAULT 0,
  updatetime    bigint       NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_channel_brandid ON channel (brandid);
CREATE INDEX IF NOT EXISTS idx_channel_status  ON channel (status);

-- A2. 生产批次台账加渠道商列
ALTER TABLE production_batch ADD COLUMN IF NOT EXISTS channelid varchar(16) NOT NULL DEFAULT '';
CREATE INDEX IF NOT EXISTS idx_production_batch_channelid ON production_batch (channelid);

-- A3. 全部 license_* 授权码分表加渠道商列（含老的主表 license）
DO $do$
DECLARE t text;
BEGIN
  FOR t IN
    SELECT tablename FROM pg_tables
     WHERE schemaname = current_schema() AND (tablename = 'license' OR tablename LIKE 'license\_%')
  LOOP
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS channelid varchar(16) NOT NULL DEFAULT %L', t, '');
    EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I (channelid)', 'idx_' || t || '_channelid', t);
  END LOOP;
END
$do$;

-- A4. stats_global_day：加列 + 把 channel_id 并进复合主键
ALTER TABLE stats_global_day ADD COLUMN IF NOT EXISTS channel_id varchar(16) NOT NULL DEFAULT '';

-- 主键重建：老主键为 (app_id, product_id, region, stat_day)，新增 channel_id 后
-- 老行 channel_id 全为空串，(旧四列) 仍唯一，故重建不会冲突。
DO $do$
DECLARE
  pkname text;
  haschan boolean;
BEGIN
  SELECT c.conname,
         EXISTS (
           SELECT 1 FROM unnest(c.conkey) k
             JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k
            WHERE a.attname = 'channel_id')
    INTO pkname, haschan
    FROM pg_constraint c
   WHERE c.conrelid = 'stats_global_day'::regclass AND c.contype = 'p';

  IF pkname IS NULL THEN
    -- 没有主键（历史遗留）：直接建全维度主键。若此时已有重复行会报错，
    -- 需先人工按下面「重复行体检」清理再重跑。
    ALTER TABLE stats_global_day
      ADD CONSTRAINT stats_global_day_pkey PRIMARY KEY (app_id, product_id, channel_id, region, stat_day);
  ELSIF NOT haschan THEN
    EXECUTE format('ALTER TABLE stats_global_day DROP CONSTRAINT %I', pkname);
    ALTER TABLE stats_global_day
      ADD CONSTRAINT stats_global_day_pkey PRIMARY KEY (app_id, product_id, channel_id, region, stat_day);
  END IF;
END
$do$;

-- 渠道商下钻索引（按渠道商 + 日区间聚合）
CREATE INDEX IF NOT EXISTS idx_stats_channel_day ON stats_global_day (channel_id, stat_day);

-- A5. 品牌商分成比例（万分比）。渠道商比例在 channel.sharerate，建表时已含。
ALTER TABLE brand ADD COLUMN IF NOT EXISTS sharerate integer NOT NULL DEFAULT 0;

-- A6. 月结算单表（与 pb.DBSettlementMonth 一致；console 启动时也会自动建）
-- ⚠️【2026-09-02 已过时】结算维度改成 (period, channelid, batchno)，品牌商列已移除。
--    下面这段建的是旧形态，只对「回放这份历史迁移」有意义；新库不要照它建，
--    直接让 console 启动时的 ensureSettlementTable 建（它也会把旧形态就地迁移过去）。
CREATE TABLE IF NOT EXISTS settlement_month (
  period          bigint      NOT NULL,
  brandid         bigint      NOT NULL DEFAULT 0,
  channelid       varchar(16) NOT NULL DEFAULT '',
  order_count     bigint      NOT NULL DEFAULT 0,
  base_amount     bigint      NOT NULL DEFAULT 0,
  brand_rate      integer     NOT NULL DEFAULT 0,
  brand_gross     bigint      NOT NULL DEFAULT 0,
  channel_rate    integer     NOT NULL DEFAULT 0,
  channel_amount  bigint      NOT NULL DEFAULT 0,
  brand_net       bigint      NOT NULL DEFAULT 0,
  platform_amount bigint      NOT NULL DEFAULT 0,
  status          integer     NOT NULL DEFAULT 0,
  gen_time        bigint      NOT NULL DEFAULT 0,
  confirm_account text        NOT NULL DEFAULT '',
  confirm_time    bigint      NOT NULL DEFAULT 0,
  remark          text        NOT NULL DEFAULT '',
  PRIMARY KEY (period, brandid, channelid)
);
CREATE INDEX IF NOT EXISTS idx_settlement_status ON settlement_month (status);

-- A7. 后台账号的归属绑定列（品牌商账号 / 渠道商账号）。console 启动时也会自动补。
ALTER TABLE console_account ADD COLUMN IF NOT EXISTS brandid bigint DEFAULT 0;
ALTER TABLE console_account ADD COLUMN IF NOT EXISTS channelid varchar(16) DEFAULT '';

-- ─────────────────────────────────────────────────────────────────────────────
-- [B] 各应用业务库（userdevice.channelid / user.lastbindchannelid）
--
-- MySQL 业务库（常规情况）：**无需执行任何 SQL**——user 模块建表走
-- lego/sys/mysql 的 CreateTable，它对已存在的表也会 AutoMigrate，新列在服务
-- 启动时自动补上。
--
-- Postgres 业务库：lego/sys/postgres 的 CreateTable 对已存在表【跳过】
-- AutoMigrate，需要放开下面两段手工补列（只在业务库执行，别在 console 主库跑）。
-- ─────────────────────────────────────────────────────────────────────────────

-- ALTER TABLE userdevice ADD COLUMN IF NOT EXISTS channelid varchar(16) NOT NULL DEFAULT '';
-- CREATE INDEX IF NOT EXISTS idx_userdevice_channelid ON userdevice (channelid);
-- ALTER TABLE "user" ADD COLUMN IF NOT EXISTS lastbindchannelid varchar(16) NOT NULL DEFAULT '';
-- CREATE INDEX IF NOT EXISTS idx_user_lastbindchannelid ON "user" (lastbindchannelid);
--
-- 订单来源设备快照（月结算的归属依据，同样只有 Postgres 业务库需要手工补）：
-- ALTER TABLE payorder ADD COLUMN IF NOT EXISTS src_license varchar(32) NOT NULL DEFAULT '';
-- ALTER TABLE payorder ADD COLUMN IF NOT EXISTS src_mac varchar(50) NOT NULL DEFAULT '';
-- ALTER TABLE payorder ADD COLUMN IF NOT EXISTS src_productid bigint NOT NULL DEFAULT 0;
-- ALTER TABLE payorder ADD COLUMN IF NOT EXISTS src_brandid bigint NOT NULL DEFAULT 0;
-- ALTER TABLE payorder ADD COLUMN IF NOT EXISTS src_channelid varchar(16) NOT NULL DEFAULT '';
-- CREATE INDEX IF NOT EXISTS idx_payorder_src_channel ON payorder (src_channelid);
-- CREATE INDEX IF NOT EXISTS idx_payorder_src_brand ON payorder (src_brandid);

COMMIT;

-- ============================================================================
-- 体检 SQL（迁移后手工执行，非脚本的一部分）
--
-- 1) 确认主键已含 channel_id：
--    SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
--      FROM pg_index i JOIN pg_attribute a
--        ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
--     WHERE i.indrelid = 'stats_global_day'::regclass AND i.indisprimary;
--
-- 2) 重复行体检（主键建不上时先看这个）：
--    SELECT app_id, product_id, channel_id, region, stat_day, count(*)
--      FROM stats_global_day
--     GROUP BY 1,2,3,4,5 HAVING count(*) > 1;
--
-- 3) 授权码渠道回填进度（历史授权码 channelid 为空属正常，见 P5 回填脚本）：
--    SELECT channelid, count(*) FROM license_b001 GROUP BY 1 ORDER BY 2 DESC;
--
-- 4) 订单快照覆盖率（业务库执行；未迁移前生成的历史订单 src_* 为空，结算时归入「未归属」）：
--    SELECT COUNT(*) AS total,
--           COUNT(NULLIF(src_channelid,'')) AS with_channel,
--           COUNT(NULLIF(src_brandid,0))    AS with_brand
--      FROM payorder WHERE status = 1;
-- ============================================================================