-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 5 — Quality-in-Warehouse (Quality Gate)
--  1. wh_quality_inspections + wh_quality_inspection_items (AQL sampling board)
--  2. warehouse_returns.inspection_id + stock_holds.inspection_id links
--  3. Seed warehouse defect codes into master_defect_library
--  4. Demo inspections (return / hold-release / receiving)
--  Idempotent: safe to re-run (guarded column adds, IF NOT EXISTS).
-- ═══════════════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;

-- ---- 1. wh_quality_inspections ----
CREATE TABLE IF NOT EXISTS wh_quality_inspections (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  inspection_no varchar(50) NOT NULL,
  inspection_type enum('receiving','return','hold_release','random') NOT NULL DEFAULT 'random',
  source_type enum('grn','return','stock_hold') DEFAULT NULL,
  source_id int unsigned DEFAULT NULL,
  source_no varchar(100) DEFAULT NULL,
  status enum('draft','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
  verdict enum('pass','fail','conditional') DEFAULT NULL,
  aql_value decimal(4,2) NOT NULL DEFAULT 2.50,
  total_items int unsigned NOT NULL DEFAULT 0,
  sampled_items int unsigned NOT NULL DEFAULT 0,
  inspected_items int unsigned NOT NULL DEFAULT 0,
  total_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  sample_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  inspected_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  defects_found int unsigned NOT NULL DEFAULT 0,
  pass_rate decimal(5,2) DEFAULT NULL,
  inspected_by int unsigned DEFAULT NULL,
  started_at datetime DEFAULT NULL,
  completed_at datetime 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_wqi_no (inspection_no, company_id),
  KEY idx_wqi_company (company_id),
  KEY idx_wqi_type (inspection_type),
  KEY idx_wqi_status (status),
  KEY idx_wqi_source (source_type, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 2. wh_quality_inspection_items ----
CREATE TABLE IF NOT EXISTS wh_quality_inspection_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  inspection_id int unsigned NOT NULL,
  item_id int unsigned NOT NULL,
  item_code varchar(50) 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 NOT NULL DEFAULT 1,
  source_item_id int unsigned DEFAULT NULL,
  source_item_no varchar(100) DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  total_qty decimal(14,4) NOT NULL,
  sample_qty decimal(14,4) NOT NULL,
  inspected_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  defects_found int unsigned NOT NULL DEFAULT 0,
  defect_codes text,
  defect_reason varchar(500) DEFAULT NULL,
  verdict enum('pass','fail','conditional') DEFAULT NULL,
  disposition enum('accept','reject','quarantine') DEFAULT NULL,
  inspected_by int unsigned DEFAULT NULL,
  inspected_at datetime DEFAULT NULL,
  notes varchar(500) 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_wqii_inspection (inspection_id),
  KEY idx_wqii_item (item_id),
  KEY idx_wqii_verdict (verdict),
  CONSTRAINT fk_wqii_inspection FOREIGN KEY (inspection_id) REFERENCES wh_quality_inspections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 3. Link columns (guarded add) ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'warehouse_returns' AND COLUMN_NAME = 'inspection_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE warehouse_returns ADD COLUMN inspection_id int unsigned DEFAULT NULL AFTER reference_no',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_holds' AND COLUMN_NAME = 'inspection_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE stock_holds ADD COLUMN inspection_id int unsigned DEFAULT NULL AFTER reference_no',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- 4. Seed warehouse defect codes (INSERT IGNORE on code+company) ----
INSERT IGNORE INTO master_defect_library
  (category, code, description, defect_type, inspection_type, is_active, company_id) VALUES
  ('major', 'WH-DMG-01', 'Damaged in transit (crushed / torn)', 'packaging', 'all', 1, 1),
  ('major', 'WH-MST-01', 'Moisture / water damage', 'storage', 'all', 1, 1),
  ('minor', 'WH-PCK-01', 'Damaged packaging / carton', 'packaging', 'all', 1, 1),
  ('major', 'WH-CLR-01', 'Color shade mismatch vs approved sample', 'color', 'all', 1, 1),
  ('minor', 'WH-CNT-01', 'Contamination / foreign matter', 'fabric', 'all', 1, 1),
  ('minor', 'WH-LBL-01', 'Missing / incorrect label or barcode', 'labeling', 'all', 1, 1),
  ('critical', 'WH-EXP-01', 'Expired or near-expiry shelf life', 'storage', 'all', 1, 1),
  ('minor', 'WH-STN-01', 'Stain / discoloration', 'fabric', 'all', 1, 1),
  ('major', 'WH-WRP-01', 'Broken / loose roll or bale', 'fabric', 'all', 1, 1),
  ('minor', 'WH-UNT-01', 'Unit count mismatch vs packing list', 'documentation', 'all', 1, 1);

-- ---- 5. Demo: link phase-4 demo rows to their inspection ----
-- (inspections are created below, links are updated at the end)

-- ---- 6. Demo inspection: RETURN (in_progress) on RMA-2026-00001 ----
INSERT INTO wh_quality_inspections
  (company_id, warehouse_id, inspection_no, inspection_type, source_type, source_id, source_no,
   status, verdict, aql_value, total_items, sampled_items, inspected_items,
   total_qty, sample_qty, inspected_qty, defects_found, pass_rate, inspected_by, started_at, notes, created_at)
SELECT 1, r.warehouse_id, 'WQ-2026-00001', 'return', 'return', r.id, r.return_no,
       'in_progress', NULL, 2.50,
       (SELECT COUNT(*) FROM warehouse_return_items wri WHERE wri.return_id = r.id),
       0, 0,
       (SELECT COALESCE(SUM(wri.qty), 0) FROM warehouse_return_items wri WHERE wri.return_id = r.id),
       0, 0, 0, NULL, 1, NOW(),
       'Return inspection for customer RMA awaiting AQL sampling', NOW()
FROM warehouse_returns r
WHERE r.return_no = 'RMA-2026-00001'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspections wi WHERE wi.inspection_no = 'WQ-2026-00001');

INSERT INTO wh_quality_inspection_items
  (company_id, inspection_id, item_id, item_code, color, size, lot_no, batch_no, uom_id,
   source_item_id, source_item_no, total_qty, sample_qty)
SELECT 1, wi.id, wri.item_id, wri.item_code, wri.color, wri.size, wri.lot_no, wri.batch_no, wri.uom_id,
       wri.id, wri.item_code, wri.qty, LEAST(20, GREATEST(3, CEIL(SQRT(wri.qty))))
FROM wh_quality_inspections wi
JOIN warehouse_return_items wri ON wri.return_id = wi.source_id
WHERE wi.inspection_no = 'WQ-2026-00001'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspection_items ii WHERE ii.inspection_id = wi.id AND ii.source_item_id = wri.id);

UPDATE wh_quality_inspections wi
JOIN (SELECT inspection_id, COUNT(*) n, COALESCE(SUM(sample_qty),0) sq
      FROM wh_quality_inspection_items GROUP BY inspection_id) x ON x.inspection_id = wi.id
SET wi.sampled_items = x.n, wi.sample_qty = x.sq
WHERE wi.inspection_no = 'WQ-2026-00001';

-- ---- 7. Demo inspection: HOLD-RELEASE (draft) on FAB-002 QA hold ----
INSERT INTO wh_quality_inspections
  (company_id, warehouse_id, inspection_no, inspection_type, source_type, source_id, source_no,
   status, verdict, aql_value, total_items, sampled_items, inspected_items,
   total_qty, sample_qty, inspected_qty, defects_found, pass_rate, created_at)
SELECT 1, sh.warehouse_id, 'WQ-2026-00002', 'hold_release', 'stock_hold', sh.id, CONCAT('#', sh.id),
       'draft', NULL, 2.50, 1, 1, 0, sh.hold_qty, 5, 0, 0, NULL, NOW()
FROM stock_holds sh
JOIN master_items i ON i.id = sh.item_id
WHERE i.code = 'FAB-002' AND sh.status = 'held'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspections wi WHERE wi.inspection_no = 'WQ-2026-00002');

