-- Create dispatch_assignments table if it doesn't exist
CREATE TABLE IF NOT EXISTS `dispatch_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignment_identifier` varchar(50) NOT NULL UNIQUE, -- e.g., A001
  `driver_id` int(11) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `type` enum('Delivery','Pickup','Transfer','Maintenance') NOT NULL,
  `origin_location` varchar(255) NOT NULL,
  `destination_location` varchar(255) NOT NULL,
  `status` enum('Pending','Active','Completed', 'Cancelled') DEFAULT 'Pending',
  `scheduled_time` datetime DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `completion_time` datetime DEFAULT NULL,
  `dispatcher_id` int(11) DEFAULT NULL, -- User who created assignment
  `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 `assignments_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `assignments_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 `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A001', 5, 1, 'Delivery', 'Central Warehouse', 'Customer Site Alpha', 'Pending', '2023-10-25 09:00:00', NULL, NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A001');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A002', 6, 2, 'Pickup', 'Supplier Beta', 'Central Warehouse', 'Active', '2023-10-24 14:00:00', '2023-10-24 14:15:00', NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A002');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A003', 7, 3, 'Transfer', 'North Depot', 'South Depot', 'Active', '2023-10-24 10:30:00', '2023-10-24 10:45:00', NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A003');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A004', 8, 4, 'Maintenance', 'East Facility', 'Service Center', 'Completed', '2023-10-23 08:00:00', '2023-10-23 08:10:00', '2023-10-23 11:45:00', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A004');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A005', 5, 1, 'Delivery', 'Central Warehouse', 'Customer Site Delta', 'Pending', '2023-10-25 13:30:00', NULL, NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A005');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A006', 6, 2, 'Pickup', 'Customer Site Gamma', 'Central Warehouse', 'Completed', '2023-10-22 15:00:00', '2023-10-22 15:05:00', '2023-10-22 16:30:00', 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A006');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A007', 7, 3, 'Delivery', 'Central Warehouse', 'Customer Site Epsilon', 'Pending', '2023-10-26 09:15:00', NULL, NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A007');

INSERT INTO `dispatch_assignments` (`assignment_identifier`, `driver_id`, `vehicle_id`, `type`, `origin_location`, `destination_location`, `status`, `scheduled_time`, `start_time`, `completion_time`, `dispatcher_id`)
SELECT 'A008', 8, 4, 'Transfer', 'West Depot', 'East Facility', 'Cancelled', '2023-10-23 14:00:00', NULL, NULL, 4
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM `dispatch_assignments` WHERE `assignment_identifier` = 'A008');
