setup.sql

-- =============================================================================
-- Classroom Management System: database setup
-- GENERATED by scripts/generate-setup-sql.mjs. Do not edit by hand.
-- =============================================================================
-- HOW TO RUN (once, on a new database):
--   Neon Console -> your project -> SQL Editor -> paste this whole file -> Run
--   or from a terminal:  psql "$DIRECT_URL" -f database/setup.sql
--   (Also works unchanged in the Supabase SQL Editor.)
--
-- WHAT IT DOES:
--   1. Creates every table, enum, index and foreign key the app needs.
--   2. Turns on Row Level Security on every table and removes all access for
--      public API roles (Neon Data API / Supabase: anon, anonymous, authenticated)
--      if they exist. Only the app's own database role can read or change data.
--
-- Everything runs in one transaction: if anything fails, nothing is changed.
-- Check the result any time with database/verify-security.sql.
-- =============================================================================

BEGIN;

-- Stop safely if this database was already set up
DO $$
BEGIN
  IF to_regclass('public.users') IS NOT NULL THEN
    RAISE EXCEPTION 'This database is already set up (table "users" exists). Nothing was changed. To re-apply the lockdown only, run database/security.sql.';
  END IF;
END $$;

-- CreateExtension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- CreateEnum
CREATE TYPE "Role" AS ENUM ('student', 'admin', 'super_admin');

-- CreateEnum
CREATE TYPE "AnnouncementType" AS ENUM ('general', 'exam', 'file_update', 'routine_update', 'urgent', 'event', 'course_update');

-- CreateEnum
CREATE TYPE "FileCategory" AS ENUM ('lecture_notes', 'assignment', 'past_paper', 'syllabus', 'other', 'resource', 'software');

-- CreateEnum
CREATE TYPE "ExamType" AS ENUM ('EXAM', 'ASSIGNMENT');

-- CreateEnum
CREATE TYPE "CourseType" AS ENUM ('THEORY', 'LAB', 'SESSIONAL');

