CREATE TABLE IF NOT EXISTS schools (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, type VARCHAR(20) NOT NULL DEFAULT 'combined', email VARCHAR(190) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL DEFAULT '', address TEXT, academic_year INT NOT NULL DEFAULT 2026, term VARCHAR(40) NOT NULL DEFAULT 'Term 3', term_start DATE NOT NULL, term_end DATE NOT NULL, currency VARCHAR(8) NOT NULL DEFAULT 'USD', late_time VARCHAR(5) NOT NULL DEFAULT '07:45', email_attendance TINYINT NOT NULL DEFAULT 1, email_results TINYINT NOT NULL DEFAULT 1, timezone VARCHAR(60) NOT NULL DEFAULT 'Africa/Harare', grade_a INT NOT NULL DEFAULT 75, grade_b INT NOT NULL DEFAULT 65, grade_c INT NOT NULL DEFAULT 50, grade_d INT NOT NULL DEFAULT 40
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, name VARCHAR(140) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role VARCHAR(30) NOT NULL, active TINYINT NOT NULL DEFAULT 1, must_change_password TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS classes (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, name VARCHAR(80) NOT NULL, section VARCHAR(20) NOT NULL, teacher_id INT NULL, UNIQUE(school_id,name), FOREIGN KEY(school_id) REFERENCES schools(id), FOREIGN KEY(teacher_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS students (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_number VARCHAR(40) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, dob DATE NULL, gender VARCHAR(20) NOT NULL DEFAULT '', class_id INT NOT NULL, user_id INT NULL UNIQUE, status VARCHAR(30) NOT NULL DEFAULT 'active', admitted_on DATE NOT NULL, left_on DATE NULL, address TEXT, created_at DATETIME NOT NULL, UNIQUE(school_id,student_number), FOREIGN KEY(school_id) REFERENCES schools(id), FOREIGN KEY(class_id) REFERENCES classes(id), FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS guardians (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, user_id INT NULL UNIQUE, name VARCHAR(140) NOT NULL, email VARCHAR(190) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL, FOREIGN KEY(school_id) REFERENCES schools(id), FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS student_guardians (
 student_id INT NOT NULL, guardian_id INT NOT NULL, relationship VARCHAR(40) NOT NULL, receive_emails TINYINT NOT NULL DEFAULT 1, PRIMARY KEY(student_id,guardian_id), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(guardian_id) REFERENCES guardians(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS enrolments (
 id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, class_id INT NOT NULL, academic_year INT NOT NULL, started_on DATE NOT NULL, ended_on DATE NULL, reason VARCHAR(255) NOT NULL DEFAULT 'Admission', FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(class_id) REFERENCES classes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS attendance (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, attendance_date DATE NOT NULL, status VARCHAR(20) NOT NULL, arrival_time VARCHAR(5) NULL, reason VARCHAR(255) NOT NULL DEFAULT '', marked_by INT NOT NULL, updated_at DATETIME NOT NULL, UNIQUE(student_id,attendance_date), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(marked_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fee_schedules (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, class_id INT NOT NULL, title VARCHAR(120) NOT NULL, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, academic_year INT NOT NULL, term VARCHAR(40) NOT NULL, due_on DATE NOT NULL, UNIQUE(class_id,title,currency,academic_year,term), FOREIGN KEY(class_id) REFERENCES classes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoices (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, schedule_id INT NULL, title VARCHAR(160) NOT NULL, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, billed_on DATE NOT NULL, due_on DATE NOT NULL, created_by INT NOT NULL, UNIQUE(student_id,schedule_id), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(schedule_id) REFERENCES fee_schedules(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, receipt_number VARCHAR(60) NOT NULL UNIQUE, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, method VARCHAR(30) NOT NULL, reference VARCHAR(120) NOT NULL, paid_on DATE NOT NULL, status VARCHAR(20) NOT NULL, idempotency_key VARCHAR(64) NOT NULL UNIQUE, created_by INT NOT NULL, verified_by INT NULL, reversal_reason VARCHAR(255) NULL, reversed_by INT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS concessions (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, reason VARCHAR(255) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_by INT NOT NULL, approved_by INT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS subjects (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, name VARCHAR(100) NOT NULL, section VARCHAR(20) NOT NULL, UNIQUE(school_id,name,section)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS assessments (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, class_id INT NOT NULL, subject_id INT NOT NULL, title VARCHAR(120) NOT NULL, maximum DECIMAL(8,2) NOT NULL, weight DECIMAL(6,2) NOT NULL DEFAULT 100, academic_year INT NOT NULL, term VARCHAR(40) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'draft', created_by INT NOT NULL, reviewed_by INT NULL, published_by INT NULL, published_at DATETIME NULL, UNIQUE(class_id,subject_id,title,academic_year,term), FOREIGN KEY(class_id) REFERENCES classes(id), FOREIGN KEY(subject_id) REFERENCES subjects(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS marks (
 id INT AUTO_INCREMENT PRIMARY KEY, assessment_id INT NOT NULL, student_id INT NOT NULL, score DECIMAL(8,2) NULL, status VARCHAR(20) NOT NULL DEFAULT 'unmarked', comment VARCHAR(255) NOT NULL DEFAULT '', UNIQUE(assessment_id,student_id), FOREIGN KEY(assessment_id) REFERENCES assessments(id), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notifications (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, guardian_id INT NULL, student_id INT NULL, recipient VARCHAR(190) NOT NULL, subject VARCHAR(190) NOT NULL, body TEXT NOT NULL, event_key VARCHAR(190) NOT NULL UNIQUE, status VARCHAR(20) NOT NULL DEFAULT 'queued', attempts INT NOT NULL DEFAULT 0, last_error VARCHAR(255) NULL, created_at DATETIME NOT NULL, sent_at DATETIME NULL, FOREIGN KEY(guardian_id) REFERENCES guardians(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notices (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, title VARCHAR(160) NOT NULL, body TEXT NOT NULL, audience VARCHAR(20) NOT NULL DEFAULT 'everyone', event_on DATE NULL, created_by INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, user_id INT NULL, action VARCHAR(80) NOT NULL, entity VARCHAR(40) NOT NULL, entity_id INT NULL, details TEXT NOT NULL, created_at DATETIME NOT NULL, INDEX(school_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS password_resets (
 id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, token_hash VARCHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used_at DATETIME NULL, FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS login_attempts (
 id INT AUTO_INCREMENT PRIMARY KEY, identity_hash VARCHAR(64) NOT NULL, attempted_at DATETIME NOT NULL, INDEX(identity_hash,attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS school_days (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, day_date DATE NOT NULL, title VARCHAR(100) NOT NULL, UNIQUE(school_id,day_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS refunds (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, payment_id INT NOT NULL, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, reason VARCHAR(255) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_by INT NOT NULL, reviewed_by INT NULL, created_at DATETIME NOT NULL, reviewed_at DATETIME NULL, FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(payment_id) REFERENCES payments(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;