db/schema/002_encryption_challenges.sql

-- 002_encryption_challenges.sql
-- The original crypto challenge table (Red + Blue encryption challenges).
-- Python (app/services/challenge_loader.py) is the source of truth for
-- flag_hash; the AI only writes the recipe.

BEGIN;

CREATE TABLE IF NOT EXISTS public.encryption_challenges (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_role       text NOT NULL CHECK (team_role IN ('blue', 'red')),
  module          text NOT NULL,             -- e.g. encryption-basics, hash-cracking, rsa-aes
  title           text NOT NULL,
  story           text NOT NULL,
  task_outline    text NOT NULL,
  files           jsonb NOT NULL DEFAULT '{}'::jsonb,
  file_metadata   jsonb NOT NULL DEFAULT '{}'::jsonb,
  command_outputs jsonb NOT NULL DEFAULT '{}'::jsonb,
  hints           jsonb NOT NULL DEFAULT '[]'::jsonb,
  tools_whitelist text[] NOT NULL DEFAULT '{}'::text[],
  flag_hash       text NOT NULL,
  flag_preview    text,
  difficulty      text NOT NULL CHECK (difficulty IN ('مبتدئ','متوسط','قوي')),
  xp_reward       integer NOT NULL DEFAULT 150,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_encryption_team_module ON public.encryption_challenges (team_role, module);
CREATE INDEX IF NOT EXISTS idx_encryption_difficulty  ON public.encryption_challenges (difficulty);

ALTER TABLE public.encryption_challenges ENABLE ROW LEVEL SECURITY;

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

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

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

COMMIT;