INSERT INTO wh_quality_inspection_items
  (company_id, inspection_id, item_id, item_code, color, size, lot_no, batch_no, uom_id,
   source_item_id, source_item_no, bin_id, bin_code, total_qty, sample_qty)
SELECT 1, wi.id, sh.item_id, sh.item_code, sh.color, sh.size, sh.lot_no, sh.batch_no,
       COALESCE((SELECT uom_id FROM master_items WHERE id = sh.item_id), 1),
       sh.id, sh.reference_no, sh.bin_id, sh.bin_code, sh.hold_qty, 5
FROM wh_quality_inspections wi
JOIN stock_holds sh ON sh.id = wi.source_id
WHERE wi.inspection_no = 'WQ-2026-00002'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspection_items ii WHERE ii.inspection_id = wi.id AND ii.source_item_id = sh.id);

-- ---- 8. Demo inspection: RECEIVING (completed, pass) on GRN-2026-0002 ----
INSERT INTO wh_quality_inspections
  (company_id, warehouse_id, inspection_no, inspection_type, source_type, source_id, source_no,
   status, verdict, aql_value, total_items, sampled_items, inspected_items,
   total_qty, sample_qty, inspected_qty, defects_found, pass_rate, inspected_by, started_at, completed_at,
   notes, created_at)
