-- ═══════════════════════════════════════════════════════════════════════
-- FashCognitive Warehouse Architecture Migration
-- Implements: Central Warehouse + Factory Direct + Hybrid shipping models
-- Integrates with existing: stock_transfers, asn, grn, packing_containers,
--   warehouse_reservations, stock_reservations
-- ═══════════════════════════════════════════════════════════════════════
-- NOTE: Run this AFTER verifying existing stock_transfers, asn, grn tables exist

START TRANSACTION;

-- ═══════════════════════════════════════════════════════════════════════
-- 1. EXTEND EXISTING TABLES
-- ═══════════════════════════════════════════════════════════════════════

-- 1a. Add warehouse_type to warehouses (every factory gets its own FGWH)
ALTER TABLE warehouses
  ADD COLUMN warehouse_type enum(
    'raw_material','fabric','trim','chemical','wip',
    'finished_goods','export','customer','3pl','consolidation'
  ) NOT NULL DEFAULT 'finished_goods' AFTER code,
  ADD COLUMN is_fg_warehouse tinyint(1) NOT NULL DEFAULT 0 AFTER warehouse_type,
  ADD COLUMN is_factory_warehouse tinyint(1) NOT NULL DEFAULT 0 AFTER is_fg_warehouse,
  ADD COLUMN managing_factory_id int unsigned DEFAULT NULL AFTER is_factory_warehouse,
  ADD COLUMN contact_person varchar(255) DEFAULT NULL AFTER managing_factory_id,
  ADD COLUMN contact_phone varchar(50) DEFAULT NULL AFTER contact_person,
  ADD COLUMN email varchar(255) DEFAULT NULL AFTER contact_phone,
  ADD COLUMN warehouse_code varchar(20) DEFAULT NULL AFTER email,
  ADD COLUMN capacity_pcs int unsigned DEFAULT NULL AFTER warehouse_code,
  ADD COLUMN used_capacity_pcs int unsigned DEFAULT 0 AFTER capacity_pcs,
  ADD COLUMN supervised_by int unsigned DEFAULT NULL AFTER used_capacity_pcs,
  ADD COLUMN allow_negative_stock tinyint(1) NOT NULL DEFAULT 0 AFTER supervised_by,
  ADD KEY idx_warehouse_type (warehouse_type),
  ADD KEY idx_fg_warehouse (is_fg_warehouse),
  ADD KEY idx_factory_wh (managing_factory_id);

-- 1b. Add shipment_strategy to buyer POs
ALTER TABLE master_buyer_pos
  ADD COLUMN shipment_strategy enum(
    'central_warehouse','direct_shipment','customer_warehouse',
    'distribution_center','3pl'
  ) NOT NULL DEFAULT 'direct_shipment' AFTER delivery_date,
  ADD KEY idx_shipment_strategy (shipment_strategy);

-- 1c. Add source warehouse + shipment strategy to finished_goods_receipts
ALTER TABLE finished_goods_receipts
  ADD COLUMN source_warehouse_id int unsigned DEFAULT NULL AFTER warehouse_id,
  ADD COLUMN factory_warehouse_id int unsigned DEFAULT NULL AFTER source_warehouse_id,
  ADD COLUMN shipment_strategy enum(
    'central_warehouse','direct_shipment','customer_warehouse',
    'distribution_center','3pl'
  ) DEFAULT NULL AFTER factory_warehouse_id,
  ADD COLUMN internal_transfer_id int unsigned DEFAULT NULL AFTER shipment_strategy,
  ADD KEY idx_factory_wh (factory_warehouse_id),
  ADD KEY idx_internal_transfer (internal_transfer_id);

-- 1d. Add warehouse location fields to warehouse_stock
ALTER TABLE warehouse_stock
  ADD COLUMN zone_id int unsigned DEFAULT NULL AFTER location_id,
  ADD COLUMN rack_id int unsigned DEFAULT NULL AFTER zone_id,
  ADD COLUMN bin_id int unsigned DEFAULT NULL AFTER rack_id,
  ADD KEY idx_zone (zone_id),
  ADD KEY idx_rack (rack_id),
  ADD KEY idx_bin (bin_id);

-- 1e. Link packing_orders to warehouse and shipment strategy
ALTER TABLE packing_orders
  ADD COLUMN warehouse_id int unsigned DEFAULT NULL AFTER production_order_id,
  ADD COLUMN shipment_strategy enum(
    'central_warehouse','direct_shipment','customer_warehouse',
    'distribution_center','3pl'
  ) DEFAULT NULL AFTER warehouse_id,
  ADD COLUMN shipment_id int unsigned DEFAULT NULL AFTER shipment_strategy,
  ADD COLUMN dispatch_note_id int unsigned DEFAULT NULL AFTER shipment_id,
  ADD KEY idx_pko_shipment (shipment_id),
  ADD KEY idx_pko_dispatch (dispatch_note_id);

-- 1f. Link stock_transfers to shipment context (for FG transfers)
ALTER TABLE stock_transfers
  ADD COLUMN transfer_type enum(
    'internal','fg_consolidation','return','customer_return',
    '3pl_transfer','cross_dock'
  ) NOT NULL DEFAULT 'internal' AFTER transfer_no,
  ADD COLUMN packing_order_id int unsigned DEFAULT NULL AFTER transfer_type,
  ADD COLUMN shipment_id int unsigned DEFAULT NULL AFTER packing_order_id,
  ADD COLUMN dispatch_note_id int unsigned DEFAULT NULL AFTER shipment_id,
  ADD COLUMN vehicle_no varchar(50) DEFAULT NULL AFTER dispatch_note_id,
  ADD COLUMN driver_name varchar(255) DEFAULT NULL AFTER vehicle_no,
  ADD COLUMN driver_phone varchar(50) DEFAULT NULL AFTER driver_name,
  ADD COLUMN transporter_name varchar(255) DEFAULT NULL AFTER driver_phone,
  ADD COLUMN lr_no varchar(100) DEFAULT NULL AFTER transporter_name,
  ADD COLUMN lr_date date DEFAULT NULL AFTER lr_no,
  ADD COLUMN expected_delivery_date date DEFAULT NULL AFTER lr_date,
  ADD COLUMN actual_delivery_date date DEFAULT NULL AFTER expected_delivery_date,
  ADD COLUMN received_by int unsigned DEFAULT NULL AFTER actual_delivery_date,
  ADD COLUMN internal_grn_id int unsigned DEFAULT NULL AFTER received_by,
  ADD KEY idx_transfer_type (transfer_type),
  ADD KEY idx_pko_link (packing_order_id),
  ADD KEY idx_shipment_link (shipment_id);

-- ═══════════════════════════════════════════════════════════════════════
-- 2. NEW TABLES
-- ═══════════════════════════════════════════════════════════════════════

-- 2a. Warehouse Zones (e.g. Export Zone, Quarantine Zone)
CREATE TABLE IF NOT EXISTS warehouse_zones (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  zone_name varchar(255) NOT NULL,
  zone_code varchar(20) DEFAULT NULL,
  zone_type enum('storage','quarantine','inspection','packing','loading','return','damaged','expedite') NOT NULL DEFAULT 'storage',
  aisle varchar(100) DEFAULT NULL,
  capacity_pcs int unsigned DEFAULT NULL,
  used_capacity_pcs int unsigned DEFAULT 0,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  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_wh_zone_warehouse (warehouse_id),
  KEY idx_wh_zone_type (zone_type),
  KEY idx_wh_zone_company (company_id),
  CONSTRAINT fk_wh_zone_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2b. Warehouse Racks
CREATE TABLE IF NOT EXISTS warehouse_racks (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_name varchar(255) NOT NULL,
  rack_code varchar(20) DEFAULT NULL,
  rack_type enum('pallet','cantilever','flow','drive_in','mezzanine','push_back','mobile') DEFAULT 'pallet',
  max_bins int unsigned DEFAULT NULL,
  max_weight_kg decimal(10,2) DEFAULT NULL,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  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_rack_warehouse (warehouse_id),
  KEY idx_rack_zone (zone_id),
  KEY idx_rack_company (company_id),
  CONSTRAINT fk_rack_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
  CONSTRAINT fk_rack_zone FOREIGN KEY (zone_id) REFERENCES warehouse_zones(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2c. Warehouse Bins (physical storage locations)
CREATE TABLE IF NOT EXISTS warehouse_bins (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_id int unsigned DEFAULT NULL,
  bin_code varchar(30) NOT NULL,
  bin_type enum('shelf','slot','floor','pallet_position','drawer','tote','bulk') NOT NULL DEFAULT 'shelf',
  max_qty decimal(14,4) DEFAULT NULL,
  max_weight_kg decimal(10,2) DEFAULT NULL,
  uom_id int unsigned DEFAULT NULL,
  length_cm decimal(8,2) DEFAULT NULL,
  width_cm decimal(8,2) DEFAULT NULL,
  height_cm decimal(8,2) DEFAULT NULL,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  is_occupied tinyint(1) NOT NULL DEFAULT 0,
  current_item_id int unsigned DEFAULT NULL,
  current_qty decimal(14,4) DEFAULT 0.0000,
  last_stocked_at datetime 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),
  UNIQUE KEY uk_bin_code_wh (warehouse_id, bin_code),
  KEY idx_bin_warehouse (warehouse_id),
  KEY idx_bin_zone (zone_id),
  KEY idx_bin_rack (rack_id),
  KEY idx_bin_item (current_item_id),
  KEY idx_bin_active (is_active, is_occupied),
  KEY idx_bin_company (company_id),
  CONSTRAINT fk_bin_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
  CONSTRAINT fk_bin_zone FOREIGN KEY (zone_id) REFERENCES warehouse_zones(id) ON DELETE SET NULL,
  CONSTRAINT fk_bin_rack FOREIGN KEY (rack_id) REFERENCES warehouse_racks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2d. Dispatch Notes (factory outbound documentation)
CREATE TABLE IF NOT EXISTS dispatch_notes (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  dispatch_no varchar(50) NOT NULL,
  dispatch_type enum('factory_to_central','factory_direct','central_to_customer','central_to_3pl','return','sample') NOT NULL DEFAULT 'factory_to_central',
  packing_order_id int unsigned DEFAULT NULL,
  packing_order_code varchar(50) DEFAULT NULL,
  production_order_id int unsigned DEFAULT NULL,
  from_warehouse_id int unsigned NOT NULL,
  to_warehouse_id int unsigned DEFAULT NULL,
  buyer_po_id int unsigned DEFAULT NULL,
  style_id int unsigned DEFAULT NULL,
  total_qty int unsigned NOT NULL DEFAULT 0,
  total_cartons int unsigned NOT NULL DEFAULT 0,
  total_pallets int unsigned DEFAULT 0,
  total_weight_kg decimal(14,2) DEFAULT NULL,
  total_volume_cbm decimal(10,3) DEFAULT NULL,
  vehicle_no varchar(50) DEFAULT NULL,
  driver_name varchar(255) DEFAULT NULL,
  driver_phone varchar(50) DEFAULT NULL,
  transporter_name varchar(255) DEFAULT NULL,
  lr_no varchar(100) DEFAULT NULL,
  lr_date date DEFAULT NULL,
  dispatch_date date NOT NULL,
  estimated_arrival date DEFAULT NULL,
  actual_arrival date DEFAULT NULL,
  gate_pass_no varchar(50) DEFAULT NULL,
  eway_bill_no varchar(50) DEFAULT NULL,
  invoice_no varchar(100) DEFAULT NULL,
  invoice_date date DEFAULT NULL,
  shipping_marks text DEFAULT NULL,
  container_no varchar(50) DEFAULT NULL,
  seal_no varchar(50) DEFAULT NULL,
  status enum('draft','dispatched','in_transit','delivered','partially_delivered','cancelled') NOT NULL DEFAULT 'draft',
  internal_asn_id int unsigned DEFAULT NULL,
  internal_grn_id int unsigned DEFAULT NULL,
  transfer_order_id int unsigned DEFAULT NULL,
  created_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_dispatch_no (dispatch_no),
  KEY idx_dispatch_type (dispatch_type),
  KEY idx_dispatch_status (status),
  KEY idx_dispatch_pko (packing_order_id),
  KEY idx_dispatch_from_wh (from_warehouse_id),
  KEY idx_dispatch_to_wh (to_warehouse_id),
  KEY idx_dispatch_company (company_id),
  KEY idx_dispatch_po (production_order_id),
  KEY idx_dispatch_bpo (buyer_po_id),
  CONSTRAINT fk_dispatch_from_wh FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_dispatch_to_wh FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2e. Shipment Manifests (multi-factory consolidation)
CREATE TABLE IF NOT EXISTS shipment_manifests (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  manifest_no varchar(50) NOT NULL,
  buyer_po_id int unsigned NOT NULL,
  buyer_id int unsigned DEFAULT NULL,
  style_id int unsigned DEFAULT NULL,
  destination_country varchar(100) DEFAULT NULL,
  destination_port varchar(100) DEFAULT NULL,
  incoterm varchar(20) DEFAULT NULL,
  shipment_mode varchar(50) DEFAULT NULL,
  carrier varchar(255) DEFAULT NULL,
  vessel_flight_no varchar(100) DEFAULT NULL,
  container_numbers json DEFAULT NULL,
  etd date DEFAULT NULL,
  eta date DEFAULT NULL,
  total_qty int unsigned NOT NULL DEFAULT 0,
  total_cartons int unsigned NOT NULL DEFAULT 0,
  total_gross_weight_kg decimal(14,2) DEFAULT NULL,
  total_volume_cbm decimal(10,3) DEFAULT NULL,
  total_invoice_value decimal(14,2) DEFAULT NULL,
  currency_id int unsigned DEFAULT NULL,
  status enum('draft','planned','consolidating','consolidated','loaded','shipped','documentation','cleared','delivered') NOT NULL DEFAULT 'draft',
  created_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_manifest_no (manifest_no),
  KEY idx_manifest_bpo (buyer_po_id),
  KEY idx_manifest_status (status),
  KEY idx_manifest_company (company_id),
  CONSTRAINT fk_manifest_bpo FOREIGN KEY (buyer_po_id) REFERENCES master_buyer_pos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2f. Shipment Manifest Items (which factory contributed what)
CREATE TABLE IF NOT EXISTS shipment_manifest_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  manifest_id int unsigned NOT NULL,
  dispatch_note_id int unsigned DEFAULT NULL,
  packing_order_id int unsigned DEFAULT NULL,
  finished_goods_receipt_id int unsigned DEFAULT NULL,
  production_order_id int unsigned DEFAULT NULL,
  factory_id int unsigned DEFAULT NULL,
  warehouse_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  cartons int unsigned DEFAULT 0,
  weight_kg decimal(14,2) DEFAULT NULL,
  volume_cbm decimal(10,3) DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mi_manifest (manifest_id),
  KEY idx_mi_dispatch (dispatch_note_id),
  KEY idx_mi_pko (packing_order_id),
  KEY idx_mi_fgr (finished_goods_receipt_id),
  KEY idx_mi_factory (factory_id),
  CONSTRAINT fk_mi_manifest FOREIGN KEY (manifest_id) REFERENCES shipment_manifests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2g. Warehouse Movement Audit Log (every inventory change tracked)
CREATE TABLE IF NOT EXISTS warehouse_movements (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  movement_type enum(
    'receipt','issue','transfer_out','transfer_in','adjustment',
    'reservation','unreservation','pick','pack','ship',
    'return','write_off','relocation','consume','production_receipt'
  ) NOT NULL,
  stock_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  warehouse_id int unsigned DEFAULT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_id int unsigned DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  from_location varchar(100) DEFAULT NULL,
  to_location varchar(100) DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  lot_no varchar(100) DEFAULT NULL,
  batch_no varchar(100) DEFAULT NULL,
  quantity decimal(14,4) NOT NULL DEFAULT 0.0000,
  quantity_before decimal(14,4) DEFAULT NULL,
  quantity_after decimal(14,4) DEFAULT NULL,
  uom_id int unsigned DEFAULT NULL,
  reference_type varchar(50) DEFAULT NULL,
  reference_id int unsigned DEFAULT NULL,
  reference_no varchar(100) DEFAULT NULL,
  movement_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  performed_by int unsigned DEFAULT NULL,
  remarks text DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mv_stock (stock_id),
  KEY idx_mv_item (item_id),
  KEY idx_mv_warehouse (warehouse_id),
  KEY idx_mv_type (movement_type),
  KEY idx_mv_ref (reference_type, reference_id),
  KEY idx_mv_date (movement_date),
  KEY idx_mv_lot (lot_no),
  KEY idx_mv_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2h. Warehouse Tasks (pick, pack, put-away assignments)
CREATE TABLE IF NOT EXISTS warehouse_tasks (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  task_no varchar(50) NOT NULL,
  task_type enum('receive','put_away','pick','pack','load','unload','cycle_count','relocation','replenish','quality_check') NOT NULL,
  warehouse_id int unsigned NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  source_location_id int unsigned DEFAULT NULL,
  destination_location_id int unsigned DEFAULT NULL,
  reference_type varchar(50) DEFAULT NULL,
  reference_id int unsigned DEFAULT NULL,
  reference_no varchar(100) DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  quantity_required decimal(14,4) DEFAULT 0.0000,
  quantity_completed decimal(14,4) DEFAULT 0.0000,
  priority enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  assigned_to int unsigned DEFAULT NULL,
  assigned_at datetime DEFAULT NULL,
  started_at datetime DEFAULT NULL,
  completed_at datetime DEFAULT NULL,
  estimated_minutes int unsigned DEFAULT NULL,
  actual_minutes int unsigned DEFAULT NULL,
  status enum('pending','assigned','in_progress','completed','skipped','cancelled') NOT NULL DEFAULT 'pending',
  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_task_no (task_no),
  KEY idx_task_type (task_type),
  KEY idx_task_warehouse (warehouse_id),
  KEY idx_task_assigned (assigned_to),
  KEY idx_task_status (status),
  KEY idx_task_ref (reference_type, reference_id),
  KEY idx_task_company (company_id),
  CONSTRAINT fk_task_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2i. Container Loading Plans (precise loading per container)
CREATE TABLE IF NOT EXISTS container_loading_plans (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  container_id int unsigned NOT NULL,
  dispatch_note_id int unsigned DEFAULT NULL,
  packing_order_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  cartons int unsigned NOT NULL DEFAULT 0,
  pallet_no int unsigned DEFAULT NULL,
  loading_order int unsigned DEFAULT 0,
  weight_kg decimal(10,2) DEFAULT NULL,
  volume_cbm decimal(8,4) DEFAULT NULL,
  loaded_at datetime DEFAULT NULL,
  verified_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_clp_container (container_id),
  KEY idx_clp_dispatch (dispatch_note_id),
  KEY idx_clp_pko (packing_order_id),
  KEY idx_clp_company (company_id),
  CONSTRAINT fk_clp_container FOREIGN KEY (container_id) REFERENCES packing_containers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 3. DEFAULT FG WAREHOUSES FOR EXISTING FACTORIES
-- ═══════════════════════════════════════════════════════════════════════

-- Create a finished goods warehouse for each active factory that doesn't have one
INSERT INTO warehouses (company_id, name, code, warehouse_type, is_fg_warehouse, is_factory_warehouse, managing_factory_id, status)
SELECT
  1,
  CONCAT(mf.name, ' - FG'),
  CONCAT('FGWH-UNIT', LPAD(ROW_NUMBER() OVER (ORDER BY mf.id), 2, '0')),
  'finished_goods', 1, 1, mf.id,
  'active'
FROM master_factories mf
WHERE mf.status = 'active'
  AND NOT EXISTS (
    SELECT 1 FROM warehouses w
    WHERE w.managing_factory_id = mf.id AND w.is_fg_warehouse = 1
  );

-- Create or update a central export warehouse if none exists
INSERT INTO warehouses (company_id, name, code, warehouse_type, is_fg_warehouse, status)
SELECT 1, 'Central Export Warehouse', 'CWH-001', 'export', 1, 'active'
WHERE NOT EXISTS (SELECT 1 FROM warehouses WHERE warehouse_type = 'export' AND is_fg_warehouse = 1);

COMMIT;
