Showcase Your Talent,
Inspire The Future.

-- 1. Create Categories and Seat Limits Table
CREATE TABLE IF NOT EXISTS competition_categories (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  group_name TEXT,
  eligible_standards INTEGER[] NOT NULL,
  gender_restriction TEXT, -- 'female' or NULL
  seat_limit INTEGER NOT NULL DEFAULT 50,
  confirmed_count INTEGER NOT NULL DEFAULT 0,
  fee_inr NUMERIC(10, 2) NOT NULL DEFAULT 150.00,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- 2. Create Registrations Table with Privacy RLS
CREATE TABLE IF NOT EXISTS student_registrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registration_id TEXT UNIQUE NOT NULL,
  student_name TEXT NOT NULL,
  school_name TEXT NOT NULL,
  standard INTEGER NOT NULL CHECK (standard BETWEEN 5 AND 8),
  date_of_birth DATE NOT NULL,
  gender TEXT NOT NULL CHECK (gender IN ('male', 'female')),
  whatsapp_number TEXT NOT NULL,
  category_id TEXT REFERENCES competition_categories(id),
  drawing_group TEXT CHECK (drawing_group IN ('Group A', 'Group B', NULL)),
  guardian_name TEXT NOT NULL,
  guardian_phone TEXT NOT NULL,
  upi_reference TEXT NOT NULL,
  payer_name TEXT,
  payment_status TEXT NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending', 'verified', 'rejected')),
  whatsapp_sent BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  verified_at TIMESTAMPTZ
);

-- 3. Atomic Registration Function with Concurrency Seat Lock
CREATE OR REPLACE FUNCTION register_student_secure(
  p_reg_id TEXT,
  p_name TEXT,
  p_school TEXT,
  p_std INT,
  p_dob DATE,
  p_gender TEXT,
  p_wa TEXT,
  p_cat_id TEXT,
  p_draw_grp TEXT,
  p_g_name TEXT,
  p_g_phone TEXT,
  p_utr TEXT,
  p_payer TEXT
) RETURNS JSON AS $$
DECLARE
  v_category RECORD;
  v_confirmed_seats INT;
BEGIN
  -- Row-level lock to prevent race conditions
  SELECT * INTO v_category FROM competition_categories 
  WHERE id = p_cat_id FOR UPDATE;

  -- Eligibility Check
  IF NOT (p_std = ANY(v_category.eligible_standards)) THEN
    RAISE EXCEPTION 'Student standard % is not eligible for %', p_std, v_category.name;
  END IF;

  IF v_category.gender_restriction = 'female' AND p_gender != 'female' THEN
    RAISE EXCEPTION 'Category % is open only to female students', v_category.name;
  END IF;

  -- Insert registration as pending
  INSERT INTO student_registrations (
    registration_id, student_name, school_name, standard, date_of_birth,
    gender, whatsapp_number, category_id, drawing_group, guardian_name,
    guardian_phone, upi_reference, payer_name, payment_status
  ) VALUES (
    p_reg_id, p_name, p_school, p_std, p_dob,
    p_gender, p_wa, p_cat_id, p_draw_grp, p_g_name,
    p_g_phone, p_utr, p_payer, 'pending'
  );

  RETURN json_build_object('success', true, 'registration_id', p_reg_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 4. Enable Row Level Security (RLS)
ALTER TABLE student_registrations ENABLE ROW LEVEL SECURITY;
ALTER TABLE competition_categories ENABLE ROW LEVEL SECURITY;

-- Public can view category limits
CREATE POLICY "Public can view category limits" 
  ON competition_categories FOR SELECT USING (true);

-- Public cannot view other students' private registrations
CREATE POLICY "Public cannot view registrations" 
  ON student_registrations FOR SELECT USING (false);

-- Organizers with admin role can access everything
CREATE POLICY "Admin full access" 
  ON student_registrations TO authenticated USING (true) WITH CHECK (true);