junhong_cmp_fiber/migrations/000006_refactor_iot_models_architecture.up.sql

-- IoT 模型架构重构迁移脚本
-- 创建时间: 2026-01-12
-- 说明: 修改所有 IoT 相关表以符合项目架构规范
--       1. 表名改为 tb_ 前缀 + 单数形式
--       2. 添加软删除字段 deleted_at
--       3. 添加审计字段 creator, updater
--       4. 金额字段从 DECIMAL 改为 BIGINT（分为单位）
--       5. 更新唯一索引以支持软删除

-- ========================================
-- 阶段 1: 重命名表（复数 -> tb_ + 单数）
-- ========================================

ALTER TABLE carriers RENAME TO tb_carrier;
ALTER TABLE iot_cards RENAME TO tb_iot_card;
ALTER TABLE devices RENAME TO tb_device;
ALTER TABLE number_cards RENAME TO tb_number_card;
ALTER TABLE package_series RENAME TO tb_package_series;
ALTER TABLE packages RENAME TO tb_package;
ALTER TABLE agent_package_allocations RENAME TO tb_agent_package_allocation;
ALTER TABLE device_sim_bindings RENAME TO tb_device_sim_binding;
ALTER TABLE orders RENAME TO tb_order;
ALTER TABLE package_usages RENAME TO tb_package_usage;
ALTER TABLE polling_configs RENAME TO tb_polling_config;
ALTER TABLE data_usage_records RENAME TO tb_data_usage_record;
ALTER TABLE agent_hierarchies RENAME TO tb_agent_hierarchy;
ALTER TABLE commission_rules RENAME TO tb_commission_rule;
ALTER TABLE commission_ladder RENAME TO tb_commission_ladder;
ALTER TABLE commission_combined_conditions RENAME TO tb_commission_combined_condition;
ALTER TABLE commission_records RENAME TO tb_commission_record;
ALTER TABLE commission_approvals RENAME TO tb_commission_approval;
ALTER TABLE commission_templates RENAME TO tb_commission_template;
ALTER TABLE carrier_settlements RENAME TO tb_carrier_settlement;
ALTER TABLE commission_withdrawal_requests RENAME TO tb_commission_withdrawal_request;
ALTER TABLE commission_withdrawal_settings RENAME TO tb_commission_withdrawal_setting;
ALTER TABLE payment_merchant_settings RENAME TO tb_payment_merchant_setting;
ALTER TABLE dev_capability_configs RENAME TO tb_dev_capability_config;
ALTER TABLE card_replacement_requests RENAME TO tb_card_replacement_request;

-- ========================================
-- 阶段 2: 添加软删除字段和审计字段
-- ========================================

-- 2.1 运营商表
ALTER TABLE tb_carrier ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_carrier ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_carrier ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.2 IoT 卡表
ALTER TABLE tb_iot_card ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_iot_card ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_iot_card ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.3 设备表
ALTER TABLE tb_device ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_device ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_device ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.4 号卡表
ALTER TABLE tb_number_card ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_number_card ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_number_card ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.5 套餐系列表
ALTER TABLE tb_package_series ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_package_series ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_package_series ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.6 套餐表
ALTER TABLE tb_package ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_package ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_package ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.7 代理套餐分配表
ALTER TABLE tb_agent_package_allocation ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_agent_package_allocation ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_agent_package_allocation ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.8 设备-SIM绑定表
ALTER TABLE tb_device_sim_binding ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_device_sim_binding ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_device_sim_binding ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.9 订单表
ALTER TABLE tb_order ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_order ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_order ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.10 套餐使用表
ALTER TABLE tb_package_usage ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_package_usage ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_package_usage ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.11 轮询配置表
ALTER TABLE tb_polling_config ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_polling_config ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_polling_config ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 注意: tb_data_usage_record 是日志表，不需要软删除和审计字段

