-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 7 — Warehouse Ops Excellence (Dock, Slotting, Dispositions,
--             Scorecards, Variance Approval, Inbound Pipeline, Auto-Orch)
--  1. dock_appointments table (scheduled inbound/outbound dock slots)
--  2. stock_dispositions table (expiry / dead-stock disposition workflow)
--  3. stocktake_batches approval columns (variance approval gate)
--  4. re_slot_suggestions.task_id + resolved_at (relocation task link)
--  Idempotent: safe to re-run (guarded column adds, IF NOT EXISTS).
-- ═══════════════════════════════════════════════════════════════════════
START TRANSACTION;

-- ---- 1. dock_appointments ----
CREATE TABLE IF NOT EXISTS dock_appointments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  appointment_no varchar(50) NOT NULL,
  direction enum('inbound','outbound') NOT NULL DEFAULT 'inbound',
  dock_no varchar(20) DEFAULT NULL,
  dock_type enum('fabric','accessory','chemical','returns','loading') DEFAULT NULL,
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  ref_no varchar(100) DEFAULT NULL,
  scheduled_at datetime DEFAULT NULL,
  vehicle_no varchar(50) DEFAULT NULL,
  driver_name varchar(120) DEFAULT NULL,
  driver_mobile varchar(30) DEFAULT NULL,
  transporter_name varchar(120) DEFAULT NULL,
  carrier_ref varchar(100) DEFAULT NULL,
  status enum('scheduled','arrived','docked','unloading','loading','completed','cancelled','missed') NOT NULL DEFAULT 'scheduled',
  arrived_at datetime DEFAULT NULL,
  docked_at datetime DEFAULT NULL,
  started_at datetime DEFAULT NULL,
  ended_at datetime DEFAULT NULL,
  dwell_minutes int unsigned DEFAULT NULL,
  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_dapp_no (company_id, appointment_no),
  KEY idx_dapp_status (company_id, status, scheduled_at),
  KEY idx_dapp_direction (direction, scheduled_at),
  KEY idx_dapp_ref (ref_type, ref_id),
  KEY idx_dapp_dock (dock_no, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 2. stock_dispositions ----
CREATE TABLE IF NOT EXISTS stock_dispositions (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  disposition_no varchar(50) NOT NULL,
  disposition_type enum('markdown','salvage','return_to_supplier','quarantine','donate','scrap') NOT NULL DEFAULT 'markdown',
  status enum('draft','pending_approval','approved','rejected','completed','cancelled') NOT NULL DEFAULT 'draft',
  alert_id int unsigned DEFAULT NULL,
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  ref_no varchar(100) DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  color varchar(30) DEFAULT NULL,
  size varchar(30) DEFAULT NULL,
  lot_no varchar(80) DEFAULT NULL,
  batch_no varchar(80) DEFAULT NULL,
  expiry_date date DEFAULT NULL,
  qty decimal(12,3) NOT NULL DEFAULT 0,
  uom_id int unsigned DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  reason text,
  decision_notes text,
  estimated_value decimal(14,2) DEFAULT NULL,
  requested_by int unsigned DEFAULT NULL,
  requested_at datetime DEFAULT CURRENT_TIMESTAMP,
  approved_by int unsigned DEFAULT NULL,
  approved_at datetime DEFAULT NULL,
  completed_by int unsigned DEFAULT NULL,
  completed_at datetime DEFAULT NULL,
  stock_ledger_id 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_sd_no (company_id, disposition_no),
  KEY idx_sd_status (company_id, status),
  KEY idx_sd_type (disposition_type),
  KEY idx_sd_alert (alert_id),
  KEY idx_sd_ref (ref_type, ref_id),
  KEY idx_sd_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 3. Guarded adds: stocktake variance approval ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stocktake_batches' AND COLUMN_NAME = 'approval_status');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN approval_status enum(''not_required'',''pending'',''approved'',''rejected'') NOT NULL DEFAULT ''not_required'' AFTER status',
  '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 = 'stocktake_batches' AND COLUMN_NAME = 'approval_required');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN approval_required tinyint(1) NOT NULL DEFAULT 0 AFTER approval_status',
  '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 = 'stocktake_batches' AND COLUMN_NAME = 'approval_threshold');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN approval_threshold decimal(12,2) NOT NULL DEFAULT 0 AFTER approval_required',
  '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 = 'stocktake_batches' AND COLUMN_NAME = 'approved_by');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN approved_by int unsigned DEFAULT NULL AFTER approval_threshold',
  '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 = 'stocktake_batches' AND COLUMN_NAME = 'approved_at');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN approved_at datetime DEFAULT NULL AFTER approved_by',
  '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 = 'stocktake_batches' AND COLUMN_NAME = 'rejection_reason');
SET @sql := IF(@col = 0,
  'ALTER TABLE stocktake_batches ADD COLUMN rejection_reason text AFTER approved_at',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- 4. Guarded adds: re_slot_suggestions task link ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 're_slot_suggestions' AND COLUMN_NAME = 'task_id');
SET @sql := IF(@col = 0,
  'ALTER TABLE re_slot_suggestions ADD COLUMN task_id int unsigned DEFAULT NULL AFTER status',
  '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 = 're_slot_suggestions' AND COLUMN_NAME = 'resolved_at');
SET @sql := IF(@col = 0,
  'ALTER TABLE re_slot_suggestions ADD COLUMN resolved_at datetime DEFAULT NULL AFTER task_id',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

COMMIT;
