db/schema/013_drop_web_exploitation.sql

-- 013_drop_web_exploitation.sql
-- ---------------------------------------------------------------------------
-- Web exploitation challenges have been removed from the platform. The
-- `web_exploitation_challenges` table, its CHECK constraints, and the
-- `web` value from the `onevone_matches.challenge_type` CHECK are all gone.
-- The frontend no longer renders a web-exploitation editor and the backend
-- has no code path that reads from or writes to this table.
--
-- This migration is idempotent: it uses IF EXISTS / DROP IF EXISTS
-- everywhere so it's safe to re-apply.
-- ---------------------------------------------------------------------------

-- 0) Drop any views / functions that reference the table (none today,
--    but defensive: drop the consistency view's web_exploitation block
--    by recreating the view without it).
DROP VIEW IF EXISTS v_challenge_type_consistency;
CREATE VIEW v_challenge_type_consistency AS
SELECT 'encryption_challenges'           AS table_name, COUNT(*) AS bad_rows
  FROM encryption_challenges           WHERE module <> 'crypto'
UNION ALL
SELECT 'code_fixing_challenges',              COUNT(*)
  FROM code_fixing_challenges          WHERE module <> 'code-fixing'
UNION ALL
SELECT 'log_analysis_challenges',             COUNT(*)
  FROM log_analysis_challenges         WHERE module <> 'log-analysis'
UNION ALL
SELECT 'vulnerability_hunter_challenges',     COUNT(*)
  FROM vulnerability_hunter_challenges WHERE module <> 'vulnerability-hunter';

-- 1) Drop the table itself. CASCADE removes any indexes / policies /
--    triggers that may still reference it.
DROP TABLE IF EXISTS public.web_exploitation_challenges CASCADE;

-- 2) Tighten the onev1 CHECK constraint: remove 'web' from the
--    allowed challenge_type values. 'vulnerability-hunter' was added
--    in migration 012; the final canonical set is:
--      ('crypto', 'code-fixing', 'log-analysis', 'vulnerability-hunter')
ALTER TABLE onevone_matches
  DROP CONSTRAINT IF EXISTS onevone_matches_challenge_type_check;
ALTER TABLE onevone_matches
  ADD CONSTRAINT onevone_matches_challenge_type_check
  CHECK (challenge_type IN ('crypto', 'code-fixing', 'log-analysis', 'vulnerability-hunter'));