-- Create vehicles table if it doesn't exist
CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_identifier` varchar(50) NOT NULL UNIQUE,
  `make` varchar(50) NOT NULL,
  `model` varchar(50) NOT NULL,
  `year` int(4) NOT NULL,
  `license_plate` varchar(20) NOT NULL,
  `status` enum('active','inactive','maintenance','retired') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data if the table is empty
INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2023-A', 'Ford', 'F-150', 2023, 'ABC-1234', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2023-A');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2023-B', 'Chevrolet', 'Silverado', 2023, 'DEF-5678', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2023-B');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2023-C', 'Ram', '1500', 2023, 'GHI-9012', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2023-C');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2023-D', 'Toyota', 'Tundra', 2023, 'JKL-3456', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2023-D');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'VAN-2023-A', 'Ford', 'Transit', 2023, 'MNO-7890', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'VAN-2023-A');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'VAN-2023-B', 'Mercedes-Benz', 'Sprinter', 2023, 'PQR-1234', 'active'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'VAN-2023-B');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2022-A', 'Ford', 'F-250', 2022, 'STU-5678', 'maintenance'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2022-A');

INSERT INTO `vehicles` (`vehicle_identifier`, `make`, `model`, `year`, `license_plate`, `status`)
SELECT 'TRK-2022-B', 'Chevrolet', 'Colorado', 2022, 'VWX-9012', 'inactive'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `vehicles` WHERE `vehicle_identifier` = 'TRK-2022-B');
