-- ============================================================
-- FashCognitive - AI-Driven Material Planning & Procurement
-- Core Schema: Indent -> Stock Allocation -> PR -> PO -> GRN
-- ============================================================

-- -----------------------------------------------------------
-- 1. WAREHOUSES
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `factory_id` int unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `address` text,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `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_wh_factory` (`factory_id`),
  KEY `idx_wh_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 2. WAREHOUSE LOCATIONS (Rack > Shelf > Bin)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_locations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `warehouse_id` int unsigned NOT NULL,
  `code` varchar(100) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `rack` varchar(50) DEFAULT NULL,
  `shelf` varchar(50) DEFAULT NULL,
  `bin` varchar(50) DEFAULT NULL,
  `capacity` decimal(14,4) DEFAULT NULL,
  `uom_id` int unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wl_warehouse` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 3. WAREHOUSE STOCK (current inventory per item per warehouse per lot)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warehouse_stock` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `warehouse_id` int unsigned NOT NULL,
  `location_id` int unsigned DEFAULT 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,
  `supplier_id` int unsigned DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT 0,
  `reserved_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `blocked_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `currency_id` int unsigned DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `grn_id` int unsigned DEFAULT NULL,
  `status` enum('available','reserved','blocked','expired') NOT NULL DEFAULT 'available',
  `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_ws_warehouse` (`warehouse_id`),
  KEY `idx_ws_item` (`item_id`),
  KEY `idx_ws_company` (`company_id`),
  KEY `idx_ws_lot` (`lot_no`),
  KEY `idx_ws_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 4. STOCK LEDGER (every movement goes through here)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_ledger` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `warehouse_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,
  `movement_type` enum('purchase_in','transfer_in','transfer_out','issue','return','adjustment','consumption','qc_hold','qc_release','reservation','unreservation') NOT NULL,
  `qty_in` decimal(14,4) DEFAULT 0,
  `qty_out` decimal(14,4) DEFAULT 0,
  `balance` decimal(14,4) NOT NULL DEFAULT 0,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` int unsigned DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sl_warehouse` (`warehouse_id`),
  KEY `idx_sl_item` (`item_id`),
  KEY `idx_sl_movement` (`movement_type`),
  KEY `idx_sl_reference` (`reference_type`, `reference_id`),
  KEY `idx_sl_company` (`company_id`),
  KEY `idx_sl_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 5. STOCK RESERVATIONS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_reservations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `stock_id` int unsigned NOT NULL,
  `reserved_qty` decimal(14,4) NOT NULL,
  `reserved_for_type` varchar(50) NOT NULL,
  `reserved_for_id` int unsigned NOT NULL,
  `reserved_for_no` varchar(100) DEFAULT NULL,
  `status` enum('active','fulfilled','cancelled') NOT NULL DEFAULT 'active',
  `reserved_by` int unsigned 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_sr_stock` (`stock_id`),
  KEY `idx_sr_status` (`status`),
  KEY `idx_sr_reference` (`reserved_for_type`, `reserved_for_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 6. MATERIAL INDENTS (the heart of procurement)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `material_indents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `indent_no` varchar(50) NOT NULL,
  `indent_type` enum('production','sampling','development','general_purchase','asset','maintenance','office_supplies','capex') NOT NULL DEFAULT 'production',
  `po_id` int unsigned DEFAULT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `required_date` date NOT NULL,
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `requested_by` int unsigned DEFAULT NULL,
  `department_id` int unsigned DEFAULT NULL,
  `status` enum('draft','pending_approval','approved','partially_fulfilled','fully_fulfilled','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `ai_allocation_done` tinyint(1) NOT NULL DEFAULT 0,
  `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_indent_no` (`indent_no`, `company_id`),
  KEY `idx_mi_po` (`po_id`),
  KEY `idx_mi_factory` (`factory_id`),
  KEY `idx_mi_status` (`status`),
  KEY `idx_mi_company` (`company_id`),
  KEY `idx_mi_date` (`required_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 7. MATERIAL INDENT ITEMS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `material_indent_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `indent_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `required_qty` decimal(14,4) NOT NULL,
  `allocated_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `issued_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `status` enum('pending','allocated','partially_issued','fully_issued','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mii_indent` (`indent_id`),
  KEY `idx_mii_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 8. INDENT ALLOCATIONS (AI stock allocation results)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `indent_allocations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `indent_item_id` int unsigned NOT NULL,
  `allocation_type` enum('warehouse_issue','warehouse_transfer','purchase_requisition') NOT NULL,
  `source_warehouse_id` int unsigned DEFAULT NULL,
  `target_warehouse_id` int unsigned DEFAULT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `status` enum('pending','approved','fulfilled','cancelled') NOT NULL DEFAULT 'pending',
  `pr_id` int unsigned DEFAULT NULL,
  `transfer_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ia_indent_item` (`indent_item_id`),
  KEY `idx_ia_type` (`allocation_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 9. PURCHASE REQUISITIONS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_requisitions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `pr_no` varchar(50) NOT NULL,
  `indent_id` int unsigned DEFAULT NULL,
  `requested_by` int unsigned DEFAULT NULL,
  `department_id` int unsigned DEFAULT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` enum('draft','pending_approval','approved','partially_ordered','fully_ordered','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `approved_by` int unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `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_pr_no` (`pr_no`, `company_id`),
  KEY `idx_pr_indent` (`indent_id`),
  KEY `idx_pr_status` (`status`),
  KEY `idx_pr_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 10. PURCHASE REQUISITION ITEMS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_requisition_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `pr_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `required_qty` decimal(14,4) NOT NULL,
  `ordered_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `estimated_rate` decimal(14,4) DEFAULT NULL,
  `supplier_id` int unsigned DEFAULT NULL,
  `required_date` date DEFAULT NULL,
  `status` enum('pending','ordered','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pri_pr` (`pr_id`),
  KEY `idx_pri_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 11. PURCHASE ORDERS (procurement from vendors)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `procurement_orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `po_no` varchar(50) NOT NULL,
  `pr_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned NOT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `order_date` date NOT NULL,
  `expected_delivery` date DEFAULT NULL,
  `payment_term_id` int unsigned DEFAULT NULL,
  `currency_id` int unsigned DEFAULT NULL,
  `exchange_rate` decimal(14,6) DEFAULT 1.000000,
  `total_amount` decimal(16,4) DEFAULT 0,
  `tax_amount` decimal(16,4) DEFAULT 0,
  `status` enum('draft','submitted','confirmed','partially_received','fully_received','cancelled') NOT NULL DEFAULT 'draft',
  `supplier_confirmation` enum('pending','accepted','rejected') NOT NULL DEFAULT 'pending',
  `notes` text,
  `created_by` int unsigned 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`),
  UNIQUE KEY `uk_po_no` (`po_no`, `company_id`),
  KEY `idx_po_supplier` (`supplier_id`),
  KEY `idx_po_pr` (`pr_id`),
  KEY `idx_po_status` (`status`),
  KEY `idx_po_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 12. PURCHASE ORDER ITEMS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `procurement_order_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `ordered_qty` decimal(14,4) NOT NULL,
  `received_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `rate` decimal(14,4) NOT NULL,
  `amount` decimal(16,4) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `expected_date` date DEFAULT NULL,
  `status` enum('pending','partially_received','fully_received','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_poi_po` (`po_id`),
  KEY `idx_poi_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 13. GOODS RECEIPT NOTES (GRN)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `grn` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `grn_no` varchar(50) NOT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `received_by` int unsigned DEFAULT NULL,
  `received_date` date NOT NULL,
  `invoice_no` varchar(100) DEFAULT NULL,
  `invoice_date` date DEFAULT NULL,
  `vehicle_no` varchar(100) DEFAULT NULL,
  `challan_no` varchar(100) DEFAULT NULL,
  `total_qty` decimal(14,4) DEFAULT 0,
  `qc_status` enum('pending','passed','partial','failed') NOT NULL DEFAULT 'pending',
  `status` enum('draft','confirmed','qc_pending','qc_passed','qc_failed','stocked','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_grn_no` (`grn_no`, `company_id`),
  KEY `idx_grn_po` (`po_id`),
  KEY `idx_grn_supplier` (`supplier_id`),
  KEY `idx_grn_warehouse` (`warehouse_id`),
  KEY `idx_grn_status` (`status`),
  KEY `idx_grn_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 14. GRN ITEMS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `grn_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `po_item_id` int unsigned DEFAULT 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,
  `received_qty` decimal(14,4) NOT NULL,
  `accepted_qty` decimal(14,4) DEFAULT NULL,
  `rejected_qty` decimal(14,4) DEFAULT 0,
  `location_id` int unsigned DEFAULT NULL,
  `qc_status` enum('pending','passed','failed') NOT NULL DEFAULT 'pending',
  `expiry_date` date DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_gi_grn` (`grn_id`),
  KEY `idx_gi_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 15. STOCK TRANSERS (inter-warehouse)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_transfers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `transfer_no` varchar(50) NOT NULL,
  `from_warehouse_id` int unsigned NOT NULL,
  `to_warehouse_id` int unsigned NOT NULL,
  `indent_id` int unsigned DEFAULT NULL,
  `requested_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `status` enum('draft','pending_approval','approved','in_transit','received','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_transfer_no` (`transfer_no`, `company_id`),
  KEY `idx_st_status` (`status`),
  KEY `idx_st_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 16. STOCK TRANSFER ITEMS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_transfer_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `transfer_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,
  `uom_id` int unsigned NOT NULL,
  `requested_qty` decimal(14,4) NOT NULL,
  `sent_qty` decimal(14,4) DEFAULT 0,
  `received_qty` decimal(14,4) DEFAULT 0,
  `stock_id` int unsigned DEFAULT NULL,
  `status` enum('pending','sent','received','cancelled') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sti_transfer` (`transfer_id`),
  KEY `idx_sti_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Seed: Default warehouses per factory
-- -----------------------------------------------------------
INSERT IGNORE INTO `warehouses` (`company_id`, `factory_id`, `name`, `code`, `is_default`, `status`)
SELECT 1, f.id, CONCAT(f.name, ' Main Warehouse'), CONCAT(f.code, '-WH-MAIN'), 1, 'active'
FROM `master_factories` f
WHERE f.status = 'active'
AND NOT EXISTS (SELECT 1 FROM `warehouses` w WHERE w.factory_id = f.id AND w.company_id = 1);
