junhong_cmp_fiber/scripts/migration/README.md

# 奇成数据迁移脚本

> 详细方案见 [`docs/脚本/奇成数据迁移方案.md`](../../docs/脚本/奇成数据迁移方案.md)。

## 1. 两条执行路径

```
业务方           我们                                  运维
  │              │                                     │
  │ cards.csv    │ scan_legacy.py → mapping.yaml      │
  │ devices.csv? │   ↓ 人工填 target_*                │
  ├─────────────▶│ migrate_assets.py → step1_*.sql    │
  │              │ migrate_runtime.py → step2_*.sql   │
  │              ├────────────────────────────────────▶│ psql -1 -f step*.sql
  │              │                                     │ migration-finalize
  │              │                                     │ 重建轮询队列/缓存
```

`devices.csv` 是可选文件:

- **只有卡**:只准备 `cards.csv`,不要放 `devices.csv`;脚本按独立卡迁移,不会生成设备绑定关系。
- **卡 + 设备**:同时准备 `cards.csv` 和 `devices.csv`;设备上的卡跟随设备归属,设备套餐挂到设备资产,旧套餐数据从 `package_source_slot` 对应绑定卡读取。

## 2. 文件清单

```
scripts/migration/
├── README.md
├── requirements.txt
├── scan_legacy.py             ← 第1步:扫描奇成,生成 mapping.yaml 模板
├── migrate_assets.py          ← 第2步:生成资产 SQL
├── migrate_runtime.py         ← 第3步:生成关联数据 SQL
├── lib/                       ← 公共库
├── resources/
│   ├── README.md
│   ├── cards.csv.example      ← 业务方提供:ICCID + is_industry
│   └── devices.csv.example    ← 可选:设备 + 卡槽 + 可选槽位覆盖
├── config/
│   ├── mapping.yaml.example   ← 唯一决策配置(归属/槽位/套餐 + carrier/package/agent)
│   └── legacy_dsn.yaml.example← 奇成 MySQL 只读 DSN
└── output/                    ← 生成的 SQL 与日志
```

## 3. 详细流程

### 3.1 业务方提供数据

把 CSV 放到 `resources/` 下:

- **cards.csv**:`iccid_19`/`iccid_20` 至少填一个;仅填 `iccid_20` 时脚本自动派生前 19 位,但奇成主表只按 20 位精确查询;`is_industry` 可选
- **devices.csv** 可选。只有卡时不要放这个文件;有设备时再提供:`imei` / `virtual_no` 至少填一个;`device_name` / `device_model` / `device_type` / `max_sim_slots` 可选;`sim_iccid_*` 引用 `cards.csv` 中的 ICCID;`current_slot` / `package_source_slot` 可选,空值由 `mapping.yaml` 批量规则填充

字段说明详见 [`resources/README.md`](resources/README.md)。

### 3.2 扫描 + 填映射

```bash
pip install -r requirements.txt
cp config/legacy_dsn.yaml.example config/legacy_dsn.yaml  # 填真实 DSN
cp config/mapping.yaml.example config/mapping.yaml        # 第一次跑要有这个文件

# 扫描奇成,把发现的 carrier/package/agent 写入 mapping.yaml
python3 scan_legacy.py

# 看 config/mapping_todo.txt,把 target_* 全部填好
vim config/mapping.yaml
```

`scan_legacy.py` 可以反复跑(增量合并),已填的 `target_*` 不会被覆盖。写回的 `mapping.yaml`
会带中文注释,说明哪些字段来自奇成、哪些字段需要人工填写。

### 3.3 只有卡的执行顺序

只有卡时,`resources/` 下只放 `cards.csv`,不要放 `devices.csv`。

```bash
python3 scan_legacy.py
# 填 config/mapping.yaml:
#   - ownership_rules.standalone_card.mode
#   - ownership_rules.default_target_shop_code (mode=default_shop 时必填)
#   - agents[].target_shop_code (mode=legacy_agent 时按奇成代理分店铺)
#   - carriers[].target_*
#   - packages[].target_package_id

python3 migrate_assets.py
```

