db/schema/006_vulnerability_hunter_challenges.sql

-- Migration 010: Vulnerability Hunter Challenges (Blue Team)
-- Creates the table for a new blue-team challenge type:
-- the player reads a vulnerable code snippet and must IDENTIFY the
-- vulnerability (name + class), not fix the code.
--
-- Difficulty uses 5 levels (Beginner / Easy / Medium / Hard / Expert) —
-- mapped to Arabic values already in use in the platform:
--   Beginner -> مبتدئ
--   Easy     -> سهل
--   Medium   -> متوسط
--   Hard     -> صعب
--   Expert   -> خبير
--
-- `vulnerability_type` is the canonical short key the player must match
-- (e.g. "sql-injection", "xss", "command-injection", "path-traversal"...).
-- `vulnerability_class` is the OWASP/MITRE family used in hints/UI
-- (e.g. "Injection", "Broken Access Control").

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS public.vulnerability_hunter_challenges (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_role                text NOT NULL DEFAULT 'blue'
                           CHECK (team_role IN ('blue')),
  language                 text NOT NULL
                           CHECK (language IN ('C++','JAVA','PYTHON','JAVASCRIPT','PHP','RUST','GO','CSHARP')),
  module                   text NOT NULL,
  title                    text NOT NULL,
  story                    text NOT NULL,
  task_outline             text NOT NULL,
  vulnerable_code          text NOT NULL,
  vulnerability_type       text NOT NULL,            -- canonical key the student types
  vulnerability_class      text NOT NULL,            -- OWASP family for hints
  vulnerability_description text NOT NULL,
  hints                    jsonb DEFAULT '[]'::jsonb,
  difficulty               text NOT NULL
                           CHECK (difficulty IN ('مبتدئ','سهل','متوسط','صعب','خبير')),
  xp_reward                integer DEFAULT 150,
  created_at               timestamptz DEFAULT now()
);

-- Fast pool / dashboard queries
CREATE INDEX IF NOT EXISTS idx_vh_team_lang   ON public.vulnerability_hunter_challenges(team_role, language);
CREATE INDEX IF NOT EXISTS idx_vh_difficulty  ON public.vulnerability_hunter_challenges(difficulty);
CREATE INDEX IF NOT EXISTS idx_vh_vuln_type   ON public.vulnerability_hunter_challenges(vulnerability_type);

-- RLS — open read for anon (same posture as the other pool tables)
ALTER TABLE public.vulnerability_hunter_challenges ENABLE ROW LEVEL SECURITY;

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

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

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

COMMIT;