-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 3 — Warehouse Operations Smoothing
--  1. pick_waves: picker assignment + timing for throughput KPIs
--  2. Stocktake / cycle count tables
-- Idempotent: safe to re-run.
-- ═══════════════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;

ALTER TABLE pick_waves
  ADD COLUMN assigned_to int unsigned DEFAULT NULL AFTER created_by,
  ADD COLUMN started_at datetime DEFAULT NULL AFTER released_at,
  ADD KEY idx_pw_assignee (assigned_to);

CREATE TABLE IF NOT EXISTS stocktake_batches (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  batch_no varchar(30) NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  scope varchar(20) NOT NULL DEFAULT 'all',
  status enum('draft','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
  total_items int unsigned NOT NULL DEFAULT 0,
  counted_items int unsigned NOT NULL DEFAULT 0,
  variance_items int unsigned NOT NULL DEFAULT 0,
  variance_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  started_by int unsigned DEFAULT NULL,
  started_at datetime DEFAULT NULL,
  completed_by int unsigned DEFAULT NULL,
  completed_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_stk_batch (batch_no, company_id),
  KEY idx_stk_company (company_id),
  KEY idx_stk_wh (warehouse_id),
  KEY idx_stk_status (status),
  CONSTRAINT fk_stk_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stocktake_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  batch_id int unsigned NOT NULL,
  bin_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  item_code varchar(50) DEFAULT NULL,
  system_qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  counted_qty decimal(14,4) DEFAULT NULL,
  variance decimal(14,4) NOT NULL DEFAULT 0.0000,
  status enum('pending','counted','verified') NOT NULL DEFAULT 'pending',
  counted_by int unsigned DEFAULT NULL,
  counted_at datetime DEFAULT NULL,
  notes varchar(255) DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sti_batch (batch_id),
  KEY idx_sti_bin (bin_id),
  KEY idx_sti_status (status),
  CONSTRAINT fk_sti_batch FOREIGN KEY (batch_id) REFERENCES stocktake_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo: one in-progress stocktake batch over occupied FAB-Z1 bins
INSERT INTO stocktake_batches (company_id, warehouse_id, batch_no, scope, status, total_items, counted_items, started_by, started_at, created_at)
SELECT 1, 1, 'STK-2026-00001', 'all', 'in_progress', 0, 0, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM stocktake_batches WHERE batch_no = 'STK-2026-00001');

INSERT INTO stocktake_items (company_id, batch_id, bin_id, bin_code, item_id, item_code, system_qty, status)
SELECT 1, st.id, b.id, b.bin_code, b.current_item_id, i.code, b.current_qty, 'pending'
FROM stocktake_batches st
JOIN warehouse_bins b ON b.is_occupied = 1 AND b.current_item_id IS NOT NULL
LEFT JOIN master_items i ON i.id = b.current_item_id
WHERE st.batch_no = 'STK-2026-00001'
  AND NOT EXISTS (SELECT 1 FROM stocktake_items si WHERE si.batch_id = st.id AND si.bin_id = b.id);

UPDATE stocktake_batches st
SET total_items = (SELECT COUNT(*) FROM stocktake_items si WHERE si.batch_id = st.id)
WHERE st.batch_no = 'STK-2026-00001';

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
