-- Create routes table if it doesn't exist
CREATE TABLE IF NOT EXISTS `routes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `route_identifier` varchar(50) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `status` enum('active','delayed','completed','cancelled') NOT NULL DEFAULT 'active',
  `current_location` varchar(255) DEFAULT NULL,
  `eta` varchar(50) DEFAULT NULL,
  `destination` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `vehicle_id` (`vehicle_id`),
  KEY `driver_id` (`driver_id`),
  CONSTRAINT `routes_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `routes_ibfk_2` FOREIGN KEY (`driver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data if the table is empty
INSERT INTO `routes` (`route_identifier`, `vehicle_id`, `driver_id`, `status`, `current_location`, `eta`, `destination`)
SELECT 'R001', 1, 5, 'active', 'Highway 101, Mile 45', '35 minutes', 'Central Warehouse'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `routes` WHERE `route_identifier` = 'R001');

INSERT INTO `routes` (`route_identifier`, `vehicle_id`, `driver_id`, `status`, `current_location`, `eta`, `destination`)
SELECT 'R002', 2, 6, 'delayed', 'Main Street Construction Zone', '1 hour 15 minutes', 'North Distribution Center'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `routes` WHERE `route_identifier` = 'R002');

INSERT INTO `routes` (`route_identifier`, `vehicle_id`, `driver_id`, `status`, `current_location`, `eta`, `destination`)
SELECT 'R003', 3, 7, 'active', 'Interstate 5, Mile 78', '20 minutes', 'South Farm Supply'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `routes` WHERE `route_identifier` = 'R003');

INSERT INTO `routes` (`route_identifier`, `vehicle_id`, `driver_id`, `status`, `current_location`, `eta`, `destination`)
SELECT 'R004', 4, 8, 'completed', 'East Distribution Center', 'Arrived', 'East Distribution Center'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `routes` WHERE `route_identifier` = 'R004');

INSERT INTO `routes` (`route_identifier`, `vehicle_id`, `driver_id`, `status`, `current_location`, `eta`, `destination`)
SELECT 'R005', 5, 9, 'active', 'Rural Route 7', '45 minutes', 'Western Farm Cooperative'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `routes` WHERE `route_identifier` = 'R005');
