-- ===== SchoolSync Database Schema (MySQL) =====
-- Migrasi dari SQLite ke MySQL untuk Sistem Manajemen Sekolah Berbasis Barcode
-- Engine: InnoDB (untuk dukungan foreign key)
-- Charset: utf8mb4 (untuk dukungan emoji & karakter lengkap)

SET FOREIGN_KEY_CHECKS = 0;

-- ===== Pengaturan Sekolah =====
CREATE TABLE IF NOT EXISTS pengaturan (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Users (akun login) =====
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('super_admin','admin','guru','siswa','orangtua') NOT NULL,
  person_id INT NULL,                 -- reference ke siswa/guru/orang_tua (null untuk admin/super_admin)
  nama_lengkap VARCHAR(150),          -- nama tampilan untuk admin/super_admin
  status VARCHAR(20) DEFAULT 'aktif',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Hak Akses Admin (permission per module) =====
-- Super Admin dapat membatasi hak akses admin per modul
CREATE TABLE IF NOT EXISTS admin_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT NOT NULL,
  modul VARCHAR(50) NOT NULL,          -- 'dashboard','students','teachers','attendance','transactions','notifications','reports','barcode','schedule','id_cards'
  diizinkan TINYINT(1) DEFAULT 1,      -- 1=boleh, 0=dilarang
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perm (admin_user_id, modul),
  FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Siswa =====
CREATE TABLE IF NOT EXISTS siswa (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nis VARCHAR(50) UNIQUE NOT NULL,
  nama VARCHAR(150) NOT NULL,
  kelas VARCHAR(50),
  jenis_kelamin ENUM('L','P'),
  alamat TEXT,
  tanggal_lahir DATE,
  nama_ortu VARCHAR(150),
  no_hp_ortu VARCHAR(30),
  email_ortu VARCHAR(150),
  barcode_id VARCHAR(100) UNIQUE NOT NULL,
  saldo BIGINT DEFAULT 0,
  pin VARCHAR(10) DEFAULT '1234',
  limit_harian BIGINT DEFAULT 50000,
  status VARCHAR(20) DEFAULT 'aktif',
  foto VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_siswa_barcode (barcode_id),
  INDEX idx_siswa_kelas (kelas)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Guru =====
CREATE TABLE IF NOT EXISTS guru (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nip VARCHAR(50) UNIQUE NOT NULL,
  nama VARCHAR(150) NOT NULL,
  mata_pelajaran VARCHAR(100),
  no_hp VARCHAR(30),
  email VARCHAR(150),
  barcode_id VARCHAR(100) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'aktif',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_guru_barcode (barcode_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Orang Tua (mapping ke siswa) =====
CREATE TABLE IF NOT EXISTS orang_tua (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(150) NOT NULL,
  siswa_id INT NOT NULL,
  no_hp VARCHAR(30),
  email VARCHAR(150),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  INDEX idx_ortu_siswa (siswa_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Kelas =====
CREATE TABLE IF NOT EXISTS kelas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) UNIQUE NOT NULL,
  wali_kelas VARCHAR(150),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Absensi =====
CREATE TABLE IF NOT EXISTS absensi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  person_id INT NOT NULL,
  person_type ENUM('siswa','guru') NOT NULL,
  tanggal DATE NOT NULL,
  waktu_masuk DATETIME,
  waktu_pulang DATETIME,
  status ENUM('hadir','terlambat','alpha','izin','sakit'),
  keterangan TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_absensi_person (person_type, person_id, tanggal),
  INDEX idx_absensi_tanggal (tanggal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Transaksi =====
CREATE TABLE IF NOT EXISTS transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  siswa_id INT NOT NULL,
  jenis ENUM('debet','kredit') NOT NULL,
  kategori VARCHAR(100),
  jumlah BIGINT NOT NULL,
  saldo_sebelum BIGINT,
  saldo_sesudah BIGINT,
  merchant VARCHAR(150),
  keterangan TEXT,
  tanggal DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  INDEX idx_transaksi_siswa (siswa_id, tanggal),
  INDEX idx_transaksi_tanggal (tanggal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Notifikasi =====
CREATE TABLE IF NOT EXISTS notifikasi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  orangtua_user_id INT,           -- user id orang tua
  siswa_id INT,
  tipe VARCHAR(50),               -- 'transaksi', 'absensi', 'sistem'
  judul VARCHAR(200),
  pesan TEXT,
  dibaca TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  INDEX idx_notifikasi_user (orangtua_user_id, dibaca)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== Jadwal Pelajaran =====
CREATE TABLE IF NOT EXISTS jadwal (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kelas VARCHAR(50) NOT NULL,
  hari VARCHAR(20) NOT NULL,
  jam_ke INT NOT NULL,
  mapel VARCHAR(100),
  guru VARCHAR(150),
  jam_mulai TIME,
  jam_selesai TIME,
  INDEX idx_jadwal_kelas (kelas, hari)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;