-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 8 — Warehouse Smooth-Run (Dock Doors, Equipment, Snapshots,
--             Short-Pick/Backorders, Shifts & Handover)
--  1. warehouse_dock_doors  — named dock door master data
--  2. warehouse_equipment   — forklift / dock / equipment registry
--  3. equipment_maintenance_logs — preventive + corrective schedule
--  4. inventory_snapshots   — period-end stock freeze + valuation
--  5. inventory_snapshot_items — per-item snapshot rows
--  6. outbound_backorders   — short-pick / partial-pick backorder lines
--  7. warehouse_shifts      — shift register
--  8. shift_handovers       — shift-to-shift handover log
--  9. dock_appointments.dock_door_id (guarded add)
--  Idempotent: safe to re-run (guarded column adds, IF NOT EXISTS).
-- ═══════════════════════════════════════════════════════════════════════
START TRANSACTION;

-- ---- 1. warehouse_dock_doors ----
CREATE TABLE IF NOT EXISTS warehouse_dock_doors (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  door_code varchar(20) NOT NULL,
  door_name varchar(120) NOT NULL,
  dock_type enum('fabric','accessory','chemical','returns','loading') NOT NULL DEFAULT 'loading',
  direction enum('inbound','outbound','both') NOT NULL DEFAULT 'both',
  capacity_trucks int unsigned NOT NULL DEFAULT 1,
  has_leveler tinyint(1) NOT NULL DEFAULT 0,
  has_dock_shelter tinyint(1) NOT NULL DEFAULT 0,
  status enum('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  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_wdd_code (company_id, door_code),
  KEY idx_wdd_status (company_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 2. warehouse_equipment ----
CREATE TABLE IF NOT EXISTS warehouse_equipment (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  equipment_code varchar(30) NOT NULL,
  equipment_name varchar(120) NOT NULL,
  equipment_type enum('forklift','reach_truck','pallet_jack','stacker','dock_leveler','dock_door','conveyor','scanner','other') NOT NULL DEFAULT 'forklift',
  location varchar(120) DEFAULT NULL,
  serial_number varchar(100) DEFAULT NULL,
  status enum('active','in_service','maintenance','out_of_service','retired') NOT NULL DEFAULT 'active',
  last_service_date date DEFAULT NULL,
  next_service_due date DEFAULT NULL,
  service_interval_days int unsigned DEFAULT 90,
  operator_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_we_code (company_id, equipment_code),
  KEY idx_we_status (company_id, status),
  KEY idx_we_due (next_service_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 3. equipment_maintenance_logs ----
CREATE TABLE IF NOT EXISTS equipment_maintenance_logs (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  equipment_id int unsigned NOT NULL,
  maintenance_no varchar(50) NOT NULL,
  maintenance_type enum('preventive','corrective','inspection','repair') NOT NULL DEFAULT 'preventive',
  status enum('scheduled','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  scheduled_date date DEFAULT NULL,
  started_at datetime DEFAULT NULL,
  completed_date date DEFAULT NULL,
  completed_by int unsigned DEFAULT NULL,
  cost decimal(12,2) DEFAULT NULL,
  downtime_minutes int unsigned DEFAULT NULL,
  note 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_eml_no (company_id, maintenance_no),
  KEY idx_eml_status (company_id, status, scheduled_date),
  KEY idx_eml_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 4. inventory_snapshots ----
CREATE TABLE IF NOT EXISTS inventory_snapshots (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  snapshot_no varchar(50) NOT NULL,
  period_start date DEFAULT NULL,
  period_end date DEFAULT NULL,
  status enum('draft','closed') NOT NULL DEFAULT 'draft',
  item_count int unsigned NOT NULL DEFAULT 0,
  total_value decimal(16,2) NOT NULL DEFAULT 0,
  created_by int unsigned DEFAULT NULL,
  closed_by int unsigned DEFAULT NULL,
  closed_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_is_no (company_id, snapshot_no),
  KEY idx_is_status (company_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 5. inventory_snapshot_items ----
CREATE TABLE IF NOT EXISTS inventory_snapshot_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  snapshot_id int unsigned NOT NULL,
  warehouse_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  item_code varchar(100) DEFAULT NULL,
  item_name varchar(255) DEFAULT NULL,
  uom_name varchar(100) DEFAULT NULL,
  qty decimal(16,4) NOT NULL DEFAULT 0,
  unit_cost decimal(16,4) DEFAULT NULL,
  line_value decimal(16,2) NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_isi_snapshot (snapshot_id),
  KEY idx_isi_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 6. outbound_backorders ----
CREATE TABLE IF NOT EXISTS outbound_backorders (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  backorder_no varchar(50) NOT NULL,
  outbound_order_id int unsigned DEFAULT NULL,
  outbound_order_item_id int unsigned DEFAULT NULL,
  wave_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  uom_id int unsigned DEFAULT NULL,
  requested_qty decimal(14,4) NOT NULL DEFAULT 0,
  picked_qty decimal(14,4) NOT NULL DEFAULT 0,
  backordered_qty decimal(14,4) NOT NULL DEFAULT 0,
  reason enum('out_of_stock','damaged','misallocated','other') NOT NULL DEFAULT 'out_of_stock',
  reason_note text,
  status enum('open','filled','cancelled') NOT NULL DEFAULT 'open',
  created_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  filled_at datetime DEFAULT NULL,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_ob_no (company_id, backorder_no),
  KEY idx_ob_status (company_id, status),
  KEY idx_ob_order (outbound_order_id),
  KEY idx_ob_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 7. warehouse_shifts ----
CREATE TABLE IF NOT EXISTS warehouse_shifts (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT NULL,
  shift_no varchar(50) NOT NULL,
  shift_date date NOT NULL,
  shift_name enum('morning','evening','night') NOT NULL DEFAULT 'morning',
  supervisor_id int unsigned DEFAULT NULL,
  status enum('open','closed') NOT NULL DEFAULT 'open',
  opened_by int unsigned DEFAULT NULL,
  opened_at datetime DEFAULT CURRENT_TIMESTAMP,
  closed_by int unsigned DEFAULT NULL,
  closed_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_ws_no (company_id, shift_no),
  KEY idx_ws_status (company_id, shift_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 8. shift_handovers ----
CREATE TABLE IF NOT EXISTS shift_handovers (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  shift_id int unsigned NOT NULL,
  handover_no varchar(50) NOT NULL,
  from_user_id int unsigned DEFAULT NULL,
  to_user_id int unsigned DEFAULT NULL,
  open_tasks int unsigned NOT NULL DEFAULT 0,
  note text,
  created_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sh_no (company_id, handover_no),
  KEY idx_sh_shift (shift_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

COMMIT;