-- 2.12 代理层级表
ALTER TABLE tb_agent_hierarchy ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_agent_hierarchy ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_agent_hierarchy ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.13 分佣规则表
ALTER TABLE tb_commission_rule ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_rule ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_rule ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.14 阶梯分佣配置表
ALTER TABLE tb_commission_ladder ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_ladder ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_ladder ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.15 组合分佣条件表
ALTER TABLE tb_commission_combined_condition ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_combined_condition ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_combined_condition ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.16 分佣记录表
ALTER TABLE tb_commission_record ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_record ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_record ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.17 分佣审批表
ALTER TABLE tb_commission_approval ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_approval ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_approval ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.18 分佣模板表
ALTER TABLE tb_commission_template ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_template ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_template ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.19 运营商结算表
ALTER TABLE tb_carrier_settlement ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_carrier_settlement ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_carrier_settlement ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.20 佣金提现申请表
ALTER TABLE tb_commission_withdrawal_request ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_withdrawal_request ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_withdrawal_request ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.21 佣金提现设置表
ALTER TABLE tb_commission_withdrawal_setting ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_commission_withdrawal_setting ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_commission_withdrawal_setting ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.22 收款商户设置表
ALTER TABLE tb_payment_merchant_setting ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_payment_merchant_setting ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_payment_merchant_setting ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.23 开发能力配置表
ALTER TABLE tb_dev_capability_config ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_dev_capability_config ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_dev_capability_config ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- 2.24 换卡申请表
ALTER TABLE tb_card_replacement_request ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE tb_card_replacement_request ADD COLUMN creator BIGINT NOT NULL DEFAULT 0;
ALTER TABLE tb_card_replacement_request ADD COLUMN updater BIGINT NOT NULL DEFAULT 0;

-- ========================================
-- 阶段 3: 修改金额字段从 DECIMAL 改为 BIGINT（分为单位）
-- ========================================

-- 3.1 IoT 卡表：成本价、分销价（元 * 100 = 分）
ALTER TABLE tb_iot_card
    ALTER COLUMN cost_price TYPE BIGINT USING (cost_price * 100)::BIGINT,
    ALTER COLUMN distribute_price TYPE BIGINT USING (distribute_price * 100)::BIGINT;

COMMENT ON COLUMN tb_iot_card.cost_price IS '成本价(分为单位)';
COMMENT ON COLUMN tb_iot_card.distribute_price IS '分销价(分为单位)';

-- 3.2 号卡表：价格
ALTER TABLE tb_number_card
    ALTER COLUMN price TYPE BIGINT USING (price * 100)::BIGINT;

COMMENT ON COLUMN tb_number_card.price IS '价格(分为单位)';

-- 3.3 套餐表：价格
ALTER TABLE tb_package
    ALTER COLUMN price TYPE BIGINT USING (price * 100)::BIGINT;

COMMENT ON COLUMN tb_package.price IS '套餐价格(分为单位)';

-- 3.4 代理套餐分配表：成本价和零售价
ALTER TABLE tb_agent_package_allocation
    ALTER COLUMN cost_price TYPE BIGINT USING (cost_price * 100)::BIGINT,
    ALTER COLUMN retail_price TYPE BIGINT USING (retail_price * 100)::BIGINT;

COMMENT ON COLUMN tb_agent_package_allocation.cost_price IS '成本价(分为单位)';
COMMENT ON COLUMN tb_agent_package_allocation.retail_price IS '零售价(分为单位)';

-- 3.5 订单表：订单金额
ALTER TABLE tb_order
    ALTER COLUMN amount TYPE BIGINT USING (amount * 100)::BIGINT;

COMMENT ON COLUMN tb_order.amount IS '订单金额(分为单位)';

