-- GA-ATLAS Phase 1: Student Schema Expansion (FBISE Format)
-- ============================================================
-- CONSTRAINT #1: ZERO REGRESSION — ALTER TABLE only, no drops, no renames.
-- All new fields are NULLABLE to prevent breaking existing enrollment flows.
-- ============================================================

-- 1. Add new columns to the Student table
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "student_id" VARCHAR(20);
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "mother_name" TEXT;
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "form_b_cnic" VARCHAR(20);
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "father_cnic" VARCHAR(20);
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "mother_cnic" VARCHAR(20);
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "guardian_occupation" TEXT;
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "emergency_contact" VARCHAR(20);
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "religion" TEXT DEFAULT 'Islam';
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "gender" TEXT;
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "dob" DATE;
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "doa" DATE;
ALTER TABLE "Student" ADD COLUMN IF NOT EXISTS "student_status" TEXT DEFAULT 'Studying';

-- 2. Add UNIQUE constraint on student_id (Excel-sourced identifier)
ALTER TABLE "Student" ADD CONSTRAINT "Student_student_id_key" UNIQUE ("student_id");

-- 3. Create index on student_id for high-speed lookups (Face Attendance module)
CREATE INDEX IF NOT EXISTS "Student_student_id_idx" ON "Student" ("student_id");
