db_schema.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


CREATE EXTENSION IF NOT EXISTS "pg_cron" WITH SCHEMA "pg_catalog";






CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "extensions";






COMMENT ON SCHEMA "public" IS 'standard public schema';



CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";






CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "pgmq";






CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";






CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";






CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA "extensions";






CREATE TYPE "public"."difficulty_level" AS ENUM (
    'easy',
    'medium',
    'hard'
);


ALTER TYPE "public"."difficulty_level" OWNER TO "postgres";


CREATE TYPE "public"."exam_batch_status" AS ENUM (
    'pending',
    'processing',
    'completed',
    'failed',
    'canceled'
);


ALTER TYPE "public"."exam_batch_status" OWNER TO "postgres";


CREATE TYPE "public"."exam_status" AS ENUM (
    'draft',
    'published'
);


ALTER TYPE "public"."exam_status" OWNER TO "postgres";


CREATE TYPE "public"."subscription_tier" AS ENUM (
    'free',
    'pro',
    'premium'
);


ALTER TYPE "public"."subscription_tier" OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."create_secure_student"("name_text" "text", "class_id_arg" "uuid", "secret_key" "text") RETURNS "uuid"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
DECLARE
  new_student_id uuid;
BEGIN
  -- Validate secret key
  IF secret_key IS NULL OR secret_key = '' THEN
    RAISE EXCEPTION 'Encryption key must be provided';
  END IF;
  
  -- Insert encrypted student with class_id and user_id (from auth context)
  INSERT INTO public.students (user_id, encrypted_name, class_id, grade_level)
  VALUES (
    auth.uid(),
    extensions.pgp_sym_encrypt(name_text, secret_key),
    class_id_arg,
    NULL
  )
  RETURNING id INTO new_student_id;

  RETURN new_student_id;
END;
$$;


ALTER FUNCTION "public"."create_secure_student"("name_text" "text", "class_id_arg" "uuid", "secret_key" "text") OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."deduct_user_credits"("p_user_id" "uuid", "p_amount" numeric) RETURNS boolean
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'public'
    AS $$
DECLARE
    current_balance numeric;
BEGIN
    -- Lock the row for update to prevent race conditions
    SELECT credits_balance INTO current_balance
    FROM public.subscriptions
    WHERE user_id = p_user_id
    FOR UPDATE;

    -- Check if user exists and has enough credits
    IF current_balance IS NULL THEN
        -- Handle case where subscription might not exist (though it should)
        RETURN false;
    END IF;

    IF current_balance >= p_amount THEN
        UPDATE public.subscriptions
        SET credits_balance = credits_balance - p_amount
        WHERE user_id = p_user_id;
        
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;
$$;


ALTER FUNCTION "public"."deduct_user_credits"("p_user_id" "uuid", "p_amount" numeric) OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."handle_updated_at"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
begin
  new.updated_at = now();
  return new;
end;
$$;


ALTER FUNCTION "public"."handle_updated_at"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."renew_free_tier_credits"() RETURNS "void"
    LANGUAGE "plpgsql" SECURITY DEFINER
    AS $$
begin
  -- Update users without active/trialing subscriptions to have 15 credits
  -- This runs daily and checks if it's the monthly anniversary of their signup
  -- For months with fewer days, we cap the day at 28 to ensure it runs
  update public.subscriptions
  set credits_balance = 15
  where 
    (status is null or (status != 'active' and status != 'trialing'))
    and (
      extract(day from created_at) = extract(day from current_date)
      or 
      (extract(day from current_date) = 28 and extract(day from created_at) > 28)
    );
end;
$$;


ALTER FUNCTION "public"."renew_free_tier_credits"() OWNER TO "postgres";


CREATE OR REPLACE FUNCTION "public"."rls_auto_enable"() RETURNS "event_trigger"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'pg_catalog'
    AS $$
DECLARE
  cmd record;
BEGIN
  FOR cmd IN
    SELECT *
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
      AND object_type IN ('table','partitioned table')
  LOOP
     IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
      BEGIN
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
      EXCEPTION
        WHEN OTHERS THEN
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
      END;
     ELSE
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
     END IF;
  END LOOP;
