SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS schools (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  institution_type ENUM('SD_MI','SMP_MTS','SMA_MA','SMK','PESANTREN','PERGURUAN_TINGGI','LAINNYA') NOT NULL DEFAULT 'LAINNYA',
  legal_name VARCHAR(200) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  address TEXT NULL,
  logo_path VARCHAR(255) NULL,
  status ENUM('active','suspended','pending') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_schools_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS school_domains (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  hostname VARCHAR(253) NOT NULL UNIQUE,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  verified_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_domains_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  INDEX idx_domains_school(school_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  price_monthly DECIMAL(14,2) NOT NULL DEFAULT 0,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  is_free TINYINT(1) NOT NULL DEFAULT 0,
  features_json JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS school_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL,
  status ENUM('trial','active','expired','cancelled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_subscription_school FOREIGN KEY (school_id) REFERENCES schools(id),
  CONSTRAINT fk_subscription_plan FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
  INDEX idx_subscription_current(school_id,status,ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  scope ENUM('platform','school') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(190) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(role_id,permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_permission FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_school_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_school_role(user_id,school_id,role_id),
  CONSTRAINT fk_usr_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_usr_school FOREIGN KEY(school_id) REFERENCES schools(id) ON DELETE CASCADE,
  CONSTRAINT fk_usr_role FOREIGN KEY(role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS school_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  setting_key VARCHAR(100) NOT NULL,
  setting_value JSON NULL,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_school_setting(school_id,setting_key),
  CONSTRAINT fk_settings_school FOREIGN KEY(school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS academic_years (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(30) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_academic_year(school_id,name),
  CONSTRAINT fk_year_school FOREIGN KEY(school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id VARCHAR(80) NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  details_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_school_date(school_id,created_at),
  INDEX idx_audit_user_date(user_id,created_at),
  CONSTRAINT fk_audit_school FOREIGN KEY(school_id) REFERENCES schools(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fondasi modul lanjutan. Semua tabel operasional ber-scope sekolah.
CREATE TABLE IF NOT EXISTS employees (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  employee_no VARCHAR(60) NULL,
  full_name VARCHAR(160) NOT NULL,
  employee_type ENUM('teacher','staff','principal','counselor','canteen') NOT NULL DEFAULT 'staff',
  phone VARCHAR(40) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_employee_no(school_id,employee_no),
  CONSTRAINT fk_employee_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_employee_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS guardians (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  full_name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  relationship_label VARCHAR(40) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guardian_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_guardian_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_no VARCHAR(60) NOT NULL,
  full_name VARCHAR(160) NOT NULL,
  birth_date DATE NULL,
  gender ENUM('L','P','X') NULL,
  status ENUM('applicant','active','graduated','transferred','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_student_no(school_id,student_no),
  CONSTRAINT fk_student_school FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS guardian_student_links (
  guardian_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  relationship_label VARCHAR(40) NULL,
  can_view_finance TINYINT(1) NOT NULL DEFAULT 1,
  can_view_academic TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY(guardian_id,student_id),
  CONSTRAINT fk_gsl_guardian FOREIGN KEY(guardian_id) REFERENCES guardians(id) ON DELETE CASCADE,
  CONSTRAINT fk_gsl_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_gsl_school FOREIGN KEY(school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS classes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  academic_year_id BIGINT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  level_label VARCHAR(50) NULL,
  homeroom_employee_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_class_year(school_id,academic_year_id,name),
  CONSTRAINT fk_class_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_class_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id) ON DELETE SET NULL,
  CONSTRAINT fk_class_homeroom FOREIGN KEY(homeroom_employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS class_memberships (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  class_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NULL,
  status ENUM('active','moved','completed') NOT NULL DEFAULT 'active',
  CONSTRAINT fk_cm_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_cm_class FOREIGN KEY(class_id) REFERENCES classes(id),
  CONSTRAINT fk_cm_student FOREIGN KEY(student_id) REFERENCES students(id),
  INDEX idx_cm_class_status(school_id,class_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fee_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  default_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_fee_type_school FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  fee_type_id BIGINT UNSIGNED NOT NULL,
  invoice_no VARCHAR(80) NOT NULL,
  due_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid','void') NOT NULL DEFAULT 'unpaid',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_invoice_no(school_id,invoice_no),
  CONSTRAINT fk_invoice_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_invoice_student FOREIGN KEY(student_id) REFERENCES students(id),
  CONSTRAINT fk_invoice_fee_type FOREIGN KEY(fee_type_id) REFERENCES fee_types(id),
  INDEX idx_invoice_student_status(school_id,student_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  method ENUM('cash','bank_transfer','qris_static') NOT NULL,
  reference_no VARCHAR(100) NULL,
  proof_path VARCHAR(255) NULL,
  status ENUM('pending','confirmed','rejected','refunded') NOT NULL DEFAULT 'pending',
  received_by BIGINT UNSIGNED NULL,
  paid_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_payment_student FOREIGN KEY(student_id) REFERENCES students(id),
  CONSTRAINT fk_payment_receiver FOREIGN KEY(received_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_payment_status(school_id,status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_allocations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  CONSTRAINT fk_pa_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_pa_payment FOREIGN KEY(payment_id) REFERENCES payments(id),
  CONSTRAINT fk_pa_invoice FOREIGN KEY(invoice_id) REFERENCES student_invoices(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS behavior_point_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  category ENUM('violation','achievement') NOT NULL,
  points INT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_bpr_school FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS behavior_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  rule_id BIGINT UNSIGNED NULL,
  recorded_by BIGINT UNSIGNED NULL,
  occurred_at DATETIME NOT NULL,
  description TEXT NOT NULL,
  visibility ENUM('internal','guardian','student_and_guardian') NOT NULL DEFAULT 'internal',
  followup_status ENUM('new','in_progress','resolved') NOT NULL DEFAULT 'new',
  points_snapshot INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_br_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_br_student FOREIGN KEY(student_id) REFERENCES students(id),
  CONSTRAINT fk_br_rule FOREIGN KEY(rule_id) REFERENCES behavior_point_rules(id) ON DELETE SET NULL,
  CONSTRAINT fk_br_user FOREIGN KEY(recorded_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_behavior_student_date(school_id,student_id,occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS parent_questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NULL,
  subject VARCHAR(180) NOT NULL,
  status ENUM('open','assigned','answered','closed') NOT NULL DEFAULT 'open',
  assigned_to BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_pq_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_pq_guardian FOREIGN KEY(guardian_id) REFERENCES guardians(id),
  CONSTRAINT fk_pq_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL,
  CONSTRAINT fk_pq_assignee FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS question_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  sender_user_id BIGINT UNSIGNED NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_qm_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_qm_question FOREIGN KEY(question_id) REFERENCES parent_questions(id) ON DELETE CASCADE,
  CONSTRAINT fk_qm_sender FOREIGN KEY(sender_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admission_periods (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  opens_at DATETIME NULL,
  closes_at DATETIME NULL,
  status ENUM('draft','open','closed','announced') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_admission_period_school FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admission_applications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NOT NULL,
  application_no VARCHAR(80) NOT NULL,
  applicant_name VARCHAR(160) NOT NULL,
  applicant_email VARCHAR(190) NULL,
  applicant_phone VARCHAR(40) NULL,
  target_program VARCHAR(120) NULL,
  status ENUM('submitted','verified','selected','rejected','registered') NOT NULL DEFAULT 'submitted',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_application_no(school_id,application_no),
  CONSTRAINT fk_application_school FOREIGN KEY(school_id) REFERENCES schools(id),
  CONSTRAINT fk_application_period FOREIGN KEY(period_id) REFERENCES admission_periods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS canteen_products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id BIGINT UNSIGNED NOT NULL,
  sku VARCHAR(60) NULL,
  name VARCHAR(160) NOT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0,
  stock_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_canteen_sku(school_id,sku),
  CONSTRAINT fk_canteen_product_school FOREIGN KEY(school_id) REFERENCES schools(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO subscription_plans(code,name,price_monthly,duration_days,is_free,features_json,is_active)
VALUES
('gratis','Gratis',0,30,1,JSON_OBJECT('schools','1','core_modules',true),1),
('premium','Premium',250000,30,0,JSON_OBJECT('core_modules',true,'reports',true),1);

INSERT IGNORE INTO roles(code,name,scope) VALUES
('super_admin','Super Admin','platform'),
('school_admin','Administrator Sekolah','school'),
('principal','Kepala Sekolah','school'),
('teacher','Guru','school'),
('homeroom','Wali Kelas','school'),
('duty_teacher','Guru Piket','school'),
('finance','Bendahara','school'),
('counselor','BK/BP','school'),
('canteen','Petugas Kantin','school'),
('guardian','Orang Tua/Wali','school'),
('student','Siswa','school');

INSERT IGNORE INTO permissions(code,description) VALUES
('platform.manage','Kelola platform'),
('school.manage','Kelola identitas dan pengaturan sekolah'),
('users.manage','Kelola pengguna'),
('students.manage','Kelola data siswa'),
('attendance.manage','Kelola absensi'),
('finance.manage','Kelola keuangan'),
('behavior.manage','Kelola catatan BK/BP'),
('admission.manage','Kelola penerimaan siswa'),
('canteen.manage','Kelola kantin'),
('reports.view','Lihat laporan');

SET FOREIGN_KEY_CHECKS=1;