SELECT 1, g.warehouse_id, 'WQ-2026-00003', 'receiving', 'grn', g.id, g.grn_no,
       'completed', 'pass', 2.50,
       (SELECT COUNT(*) FROM grn_items gi WHERE gi.grn_id = g.id), 6, 6,
       g.total_qty, 300, 300, 0, 100.00, 1, NOW(), NOW(),
       'Post-receipt spot check on GRN-2026-0002 - all samples passed', NOW()
FROM grn g
WHERE g.grn_no = 'GRN-2026-0002'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspections wi WHERE wi.inspection_no = 'WQ-2026-00003');

INSERT INTO wh_quality_inspection_items
  (company_id, inspection_id, item_id, item_code, color, size, lot_no, batch_no, uom_id,
   source_item_id, source_item_no, total_qty, sample_qty, inspected_qty, defects_found, verdict, disposition, inspected_by, inspected_at)
SELECT 1, wi.id, gi.item_id, i.code, gi.color, gi.size, gi.lot_no, gi.batch_no, gi.uom_id,
       gi.id, i.code, gi.received_qty, 50, 50, 0, 'pass', 'accept', 1, NOW()
FROM wh_quality_inspections wi
JOIN grn g ON g.id = wi.source_id
JOIN grn_items gi ON gi.grn_id = g.id
JOIN master_items i ON i.id = gi.item_id
WHERE wi.inspection_no = 'WQ-2026-00003'
  AND NOT EXISTS (SELECT 1 FROM wh_quality_inspection_items ii WHERE ii.inspection_id = wi.id AND ii.source_item_id = gi.id);

-- ---- 9. Link demo rows to their inspections ----
UPDATE warehouse_returns r
JOIN wh_quality_inspections wi ON wi.source_type = 'return' AND wi.source_id = r.id
SET r.inspection_id = wi.id
WHERE r.return_no = 'RMA-2026-00001' AND r.inspection_id IS NULL;

UPDATE stock_holds sh
JOIN wh_quality_inspections wi ON wi.source_type = 'stock_hold' AND wi.source_id = sh.id
SET sh.inspection_id = wi.id
WHERE sh.inspection_id IS NULL;

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
