-- 修改原数据库脚本
ALTER TABLE crmeb.`eb_product`
ADD COLUMN `is_brokerage` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否分销商品：0否，1是' AFTER `update_time`;

ALTER TABLE crmeb.`eb_order_detail`
ADD COLUMN `brokerage_status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '分佣状态：0未分佣，1已分佣' AFTER `update_time`;

-- 新建数据表
DROP TABLE IF EXISTS crmeb.bcx_channel;
CREATE TABLE crmeb.bcx_channel(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id' ,
    `bcx_id` VARCHAR(50)   DEFAULT NULL COMMENT '平台id' ,
    `name` VARCHAR(200)   DEFAULT NULL COMMENT '渠道名称' ,
    `status` TINYINT(4) NOT NULL  DEFAULT 1 COMMENT '状态：0无效，1有效' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '渠道表';

DROP TABLE IF EXISTS crmeb.bcx_department;
CREATE TABLE crmeb.bcx_department(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id' ,
    `bcx_id` VARCHAR(50)   DEFAULT NULL COMMENT '平台id' ,
    `code` VARCHAR(100)   DEFAULT NULL COMMENT '部门代码' ,
    `parent_code` VARCHAR(100)   DEFAULT NULL COMMENT '上级部门代码' ,
    `tree_code` VARCHAR(1000)   DEFAULT NULL COMMENT '部门代码树' ,
    `name` VARCHAR(200)   DEFAULT NULL COMMENT '部门名称' ,
    `status` TINYINT(4) NOT NULL  DEFAULT 1 COMMENT '状态：0无效，1有效' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '部门表';

DROP TABLE IF EXISTS crmeb.bcx_department_member;
CREATE TABLE crmeb.bcx_department_member(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id' ,
    `bcx_id` VARCHAR(50)   DEFAULT NULL COMMENT '平台id' ,
    `name` VARCHAR(200)   DEFAULT NULL COMMENT '姓名' ,
    `department_id` BIGINT(20)   DEFAULT NULL COMMENT '部门id' ,
    `status` TINYINT(4) NOT NULL  DEFAULT 1 COMMENT '状态：0无效，1有效' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '部门成员表';

DROP TABLE IF EXISTS crmeb.bcx_brokerage_config;
CREATE TABLE crmeb.bcx_brokerage_config(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id' ,
    `product_id` BIGINT(20)   DEFAULT NULL COMMENT '商品id（为空表示默认配置，不为空表示某个商品单独配置）' ,
    `belong_id` BIGINT(20)   DEFAULT NULL COMMENT '关联id：渠道表id，部门表id，部门成员表id' ,
    `belong_type` TINYINT(4)   DEFAULT NULL COMMENT '关联类型：1渠道表，2部门表，3部门成员表' ,
    `ratio` DECIMAL(5,2)   DEFAULT NULL COMMENT '佣金比例' ,
    `status` TINYINT(4) NOT NULL  DEFAULT 1 COMMENT '状态：0无效，1有效' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '佣金绩效配置表';

DROP TABLE IF EXISTS crmeb.bcx_brokerage_config_region;
CREATE TABLE crmeb.bcx_brokerage_config_region(
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `brokerage_config_id` bigint(20) DEFAULT NULL COMMENT '配置表id',
  `region_code` varchar(20) DEFAULT NULL COMMENT '区域code',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `fk_bcx_brokerage_config_region_brokerage_config_id` (`brokerage_config_id`),
  CONSTRAINT `fk_bcx_brokerage_config_region_brokerage_config_id` FOREIGN KEY (`brokerage_config_id`) REFERENCES `bcx_brokerage_config` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)  COMMENT = '佣金绩效配置区域关联表';

DROP TABLE IF EXISTS crmeb.bcx_performance_report;
CREATE TABLE crmeb.bcx_performance_report(
    `id` BIGINT(20) NOT NULL   COMMENT '主键id' ,
    `belong_id` BIGINT(20)   DEFAULT NULL COMMENT '关联id：渠道表id，部门表id，部门成员表id，用户表id' ,
    `belong_type` TINYINT(4)   DEFAULT NULL COMMENT '关联类型：1渠道表，2部门表，3部门成员表，4用户表-分销商，5用户表-分销员' ,
    `order_id` INT(11)   DEFAULT NULL COMMENT '订单id' ,
    `order_no` varchar(50) DEFAULT NULL COMMENT '订单号',
    `order_user_id` INT(11)   DEFAULT NULL COMMENT '下单用户id' ,
    `order_time` DATETIME   DEFAULT NULL COMMENT '下单时间' ,
    `order_detail_id` INT(11)   DEFAULT NULL COMMENT '订单明细id' ,
    `product_id` INT(11)   DEFAULT NULL COMMENT '商品id' ,
    `product_name` VARCHAR(500)   DEFAULT NULL COMMENT '商品名称' ,
    `attr_value_id` INT(11)   DEFAULT NULL COMMENT '商品规格值id' ,
    `sku` VARCHAR(255)   DEFAULT NULL COMMENT '商品sku' ,
    `pay_price` DECIMAL(15,2)   DEFAULT NULL COMMENT '实际支付金额' ,
    `brokerage_config_type` TINYINT(4)   DEFAULT NULL COMMENT '佣金配置类型：1默认比例，2自定义比例' ,
    `brokerage_config_snapshot` TEXT   DEFAULT NULL COMMENT '佣金配置快照' ,
    `brokerage_ratio` DECIMAL(5,2)   DEFAULT NULL COMMENT '佣金比例' ,
    `brokerage_price` DECIMAL(15,2)   DEFAULT NULL COMMENT '佣金金额' ,
    `un_freeze_time` DATETIME   DEFAULT NULL COMMENT '解冻时间' ,
    `settle_status` TINYINT(4)   DEFAULT NULL COMMENT '结算状态：1待生效，2冻结中，3待结算，4已结算，5无效' ,
    `pay_way` tinyint(4) DEFAULT NULL COMMENT '支付方式：1线下结算，2企业钱包结算，3第三方支付结算',
    `settle_bill_id` BIGINT(20)   DEFAULT NULL COMMENT '所属账单id' ,
    `settle_bill_attachments_id` BIGINT(11)   DEFAULT NULL COMMENT '结算凭证id' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '业绩报表';

DROP TABLE IF EXISTS crmeb.bcx_settle_bill;
CREATE TABLE crmeb.bcx_settle_bill(
    `id` BIGINT(20) NOT NULL   COMMENT '主键id' ,
    `belong_id` BIGINT(20)   DEFAULT NULL COMMENT '关联id：渠道表id，部门表id，部门成员表id，用户表id' ,
    `belong_type` TINYINT(4)   DEFAULT NULL COMMENT '关联类型：1渠道表，2部门表，3部门成员表，4用户表-分销商，5用户表-分销员' ,
    `bill_period` VARCHAR(10)   DEFAULT NULL COMMENT '账期，yyyy-MM' ,
    `order_count` INT(11)   DEFAULT NULL COMMENT '订单总数量' ,
    `order_total_price` DECIMAL(15,2)   DEFAULT NULL COMMENT '订单总金额' ,
    `brokerage_total_price` DECIMAL(15,2)   DEFAULT NULL COMMENT '佣金总金额' ,
    `brokerage_settle_total_price` DECIMAL(15,2)   DEFAULT NULL COMMENT '佣金已结算总金额' ,
    `settle_status` TINYINT(4)   DEFAULT NULL COMMENT '结算状态：1待结算，2部分结算，3已结算' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '结算账单表';

DROP TABLE IF EXISTS crmeb.bcx_settle_bill_attachments;
CREATE TABLE crmeb.bcx_settle_bill_attachments(
    `id` BIGINT(20) NOT NULL   COMMENT '主键id' ,
    `settle_bill_id` BIGINT(20)   DEFAULT NULL COMMENT '账单id' ,
    `attachments_url` VARCHAR(500)   DEFAULT NULL COMMENT '凭证附件地址' ,
    `create_time` DATETIME   DEFAULT NULL COMMENT '创建时间' ,
    `update_time` DATETIME   DEFAULT NULL COMMENT '更新时间' ,
    PRIMARY KEY (id)
)  COMMENT = '结算账单凭证表';


DROP TABLE IF EXISTS crmeb.bcx_region;
CREATE TABLE crmeb.bcx_region(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id' ,
    `region_code` VARCHAR(20)   DEFAULT NULL COMMENT '区域code' ,
    `region_name` VARCHAR(50)   DEFAULT NULL COMMENT '区域名称' ,
    `region_full_name` VARCHAR(200)   DEFAULT NULL COMMENT '区域全称' ,
    `region_level` INT(11)   DEFAULT NULL COMMENT '区域级别' ,
    `seq` INT(11)   DEFAULT NULL COMMENT '排序' ,
    `parent_code` VARCHAR(20)   DEFAULT NULL COMMENT '上级区域code' ,
    PRIMARY KEY (id)
)  COMMENT = 'v5区域表';

INSERT INTO crmeb.`bcx_region` (`region_code`, `region_name`, `region_full_name`, `region_level`, `seq`, `parent_code`)
SELECT id, region_name, region_full_name, region_level, seq, parent_id FROM obpm2.`sys_region`;


ALTER TABLE `crmeb`.`bcx_department`
    ADD UNIQUE INDEX `fk_dept_bcx_id`(`bcx_id`) USING BTREE;
ALTER TABLE `crmeb`.`bcx_department_member`
    ADD UNIQUE INDEX `fk_dept_bcx_id`(`bcx_id`) USING BTREE;

UPDATE `crmeb`.`eb_system_config` SET `value` = '-1' WHERE `name` = 'retail_store_line';

ALTER TABLE `crmeb`.`bcx_performance_report`
    ADD COLUMN `settle_time` datetime NULL DEFAULT NULL COMMENT '结算时间' AFTER `un_freeze_time`;

ALTER TABLE `crmeb`.`bcx_performance_report`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号' AFTER `order_id`,
    ADD COLUMN `refund_order_id` int(11) NULL DEFAULT NULL COMMENT '退款订单id' AFTER `sku`,
    ADD COLUMN `refund_order_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '退款订单号' AFTER `refund_order_id`,
    ADD COLUMN `refund_order_info_id` int(11) NULL DEFAULT NULL COMMENT '退款订单明细id' AFTER `refund_order_no`,
    ADD COLUMN `can_refund_price` decimal(15, 2) NULL COMMENT '能退款的佣金余额' AFTER `brokerage_price`;