-- CreateTable
CREATE TABLE "users" (
    "email" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "avatarUrl" TEXT,
    "studentId" TEXT,
    "phone" TEXT,
    "bio" TEXT,
    "nickname" TEXT,
    "bloodGroup" TEXT,
    "gender" TEXT,
    "dob" TEXT,
    "lastLogin" TIMESTAMP(3),
    "passwordHash" TEXT,
    "twoFactorSecret" TEXT,
    "twoFactorEnabled" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "id" TEXT NOT NULL,
    "role" "Role" NOT NULL DEFAULT 'student',

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "sessions" (
    "sessionToken" TEXT NOT NULL,
    "expires" TIMESTAMP(3) NOT NULL,
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "sessions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "accounts" (
    "type" TEXT NOT NULL,
    "provider" TEXT NOT NULL,
    "providerAccountId" TEXT NOT NULL,
    "refresh_token" TEXT,
    "access_token" TEXT,
    "expires_at" INTEGER,
    "token_type" TEXT,
    "scope" TEXT,
    "id_token" TEXT,
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "accounts_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "courses" (
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "creditHours" DOUBLE PRECISION NOT NULL,
    "teacherName" TEXT,
    "semester" INTEGER NOT NULL,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "courseType" "CourseType",

    CONSTRAINT "courses_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "announcements" (
    "title" TEXT NOT NULL,
    "body" TEXT NOT NULL,
    "source" TEXT,
    "isPublished" BOOLEAN NOT NULL DEFAULT false,
    "publishedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "id" TEXT NOT NULL,
    "type" "AnnouncementType" NOT NULL DEFAULT 'general',
    "authorId" TEXT NOT NULL,

    CONSTRAINT "announcements_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "announcement_courses" (
    "announcementId" TEXT NOT NULL,
    "courseId" TEXT NOT NULL,

    CONSTRAINT "announcement_courses_pkey" PRIMARY KEY ("announcementId","courseId")
);

-- CreateTable
CREATE TABLE "file_uploads" (
    "name" TEXT NOT NULL,
    "driveId" TEXT NOT NULL,
    "driveUrl" TEXT NOT NULL,
    "downloadUrl" TEXT,
    "mimeType" TEXT NOT NULL,
    "sizeBytes" BIGINT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "category" "FileCategory" NOT NULL DEFAULT 'other',
    "courseId" TEXT,
    "uploadedById" TEXT NOT NULL,
    "connectedDriveId" TEXT,
    "folderName" TEXT,
    "isHidden" BOOLEAN NOT NULL DEFAULT false,

    CONSTRAINT "file_uploads_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "exams" (
    "title" TEXT NOT NULL,
    "description" TEXT,
    "examDate" TIMESTAMP(3) NOT NULL,
    "duration" INTEGER,
    "room" TEXT,
    "syllabus" TEXT,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "archivedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "id" TEXT NOT NULL,
    "courseId" TEXT NOT NULL,
    "instructions" TEXT,
    "submissionLink" TEXT,
    "submissionMethod" TEXT,
    "type" "ExamType" NOT NULL DEFAULT 'EXAM',

    CONSTRAINT "exams_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "assignment_submissions" (
    "id" TEXT NOT NULL,
    "examId" TEXT NOT NULL,
    "studentId" TEXT NOT NULL,
    "isSubmitted" BOOLEAN NOT NULL DEFAULT true,
    "submittedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "assignment_submissions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "polls" (
    "question" TEXT NOT NULL,
    "isAnonymous" BOOLEAN NOT NULL DEFAULT true,
    "isClosed" BOOLEAN NOT NULL DEFAULT false,
    "closesAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "options" JSONB NOT NULL,
    "createdById" TEXT NOT NULL,

    CONSTRAINT "polls_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "poll_votes" (
    "optionIndex" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "pollId" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "poll_votes_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "study_groups" (
    "title" TEXT NOT NULL,
    "courseCode" TEXT,
    "description" TEXT,
    "maxMembers" INTEGER NOT NULL DEFAULT 5,
    "meetTime" TEXT,
    "location" TEXT,
    "isOpen" BOOLEAN NOT NULL DEFAULT true,
    "targetGroup" TEXT NOT NULL DEFAULT 'ALL',
    "archivedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,

    CONSTRAINT "study_groups_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "study_group_members" (
    "isLeader" BOOLEAN NOT NULL DEFAULT false,
    "joinedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "groupId" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "study_group_members_pkey" PRIMARY KEY ("groupId","userId")
);

-- CreateTable
CREATE TABLE "notifications" (
    "title" TEXT NOT NULL,
    "body" TEXT NOT NULL,
    "link" TEXT,
    "isRead" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "notifications_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "push_tokens" (
    "token" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,

    CONSTRAINT "push_tokens_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "audit_logs" (
    "action" TEXT NOT NULL,
    "targetType" TEXT NOT NULL,
    "targetId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "actorId" TEXT NOT NULL,
    "metadata" JSONB,

    CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "system_configs" (
    "key" TEXT NOT NULL,
    "value" TEXT NOT NULL,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "system_configs_pkey" PRIMARY KEY ("key")
);

-- CreateTable
CREATE TABLE "base_routines" (
    "courseCode" TEXT NOT NULL,
    "courseName" TEXT,
    "dayOfWeek" TEXT NOT NULL,
    "startTime" TEXT NOT NULL,
    "endTime" TEXT NOT NULL,
    "room" TEXT NOT NULL,
    "teacher" TEXT,
    "targetGroup" TEXT NOT NULL DEFAULT 'ALL',
    "weekParity" TEXT NOT NULL DEFAULT 'ALL',
    "isLab" BOOLEAN NOT NULL DEFAULT false,
    "semester" TEXT,
    "archivedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,

    CONSTRAINT "base_routines_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "routine_overrides" (
    "date" TIMESTAMP(3) NOT NULL,
    "type" TEXT NOT NULL,
    "courseCode" TEXT,
    "courseName" TEXT,
    "startTime" TEXT,
    "endTime" TEXT,
    "room" TEXT,
    "teacher" TEXT,
    "targetGroup" TEXT NOT NULL DEFAULT 'ALL',
    "reason" TEXT,
    "archivedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,
    "baseRoutineId" TEXT,
    "createdById" TEXT NOT NULL,
    "weekParity" TEXT NOT NULL DEFAULT 'ALL',

    CONSTRAINT "routine_overrides_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "routine_weeks" (
    "calendarWeekStart" TIMESTAMP(3) NOT NULL,
    "workingWeekNumber" INTEGER,
    "weekType" TEXT,
    "isSkipped" BOOLEAN NOT NULL DEFAULT false,
    "skippedReason" TEXT,
    "markedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    "id" TEXT NOT NULL,
    "markedById" TEXT,

    CONSTRAINT "routine_weeks_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "connected_drives" (
    "label" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "refreshToken" TEXT NOT NULL,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" TEXT NOT NULL,

    CONSTRAINT "connected_drives_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "shared_drive_folders" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "driveUrl" TEXT NOT NULL,
    "driveFolderId" TEXT NOT NULL,
    "courseCode" TEXT,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "addedById" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "shared_drive_folders_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "shared_drive_subfolders" (
    "id" TEXT NOT NULL,
    "parentId" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "driveFolderId" TEXT NOT NULL,
    "driveUrl" TEXT NOT NULL,
    "isHidden" BOOLEAN NOT NULL DEFAULT false,
    "lastSyncedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "shared_drive_subfolders_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE UNIQUE INDEX "users_studentId_key" ON "users"("studentId");

-- CreateIndex
CREATE UNIQUE INDEX "sessions_sessionToken_key" ON "sessions"("sessionToken");

-- CreateIndex
CREATE INDEX "sessions_userId_idx" ON "sessions"("userId");

-- CreateIndex
CREATE INDEX "accounts_userId_idx" ON "accounts"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "accounts_provider_providerAccountId_key" ON "accounts"("provider", "providerAccountId");

-- CreateIndex
CREATE UNIQUE INDEX "courses_code_key" ON "courses"("code");

-- CreateIndex
CREATE INDEX "announcements_authorId_idx" ON "announcements"("authorId");

-- CreateIndex
CREATE INDEX "announcements_isPublished_publishedAt_idx" ON "announcements"("isPublished", "publishedAt");

-- CreateIndex
CREATE INDEX "announcement_courses_courseId_idx" ON "announcement_courses"("courseId");

-- CreateIndex
CREATE UNIQUE INDEX "file_uploads_driveId_key" ON "file_uploads"("driveId");

-- CreateIndex
CREATE INDEX "file_uploads_courseId_idx" ON "file_uploads"("courseId");

-- CreateIndex
CREATE INDEX "file_uploads_uploadedById_idx" ON "file_uploads"("uploadedById");

-- CreateIndex
CREATE INDEX "file_uploads_connectedDriveId_idx" ON "file_uploads"("connectedDriveId");

-- CreateIndex
CREATE INDEX "file_uploads_createdAt_idx" ON "file_uploads"("createdAt");

-- CreateIndex
CREATE INDEX "exams_courseId_idx" ON "exams"("courseId");

-- CreateIndex
CREATE INDEX "exams_examDate_idx" ON "exams"("examDate");

-- CreateIndex
CREATE INDEX "exams_isActive_examDate_idx" ON "exams"("isActive", "examDate");

-- CreateIndex
CREATE INDEX "assignment_submissions_examId_idx" ON "assignment_submissions"("examId");

-- CreateIndex
CREATE INDEX "assignment_submissions_studentId_idx" ON "assignment_submissions"("studentId");

-- CreateIndex
CREATE UNIQUE INDEX "assignment_submissions_examId_studentId_key" ON "assignment_submissions"("examId", "studentId");

-- CreateIndex
CREATE INDEX "polls_createdAt_idx" ON "polls"("createdAt");

-- CreateIndex
CREATE INDEX "poll_votes_pollId_idx" ON "poll_votes"("pollId");

-- CreateIndex
CREATE INDEX "poll_votes_userId_idx" ON "poll_votes"("userId");

-- CreateIndex
CREATE UNIQUE INDEX "poll_votes_pollId_userId_key" ON "poll_votes"("pollId", "userId");

-- CreateIndex
CREATE INDEX "study_groups_createdAt_idx" ON "study_groups"("createdAt");

-- CreateIndex
CREATE INDEX "study_group_members_userId_idx" ON "study_group_members"("userId");

-- CreateIndex
CREATE INDEX "notifications_userId_isRead_idx" ON "notifications"("userId", "isRead");

-- CreateIndex
CREATE INDEX "notifications_userId_createdAt_idx" ON "notifications"("userId", "createdAt");

-- CreateIndex
CREATE UNIQUE INDEX "push_tokens_token_key" ON "push_tokens"("token");

-- CreateIndex
CREATE INDEX "push_tokens_userId_idx" ON "push_tokens"("userId");

-- CreateIndex
CREATE INDEX "audit_logs_actorId_idx" ON "audit_logs"("actorId");

-- CreateIndex
CREATE INDEX "audit_logs_createdAt_idx" ON "audit_logs"("createdAt");

-- CreateIndex
CREATE INDEX "base_routines_dayOfWeek_targetGroup_idx" ON "base_routines"("dayOfWeek", "targetGroup");

-- CreateIndex
CREATE INDEX "routine_overrides_date_targetGroup_idx" ON "routine_overrides"("date", "targetGroup");

-- CreateIndex
CREATE INDEX "routine_overrides_baseRoutineId_idx" ON "routine_overrides"("baseRoutineId");

-- CreateIndex
CREATE UNIQUE INDEX "routine_weeks_calendarWeekStart_key" ON "routine_weeks"("calendarWeekStart");

-- CreateIndex
CREATE INDEX "routine_weeks_calendarWeekStart_idx" ON "routine_weeks"("calendarWeekStart");

-- CreateIndex
CREATE INDEX "routine_weeks_weekType_idx" ON "routine_weeks"("weekType");

-- CreateIndex
CREATE UNIQUE INDEX "shared_drive_folders_driveUrl_key" ON "shared_drive_folders"("driveUrl");

-- CreateIndex
CREATE UNIQUE INDEX "shared_drive_folders_driveFolderId_key" ON "shared_drive_folders"("driveFolderId");

-- CreateIndex
CREATE INDEX "shared_drive_folders_addedById_idx" ON "shared_drive_folders"("addedById");

-- CreateIndex
CREATE UNIQUE INDEX "shared_drive_subfolders_driveFolderId_key" ON "shared_drive_subfolders"("driveFolderId");

-- CreateIndex
CREATE INDEX "shared_drive_subfolders_parentId_idx" ON "shared_drive_subfolders"("parentId");

-- AddForeignKey
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "announcements" ADD CONSTRAINT "announcements_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "announcement_courses" ADD CONSTRAINT "announcement_courses_announcementId_fkey" FOREIGN KEY ("announcementId") REFERENCES "announcements"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "announcement_courses" ADD CONSTRAINT "announcement_courses_courseId_fkey" FOREIGN KEY ("courseId") REFERENCES "courses"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "file_uploads" ADD CONSTRAINT "file_uploads_connectedDriveId_fkey" FOREIGN KEY ("connectedDriveId") REFERENCES "connected_drives"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "file_uploads" ADD CONSTRAINT "file_uploads_courseId_fkey" FOREIGN KEY ("courseId") REFERENCES "courses"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "file_uploads" ADD CONSTRAINT "file_uploads_uploadedById_fkey" FOREIGN KEY ("uploadedById") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "exams" ADD CONSTRAINT "exams_courseId_fkey" FOREIGN KEY ("courseId") REFERENCES "courses"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "assignment_submissions" ADD CONSTRAINT "assignment_submissions_examId_fkey" FOREIGN KEY ("examId") REFERENCES "exams"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "assignment_submissions" ADD CONSTRAINT "assignment_submissions_studentId_fkey" FOREIGN KEY ("studentId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "poll_votes" ADD CONSTRAINT "poll_votes_pollId_fkey" FOREIGN KEY ("pollId") REFERENCES "polls"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "poll_votes" ADD CONSTRAINT "poll_votes_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "study_group_members" ADD CONSTRAINT "study_group_members_groupId_fkey" FOREIGN KEY ("groupId") REFERENCES "study_groups"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "study_group_members" ADD CONSTRAINT "study_group_members_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "push_tokens" ADD CONSTRAINT "push_tokens_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "routine_overrides" ADD CONSTRAINT "routine_overrides_baseRoutineId_fkey" FOREIGN KEY ("baseRoutineId") REFERENCES "base_routines"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "shared_drive_folders" ADD CONSTRAINT "shared_drive_folders_addedById_fkey" FOREIGN KEY ("addedById") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "shared_drive_subfolders" ADD CONSTRAINT "shared_drive_subfolders_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "shared_drive_folders"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- =============================================================================
-- Classroom Management System: lock down the database (safe to run any time)
-- =============================================================================
-- HOW TO RUN: Neon Console -> your project -> SQL Editor -> paste -> Run
--             (or: psql "$DIRECT_URL" -f database/security.sql)
-- This block is already included at the end of setup.sql. Run this file on its
-- own for a database created before it existed, or after schema changes.
-- Works on Neon and on Supabase.
--
-- WHO CAN CHANGE DATA AFTER THIS
--   * The app server. It connects with the database's owner role through
--     DATABASE_URL (Neon: neondb_owner). The owner is not blocked by Row Level
--     Security, so the app keeps working.
--   * People you trust with that connection string or with your Neon account.
--
-- WHO CANNOT
--   * Anyone using a public data API role: Neon Data API (anonymous,
--     authenticated) or Supabase (anon, authenticated). No policies and no
--     privileges = no access. Roles that don't exist are simply skipped.
-- =============================================================================

DO $$
DECLARE
  t record;
  api_role text;
BEGIN
  -- 1. Row Level Security on every table in the public schema.
  --    RLS with no policies denies every row to every non-owner role.
  FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', t.tablename);
  END LOOP;

  -- 2. Remove every privilege public API roles have, now and for objects created later.
  FOREACH api_role IN ARRAY ARRAY['anon', 'anonymous', 'authenticated'] LOOP
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = api_role) THEN
      EXECUTE format('REVOKE ALL ON ALL TABLES IN SCHEMA public FROM %I', api_role);
      EXECUTE format('REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM %I', api_role);
      EXECUTE format('REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM %I', api_role);
      EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA public REVOKE ALL ON TABLES FROM %I', current_user, api_role);
      EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA public REVOKE ALL ON SEQUENCES FROM %I', current_user, api_role);
      EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM %I', current_user, api_role);
    END IF;
  END LOOP;
END $$;

COMMIT;