-- Reproducible base demo seed (subphase 2.11). Bootstraps a coherent Galapagos demo on a CLEAN database
-- so the platform never depends on unversioned out of band data. Idempotent: fixed UUIDs plus ON
-- CONFLICT DO NOTHING, so re running adds nothing. This is a MODEST representative pool (the production
-- exact pool is reproduced from the encrypted backups, not this file). Run with `make seed`. All
-- candidates are SYNTHETIC Demo data and every clearance is a self reported indicator, never verified.
-- demo_gapfillers.sql runs AFTER this to shape the three hardest staffing gaps.

BEGIN;

-- Organization (the demo org id the dev frontend uses).
INSERT INTO organizations (id, name, slug)
VALUES ('11111111-1111-1111-1111-111111111111', 'Galapagos Federal Systems', 'galapagos')
ON CONFLICT (id) DO NOTHING;

-- Skills ontology. Names align with the extraction gazetteer and the matching engine so scores tie out.
INSERT INTO skills (name, category) VALUES
  ('Python','language'), ('Java','language'), ('JavaScript','language'), ('Go','language'),
  ('SQL','data'), ('Linux','platform'), ('AWS','cloud'), ('Azure','cloud'), ('Kubernetes','cloud'),
  ('Docker','cloud'), ('Terraform','cloud'), ('Ansible','cloud'), ('Splunk','cyber'), ('SIEM','cyber'),
  ('Incident Response','cyber'), ('Penetration Testing','cyber'), ('Vulnerability Management','cyber'),
  ('Network Security','cyber'), ('Cloud Security','cyber'), ('DevSecOps','cyber'), ('RMF','compliance'),
  ('ATO','compliance'), ('NIST','compliance'), ('Threat Intelligence','cyber'), ('Active Directory','platform'),
  ('Machine Learning','data'), ('Data Analysis','data'), ('Cryptography','cyber'), ('Cybersecurity','cyber'),
  ('Systems Engineering','engineering'), ('Software Engineering','engineering'), ('Intelligence Analysis','intel'),
  -- The three rare cleared skills that map one to one to the three hardest staffing gaps below. They also
  -- live in cleared_skills.sql; repeating them here keeps this base seed self contained on a clean database.
  ('SIGINT','intel'), ('Satellite C2','space'), ('Cross Domain Solutions','cyber')
ON CONFLICT (name) DO NOTHING;

-- Candidates (synthetic). Fixed UUIDs a0..0001 through a0..000a. A mix of clearances, availability, and
-- skill depth so the Command Center shows real coverage and real gaps.
INSERT INTO candidates (id, organization_id, full_name, clearance_level, clearance_poly, clearance_status, years_experience, location, available, source) VALUES
  ('a0000000-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111','Dana Reyes','ts_sci','full_scope','active',12,'Honolulu, HI',true,'seed'),
  ('a0000000-0000-0000-0000-000000000002','11111111-1111-1111-1111-111111111111','Marcus Hill','ts_sci','ci','active',9,'Honolulu, HI',true,'seed'),
  ('a0000000-0000-0000-0000-000000000003','11111111-1111-1111-1111-111111111111','Priya Nair','top_secret','none','active',7,'Albuquerque, NM',true,'seed'),
  ('a0000000-0000-0000-0000-000000000004','11111111-1111-1111-1111-111111111111','Sam Tucker','secret','none','active',5,'Albuquerque, NM',true,'seed'),
  ('a0000000-0000-0000-0000-000000000005','11111111-1111-1111-1111-111111111111','Lena Park','ts_sci','full_scope','active',11,'Honolulu, HI',false,'seed'),
  ('a0000000-0000-0000-0000-000000000006','11111111-1111-1111-1111-111111111111','Omar Diaz','secret','none','active',8,'Honolulu, HI',true,'seed'),
  ('a0000000-0000-0000-0000-000000000007','11111111-1111-1111-1111-111111111111','Grace Cho','top_secret','ci','active',6,'Albuquerque, NM',true,'seed'),
  ('a0000000-0000-0000-0000-000000000008','11111111-1111-1111-1111-111111111111','Ethan Boyd','secret','none','active',4,'Honolulu, HI',true,'seed'),
  ('a0000000-0000-0000-0000-000000000009','11111111-1111-1111-1111-111111111111','Nadia Khan','ts_sci','none','active',10,'Honolulu, HI',true,'seed'),
  ('a0000000-0000-0000-0000-00000000000a','11111111-1111-1111-1111-111111111111','Victor Long','secret','none','active',3,'Albuquerque, NM',true,'seed')
