CREATE TABLE IF NOT EXISTS academic_terms (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, academic_year INT NOT NULL, name VARCHAR(40) NOT NULL, starts_on DATE NOT NULL, ends_on DATE NOT NULL, UNIQUE(school_id,academic_year,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS class_subjects (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, class_id INT NOT NULL, subject_id INT NOT NULL, teacher_id INT NOT NULL, academic_year INT NOT NULL, term VARCHAR(40) NOT NULL, UNIQUE(class_id,subject_id,academic_year,term), FOREIGN KEY(class_id) REFERENCES classes(id), FOREIGN KEY(subject_id) REFERENCES subjects(id), FOREIGN KEY(teacher_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS timetable_slots (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, assignment_id INT NOT NULL, weekday INT NOT NULL, starts_at TIME NOT NULL, ends_at TIME NOT NULL, room VARCHAR(80) NOT NULL, active TINYINT NOT NULL DEFAULT 1, FOREIGN KEY(assignment_id) REFERENCES class_subjects(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS homework (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, assignment_id INT NOT NULL, title VARCHAR(160) NOT NULL, instructions TEXT NOT NULL, due_at DATETIME NOT NULL, created_by INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(assignment_id) REFERENCES class_subjects(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS submissions (
 id INT AUTO_INCREMENT PRIMARY KEY, homework_id INT NOT NULL, student_id INT NOT NULL, response TEXT NOT NULL, submitted_at DATETIME NOT NULL, feedback TEXT NULL, reviewed_by INT NULL, reviewed_at DATETIME NULL, UNIQUE(homework_id,student_id), FOREIGN KEY(homework_id) REFERENCES homework(id), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS student_details (
 student_id INT PRIMARY KEY, prior_school VARCHAR(160) NOT NULL DEFAULT '', emergency_name VARCHAR(140) NOT NULL DEFAULT '', emergency_phone VARCHAR(40) NOT NULL DEFAULT '', nationality VARCHAR(80) NOT NULL DEFAULT '', FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS documents (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, title VARCHAR(160) NOT NULL, category VARCHAR(30) NOT NULL, storage_key VARCHAR(80) NOT NULL, mime_type VARCHAR(80) NOT NULL, size_bytes INT NOT NULL, uploaded_by INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS promotion_batches (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, request_key VARCHAR(64) NOT NULL UNIQUE, source_class INT NOT NULL, academic_year INT NOT NULL, processed_count INT NOT NULL, created_by INT NOT NULL, created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS grading_schemes (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, section VARCHAR(20) NOT NULL, name VARCHAR(100) NOT NULL, boundaries TEXT NOT NULL, UNIQUE(school_id,section)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS report_comments (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, academic_year INT NOT NULL, term VARCHAR(40) NOT NULL, teacher_comment TEXT NOT NULL, head_comment TEXT NOT NULL, updated_by INT NOT NULL, UNIQUE(student_id,academic_year,term), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ledger_accounts (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(120) NOT NULL, type VARCHAR(20) NOT NULL, control_account TINYINT NOT NULL DEFAULT 0, UNIQUE(school_id,code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS journal_entries (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, entry_date DATE NOT NULL, currency VARCHAR(8) NOT NULL, description VARCHAR(255) NOT NULL, source_key VARCHAR(120) NOT NULL UNIQUE, created_by INT NOT NULL, created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS journal_lines (
 id INT AUTO_INCREMENT PRIMARY KEY, journal_id INT NOT NULL, account_id INT NOT NULL, debit_minor BIGINT NOT NULL DEFAULT 0, credit_minor BIGINT NOT NULL DEFAULT 0, FOREIGN KEY(journal_id) REFERENCES journal_entries(id), FOREIGN KEY(account_id) REFERENCES ledger_accounts(id), INDEX(account_id,journal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS finance_periods (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, starts_on DATE NOT NULL, ends_on DATE NOT NULL, label VARCHAR(100) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'open', reviewed_by INT NULL, UNIQUE(school_id,starts_on,ends_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS journal_requests (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, entry_date DATE NOT NULL, currency VARCHAR(8) NOT NULL, description VARCHAR(255) NOT NULL, lines_json TEXT 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS suppliers (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, name VARCHAR(140) NOT NULL, email VARCHAR(190) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_bills (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, supplier_id INT NOT NULL, reference VARCHAR(120) NOT NULL, description VARCHAR(255) NOT NULL, amount_minor BIGINT NOT NULL, currency VARCHAR(8) NOT NULL, expense_account INT NOT NULL, billed_on DATE NOT NULL, due_on DATE NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_by INT NOT NULL, reviewed_by INT NULL, UNIQUE(supplier_id,reference), FOREIGN KEY(supplier_id) REFERENCES suppliers(id), FOREIGN KEY(expense_account) REFERENCES ledger_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_payments (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, bill_id INT NOT NULL, amount_minor BIGINT NOT NULL, account_id INT NOT NULL, paid_on DATE NOT NULL, reference VARCHAR(120) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_by INT NOT NULL, reviewed_by INT NULL, UNIQUE(bill_id,reference), FOREIGN KEY(bill_id) REFERENCES supplier_bills(id), FOREIGN KEY(account_id) REFERENCES ledger_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_statement_lines (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, account_id INT NOT NULL, entry_date DATE NOT NULL, currency VARCHAR(8) NOT NULL, reference VARCHAR(120) NOT NULL, amount_minor BIGINT NOT NULL, matched_line_id INT NULL UNIQUE, matched_by INT NULL, matched_at DATETIME NULL, UNIQUE(account_id,entry_date,currency,reference,amount_minor), FOREIGN KEY(account_id) REFERENCES ledger_accounts(id), FOREIGN KEY(matched_line_id) REFERENCES journal_lines(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS opening_balances (
 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, as_of DATE NOT NULL, reference VARCHAR(160) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_by INT NOT NULL, reviewed_by INT NULL, invoice_id INT NULL UNIQUE, UNIQUE(student_id,currency,as_of), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(invoice_id) REFERENCES invoices(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_allocations (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, invoice_id INT NOT NULL, payment_id INT NOT NULL, amount_minor BIGINT NOT NULL, created_by INT NOT NULL, created_at DATETIME NOT NULL, UNIQUE(invoice_id,payment_id), FOREIGN KEY(invoice_id) REFERENCES invoices(id), FOREIGN KEY(payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admissions (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, application_number VARCHAR(40) NOT NULL UNIQUE, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, dob DATE NULL, class_id INT NOT NULL, guardian_name VARCHAR(140) NOT NULL, guardian_email VARCHAR(190) NOT NULL DEFAULT '', guardian_phone VARCHAR(40) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'applied', notes TEXT NOT NULL, student_id INT NULL UNIQUE, created_by INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY(class_id) REFERENCES classes(id), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS report_snapshots (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, academic_year INT NOT NULL, term VARCHAR(40) NOT NULL, revision INT NOT NULL, payload_json LONGTEXT NOT NULL, payload_hash VARCHAR(64) NOT NULL, reason VARCHAR(255) NOT NULL, published_by INT NOT NULL, published_at DATETIME NOT NULL, UNIQUE(student_id,academic_year,term,payload_hash), UNIQUE(student_id,academic_year,term,revision), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fee_adjustments (
 id INT AUTO_INCREMENT PRIMARY KEY, school_id INT NOT NULL, student_id INT NOT NULL, invoice_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(invoice_id) REFERENCES invoices(id), FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;