-- ANish 24 schema for MariaDB 10.2+ / MySQL 5.7+ (InnoDB, utf8mb4). Safe to run more than once.
-- School separation: every school-owned table carries school_id and every query filters by it.

CREATE TABLE IF NOT EXISTS schools (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  city VARCHAR(100), phone VARCHAR(30),
  plan VARCHAR(30) NOT NULL DEFAULT 'trial',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Roles: admin, management, teacher, parent, student
CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  role VARCHAR(20) NOT NULL,
  full_name VARCHAR(200) NOT NULL,
  phone VARCHAR(30), email VARCHAR(190),
  password_hash VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_phone (school_id, phone),
  UNIQUE KEY uq_users_email (school_id, email),
  CONSTRAINT fk_users_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT chk_users_role CHECK (role IN ('admin','management','teacher','parent','student'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS classes (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  name VARCHAR(50) NOT NULL, section VARCHAR(20),
  teacher_id CHAR(36),
  UNIQUE KEY uq_classes (school_id, name, section),
  CONSTRAINT fk_classes_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_classes_teacher FOREIGN KEY (teacher_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subjects (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  name VARCHAR(100) NOT NULL,
  CONSTRAINT fk_subjects_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS students (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  user_id CHAR(36), class_id CHAR(36),
  roll_no VARCHAR(30), full_name VARCHAR(200) NOT NULL,
  dob DATE, gender VARCHAR(20), admitted_on DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  KEY ix_students_class (school_id, class_id),
  CONSTRAINT fk_students_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_students_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_students_class FOREIGN KEY (class_id) REFERENCES classes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- A parent user can have many children; this link drives parent permissions.
CREATE TABLE IF NOT EXISTS guardians (
  student_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  relation VARCHAR(30),
  PRIMARY KEY (student_id, user_id),
  CONSTRAINT fk_guardians_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_guardians_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS timetable (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL, class_id CHAR(36) NOT NULL,
  subject_id CHAR(36), teacher_id CHAR(36),
  weekday SMALLINT NOT NULL, starts_at TIME NOT NULL, ends_at TIME NOT NULL,
  CONSTRAINT fk_tt_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_tt_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance (
  school_id CHAR(36) NOT NULL,
  student_id CHAR(36) NOT NULL,
  day DATE NOT NULL,
  status VARCHAR(10) NOT NULL,
  marked_by CHAR(36),
  PRIMARY KEY (student_id, day),
  KEY ix_attendance_day (school_id, day),
  CONSTRAINT fk_att_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT chk_att_status CHECK (status IN ('present','absent','late','leave'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS homework (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL, class_id CHAR(36) NOT NULL, subject_id CHAR(36),
  title VARCHAR(300) NOT NULL, due_on DATE, created_by CHAR(36),
  CONSTRAINT fk_hw_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_hw_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS homework_status (
  homework_id CHAR(36) NOT NULL, student_id CHAR(36) NOT NULL,
  done TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (homework_id, student_id),
  CONSTRAINT fk_hws_hw FOREIGN KEY (homework_id) REFERENCES homework(id) ON DELETE CASCADE,
  CONSTRAINT fk_hws_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fee_plans (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL, class_id CHAR(36),
  head VARCHAR(100) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  frequency VARCHAR(20) NOT NULL DEFAULT 'monthly',
  CONSTRAINT fk_fp_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL, student_id CHAR(36) NOT NULL,
  period VARCHAR(7) NOT NULL,
  amount DECIMAL(12,2) NOT NULL, due_on DATE,
  status VARCHAR(10) NOT NULL DEFAULT 'unpaid',
  UNIQUE KEY uq_invoice_period (student_id, period),
  KEY ix_invoices_status (school_id, status),
  CONSTRAINT fk_inv_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT chk_inv_status CHECK (status IN ('unpaid','partial','paid','waived'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL, invoice_id CHAR(36) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method VARCHAR(20) NOT NULL,
  gateway_ref VARCHAR(120) UNIQUE,
  paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_payments_invoice (invoice_id),
  CONSTRAINT fk_pay_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS exams (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  name VARCHAR(200) NOT NULL, starts_on DATE, ends_on DATE,
  CONSTRAINT fk_exams_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS marks (
  exam_id CHAR(36) NOT NULL, student_id CHAR(36) NOT NULL, subject_id CHAR(36) NOT NULL,
  obtained DECIMAL(6,2) NOT NULL, total DECIMAL(6,2) NOT NULL DEFAULT 100,
  remarks VARCHAR(500),
  PRIMARY KEY (exam_id, student_id, subject_id),
  KEY ix_marks_student (student_id, exam_id),
  CONSTRAINT fk_marks_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  CONSTRAINT fk_marks_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_marks_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notices (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  audience VARCHAR(60) NOT NULL DEFAULT 'all',
  body TEXT NOT NULL, posted_by CHAR(36),
  posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notices_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  from_user CHAR(36) NOT NULL, to_user CHAR(36) NOT NULL,
  body TEXT NOT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_msg_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- WhatsApp / SMS queue, sent by the worker.
CREATE TABLE IF NOT EXISTS alerts_outbox (
  id CHAR(36) PRIMARY KEY,
  school_id CHAR(36) NOT NULL,
  to_phone VARCHAR(30) NOT NULL,
  channel VARCHAR(10) NOT NULL,
  body TEXT NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  error VARCHAR(400),
  next_try_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY ix_alerts_queue (status, next_try_at),
  KEY ix_alerts_school (school_id, created_at),
  CONSTRAINT fk_alerts_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  school_id CHAR(36) NOT NULL, user_id CHAR(36),
  action VARCHAR(80) NOT NULL, entity VARCHAR(60), entity_id VARCHAR(64),
  at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_audit_school (school_id, at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;