tnc-be/db/增量SQL/bill_tables.sql

-- ----------------------------
-- 账单数据发送功能相关表
-- ----------------------------

-- ----------------------------
-- Table structure for bill_batch
-- ----------------------------
DROP TABLE IF EXISTS `bill_batch`;
CREATE TABLE `bill_batch`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `batch_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '批次号',
  `send_rule_type` tinyint(1) NULL DEFAULT NULL COMMENT '发送规则类型：1-固定年月日，2-每月几日',
  `send_date` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送日期，固定年月日时存储具体日期，每月几日时存储日期数字1-31',
  `sender_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送人邮箱',
  `sender_user_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送人用户ID',
  `customer_count` int(11) NULL DEFAULT 0 COMMENT '客户总数',
  `sent_count` int(11) NULL DEFAULT 0 COMMENT '已发送条数',
  `import_time` datetime NULL DEFAULT NULL COMMENT '导入时间',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uniq_bill_batch_no`(`batch_no`) USING BTREE,
  INDEX `idx_bill_batch_sender`(`sender_user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '账单批次表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for bill_customer
-- ----------------------------
DROP TABLE IF EXISTS `bill_customer`;
CREATE TABLE `bill_customer`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `batch_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '批次ID',
  `serial_no` int(11) NULL DEFAULT NULL COMMENT '序号',
  `customer_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '客户名称',
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '邮箱',
  `contract_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '合同号',
  `developer_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '发展人邮箱',
  `bill_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '账单种类',
  `new_manager` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新客户经理',
  `contact_person` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系电话',
  `new_developer` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '新业务发展人',
  `send_date` date NULL DEFAULT NULL COMMENT '发送日期',
  `send_status` tinyint(1) NULL DEFAULT 0 COMMENT '发送状态：0-未发送，1-发送成功，2-发送失败',
  `send_time` datetime NULL DEFAULT NULL COMMENT '发送时间',
  `send_result` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送结果描述',
  `error_msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '错误信息',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_bill_customer_batch`(`batch_id`) USING BTREE,
  INDEX `idx_bill_customer_contract`(`contract_no`) USING BTREE,
  INDEX `idx_bill_customer_status`(`send_status`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '账单客户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for bill_common_file
-- ----------------------------
DROP TABLE IF EXISTS `bill_common_file`;
CREATE TABLE `bill_common_file`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `file_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件名',
  `file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件路径/URL',
  `file_size` bigint(20) NULL DEFAULT NULL COMMENT '文件大小（字节）',
  `bill_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '账单种类，从文件名解析',
  `contract_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '合同号，从文件名解析',
  `date_str` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '日期字符串，从文件名解析，如202505',
  `upload_time` datetime NULL DEFAULT NULL COMMENT '上传时间',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_bill_file_contract`(`contract_no`) USING BTREE,
  INDEX `idx_bill_file_type`(`bill_type`) USING BTREE,
  INDEX `idx_bill_file_date`(`date_str`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '通用文件表' ROW_FORMAT = Dynamic;