db/schema/014_recreate_web_exploitation.sql

-- 014_recreate_web_exploitation.sql
-- Re-creates the `web_exploitation_challenges` table that was dropped in
-- migration 013. This version is Red-Team-only (team_role = 'red') and
-- stores a complete web exploitation scenario that includes:
--   - A realistic HTTP request/response pair (raw strings)
--   - The vulnerability type / class / description
--   - The expected payload (flag or exploit string)
--   - A hint of what to look for in the intercepted traffic
--
-- The AI generator must produce diverse scenarios across XSS, SQLi, CSRF,
-- SSRF, IDOR, LFI, XXE, Command Injection, Auth Bypass, and File Upload.

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS public.web_exploitation_challenges (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_role                text NOT NULL DEFAULT 'red'
                           CHECK (team_role IN ('red')),
  module                   text NOT NULL DEFAULT 'web-exploitation',
  topic                    text NOT NULL,
  title                    text NOT NULL,
  story                    text NOT NULL,
  task_outline             text NOT NULL,
  -- Describes the vulnerable endpoint / scenario
  vulnerability_type       text NOT NULL,
  vulnerability_class      text NOT NULL,
  vulnerability_description text NOT NULL,
  -- The raw HTTP exchange (simulated)
  http_request             text NOT NULL,
  http_response            text NOT NULL,
  -- The expected answer (flag or exploit payload)
  flag_preview             text NOT NULL,
  flag_hash                text NOT NULL,
  -- Hints (3 levels, jsonb)
  hints                    jsonb DEFAULT '[]'::jsonb,
  difficulty               text NOT NULL
                           CHECK (difficulty IN ('مبتدئ','سهل','متوسط','صعب','خبير')),
  xp_reward                integer DEFAULT 200,
  created_at               timestamptz DEFAULT now()
);

-- Fast pool / dashboard queries
CREATE INDEX IF NOT EXISTS idx_web_exploit_topic   ON public.web_exploitation_challenges(topic);
CREATE INDEX IF NOT EXISTS idx_web_exploit_vuln    ON public.web_exploitation_challenges(vulnerability_type);
CREATE INDEX IF NOT EXISTS idx_web_exploit_diff    ON public.web_exploitation_challenges(difficulty);

-- RLS — open read for anon, insert/delete for service role
ALTER TABLE public.web_exploitation_challenges ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Open read web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Open read web_exp" ON public.web_exploitation_challenges
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Service role insert web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Service role insert web_exp" ON public.web_exploitation_challenges
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "Service role delete web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Service role delete web_exp" ON public.web_exploitation_challenges
  FOR DELETE USING (true);

-- Update the v_challenge_type_consistency view to include web_exploitation
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'
UNION ALL
SELECT 'web_exploitation_challenges',         COUNT(*)
  FROM web_exploitation_challenges     WHERE module <> 'web-exploitation';

COMMIT;