-- ==========================================================
-- Scrap Module — Migration: Add operation tracking columns
-- Purpose: Link scrap records to MO routing operations (fk_ws + op_rank)
-- Compatible with MySQL 8+ / MariaDB 10.6+ / Dolibarr 22+
--
-- Adds:
--   fk_ws   INT      — FK to llx_workstation_workstation.rowid (nullable)
--   op_rank SMALLINT — snapshot of routing position at time of scrap (nullable)
--
-- NULL in both columns = scrap not linked to a specific operation (backwards compatible)
-- No FK constraint on fk_ws — intentional, avoids issues if workstation is deleted later.
-- ==========================================================

-- ✅ 1️⃣ Add fk_ws column
ALTER TABLE llx_scrap
  ADD COLUMN fk_ws INT DEFAULT NULL
    COMMENT 'FK to llx_workstation_workstation.rowid — operation where scrap occurred';

-- ✅ 2️⃣ Add op_rank column
ALTER TABLE llx_scrap
  ADD COLUMN op_rank SMALLINT DEFAULT NULL
    COMMENT 'Snapshot of routing position (op order) at time of scrap';

-- ✅ 3️⃣ Add index on fk_ws for fast GROUP BY queries in reports
CREATE INDEX idx_scrap_ws ON llx_scrap (fk_ws);
