ALTER TABLE eb_product
    add `type` int(2) DEFAULT '0' COMMENT '商品类型：0-实体 1-虚拟';
-- ALTER TABLE eb_product DROP COLUMN `type`;


ALTER TABLE `eb_product_description`
    ADD COLUMN `desc_order` int          NULL DEFAULT 0 COMMENT '序号 1-5 每个商品5个详情',
    ADD COLUMN `is_show`    tinyint(1)   NULL DEFAULT 1 COMMENT '是否显示 0-不显示 1-显示',
    ADD COLUMN `name`       varchar(255) NULL COMMENT '详情名称';

-- ALTER TABLE `eb_product_description`
-- DROP COLUMN `desc_order`,
-- DROP COLUMN `is_show`,
-- DROP COLUMN `name`;


-- 开发和测试用户
-- INSERT INTO `eb_user` (`id`, `account`, `pwd`, `real_name`, `birthday`, `identity_card_no`, `tag_id`, `nickname`, `avatar`, `phone`, `country`, `province`, `city`, `district`, `address`, `sex`, `integral`, `experience`, `now_money`, `brokerage_price`, `level`, `sign_num`, `is_wechat_public`, `is_wechat_routine`, `pay_count`, `is_promoter`, `promoter_time`, `spread_uid`, `spread_time`, `spread_count`, `register_type`, `add_ip`, `last_ip`, `last_login_time`, `status`, `mark`, `create_time`, `update_time`, `is_logoff`, `logoff_time`, `is_wechat_ios`, `is_wechat_android`, `is_binding_ios`, `signature`) VALUES (1, '18292417675', 'f6mcpGQ8NEmwbab2TlkpUg==', '大粽子', '', '', '', '大粽子', 'crmebimage/public/maintain/2023/02/23/1c0fa967eb764d918f064744cc51dc70a6f2bj3sha.jpg', '18292417675', 'CN', '', '', '', '', 1, 0, 0, 0.00, 0.00, 0, 0, 0, 0, 0, 0, NULL, 0, NULL, 0, '', '', '', NULL, 1, '', '2023-07-25 02:12:08', '2023-07-25 02:13:03', 0, NULL, 0, 0, 0, '');
-- 18292417675 / crmeb@123456

# 禁用视图和存储过程
# CREATE VIEW `bcx_product_category_view` AS

ALTER TABLE eb_user
    ADD COLUMN `user_type` int(10) NULL COMMENT '用户类型：0-默认 1-企业 2-职员';


CREATE TABLE `bcx_user_relation` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户账号关联id',
     `uid` int(11) NOT NULL COMMENT '用户id',
     `sub_uid` int(11) NOT NULL COMMENT '关联用户id',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `eb_user`
    ADD COLUMN `region_code` varchar(20) NULL COMMENT '区域编码';

ALTER TABLE `eb_order`
    ADD COLUMN `region_code` varchar(20) NULL COMMENT '区域编码';

ALTER TABLE `eb_product`
    ADD COLUMN `show_buy_btn` tinyint(1) NULL COMMENT '显示立即购买按钮 0-不显示 1-显示',
    ADD COLUMN `show_try_btn` tinyint(1) NULL COMMENT '显示申请试用按钮 0-不显示 1-显示';

CREATE UNIQUE INDEX unique_index_account_eb_user ON eb_user(account);

ALTER TABLE `eb_user`
    MODIFY COLUMN `real_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '真实姓名' ;

DROP TABLE IF EXISTS bcx_user_product;

ALTER TABLE `eb_user`
    MODIFY COLUMN `account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户账号';

ALTER TABLE `eb_user`
    ADD COLUMN `identity_no` varchar(255) NULL COMMENT 'bcx账户联合id';

ALTER TABLE `eb_system_admin`
    ADD COLUMN `identity_no` varchar(255) NULL COMMENT 'bcx账户联合id';

ALTER TABLE `eb_merchant`
    ADD COLUMN `identity_no` varchar(255) NULL COMMENT 'bcx账户联合id';

INSERT INTO `eb_system_config` ( `name`, `title`, `form_id`, `value`, `status`, `create_time`, `update_time`, `form_name`) VALUES ( 'payment_reconciliation_download_folder', 'payment_reconciliation_download_folder', 100, '/data/upload/pay', 0, '2023-08-07 03:00:24', '2023-08-07 03:01:06', '汇付对账单下载保存目录');

