-- ============================================================
-- FashCognitive Demo - TNA Timeline Rebase
-- Rebalances tna_projects.target_date and tna_tasks.due_date
-- into a believable risk pipeline so the AI Predictions
-- dashboard shows a healthy spread of severities and types
-- instead of an all-critical wall.
--
-- Bucket strategy:
--   Projects with no open tasks are finished → long-horizon.
--   Open-work projects are bucketed by their own sequential
--   index (MOD(rn, 20)) so every risk bucket gets real work:
--     < 2    overdue heavy  target 30-80d in the past
--     < 5    overdue light  target 3-12d  in the past
--     < 9    near-term      target 2-10d  ahead
--     < 13   mid-term       target 12-30d ahead
--     < 16   far-term       target 32-60d ahead
--     else   horizon        target 65-150d ahead
--
-- Steps:
--   1. Build a bucket table (temp).
--   2. Rebase project target dates.
--   3. Finish a share of pending tasks (varies open ratio).
--   4. Rebase open-task due dates + clear stale delay/completion.
--   5. Set delay_days on open tasks that are genuinely late.
--
-- Apply:  mysql -u root fcqc_database < seed_tna_demo_dates.sql
-- ============================================================

-- 1. Bucket table: number open-work projects independently
DROP TEMPORARY TABLE IF EXISTS tmp_tna_bucket;
CREATE TEMPORARY TABLE tmp_tna_bucket AS
SELECT ow.id, MOD(ROW_NUMBER() OVER (ORDER BY ow.id) - 1, 20) AS b20
FROM (
    SELECT p.id FROM tna_projects p
    WHERE p.status = 'active'
      AND EXISTS (
          SELECT 1 FROM tna_tasks t
          WHERE t.project_id = p.id AND t.company_id = p.company_id
            AND t.status IN ('pending','in_progress','delayed')
      )
) ow
UNION ALL
SELECT p.id, 99
FROM tna_projects p
WHERE p.status = 'active'
  AND NOT EXISTS (
      SELECT 1 FROM tna_tasks t
      WHERE t.project_id = p.id AND t.company_id = p.company_id
        AND t.status IN ('pending','in_progress','delayed')
  );

-- 2. Rebase project target dates across a balanced pipeline
UPDATE tna_projects p
JOIN tmp_tna_bucket b ON b.id = p.id
SET p.target_date = CASE
    WHEN b.b20 = 99 THEN DATE_ADD(CURDATE(), INTERVAL (90 + MOD(p.id, 60)) DAY)
    WHEN b.b20 < 2 THEN DATE_SUB(CURDATE(), INTERVAL (30 + MOD(p.id, 6) * 10) DAY)
    WHEN b.b20 < 5 THEN DATE_SUB(CURDATE(), INTERVAL (3 + MOD(p.id, 4) * 3) DAY)
    WHEN b.b20 < 9 THEN DATE_ADD(CURDATE(), INTERVAL (2 + MOD(p.id, 9)) DAY)
    WHEN b.b20 < 13 THEN DATE_ADD(CURDATE(), INTERVAL (12 + MOD(p.id, 19)) DAY)
    WHEN b.b20 < 16 THEN DATE_ADD(CURDATE(), INTERVAL (32 + MOD(p.id, 29)) DAY)
    ELSE DATE_ADD(CURDATE(), INTERVAL (65 + MOD(p.id, 85)) DAY)
END;

-- 3. Finish a share of pending tasks per bucket to vary the open ratio
UPDATE tna_tasks t
JOIN tna_projects p ON p.id = t.project_id AND p.company_id = t.company_id
JOIN tmp_tna_bucket b ON b.id = p.id
SET t.status = 'completed', t.completed_date = CURDATE(), t.completion_percentage = 100
WHERE t.status = 'pending'
  AND (
       (b.b20 < 2 AND MOD(t.id, 10) = 0)
    OR (b.b20 < 5 AND MOD(t.id, 10) = 0)
    OR (b.b20 < 9 AND MOD(t.id, 4) = 0)
    OR (b.b20 < 13 AND MOD(t.id, 3) = 0)
    OR (b.b20 < 16 AND MOD(t.id, 2) = 0)
    OR (b.b20 = 99 OR b.b20 >= 16 AND MOD(t.id, 2) = 0)
  );

-- 4. Rebase open-task due dates (and clear stale delay/completion)
UPDATE tna_tasks t
JOIN tna_projects p ON p.id = t.project_id AND p.company_id = t.company_id
JOIN tmp_tna_bucket b ON b.id = p.id
SET t.due_date = CASE
    -- overdue heavy: ~85% late by 10-40d, rest due within 5d
    WHEN b.b20 < 2 THEN IF(MOD(t.id, 100) < 85,
        DATE_SUB(CURDATE(), INTERVAL (10 + MOD(t.id, 31)) DAY),
        DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 4) + 1) DAY))
    -- overdue light: ~50% late by 1-8d, rest due within 7d
    WHEN b.b20 < 5 THEN IF(MOD(t.id, 100) < 50,
        DATE_SUB(CURDATE(), INTERVAL (1 + MOD(t.id, 8)) DAY),
        DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 6) + 1) DAY))
    -- near-term: ~25% late by 1-5d, rest due within 10d
    WHEN b.b20 < 9 THEN IF(MOD(t.id, 100) < 25,
        DATE_SUB(CURDATE(), INTERVAL (1 + MOD(t.id, 5)) DAY),
        DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 10) + 1) DAY))
    -- mid-term: ~10% late by 1-3d, rest due within 20d
    WHEN b.b20 < 13 THEN IF(MOD(t.id, 100) < 10,
        DATE_SUB(CURDATE(), INTERVAL (1 + MOD(t.id, 3)) DAY),
        DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 18) + 2) DAY))
    -- far-term: ~3% late by 1-2d, rest due within 25d
    WHEN b.b20 < 16 THEN IF(MOD(t.id, 100) < 3,
        DATE_SUB(CURDATE(), INTERVAL (1 + MOD(t.id, 2)) DAY),
        DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 22) + 4) DAY))
    -- horizon: on track, due within 60d
    ELSE DATE_ADD(CURDATE(), INTERVAL (MOD(t.id, 26) + 34) DAY)
END,
t.delay_days = 0,
t.completed_date = NULL
WHERE t.status IN ('pending', 'in_progress', 'delayed');

-- 5. Reflect actual delay on open tasks that are past due
UPDATE tna_tasks
SET delay_days = DATEDIFF(CURDATE(), due_date)
WHERE status IN ('pending', 'in_progress', 'delayed') AND due_date < CURDATE();

DROP TEMPORARY TABLE IF EXISTS tmp_tna_bucket;
