-- Migration: Add job_number column to prod_bom_mo_qty for efficient job tracking
-- Date: 2026-03-29
-- Replaces label parsing with indexed column for O(1) MAX queries

-- Step 1: Add job_number column with index
ALTER TABLE llx_prod_bom_mo_qty 
ADD COLUMN job_number INT NULL AFTER qty_to_produce,
ADD INDEX idx_job_entity (job_number, entity);

-- Step 2: Backfill job_number from BOM labels for existing records
-- This query extracts job number from BOM label "(Job N)" pattern
UPDATE llx_prod_bom_mo_qty AS q
INNER JOIN llx_bom_bom AS b ON q.fk_bom = b.rowid
SET q.job_number = CAST(
    SUBSTRING_INDEX(SUBSTRING_INDEX(b.label, '(Job ', -1), ')', 1)
    AS UNSIGNED
)
WHERE b.label LIKE '%(Job %)%'
  AND q.job_number IS NULL;

-- Step 3: Verify migration (optional, for audit)
-- SELECT 
--   COUNT(*) as total_records,
--   COUNT(job_number) as with_job,
--   COUNT(*) - COUNT(job_number) as without_job
-- FROM llx_prod_bom_mo_qty;