ON CONFLICT (id) DO NOTHING;

-- Candidate skills, certs, and customer experience as STRUCTURED rows (linked by skill name).
-- Skill assignments are shaped so nine of the ten candidates hold the mandatory skills of exactly one of the
-- three hardest gaps, three fillers per gap: Intelligence Analyst needs Intelligence Analysis plus SIGINT
-- (fillers 01, 02, 07), Data Scientist needs Machine Learning plus Satellite C2 (fillers 03, 06, 08), Cyber
-- Architect needs Cloud Security plus Cross Domain Solutions (fillers 04, 05, 09). Candidate 0a is a lower tier
-- generalist. demo_gapfillers.sql then sets the nine holders TS/SCI active and available so each gap has three
-- legitimate fillers while the quantities keep the gap open.
INSERT INTO candidate_skills (organization_id, candidate_id, skill_id)
SELECT '11111111-1111-1111-1111-111111111111'::uuid, v.cand::uuid, s.id
FROM (VALUES
  ('a0000000-0000-0000-0000-000000000001','Intelligence Analysis'),('a0000000-0000-0000-0000-000000000001','Threat Intelligence'),('a0000000-0000-0000-0000-000000000001','Python'),('a0000000-0000-0000-0000-000000000001','SIGINT'),
  ('a0000000-0000-0000-0000-000000000002','Intelligence Analysis'),('a0000000-0000-0000-0000-000000000002','Data Analysis'),('a0000000-0000-0000-0000-000000000002','SIGINT'),
  ('a0000000-0000-0000-0000-000000000007','Intelligence Analysis'),('a0000000-0000-0000-0000-000000000007','Python'),('a0000000-0000-0000-0000-000000000007','SIGINT'),
  ('a0000000-0000-0000-0000-000000000003','Machine Learning'),('a0000000-0000-0000-0000-000000000003','Python'),('a0000000-0000-0000-0000-000000000003','Data Analysis'),('a0000000-0000-0000-0000-000000000003','Satellite C2'),
  ('a0000000-0000-0000-0000-000000000006','Systems Engineering'),('a0000000-0000-0000-0000-000000000006','Linux'),('a0000000-0000-0000-0000-000000000006','Machine Learning'),('a0000000-0000-0000-0000-000000000006','Satellite C2'),
  ('a0000000-0000-0000-0000-000000000008','Software Engineering'),('a0000000-0000-0000-0000-000000000008','Machine Learning'),('a0000000-0000-0000-0000-000000000008','Satellite C2'),
  ('a0000000-0000-0000-0000-000000000004','Cybersecurity'),('a0000000-0000-0000-0000-000000000004','Network Security'),('a0000000-0000-0000-0000-000000000004','Cloud Security'),('a0000000-0000-0000-0000-000000000004','Cross Domain Solutions'),
  ('a0000000-0000-0000-0000-000000000005','Cloud Security'),('a0000000-0000-0000-0000-000000000005','AWS'),('a0000000-0000-0000-0000-000000000005','Kubernetes'),('a0000000-0000-0000-0000-000000000005','Cross Domain Solutions'),
  ('a0000000-0000-0000-0000-000000000009','Cybersecurity'),('a0000000-0000-0000-0000-000000000009','RMF'),('a0000000-0000-0000-0000-000000000009','Cloud Security'),('a0000000-0000-0000-0000-000000000009','Cross Domain Solutions'),
  ('a0000000-0000-0000-0000-00000000000a','Data Analysis'),('a0000000-0000-0000-0000-00000000000a','SQL')
) AS v(cand, skill)
JOIN skills s ON s.name = v.skill
ON CONFLICT (candidate_id, skill_id) DO NOTHING;

INSERT INTO candidate_certifications (organization_id, candidate_id, name)
SELECT '11111111-1111-1111-1111-111111111111'::uuid, v.cand::uuid, v.cert
FROM (VALUES
  ('a0000000-0000-0000-0000-000000000004','CISSP'),('a0000000-0000-0000-0000-000000000004','CompTIA Security+'),
  ('a0000000-0000-0000-0000-000000000005','AWS Certified Solutions Architect'),
  ('a0000000-0000-0000-0000-000000000009','CISSP')
) AS v(cand, cert)
ON CONFLICT (candidate_id, name) DO NOTHING;

