school.sql

-- Basic lookup tables
CREATE TABLE genders ( id smallint PRIMARY KEY, name text UNIQUE );
INSERT INTO genders VALUES (1,'Male'),(2,'Female'),(3,'Other');

CREATE TABLE roles (
  id smallint PRIMARY KEY,
  name text UNIQUE
);
INSERT INTO roles VALUES (1,'Student'),(2,'Teacher'),(3,'Admin'),(4,'Parent');

-- Persons (common attributes for students, staff, guardians)
CREATE TABLE persons (
  person_id BIGSERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  birth_date DATE,
  gender_id SMALLINT REFERENCES genders(id),
  email TEXT,
  phone TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX ON persons (last_name, first_name);

-- Students
CREATE TABLE students (
  student_id BIGINT PRIMARY KEY REFERENCES persons(person_id),
  student_number TEXT UNIQUE NOT NULL, -- e.g., school ID
  admission_date DATE,
  status TEXT CHECK (status IN ('active','graduated','inactive','expelled')) DEFAULT 'active',
  grade_level TEXT, -- e.g., "Grade 5" or numeric
  house TEXT,
  photo_url TEXT
);

-- Guardians / Parents (reuse persons)
CREATE TABLE guardians (
  guardian_id BIGINT PRIMARY KEY REFERENCES persons(person_id),
  relationship TEXT -- 'mother', 'father', 'guardian'
);

-- Linking students and guardians (many-to-many)
CREATE TABLE student_guardians (
  student_id BIGINT REFERENCES students(student_id) ON DELETE CASCADE,
  guardian_id BIGINT REFERENCES guardians(guardian_id) ON DELETE CASCADE,
  primary_contact BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (student_id, guardian_id)
);

-- Staff table
CREATE TABLE staff (
  staff_id BIGINT PRIMARY KEY REFERENCES persons(person_id),
  staff_number TEXT UNIQUE,
  hire_date DATE,
  role_id SMALLINT REFERENCES roles(id),
  department TEXT
);

-- Academic structures
CREATE TABLE academic_years (
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE, -- '2024-2025'
  start_date DATE,
  end_date DATE
);

CREATE TABLE terms (
  id SERIAL PRIMARY KEY,
  academic_year_id INT REFERENCES academic_years(id) ON DELETE CASCADE,
  name TEXT, -- 'Term 1'
  start_date DATE,
  end_date DATE
);

CREATE TABLE courses (
  course_id SERIAL PRIMARY KEY,
  code TEXT NOT NULL, -- 'ENG101'
  name TEXT NOT NULL,
  description TEXT,
  credits NUMERIC DEFAULT 0
);
CREATE UNIQUE INDEX idx_course_code ON courses(code);

-- ClassSection = a course offering in a term/grade
CREATE TABLE class_sections (
  section_id BIGSERIAL PRIMARY KEY,
  course_id INT REFERENCES courses(course_id),
  term_id INT REFERENCES terms(id),
  section_name TEXT, -- '5A', 'Math - A'
  grade_level TEXT, -- e.g., 'Grade 5'
  capacity INT,
  active BOOLEAN DEFAULT TRUE
);

-- Teachers assigned to sections (many-to-many)
CREATE TABLE section_teachers (
  section_id BIGINT REFERENCES class_sections(section_id) ON DELETE CASCADE,
  staff_id BIGINT REFERENCES staff(staff_id) ON DELETE SET NULL,
  role TEXT, -- 'main', 'assistant'
  PRIMARY KEY (section_id, staff_id)
);

-- Enrollment: which students are in which section
CREATE TABLE enrollments (
  enroll_id BIGSERIAL PRIMARY KEY,
  student_id BIGINT REFERENCES students(student_id) ON DELETE CASCADE,
  section_id BIGINT REFERENCES class_sections(section_id) ON DELETE CASCADE,
  enroll_date DATE DEFAULT now(),
  exit_date DATE,
  status TEXT CHECK (status IN ('enrolled','withdrawn','completed')) DEFAULT 'enrolled',
  UNIQUE (student_id, section_id)
);
CREATE INDEX ON enrollments (section_id, student_id);

-- Rooms
CREATE TABLE rooms (
  room_id SERIAL PRIMARY KEY,
  campus TEXT,
  building TEXT,
  room_no TEXT,
  capacity INT
);
CREATE UNIQUE INDEX idx_room_location ON rooms(campus, building, room_no);

-- Timetable / Sessions (a scheduled meeting of a section)
CREATE TABLE sessions (
  session_id BIGSERIAL PRIMARY KEY,
  section_id BIGINT REFERENCES class_sections(section_id) ON DELETE CASCADE,
  room_id INT REFERENCES rooms(room_id),
  session_date DATE NOT NULL,
  start_time TIME WITHOUT TIME ZONE,
  end_time TIME WITHOUT TIME ZONE,
  recurrence_rule TEXT, -- optional RRULE for recurring sessions
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX ON sessions (section_id, session_date);

-- Attendance (one row per student per session)
CREATE TABLE attendance (
  attendance_id BIGSERIAL PRIMARY KEY,
  session_id BIGINT REFERENCES sessions(session_id) ON DELETE CASCADE,
  student_id BIGINT REFERENCES students(student_id) ON DELETE CASCADE,
  status TEXT CHECK (status IN ('present','absent','late','excused')),
  recorded_by BIGINT REFERENCES staff(staff_id),
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  notes TEXT,
  UNIQUE(session_id, student_id)
);
CREATE INDEX ON attendance (session_id, student_id);
CREATE INDEX ON attendance (student_id, recorded_at);

-- Exams and grading
CREATE TABLE exams (
  exam_id BIGSERIAL PRIMARY KEY,
  name TEXT,
  course_id INT REFERENCES courses(course_id),
  term_id INT REFERENCES terms(id),
  exam_date DATE,
  max_score NUMERIC
);

CREATE TABLE grades (
  grade_id BIGSERIAL PRIMARY KEY,
  exam_id BIGINT REFERENCES exams(exam_id) ON DELETE CASCADE,
  student_id BIGINT REFERENCES students(student_id) ON DELETE CASCADE,
  score NUMERIC,
  grade_text TEXT, -- 'A', 'B'
  remarks TEXT,
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(exam_id, student_id)
);
CREATE INDEX ON grades (student_id, exam_id);

-- Fee management
CREATE TABLE fee_items (
  fee_item_id SERIAL PRIMARY KEY,
  name TEXT,
  description TEXT,
  amount NUMERIC NOT NULL
);

CREATE TABLE invoices (
  invoice_id BIGSERIAL PRIMARY KEY,
  student_id BIGINT REFERENCES students(student_id),
  issued_date DATE DEFAULT now(),
  due_date DATE,
  total_amount NUMERIC,
  status TEXT CHECK (status IN ('unpaid','partial','paid','overdue')) DEFAULT 'unpaid'
);

CREATE TABLE invoice_lines (
  line_id BIGSERIAL PRIMARY KEY,
  invoice_id BIGINT REFERENCES invoices(invoice_id) ON DELETE CASCADE,
  fee_item_id INT REFERENCES fee_items(fee_item_id),
  amount NUMERIC
);

CREATE TABLE payments (
  payment_id BIGSERIAL PRIMARY KEY,
  invoice_id BIGINT REFERENCES invoices(invoice_id) ON DELETE CASCADE,
  paid_date TIMESTAMP WITH TIME ZONE DEFAULT now(),
  amount NUMERIC,
  method TEXT -- 'cash', 'card', 'bank_transfer'
);

-- Audit logs (immutable)
CREATE TABLE audit_logs (
  log_id BIGSERIAL PRIMARY KEY,
  entity TEXT,
  entity_id TEXT,
  action TEXT,
  performed_by BIGINT, -- person_id
  performed_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  details JSONB
);
CREATE INDEX ON audit_logs (entity, entity_id);