ALTER TABLE `crmeb`.`eb_refund_order_info`
    ADD COLUMN `brokerage_status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '分佣状态：0未分佣，1已分佣' AFTER `update_time`;

ALTER TABLE `crmeb`.`bcx_performance_report`
    CHANGE COLUMN `settle_bill_attachments_id` `settle_attachments_id` bigint(11) NULL DEFAULT NULL COMMENT '结算凭证id' AFTER `settle_bill_id`,
    ADD COLUMN `settle_id` bigint(20) NULL DEFAULT NULL COMMENT '结算id' AFTER `settle_bill_id`;

DROP TABLE IF EXISTS crmeb.bcx_settle_bill_attachments;
CREATE TABLE crmeb.`bcx_settle_attachments` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `settle_id` bigint(20) DEFAULT NULL COMMENT '结算id',
  `type` tinyint(4) DEFAULT NULL COMMENT '附件类型',
  `url` varchar(500) DEFAULT NULL COMMENT '附件地址',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                          PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='结算附件表';

CREATE TABLE `bcx_settle` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `settle_bill_id` bigint(20) DEFAULT NULL COMMENT '账单id',
  `belong_id` bigint(20) DEFAULT NULL COMMENT '关联id：渠道表id，部门表id，部门成员表id，用户表id',
  `belong_type` tinyint(4) DEFAULT NULL COMMENT '关联类型：1渠道表，2部门表，3部门成员表，4用户表-分销商，5用户表-分销员',
  `bill_period` varchar(10) DEFAULT NULL COMMENT '账期，yyyy-MM',
  `settle_no` varchar(50) DEFAULT NULL COMMENT '结算单号',
  `report_count` int(11) DEFAULT NULL COMMENT '结算数量',
  `total_price` decimal(15,2) DEFAULT NULL COMMENT '实付总金额',
  `brokerage_total_price` decimal(15,2) DEFAULT NULL COMMENT '佣金总金额',
  `settle_status` tinyint(4) DEFAULT NULL COMMENT '结算状态：1待结算，2已结算',
  `pay_way` tinyint(4) DEFAULT NULL COMMENT '支付方式：1线下结算，2企业钱包结算，3第三方支付结算',
  `upload_attachments` tinyint(4) DEFAULT NULL COMMENT '发票/结算凭证：1已上传，2未上传',
  `settle_time` datetime DEFAULT NULL COMMENT '结算时间',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='结算表';


INSERT INTO `crmeb`.`eb_system_role` (`id`, `mer_id`, `role_name`, `rules`, `level`, `status`, `type`, `create_time`, `update_time`)
 VALUES (11, 0, '部门管理员', '', 0, 1, 3, '2023-09-05 09:40:15', '2023-09-05 09:40:15');
INSERT INTO `crmeb`.`eb_system_role` (`id`, `mer_id`, `role_name`, `rules`, `level`, `status`, `type`, `create_time`, `update_time`)
 VALUES (12, 0, '部门成员', '', 0, 1, 3, '2023-09-05 09:40:23', '2023-09-05 09:40:23');

INSERT INTO `crmeb`.`eb_system_admin` (`account`, `pwd`, `real_name`, `header_image`, `roles`, `last_ip`, `login_count`, `level`, `status`, `is_del`, `phone`, `is_sms`, `type`, `mer_id`, `create_time`, `update_time`, `identity_no`)
 VALUES ('department_manager', 'Yoa3yW3NQi0LnNck4+Zp9j70SdTQ3KC0', '部门管理员', '', '11', NULL, 0, 1, 1, 0, '0', 0, 3, 0, '2023-09-05 09:42:22', '2023-09-05 09:42:38', NULL);
INSERT INTO `crmeb`.`eb_system_admin` (`account`, `pwd`, `real_name`, `header_image`, `roles`, `last_ip`, `login_count`, `level`, `status`, `is_del`, `phone`, `is_sms`, `type`, `mer_id`, `create_time`, `update_time`, `identity_no`)
VALUES ('department_member', 'Yoa3yW3NQi03qlZOXzu3qzLoDBpbyXuH', '部门成员', '', '12', NULL, 0, 1, 1, 0, '0', 0, 3, 0, '2023-09-05 09:43:07', '2023-09-05 09:43:07', NULL);

INSERT INTO `crmeb`.`eb_schedule_job` (`bean_name`, `method_name`, `params`, `cron_expression`, `status`, `remark`, `is_delete`, `create_time`) VALUES ('GenerateReportTask', 'generateReport', NULL, '0 0 */1 * * ?', 0, '生成业绩报表任务', 0, '2023-09-06 17:50:26');


ALTER TABLE `crmeb`.`bcx_settle_attachments`
    CHANGE COLUMN `url` `file_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件名' AFTER `type`,
    ADD COLUMN `ext_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '扩展名' AFTER `file_name`,
    ADD COLUMN `url` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件地址' AFTER `ext_name`;



update `crmeb`.`eb_schedule_job` set `cron_expression` = '0 */5 * * * ?' where `bean_name` = 'GenerateReportTask';

update `crmeb`.`bcx_department` set `parent_code` = '0' where `parent_code` is null;

ALTER TABLE `crmeb`.`bcx_department`
    MODIFY COLUMN `parent_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '上级部门代码' AFTER `code`;