END;
$$;


ALTER FUNCTION "public"."rls_auto_enable"() OWNER TO "postgres";

SET default_tablespace = '';

SET default_table_access_method = "heap";


CREATE TABLE IF NOT EXISTS "public"."background_jobs" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "job_type" "text" NOT NULL,
    "payload" "jsonb" NOT NULL,
    "status" "text" DEFAULT 'pending'::"text",
    "result" "jsonb",
    "error_message" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    CONSTRAINT "background_jobs_job_type_check" CHECK (("job_type" = ANY (ARRAY['ocr_correction'::"text", 'ocr_correction_v2'::"text", 'exam_generation'::"text", 'weekly_report'::"text", 'generate_questions'::"text", 'generate_questions_v2'::"text"]))),
    CONSTRAINT "background_jobs_status_check" CHECK (("status" = ANY (ARRAY['pending'::"text", 'processing'::"text", 'completed'::"text", 'failed'::"text"])))
);


ALTER TABLE "public"."background_jobs" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."class_attendance" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "class_id" "uuid" NOT NULL,
    "student_id" "uuid" NOT NULL,
    "date" "date" DEFAULT CURRENT_DATE NOT NULL,
    "status" "text" NOT NULL,
    "user_id" "uuid" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"() NOT NULL,
    CONSTRAINT "class_attendance_status_check" CHECK (("status" = ANY (ARRAY['present'::"text", 'absent'::"text", 'late'::"text"])))
);


ALTER TABLE "public"."class_attendance" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."class_histories" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "timezone"('utc'::"text", "now"()) NOT NULL,
    "snapshot_data" "jsonb" NOT NULL,
    "description" "text"
);


ALTER TABLE "public"."class_histories" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."classes" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "name" "text" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "timezone"('utc'::"text", "now"()) NOT NULL,
    "lesson_days" integer[] DEFAULT '{}'::integer[],
    "disciplines" "text"[] DEFAULT '{}'::"text"[],
    "academic_year" integer DEFAULT EXTRACT(year FROM "now"()),
    "period_type" "text" DEFAULT 'bimestre'::"text",
    "period_starts" "jsonb" DEFAULT '[]'::"jsonb",
    "passing_grade" numeric DEFAULT 6.0,
    "min_frequency" numeric DEFAULT 75.0,
    "is_archived" boolean DEFAULT false,
    "exams_config" "jsonb" DEFAULT '{}'::"jsonb"
);


ALTER TABLE "public"."classes" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."exam_batches" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "exam_id" "uuid",
    "user_id" "uuid",
    "student_count" integer DEFAULT 0 NOT NULL,
    "status" "public"."exam_batch_status" DEFAULT 'pending'::"public"."exam_batch_status",
    "created_at" timestamp with time zone DEFAULT "timezone"('utc'::"text", "now"()) NOT NULL,
    "updated_at" timestamp with time zone DEFAULT "timezone"('utc'::"text", "now"()) NOT NULL
);


ALTER TABLE "public"."exam_batches" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."exam_classes" (
    "exam_id" "uuid" NOT NULL,
    "class_id" "uuid" NOT NULL
);


ALTER TABLE "public"."exam_classes" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."exam_grades" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "job_id" "uuid" NOT NULL,
    "student_id" "uuid",
    "exam_id" "uuid",
    "final_score" numeric(5,2) NOT NULL,
    "answers" "jsonb" NOT NULL,
    "verified_at" timestamp with time zone DEFAULT "now"(),
    "created_at" timestamp with time zone DEFAULT "now"(),
    "batch_id" "uuid",
    "student_pdf_url" "text",
    "ai_rationale" "jsonb",
    "ai_confidence" numeric(4,3),
    "tfjs_confidence" numeric(4,3),
    "is_ai_correct" boolean,
    "original_ai_score" numeric(5,2),
    CONSTRAINT "exam_grades_final_score_check" CHECK ((("final_score" >= (0)::numeric) AND ("final_score" <= (10)::numeric)))
);


