-- 目的：支持上游 IoT 平台传入 19/20 位 ICCID 均能精确命中卡记录
-- 背景：部分运营商 19 位为唯一标识，部分运营商需要完整 20 位才能唯一标识一张卡
-- 策略：双列存储 + 按上游传入长度路由查询，Miss 时记录 Warn 日志不降级

-- 注意：golang-migrate 默认在事务中运行，CREATE INDEX CONCURRENTLY 不能在事务中执行
-- 因此使用 CREATE INDEX IF NOT EXISTS（非 CONCURRENTLY）
-- 生产环境若需零停机建索引，可手动执行 CONCURRENTLY 版本

-- ==========================================================================
-- 1. tb_iot_card 新增双列
-- ==========================================================================

ALTER TABLE tb_iot_card
    ADD COLUMN IF NOT EXISTS iccid_19 varchar(19),
    ADD COLUMN IF NOT EXISTS iccid_20 varchar(20);

COMMENT ON COLUMN tb_iot_card.iccid_19 IS 'ICCID前19位（所有卡必填，用于19位运营商精确查询）';
COMMENT ON COLUMN tb_iot_card.iccid_20 IS '完整20位ICCID（仅20位运营商卡有值，19位卡为NULL）';

-- ==========================================================================
-- 1.1a 冲突检测（执行后人工确认均为 0 行，否则停止迁移人工处理）
-- 检测 19 位前缀重复：若 19 位前缀不唯一，回填后 iccid_19 无法作为唯一索引列
-- ==========================================================================

DO $$
DECLARE
    conflict_count INTEGER;
    abnormal_count INTEGER;
BEGIN
    -- 检测 19 位前缀重复
    SELECT COUNT(*) INTO conflict_count
    FROM (
        SELECT LEFT(iccid, 19)
        FROM tb_iot_card
        WHERE deleted_at IS NULL
        GROUP BY LEFT(iccid, 19)
        HAVING COUNT(*) > 1
    ) t;

    IF conflict_count > 0 THEN
        RAISE EXCEPTION '冲突检测失败：发现 % 组 19 位前缀重复的 ICCID，请人工处理后再执行迁移', conflict_count;
    END IF;

    -- 检测异常长度 ICCID（长度既非 19 也非 20 的记录）
    SELECT COUNT(*) INTO abnormal_count
    FROM tb_iot_card
    WHERE deleted_at IS NULL
      AND LENGTH(iccid) NOT IN (19, 20);

    IF abnormal_count > 0 THEN
        RAISE WARNING '警告：发现 % 条异常长度 ICCID 记录（长度非19也非20），回填后这些卡将无法通过新查询路径命中', abnormal_count;
    END IF;
END $$;

-- ==========================================================================
-- 1.2 回填存量数据
-- iccid_19 = LEFT(iccid, 19)（所有卡）
-- iccid_20 = iccid（仅当 LENGTH(iccid)=20，否则 NULL）
-- ==========================================================================

UPDATE tb_iot_card
SET iccid_19 = LEFT(iccid, 19),
    iccid_20 = CASE WHEN LENGTH(iccid) = 20 THEN iccid ELSE NULL END;

-- ==========================================================================
-- 1.3 为 iccid_19 创建 Partial Index（过滤软删除记录）
-- ==========================================================================

CREATE INDEX IF NOT EXISTS idx_iot_card_iccid_19
    ON tb_iot_card (iccid_19)
    WHERE deleted_at IS NULL;

-- ==========================================================================
-- 1.4 为 iccid_20 创建 Partial Index（过滤软删除和 NULL 记录）
-- ==========================================================================

CREATE INDEX IF NOT EXISTS idx_iot_card_iccid_20
    ON tb_iot_card (iccid_20)
    WHERE deleted_at IS NULL AND iccid_20 IS NOT NULL;

-- ==========================================================================
-- 1.5 tb_personal_customer_iccid 新增 iccid_19 列
-- ==========================================================================

ALTER TABLE tb_personal_customer_iccid
    ADD COLUMN IF NOT EXISTS iccid_19 varchar(19);

COMMENT ON COLUMN tb_personal_customer_iccid.iccid_19 IS 'ICCID前19位（用于19位运营商精确查询）';

-- 回填存量数据
UPDATE tb_personal_customer_iccid
SET iccid_19 = LEFT(iccid, 19);

-- 创建对应 Partial Index
CREATE INDEX IF NOT EXISTS idx_personal_customer_iccid_19
    ON tb_personal_customer_iccid (iccid_19)
    WHERE deleted_at IS NULL;