-- Demo seed shaping for the Galapagos sales demo. Run as the migrator role against the demo org.
--
-- IMPORTANT: the entire candidate pool in the demo org is SYNTHETIC generated data, not real people. The
-- clearance values are seeded self reported indicators on generated profiles. The real check is the
-- customer FSO through DISS at onboarding. The UI labels the whole pool "Demo data".
--
-- This file documents and reproduces one deliberate adjustment. The original synthetic seed left the
-- three hardest staffing gaps unfillable by anyone available, which made Win Impact read LOW for every
-- candidate and hid the feature. So nine rare skill holders (three each of the candidates who hold
-- Cross Domain Solutions, Satellite C2, and SIGINT Tooling, the single mandatory skill of each gap) are
-- set available and TS/SCI active with enough experience to legitimately qualify.
--
-- Gap to skill mapping:
--   Cyber Architect (TS/SCI, 8 yrs, qty 5)        needs Cross Domain Solutions
--   Data Scientist (Top Secret, 6 yrs, qty 6)     needs Satellite C2
--   Intelligence Analyst (TS/SCI, 5 yrs, qty 12)  needs SIGINT Tooling
--
-- Effect: each gap gets three qualified fillers, so the gaps stay open (the Command Center still names
-- NEED 10 Intelligence Analysts, 3 Data Scientists, 2 Cyber Architects) while about eight candidates now
-- show HIGH Win Impact on the Candidates page, citing those exact open counts. This moves the Command
-- Center to roughly readiness 86 Watch rather than 59.75 At Risk. The connected story is intentional:
-- the Command Center names a gap and the Candidates page shows the exact people who close it.
--
-- Idempotent: re running it just reasserts the same nine rows. When Galapagos loads their own bench these
-- seeded fillers fall away on their own.

\set org '11111111-1111-1111-1111-111111111111'

WITH ranked AS (
    SELECT cs.candidate_id,
           row_number() OVER (PARTITION BY s.name ORDER BY cs.candidate_id) AS rn
    FROM candidate_skills cs
    JOIN skills s ON s.id = cs.skill_id
    JOIN candidates c ON c.id = cs.candidate_id
    WHERE c.organization_id = :'org'
      AND s.name IN ('Cross Domain Solutions', 'Satellite C2', 'SIGINT Tooling')
)
UPDATE candidates SET
    available            = true,
    clearance_level      = 'ts_sci',
    clearance_status     = 'active',
    clearance_granted_at = '2021-06-01',
    clearance_expires_at = '2030-06-01',
    years_experience     = GREATEST(years_experience, 10),
    updated_at           = now()
WHERE id IN (SELECT candidate_id FROM ranked WHERE rn <= 3);