-- 车管家4S店车辆维保管理系统 - 初始数据
-- 注意：密码使用明文存储仅用于演示，实际应使用加密(如BCrypt)

USE car_maintenance_db;

-- 1. 插入用户数据
INSERT INTO users (username, password, real_name, phone, email, role, status) VALUES
('admin', '123456', '系统管理员', '13800138000', 'admin@carmaintenance.com', 'admin', 1),
('staff001', '123456', '张维修', '13800138001', 'zhang@carmaintenance.com', 'staff', 1),
('staff002', '123456', '李技师', '13800138002', 'li@carmaintenance.com', 'staff', 1),
('staff003', '123456', '王顾问', '13800138003', 'wang@carmaintenance.com', 'staff', 1),
('customer001', '123456', '赵客户', '13900139001', 'zhao@example.com', 'customer', 1),
('customer002', '123456', '钱客户', '13900139002', 'qian@example.com', 'customer', 1),
('customer003', '123456', '孙客户', '13900139003', 'sun@example.com', 'customer', 1);

-- 2. 插入客户信息
INSERT INTO customers (user_id, customer_no, id_card, address, gender, birth_date, membership_level, points) VALUES
(5, 'C20250001', '110101199001011234', '北京市朝阳区建国路100号', 'male', '1990-01-01', 'gold', 5000),
(6, 'C20250002', '110101198505055678', '北京市海淀区中关村大街1号', 'female', '1985-05-05', 'silver', 2000),
(7, 'C20250003', '110101199207079012', '北京市东城区王府井大街50号', 'male', '1992-07-07', 'normal', 500);

-- 3. 插入车辆档案
INSERT INTO vehicles (customer_id, license_plate, brand, model, color, vin, engine_no, purchase_date, mileage, last_maintenance_date, next_maintenance_date, status) VALUES
(1, '京A12345', '奔驰', 'C200L', '黑色', 'LBVCU31J7DR123456', 'M274920123456', '2022-03-15', 25000.00, '2024-12-01', '2025-06-01', 'normal'),
(1, '京B88888', '宝马', 'X5', '白色', 'WBAJW5105BVZ12345', 'N20B20A1234567', '2021-06-20', 35000.00, '2024-11-15', '2025-05-15', 'normal'),
(2, '京C99999', '奥迪', 'A6L', '银色', 'LFVBA28K5D3123456', 'EA888123456789', '2023-01-10', 15000.00, '2024-12-20', '2025-06-20', 'normal'),
(3, '京D66666', '丰田', '凯美瑞', '蓝色', 'LVGBM53E5CG123456', '2AR-FE123456', '2022-08-05', 20000.00, '2024-10-10', '2025-04-10', 'normal');

-- 4. 插入服务项目
INSERT INTO service_items (item_code, item_name, category, standard_price, duration, description, status) VALUES
('M001', '小保养', '保养维护', 500.00, 60, '更换机油、机滤，常规检查', 1),
('M002', '大保养', '保养维护', 1200.00, 120, '更换机油、三滤，全面检查', 1),
('M003', '刹车系统检查', '检测诊断', 200.00, 30, '检查刹车片、刹车盘、制动液', 1),
('R001', '更换刹车片', '维修更换', 800.00, 90, '更换前/后刹车片', 1),
('R002', '更换电瓶', '维修更换', 600.00, 30, '更换蓄电池', 1),
('R003', '空调系统维修', '维修更换', 1500.00, 180, '空调检测、加氟、维修', 1),
('B001', '精致洗车', '美容服务', 80.00, 30, '外观清洗、内饰简单清理', 1),
('B002', '深度美容', '美容服务', 500.00, 180, '全车深度清洁、打蜡、内饰护理', 1),
('I001', '车辆年检代办', '保险代理', 300.00, 30, '代办车辆年检服务', 1),
('I002', '保险续保', '保险代理', 0.00, 30, '车辆保险续保服务', 1);

