-- Backfill the inspection approval chain for sessions already in 'submitted' status.
-- This was missing before seedApprovalChain() was added to sessions.php PUT,
-- so existing submitted sessions had no approval rows and the approvals queue
-- stayed empty. Inspector level = approved (signed off by submitting inspector),
-- downstream levels = pending.
--
-- Safe to re-run: INSERT ... SELECT ... ON DUPLICATE KEY UPDATE keeps it idempotent.

INSERT INTO inspection_approvals (session_id, approval_level, approver_id, approver_name, decision, decided_at, created_at, updated_at)
SELECT s.id, 'inspector', COALESCE(s.inspector_id, s.created_by), s.inspector_name, 'approved', s.submitted_at, NOW(), NOW()
FROM inspection_sessions s
WHERE s.status = 'submitted'
  AND NOT EXISTS (
    SELECT 1 FROM inspection_approvals a
    WHERE a.session_id = s.id AND a.approval_level = 'inspector'
  )
ON DUPLICATE KEY UPDATE decision = 'approved', decided_at = s.submitted_at;

INSERT INTO inspection_approvals (session_id, approval_level, approver_id, decision, decided_at, created_at, updated_at)
SELECT s.id, lvl.level, NULL, 'pending', NULL, NOW(), NOW()
FROM inspection_sessions s
JOIN (
  SELECT 'factory_qa' AS level
  UNION ALL SELECT 'buyer_qa'
  UNION ALL SELECT 'regional_manager'
) lvl
WHERE s.status = 'submitted'
  AND NOT EXISTS (
    SELECT 1 FROM inspection_approvals a
    WHERE a.session_id = s.id AND a.approval_level = lvl.level
  )
ON DUPLICATE KEY UPDATE decision = 'pending', approver_id = NULL, decided_at = NULL;
