-- 会员代理业务归属升级脚本
-- 说明：
-- 1. 为核心代理业务表增加 member_user_id 字段，逐步从 sys_user 归属切到 member_user。
-- 2. 该脚本只做结构升级，不强制迁移历史数据。

ALTER TABLE `xxk_proxy_order`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_proxy_order_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_proxy_order_item`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_proxy_order_item_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_wallet_account`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_wallet_account_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_wallet_flow`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_wallet_flow_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_static_proxy_asset`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_static_proxy_asset_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_static_proxy_whitelist`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_static_proxy_whitelist_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_dynamic_channel`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_dynamic_channel_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_dynamic_channel_traffic_log`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_dynamic_channel_traffic_log_member_user_id` (`member_user_id`);

ALTER TABLE `xxk_dynamic_proxy_generate_log`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '会员ID' AFTER `user_id`,
    ADD KEY `idx_xxk_dynamic_proxy_generate_log_member_user_id` (`member_user_id`);

-- 历史数据迁移建议：
-- 可按手机号将 sys_user 关联到 xxk_member_user 后，回填 member_user_id。
-- 示例思路：
-- UPDATE xxk_wallet_account wa
-- JOIN sys_user su ON su.id = wa.user_id
-- JOIN xxk_member_user mu ON mu.mobile = su.mobile
-- SET wa.member_user_id = mu.id
-- WHERE wa.member_user_id IS NULL;
