shanxia/plan/vehicle_management.sql

/*
 Navicat Premium Dump SQL

 Source Server         : 47.99.164.66  hn02le.34lkdLKD
 Source Server Type    : MySQL
 Source Server Version : 50736 (5.7.36)
 Source Host           : 47.99.164.66:3306
 Source Schema         : vehicle_management

 Target Server Type    : MySQL
 Target Server Version : 50736 (5.7.36)
 File Encoding         : 65001

 Date: 05/05/2026 10:14:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for certificates
-- ----------------------------
DROP TABLE IF EXISTS `certificates`;
CREATE TABLE `certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `certificate_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '证件类型: driving_license(驾驶证), escort_license(押运证)等',
  `file_url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件URL',
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '原文件名',
  `expiry_date` date DEFAULT NULL COMMENT '证件过期日期',
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_certificates_expiry` (`expiry_date`),
  CONSTRAINT `certificates_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='证件表';

-- ----------------------------
-- Records of certificates
-- ----------------------------
BEGIN;
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (5, 11, 'driving_license', 'http://localhost:8000/files/uploads/general/2026/01/15/7cbec2b6-6bef-4b57-90fc-b85460f99fd8.png', 'Snipaste_2025-12-27_16-03-25.png', '2026-01-21', '2026-01-15 16:50:44', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (6, 12, 'driving_license', 'http://121.43.228.102:9000/clgl/ed678c29-239d-46d4-a03b-75d1bbca6d1c.png', 'Snipaste_2025-12-27_16-03-21.png', '2026-02-04', '2026-01-15 17:05:56', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (7, 12, 'id_card', 'http://121.43.228.102:9000/clgl/4dbae2be-ab0b-43ca-b7f1-d9f1cdf9bbc1.png', 'Snipaste_2025-12-27_16-03-11.png', '2025-12-30', '2026-01-15 17:06:14', 1);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (8, 12, 'escort_license', 'http://121.43.228.102:9000/clgl/6f20a27a-7399-47f3-aa23-5e30d74f507b.png', 'Snipaste_2025-12-27_16-03-06.png', '2026-01-14', '2026-01-15 17:10:05', 1);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (9, 12, 'id_card', 'http://121.43.228.102:9000/clgl/736349d7-9443-4677-aacf-8ced1396c14c.png', 'Snipaste_2025-12-27_16-03-33.png', '2026-01-21', '2026-01-15 17:10:13', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (10, 12, 'id_card', 'http://121.43.228.102:9000/clgl/5e3203df-b0c3-45d5-a6b2-0a83c6ea7cda.png', 'Snipaste_2025-12-27_16-03-21.png', '2025-12-30', '2026-01-15 17:10:40', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (11, 12, 'qualification_certificate', 'http://121.43.228.102:9000/clgl/3b3bf250-62ab-42b9-920e-79401538ead2.png', 'Snipaste_2025-12-27_16-03-17.png', '2025-12-30', '2026-01-15 17:10:47', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (12, 12, 'escort_license', 'http://121.43.228.102:9000/clgl/bb416d90-8ce1-43bb-841d-ff86bb192190.png', 'Snipaste_2025-12-27_16-03-21.png', '2026-01-14', '2026-01-15 17:10:56', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (13, 9, 'driving_license', 'http://121.43.228.102:9000/clgl/ac003234-a437-4a18-92cf-5260ac71ec07.png', 'Snipaste_2025-12-27_16-03-33.png', '2026-01-14', '2026-01-15 22:53:09', 0);
INSERT INTO `certificates` (`id`, `user_id`, `certificate_type`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (14, 10, 'escort_license', 'http://121.43.228.102:9000/clgl/4907c641-d10e-4016-a517-65d1824df918.png', 'Snipaste_2025-12-27_16-03-21.png', '2025-12-31', '2026-01-15 22:53:21', 0);
COMMIT;

-- ----------------------------
-- Table structure for manufacturers
-- ----------------------------
DROP TABLE IF EXISTS `manufacturers`;
CREATE TABLE `manufacturers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '厂商名称',
  `safety_notice` text COLLATE utf8mb4_unicode_ci COMMENT '厂商安全须知',
  `address` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '厂商地址',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `idx_name` (`name`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='厂商表';

-- ----------------------------
-- Records of manufacturers
-- ----------------------------
BEGIN;
INSERT INTO `manufacturers` (`id`, `name`, `safety_notice`, `address`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (1, '山峡水泥厂', '注意安全，戴好安全帽，禁止在装卸区吸烟', '山峡市工业区A区', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `manufacturers` (`id`, `name`, `safety_notice`, `address`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (2, '山峡钢材厂', '注意防火，禁止明火，穿戴好防护装备', '山峡市工业区B区', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `manufacturers` (`id`, `name`, `safety_notice`, `address`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (3, '山峡物流中心', '注意交通安全，服从指挥', '山峡市物流园区', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `manufacturers` (`id`, `name`, `safety_notice`, `address`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (4, 'a s d f da s', '<h1><span style=\"color: rgb(225, 60, 57);\">123123123123123123213</span></h1><p><br></p><p>1额啊范德萨范德萨</p>', '啊是否撒点发三顿饭撒的范德萨发撒点饭撒的饭都是法师123123123123', 1, '2026-01-16 00:20:08', '2026-01-16 00:20:24', 1);
COMMIT;

-- ----------------------------
-- Table structure for plan_photos
-- ----------------------------
DROP TABLE IF EXISTS `plan_photos`;
CREATE TABLE `plan_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_id` int(11) NOT NULL COMMENT '计划ID',
  `photo_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '照片类型: loading_arrival(装货到场), loading_departure(装货离场), unloading_arrival(卸货到场), unloading_departure(卸货离场), waybill(运单)',
  `file_url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件URL',
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '原文件名',
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_plan_id` (`plan_id`),
  CONSTRAINT `plan_photos_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `vehicle_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='计划照片表';

-- ----------------------------
-- Records of plan_photos
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商品名称',
  `specification` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商品规格',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品表';

-- ----------------------------
-- Records of products
-- ----------------------------
BEGIN;
INSERT INTO `products` (`id`, `name`, `specification`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (1, '水泥', '425号普通硅酸盐水泥', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `products` (`id`, `name`, `specification`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (2, '钢筋', '直径12mm HRB400钢筋', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `products` (`id`, `name`, `specification`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (3, '砂石', '中砂（细度模数2.3-1.6）', 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `products` (`id`, `name`, `specification`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (4, '混凝土', 'C30商品混凝土', 1, '2026-01-13 07:14:49', '2026-01-16 00:33:18', 1);
INSERT INTO `products` (`id`, `name`, `specification`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (5, '12344', '12344', 0, '2026-01-16 00:36:27', '2026-01-16 00:36:34', 1);
COMMIT;

-- ----------------------------
-- Table structure for schema_version
-- ----------------------------
DROP TABLE IF EXISTS `schema_version`;
CREATE TABLE `schema_version` (
  `version` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '数据库版本',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '版本描述',
  `applied_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '应用时间',
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of schema_version
-- ----------------------------
BEGIN;
INSERT INTO `schema_version` (`version`, `description`, `applied_at`) VALUES ('2.0.0', '添加证件过期时间管理功能，完善车辆证件分类系统', '2026-01-13 07:14:49');
COMMIT;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '登录工号',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `role` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'operator' COMMENT '角色: operator(操作员), admin(管理员), driver(司机), escort(押运员)',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号码（司机、押运员必填）',
  `id_card` varchar(18) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '身份证号（司机、押运员必填）',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`),
  KEY `idx_role` (`role`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (1, 'admin', 'admin123', 'admin', '系统管理员', NULL, NULL, 1, '2026-01-13 07:14:49', '2026-01-13 07:14:49', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (2, 'testuser1768458386083', '123456', 'operator', '测试用户', NULL, NULL, 1, '2026-01-15 14:26:26', '2026-01-15 14:26:26', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (3, 'testuser1768458429763', '123456', 'operator', '测试用户', NULL, NULL, 1, '2026-01-15 14:27:09', '2026-01-15 14:27:09', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (7, 'test422', '123456', 'operator', '测试422', '13877711221', NULL, 1, '2026-01-15 14:37:18', '2026-01-15 14:37:18', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (8, 'czy03', '123456', 'operator', '操作员03', '13899001122', NULL, 1, '2026-01-15 14:39:18', '2026-01-15 14:39:18', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (9, '1231', '123456', 'escort', '王五', '13899112232', '340822198111221122', 1, '2026-01-15 14:43:18', '2026-01-15 19:01:50', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (10, '3', '123456', 'driver', 'lisan', '13877112211', '340822198811223344', 1, '2026-01-15 14:43:41', '2026-01-15 15:09:56', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (11, '4', '123456', 'operator', '4', '13511223311', NULL, 1, '2026-01-15 14:49:14', '2026-01-15 14:49:14', 0);
INSERT INTO `users` (`id`, `username`, `password`, `role`, `name`, `phone`, `id_card`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (12, '5', '123456', 'operator', '5', '13611332211', NULL, 0, '2026-01-15 14:49:23', '2026-01-15 17:19:18', 0);
COMMIT;

-- ----------------------------
-- Table structure for vehicle_certificates
-- ----------------------------
DROP TABLE IF EXISTS `vehicle_certificates`;
CREATE TABLE `vehicle_certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_id` int(11) NOT NULL COMMENT '车辆ID',
  `certificate_category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '证件类别: license(车牌), registration(行驶证), transport(运输证), insurance(保险), inspection(罐检)',
  `vehicle_part` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '车辆部位: head(车头), tail(车尾), tank(罐体)',
  `file_url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文件URL',
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '原文件名',
  `expiry_date` date DEFAULT NULL COMMENT '证件过期日期',
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_vehicle_id` (`vehicle_id`),
  KEY `idx_vehicle_certificates_expiry` (`expiry_date`),
  KEY `idx_vehicle_certificates_part_category` (`vehicle_part`,`certificate_category`),
  CONSTRAINT `vehicle_certificates_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆证件表';

-- ----------------------------
-- Records of vehicle_certificates
-- ----------------------------
BEGIN;
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (1, 1, 'license', 'head', 'http://121.43.228.102:9000/clgl/77ccdee8-1e0e-47fc-a34f-d9d06574a335.png', 'Snipaste_2025-12-27_16-03-25.png', '2026-01-13', '2026-01-15 20:00:52', 1);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (2, 1, 'registration', 'head', 'http://example.com/test.pdf', 'test.pdf', '2025-12-31', '2026-01-15 20:01:02', 1);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (3, 1, 'registration', 'head', 'http://121.43.228.102:9000/clgl/40194dc8-62f9-49eb-9e13-b5a7fefb9ed0.png', 'Snipaste_2025-12-27_16-03-25.png', '2026-01-21', '2026-01-15 20:28:36', 0);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (4, 1, 'inspection', 'head', 'http://121.43.228.102:9000/clgl/56f3c3a2-355f-4ffb-b59a-63db99d82ad3.png', 'Snipaste_2025-12-27_16-03-21.png', '2026-01-06', '2026-01-15 20:32:47', 1);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (5, 1, 'insurance', 'head', 'http://121.43.228.102:9000/clgl/350bad8b-ce21-42f4-913c-b279a0347f92.png', 'Snipaste_2025-12-27_16-03-33.png', '2025-12-30', '2026-01-15 20:33:01', 1);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (6, 1, 'registration', 'head', 'http://121.43.228.102:9000/clgl/b799d8e6-8b94-439a-8402-1fc11016b90e.png', 'Snipaste_2025-12-27_16-03-25.png', '2025-12-30', '2026-01-15 20:52:21', 1);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (7, 1, 'transport', 'tail', 'http://121.43.228.102:9000/clgl/86b2b5f4-938f-4bac-9621-eebcc5522b08.png', 'Snipaste_2025-12-27_16-03-25.png', '2026-01-28', '2026-01-15 21:28:56', 0);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (8, 1, 'registration', 'tail', 'http://121.43.228.102:9000/clgl/9468380e-9f5d-44a3-ba22-b5805570e8bc.png', 'Snipaste_2025-12-27_16-03-17.png', '2026-01-07', '2026-01-15 21:29:10', 0);
INSERT INTO `vehicle_certificates` (`id`, `vehicle_id`, `certificate_category`, `vehicle_part`, `file_url`, `file_name`, `expiry_date`, `uploaded_at`, `is_deleted`) VALUES (9, 1, 'license', 'tail', 'http://121.43.228.102:9000/clgl/b17330dc-1c32-4408-8f2d-bbcae00a3046.jpg', 'avatar.jpg', '2026-01-22', '2026-01-15 21:29:21', 0);
COMMIT;

-- ----------------------------
-- Table structure for vehicle_heads
-- ----------------------------
DROP TABLE IF EXISTS `vehicle_heads`;
CREATE TABLE `vehicle_heads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plate_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `category` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'owned' COMMENT '车辆分类: owned(自有车辆), other(其他车辆)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `plate_number` (`plate_number`),
  KEY `idx_plate_number` (`plate_number`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车头表';

-- ----------------------------
-- Records of vehicle_heads
-- ----------------------------
BEGIN;
INSERT INTO `vehicle_heads` (`id`, `plate_number`, `is_active`, `is_deleted`, `created_at`, `updated_at`, `category`) VALUES (1, '皖P13231', 1, 0, '2026-01-15 19:18:40', '2026-01-15 19:18:40', 'owned');
INSERT INTO `vehicle_heads` (`id`, `plate_number`, `is_active`, `is_deleted`, `created_at`, `updated_at`, `category`) VALUES (2, '皖D13223', 1, 1, '2026-01-15 21:16:13', '2026-01-15 21:30:24', 'owned');
INSERT INTO `vehicle_heads` (`id`, `plate_number`, `is_active`, `is_deleted`, `created_at`, `updated_at`, `category`) VALUES (3, '皖S12222', 1, 0, '2026-01-15 22:28:32', '2026-01-15 22:28:32', 'owned');
COMMIT;

-- ----------------------------
-- Table structure for vehicle_plans
-- ----------------------------
DROP TABLE IF EXISTS `vehicle_plans`;
CREATE TABLE `vehicle_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plan_date` date NOT NULL COMMENT '计划日期',
  `start_time` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '开始时间 (HH:MM格式)',
  `end_time` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '结束时间 (HH:MM格式)',
  `customer_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '客户名称',
  `product_id` int(11) NOT NULL COMMENT '商品ID',
  `quantity` float NOT NULL COMMENT '数量',
  `unit` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '单位',
  `loading_manufacturer_id` int(11) NOT NULL COMMENT '装货厂商ID',
  `unloading_manufacturer_id` int(11) NOT NULL COMMENT '卸货厂商ID',
  `vehicle_id` int(11) NOT NULL COMMENT '车辆ID',
  `driver_id` int(11) NOT NULL COMMENT '司机ID',
  `escort_id` int(11) NOT NULL COMMENT '押运员ID',
  `loading_arrival_time` datetime DEFAULT NULL COMMENT '装货到场时间',
  `loading_departure_time` datetime DEFAULT NULL COMMENT '装货离场时间',
  `unloading_arrival_time` datetime DEFAULT NULL COMMENT '卸货到场时间',
  `unloading_departure_time` datetime DEFAULT NULL COMMENT '卸货离场时间',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending' COMMENT '状态: pending(待开始), loading(装货中), unloading(卸货中), completed(已完成)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_plan_date` (`plan_date`),
  KEY `idx_status` (`status`),
  KEY `idx_driver_id` (`driver_id`),
  KEY `idx_escort_id` (`escort_id`),
  KEY `product_id` (`product_id`),
  KEY `loading_manufacturer_id` (`loading_manufacturer_id`),
  KEY `unloading_manufacturer_id` (`unloading_manufacturer_id`),
  KEY `vehicle_id` (`vehicle_id`),
  KEY `idx_vehicle_plans_date_status` (`plan_date`,`status`),
  KEY `idx_vehicle_plans_driver_escort` (`driver_id`,`escort_id`),
  CONSTRAINT `vehicle_plans_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `vehicle_plans_ibfk_2` FOREIGN KEY (`loading_manufacturer_id`) REFERENCES `manufacturers` (`id`),
  CONSTRAINT `vehicle_plans_ibfk_3` FOREIGN KEY (`unloading_manufacturer_id`) REFERENCES `manufacturers` (`id`),
  CONSTRAINT `vehicle_plans_ibfk_4` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`),
  CONSTRAINT `vehicle_plans_ibfk_5` FOREIGN KEY (`driver_id`) REFERENCES `users` (`id`),
  CONSTRAINT `vehicle_plans_ibfk_6` FOREIGN KEY (`escort_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆计划表';

-- ----------------------------
-- Records of vehicle_plans
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for vehicle_tails
-- ----------------------------
DROP TABLE IF EXISTS `vehicle_tails`;
CREATE TABLE `vehicle_tails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plate_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `category` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'owned' COMMENT '车辆分类: owned(自有车辆), other(其他车辆)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `plate_number` (`plate_number`),
  KEY `idx_plate_number` (`plate_number`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车尾表';

-- ----------------------------
-- Records of vehicle_tails
-- ----------------------------
BEGIN;
INSERT INTO `vehicle_tails` (`id`, `plate_number`, `is_active`, `is_deleted`, `created_at`, `updated_at`, `category`) VALUES (1, '皖A09871', 0, 0, '2026-01-15 19:18:41', '2026-01-15 21:29:50', 'owned');
INSERT INTO `vehicle_tails` (`id`, `plate_number`, `is_active`, `is_deleted`, `created_at`, `updated_at`, `category`) VALUES (2, '皖A12333', 1, 0, '2026-01-15 22:28:39', '2026-01-15 22:28:39', 'owned');
COMMIT;

-- ----------------------------
-- Table structure for vehicles
-- ----------------------------
DROP TABLE IF EXISTS `vehicles`;
CREATE TABLE `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类: owned(自有车辆), other(其他车辆)',
  `head_id` int(11) DEFAULT NULL,
  `tail_id` int(11) DEFAULT NULL,
  `default_driver_id` int(11) DEFAULT NULL COMMENT '默认司机ID',
  `default_escort_id` int(11) DEFAULT NULL COMMENT '默认押运员ID',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除: 0-正常, 1-删除',
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_is_active` (`is_active`),
  KEY `default_driver_id` (`default_driver_id`),
  KEY `default_escort_id` (`default_escort_id`),
  KEY `fk_vehicle_head` (`head_id`),
  KEY `fk_vehicle_tail` (`tail_id`),
  CONSTRAINT `fk_vehicle_head` FOREIGN KEY (`head_id`) REFERENCES `vehicle_heads` (`id`),
  CONSTRAINT `fk_vehicle_tail` FOREIGN KEY (`tail_id`) REFERENCES `vehicle_tails` (`id`) ON DELETE SET NULL,
  CONSTRAINT `vehicles_ibfk_1` FOREIGN KEY (`default_driver_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `vehicles_ibfk_2` FOREIGN KEY (`default_escort_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆表';

-- ----------------------------
-- Records of vehicles
-- ----------------------------
BEGIN;
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (1, 'owned', 1, 1, 10, 9, 1, '2026-01-15 19:02:06', '2026-01-15 22:35:59', 1);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (2, 'other', 1, 1, 10, 9, 1, '2026-01-15 22:28:10', '2026-01-15 22:43:35', 0);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (3, 'owned', 3, 2, 10, 9, 0, '2026-01-15 22:29:18', '2026-01-15 23:45:59', 0);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (4, 'owned', 1, 2, 10, 9, 1, '2026-01-15 22:33:46', '2026-01-15 22:33:46', 0);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (5, 'owned', 1, 2, 10, 9, 1, '2026-01-15 22:33:58', '2026-01-15 22:33:58', 0);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (6, 'owned', 1, 2, 10, 9, 1, '2026-01-15 22:35:36', '2026-01-15 22:35:36', 0);
INSERT INTO `vehicles` (`id`, `category`, `head_id`, `tail_id`, `default_driver_id`, `default_escort_id`, `is_active`, `created_at`, `updated_at`, `is_deleted`) VALUES (7, 'owned', 1, 2, 10, 9, 1, '2026-01-15 22:35:54', '2026-01-15 22:35:54', 0);
COMMIT;

-- ----------------------------
-- Triggers structure for table manufacturers
-- ----------------------------
DROP TRIGGER IF EXISTS `update_manufacturers_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_manufacturers_timestamp` BEFORE UPDATE ON `manufacturers` FOR EACH ROW BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table products
-- ----------------------------
DROP TRIGGER IF EXISTS `update_products_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_products_timestamp` BEFORE UPDATE ON `products` FOR EACH ROW BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table users
-- ----------------------------
DROP TRIGGER IF EXISTS `update_users_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_users_timestamp` BEFORE UPDATE ON `users` FOR EACH ROW BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table vehicle_heads
-- ----------------------------
DROP TRIGGER IF EXISTS `update_vehicle_heads_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_vehicle_heads_timestamp` BEFORE UPDATE ON `vehicle_heads` FOR EACH ROW SET NEW.updated_at = CURRENT_TIMESTAMP
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table vehicle_plans
-- ----------------------------
DROP TRIGGER IF EXISTS `update_vehicle_plans_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_vehicle_plans_timestamp` BEFORE UPDATE ON `vehicle_plans` FOR EACH ROW BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table vehicle_tails
-- ----------------------------
DROP TRIGGER IF EXISTS `update_vehicle_tails_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_vehicle_tails_timestamp` BEFORE UPDATE ON `vehicle_tails` FOR EACH ROW SET NEW.updated_at = CURRENT_TIMESTAMP
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table vehicles
-- ----------------------------
DROP TRIGGER IF EXISTS `update_vehicles_timestamp`;
delimiter ;;
CREATE TRIGGER `vehicle_management`.`update_vehicles_timestamp` BEFORE UPDATE ON `vehicles` FOR EACH ROW BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;