ALTER TABLE "public"."exam_grades" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."exam_results" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "exam_id" "uuid",
    "student_id" "uuid",
    "score" numeric(5,2),
    "created_at" timestamp with time zone DEFAULT "timezone"('utc'::"text", "now"()) NOT NULL
);


ALTER TABLE "public"."exam_results" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."exams" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "title" "text" NOT NULL,
    "status" "public"."exam_status" DEFAULT 'draft'::"public"."exam_status",
    "questions_list" "jsonb" DEFAULT '[]'::"jsonb" NOT NULL,
    "answer_key" "jsonb" DEFAULT '{}'::"jsonb" NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "correction_count" integer DEFAULT 0,
    "description" "text",
    "discipline" "text",
    "grade_level" "text",
    "term" "text" DEFAULT '1_bimestre'::"text"
);


ALTER TABLE "public"."exams" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."ia_cost_log" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "job_id" "uuid",
    "feature" "text" NOT NULL,
    "model_used" "text" NOT NULL,
    "input_tokens" integer NOT NULL,
    "output_tokens" integer NOT NULL,
    "cost_credits" numeric NOT NULL,
    "created_at" timestamp with time zone DEFAULT "now"(),
    "provider_cost_brl" numeric(10,6),
    "stripe_session_id" "text"
);


ALTER TABLE "public"."ia_cost_log" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."questions" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "user_id" "uuid" NOT NULL,
    "topic" "text" NOT NULL,
    "difficulty" "public"."difficulty_level" NOT NULL,
    "content" "jsonb" NOT NULL,
    "correct_answer" "text" NOT NULL,
    "bncc_code" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "type" "text",
    "style" "text",
    "usage_count" integer DEFAULT 0,
    "structured_data" "jsonb",
    "bncc" "text",
    "discipline" "text",
    "subject" "text",
    "explanation" "text",
    "options" "jsonb",
    "source_tag" "text",
    CONSTRAINT "questions_style_check" CHECK (("style" = ANY (ARRAY['enem'::"text", 'high_school'::"text", 'entrance_exam'::"text", 'civil_service'::"text"]))),
    CONSTRAINT "questions_type_check" CHECK (("type" = ANY (ARRAY['multiple_choice'::"text", 'true_false'::"text", 'essay'::"text", 'sum'::"text", 'redaction'::"text", 'association'::"text", 'open_ended'::"text"])))
);


ALTER TABLE "public"."questions" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."students" (
    "id" "uuid" DEFAULT "gen_random_uuid"() NOT NULL,
    "encrypted_name" "bytea" NOT NULL,
    "grade_level" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "user_id" "uuid",
    "name" "text",
    "class_id" "uuid"
);


ALTER TABLE "public"."students" OWNER TO "postgres";


CREATE TABLE IF NOT EXISTS "public"."subscriptions" (
    "user_id" "uuid" NOT NULL,
    "stripe_customer_id" "text",
    "stripe_subscription_id" "text",
    "stripe_price_id" "text",
    "stripe_current_period_end" timestamp with time zone,
    "status" "text",
    "created_at" timestamp with time zone DEFAULT "now"(),
    "updated_at" timestamp with time zone DEFAULT "now"(),
    "credits_balance" numeric DEFAULT 15 NOT NULL,
    "tier" "public"."subscription_tier" DEFAULT 'free'::"public"."subscription_tier" NOT NULL
);


ALTER TABLE "public"."subscriptions" OWNER TO "postgres";


ALTER TABLE ONLY "public"."background_jobs"
    ADD CONSTRAINT "background_jobs_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."class_attendance"
    ADD CONSTRAINT "class_attendance_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."class_attendance"
    ADD CONSTRAINT "class_attendance_student_id_date_key" UNIQUE ("student_id", "date");