INSERT INTO candidate_customer_experience (organization_id, candidate_id, agency)
SELECT '11111111-1111-1111-1111-111111111111'::uuid, v.cand::uuid, v.agency
FROM (VALUES
  ('a0000000-0000-0000-0000-000000000001','Air Force'),('a0000000-0000-0000-0000-000000000002','Space Force'),
  ('a0000000-0000-0000-0000-000000000005','Air Force'),('a0000000-0000-0000-0000-000000000009','DISA')
) AS v(cand, agency)
ON CONFLICT (candidate_id, agency) DO NOTHING;

-- Opportunities (representative values: synthetic, never a real federal headline).
INSERT INTO opportunities (id, organization_id, name, solicitation_number, customer_agency, due_date, status, value, value_source) VALUES
  ('b0000000-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111','INDOPACOM Intelligence Support','SOL-AF-001','Air Force', now()::date + 30,'open', 24000000,'representative'),
  ('b0000000-0000-0000-0000-000000000002','11111111-1111-1111-1111-111111111111','Space Force Data Science Cell','SOL-SF-002','Space Force', now()::date + 45,'open', 12500000,'representative'),
  ('b0000000-0000-0000-0000-000000000003','11111111-1111-1111-1111-111111111111','Cyber Architecture Modernization','SOL-AF-003','Air Force', now()::date + 60,'open', 18750000,'representative'),
  ('b0000000-0000-0000-0000-000000000004','11111111-1111-1111-1111-111111111111','Enterprise Systems Engineering','SOL-DISA-004','DISA', now()::date + 25,'open', NULL,'representative')
ON CONFLICT (id) DO NOTHING;

-- Requirements: labor categories with quantities that exceed available qualified supply, so the Command
-- Center surfaces the documented gaps (Intelligence Analysts, Data Scientists, Cyber Architects).
INSERT INTO requirements (id, organization_id, opportunity_id, title, customer_agency, labor_category, quantity, required_level, required_poly, required_years, work_location, key_personnel) VALUES
  ('c0000000-0000-0000-0000-000000000001','11111111-1111-1111-1111-111111111111','b0000000-0000-0000-0000-000000000001','Intelligence Analyst','Air Force','Intelligence Analyst',13,'ts_sci','none',5,'Honolulu, HI',true),
  ('c0000000-0000-0000-0000-000000000002','11111111-1111-1111-1111-111111111111','b0000000-0000-0000-0000-000000000002','Data Scientist','Space Force','Data Scientist',5,'top_secret','none',4,'Albuquerque, NM',false),
  ('c0000000-0000-0000-0000-000000000003','11111111-1111-1111-1111-111111111111','b0000000-0000-0000-0000-000000000003','Cyber Architect','Air Force','Cyber Architect',4,'ts_sci','none',8,'Honolulu, HI',true),
  ('c0000000-0000-0000-0000-000000000004','11111111-1111-1111-1111-111111111111','b0000000-0000-0000-0000-000000000004','Systems Engineer','DISA','Systems Engineer',3,'secret','none',5,'Honolulu, HI',false)
ON CONFLICT (id) DO NOTHING;

INSERT INTO required_skills (organization_id, requirement_id, skill_id, mandatory)
SELECT '11111111-1111-1111-1111-111111111111'::uuid, v.req::uuid, s.id, true
-- The three gap requirements require the rare skill of their gap as mandatory, so the gap the Command Center
-- names is exactly the gap the seeded fillers close: Intelligence Analyst needs Intelligence Analysis plus
-- SIGINT, Data Scientist needs Machine Learning plus Satellite C2, Cyber Architect needs Cloud Security plus
-- Cross Domain Solutions. The Systems Engineer requirement stays a common role for contrast.
FROM (VALUES
  ('c0000000-0000-0000-0000-000000000001','Intelligence Analysis'),('c0000000-0000-0000-0000-000000000001','SIGINT'),
  ('c0000000-0000-0000-0000-000000000002','Machine Learning'),('c0000000-0000-0000-0000-000000000002','Satellite C2'),
  ('c0000000-0000-0000-0000-000000000003','Cloud Security'),('c0000000-0000-0000-0000-000000000003','Cross Domain Solutions'),
  ('c0000000-0000-0000-0000-000000000004','Systems Engineering'),('c0000000-0000-0000-0000-000000000004','Linux')
) AS v(req, skill)
JOIN skills s ON s.name = v.skill
ON CONFLICT (requirement_id, skill_id) DO UPDATE SET mandatory = EXCLUDED.mandatory;

COMMIT;