DROP TABLE IF EXISTS bcx_user_products;

CREATE TABLE `bcx_product_rules` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品可见规则表id',
     `referenceid` varchar(200)  not null unique COMMENT '外部唯一ID',
     `product_id` int(11) unsigned DEFAULT NULL COMMENT '商品ID',
     `match_type` int(2) DEFAULT NULL COMMENT '规则类型 0: none全部可见 1: organization 指定企业 2: region 指定区域 ',
     `match_value` varchar(255) DEFAULT NULL COMMENT '规则值 企业ID或区域编码 ',
     `industry_code` varchar(200) DEFAULT NULL COMMENT '行业类型编码',
     `institutional_code` varchar(200) DEFAULT NULL COMMENT '机构类型编码',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '商品可见规则表';

CREATE UNIQUE INDEX unique_index_bcx_product_rules_reference_id ON bcx_product_rules (referenceId);

ALTER TABLE `eb_user`
    ADD COLUMN `industry_code` varchar(255) NULL COMMENT '行业类型编码';

ALTER TABLE `eb_user`
    ADD COLUMN `institutional_code` varchar(255) NULL COMMENT '机构类型编码';

CREATE TABLE `bcx_product_quota` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '限购id',
     `product_id` int(11) NOT NULL COMMENT '限购商品id',
     `enabled` tinyint(1) DEFAULT NULL COMMENT '启用限购 1-启用 0其他-不启用',
     `one_quota` int(11) DEFAULT NULL COMMENT '单次限购数量',
     `all_quota` int(11) DEFAULT NULL COMMENT '用户总限购数量',
     `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品限购表';
CREATE UNIQUE INDEX unique_index_bcx_product_quota_product_id ON bcx_product_quota (product_id);

ALTER TABLE `eb_system_admin`
    MODIFY COLUMN `real_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '管理员姓名';

ALTER TABLE `eb_order_detail`
    ADD COLUMN `source_id` varchar(255) NULL COMMENT '源头业务编号' AFTER `update_time`;

ALTER TABLE `eb_merchant_order`
    ADD COLUMN `last_delivery_time` timestamp NULL COMMENT '最新发货时间' AFTER `update_time`;

ALTER TABLE `eb_order`
    ADD COLUMN `biz_type` int(3) NULL COMMENT '业务类型:0-实物商品订单，1-虚拟商品订单,2-特殊类型(考培)订单' AFTER `region_code`;

ALTER TABLE eb_order
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
    MODIFY COLUMN `plat_order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '平台订单号';
ALTER TABLE eb_merchant_order
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号';
ALTER TABLE `eb_order_detail`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号';
ALTER TABLE `eb_refund_order`
    MODIFY COLUMN `refund_order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '退款订单号',
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商户订单号';
ALTER TABLE `eb_refund_order_info`
    MODIFY COLUMN `refund_order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '退款订单号';
ALTER TABLE `eb_refund_order_status`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号';
ALTER TABLE `eb_order_status`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号';
ALTER TABLE `eb_order_profit_sharing`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号（商户）';
ALTER TABLE `eb_order_invoice`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单号';
ALTER TABLE `eb_bill`
    MODIFY COLUMN `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '关联订单';

