-- -----------------------------------------------------------
-- Transfer Requests module
-- Allows users to request stock transfers before they are executed
-- -----------------------------------------------------------

CREATE TABLE IF NOT EXISTS `transfer_requests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `request_no` varchar(50) NOT NULL,
  `from_warehouse_id` int unsigned NOT NULL,
  `to_warehouse_id` int unsigned NOT NULL,
  `requested_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `status` enum('draft','pending_approval','approved','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_request_no` (`request_no`, `company_id`),
  KEY `idx_tr_status` (`status`),
  KEY `idx_tr_company` (`company_id`),
  KEY `idx_tr_from_wh` (`from_warehouse_id`),
  KEY `idx_tr_to_wh` (`to_warehouse_id`),
  KEY `idx_tr_requested_by` (`requested_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfer_request_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `request_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT 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 NOT NULL,
  `requested_qty` decimal(14,4) NOT NULL,
  `approved_qty` decimal(14,4) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tri_request` (`request_id`),
  KEY `idx_tri_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Link transfer_requests to stock_transfers
ALTER TABLE `stock_transfers`
  ADD COLUMN `transfer_request_id` int unsigned DEFAULT NULL AFTER `indent_id`,
  ADD KEY `idx_st_transfer_request` (`transfer_request_id`);
