-- SQL to create order_department_assignments table
-- This table stores department assignments to orders (instead of individual user assignments)

CREATE TABLE IF NOT EXISTS `order_department_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `assigned_by` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_department_unique` (`order_id`, `department_id`),
  KEY `order_department_index` (`order_id`, `department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Add foreign key constraints if your tables support them
-- ALTER TABLE `order_department_assignments`
--   ADD CONSTRAINT `fk_order_dept_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE,
--   ADD CONSTRAINT `fk_order_dept_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE CASCADE;