junhong_cmp_fiber/docs/traffic-model-unification/手工验收脚本.sql

-- 流量模型统一改造手工验收脚本
-- 用途：验证 package_usage 快照、统一公式和 B/C 端 contract 改造后的数据库基线。

-- 1. 查看 package_usage 快照字段是否存在
SELECT
    column_name,
    data_type,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'tb_package_usage'
  AND column_name IN (
      'data_limit_mb',
      'data_usage_mb',
      'virtual_total_mb_snapshot',
      'display_gain_ratio_snapshot',
      'enable_virtual_data_snapshot'
  )
ORDER BY column_name;

-- 2. 查看历史记录快照回填结果
SELECT
    pu.id AS package_usage_id,
    pu.package_id,
    pu.data_limit_mb,
    pu.data_usage_mb,
    pu.virtual_total_mb_snapshot,
    pu.display_gain_ratio_snapshot,
    pu.enable_virtual_data_snapshot,
    pu.status,
    pu.master_usage_id,
    pu.activated_at,
    pu.expires_at
FROM tb_package_usage pu
ORDER BY pu.id DESC
LIMIT 50;

-- 3. 统一公式核验：启用虚流量时的五字段与 reduction_pct
SELECT
    pu.id AS package_usage_id,
    pu.data_limit_mb AS real_total_mb,
    pu.data_usage_mb AS real_used_mb,
    CASE
        WHEN pu.enable_virtual_data_snapshot AND pu.virtual_total_mb_snapshot > 0 THEN pu.virtual_total_mb_snapshot
        ELSE pu.data_limit_mb
    END AS virtual_total_mb,
    CASE
        WHEN pu.enable_virtual_data_snapshot AND pu.virtual_total_mb_snapshot > 0
            THEN LEAST(pu.data_usage_mb * pu.display_gain_ratio_snapshot, pu.data_limit_mb)
        ELSE pu.data_usage_mb
    END AS virtual_used_mb,
    CASE
        WHEN pu.enable_virtual_data_snapshot AND pu.virtual_total_mb_snapshot > 0
            THEN pu.display_gain_ratio_snapshot - 1
        ELSE 0
    END AS reduction_pct
FROM tb_package_usage pu
ORDER BY pu.id DESC
LIMIT 50;

-- 4. 停机阈值核验：统一 effective_threshold_mb
SELECT
    pu.id AS package_usage_id,
    pu.data_limit_mb AS real_total_mb,
    pu.data_usage_mb AS real_used_mb,
    CASE
        WHEN pu.enable_virtual_data_snapshot AND pu.virtual_total_mb_snapshot > 0 THEN pu.virtual_total_mb_snapshot
        ELSE pu.data_limit_mb
    END AS effective_threshold_mb,
    CASE
        WHEN pu.data_usage_mb >= CASE
            WHEN pu.enable_virtual_data_snapshot AND pu.virtual_total_mb_snapshot > 0 THEN pu.virtual_total_mb_snapshot
            ELSE pu.data_limit_mb
        END THEN TRUE
        ELSE FALSE
    END AS is_depleted
FROM tb_package_usage pu
ORDER BY pu.id DESC
LIMIT 50;

-- 5. 样例一：100 / 70 / 50 -> 71.43
WITH sample AS (
    SELECT
        100::bigint AS real_total_mb,
        50::bigint AS real_used_mb,
        70::bigint AS virtual_total_mb,
        ROUND(100::numeric / 70::numeric, 6) AS display_gain_ratio
)
SELECT
    real_total_mb,
    real_used_mb,
    virtual_total_mb,
    display_gain_ratio,
    ROUND(LEAST(real_used_mb * display_gain_ratio, real_total_mb)::numeric, 2) AS expected_virtual_used_mb,
    ROUND((display_gain_ratio - 1)::numeric, 6) AS expected_reduction_pct
FROM sample;

-- 6. 样例二：100 / 70 / 69 -> 98.57
WITH sample AS (
    SELECT
        100::bigint AS real_total_mb,
        69::bigint AS real_used_mb,
        70::bigint AS virtual_total_mb,
        ROUND(100::numeric / 70::numeric, 6) AS display_gain_ratio
)
SELECT
    real_total_mb,
    real_used_mb,
    virtual_total_mb,
    display_gain_ratio,
    ROUND(LEAST(real_used_mb * display_gain_ratio, real_total_mb)::numeric, 2) AS expected_virtual_used_mb,
    ROUND((display_gain_ratio - 1)::numeric, 6) AS expected_reduction_pct
FROM sample;

-- 7. 样例三：未启用虚流量时应退化为真流量视图
WITH sample AS (
    SELECT
        100::bigint AS real_total_mb,
        69::bigint AS real_used_mb,
        FALSE AS enable_virtual_data_snapshot,
        100::bigint AS virtual_total_mb_snapshot,
        1.0::numeric AS display_gain_ratio_snapshot
)
SELECT
    real_total_mb,
    real_used_mb,
    CASE
        WHEN enable_virtual_data_snapshot AND virtual_total_mb_snapshot > 0 THEN virtual_total_mb_snapshot
        ELSE real_total_mb
    END AS expected_virtual_total_mb,
    CASE
        WHEN enable_virtual_data_snapshot AND virtual_total_mb_snapshot > 0 THEN LEAST(real_used_mb * display_gain_ratio_snapshot, real_total_mb)
        ELSE real_used_mb
    END AS expected_virtual_used_mb,
    CASE
        WHEN enable_virtual_data_snapshot AND virtual_total_mb_snapshot > 0 THEN display_gain_ratio_snapshot - 1
        ELSE 0
    END AS expected_reduction_pct
FROM sample;