先审核:

- `output/ownership_resolution.csv`:只会有独立卡归属决策。
- `output/errors.csv`:非空时不要执行 SQL。
- `output/summary.txt`:确认独立卡数量、分配卡数。

再按编号执行 step1。纯卡迁移时 `step1_02_devices.sql` 和 `step1_05_sim_bindings.sql`
只包含文件头或空操作,可以执行也可以跳过;为了减少漏执行,建议仍按编号执行:

```bash
psql -h $HOST -1 -f output/step1_01_iot_cards.sql
psql -h $HOST -1 -f output/step1_02_devices.sql
psql -h $HOST -1 -f output/step1_03_asset_identifiers.sql
psql -h $HOST -1 -f output/step1_04_asset_wallets.sql
psql -h $HOST -1 -f output/step1_05_sim_bindings.sql
psql -h $HOST -1 -f output/step1_06_shop_allocations.sql
```

step1 执行完成后再生成并执行 step2:

```bash
python3 migrate_runtime.py
```

审核 `package_resolution.csv` / `errors.csv` / `warnings.csv` / `summary.txt` 后,按编号执行:

```bash
psql -h $HOST -1 -f output/step2_01_migration_orders.sql
psql -h $HOST -1 -f output/step2_02_package_usages.sql
psql -h $HOST -1 -f output/step2_03_card_data_usage_update.sql
psql -h $HOST -1 -f output/step2_04_agent_wallet_init.sql
psql -h $HOST -1 -f output/step2_05_agent_wallet_transactions.sql
psql -h $HOST -1 -f output/step2_06_asset_series_update.sql
```

### 3.4 卡 + 设备的执行顺序

有设备时,同时准备 `cards.csv` 和 `devices.csv`。所有设备槽位里引用的 ICCID 必须存在于
`cards.csv`。

```bash
python3 scan_legacy.py
# 填 config/mapping.yaml:
#   - ownership_rules.device.mode/current_slot/package_source_slot
#   - ownership_rules.standalone_card.mode
#   - agents[].target_shop_code (mode=legacy_agent 时按奇成代理分店铺)
#   - overrides.devices/cards (如有特殊设备或特殊卡)
#   - carriers[].target_*
#   - packages[].target_package_id

python3 migrate_assets.py
```

先审核:

- `ownership_resolution.csv`:核对每台设备归属、当前槽位、旧套餐读取槽位;核对独立卡归属。
- `errors.csv`:非空时不要执行 SQL。
- `summary.txt`:确认卡数、设备数、分配数量。

再执行 step1,然后生成 step2,审核 `package_resolution.csv`,最后执行 step2。命令顺序与
“只有卡”相同。

### 3.5 生成 SQL 的审核要求

```bash
python3 migrate_assets.py   # → output/step1_*.sql + ownership_resolution.csv + errors.csv
python3 migrate_runtime.py  # → output/step2_*.sql + package_resolution.csv + errors.csv + warnings.csv
```

执行 SQL 前必须先审核:

- `ownership_resolution.csv`:核对每个设备/独立卡的目标店铺、决策来源、当前槽位和旧套餐读取槽位
- `package_resolution.csv`:核对 active/pending 套餐、目标套餐、优先级、跳过原因和阻断原因
- `summary.txt`:核对分配数、active/pending 套餐数、跳过数、错误数
- `errors.csv`:非空时不执行 SQL,先修正配置或输入后重跑

### 3.6 目标库执行

