-- 分销功能补齐：区域代理、复购返现、级差分佣、邀请码
-- 执行前请将 fa_ 替换为实际表前缀（如 fa_）
-- 本脚本可重复执行：已存在的字段/索引/表/配置会自动跳过

-- 用户邀请码（列）
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_user' AND COLUMN_NAME = 'invite_code') = 0,
    'ALTER TABLE `fa_user` ADD COLUMN `invite_code` varchar(16) DEFAULT NULL COMMENT ''邀请码'' AFTER `parent_user_id`',
    'SELECT ''skip: fa_user.invite_code 已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 用户邀请码（唯一索引）
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_user' AND INDEX_NAME = 'invite_code') = 0,
    'ALTER TABLE `fa_user` ADD UNIQUE INDEX `invite_code`(`invite_code`) USING BTREE',
    'SELECT ''skip: fa_user.invite_code 索引已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 分销扩展配置
INSERT IGNORE INTO `fa_shopro_commission_config` (`id`, `name`, `value`, `group`, `description`) VALUES
(20, 'commission_mode', 'chain', 'basic', '分佣模式:chain=关系链分佣,differential=级差分佣'),
(21, 'repurchase_enable', '1', 'repurchase', '复购返现开关'),
(22, 'repurchase_self_enable', '1', 'repurchase', '买家自购复购返现'),
(23, 'repurchase_rate', '5', 'repurchase', '复购佣金比例(%)'),
(24, 'repurchase_self_rate', '3', 'repurchase', '买家自购复购返现比例(%)'),
(25, 'invite_code_enable', '1', 'basic', '邀请码功能开关'),
(26, 'management_award_enable', '1', 'basic', '管理奖开关'),
(27, 'repurchase_referrer_enable', '1', 'repurchase', '复购时推荐人继续分佣'),
(28, 'repurchase_self_type', 'fixed', 'repurchase', '本人复购返现类型:fixed=固定金额,rate=百分比'),
(29, 'repurchase_self_amount', '5', 'repurchase', '本人复购固定返现金额(元)'),
(30, 'chain_commission_enable', '1', 'basic', '关系链分佣开关:0=关,1=开'),
(31, 'differential_commission_enable', '1', 'basic', '级差奖开关:0=关,1=开');

-- 分销商品：级差/管理奖开关
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_shopro_commission_goods' AND COLUMN_NAME = 'differential_enable') = 0,
    'ALTER TABLE `fa_shopro_commission_goods` ADD COLUMN `differential_enable` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否参与级差奖:0=否,1=是'' AFTER `commission_order_status`, ADD COLUMN `management_enable` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否参与管理奖:0=否,1=是'' AFTER `differential_enable`',
    'SELECT ''skip: fa_shopro_commission_goods differential/management 已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 分销商品：复购返现开关
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_shopro_commission_goods' AND COLUMN_NAME = 'repurchase_enable') = 0,
    'ALTER TABLE `fa_shopro_commission_goods` ADD COLUMN `repurchase_enable` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否参与复购返现:0=否,1=是'' AFTER `management_enable`',
    'SELECT ''skip: fa_shopro_commission_goods repurchase_enable 已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 区域代理等级