ALTER TABLE ONLY "public"."class_histories"
    ADD CONSTRAINT "class_histories_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."classes"
    ADD CONSTRAINT "classes_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."exam_batches"
    ADD CONSTRAINT "exam_batches_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."exam_classes"
    ADD CONSTRAINT "exam_classes_pkey" PRIMARY KEY ("exam_id", "class_id");



ALTER TABLE ONLY "public"."exam_grades"
    ADD CONSTRAINT "exam_grades_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."exam_results"
    ADD CONSTRAINT "exam_results_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."exams"
    ADD CONSTRAINT "exams_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."ia_cost_log"
    ADD CONSTRAINT "ia_cost_log_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."ia_cost_log"
    ADD CONSTRAINT "ia_cost_log_stripe_session_id_key" UNIQUE ("stripe_session_id");



ALTER TABLE ONLY "public"."questions"
    ADD CONSTRAINT "questions_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."students"
    ADD CONSTRAINT "students_pkey" PRIMARY KEY ("id");



ALTER TABLE ONLY "public"."students"
    ADD CONSTRAINT "students_user_id_name_key" UNIQUE ("user_id", "name");



ALTER TABLE ONLY "public"."subscriptions"
    ADD CONSTRAINT "subscriptions_pkey" PRIMARY KEY ("user_id");



ALTER TABLE ONLY "public"."subscriptions"
    ADD CONSTRAINT "subscriptions_stripe_customer_id_key" UNIQUE ("stripe_customer_id");



ALTER TABLE ONLY "public"."subscriptions"
    ADD CONSTRAINT "subscriptions_stripe_subscription_id_key" UNIQUE ("stripe_subscription_id");



CREATE INDEX "idx_class_attendance_class_id" ON "public"."class_attendance" USING "btree" ("class_id");



CREATE INDEX "idx_class_attendance_date" ON "public"."class_attendance" USING "btree" ("date");



CREATE INDEX "idx_class_attendance_student_id" ON "public"."class_attendance" USING "btree" ("student_id");



CREATE INDEX "idx_class_attendance_user_id" ON "public"."class_attendance" USING "btree" ("user_id");



CREATE INDEX "idx_classes_is_archived" ON "public"."classes" USING "btree" ("is_archived");



CREATE INDEX "idx_exams_teacher_id" ON "public"."exams" USING "btree" ("user_id");



CREATE INDEX "idx_exams_user_id" ON "public"."exams" USING "btree" ("user_id");



CREATE INDEX "idx_ia_cost_log_stripe_session_id" ON "public"."ia_cost_log" USING "btree" ("stripe_session_id");



CREATE INDEX "idx_jobs_created_at" ON "public"."background_jobs" USING "btree" ("created_at" DESC);



CREATE INDEX "idx_jobs_user_status" ON "public"."background_jobs" USING "btree" ("user_id", "status");



CREATE INDEX "idx_questions_discipline" ON "public"."questions" USING "btree" ("discipline");



CREATE INDEX "idx_questions_style" ON "public"."questions" USING "btree" ("style");



CREATE INDEX "idx_questions_subject" ON "public"."questions" USING "btree" ("subject");



CREATE INDEX "idx_questions_teacher_id" ON "public"."questions" USING "btree" ("user_id");



CREATE INDEX "idx_questions_type" ON "public"."questions" USING "btree" ("type");



CREATE INDEX "idx_questions_usage_count" ON "public"."questions" USING "btree" ("usage_count");



CREATE INDEX "idx_questions_user_id" ON "public"."questions" USING "btree" ("user_id");



CREATE OR REPLACE TRIGGER "handle_subscriptions_updated_at" BEFORE UPDATE ON "public"."subscriptions" FOR EACH ROW EXECUTE FUNCTION "public"."handle_updated_at"();



CREATE OR REPLACE TRIGGER "set_updated_at" BEFORE UPDATE ON "public"."background_jobs" FOR EACH ROW EXECUTE FUNCTION "public"."handle_updated_at"();