```bash
# 按编号顺序执行
psql -h $HOST -1 -f output/step1_01_iot_cards.sql
psql -h $HOST -1 -f output/step1_02_devices.sql
psql -h $HOST -1 -f output/step1_03_asset_identifiers.sql
psql -h $HOST -1 -f output/step1_04_asset_wallets.sql
psql -h $HOST -1 -f output/step1_05_sim_bindings.sql
psql -h $HOST -1 -f output/step1_06_shop_allocations.sql

psql -h $HOST -1 -f output/step2_01_migration_orders.sql
psql -h $HOST -1 -f output/step2_02_package_usages.sql
psql -h $HOST -1 -f output/step2_03_card_data_usage_update.sql
psql -h $HOST -1 -f output/step2_04_agent_wallet_init.sql
psql -h $HOST -1 -f output/step2_05_agent_wallet_transactions.sql
psql -h $HOST -1 -f output/step2_06_asset_series_update.sql
```

`-1` = 单事务,失败回滚整文件;`ON CONFLICT DO NOTHING` 保证重跑安全。

SQL 导入完成后必须按批次执行迁移收尾。SQL 只负责落库,不会触发 Service 生命周期,
也不会自动写入 Redis 轮询队列或卡缓存:

```bash
# 先看本批次会处理多少卡、会写哪些轮询任务,不会写 Redis
go run ./cmd/migration-finalize --batch-no MIGRATION-QICHENG-YYYYMMDD

# dry-run 无误后再执行,会按 tb_polling_config 重建本批卡的轮询队列和卡缓存
go run ./cmd/migration-finalize --batch-no MIGRATION-QICHENG-YYYYMMDD --apply
```

## 4. 配置说明 (`mapping.yaml`)

唯一需要人工填写的文件:

```yaml
migration_user_id: 0                         # 后台超级管理员 ID,作为所有 creator/updater
migration_batch_no: "MIGRATION-QICHENG-YYYYMMDD" # 本次迁移批次号,线上每批必须唯一

ownership_rules:
  default_target_shop_code: KWTX       # default_shop 模式的批量目标店铺;legacy_agent/none 可为 null
  device:
    mode: none                         # default_shop / legacy_agent / none;只有卡时保持 none
    current_slot: null                 # 设备当前卡槽,写 is_current;只有卡时 null
    package_source_slot: null          # 设备旧套餐读取槽位;只有卡时 null
  standalone_card:
    mode: legacy_agent                 # default_shop / legacy_agent / none

package_rules:
  migrate_statuses: [active, pending]  # 迁移当前生效 + 未生效待生效正式套餐

overrides:
  devices:
    - virtual_no: "862639073940258"
      target_shop_code: OTHER
      current_slot: 1
      package_source_slot: 1
  cards:
    - iccid: "89861590172420360956"
      target_shop_code: OTHER

carriers:              # 由 scan_legacy 自动发现 legacy_*,你只填 target_*
  - legacy_account_id: "72493DB64A2A4F01B5E59E49D58A2A34"
    legacy_account_name: "联通10-1"
    legacy_category: 9                 # 奇成运营商大类,仅作辅助判断
    legacy_category_name: "中国联通"
    target_carrier_id: 1585       ← 必填:新库 tb_carrier.id
    target_carrier_type: CUCC     ← 必填:CMCC/CUCC/CTCC/CBN
    target_carrier_name: "联通22-..."

packages:              # 同理
  - legacy_meal_id: "D6554244711498752"
    legacy_meal_name: "三网充电宝全国畅玩年卡套餐..."
    target_package_id: 2          ← 必填:新库 tb_package.id

series:                # 套餐系列兜底映射;有 target_package_id 时优先用 tb_package.series_id
  - legacy_series_id: "..."
    legacy_series_name: "国内卡 无预存30天"
    target_series_id: 5          ← 可选:无当前套餐时作为资产 series_id 兜底

agents:                # legacy_agent 归属模式 + 代理钱包初始化
  - legacy_agent_id: "D15EC779767B412A82B8448AC2324F04"
    legacy_agent_name: "叶发伟"
    target_shop_code: "SHOP20260428103424CZZQ"  # legacy_agent 模式下 null=该代理资产进平台库存
```

