sql/upgrade_260806/chargeservice.sql

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `chargeservice`.`cg_model_info` ROW_FORMAT = Dynamic;

ALTER TABLE `chargeservice`.`cg_order_integrate_rel` ROW_FORMAT = Dynamic;

ALTER TABLE `chargeservice`.`cg_park_fee_discount` ROW_FORMAT = Dynamic;

ALTER TABLE `chargeservice`.`cg_park_fee_discount` DROP INDEX `index_carinid`;

ALTER TABLE `chargeservice`.`cg_park_fee_discount` DROP INDEX `index_orderid`;

ALTER TABLE `chargeservice`.`cg_park_fee_discount` ADD COLUMN `merchant_code` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '商家编号' AFTER `discount_id`;

ALTER TABLE `chargeservice`.`cg_park_fee_discount` MODIFY COLUMN `car_in_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '入场id' AFTER `order_id`;

ALTER TABLE `chargeservice`.`cg_park_fee_log` ROW_FORMAT = Dynamic;

ALTER TABLE `chargeservice`.`cg_park_fee_log` MODIFY COLUMN `car_in_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '入场id' AFTER `order_id`;

ALTER TABLE `chargeservice`.`cg_park_fee_order` ROW_FORMAT = Dynamic;

ALTER TABLE `chargeservice`.`cg_park_fee_order` MODIFY COLUMN `car_in_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '入场id' AFTER `order_id`;

CREATE TABLE `chargeservice`.`cg_park_roll_charge_segment`  (
  `id` bigint NOT NULL COMMENT 'Snowflake主键',
  `order_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '临停订单号，零元离场可为空',
  `project_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目编号',
  `park_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '停车场ID，滚动计费隔离维度之一',
  `plate_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车牌号，滚动计费隔离维度之一',
  `car_in_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本次进场记录ID',
  `pre_car_in_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '同一滚动周期内上一次进场记录ID，首段为空',
  `first_car_in_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '本滚动周期首次进场记录ID，用于标识完整周期链',
  `model_code` int NULL DEFAULT NULL COMMENT '本周期使用的计费套餐类型编码',
  `cycle_time_type` tinyint NOT NULL COMMENT '0首次入场起算，1自然日',
  `first_in_time` datetime NOT NULL COMMENT '本滚动周期首次进场时间；自然日模式为当天00:00:00',
  `cycle_start_time` datetime NOT NULL COMMENT '当前片段所属24小时周期开始时间，左闭',
  `cycle_end_time` datetime NOT NULL COMMENT '当前片段所属24小时周期结束时间，右开',
  `segment_start_time` datetime NOT NULL COMMENT '本计费片段开始时间',
  `segment_end_time` datetime NOT NULL COMMENT '本计费片段结束时间',
  `roll_node_time` datetime NULL DEFAULT NULL COMMENT '计费组件返回的滚动算费节点时间',
  `roll_billing_date` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '计费组件返回的滚动账期，格式yyyy-MM-dd',
  `cycle_calculated_amount` int NOT NULL DEFAULT 0 COMMENT '组件周期累计费用，分',
  `settled_before_amount` int NOT NULL DEFAULT 0 COMMENT '本次计算前本周期已覆盖金额，分',
  `increment_amount` int NOT NULL DEFAULT 0 COMMENT '本次应结算增量，分',
  `actual_amount` int NOT NULL DEFAULT 0 COMMENT '本次实付，分',
  `discount_amount` int NOT NULL DEFAULT 0 COMMENT '本次优惠，分',
  `free_amount` int NOT NULL DEFAULT 0 COMMENT '本次免费，分',
  `settled_total_amount` int NOT NULL DEFAULT 0 COMMENT '本次已确认覆盖金额，分',
  `max_amount` int NOT NULL DEFAULT 0 COMMENT '周期封顶，分',
  `car_identity` tinyint NOT NULL DEFAULT 2 COMMENT '1长租车，2临停车',
  `cross_interval_type` tinyint NULL DEFAULT NULL COMMENT '跨周期片段标识：1跨周期拆分产生的中间片段，空为普通片段',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '0待确认，1已确认，2已作废',
  `active_slot` tinyint GENERATED ALWAYS AS ((case when (`status` in (0,1)) then 1 else NULL end)) STORED COMMENT '有效唯一槽：待确认/已确认占槽，作废记录为NULL释放槽位' NULL,
  `rollback_record` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '计费组件RollBackRecord原始JSON，后续同周期进场原样回传',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_roll_segment`(`park_id` ASC, `car_in_id` ASC, `cycle_start_time` ASC, `segment_start_time` ASC, `segment_end_time` ASC, `active_slot` ASC) USING BTREE,
  INDEX `idx_roll_order_status`(`order_id` ASC, `status` ASC) USING BTREE,
  INDEX `idx_roll_park_plate_status_end`(`park_id` ASC, `plate_no` ASC, `status` ASC, `segment_end_time` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '滚动计费区间明细' ROW_FORMAT = DYNAMIC;

ALTER TABLE `chargeservice`.`cg_temp_standard` ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS=1;