School management

CREATE TYPE attendance_status AS ENUM ('present', 'absent', 'late', 'excused');

CREATE TABLE students (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    date_of_birth date NOT NULL,
    enrolled_on date DEFAULT current_date NOT NULL
);

CREATE TABLE guardians (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    phone text,
    email text
);

CREATE TABLE student_guardians (
    student_id uuid NOT NULL,
    guardian_id uuid NOT NULL,
    relationship text NOT NULL,
    PRIMARY KEY (student_id, guardian_id)
);
CREATE INDEX student_guardians_guardian_id_idx ON student_guardians (guardian_id);

CREATE TABLE teachers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    email text NOT NULL UNIQUE
);

CREATE TABLE subjects (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE classes (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    subject_id uuid NOT NULL,
    teacher_id uuid NOT NULL,
    year int NOT NULL,
    term int NOT NULL,
    room text
);
CREATE INDEX classes_subject_id_idx ON classes (subject_id);
CREATE INDEX classes_teacher_id_idx ON classes (teacher_id);

CREATE TABLE class_enrollments (
    class_id uuid NOT NULL,
    student_id uuid NOT NULL,
    PRIMARY KEY (class_id, student_id)
);
CREATE INDEX class_enrollments_student_id_idx ON class_enrollments (student_id);

CREATE TABLE attendance (
    class_id uuid NOT NULL,
    student_id uuid NOT NULL,
    day date NOT NULL,
    status attendance_status NOT NULL,
    PRIMARY KEY (class_id, student_id, day)
);
CREATE INDEX attendance_student_id_idx ON attendance (student_id);

CREATE TABLE grades (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    class_id uuid NOT NULL,
    student_id uuid NOT NULL,
    assessment text NOT NULL,
    score numeric(5,2) NOT NULL,
    max_score numeric(5,2) DEFAULT 100 NOT NULL
);
CREATE INDEX grades_class_id_idx ON grades (class_id);
CREATE INDEX grades_student_id_idx ON grades (student_id);

ALTER TABLE student_guardians ADD FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE;

ALTER TABLE student_guardians ADD FOREIGN KEY (guardian_id) REFERENCES guardians (id) ON DELETE CASCADE;

ALTER TABLE classes ADD FOREIGN KEY (subject_id) REFERENCES subjects (id);

ALTER TABLE classes ADD FOREIGN KEY (teacher_id) REFERENCES teachers (id);

ALTER TABLE class_enrollments ADD FOREIGN KEY (class_id) REFERENCES classes (id) ON DELETE CASCADE;

ALTER TABLE class_enrollments ADD FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE;

ALTER TABLE attendance ADD FOREIGN KEY (class_id) REFERENCES classes (id) ON DELETE CASCADE;

ALTER TABLE attendance ADD FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE;

ALTER TABLE grades ADD FOREIGN KEY (class_id) REFERENCES classes (id) ON DELETE CASCADE;

ALTER TABLE grades ADD FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE;