-- ═══════════════════════════════════════════════════════════════════════
-- FashCognitive Digital Warehouse Migration — Phase 1 (Inbound slice)
-- Digital Identity (serials/barcodes/QR/passport) + Gate → Dock → Put-Away
-- + Camera/OCR event layer + Traceability + AI analysis.
-- Builds on existing: warehouses/zones/racks/bins, asn, grn/grn_putaway,
--   warehouse_stock/movements/tasks, stock_reservations, cartons, fabric_rolls.
-- Convention: company_id scoped, utf8mb4_unicode_ci, enums, timestamps.
-- ═══════════════════════════════════════════════════════════════════════

START TRANSACTION;

-- ═══════════════════════════════════════════════════════════════════════
-- 1. CAMERA REGISTRY + GATE ENTRY
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS gate_cameras (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  camera_code varchar(30) NOT NULL,
  name varchar(255) NOT NULL,
  station enum('gate','dock','rack','forklift','packing','loading') NOT NULL DEFAULT 'gate',
  warehouse_id int unsigned DEFAULT NULL,
  zone_id int unsigned DEFAULT NULL,
  stream_url varchar(500) DEFAULT NULL,
  status enum('active','inactive','offline') NOT NULL DEFAULT 'active',
  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_camera_code (camera_code, company_id),
  KEY idx_cam_station (station),
  KEY idx_cam_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gate_entries (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  gate_entry_no varchar(50) NOT NULL,
  asn_id int unsigned DEFAULT NULL,
  supplier_id int unsigned DEFAULT NULL,
  po_no varchar(100) DEFAULT NULL,
  vehicle_no varchar(50) DEFAULT NULL,
  driver_name varchar(255) DEFAULT NULL,
  driver_phone varchar(50) DEFAULT NULL,
  container_no varchar(50) DEFAULT NULL,
  seal_no varchar(50) DEFAULT NULL,
  expected_cartons int unsigned DEFAULT NULL,
  expected_rolls int unsigned DEFAULT NULL,
  arrived_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gps_lat decimal(10,7) DEFAULT NULL,
  gps_lng decimal(10,7) DEFAULT NULL,
  ocr_status enum('pending','matched','mismatch','failed') NOT NULL DEFAULT 'pending',
  vehicle_verified tinyint(1) NOT NULL DEFAULT 0,
  container_verified tinyint(1) NOT NULL DEFAULT 0,
  seal_verified tinyint(1) NOT NULL DEFAULT 0,
  po_verified tinyint(1) NOT NULL DEFAULT 0,
  quantity_verified tinyint(1) NOT NULL DEFAULT 0,
  status enum('expected','arrived','verified','docked','rejected') NOT NULL DEFAULT 'expected',
  gate_by int unsigned DEFAULT NULL,
  security_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),
  UNIQUE KEY uk_gate_no (gate_entry_no, company_id),
  KEY idx_gate_asn (asn_id),
  KEY idx_gate_status (status),
  KEY idx_gate_vehicle (vehicle_no),
  KEY idx_gate_company (company_id),
  CONSTRAINT fk_gate_asn FOREIGN KEY (asn_id) REFERENCES asn(id) ON DELETE SET NULL,
  CONSTRAINT fk_gate_supplier FOREIGN KEY (supplier_id) REFERENCES master_vendors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gate_images (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  gate_entry_id int unsigned NOT NULL,
  image_type enum('vehicle','driver','container','seal','loading') NOT NULL DEFAULT 'vehicle',
  image_path varchar(500) DEFAULT NULL,
  ocr_text text DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_gimg_gate (gate_entry_id),
  KEY idx_gimg_type (image_type),
  CONSTRAINT fk_gimg_gate FOREIGN KEY (gate_entry_id) REFERENCES gate_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dock_assignments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  gate_entry_id int unsigned NOT NULL,
  dock_no varchar(20) NOT NULL,
  dock_type enum('fabric','accessory','chemical','returns') NOT NULL DEFAULT 'fabric',
  warehouse_id int unsigned NOT NULL,
  assigned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at datetime DEFAULT NULL,
  ended_at datetime DEFAULT NULL,
  status enum('assigned','unloading','complete','cancelled') NOT NULL DEFAULT 'assigned',
  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_dock_gate (gate_entry_id),
  KEY idx_dock_status (status),
  KEY idx_dock_company (company_id),
  CONSTRAINT fk_dock_gate FOREIGN KEY (gate_entry_id) REFERENCES gate_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_dock_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 2. DIGITAL IDENTITY (serial + batch + barcode + QR)
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS inventory_batches (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  batch_no varchar(50) NOT NULL,
  lot_no varchar(50) DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  supplier_id int unsigned DEFAULT NULL,
  po_id int unsigned DEFAULT NULL,
  received_date date DEFAULT NULL,
  expiry_date date 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_batch_no (batch_no),
  KEY idx_batch_lot (lot_no),
  KEY idx_batch_item (item_id),
  KEY idx_batch_company (company_id),
  CONSTRAINT fk_batch_item FOREIGN KEY (item_id) REFERENCES master_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_serials (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  serial_no varchar(50) NOT NULL,
  serial_type enum('roll','carton','pallet','garment','bundle','accessory_box') NOT NULL DEFAULT 'roll',
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  batch_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  shade varchar(50) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  warehouse_id int unsigned DEFAULT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_id int unsigned DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  location_code varchar(100) DEFAULT NULL,
  status enum('expected','received','inspected','available','reserved','allocated','issued','in_production','packed','shipped','blocked') NOT NULL DEFAULT 'expected',
  first_received_at datetime DEFAULT NULL,
  last_moved_at datetime 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_serial_no (serial_no, company_id),
  KEY idx_serial_ref (ref_type, ref_id),
  KEY idx_serial_item (item_id),
  KEY idx_serial_status (status),
  KEY idx_serial_loc (warehouse_id, zone_id, rack_id, bin_id),
  KEY idx_serial_company (company_id),
  CONSTRAINT fk_serial_item FOREIGN KEY (item_id) REFERENCES master_items(id) ON DELETE SET NULL,
  CONSTRAINT fk_serial_batch FOREIGN KEY (batch_id) REFERENCES inventory_batches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_barcodes (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  serial_id int unsigned NOT NULL,
  barcode_value varchar(100) NOT NULL,
  symbology enum('code128','code39','ean13') NOT NULL DEFAULT 'code128',
  format enum('roll','carton','pallet','garment','accessory_box') NOT NULL DEFAULT 'roll',
  status enum('generated','printed','scanned') NOT NULL DEFAULT 'generated',
  scan_count int unsigned NOT NULL DEFAULT 0,
  last_scanned_at timestamp NULL DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_barcode_value (barcode_value, company_id),
  KEY idx_bc_serial (serial_id),
  KEY idx_bc_status (status),
  CONSTRAINT fk_bc_serial FOREIGN KEY (serial_id) REFERENCES inventory_serials(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_qr_codes (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  serial_id int unsigned NOT NULL,
  qr_data text NOT NULL,
  encoded_url varchar(500) DEFAULT NULL,
  status enum('generated','printed','scanned') NOT NULL DEFAULT 'generated',
  scan_count int unsigned NOT NULL DEFAULT 0,
  last_scanned_at timestamp NULL DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_qr_serial (serial_id),
  KEY idx_qr_status (status),
  KEY idx_qr_company (company_id),
  CONSTRAINT fk_qr_serial FOREIGN KEY (serial_id) REFERENCES inventory_serials(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 3. AI PUT-AWAY + CAMERA EVENTS + OCR + TRACEABILITY + AI ANALYSIS
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS rack_allocations (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  grn_item_id int unsigned DEFAULT NULL,
  inventory_serial_id int unsigned DEFAULT NULL,
  warehouse_id int unsigned NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_id int unsigned DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  ai_score decimal(5,2) DEFAULT NULL,
  factors json DEFAULT NULL,
  status enum('suggested','confirmed','skipped','cancelled') NOT NULL DEFAULT 'suggested',
  confirmed_by int unsigned DEFAULT NULL,
  confirmed_at datetime 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_ra_serial (inventory_serial_id),
  KEY idx_ra_grn (grn_item_id),
  KEY idx_ra_status (status),
  KEY idx_ra_loc (warehouse_id, zone_id, rack_id, bin_id),
  KEY idx_ra_company (company_id),
  CONSTRAINT fk_ra_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL,
  CONSTRAINT fk_ra_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS camera_events (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  gate_camera_id int unsigned DEFAULT NULL,
  station varchar(50) DEFAULT NULL,
  event_type enum('vehicle_ocr','barcode_detected','qr_detected','carton_count','damage_detected','wrong_rack','wrong_label','seal_check','forklift_verified','loading_sequence') NOT NULL DEFAULT 'qr_detected',
  reference_type varchar(50) DEFAULT NULL,
  reference_id int unsigned DEFAULT NULL,
  payload json DEFAULT NULL,
  confidence decimal(5,2) DEFAULT NULL,
  is_alert tinyint(1) NOT NULL DEFAULT 0,
  status enum('verified','alert','ignored','resolved') NOT NULL DEFAULT 'verified',
  image_path varchar(500) DEFAULT NULL,
  occurred_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ce_camera (gate_camera_id),
  KEY idx_ce_station (station),
  KEY idx_ce_type (event_type),
  KEY idx_ce_status (status),
  KEY idx_ce_ref (reference_type, reference_id),
  KEY idx_ce_company (company_id),
  CONSTRAINT fk_ce_camera FOREIGN KEY (gate_camera_id) REFERENCES gate_cameras(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ocr_results (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  camera_event_id int unsigned DEFAULT NULL,
  gate_entry_id int unsigned DEFAULT NULL,
  ocr_type enum('vehicle','container','seal','barcode','qr','label') NOT NULL DEFAULT 'barcode',
  detected_text text DEFAULT NULL,
  confidence decimal(5,2) DEFAULT NULL,
  raw json DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ocr_event (camera_event_id),
  KEY idx_ocr_gate (gate_entry_id),
  KEY idx_ocr_type (ocr_type),
  KEY idx_ocr_company (company_id),
  CONSTRAINT fk_ocr_event FOREIGN KEY (camera_event_id) REFERENCES camera_events(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_gate FOREIGN KEY (gate_entry_id) REFERENCES gate_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_traceability (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  entity_type varchar(50) NOT NULL,
  entity_id int unsigned NOT NULL,
  station enum('supplier','po','asn','gate','dock','grn','inspection','putaway','issue','production','fg','packing','shipment') NOT NULL,
  location_code varchar(100) DEFAULT NULL,
  reference_type varchar(50) DEFAULT NULL,
  reference_id int unsigned DEFAULT NULL,
  reference_no varchar(100) DEFAULT NULL,
  status_at varchar(50) DEFAULT NULL,
  occurred_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  camera_event_id int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_trace_entity (entity_type, entity_id),
  KEY idx_trace_station (station),
  KEY idx_trace_ref (reference_type, reference_id),
  KEY idx_trace_company (company_id),
  CONSTRAINT fk_trace_event FOREIGN KEY (camera_event_id) REFERENCES camera_events(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_ai_analysis (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  analysis_type enum('putaway','inventory_health','dead_stock','reorder') NOT NULL,
  entity_id int unsigned DEFAULT NULL,
  input_snapshot json DEFAULT NULL,
  result json DEFAULT NULL,
  score decimal(6,2) DEFAULT NULL,
  recommendation text DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ia_type (analysis_type),
  KEY idx_ia_entity (entity_id),
  KEY idx_ia_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 4. EXTEND EXISTING TABLES (additive)
-- ═══════════════════════════════════════════════════════════════════════

ALTER TABLE grn_putaway
  ADD COLUMN zone_id int unsigned DEFAULT NULL AFTER location_code,
  ADD COLUMN rack_id int unsigned DEFAULT NULL AFTER zone_id,
  ADD COLUMN bin_id int unsigned DEFAULT NULL AFTER rack_id,
  ADD COLUMN inventory_serial_id int unsigned DEFAULT NULL AFTER bin_id,
  ADD COLUMN ai_score decimal(5,2) DEFAULT NULL AFTER inventory_serial_id,
  ADD COLUMN ai_reason varchar(500) DEFAULT NULL AFTER ai_score,
  ADD KEY idx_gp_serial (inventory_serial_id),
  ADD KEY idx_gp_rack (rack_id),
  ADD KEY idx_gp_bin (bin_id);

ALTER TABLE fabric_rolls
  ADD COLUMN inventory_serial_id int unsigned DEFAULT NULL AFTER source_inspection_roll_id,
  ADD COLUMN batch_id int unsigned DEFAULT NULL AFTER inventory_serial_id,
  ADD COLUMN barcode_value varchar(100) DEFAULT NULL AFTER batch_id,
  ADD COLUMN qr_code varchar(500) DEFAULT NULL AFTER barcode_value,
  ADD KEY idx_fr_serial (inventory_serial_id),
  ADD KEY idx_fr_batch (batch_id);

ALTER TABLE warehouse_tasks
  ADD COLUMN dock_assignment_id int unsigned DEFAULT NULL AFTER reference_no,
  ADD COLUMN camera_verified tinyint(1) NOT NULL DEFAULT 0 AFTER dock_assignment_id,
  ADD COLUMN verified_at datetime DEFAULT NULL AFTER camera_verified,
  ADD KEY idx_wt_dock (dock_assignment_id);

ALTER TABLE cartons
  ADD COLUMN inventory_serial_id int unsigned DEFAULT NULL AFTER pallet_id,
  ADD KEY idx_ct_serial (inventory_serial_id);

-- ═══════════════════════════════════════════════════════════════════════
-- 5. PERMISSIONS
-- ═══════════════════════════════════════════════════════════════════════

INSERT IGNORE INTO `permissions` (`code`, `label`, `group_name`, `description`) VALUES
('warehouse.view', 'View Digital Warehouse', 'Warehouse', 'View digital warehouse, gate, dock, receiving, identity, traceability and camera events'),
('warehouse.manage', 'Manage Digital Warehouse', 'Warehouse', 'Create/edit warehouse records, confirm put-away, resolve camera alerts');

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name IN ('admin','quality_manager')
  AND p.code IN ('warehouse.view', 'warehouse.manage');

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name IN ('quality_viewer','quality_inspector')
  AND p.code IN ('warehouse.view');

-- ═══════════════════════════════════════════════════════════════════════
-- 6. DEMO SEED — Zones / Racks / Bins (warehouse 1, DGL Main Warehouse)
-- ═══════════════════════════════════════════════════════════════════════

INSERT INTO warehouse_zones (id, company_id, warehouse_id, zone_name, zone_code, zone_type, aisle, capacity_pcs, notes)
SELECT v.id, 1, 1, v.zone_name, v.zone_code, v.zone_type, v.aisle, v.capacity_pcs, v.notes
FROM (SELECT 1 id, 'Fabric Zone' zone_name, 'FAB-Z1' zone_code, 'storage' zone_type, 'Aisle A' aisle, 1000 capacity_pcs, 'Raw fabric rolls' notes
      UNION ALL SELECT 2, 'Trim & Accessories', 'TRIM-Z2', 'storage', 'Aisle B', 5000, 'Buttons, zippers, labels'
      UNION ALL SELECT 3, 'Chemical Store', 'CHEM-Z3', 'storage', 'Aisle C', 500, 'Dyes and chemicals'
      UNION ALL SELECT 4, 'WIP Zone', 'WIP-Z4', 'storage', 'Aisle D', 2000, 'Work in progress'
      UNION ALL SELECT 5, 'Finished Goods', 'FG-Z5', 'storage', 'Aisle E', 10000, 'Packed finished cartons'
      UNION ALL SELECT 6, 'Export Zone', 'EXP-Z6', 'storage', 'Aisle F', 10000, 'Export ready goods'
      UNION ALL SELECT 7, 'Returns Zone', 'RET-Z7', 'return', 'Aisle G', 1000, 'Customer returns'
      UNION ALL SELECT 8, 'Quarantine', 'QUAR-Z8', 'quarantine', 'Aisle H', 500, 'Inspection hold') v
WHERE NOT EXISTS (SELECT 1 FROM warehouse_zones WHERE warehouse_id = 1);

INSERT INTO warehouse_racks (id, company_id, warehouse_id, zone_id, rack_name, rack_code, rack_type, max_bins, max_weight_kg, notes)
SELECT v.id, 1, 1, v.zone_id, v.rack_name, v.rack_code, v.rack_type, v.max_bins, v.max_weight_kg, NULL
FROM (SELECT 1 id, 1 zone_id, 'Rack A01' rack_name, 'A01' rack_code, 'pallet' rack_type, 4 max_bins, 1000 max_weight_kg
      UNION ALL SELECT 2, 1, 'Rack A02', 'A02', 'pallet', 4, 1000
      UNION ALL SELECT 3, 1, 'Rack A03', 'A03', 'cantilever', 4, 800
      UNION ALL SELECT 4, 2, 'Rack B01', 'B01', 'flow', 4, 600
      UNION ALL SELECT 5, 2, 'Rack B02', 'B02', 'flow', 4, 600
      UNION ALL SELECT 6, 3, 'Rack C01', 'C01', 'pallet', 4, 900
      UNION ALL SELECT 7, 4, 'Rack D01', 'D01', 'flow', 4, 700
      UNION ALL SELECT 8, 4, 'Rack D02', 'D02', 'flow', 4, 700
      UNION ALL SELECT 9, 5, 'Rack E01', 'E01', 'pallet', 4, 1200
      UNION ALL SELECT 10, 5, 'Rack E02', 'E02', 'pallet', 4, 1200
      UNION ALL SELECT 11, 6, 'Rack F01', 'F01', 'pallet', 4, 1200
      UNION ALL SELECT 12, 7, 'Rack G01', 'G01', 'pallet', 4, 800
      UNION ALL SELECT 13, 8, 'Rack H01', 'H01', 'pallet', 4, 800) v
WHERE NOT EXISTS (SELECT 1 FROM warehouse_racks WHERE warehouse_id = 1);

INSERT INTO warehouse_bins (company_id, warehouse_id, zone_id, rack_id, bin_code, bin_type, max_qty, max_weight_kg, is_active)
SELECT 1, 1, r.zone_id, r.id, CONCAT(r.rack_code, '-', LPAD(n.n, 2, '0')), 'shelf', 50, 500, 1
FROM warehouse_racks r
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHERE r.warehouse_id = 1 AND r.id BETWEEN 1 AND 13
  AND NOT EXISTS (SELECT 1 FROM warehouse_bins b WHERE b.rack_id = r.id AND b.bin_code = CONCAT(r.rack_code, '-', LPAD(n.n, 2, '0')));

-- ═══════════════════════════════════════════════════════════════════════
-- 7. DEMO SEED — Cameras, Serials, Barcodes, QR, Gate, Dock, AI, Events
-- ═══════════════════════════════════════════════════════════════════════

INSERT INTO gate_cameras (company_id, camera_code, name, station, warehouse_id, zone_id, status)
SELECT 1, v.camera_code, v.name, v.station, v.warehouse_id, v.zone_id, 'active'
FROM (SELECT 'GATE-01' camera_code, 'Main Gate Camera' name, 'gate' station, 1 warehouse_id, NULL zone_id
      UNION ALL SELECT 'DOCK-01', 'Fabric Dock Camera', 'dock', 1, 1
      UNION ALL SELECT 'DOCK-02', 'Accessory Dock Camera', 'dock', 1, 2
      UNION ALL SELECT 'RACK-A01', 'Rack A Zone Camera', 'rack', 1, 1
      UNION ALL SELECT 'FORKLIFT-01', 'Forklift Zone Camera', 'forklift', 1, 1
      UNION ALL SELECT 'LOADING-01', 'Loading Dock Camera', 'loading', 1, 6) v
WHERE NOT EXISTS (SELECT 1 FROM gate_cameras WHERE camera_code = v.camera_code);

-- Serialize existing fabric rolls R001..R010
INSERT INTO inventory_serials (company_id, serial_no, serial_type, ref_type, ref_id, item_id, color, shade, qty, warehouse_id, zone_id, rack_id, bin_id, location_code, status, first_received_at, last_moved_at)
SELECT 1,
  CONCAT('FAB-2028-', LPAD(r.id, 6, '0')),
  'roll', 'fabric_rolls', r.id, r.fabric_item_id, r.color, r.shade,
  r.remaining_weight_kg, 1,
  CASE WHEN r.id = 5 THEN 1 WHEN r.id = 7 THEN NULL ELSE 1 END,
  CASE WHEN r.id = 5 THEN 1 ELSE NULL END,
  CASE WHEN r.id = 5 THEN 1 ELSE NULL END,
  CASE WHEN r.id = 5 THEN 'A01-01' ELSE NULL END,
  CASE WHEN r.id = 5 THEN 'issued' WHEN r.id = 7 THEN 'blocked' ELSE 'available' END,
  DATE_SUB(NOW(), INTERVAL 5 DAY),
  CASE WHEN r.id = 5 THEN DATE_SUB(NOW(), INTERVAL 2 HOUR) ELSE DATE_SUB(NOW(), INTERVAL 5 DAY) END
FROM fabric_rolls r
WHERE r.company_id = 1 AND r.id BETWEEN 1 AND 10
  AND NOT EXISTS (SELECT 1 FROM inventory_serials s WHERE s.ref_type = 'fabric_rolls' AND s.ref_id = r.id);

-- Barcodes for those serials
INSERT INTO inventory_barcodes (company_id, serial_id, barcode_value, symbology, format, status)
SELECT s.company_id, s.id, s.serial_no, 'code128', 'roll', 'generated'
FROM inventory_serials s
WHERE s.serial_type = 'roll' AND s.serial_no LIKE 'FAB-2028-%'
  AND NOT EXISTS (SELECT 1 FROM inventory_barcodes bc WHERE bc.serial_id = s.id);

-- QR codes encoding the public passport URL
INSERT INTO inventory_qr_codes (company_id, serial_id, qr_data, encoded_url, status)
SELECT s.company_id, s.id, s.serial_no, CONCAT('https://erp.fashcognitive.com/inventory/', s.serial_no), 'generated'
FROM inventory_serials s
WHERE s.serial_type = 'roll' AND s.serial_no LIKE 'FAB-2028-%'
  AND NOT EXISTS (SELECT 1 FROM inventory_qr_codes q WHERE q.serial_id = s.id);

-- Link serial ids back onto fabric_rolls
UPDATE fabric_rolls r
JOIN inventory_serials s ON s.ref_type = 'fabric_rolls' AND s.ref_id = r.id
SET r.inventory_serial_id = s.id, r.barcode_value = s.serial_no, r.qr_code = s.serial_no
WHERE r.company_id = 1;

-- Demo gate entry + dock + OCR
INSERT INTO gate_entries (id, company_id, gate_entry_no, asn_id, supplier_id, po_no, vehicle_no, driver_name, driver_phone, container_no, seal_no, expected_rolls, expected_cartons, arrived_at, ocr_status, vehicle_verified, container_verified, seal_verified, po_verified, quantity_verified, status, gate_by)
SELECT 1, 1, 'GE-2026-0001', a.id,
  (SELECT MIN(id) FROM master_vendors WHERE company_id = 1),
  CONCAT('PO-', YEAR(NOW())), 'DHA-METRO-12-3456', 'Rahim Uddin', '01711-000000',
  'MSKU-1234567', 'SEAL-8899', 10, 4, DATE_SUB(NOW(), INTERVAL 2 HOUR),
  'matched', 1, 1, 1, 1, 1, 'docked', 1
FROM asn a
WHERE a.company_id = 1
ORDER BY a.id LIMIT 1;

INSERT INTO gate_entries (id, company_id, gate_entry_no, status, arrived_at, expected_rolls, expected_cartons)
SELECT 2, 1, 'GE-2026-0002', 'expected', DATE_ADD(NOW(), INTERVAL 3 HOUR), 20, 8
WHERE NOT EXISTS (SELECT 1 FROM gate_entries WHERE id = 2);

INSERT INTO dock_assignments (id, company_id, gate_entry_id, dock_no, dock_type, warehouse_id, status, assigned_at, started_at)
SELECT 1, 1, 1, 'DOCK-01', 'fabric', 1, 'unloading', DATE_SUB(NOW(), INTERVAL 1 HOUR), DATE_SUB(NOW(), INTERVAL 50 MINUTE)
WHERE NOT EXISTS (SELECT 1 FROM dock_assignments WHERE id = 1);

-- AI rack allocation suggestions for the first two rolls
INSERT INTO rack_allocations (company_id, inventory_serial_id, warehouse_id, zone_id, rack_id, bin_id, ai_score, factors, status)
SELECT 1, s.id, 1, 1,
  (SELECT id FROM warehouse_racks WHERE zone_id = 1 ORDER BY id LIMIT 1),
  (SELECT id FROM warehouse_bins WHERE zone_id = 1 AND bin_code = 'A01-01'),
  92.00, JSON_OBJECT('nearest_rack', 0.9, 'weight', 0.8, 'fifo', 1.0, 'space', 0.9), 'suggested'
FROM inventory_serials s WHERE s.ref_type = 'fabric_rolls' AND s.ref_id = 1
  AND NOT EXISTS (SELECT 1 FROM rack_allocations ra WHERE ra.inventory_serial_id = s.id);

INSERT INTO rack_allocations (company_id, inventory_serial_id, warehouse_id, zone_id, rack_id, bin_id, ai_score, factors, status)
SELECT 1, s.id, 1, 1,
  (SELECT id FROM warehouse_racks WHERE zone_id = 1 ORDER BY id LIMIT 1),
  (SELECT id FROM warehouse_bins WHERE zone_id = 1 AND bin_code = 'A01-02'),
  89.50, JSON_OBJECT('nearest_rack', 0.85, 'weight', 0.8, 'fifo', 0.95, 'space', 0.9), 'suggested'
FROM inventory_serials s WHERE s.ref_type = 'fabric_rolls' AND s.ref_id = 2
  AND NOT EXISTS (SELECT 1 FROM rack_allocations ra WHERE ra.inventory_serial_id = s.id);

-- Camera events
INSERT INTO camera_events (id, company_id, gate_camera_id, station, event_type, reference_type, reference_id, payload, confidence, is_alert, status, occurred_at)
SELECT 1, 1, (SELECT id FROM gate_cameras WHERE camera_code = 'GATE-01'), 'gate', 'vehicle_ocr', 'gate_entries', 1,
  JSON_OBJECT('vehicle_no', 'DHA-METRO-12-3456', 'container_no', 'MSKU-1234567', 'seal_no', 'SEAL-8899'),
  0.97, 0, 'verified', DATE_SUB(NOW(), INTERVAL 2 HOUR)
WHERE NOT EXISTS (SELECT 1 FROM camera_events WHERE id = 1);

INSERT INTO camera_events (id, company_id, gate_camera_id, station, event_type, reference_type, reference_id, payload, confidence, is_alert, status, occurred_at)
SELECT 2, 1, (SELECT id FROM gate_cameras WHERE camera_code = 'DOCK-01'), 'dock', 'carton_count', 'gate_entries', 1,
  JSON_OBJECT('counted', 4, 'expected', 4), 0.88, 0, 'verified', DATE_SUB(NOW(), INTERVAL 45 MINUTE)
WHERE NOT EXISTS (SELECT 1 FROM camera_events WHERE id = 2);

INSERT INTO camera_events (id, company_id, gate_camera_id, station, event_type, reference_type, reference_id, payload, confidence, is_alert, status, occurred_at)
SELECT 3, 1, (SELECT id FROM gate_cameras WHERE camera_code = 'RACK-A01'), 'rack', 'wrong_rack', 'inventory_serials', 3,
  JSON_OBJECT('expected', 'A01-02', 'detected', 'B01-01'), 0.79, 1, 'alert', DATE_SUB(NOW(), INTERVAL 20 MINUTE)
WHERE NOT EXISTS (SELECT 1 FROM camera_events WHERE id = 3);

-- OCR results for the gate event
INSERT INTO ocr_results (company_id, camera_event_id, gate_entry_id, ocr_type, detected_text, confidence)
SELECT 1, 1, 1, 'vehicle', 'DHA-METRO-12-3456', 0.97
WHERE NOT EXISTS (SELECT 1 FROM ocr_results WHERE camera_event_id = 1 AND ocr_type = 'vehicle');
INSERT INTO ocr_results (company_id, camera_event_id, gate_entry_id, ocr_type, detected_text, confidence)
SELECT 1, 1, 1, 'container', 'MSKU-1234567', 0.95
WHERE NOT EXISTS (SELECT 1 FROM ocr_results WHERE camera_event_id = 1 AND ocr_type = 'container');
INSERT INTO ocr_results (company_id, camera_event_id, gate_entry_id, ocr_type, detected_text, confidence)
SELECT 1, 1, 1, 'seal', 'SEAL-8899', 0.91
WHERE NOT EXISTS (SELECT 1 FROM ocr_results WHERE camera_event_id = 1 AND ocr_type = 'seal');

-- Full traceability journey for roll R005 (issued) demonstrating the passport
INSERT INTO inventory_traceability (company_id, entity_type, entity_id, station, location_code, reference_type, reference_id, reference_no, status_at, occurred_at)
SELECT 1, 'inventory_serials', s.id, v.station, v.loc, v.ref_type, NULL, v.ref_no, v.status_at, DATE_SUB(NOW(), INTERVAL v.days DAY)
FROM inventory_serials s
JOIN (SELECT 'supplier' station, NULL loc, NULL ref_type, 'SUP-FAB-001' ref_no, 'supplier' status_at, 30 days
      UNION ALL SELECT 'po', NULL, 'purchase_orders', 'PO-2026-0001', 'ordered', 25
      UNION ALL SELECT 'asn', NULL, 'asn', 'ASN-2026-001', 'in_transit', 10
      UNION ALL SELECT 'gate', 'GATE-01', 'gate_entries', 'GE-2026-0001', 'verified', 2
      UNION ALL SELECT 'dock', 'DOCK-01', 'dock_assignments', 'DOCK-01', 'unloading', 1
      UNION ALL SELECT 'grn', NULL, 'grn', 'GRN-2026-001', 'received', 1
      UNION ALL SELECT 'inspection', 'QUAR-Z8', 'inspection_sessions', NULL, 'passed', 0
      UNION ALL SELECT 'putaway', 'A01-01', 'rack_allocations', 'A01-01', 'confirmed', 0
      UNION ALL SELECT 'issue', NULL, 'material_issues', 'MI-2026-001', 'issued', 0
      UNION ALL SELECT 'production', NULL, 'production_orders', 'PROD-2026-001', 'in_production', 0) v
WHERE s.ref_type = 'fabric_rolls' AND s.ref_id = 5
  AND NOT EXISTS (SELECT 1 FROM inventory_traceability t WHERE t.entity_type = 'inventory_serials' AND t.entity_id = s.id AND t.station = v.station);

COMMIT;