ALTER TABLE ONLY "public"."background_jobs"
    ADD CONSTRAINT "background_jobs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."class_attendance"
    ADD CONSTRAINT "class_attendance_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "public"."classes"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."class_attendance"
    ADD CONSTRAINT "class_attendance_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "public"."students"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."class_attendance"
    ADD CONSTRAINT "class_attendance_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."class_histories"
    ADD CONSTRAINT "class_histories_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."classes"
    ADD CONSTRAINT "classes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_batches"
    ADD CONSTRAINT "exam_batches_exam_id_fkey" FOREIGN KEY ("exam_id") REFERENCES "public"."exams"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_batches"
    ADD CONSTRAINT "exam_batches_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_classes"
    ADD CONSTRAINT "exam_classes_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "public"."classes"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_classes"
    ADD CONSTRAINT "exam_classes_exam_id_fkey" FOREIGN KEY ("exam_id") REFERENCES "public"."exams"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_grades"
    ADD CONSTRAINT "exam_grades_batch_id_fkey" FOREIGN KEY ("batch_id") REFERENCES "public"."exam_batches"("id") ON DELETE SET NULL;



ALTER TABLE ONLY "public"."exam_grades"
    ADD CONSTRAINT "exam_grades_job_id_fkey" FOREIGN KEY ("job_id") REFERENCES "public"."background_jobs"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_grades"
    ADD CONSTRAINT "exam_grades_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "public"."students"("id") ON DELETE SET NULL;



ALTER TABLE ONLY "public"."exam_results"
    ADD CONSTRAINT "exam_results_exam_id_fkey" FOREIGN KEY ("exam_id") REFERENCES "public"."exams"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exam_results"
    ADD CONSTRAINT "exam_results_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "public"."students"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."exams"
    ADD CONSTRAINT "exams_teacher_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."ia_cost_log"
    ADD CONSTRAINT "ia_cost_log_job_id_fkey" FOREIGN KEY ("job_id") REFERENCES "public"."background_jobs"("id");



ALTER TABLE ONLY "public"."ia_cost_log"
    ADD CONSTRAINT "ia_cost_log_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."questions"
    ADD CONSTRAINT "questions_teacher_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



ALTER TABLE ONLY "public"."students"
    ADD CONSTRAINT "students_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "public"."classes"("id") ON DELETE SET NULL;



ALTER TABLE ONLY "public"."students"
    ADD CONSTRAINT "students_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE CASCADE;



ALTER TABLE ONLY "public"."subscriptions"
    ADD CONSTRAINT "subscriptions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id");



CREATE POLICY "Service role can insert cost logs" ON "public"."ia_cost_log" FOR INSERT WITH CHECK (true);



CREATE POLICY "Service role has full access" ON "public"."subscriptions" USING (true) WITH CHECK (true);



CREATE POLICY "Users can create exam grades" ON "public"."exam_grades" FOR INSERT WITH CHECK (("job_id" IN ( SELECT "background_jobs"."id"
   FROM "public"."background_jobs"
  WHERE ("background_jobs"."user_id" = "auth"."uid"()))));



CREATE POLICY "Users can create own jobs" ON "public"."background_jobs" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can delete attendance for their own classes" ON "public"."class_attendance" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can delete own exam grades" ON "public"."exam_grades" FOR DELETE USING (("job_id" IN ( SELECT "background_jobs"."id"
   FROM "public"."background_jobs"
  WHERE ("background_jobs"."user_id" = "auth"."uid"()))));



CREATE POLICY "Users can delete results for their exams" ON "public"."exam_results" FOR DELETE USING ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_results"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can delete their own classes" ON "public"."classes" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can delete their own exam_classes" ON "public"."exam_classes" FOR DELETE USING ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_classes"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can delete their own exams" ON "public"."exams" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can delete their own questions" ON "public"."questions" FOR DELETE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert attendance for their own classes" ON "public"."class_attendance" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert results for their exams" ON "public"."exam_results" FOR INSERT WITH CHECK ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_results"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can insert their own classes" ON "public"."classes" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert their own exam_classes" ON "public"."exam_classes" FOR INSERT WITH CHECK ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_classes"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can insert their own exams" ON "public"."exams" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can insert their own questions" ON "public"."questions" FOR INSERT WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can manage their own class histories" ON "public"."class_histories" USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can manage their own exam batches" ON "public"."exam_batches" USING (("auth"."uid"() = "user_id")) WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can manage their own students" ON "public"."students" USING (("auth"."uid"() = "user_id")) WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can see own exam grades" ON "public"."exam_grades" FOR SELECT USING (("job_id" IN ( SELECT "background_jobs"."id"
   FROM "public"."background_jobs"
  WHERE ("background_jobs"."user_id" = "auth"."uid"()))));



