-- 会员分销邀请码与独立佣金账户升级脚本
-- 说明：
-- 1. 为会员资料增加邀请码字段，支持邀请绑定。
-- 2. 为分销关系与佣金流水增加 member_user_id 口径，避免与后台 sys_user 混用。
-- 3. 新增独立佣金账户与账户流水，佣金可转入钱包，但不直接混入钱包口径。

ALTER TABLE `xxk_member_profile`
    ADD COLUMN `invite_code` VARCHAR(32) NULL COMMENT '邀请码' AFTER `member_user_id`,
    ADD UNIQUE INDEX `uk_xxk_member_profile_invite_code` (`invite_code`);

ALTER TABLE `xxk_distribution_relation`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '当前会员ID' AFTER `grand_parent_user_id`,
    ADD COLUMN `parent_member_user_id` BIGINT NULL COMMENT '一级上级会员ID' AFTER `member_user_id`,
    ADD COLUMN `grand_parent_member_user_id` BIGINT NULL COMMENT '二级上级会员ID' AFTER `parent_member_user_id`,
    ADD UNIQUE INDEX `uk_xxk_distribution_relation_member_user_id` (`member_user_id`),
    ADD KEY `idx_xxk_distribution_relation_parent_member_user_id` (`parent_member_user_id`);

ALTER TABLE `xxk_distribution_commission`
    ADD COLUMN `member_user_id` BIGINT NULL COMMENT '佣金归属会员ID' AFTER `from_user_id`,
    ADD COLUMN `from_member_user_id` BIGINT NULL COMMENT '消费会员ID' AFTER `member_user_id`,
    ADD KEY `idx_xxk_distribution_commission_member_user_id` (`member_user_id`),
    ADD KEY `idx_xxk_distribution_commission_from_member_user_id` (`from_member_user_id`);

CREATE TABLE `xxk_distribution_account` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` BIGINT DEFAULT NULL COMMENT '后台用户ID',
  `member_user_id` BIGINT DEFAULT NULL COMMENT '会员ID',
  `available_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '可用佣金余额',
  `frozen_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '冻结佣金余额',
  `total_earned_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '累计获得佣金',
  `total_revoked_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '累计回退佣金',
  `total_transfer_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '累计转入钱包金额',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1正常 0禁用)',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_distribution_account_member_user_id` (`member_user_id`),
  UNIQUE KEY `uk_xxk_distribution_account_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分销佣金账户表';

CREATE TABLE `xxk_distribution_account_flow` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `flow_no` VARCHAR(64) NOT NULL COMMENT '流水号',
  `user_id` BIGINT DEFAULT NULL COMMENT '后台用户ID',
  `member_user_id` BIGINT DEFAULT NULL COMMENT '会员ID',
  `commission_id` BIGINT DEFAULT NULL COMMENT '关联佣金记录ID',
  `biz_type` VARCHAR(32) NOT NULL COMMENT '业务类型',
  `change_type` VARCHAR(16) NOT NULL COMMENT '变动类型(IN/OUT)',
  `change_amount` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '变动金额',
  `before_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '变动前余额',
  `after_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '变动后余额',
  `related_order_no` VARCHAR(64) DEFAULT NULL COMMENT '关联订单号',
  `related_wallet_flow_no` VARCHAR(64) DEFAULT NULL COMMENT '关联钱包流水号',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `operate_by` BIGINT DEFAULT NULL COMMENT '操作人ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_distribution_account_flow_no` (`flow_no`),
  KEY `idx_xxk_distribution_account_flow_member_user_id` (`member_user_id`),
  KEY `idx_xxk_distribution_account_flow_commission_id` (`commission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分销佣金账户流水表';