-- 车管家4S店车辆维保管理系统数据库设计
-- Database: car_maintenance_db

CREATE DATABASE IF NOT EXISTS car_maintenance_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE car_maintenance_db;

-- 1. 用户表 (users)
CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码(加密)',
    real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
    phone VARCHAR(20) NOT NULL COMMENT '联系电话',
    email VARCHAR(100) COMMENT '电子邮箱',
    role ENUM('admin', 'staff', 'customer') NOT NULL COMMENT '角色:管理员/工作人员/客户',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-禁用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 2. 客户信息表 (customers)
CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
    user_id INT NOT NULL COMMENT '关联用户ID',
    customer_no VARCHAR(50) UNIQUE COMMENT '客户编号',
    id_card VARCHAR(18) COMMENT '身份证号',
    address VARCHAR(200) COMMENT '联系地址',
    gender ENUM('male', 'female', 'other') COMMENT '性别',
    birth_date DATE COMMENT '出生日期',
    membership_level ENUM('normal', 'silver', 'gold', 'platinum') DEFAULT 'normal' COMMENT '会员等级',
    points INT DEFAULT 0 COMMENT '积分',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_customer_no (customer_no),
    INDEX idx_membership (membership_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户信息表';

-- 3. 车辆档案表 (vehicles)
CREATE TABLE IF NOT EXISTS vehicles (
    vehicle_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '车辆ID',
    customer_id INT NOT NULL COMMENT '车主客户ID',
    license_plate VARCHAR(20) NOT NULL UNIQUE COMMENT '车牌号',
    brand VARCHAR(50) NOT NULL COMMENT '品牌',
    model VARCHAR(50) NOT NULL COMMENT '型号',
    color VARCHAR(20) COMMENT '颜色',
    vin VARCHAR(17) UNIQUE COMMENT '车架号',
    engine_no VARCHAR(50) COMMENT '发动机号',
    purchase_date DATE COMMENT '购买日期',
    mileage DECIMAL(10,2) DEFAULT 0 COMMENT '当前里程(公里)',
    last_maintenance_date DATE COMMENT '上次保养日期',
    next_maintenance_date DATE COMMENT '下次保养日期',
    status ENUM('normal', 'in_service', 'completed') DEFAULT 'normal' COMMENT '状态',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    INDEX idx_license_plate (license_plate),
    INDEX idx_customer (customer_id),
    INDEX idx_vin (vin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆档案表';

-- 4. 维保工单表 (service_orders)
CREATE TABLE IF NOT EXISTS service_orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '工单ID',
    order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '工单编号',
    vehicle_id INT NOT NULL COMMENT '车辆ID',
    customer_id INT NOT NULL COMMENT '客户ID',
    service_type ENUM('maintenance', 'repair', 'beauty', 'insurance') NOT NULL COMMENT '服务类型',
    appointment_time DATETIME COMMENT '预约时间',
    arrival_time DATETIME COMMENT '到店时间',
    start_time DATETIME COMMENT '开始时间',
    complete_time DATETIME COMMENT '完成时间',
    staff_id INT COMMENT '负责员工ID',
    current_mileage DECIMAL(10,2) COMMENT '当前里程',
    fault_description TEXT COMMENT '故障描述',
    diagnosis_result TEXT COMMENT '诊断结果',
    service_items TEXT COMMENT '服务项目(JSON格式)',
    parts_cost DECIMAL(10,2) DEFAULT 0 COMMENT '配件费用',
    labor_cost DECIMAL(10,2) DEFAULT 0 COMMENT '工时费用',
    total_cost DECIMAL(10,2) DEFAULT 0 COMMENT '总费用',
    status ENUM('pending', 'appointed', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending' COMMENT '工单状态',
    payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid' COMMENT '支付状态',
    remark TEXT COMMENT '备注',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_order_no (order_no),
    INDEX idx_vehicle (vehicle_id),
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_appointment (appointment_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='维保工单表';

-- 5. 配件库存表 (parts_inventory)
CREATE TABLE IF NOT EXISTS parts_inventory (
    part_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '配件ID',
    part_no VARCHAR(50) NOT NULL UNIQUE COMMENT '配件编号',
    part_name VARCHAR(100) NOT NULL COMMENT '配件名称',
    category VARCHAR(50) COMMENT '配件类别',
    brand VARCHAR(50) COMMENT '品牌',
    model VARCHAR(100) COMMENT '适用车型',
    unit VARCHAR(20) DEFAULT '个' COMMENT '单位',
    unit_price DECIMAL(10,2) NOT NULL COMMENT '单价',
    stock_quantity INT DEFAULT 0 COMMENT '库存数量',
    min_stock INT DEFAULT 10 COMMENT '最小库存预警',
    supplier VARCHAR(100) COMMENT '供应商',
    warehouse_location VARCHAR(50) COMMENT '仓库位置',
    status TINYINT DEFAULT 1 COMMENT '状态:1-正常,0-停用',
    remark TEXT COMMENT '备注',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_part_no (part_no),
    INDEX idx_category (category),
    INDEX idx_stock (stock_quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配件库存表';

-- 6. 配件使用记录表 (parts_usage)
CREATE TABLE IF NOT EXISTS parts_usage (
    usage_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '使用记录ID',
    order_id INT NOT NULL COMMENT '工单ID',
    part_id INT NOT NULL COMMENT '配件ID',
    quantity INT NOT NULL COMMENT '使用数量',
    unit_price DECIMAL(10,2) NOT NULL COMMENT '单价',
    total_price DECIMAL(10,2) NOT NULL COMMENT '总价',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    FOREIGN KEY (order_id) REFERENCES service_orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (part_id) REFERENCES parts_inventory(part_id) ON DELETE CASCADE,
    INDEX idx_order (order_id),
    INDEX idx_part (part_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配件使用记录表';

-- 7. 预约记录表 (appointments)
CREATE TABLE IF NOT EXISTS appointments (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '预约ID',
    customer_id INT NOT NULL COMMENT '客户ID',
    vehicle_id INT NOT NULL COMMENT '车辆ID',
    service_type ENUM('maintenance', 'repair', 'beauty', 'insurance') NOT NULL COMMENT '服务类型',
    appointment_time DATETIME NOT NULL COMMENT '预约时间',
    contact_phone VARCHAR(20) NOT NULL COMMENT '联系电话',
    description TEXT COMMENT '预约说明',
    status ENUM('pending', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending' COMMENT '预约状态',
    order_id INT COMMENT '关联工单ID',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES service_orders(order_id) ON DELETE SET NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_appointment_time (appointment_time),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预约记录表';

-- 8. 服务项目表 (service_items)
CREATE TABLE IF NOT EXISTS service_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '服务项目ID',
    item_code VARCHAR(50) NOT NULL UNIQUE COMMENT '项目编码',
    item_name VARCHAR(100) NOT NULL COMMENT '项目名称',
    category VARCHAR(50) COMMENT '项目类别',
    standard_price DECIMAL(10,2) NOT NULL COMMENT '标准价格',
    duration INT COMMENT '标准工时(分钟)',
    description TEXT COMMENT '项目描述',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-停用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_item_code (item_code),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='服务项目表';

-- 9. 系统日志表 (system_logs)
CREATE TABLE IF NOT EXISTS system_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    user_id INT COMMENT '用户ID',
    operation VARCHAR(100) NOT NULL COMMENT '操作类型',
    module VARCHAR(50) COMMENT '模块名称',
    method VARCHAR(200) COMMENT '方法名',
    params TEXT COMMENT '请求参数',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user (user_id),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';