### 店铺归属是怎么决定的

CSV 里**没有** `target_shop_code` 列。资产归属由 `mapping.yaml` 显式决定:

| 资产 | 归属规则 |
|------|---------|
| 独立卡 | `overrides.cards` 优先,否则 `ownership_rules.standalone_card` |
| 设备 | `overrides.devices` 优先,否则 `ownership_rules.device` |
| 设备上的卡 | 跟随设备所属店铺(`shop_id` 与设备一致,设备分配时卡状态同步为已分销) |

`ownership_rules.*.mode` 有三种:

| mode | 含义 | 适用场景 |
|------|------|----------|
| `default_shop` | 全部分到 `default_target_shop_code` | 单店铺批次 |
| `legacy_agent` | 按奇成 `tbl_card.agent_id` 查 `agents[].target_shop_code` | 一批卡分散到多个店铺 |
| `none` | 不分配店铺,进入平台库存 | 先入库后分配 |

一万张卡分散到多个店铺时,不要写一万条 `overrides.cards`。做法是:

```yaml
ownership_rules:
  default_target_shop_code: null
  standalone_card:
    mode: legacy_agent

agents:
  - legacy_agent_id: "0072250EBB88422C88A573AB06870D17"
    legacy_agent_name: "酷蛙通讯"
    target_shop_code: "KWTX"
  - legacy_agent_id: "..."
    legacy_agent_name: "另一个代理"
    target_shop_code: "SHOP_A"
```

这样脚本会按每张卡在奇成 `tbl_card.agent_id` 自动分流到不同店铺。设备批次如果也要按代理分店铺,设置
`ownership_rules.device.mode: legacy_agent`,脚本会用设备 `package_source_slot` 那张绑定卡的 `agent_id`
决定设备归属。

设备槽位解析优先级是 `devices.csv` 行级配置 > `overrides.devices` > `ownership_rules.device`。`current_slot` 是默认当前卡槽,对应 `tb_device_sim_binding.is_current`;`package_source_slot` 是默认旧套餐读取卡槽,只决定从哪张绑定卡读取奇成旧套餐,新系统套餐仍写到设备资产。若 CSV 行级和配置都不填 `package_source_slot`,会回退到 `current_slot`。

## 5. 识别迁移数据(后台报表过滤用)

| 数据 | 识别条件 |
|------|---------|
| 迁移伪订单 | `WHERE order_no LIKE 'MIG-%'` |
| 迁移分配记录 | `WHERE allocation_no LIKE 'MIG-ALLOC-%'` |
| 迁移钱包流水 | `WHERE metadata->>'source' = 'qicheng'` |

迁移伪订单号固定为 `MIG-<ICCID>-<priority>`:

- 20 位卡加优先级总长小于 30,符合新库 `order_no varchar(30)` 限制。
- 订单号不包含生成时间,`step2_01_migration_orders.sql` 和 `step2_02_package_usages.sql` 分开重跑也能稳定匹配。

## 6. 流量用量迁移规则(真用量 / 虚用量)

奇成与新系统的"虚流量"模型口径不同,必须做一次反算才能落对地方。

### 6.1 两边的口径差异

| | 奇成 | 新系统 |
|---|---|---|
| 用量存储 | `tbl_card.total_bytes_cnt`(varchar 小数 MB)= **虚用量**(真 × `(1 + flow_add_discount/100)`) | `tb_iot_card.data_usage_mb`(BIGINT MB)= **真用量**(网关返回的物理读数) |
| 加成配置 | `tbl_set_meal.flow_add_discount`(0~100 百分比,作用于"已用量") | `tb_package.enable_virtual_data` + `virtual_data_mb` + `virtual_ratio`(`= real_data_mb / virtual_data_mb`) |
| 用户看到的"已用" | = 数据库 `total_bytes_cnt`(已加成) | 前端用 `data_usage_mb × display_gain_ratio_snapshot` 现算 |

