-- Shipment Reservations & Allocations Migration
-- Creates shipment_allocations table and enhances stock_reservations

CREATE TABLE IF NOT EXISTS `shipment_allocations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `reservation_id` int unsigned NOT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `location_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned DEFAULT NULL,
  `allocated_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `picked_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `status` enum('allocated','picked','partially_picked','fulfilled','cancelled') NOT NULL DEFAULT 'allocated',
  `allocated_by` int unsigned DEFAULT NULL,
  `notes` text DEFAULT 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 `idx_sa_reservation` (`reservation_id`),
  KEY `idx_sa_stock` (`stock_id`),
  KEY `idx_sa_status` (`status`),
  KEY `idx_sa_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE stock_reservations ADD COLUMN `warehouse_id` int unsigned DEFAULT NULL AFTER `stock_id`;
ALTER TABLE stock_reservations ADD COLUMN `item_id` int unsigned DEFAULT NULL AFTER `warehouse_id`;
ALTER TABLE stock_reservations ADD COLUMN `color` varchar(100) DEFAULT NULL AFTER `item_id`;
ALTER TABLE stock_reservations ADD COLUMN `size` varchar(100) DEFAULT NULL AFTER `color`;
ALTER TABLE stock_reservations ADD COLUMN `lot_no` varchar(100) DEFAULT NULL AFTER `size`;
ALTER TABLE stock_reservations ADD COLUMN `batch_no` varchar(100) DEFAULT NULL AFTER `lot_no`;
ALTER TABLE stock_reservations ADD COLUMN `uom_id` int unsigned DEFAULT NULL AFTER `batch_no`;
ALTER TABLE stock_reservations ADD COLUMN `ship_date` date DEFAULT NULL AFTER `reserved_qty`;
ALTER TABLE stock_reservations ADD COLUMN `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal' AFTER `ship_date`;
ALTER TABLE stock_reservations ADD COLUMN `notes` text DEFAULT NULL AFTER `priority`;
ALTER TABLE stock_reservations ADD INDEX `idx_sr_warehouse` (`warehouse_id`);
ALTER TABLE stock_reservations ADD INDEX `idx_sr_item` (`item_id`);
ALTER TABLE stock_reservations ADD INDEX `idx_sr_priority` (`priority`);