-- 5. 插入配件库存
INSERT INTO parts_inventory (part_no, part_name, category, brand, model, unit, unit_price, stock_quantity, min_stock, supplier, warehouse_location, status) VALUES
('P001', '机油滤清器', '滤清器', '曼牌', '通用型', '个', 45.00, 100, 20, '北京汽配公司', 'A-01', 1),
('P002', '空气滤清器', '滤清器', '曼牌', '通用型', '个', 60.00, 80, 15, '北京汽配公司', 'A-02', 1),
('P003', '空调滤清器', '滤清器', '博世', '通用型', '个', 80.00, 60, 15, '博世配件公司', 'A-03', 1),
('P004', '汽油滤清器', '滤清器', '马勒', '通用型', '个', 120.00, 50, 10, '马勒配件公司', 'A-04', 1),
('P005', '全合成机油5W-30', '润滑油', '美孚', '4L装', '桶', 380.00, 200, 30, '美孚授权经销商', 'B-01', 1),
('P006', '刹车片(前)', '制动系统', '博世', '奔驰C级专用', '套', 650.00, 30, 5, '博世配件公司', 'C-01', 1),
('P007', '刹车片(后)', '制动系统', '博世', '奔驰C级专用', '套', 550.00, 30, 5, '博世配件公司', 'C-02', 1),
('P008', '蓄电池12V60AH', '电气系统', '瓦尔塔', '通用型', '个', 500.00, 40, 8, '瓦尔塔授权商', 'D-01', 1),
('P009', '火花塞', '点火系统', 'NGK', '通用型', '个', 80.00, 100, 20, 'NGK配件公司', 'E-01', 1),
('P010', '雨刷片', '易损件', '博世', '通用型', '对', 120.00, 80, 15, '博世配件公司', 'F-01', 1);

-- 6. 插入预约记录
INSERT INTO appointments (customer_id, vehicle_id, service_type, appointment_time, contact_phone, description, status) VALUES
(1, 1, 'maintenance', '2025-01-10 09:00:00', '13900139001', '车辆到5000公里，需要小保养', 'confirmed'),
(2, 3, 'repair', '2025-01-12 14:00:00', '13900139002', '空调不制冷，需要检修', 'pending'),
(3, 4, 'beauty', '2025-01-15 10:00:00', '13900139003', '准备年检，需要深度清洗', 'pending');

-- 7. 插入维保工单
INSERT INTO service_orders (order_no, vehicle_id, customer_id, service_type, appointment_time, arrival_time, start_time, complete_time, staff_id, current_mileage, fault_description, diagnosis_result, service_items, parts_cost, labor_cost, total_cost, status, payment_status, remark) VALUES
('SO202501070001', 1, 1, 'maintenance', '2025-01-07 09:00:00', '2025-01-07 09:15:00', '2025-01-07 09:30:00', '2025-01-07 11:00:00', 2, 24500.00, '常规保养', '车辆状况良好，已完成小保养', '[{"code":"M001","name":"小保养","price":500}]', 545.00, 500.00, 1045.00, 'completed', 'paid', '客户满意'),
('SO202501070002', 3, 2, 'repair', '2025-01-07 14:00:00', '2025-01-07 14:10:00', '2025-01-07 14:30:00', NULL, 3, 15200.00, '发动机异响', '检查发现需要更换机油和机滤', '[{"code":"M001","name":"小保养","price":500}]', 425.00, 500.00, 925.00, 'in_progress', 'unpaid', '正在处理中'),
('SO202412200003', 2, 1, 'maintenance', '2024-12-20 10:00:00', '2024-12-20 10:20:00', '2024-12-20 10:30:00', '2024-12-20 13:00:00', 2, 34500.00, '大保养', '已完成大保养，更换三滤和机油', '[{"code":"M002","name":"大保养","price":1200}]', 1085.00, 1200.00, 2285.00, 'completed', 'paid', '服务优质');

-- 8. 插入配件使用记录
INSERT INTO parts_usage (order_id, part_id, quantity, unit_price, total_price) VALUES
(1, 1, 1, 45.00, 45.00),
(1, 5, 1, 380.00, 380.00),
(1, 10, 1, 120.00, 120.00),
(2, 1, 1, 45.00, 45.00),
(2, 5, 1, 380.00, 380.00),
(3, 1, 1, 45.00, 45.00),
(3, 2, 1, 60.00, 60.00),
(3, 3, 1, 80.00, 80.00),
(3, 4, 1, 120.00, 120.00),
(3, 5, 2, 380.00, 760.00),
(3, 9, 4, 20.00, 80.00);