-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 6 — Warehouse Ops Smoothing 2 (Alerts, Expiry/FEFO, Scan, KPIs)
--  1. warehouse_alerts table (alert center + bell badge)
--  2. inventory_serials.expiry_date + warehouse_tasks.due_at (guarded adds)
--  3. Demo data: expiry dates on serials, overdue task, seeded alerts
--  Idempotent: safe to re-run (guarded column adds, IF NOT EXISTS).
-- ═══════════════════════════════════════════════════════════════════════
START TRANSACTION;

-- ---- 1. warehouse_alerts ----
CREATE TABLE IF NOT EXISTS warehouse_alerts (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  alert_no varchar(50) NOT NULL,
  alert_type enum('low_stock','expiring','dead_stock','aging_hold','uninspected_return','overdue_task','short_allocation','variance','system') NOT NULL DEFAULT 'system',
  severity enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  title varchar(255) NOT NULL,
  message text,
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  ref_no varchar(100) DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  status enum('open','acknowledged','resolved','dismissed') NOT NULL DEFAULT 'open',
  first_seen_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at datetime DEFAULT NULL,
  resolved_by int unsigned 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_wa_dedup (company_id, alert_type, ref_type, ref_id),
  KEY idx_wa_status (company_id, status, severity),
  KEY idx_wa_type (alert_type),
  KEY idx_wa_severity (severity),
  KEY idx_wa_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 2. Guarded column adds ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_serials' AND COLUMN_NAME = 'expiry_date');
SET @sql := IF(@col = 0,
  'ALTER TABLE inventory_serials ADD COLUMN expiry_date date DEFAULT NULL AFTER batch_id',
  '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 = 'warehouse_tasks' AND COLUMN_NAME = 'due_at');
SET @sql := IF(@col = 0,
  'ALTER TABLE warehouse_tasks ADD COLUMN due_at datetime DEFAULT NULL AFTER assigned_to',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- 3. Demo: expiry dates on a few available serials (for FEFO + expiry alerts) ----
UPDATE inventory_serials s
JOIN master_items i ON i.id = s.item_id
SET s.expiry_date = CASE s.serial_no
  WHEN 'FAB-2028-000006' THEN CURDATE() + INTERVAL 9 DAY      -- expires very soon (item 2, available A01-02)
  WHEN 'FAB-2028-000004' THEN CURDATE() + INTERVAL 26 DAY     -- item 1, available A01-01
  WHEN 'FAB-2028-000005' THEN CURDATE() + INTERVAL 55 DAY     -- item 2, issued A02-01
  ELSE s.expiry_date
END
WHERE s.serial_no IN ('FAB-2028-000006','FAB-2028-000004','FAB-2028-000005')
  AND s.expiry_date IS NULL;

-- ---- 4. Demo: due_at on pending high-priority tasks (overdue visibility) ----
UPDATE warehouse_tasks
SET due_at = NOW() - INTERVAL 2 HOUR
WHERE task_no = 'RPL-2026-00002' AND due_at IS NULL;

UPDATE warehouse_tasks
SET due_at = NOW() + INTERVAL 1 HOUR
WHERE task_no = 'TSK-2026-00001' AND due_at IS NULL;

-- ---- 5. Seed demo alerts (guarded so re-runs don't duplicate) ----
INSERT INTO warehouse_alerts
  (company_id, warehouse_id, alert_no, alert_type, severity, title, message,
   ref_type, ref_id, ref_no, item_id, bin_code, status, first_seen_at, last_seen_at, created_at)
SELECT 1, 1, 'WAL-2026-000001', 'aging_hold', 'critical', 'Quality hold ageing',
       'Hold #1 on FAB-002 has been held for over 24 hours with no release decision.',
       'stock_holds', sh.id, CONCAT('#', sh.id), sh.item_id, sh.bin_code, 'open',
       NOW() - INTERVAL 26 HOUR, NOW() - INTERVAL 26 HOUR, NOW()
FROM stock_holds sh
WHERE sh.status = 'held'
  AND NOT EXISTS (SELECT 1 FROM warehouse_alerts wa WHERE wa.alert_type = 'aging_hold' AND wa.ref_type = 'stock_holds' AND wa.ref_id = sh.id AND wa.status IN ('open','acknowledged'));

INSERT INTO warehouse_alerts
  (company_id, warehouse_id, alert_no, alert_type, severity, title, message,
   ref_type, ref_id, ref_no, status, first_seen_at, last_seen_at, created_at)
SELECT 1, 1, 'WAL-2026-000002', 'uninspected_return', 'warning', 'Return awaiting inspection',
       'RMA-2026-00002 has been received but has no quality inspection yet.',
       'warehouse_returns', r.id, r.return_no, 'open',
       r.received_at, r.received_at, NOW()
FROM warehouse_returns r
WHERE r.return_no = 'RMA-2026-00002' AND r.status = 'received' AND r.inspection_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM warehouse_alerts wa WHERE wa.alert_type = 'uninspected_return' AND wa.ref_type = 'warehouse_returns' AND wa.ref_id = r.id AND wa.status IN ('open','acknowledged'));

INSERT INTO warehouse_alerts
  (company_id, warehouse_id, alert_no, alert_type, severity, title, message,
   ref_type, ref_id, ref_no, item_id, status, first_seen_at, last_seen_at, created_at)
SELECT 1, 1, 'WAL-2026-000003', 'expiring', 'warning', 'Stock expiring within 30 days',
       'Serial FAB-2028-000006 (FAB-002) expires in 9 days - dispatch via FEFO or move to a priority location.',
       'inventory_serials', s.id, s.serial_no, s.item_id, 'open', NOW(), NOW(), NOW()
FROM inventory_serials s
WHERE s.serial_no = 'FAB-2028-000006'
  AND NOT EXISTS (SELECT 1 FROM warehouse_alerts wa WHERE wa.alert_type = 'expiring' AND wa.ref_type = 'inventory_serials' AND wa.ref_id = s.id AND wa.status IN ('open','acknowledged'));

INSERT INTO warehouse_alerts
  (company_id, warehouse_id, alert_no, alert_type, severity, title, message,
   ref_type, ref_id, ref_no, status, first_seen_at, last_seen_at, created_at)
SELECT 1, 1, 'WAL-2026-000004', 'overdue_task', 'warning', 'Task past due',
       'Replenishment task RPL-2026-00002 is past its due time and still pending.',
       'warehouse_tasks', wt.id, wt.task_no, 'open', wt.due_at, wt.due_at, NOW()
FROM warehouse_tasks wt
WHERE wt.task_no = 'RPL-2026-00002' AND wt.status IN ('pending','assigned','in_progress')
  AND NOT EXISTS (SELECT 1 FROM warehouse_alerts wa WHERE wa.alert_type = 'overdue_task' AND wa.ref_type = 'warehouse_tasks' AND wa.ref_id = wt.id AND wa.status IN ('open','acknowledged'));

COMMIT;