-- 3.6 分佣规则表：分佣值
ALTER TABLE tb_commission_rule
    ALTER COLUMN commission_value TYPE BIGINT USING (commission_value * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_rule.commission_value IS '分佣值(分为单位,百分比时为千分比如2000表示20%)';

-- 3.7 阶梯分佣配置表：分佣值
ALTER TABLE tb_commission_ladder
    ALTER COLUMN commission_value TYPE BIGINT USING (commission_value * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_ladder.commission_value IS '分佣值(分为单位,百分比时为千分比如2000表示20%)';

-- 3.8 组合分佣条件表：一次性分佣值、长期分佣值
ALTER TABLE tb_commission_combined_condition
    ALTER COLUMN one_time_commission_value TYPE BIGINT USING (one_time_commission_value * 100)::BIGINT,
    ALTER COLUMN long_term_commission_value TYPE BIGINT USING (long_term_commission_value * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_combined_condition.one_time_commission_value IS '一次性分佣值(分为单位,百分比时为千分比如2000表示20%)';
COMMENT ON COLUMN tb_commission_combined_condition.long_term_commission_value IS '长期分佣值(分为单位,百分比时为千分比如2000表示20%)';

-- 3.9 分佣记录表：分佣金额
ALTER TABLE tb_commission_record
    ALTER COLUMN amount TYPE BIGINT USING (amount * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_record.amount IS '分佣金额(分为单位)';

-- 3.10 分佣模板表：分佣值
ALTER TABLE tb_commission_template
    ALTER COLUMN commission_value TYPE BIGINT USING (commission_value * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_template.commission_value IS '分佣值(分为单位,百分比时为千分比如2000表示20%)';

-- 3.11 运营商结算表：结算金额
ALTER TABLE tb_carrier_settlement
    ALTER COLUMN settlement_amount TYPE BIGINT USING (settlement_amount * 100)::BIGINT;

COMMENT ON COLUMN tb_carrier_settlement.settlement_amount IS '结算金额(分为单位)';

-- 3.12 佣金提现申请表：提现金额、手续费、实际到账金额
ALTER TABLE tb_commission_withdrawal_request
    ALTER COLUMN amount TYPE BIGINT USING (amount * 100)::BIGINT,
    ALTER COLUMN fee TYPE BIGINT USING (fee * 100)::BIGINT,
    ALTER COLUMN actual_amount TYPE BIGINT USING (actual_amount * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_withdrawal_request.amount IS '提现金额(分为单位)';
COMMENT ON COLUMN tb_commission_withdrawal_request.fee IS '手续费(分为单位)';
COMMENT ON COLUMN tb_commission_withdrawal_request.actual_amount IS '实际到账金额(分为单位)';

-- 3.13 佣金提现设置表：最低提现金额
ALTER TABLE tb_commission_withdrawal_setting
    ALTER COLUMN min_withdrawal_amount TYPE BIGINT USING (min_withdrawal_amount * 100)::BIGINT;

COMMENT ON COLUMN tb_commission_withdrawal_setting.min_withdrawal_amount IS '最低提现金额(分为单位)';

-- ========================================
-- 阶段 4: 更新唯一索引以支持软删除
-- ========================================

-- 4.1 运营商表
ALTER TABLE tb_carrier DROP CONSTRAINT IF EXISTS carriers_carrier_code_key;
CREATE UNIQUE INDEX idx_carrier_code ON tb_carrier(carrier_code) WHERE deleted_at IS NULL;

-- 4.2 IoT 卡表
ALTER TABLE tb_iot_card DROP CONSTRAINT IF EXISTS iot_cards_iccid_key;
CREATE UNIQUE INDEX idx_iot_card_iccid ON tb_iot_card(iccid) WHERE deleted_at IS NULL;

-- 4.3 设备表
ALTER TABLE tb_device DROP CONSTRAINT IF EXISTS devices_device_no_key;
CREATE UNIQUE INDEX idx_device_no ON tb_device(device_no) WHERE deleted_at IS NULL;

-- 4.4 号卡表
ALTER TABLE tb_number_card DROP CONSTRAINT IF EXISTS number_cards_virtual_product_code_key;
CREATE UNIQUE INDEX idx_number_card_code ON tb_number_card(virtual_product_code) WHERE deleted_at IS NULL;

-- 4.5 套餐系列表
ALTER TABLE tb_package_series DROP CONSTRAINT IF EXISTS package_series_series_code_key;
CREATE UNIQUE INDEX idx_package_series_code ON tb_package_series(series_code) WHERE deleted_at IS NULL;

-- 4.6 套餐表
ALTER TABLE tb_package DROP CONSTRAINT IF EXISTS packages_package_code_key;
CREATE UNIQUE INDEX idx_package_code ON tb_package(package_code) WHERE deleted_at IS NULL;

-- 4.7 代理套餐分配表（复合唯一索引）
ALTER TABLE tb_agent_package_allocation DROP CONSTRAINT IF EXISTS uk_agent_package;
CREATE UNIQUE INDEX idx_agent_package_allocation_agent_package ON tb_agent_package_allocation(agent_id, package_id) WHERE deleted_at IS NULL;

-- 4.8 设备-SIM绑定表：暂时跳过，因为没有约束需要修改
-- 原始表使用条件唯一索引 idx_device_sim_bindings_active_card，不需要修改

-- 4.9 订单表
ALTER TABLE tb_order DROP CONSTRAINT IF EXISTS orders_order_no_key;
CREATE UNIQUE INDEX idx_order_no ON tb_order(order_no) WHERE deleted_at IS NULL;

-- 4.10 轮询配置表
ALTER TABLE tb_polling_config DROP CONSTRAINT IF EXISTS polling_configs_config_name_key;
CREATE UNIQUE INDEX idx_polling_config_name ON tb_polling_config(config_name) WHERE deleted_at IS NULL;

-- 4.11 代理层级表
ALTER TABLE tb_agent_hierarchy DROP CONSTRAINT IF EXISTS agent_hierarchies_agent_id_key;
CREATE UNIQUE INDEX idx_agent_hierarchy_agent ON tb_agent_hierarchy(agent_id) WHERE deleted_at IS NULL;

-- 4.12 组合分佣条件表
ALTER TABLE tb_commission_combined_condition DROP CONSTRAINT IF EXISTS commission_combined_conditions_rule_id_key;
CREATE UNIQUE INDEX idx_commission_combined_rule ON tb_commission_combined_condition(rule_id) WHERE deleted_at IS NULL;

-- 4.13 分佣模板表
ALTER TABLE tb_commission_template DROP CONSTRAINT IF EXISTS commission_templates_template_name_key;
CREATE UNIQUE INDEX idx_commission_template_name ON tb_commission_template(template_name) WHERE deleted_at IS NULL;

-- 4.14 运营商结算表
ALTER TABLE tb_carrier_settlement DROP CONSTRAINT IF EXISTS carrier_settlements_commission_record_id_key;
CREATE UNIQUE INDEX idx_carrier_settlement_record ON tb_carrier_settlement(commission_record_id) WHERE deleted_at IS NULL;

-- 4.15 开发能力配置表
ALTER TABLE tb_dev_capability_config DROP CONSTRAINT IF EXISTS dev_capability_configs_app_id_key;
CREATE UNIQUE INDEX idx_dev_capability_app ON tb_dev_capability_config(app_id) WHERE deleted_at IS NULL;

-- ========================================
-- 阶段 5: 更新表注释
-- ========================================

COMMENT ON TABLE tb_carrier IS '运营商表';
COMMENT ON TABLE tb_iot_card IS 'IoT卡表(物联网卡/流量卡)';
COMMENT ON TABLE tb_device IS '设备表(可容纳1-4张SIM卡)';
COMMENT ON TABLE tb_number_card IS '号卡表(虚拟商品)';
COMMENT ON TABLE tb_package_series IS '套餐系列表';
COMMENT ON TABLE tb_package IS '套餐表';
COMMENT ON TABLE tb_agent_package_allocation IS '代理套餐分配表';
COMMENT ON TABLE tb_device_sim_binding IS '设备-SIM卡绑定表';
COMMENT ON TABLE tb_order IS '订单表';
COMMENT ON TABLE tb_package_usage IS '套餐使用表';
COMMENT ON TABLE tb_polling_config IS '轮询配置表';
COMMENT ON TABLE tb_data_usage_record IS '流量使用记录表';
COMMENT ON TABLE tb_agent_hierarchy IS '代理层级关系表';
COMMENT ON TABLE tb_commission_rule IS '分佣规则表';
COMMENT ON TABLE tb_commission_ladder IS '阶梯分佣配置表';
COMMENT ON TABLE tb_commission_combined_condition IS '组合分佣条件表';
COMMENT ON TABLE tb_commission_record IS '分佣记录表';
COMMENT ON TABLE tb_commission_approval IS '分佣审批表';
COMMENT ON TABLE tb_commission_template IS '分佣模板表';
COMMENT ON TABLE tb_carrier_settlement IS '号卡运营商结算表';
COMMENT ON TABLE tb_commission_withdrawal_request IS '佣金提现申请表';
COMMENT ON TABLE tb_commission_withdrawal_setting IS '佣金提现设置表';
COMMENT ON TABLE tb_payment_merchant_setting IS '收款商户设置表';
COMMENT ON TABLE tb_dev_capability_config IS '开发能力配置表';
COMMENT ON TABLE tb_card_replacement_request IS '换卡申请表';