CREATE TABLE `bcx_product_benefits` (
    `id` varchar(255) NOT NULL COMMENT '福利配置id',
    `sku_id` int(11) NOT NULL COMMENT '商品sku ID',
    `product_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
    `company_id` varchar(255) NOT NULL COMMENT '企业id',
    `company_name` varchar(255) DEFAULT NULL COMMENT '企业名称',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='福利配置表';

ALTER TABLE `eb_order`
    MODIFY COLUMN `total_num` int(11) NOT NULL DEFAULT 0 COMMENT '订单商品总数',
    MODIFY COLUMN `pro_total_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '商品总价',
    MODIFY COLUMN `total_postage` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '邮费',
    MODIFY COLUMN `total_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总价',
    MODIFY COLUMN `coupon_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '优惠券金额',
    MODIFY COLUMN `integral_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '积分抵扣金额',
    MODIFY COLUMN `pay_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '实际支付金额',
    MODIFY COLUMN `pay_postage` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '支付邮费';
ALTER TABLE `eb_order_detail`
    MODIFY COLUMN `price` decimal(8, 2) NOT NULL COMMENT '商品单价',
    MODIFY COLUMN `vip_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '会员价格',
    MODIFY COLUMN `pay_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '实际支付金额',
    MODIFY COLUMN `pay_num` int(11) NOT NULL DEFAULT 0 COMMENT '购买数量',
    MODIFY COLUMN `freight_fee` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '运费金额',
    MODIFY COLUMN `coupon_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '优惠券金额',
    MODIFY COLUMN `integral_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '积分抵扣金额',
    MODIFY COLUMN `first_brokerage_fee` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '一级返佣金额',
    MODIFY COLUMN `second_brokerage_fee` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '二级返佣金额',
    MODIFY COLUMN `refund_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '退款金额',
    MODIFY COLUMN `refund_integral_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '退款积分抵扣金额',
    MODIFY COLUMN `refund_first_brokerage_fee` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '退一级返佣金额',
    MODIFY COLUMN `refund_second_brokerage_fee` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '退二级返佣金额';
ALTER TABLE `eb_merchant_order`
    MODIFY COLUMN `total_num` int(11) NOT NULL DEFAULT 0 COMMENT '订单商品总数',
    MODIFY COLUMN `pro_total_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '商品总价',
    MODIFY COLUMN `total_postage` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '邮费',
    MODIFY COLUMN `total_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总价',
    MODIFY COLUMN `pay_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '实际支付金额',
    MODIFY COLUMN `pay_postage` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '支付邮费',
    MODIFY COLUMN `integral_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '积分抵扣金额',
    MODIFY COLUMN `coupon_price` decimal(8, 2) NOT NULL DEFAULT 0.00 COMMENT '优惠券金额';

ALTER TABLE `eb_product_attr_value`
    ADD COLUMN `plat_fee` decimal(8, 2) NULL COMMENT '平台抽佣';
ALTER TABLE `eb_order`
    ADD COLUMN `plat_fee` decimal(8, 2) NULL COMMENT '平台抽佣';
ALTER TABLE `eb_order_detail`
    ADD COLUMN `plat_fee` decimal(8, 2) NULL COMMENT '平台抽佣';
ALTER TABLE `eb_product`
    CHANGE COLUMN `type` `biz_type` int(2) NULL DEFAULT 0 COMMENT '商品类型：0-实体 1-虚拟 2-特殊(考培) 3-保险 等等';

INSERT INTO `crmeb`.`eb_system_config` (`id`, `name`, `title`, `form_id`, `value`, `status`, `create_time`, `update_time`, `form_name`) VALUES (10000, 'e_risk_system_url', 'e_risk_system_url', 10000, 'http://estand.test.pss360.cn/', 0, '2023-10-20 09:54:02', '2023-10-20 09:54:02', '风评系统地址');
INSERT INTO `crmeb`.`eb_system_config` (`id`, `name`, `title`, `form_id`, `value`, `status`, `create_time`, `update_time`, `form_name`) VALUES (10001, 'payment_reconciliation_download_folder', 'payment_reconciliation_download_folder', 10001, '/data/upload/pay', 0, '2023-08-07 03:00:24', '2023-08-07 03:01:06', '汇付对账单下载保存目录');

DROP TABLE IF EXISTS bcx_product_menu;
CREATE TABLE bcx_product_menu(
     `id` INT(11) NOT NULL AUTO_INCREMENT  COMMENT 'id主键' ,
     `pid` INT(11) NOT NULL  DEFAULT 0 COMMENT '父节点id' ,
     `name` VARCHAR(100)    COMMENT '名称' ,
     `level` INT(2) NOT NULL  DEFAULT 1 COMMENT '层级' ,
     `sort` INT(5) NOT NULL  DEFAULT 9 COMMENT '排序号' ,
     `category_id` INT(11)    COMMENT '商品一级分类id' ,
     `create_time` TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
     `update_time` TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
     `created_by` INT(11)    COMMENT '创建人' ,
     `updated_by` INT(11)    COMMENT '更新人' ,
     `type` INT(2) NOT NULL  DEFAULT 0 COMMENT '菜单类型 0- PC菜单' ,
     PRIMARY KEY (id)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '商城商品菜单';

-- # ALTER TABLE `eb_user`
-- #     ADD COLUMN `tax_no` varchar(30) NULL COMMENT '税号';
-- # ALTER TABLE `eb_merchant`
-- #     ADD COLUMN `tax_no` varchar(30) NULL COMMENT '税号';

--  已更新到dev
--  已更新到test
--  已更新到beta
--  已更新到prod