CREATE TABLE IF NOT EXISTS `fa_shopro_regional_agent_level` (
  `level` int(10) NOT NULL COMMENT '等级权重',
  `name` varchar(50) NOT NULL COMMENT '等级名称',
  `area_level` tinyint(1) NOT NULL DEFAULT 1 COMMENT '区域层级:1=省,2=市,3=区县',
  `commission_rate` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '佣金比例(%)',
  `commission_money` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '固定佣金(每单)',
  `commission_type` enum('rate','money') NOT NULL DEFAULT 'rate' COMMENT '佣金类型',
  PRIMARY KEY (`level`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区域代理等级';

INSERT IGNORE INTO `fa_shopro_regional_agent_level` (`level`, `name`, `area_level`, `commission_rate`, `commission_money`, `commission_type`) VALUES
(1, '省级代理', 1, 3.00, 0.00, 'rate'),
(2, '市级代理', 2, 2.00, 0.00, 'rate'),
(3, '区县代理', 3, 1.00, 0.00, 'rate');

-- 区域代理
CREATE TABLE IF NOT EXISTS `fa_shopro_regional_agent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `level` int(10) NOT NULL DEFAULT 3 COMMENT '代理等级',
  `area_id` int(11) NOT NULL COMMENT '管辖区域ID',
  `area_level` tinyint(1) NOT NULL DEFAULT 3 COMMENT '区域层级:1=省,2=市,3=区县',
  `area_name` varchar(100) DEFAULT NULL COMMENT '区域名称',
  `commission_type` enum('rate','money') DEFAULT NULL COMMENT '自定义佣金类型，空=跟随等级',
  `commission_rate` decimal(5,2) DEFAULT NULL COMMENT '自定义佣金比例(%)',
  `commission_money` decimal(10,2) DEFAULT NULL COMMENT '自定义固定佣金(每单)',
  `apply_info` text COMMENT '申请资料',
  `total_income` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '总收益',
  `order_money` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '区域订单金额',
  `order_count` int(11) NOT NULL DEFAULT 0 COMMENT '区域订单数量',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '状态:pending=审核中,normal=正常,forbidden=禁用',
  `becometime` int(10) DEFAULT NULL COMMENT '成为代理时间',
  `createtime` int(10) NOT NULL COMMENT '创建时间',
  `updatetime` int(10) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `user_area` (`user_id`, `area_id`) USING BTREE,
  KEY `area_id` (`area_id`) USING BTREE,
  KEY `status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区域代理';

-- 区域代理配置
CREATE TABLE IF NOT EXISTS `fa_shopro_regional_agent_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '配置名',
  `value` varchar(2500) DEFAULT NULL COMMENT '配置值',
  `group` varchar(50) DEFAULT NULL COMMENT '分组',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区域代理设置';

INSERT IGNORE INTO `fa_shopro_regional_agent_config` (`id`, `name`, `value`, `group`, `description`) VALUES
(1, 'enable', '1', 'basic', '区域代理开关'),
(2, 'agent_check', '1', 'basic', '代理申请审核'),
(3, 'commission_event', 'confirm', 'amount', '佣金结算事件:payed/confirm/finish'),
(4, 'apply_form', '{"content":[{"name":"真实姓名","type":"input"},{"name":"手机号","type":"number"},{"name":"身份证正面","type":"image"}]}', 'agent', '申请表单'),
(5, 'commission_mode', 'stack', 'amount', '分佣模式:stack=叠加,exclusive=独占,differential=级差');

-- 分销商品：区域代理分佣开关
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_shopro_commission_goods' AND COLUMN_NAME = 'regional_enable') = 0,
    'ALTER TABLE `fa_shopro_commission_goods` ADD COLUMN `regional_enable` tinyint(1) NOT NULL DEFAULT 1 COMMENT ''是否参与区域代理分佣:0=否,1=是'' AFTER `repurchase_enable`',
    'SELECT ''skip: fa_shopro_commission_goods regional_enable 已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 区域代理：单个代理自定义佣金（空=跟随等级通用配置）
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fa_shopro_regional_agent' AND COLUMN_NAME = 'commission_type') = 0,
    'ALTER TABLE `fa_shopro_regional_agent`
        ADD COLUMN `commission_type` enum(''rate'',''money'') DEFAULT NULL COMMENT ''自定义佣金类型，空=跟随等级'' AFTER `area_name`,
        ADD COLUMN `commission_rate` decimal(5,2) DEFAULT NULL COMMENT ''自定义佣金比例(%)'' AFTER `commission_type`,
        ADD COLUMN `commission_money` decimal(10,2) DEFAULT NULL COMMENT ''自定义固定佣金(每单)'' AFTER `commission_rate`',
    'SELECT ''skip: fa_shopro_regional_agent commission 字段已存在'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 区域代理订单
CREATE TABLE IF NOT EXISTS `fa_shopro_regional_agent_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `order_item_id` int(11) NOT NULL DEFAULT 0 COMMENT '订单商品ID',
  `buyer_id` int(11) NOT NULL COMMENT '购买人',
  `agent_id` int(11) NOT NULL COMMENT '区域代理记录ID',
  `agent_user_id` int(11) NOT NULL COMMENT '代理用户ID',
  `area_id` int(11) NOT NULL COMMENT '匹配区域ID',
  `area_level` tinyint(1) NOT NULL COMMENT '区域层级',
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '结算金额',
  `commission_event` varchar(20) NOT NULL DEFAULT 'confirm' COMMENT '结算事件',
  `commission_reward_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '结算状态:0=待结算,1=已结算,-1=已退回',
  `commission_time` int(10) DEFAULT NULL COMMENT '结算时间',
  `createtime` int(10) NOT NULL COMMENT '创建时间',
  `updatetime` int(10) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `order_id` (`order_id`) USING BTREE,
  KEY `agent_id` (`agent_id`) USING BTREE,
  KEY `agent_user_id` (`agent_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区域代理订单';

-- 区域代理佣金
CREATE TABLE IF NOT EXISTS `fa_shopro_regional_agent_reward` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `order_item_id` int(11) NOT NULL DEFAULT 0 COMMENT '订单商品ID',
  `buyer_id` int(11) NOT NULL COMMENT '购买人',
  `regional_order_id` int(11) NOT NULL COMMENT '区域代理订单ID',
  `agent_id` int(11) NOT NULL COMMENT '区域代理记录ID',
  `agent_user_id` int(11) NOT NULL COMMENT '代理用户ID',
  `type` varchar(20) NOT NULL DEFAULT 'money' COMMENT '奖金类型',
  `commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '佣金',
  `original_commission` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '原始佣金',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态:0=待入账,1=已入账,-1=已退回',
  `createtime` int(10) NOT NULL COMMENT '创建时间',
  `updatetime` int(10) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `agent_user_id` (`agent_user_id`) USING BTREE,
  KEY `regional_order_id` (`regional_order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='区域代理佣金';