-- ============================================================================
-- Phase M4-p6-h1 — JOBFORM_TASK.qty_allocated column
-- ============================================================================
--
-- M4-p6-h1 introduces a task-level allocation counter to fix the assignment-
-- overwrite bug in M4-p1's replace-all design. Under the new model:
--
--   - Each Assign Selected call ADDS workers (no DELETE). Re-assigning the
--     same worker accumulates their personal qty (INSERT ... ON DUPLICATE KEY
--     UPDATE qty = qty + VALUES(qty)).
--   - The TASK separately tracks total allocated units in qty_allocated,
--     bumped by the batch qty (= the max worker qty in this Assign call
--     under shared-work semantics) on each call.
--   - The Android client reads task.qty_allocated for the "Alloc N" display
--     instead of deriving from worker rows. This handles the edge case
--     where batch 2 uses entirely new workers (MAX/SUM of worker rows
--     would give the wrong answer there).
--
-- For per-unit (Lamination / Autoclave / Final Inspection) tasks the
-- qty_allocated value is set to the count of distinct serial_ids assigned
-- (one row per (worker × serial), each row qty=1).
--
-- This migration is purely additive: existing rows get qty_allocated=0,
-- which is then backfilled by a single UPDATE that computes the M4-p1
-- equivalent (MAX worker qty for pre-lam, distinct serial count for
-- per-unit) so the screen doesn't regress for already-assigned tasks.
--
-- Idempotent — uses information_schema to skip the ALTER if the column
-- already exists.
-- ============================================================================

-- JOBFORM_TASK.qty_allocated
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_TASK'
       AND COLUMN_NAME = 'qty_allocated'
);

SET @ddl = IF(@col_exists = 0,
    'ALTER TABLE JOBFORM_TASK ADD COLUMN qty_allocated INT NOT NULL DEFAULT 0 AFTER qty_required',
    'SELECT "JOBFORM_TASK.qty_allocated already exists — skipped"'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill qty_allocated for any tasks that already have worker rows.
-- The M4-p1 client formula was:
--   pre-lam : MAX(qty)                across worker rows
--   per-unit: COUNT(DISTINCT serial_id) across worker rows
-- The branch is determined by whether ANY worker row has serial_id != 0.
-- We compute both and pick via a CASE; tasks with no workers stay at 0.
UPDATE JOBFORM_TASK t
   SET t.qty_allocated = COALESCE((
       SELECT CASE
                  WHEN SUM(CASE WHEN w.serial_id > 0 THEN 1 ELSE 0 END) > 0
                       THEN COUNT(DISTINCT w.serial_id)
                  ELSE MAX(w.qty)
              END
         FROM JOBFORM_TASK_WORKER w
        WHERE w.task_id = t.id
   ), 0)
 WHERE t.qty_allocated = 0;

-- ============================================================================
-- Verification:
--   DESCRIBE JOBFORM_TASK;
--     expect qty_allocated INT NOT NULL DEFAULT 0 after qty_required
--   SELECT id, qty_required, qty_allocated, qty_completed
--     FROM JOBFORM_TASK
--    WHERE qty_allocated > 0 LIMIT 20;
--     expect the seeded counter to match the previous M4-p1 Alloc display.
-- ============================================================================