### 6.2 反算公式

迁移脚本对每张卡按下式得到真用量,再写入 `data_usage_mb` / `current_month_usage_mb` / `last_gateway_reading_mb`:

```
real_used_mb = total_bytes_cnt / (1 + flow_add_discount / 100)
```

其中 `flow_add_discount` 取自该卡在 `tbl_card_life`(status=1, type!=1, expire_date 最大)对应的当前生效正式套餐。奇成 `type=1` 是叠加包/加油包,不能作为新系统主套餐迁入。

### 6.3 精度与近似

- **跨多个套餐周期的累计**:`total_bytes_cnt` 是按各时段对应套餐虚比加权累加的,这里只用"当前生效正式套餐"的虚比反算,**有误差但可接受**(奇成没有按套餐分段的用量明细,无更好办法)。
- **无当前生效正式套餐却有 `total_bytes_cnt`**:按 `flow_add_discount=0` 处理(真=虚),并写入 `warnings.csv`(`kind=usage_without_active_package`)供人工核查。
- **整数取整规则**(`data_usage_mb` 是 BIGINT):
  - `real_used_mb = 0` → 写 0
  - `0 < real_used_mb < 1` → **上取整为 1**,避免"明明用过的卡显示 0"
  - `real_used_mb ≥ 1` → 向下取整
- **小数字段保留精度**:`current_month_usage_mb`(numeric)与 `last_gateway_reading_mb`(double)直接写 Decimal 真值,作为下次轮询增量计算的基线,**避免轮询第一次回写时产生跳变**。

### 6.4 套餐使用记录的虚流量快照

`tb_package_usage` 的 `virtual_total_mb_snapshot` / `display_gain_ratio_snapshot` / `enable_virtual_data_snapshot` 三个字段**全部从新系统 `tb_package` 当场快照**,与奇成无关。迁移脚本只允许 `target_package_id` 指向新系统 `package_type='formal'` 的套餐;如果误指向 `addon` 加油包,生成的 step2 SQL 会直接报错并回滚:

| 快照字段 | 取值 |
|---|---|
| `virtual_total_mb_snapshot` | 启用虚:`virtual_data_mb`;否则:`real_data_mb` |
| `display_gain_ratio_snapshot` | 启用虚:`virtual_ratio`(即 `real_data_mb/virtual_data_mb`);否则:`1.0` |
| `enable_virtual_data_snapshot` | 启用虚:`TRUE`;否则:`FALSE` |
| `data_limit_mb` | 始终 `real_data_mb`(真总量) |
| `data_usage_mb` | `LEAST(real_used_mb_floor, virtual_total_mb_snapshot)`(防卫上限) |

### 6.5 当前生效 + 待生效套餐

脚本读取奇成 `tbl_card_life` 当前/历史正式套餐和 `tbl_next_month_card_life` 次月待生效套餐:

- `type=1` 加油包不迁移,写入 `package_resolution.csv` 跳过原因
- 已过期套餐不迁移,写入跳过原因
- active 写 `tb_package_usage.status=1`,保留 `activated_at` / `expires_at` 和用量快照
- pending 写 `tb_package_usage.status=0`,保留 `expires_at`,用量为 0,按 `start_date ASC, expire_date ASC, legacy_life_id ASC` 生成稳定 `priority`
- 同一资产出现多个 active 正式套餐时写入 `errors.csv` 并阻断该资产套餐 SQL
- legacy 套餐未映射或目标套餐缺失时写入 `errors.csv` 并阻断对应套餐 SQL

### 6.6 设备绑定卡的套餐归属

设备上的卡在新系统运行态会按设备维度判断有效套餐和复机条件，因此迁移时只用设备 `package_source_slot` 对应绑定卡读取奇成旧套餐生命周期。生成的新系统套餐使用记录仍然是 `usage_type=device`、`device_id=设备ID`,不会挂到这张卡资产上。其他槽位绑定卡不读取旧套餐,避免同一设备重复迁入多份主套餐。

