-- CreateEnum (IF NOT EXISTS for idempotency)
DO $$ BEGIN
  CREATE TYPE "ReportCardStatus" AS ENUM ('DRAFT', 'PUBLISHED');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

-- CreateTable
CREATE TABLE "report_card_categories" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "icon" TEXT NOT NULL DEFAULT '📋',
    "name" TEXT NOT NULL,
    "description" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "report_cards" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "student_id" TEXT NOT NULL,
    "classroom_id" TEXT,
    "date" DATE NOT NULL,
    "status" "ReportCardStatus" NOT NULL DEFAULT 'DRAFT',
    "published_at" TIMESTAMP(3),
    "created_by_user_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "report_card_entries" (
    "id" TEXT NOT NULL,
    "report_card_id" TEXT NOT NULL,
    "category_id" TEXT NOT NULL,
    "value" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateIndex
CREATE INDEX "report_card_categories_tenant_id_sort_order_idx" ON "report_card_categories"("tenant_id", "sort_order");

-- CreateIndex
CREATE UNIQUE INDEX "report_cards_tenant_id_student_id_date_key" ON "report_cards"("tenant_id", "student_id", "date");

-- CreateIndex
CREATE INDEX "report_cards_tenant_id_date_idx" ON "report_cards"("tenant_id", "date");

-- CreateIndex
CREATE INDEX "report_cards_tenant_id_student_id_idx" ON "report_cards"("tenant_id", "student_id");

-- CreateIndex
CREATE INDEX "report_cards_tenant_id_classroom_id_date_idx" ON "report_cards"("tenant_id", "classroom_id", "date");

-- CreateIndex
CREATE UNIQUE INDEX "report_card_entries_report_card_id_category_id_key" ON "report_card_entries"("report_card_id", "category_id");

-- CreateIndex
CREATE INDEX "report_card_entries_report_card_id_idx" ON "report_card_entries"("report_card_id");

-- AddForeignKey
ALTER TABLE "report_card_categories" ADD CONSTRAINT "report_card_categories_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_cards" ADD CONSTRAINT "report_cards_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_cards" ADD CONSTRAINT "report_cards_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "students"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_cards" ADD CONSTRAINT "report_cards_classroom_id_fkey" FOREIGN KEY ("classroom_id") REFERENCES "classrooms"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_cards" ADD CONSTRAINT "report_cards_created_by_user_id_fkey" FOREIGN KEY ("created_by_user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_card_entries" ADD CONSTRAINT "report_card_entries_report_card_id_fkey" FOREIGN KEY ("report_card_id") REFERENCES "report_cards"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "report_card_entries" ADD CONSTRAINT "report_card_entries_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "report_card_categories"("id") ON DELETE CASCADE ON UPDATE CASCADE;