-- Curated GovCon acronym and synonym alias seed. Idempotent: ON CONFLICT (lower(alias_text)) DO NOTHING.
-- Each subselect returns no row if the canonical skill is not yet seeded, making this safely
-- re-runnable before or after cleared_skills.sql (rows with an unresolved subselect simply produce
-- zero rows, not an error). verified_at = now() marks these as editorially reviewed.

-- Intel discipline full names
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Signals Intelligence', 'acronym', 'curated', now()
FROM skills WHERE name = 'SIGINT'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Geospatial Intelligence', 'acronym', 'curated', now()
FROM skills WHERE name = 'GEOINT'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Human Intelligence', 'acronym', 'curated', now()
FROM skills WHERE name = 'HUMINT'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Measurement and Signature Intelligence', 'acronym', 'curated', now()
FROM skills WHERE name = 'MASINT'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Open Source Intelligence', 'acronym', 'curated', now()
FROM skills WHERE name = 'OSINT'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- COMSEC and information security acronyms
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Communications Security', 'acronym', 'curated', now()
FROM skills WHERE name = 'COMSEC'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- Compliance and governance acronyms
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Risk Management Framework', 'acronym', 'curated', now()
FROM skills WHERE name = 'RMF'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'ATO', 'acronym', 'curated', now()
FROM skills WHERE name = 'Authority to Operate'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- CDS resolves to Cross Domain Solutions (the product space term); also captures the singular form
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'CDS', 'acronym', 'curated', now()
FROM skills WHERE name = 'Cross Domain Solutions'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Cross Domain Solution', 'synonym', 'curated', now()
FROM skills WHERE name = 'Cross Domain Solutions'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- Electronic Warfare acronym
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'EW', 'acronym', 'curated', now()
FROM skills WHERE name = 'Electronic Warfare'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- Platform and tool alternate names
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'K8s', 'acronym', 'curated', now()
FROM skills WHERE name = 'Kubernetes'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Security Information and Event Management', 'acronym', 'curated', now()
FROM skills WHERE name = 'SIEM'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Security Orchestration Automation and Response', 'acronym', 'curated', now()
FROM skills WHERE name = 'SOAR'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- Compliance framework alternate names
INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'FISMA Compliance', 'synonym', 'curated', now()
FROM skills WHERE name = 'FISMA'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'FedRAMP Authorization', 'synonym', 'curated', now()
FROM skills WHERE name = 'FedRAMP'
ON CONFLICT (lower(alias_text)) DO NOTHING;

INSERT INTO skill_aliases (skill_id, alias_text, alias_type, source, verified_at)
SELECT id, 'Cybersecurity Maturity Model Certification', 'acronym', 'curated', now()
FROM skills WHERE name = 'CMMC'
ON CONFLICT (lower(alias_text)) DO NOTHING;

-- -----------------------------------------------------------------------
-- Curated skill relationships. Both skills must exist for the row to land.
-- Relation types: broader_than, substitutes_for, requires, related_to.
-- Strength is 0.0 to 1.0: the partial credit weight the engine applies.
-- -----------------------------------------------------------------------

-- Container orchestration hierarchy
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'broader_than', 0.85, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Kubernetes' AND b.name = 'Docker'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- OpenShift is a Kubernetes distribution; Kubernetes skill is a close substitute
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'substitutes_for', 0.80, 'curated', now()
FROM skills a, skills b WHERE a.name = 'OpenShift' AND b.name = 'Kubernetes'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- AWS GovCloud is a specialization of AWS; AWS satisfies most AWS GovCloud requirements
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'substitutes_for', 0.75, 'curated', now()
FROM skills a, skills b WHERE a.name = 'AWS' AND b.name = 'AWS GovCloud'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- Azure Government is a specialization of Azure
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'substitutes_for', 0.75, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Azure' AND b.name = 'Azure Government'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- Security Operations is a broader category that contains Incident Response and Threat Hunting
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'broader_than', 0.70, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Security Operations' AND b.name = 'Incident Response'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'broader_than', 0.70, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Security Operations' AND b.name = 'Threat Hunting'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- All Source Analysis requires both SIGINT and GEOINT and HUMINT as foundations
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'requires', 0.60, 'curated', now()
FROM skills a, skills b WHERE a.name = 'All Source Analysis' AND b.name = 'Intelligence Analysis'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- Space Domain Awareness relates to Satellite C2 and Space Situational Awareness
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'broader_than', 0.80, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Space Domain Awareness' AND b.name = 'Space Situational Awareness'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- Penetration Testing and Vulnerability Management are closely related in the cleared context
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'related_to', 0.65, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Penetration Testing' AND b.name = 'Vulnerability Management'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- Malware Analysis requires Reverse Engineering as a foundational skill
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'requires', 0.70, 'curated', now()
FROM skills a, skills b WHERE a.name = 'Malware Analysis' AND b.name = 'Reverse Engineering'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;

-- DevSecOps requires both Software Engineering and compliance awareness
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
SELECT a.id, b.id, 'requires', 0.65, 'curated', now()
FROM skills a, skills b WHERE a.name = 'DevSecOps' AND b.name = 'Software Engineering'
ON CONFLICT (from_skill_id, to_skill_id, relation) DO NOTHING;