设备和卡的 `series_id` 以新系统目标套餐为准：脚本优先通过 `mapping.packages[].target_package_id` 对应的 `tb_package.series_id` 写入资产。若旧卡没有当前正式套餐、但本批 `series[].target_series_id` 只配置了一个目标系列,脚本会把它作为资产系列兜底。`step1` 对已存在资产也会更新 `series_id`;`step2_06_asset_series_update.sql` 继续用于按已生成套餐使用记录回填系列。

## 7. 重要约束

- ❗ 所有奇成查询走 `SET TRANSACTION READ ONLY`,严禁误写。
- ❗ 所有 INSERT 用 `ON CONFLICT DO NOTHING`,严禁更新已有数据。
- ❗ 目标店铺和目标套餐都有 SQL 前置校验,缺失时直接报错并回滚当前 SQL 文件。
- ❗ `errors.csv` 非空时必须先修复后重跑,不要执行已生成 SQL。
- ❗ 伪订单的 `payment_status=2 commission_status=2 commission_result=2`,避免被支付回调、佣金调度器扫到。
- ❗ 迁移卡默认 `status=1(在库)` / `activation_status=0(未激活)`,激活由后续业务流程驱动。

## 8. 常见问题

| 现象 | 排查 |
|------|------|
| `scan_legacy.py` 报 `pymysql 缺失` | `pip install -r requirements.txt` |
| `errors.csv` 出现 `carrier_not_in_mapping` | `mapping.yaml.carriers` 没覆盖到这个 account_id/account_name,重跑 `scan_legacy.py` 自动补 |
| `errors.csv` 出现 `carrier_target_missing` | `mapping.yaml.carriers` 里有这一项但 `target_*` 还是 null,手动填 |
| `errors.csv` 出现 `not_in_legacy` | 该 ICCID 在奇成 `tbl_card` 查不到,业务方确认 |
| `errors.csv` 出现 `legacy_duplicate` | 同一 ICCID 在奇成命中多条 `tbl_card`,业务方人工确认是哪张物理卡 |
| `errors.csv` 出现 `current_slot_empty` | `current_slot` 指向的设备槽位无卡,修正 devices.csv 或 mapping.yaml |
| `errors.csv` 出现 `package_source_slot_empty` | `package_source_slot` 指向的设备槽位无卡,修正后重跑 |
| `errors.csv` 出现 `package_not_in_mapping` | 可迁移 legacy 套餐未配置 `target_package_id`,补齐 mapping.yaml |
| `errors.csv` 出现 `multiple_active_packages` | 同一资产多个 active 主套餐,需人工裁决后用配置或数据修正 |
| `errors.csv` 出现 `carrier_length_conflict` | 业务方填的 `iccid_20` 与奇成查到的运营商不匹配(CTCC 不该有 20 位 / 非 CTCC 必须有 20 位) |
| `errors.csv` 出现 `iccid_20_prefix_mismatch` | `iccid_20` 前 19 位 ≠ `iccid_19`,业务方校对 |
| `warnings.csv` 出现 `agent_no_legacy` | `mapping.agents` 配了但奇成没有 tbl_agent 记录,业务方确认 |
| `warnings.csv` 出现 `usage_without_active_package` | 该卡奇成 `total_bytes_cnt > 0` 但 `tbl_card_life` 中没有 status=1 且 type!=1 的当前生效正式套餐,无法精确反算真用量(按真=虚处理),详见 §6.3 |
| 线上报 `null value in column "creator"` | `migration_user_id` 是 0,某些约束可能拒绝;改成真实管理员 ID |
| 线上报目标店铺不存在 | `ownership_rules` 或 `overrides` 的 shop_code 在新库不存在,先建店铺或修正配置 |