sql/upgrade_260806/boxservice.sql

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `boxservice`.`box_duty`  (
  `box_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '岗亭',
  `user_id` int NOT NULL COMMENT '在线操作员',
  `username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '在线操作员账号',
  `on_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上班时间',
  `total_amount` int NOT NULL COMMENT '应收金额',
  `actual_amount` int NOT NULL COMMENT '实收金额',
  `discount_amount` int NOT NULL COMMENT '优惠金额',
  `free_amount` int NOT NULL COMMENT '免费金额',
  `refund_amount` int NOT NULL COMMENT '退款金额',
  `sum_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上一次结算时间',
  `project_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  PRIMARY KEY (`box_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '当前值班表' ROW_FORMAT = Dynamic;

CREATE TABLE `boxservice`.`box_duty_pay`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `box_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '岗亭',
  `pay_channel_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道',
  `actual_amount` int NOT NULL COMMENT '实收金额',
  `num` int NOT NULL DEFAULT 0 COMMENT '支付笔数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1199 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '当前值班支付统计表' ROW_FORMAT = Dynamic;

CREATE TABLE `boxservice`.`box_duty_record`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `box_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '岗亭',
  `user_id` int NOT NULL COMMENT '在线操作员',
  `username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '在线操作员账号',
  `on_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上班时间',
  `off_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下班时间',
  `total_amount` int NOT NULL COMMENT '应收金额',
  `actual_amount` int NOT NULL COMMENT '实收金额',
  `discount_amount` int NOT NULL COMMENT '优惠金额',
  `free_amount` int NOT NULL COMMENT '免费金额',
  `refund_amount` int NOT NULL COMMENT '退款金额',
  `sum_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上一次结算时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '下班/换班备注',
  `project_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_box_id`(`box_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '值班记录表' ROW_FORMAT = Dynamic;

CREATE TABLE `boxservice`.`box_duty_record_pay`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `duty_record_id` bigint NOT NULL COMMENT '值班记录ID',
  `pay_channel_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道',
  `actual_amount` int NOT NULL COMMENT '实收金额',
  `num` int NOT NULL DEFAULT 0 COMMENT '支付笔数',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_duty_record_id`(`duty_record_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 79 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '值班记录支付统计表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS=1;