-- 客户端接口数据模型基础准备
-- 提案: client-api-data-model-fixes
-- 包含: 资产状态、世代编号、订单来源、操作人类型、实名链接配置、代理零售价、索引变更

-- 1. tb_iot_card: 新增 asset_status 和 generation
ALTER TABLE tb_iot_card ADD COLUMN asset_status int NOT NULL DEFAULT 1;
ALTER TABLE tb_iot_card ADD COLUMN generation int NOT NULL DEFAULT 1;
COMMENT ON COLUMN tb_iot_card.asset_status IS '业务状态 1-在库 2-已销售 3-已换货 4-已停用';
COMMENT ON COLUMN tb_iot_card.generation IS '资产世代编号';

-- 2. tb_device: 新增 asset_status 和 generation
ALTER TABLE tb_device ADD COLUMN asset_status int NOT NULL DEFAULT 1;
ALTER TABLE tb_device ADD COLUMN generation int NOT NULL DEFAULT 1;
COMMENT ON COLUMN tb_device.asset_status IS '业务状态 1-在库 2-已销售 3-已换货 4-已停用';
COMMENT ON COLUMN tb_device.generation IS '资产世代编号';

-- 3. tb_order: 新增 source 和 generation
ALTER TABLE tb_order ADD COLUMN source varchar(20) NOT NULL DEFAULT 'admin';
ALTER TABLE tb_order ADD COLUMN generation int NOT NULL DEFAULT 1;
COMMENT ON COLUMN tb_order.source IS '订单来源 admin-后台 client-客户端';
COMMENT ON COLUMN tb_order.generation IS '资产世代编号';

-- 4. tb_package_usage: 新增 generation
ALTER TABLE tb_package_usage ADD COLUMN generation int NOT NULL DEFAULT 1;
COMMENT ON COLUMN tb_package_usage.generation IS '资产世代编号';

-- 5. tb_asset_recharge_record: 新增 operator_type、generation 和强充关联字段
ALTER TABLE tb_asset_recharge_record ADD COLUMN operator_type varchar(20) NOT NULL DEFAULT 'admin_user';
ALTER TABLE tb_asset_recharge_record ADD COLUMN generation int NOT NULL DEFAULT 1;
ALTER TABLE tb_asset_recharge_record ADD COLUMN linked_package_ids jsonb DEFAULT '[]';
ALTER TABLE tb_asset_recharge_record ADD COLUMN linked_order_type varchar(20);
ALTER TABLE tb_asset_recharge_record ADD COLUMN linked_carrier_type varchar(20);
ALTER TABLE tb_asset_recharge_record ADD COLUMN linked_carrier_id bigint;
COMMENT ON COLUMN tb_asset_recharge_record.operator_type IS '操作人类型 admin_user-后台用户 personal_customer-个人客户';
COMMENT ON COLUMN tb_asset_recharge_record.generation IS '资产世代编号';
COMMENT ON COLUMN tb_asset_recharge_record.linked_package_ids IS '强充关联套餐ID列表';
COMMENT ON COLUMN tb_asset_recharge_record.linked_order_type IS '关联订单类型';
COMMENT ON COLUMN tb_asset_recharge_record.linked_carrier_type IS '关联载体类型';
COMMENT ON COLUMN tb_asset_recharge_record.linked_carrier_id IS '关联载体ID';

-- 6. tb_carrier: 新增实名链接配置
ALTER TABLE tb_carrier ADD COLUMN realname_link_type varchar(20) NOT NULL DEFAULT 'none';
ALTER TABLE tb_carrier ADD COLUMN realname_link_template varchar(500) DEFAULT '';
COMMENT ON COLUMN tb_carrier.realname_link_type IS '实名链接类型 none-不支持 template-模板URL gateway-Gateway接口';
COMMENT ON COLUMN tb_carrier.realname_link_template IS '实名链接模板URL';

-- 7. tb_shop_package_allocation: 新增 retail_price
ALTER TABLE tb_shop_package_allocation ADD COLUMN retail_price bigint NOT NULL DEFAULT 0;
COMMENT ON COLUMN tb_shop_package_allocation.retail_price IS '代理面向终端客户的零售价（分）';

-- 8. 存量数据修复: 将 retail_price 设为对应套餐的 suggested_retail_price
UPDATE tb_shop_package_allocation spa
SET retail_price = (SELECT suggested_retail_price FROM tb_package p WHERE p.id = spa.package_id)
WHERE retail_price = 0;

-- 9. tb_personal_customer: wx_open_id 唯一索引改为普通索引
DROP INDEX IF EXISTS idx_personal_customer_wx_open_id;
CREATE INDEX idx_personal_customer_wx_open_id ON tb_personal_customer(wx_open_id);