-- VIN码充电订单独立存储增量脚本
-- MyCat 环境请分逻辑库执行：订单表在 charging_pile_order，菜单在 charging_pile_other。

USE charging_pile_order;

CREATE TABLE IF NOT EXISTS `t_vin_charging_order` (
  `id` bigint NOT NULL COMMENT '主键',
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单编号',
  `vin_id` bigint NOT NULL COMMENT 'VIN管理记录ID（分片键）',
  `vin` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '握手报文VIN',
  `order_type` int DEFAULT '2' COMMENT '订单类型（VIN码充电订单）',
  `order_classification` int DEFAULT NULL COMMENT '订单分类（1=线上，2=线下）',
  `app_user_id` bigint DEFAULT NULL COMMENT '用户id',
  `app_user_car_id` bigint DEFAULT NULL COMMENT '充电车辆id',
  `plate_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '车牌号',
  `reservation_time` datetime DEFAULT NULL COMMENT '预约充电时间',
  `expected_vin` varchar(64) DEFAULT NULL COMMENT '支付车辆VIN快照',
  `actual_vin` varchar(64) DEFAULT NULL COMMENT '启动时设备识别VIN',
  `site_id` int DEFAULT NULL COMMENT '站点id',
  `parking_lot_id` int DEFAULT NULL COMMENT '停车场id',
  `charging_pile_id` int DEFAULT NULL COMMENT '充电桩id',
  `charging_gun_id` int DEFAULT NULL COMMENT '充电枪id',
  `charging_capacity` decimal(10,5) DEFAULT '0.00000' COMMENT '充电总度数',
  `charging_power` decimal(10,4) DEFAULT NULL COMMENT '充电功率',
  `start_time` datetime DEFAULT NULL COMMENT '充电开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '充电结束时间',
  `status` int DEFAULT NULL COMMENT '订单状态',
  `end_mode` int DEFAULT NULL COMMENT '结束方式',
  `recharge_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '充值金额',
  `recharge_payment_type` int DEFAULT NULL COMMENT '充值支付方式',
  `recharge_payment_status` int DEFAULT NULL COMMENT '充值支付状态',
  `pay_method` int DEFAULT NULL COMMENT '充电支付模式',
  `balance_pay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '余额支付金额',
  `third_pay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '第三方支付金额',
  `balance_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '余额退款金额',
  `third_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '第三方退款金额',
  `recharge_serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '充值支付第三方流水号',
  `charge_amount` decimal(10,2) DEFAULT NULL COMMENT '充电金额',
  `residual_amount` decimal(10,2) DEFAULT NULL COMMENT '充值剩余金额',
  `order_amount` decimal(10,4) DEFAULT NULL COMMENT '订单金额',
  `payment_amount` decimal(10,4) DEFAULT NULL COMMENT '支付金额',
  `refund_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '退款金额',
  `app_coupon_id` bigint DEFAULT NULL COMMENT '优惠券id',
  `coupon_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '优惠金额',
  `is_whitelist` tinyint NOT NULL DEFAULT '0' COMMENT '是否白名单订单',
  `whitelist_id` bigint DEFAULT NULL COMMENT '白名单用户ID',
  `whitelist_benefit_type` tinyint DEFAULT NULL COMMENT '白名单权益',
  `whitelist_discount_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单减免金额',
  `whitelist_electric_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单电费退款金额',
  `whitelist_service_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单服务费退款金额',
  `vip_discount` decimal(10,2) DEFAULT NULL COMMENT '会员折扣',
  `vip_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '会员优惠金额',
  `refund_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '退款流水号',
  `refund_status` int DEFAULT NULL COMMENT '退款状态',
  `refund_serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '退款第三方流水号',
  `refund_time` datetime DEFAULT NULL COMMENT '退款成功时间',
  `create_time` datetime DEFAULT NULL COMMENT '添加时间',
  `del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标记',
  `order_source` int DEFAULT '1' COMMENT '订单来源',
  `source_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '平台名称',
  `operator_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '运营商ID',
  `tripartite_platform_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '三方平台名称',
  `start_charge_seq` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '三方平台充电订单号',
  `commission_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '手续费',
  `sharing_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '分佣',
  `service_charge` decimal(10,4) DEFAULT NULL COMMENT '累计服务费',
  `electrovalence` decimal(10,4) DEFAULT NULL COMMENT '累计电费',
  `current` decimal(10,2) DEFAULT NULL COMMENT '电流',
  `voltage` decimal(10,2) DEFAULT NULL COMMENT '电压',
  `electricity` decimal(10,2) DEFAULT NULL COMMENT '电量',
  `power` decimal(10,2) DEFAULT NULL COMMENT '功率',
  `pay_time` datetime DEFAULT NULL COMMENT '最后支付时间',
  `phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '电话',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单标题',
  `need_elec` decimal(10,2) DEFAULT NULL COMMENT '实时需求电流',
  `total_electricity` double DEFAULT NULL COMMENT '剩余电量',
  `start_soc` decimal(10,2) DEFAULT '0.00' COMMENT '开始SOC',
  `end_soc` decimal(10,2) DEFAULT NULL COMMENT '结束SOC',
  PRIMARY KEY USING BTREE (`id`),
  KEY `idx_vin_id` USING BTREE (`vin_id`),
  KEY `idx_vin` USING BTREE (`vin`),
  KEY `idx_code` USING BTREE (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='VIN码充电订单'
TBPARTITION BY MOD_HASH(vin_id) TBPARTITIONS 20;

CREATE TABLE IF NOT EXISTS `t_vin_accounting_strategy_detail_order` (
  `id` int NOT NULL COMMENT '计费策略明细主键id',
  `accounting_strategy_id` int DEFAULT NULL COMMENT '策略id',
  `type` int DEFAULT NULL COMMENT '阶段',
  `start_time` varchar(100) DEFAULT NULL COMMENT '开始时间',
  `end_time` varchar(100) DEFAULT NULL COMMENT '结束时间',
  `electrovalence` double(10,4) DEFAULT NULL COMMENT '电价',
  `service_charge` double(10,4) DEFAULT NULL COMMENT '服务费',
  `cost_service_charge` double(10,4) DEFAULT NULL COMMENT '原价服务费',
  `charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `del_flag` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码订单计费策略明细快照'
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;

CREATE TABLE IF NOT EXISTS `t_vin_accounting_strategy_order` (
  `id` int NOT NULL COMMENT '策略id',
  `site_id` int DEFAULT NULL COMMENT '站点id',
  `name` varchar(255) DEFAULT NULL COMMENT '策略名称',
  `description` varchar(255) DEFAULT NULL COMMENT '策略说明',
  `discount` double(10,2) DEFAULT NULL COMMENT '会员折扣',
  `create_time` datetime DEFAULT NULL COMMENT '添加时间',
  `del_flag` int DEFAULT '0' COMMENT '删除状态',
  `first_user_id` bigint DEFAULT NULL COMMENT '一级审核用户id',
  `two_user_id` bigint DEFAULT NULL COMMENT '二级审核用户id',
  `first_remark` varchar(255) DEFAULT NULL COMMENT '一级审核备注',
  `two_remark` varchar(255) DEFAULT NULL COMMENT '二级审核备注',
  `audit_status` int DEFAULT '1' COMMENT '审核状态',
  `first_audit_time` datetime DEFAULT NULL COMMENT '一级审核时间',
  `two_audit_time` datetime DEFAULT NULL COMMENT '二级审核时间',
  `user_id` bigint DEFAULT NULL COMMENT '申请人ID',
  `parent_id` int DEFAULT NULL COMMENT '父级id',
  `charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码订单计费策略快照'
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;

CREATE TABLE IF NOT EXISTS `t_vin_charging_order_accounting_strategy` (
  `id` bigint NOT NULL COMMENT '主键',
  `charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
  `accounting_strategy_detail_id` bigint DEFAULT NULL COMMENT '计费策略明细id',
  `type` int DEFAULT NULL COMMENT '阶段',
  `start_time` varchar(100) DEFAULT NULL COMMENT '开始时间',
  `end_time` varchar(100) DEFAULT NULL COMMENT '结束时间',
  `electrovalence` double(10,4) DEFAULT NULL COMMENT '电价',
  `service_charge` double(10,4) DEFAULT NULL COMMENT '服务费',
  `cost_service_charge` double(10,4) DEFAULT NULL COMMENT '原价服务费',
  `charging_capacity` decimal(10,4) DEFAULT NULL COMMENT '充电度数',
  `create_time` datetime DEFAULT NULL COMMENT '首次添加时间',
  `period_electric_price` decimal(10,4) DEFAULT NULL COMMENT '时段电费',
  `period_service_price` decimal(10,4) DEFAULT NULL COMMENT '时段服务费',
  `period_original_service_price` decimal(10,4) DEFAULT NULL COMMENT '时段原服务费',
  `vip_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '会员折扣金额',
  `coupon_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '优惠券抵扣金额',
  PRIMARY KEY USING BTREE (`id`),
  KEY `idx_vin_order_id` USING BTREE (`charging_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电订单计费明细'
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;

CREATE TABLE IF NOT EXISTS `t_vin_charging_order_refund` (
  `id` bigint NOT NULL COMMENT '主键',
  `charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
  `refund_code` varchar(255) DEFAULT NULL COMMENT '退款流水号',
  `refund_amount` decimal(10,2) DEFAULT NULL COMMENT '退款金额',
  `balance_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '余额退款金额',
  `third_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '第三方退款金额',
  `refund_status` int DEFAULT NULL COMMENT '退款状态',
  `refund_serial_number` varchar(255) DEFAULT NULL COMMENT '退款第三方流水号',
  `refund_time` datetime DEFAULT NULL COMMENT '退款成功时间',
  `code` varchar(255) DEFAULT NULL COMMENT '订单编号',
  `refund_title` varchar(255) DEFAULT NULL COMMENT '退款标题',
  `refund_content` varchar(255) DEFAULT NULL COMMENT '退款内容',
  `refund_reason` varchar(255) DEFAULT NULL COMMENT '退款原因',
  `refund_remark` varchar(255) DEFAULT NULL COMMENT '退款备注',
  `refund_total_amount` decimal(10,2) DEFAULT NULL COMMENT '累计退款金额',
  `refund_fee` decimal(10,2) DEFAULT '0.00' COMMENT '手续费',
  `pay_amount` decimal(10,2) DEFAULT NULL COMMENT '支付金额',
  `pay_type` int DEFAULT NULL COMMENT '支付类型',
  `create_time` datetime DEFAULT NULL,
  `del_flag` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电退款明细'
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;

CREATE TABLE IF NOT EXISTS `t_vin_charging_order_share_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `charging_order_id` bigint DEFAULT NULL COMMENT 'VIN码充电订单ID',
  `charging_order_code` varchar(64) NOT NULL COMMENT 'VIN码充电订单号',
  `site_id` int DEFAULT NULL COMMENT '站点ID',
  `partner_id` int DEFAULT NULL COMMENT '合作商ID',
  `pay_channel` varchar(16) NOT NULL COMMENT '支付渠道',
  `channel_pay_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `channel_gift_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `channel_pay_ratio` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `channel_electricity_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `channel_service_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `electricity_share_ratio` decimal(5,2) DEFAULT NULL,
  `service_share_ratio` decimal(5,2) DEFAULT NULL,
  `electricity_share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `service_share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `need_online_share` tinyint(1) NOT NULL DEFAULT '0',
  `online_share_status` varchar(16) NOT NULL DEFAULT 'RECORDED',
  `seq_no` varchar(50) DEFAULT NULL,
  `busi_type` varchar(1) DEFAULT NULL,
  `ori_mer_id` varchar(20) DEFAULT NULL,
  `ori_trx_date` date DEFAULT NULL,
  `sub_order_id` varchar(50) DEFAULT NULL,
  `sub_mer_id` varchar(20) DEFAULT NULL,
  `sub_mer_prtcl_no` varchar(20) DEFAULT NULL,
  `mer_acct` varchar(34) DEFAULT NULL,
  `icbc_order_id` varchar(64) DEFAULT NULL,
  `sub_order_seq` varchar(32) DEFAULT NULL,
  `sub_order_trxid` varchar(64) DEFAULT NULL,
  `rec_result` varchar(8) DEFAULT NULL,
  `request_json` text,
  `response_json` text,
  `fail_reason` varchar(512) DEFAULT NULL,
  `retry_count` int NOT NULL DEFAULT '0',
  `last_retry_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `del_flag` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_channel` (`charging_order_code`,`pay_channel`),
  KEY `idx_share_status` (`online_share_status`),
  KEY `idx_share_sub_order_id` (`sub_order_id`),
  KEY `idx_share_partner` (`partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电订单全渠道分账记录';

USE charging_pile_other;

INSERT INTO `sys_menu`
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
SELECT 'VIN码充电订单', parent.menu_id, 2, 'vinChargingOrder', 'order/vinChargingOrder/index', '', 1, 0, 'C', '0', '0', '/vinChargingOrder/list', 'transaction', 'admin', NOW(), '', NULL, 'VIN码充电订单独立查询'
FROM `sys_menu` parent
WHERE parent.perms = '/vinManagement'
  AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/list');

INSERT INTO `sys_menu`
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
SELECT 'VIN订单详情', parent.menu_id, 1, '#', '', '', 1, 0, 'F', '0', '0', '/vinChargingOrder/detail', '#', 'admin', NOW(), '', NULL, ''
FROM `sys_menu` parent
WHERE parent.perms = '/vinChargingOrder/list'
  AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/detail');

INSERT INTO `sys_menu`
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
SELECT 'VIN订单导出', parent.menu_id, 2, '#', '', '', 1, 0, 'F', '0', '0', '/vinChargingOrder/export', '#', 'admin', NOW(), '', NULL, ''
FROM `sys_menu` parent
WHERE parent.perms = '/vinChargingOrder/list'
  AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/export');

INSERT INTO `sys_role_menu` (`role_id`,`menu_id`)
SELECT 1, menu.menu_id
FROM `sys_menu` menu
WHERE menu.perms IN ('/vinChargingOrder/list','/vinChargingOrder/detail','/vinChargingOrder/export')
  AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu` role_menu
    WHERE role_menu.role_id = 1 AND role_menu.menu_id = menu.menu_id
  );