团队冲刺（个人博客）--第三篇

创建了数据库表单

1. 用户表 (user)
   sql
   CREATE TABLE user (
   user_id INT PRIMARY KEY AUTO_INCREMENT,
   username VARCHAR(50) NOT NULL UNIQUE,
   password VARCHAR(100) NOT NULL,
   real_name VARCHAR(50) NOT NULL,
   phone VARCHAR(20),
   email VARCHAR(100),
   role ENUM('admin', 'engineer', 'repair_level2') NOT NULL,
   status ENUM('active', 'inactive') DEFAULT 'active',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
2. 车站表 (station)
   sql
   CREATE TABLE station (
   station_id INT PRIMARY KEY AUTO_INCREMENT,
   station_name VARCHAR(100) NOT NULL,
   station_code VARCHAR(20) NOT NULL UNIQUE,
   location VARCHAR(200),
   status ENUM('active', 'inactive') DEFAULT 'active',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
3. 设备表 (equipment)
   sql
   CREATE TABLE equipment (
   equipment_id INT PRIMARY KEY AUTO_INCREMENT,
   equipment_name VARCHAR(100) NOT NULL,
   equipment_code VARCHAR(50) NOT NULL UNIQUE,
   equipment_type VARCHAR(50) NOT NULL,
   station_id INT NOT NULL,
   status ENUM('normal', 'abnormal', 'maintaining', 'scrapped') DEFAULT 'normal',
   install_date DATE,
   manufacturer VARCHAR(100),
   model VARCHAR(100),
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (station_id) REFERENCES station(station_id)
   );
4. 备件表 (spare_part)
   sql
   CREATE TABLE spare_part (
   part_id INT PRIMARY KEY AUTO_INCREMENT,
   part_name VARCHAR(100) NOT NULL,
   part_code VARCHAR(50) NOT NULL UNIQUE,
   part_type VARCHAR(50) NOT NULL,
   model VARCHAR(100),
   manufacturer VARCHAR(100),
   status ENUM('good', 'bad', 'in_use', 'scrapped') DEFAULT 'good',
   quantity INT DEFAULT 0,
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
5. 工程师表 (engineer)
   sql
   CREATE TABLE engineer (
   engineer_id INT PRIMARY KEY AUTO_INCREMENT,
   user_id INT NOT NULL,
   station_id INT,
   skill_level VARCHAR(50),
   certification VARCHAR(100),
   status ENUM('available', 'busy', 'offline') DEFAULT 'available',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (user_id) REFERENCES user(user_id),
   FOREIGN KEY (station_id) REFERENCES station(station_id)
   );
6. 报修单表 (fault_report)
   sql
   CREATE TABLE fault_report (
   report_id INT PRIMARY KEY AUTO_INCREMENT,
   report_code VARCHAR(50) NOT NULL UNIQUE,
   station_id INT NOT NULL,
   equipment_id INT NOT NULL,
   reporter_id INT NOT NULL,
   fault_type VARCHAR(50) NOT NULL,
   fault_level ENUM('low', 'medium', 'high', 'critical') NOT NULL,
   fault_description TEXT,
   status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (station_id) REFERENCES station(station_id),
   FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id),
   FOREIGN KEY (reporter_id) REFERENCES user(user_id)
   );
7. 维修单表 (repair_order)
   sql
   CREATE TABLE repair_order (
   order_id INT PRIMARY KEY AUTO_INCREMENT,
   order_code VARCHAR(50) NOT NULL UNIQUE,
   report_id INT NOT NULL,
   assignee_id INT NOT NULL,
   start_time DATETIME,
   end_time DATETIME,
   repair_description TEXT,
   status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (report_id) REFERENCES fault_report(report_id),
   FOREIGN KEY (assignee_id) REFERENCES user(user_id)
   );
8. 巡检单表 (inspection_order)
   sql
   CREATE TABLE inspection_order (
   order_id INT PRIMARY KEY AUTO_INCREMENT,
   order_code VARCHAR(50) NOT NULL UNIQUE,
   station_id INT NOT NULL,
   assignee_id INT,
   inspection_type VARCHAR(50) NOT NULL,
   planned_time DATETIME NOT NULL,
   start_time DATETIME,
   end_time DATETIME,
   status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (station_id) REFERENCES station(station_id),
   FOREIGN KEY (assignee_id) REFERENCES user(user_id)
   );
9. 保养单表 (maintenance_order)
   sql
   CREATE TABLE maintenance_order (
   order_id INT PRIMARY KEY AUTO_INCREMENT,
   order_code VARCHAR(50) NOT NULL UNIQUE,
   station_id INT NOT NULL,
   assignee_id INT,
   maintenance_type VARCHAR(50) NOT NULL,
   planned_time DATETIME NOT NULL,
   start_time DATETIME,
   end_time DATETIME,
   status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
   updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   FOREIGN KEY (station_id) REFERENCES station(station_id),
   FOREIGN KEY (assignee_id) REFERENCES user(user_id)
   );
10. 检修单表 (check_order)
    sql
    CREATE TABLE check_order (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_code VARCHAR(50) NOT NULL UNIQUE,
    station_id INT NOT NULL,
    assignee_id INT,
    check_type VARCHAR(50) NOT NULL,
    planned_time DATETIME NOT NULL,
    start_time DATETIME,
    end_time DATETIME,
    status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (station_id) REFERENCES station(station_id),
    FOREIGN KEY (assignee_id) REFERENCES user(user_id)
    );
11. 备件单表 (spare_part_order)
    sql
    CREATE TABLE spare_part_order (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_code VARCHAR(50) NOT NULL UNIQUE,
    part_id INT NOT NULL,
    assignee_id INT,
    fault_description TEXT,
    repair_description TEXT,
    status ENUM('pending', 'processing', 'completed', 'closed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (part_id) REFERENCES spare_part(part_id),
    FOREIGN KEY (assignee_id) REFERENCES user(user_id)
    );
12. 照片表 (photo) - 用于存储所有模块的照片
    sql
    CREATE TABLE photo (
    photo_id INT PRIMARY KEY AUTO_INCREMENT,
    related_type ENUM('fault_report', 'repair_order', 'inspection_order', 'maintenance_order', 'check_order', 'spare_part_order') NOT NULL,
    related_id INT NOT NULL,
    photo_url VARCHAR(255) NOT NULL,
    photo_time DATETIME NOT NULL,
    photo_type ENUM('before', 'after', 'during') NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    reject_reason VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
13. 时间线表 (timeline) - 用于记录所有工单的处理时间线
    sql
    CREATE TABLE timeline (
    timeline_id INT PRIMARY KEY AUTO_INCREMENT,
    related_type ENUM('fault_report', 'repair_order', 'inspection_order', 'maintenance_order', 'check_order', 'spare_part_order') NOT NULL,
    related_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    action_description TEXT,
    operator_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (operator_id) REFERENCES user(user_id)
    );