-- CreateTable: academic_years
CREATE TABLE "academic_years" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "start_date" DATE NOT NULL,
    "end_date" DATE NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable: academic_terms
CREATE TABLE "academic_terms" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "academic_year_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "start_date" DATE NOT NULL,
    "end_date" DATE NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable: student_enrollments
CREATE TABLE "student_enrollments" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "student_id" TEXT NOT NULL,
    "academic_term_id" TEXT NOT NULL,
    "classroom_id" TEXT NOT NULL,
    "enrolled_at" DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "notes" TEXT,

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

-- CreateIndex: academic_years
CREATE UNIQUE INDEX "academic_years_tenant_id_name_key" ON "academic_years"("tenant_id", "name");
CREATE INDEX "academic_years_tenant_id_idx" ON "academic_years"("tenant_id");
CREATE INDEX "academic_years_tenant_id_is_active_idx" ON "academic_years"("tenant_id", "is_active");

-- CreateIndex: academic_terms
CREATE UNIQUE INDEX "academic_terms_tenant_id_academic_year_id_name_key" ON "academic_terms"("tenant_id", "academic_year_id", "name");
CREATE INDEX "academic_terms_tenant_id_idx" ON "academic_terms"("tenant_id");
CREATE INDEX "academic_terms_tenant_id_academic_year_id_idx" ON "academic_terms"("tenant_id", "academic_year_id");
CREATE INDEX "academic_terms_tenant_id_is_active_idx" ON "academic_terms"("tenant_id", "is_active");

-- CreateIndex: student_enrollments
CREATE UNIQUE INDEX "student_enrollments_tenant_id_student_id_academic_term_id_key" ON "student_enrollments"("tenant_id", "student_id", "academic_term_id");
CREATE INDEX "student_enrollments_tenant_id_academic_term_id_idx" ON "student_enrollments"("tenant_id", "academic_term_id");
CREATE INDEX "student_enrollments_tenant_id_student_id_idx" ON "student_enrollments"("tenant_id", "student_id");
CREATE INDEX "student_enrollments_tenant_id_classroom_id_academic_term_id_idx" ON "student_enrollments"("tenant_id", "classroom_id", "academic_term_id");

-- AddForeignKey: academic_years → tenants
ALTER TABLE "academic_years" ADD CONSTRAINT "academic_years_tenant_id_fkey"
    FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: academic_terms → tenants
ALTER TABLE "academic_terms" ADD CONSTRAINT "academic_terms_tenant_id_fkey"
    FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: academic_terms → academic_years
ALTER TABLE "academic_terms" ADD CONSTRAINT "academic_terms_academic_year_id_fkey"
    FOREIGN KEY ("academic_year_id") REFERENCES "academic_years"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: student_enrollments → tenants
ALTER TABLE "student_enrollments" ADD CONSTRAINT "student_enrollments_tenant_id_fkey"
    FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: student_enrollments → students
ALTER TABLE "student_enrollments" ADD CONSTRAINT "student_enrollments_student_id_fkey"
    FOREIGN KEY ("student_id") REFERENCES "students"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: student_enrollments → academic_terms
ALTER TABLE "student_enrollments" ADD CONSTRAINT "student_enrollments_academic_term_id_fkey"
    FOREIGN KEY ("academic_term_id") REFERENCES "academic_terms"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey: student_enrollments → classrooms
ALTER TABLE "student_enrollments" ADD CONSTRAINT "student_enrollments_classroom_id_fkey"
    FOREIGN KEY ("classroom_id") REFERENCES "classrooms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;