CREATE POLICY "Users can see own jobs" ON "public"."background_jobs" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update attendance for their own classes" ON "public"."class_attendance" FOR UPDATE USING (("auth"."uid"() = "user_id")) WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update own exam grades" ON "public"."exam_grades" FOR UPDATE USING (("job_id" IN ( SELECT "background_jobs"."id"
   FROM "public"."background_jobs"
  WHERE ("background_jobs"."user_id" = "auth"."uid"())))) WITH CHECK (("job_id" IN ( SELECT "background_jobs"."id"
   FROM "public"."background_jobs"
  WHERE ("background_jobs"."user_id" = "auth"."uid"()))));



CREATE POLICY "Users can update own jobs" ON "public"."background_jobs" FOR UPDATE USING (("auth"."uid"() = "user_id")) WITH CHECK (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update results for their exams" ON "public"."exam_results" FOR UPDATE USING ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_results"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can update their own classes" ON "public"."classes" FOR UPDATE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update their own exams" ON "public"."exams" FOR UPDATE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can update their own questions" ON "public"."questions" FOR UPDATE USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view attendance for their own classes" ON "public"."class_attendance" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view own cost logs" ON "public"."ia_cost_log" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view own subscription" ON "public"."subscriptions" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view results for their exams" ON "public"."exam_results" FOR SELECT USING ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_results"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can view their own classes" ON "public"."classes" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view their own exam_classes" ON "public"."exam_classes" FOR SELECT USING ((EXISTS ( SELECT 1
   FROM "public"."exams"
  WHERE (("exams"."id" = "exam_classes"."exam_id") AND ("exams"."user_id" = "auth"."uid"())))));



CREATE POLICY "Users can view their own exams" ON "public"."exams" FOR SELECT USING (("auth"."uid"() = "user_id"));



CREATE POLICY "Users can view their own questions" ON "public"."questions" FOR SELECT USING (("auth"."uid"() = "user_id"));



ALTER TABLE "public"."background_jobs" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."class_attendance" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."class_histories" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."classes" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."exam_batches" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."exam_classes" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."exam_grades" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."exam_results" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."exams" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."ia_cost_log" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."questions" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."students" ENABLE ROW LEVEL SECURITY;


ALTER TABLE "public"."subscriptions" ENABLE ROW LEVEL SECURITY;




ALTER PUBLICATION "supabase_realtime" OWNER TO "postgres";






ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."class_attendance";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."class_histories";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."classes";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."exam_classes";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."exam_grades";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."exam_results";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."ia_cost_log";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."questions";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."students";



ALTER PUBLICATION "supabase_realtime" ADD TABLE ONLY "public"."subscriptions";









GRANT USAGE ON SCHEMA "public" TO "postgres";
GRANT USAGE ON SCHEMA "public" TO "anon";
GRANT USAGE ON SCHEMA "public" TO "authenticated";
GRANT USAGE ON SCHEMA "public" TO "service_role";




































































































































































































































































































































































































































































































































GRANT ALL ON FUNCTION "public"."create_secure_student"("name_text" "text", "class_id_arg" "uuid", "secret_key" "text") TO "anon";
GRANT ALL ON FUNCTION "public"."create_secure_student"("name_text" "text", "class_id_arg" "uuid", "secret_key" "text") TO "authenticated";
GRANT ALL ON FUNCTION "public"."create_secure_student"("name_text" "text", "class_id_arg" "uuid", "secret_key" "text") TO "service_role";



GRANT ALL ON FUNCTION "public"."deduct_user_credits"("p_user_id" "uuid", "p_amount" numeric) TO "anon";
GRANT ALL ON FUNCTION "public"."deduct_user_credits"("p_user_id" "uuid", "p_amount" numeric) TO "authenticated";
GRANT ALL ON FUNCTION "public"."deduct_user_credits"("p_user_id" "uuid", "p_amount" numeric) TO "service_role";



GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "anon";
GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."handle_updated_at"() TO "service_role";



GRANT ALL ON FUNCTION "public"."renew_free_tier_credits"() TO "anon";
GRANT ALL ON FUNCTION "public"."renew_free_tier_credits"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."renew_free_tier_credits"() TO "service_role";



GRANT ALL ON FUNCTION "public"."rls_auto_enable"() TO "anon";
GRANT ALL ON FUNCTION "public"."rls_auto_enable"() TO "authenticated";
GRANT ALL ON FUNCTION "public"."rls_auto_enable"() TO "service_role";




































GRANT ALL ON TABLE "public"."background_jobs" TO "anon";
GRANT ALL ON TABLE "public"."background_jobs" TO "authenticated";
GRANT ALL ON TABLE "public"."background_jobs" TO "service_role";



GRANT ALL ON TABLE "public"."class_attendance" TO "anon";
GRANT ALL ON TABLE "public"."class_attendance" TO "authenticated";
GRANT ALL ON TABLE "public"."class_attendance" TO "service_role";



GRANT ALL ON TABLE "public"."class_histories" TO "anon";
GRANT ALL ON TABLE "public"."class_histories" TO "authenticated";
GRANT ALL ON TABLE "public"."class_histories" TO "service_role";



GRANT ALL ON TABLE "public"."classes" TO "anon";
GRANT ALL ON TABLE "public"."classes" TO "authenticated";
GRANT ALL ON TABLE "public"."classes" TO "service_role";



GRANT ALL ON TABLE "public"."exam_batches" TO "anon";
GRANT ALL ON TABLE "public"."exam_batches" TO "authenticated";
GRANT ALL ON TABLE "public"."exam_batches" TO "service_role";



GRANT ALL ON TABLE "public"."exam_classes" TO "anon";
GRANT ALL ON TABLE "public"."exam_classes" TO "authenticated";
GRANT ALL ON TABLE "public"."exam_classes" TO "service_role";



GRANT ALL ON TABLE "public"."exam_grades" TO "anon";
GRANT ALL ON TABLE "public"."exam_grades" TO "authenticated";
GRANT ALL ON TABLE "public"."exam_grades" TO "service_role";



GRANT ALL ON TABLE "public"."exam_results" TO "anon";
GRANT ALL ON TABLE "public"."exam_results" TO "authenticated";
GRANT ALL ON TABLE "public"."exam_results" TO "service_role";



GRANT ALL ON TABLE "public"."exams" TO "anon";
GRANT ALL ON TABLE "public"."exams" TO "authenticated";
GRANT ALL ON TABLE "public"."exams" TO "service_role";



GRANT ALL ON TABLE "public"."ia_cost_log" TO "anon";
GRANT ALL ON TABLE "public"."ia_cost_log" TO "authenticated";
GRANT ALL ON TABLE "public"."ia_cost_log" TO "service_role";



GRANT ALL ON TABLE "public"."questions" TO "anon";
GRANT ALL ON TABLE "public"."questions" TO "authenticated";
GRANT ALL ON TABLE "public"."questions" TO "service_role";



GRANT ALL ON TABLE "public"."students" TO "anon";
GRANT ALL ON TABLE "public"."students" TO "authenticated";
GRANT ALL ON TABLE "public"."students" TO "service_role";



GRANT ALL ON TABLE "public"."subscriptions" TO "anon";
GRANT ALL ON TABLE "public"."subscriptions" TO "authenticated";
GRANT ALL ON TABLE "public"."subscriptions" TO "service_role";









ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS TO "service_role";






ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES TO "service_role";