-- ============================================================
--  DATABASE: spp_sekolah
--  Sesuai dengan model Android App_pembayaran_spp
--  Siswa.kt | Siswarepository.kt | PembayaranFragment.kt
-- ============================================================

CREATE DATABASE IF NOT EXISTS spp_sekolah
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE spp_sekolah;

-- ============================================================
--  TABLE: admin
--  Login panel admin web
-- ============================================================
CREATE TABLE admin (
  id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  username    VARCHAR(50)     NOT NULL UNIQUE,
  password    VARCHAR(255)    NOT NULL COMMENT 'bcrypt hash',
  nama_lengkap VARCHAR(100)   NOT NULL,
  email       VARCHAR(100)    NOT NULL UNIQUE,
  role        ENUM('superadmin','admin','bendahara') NOT NULL DEFAULT 'admin',
  foto        VARCHAR(255)    DEFAULT NULL,
  last_login  DATETIME        DEFAULT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- ============================================================
--  TABLE: siswa
--  Sesuai data class Siswa.kt
--  id, nis, nama, kelas, jurusan, tahunAjaran, noHp, alamat
-- ============================================================
CREATE TABLE siswa (
  id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  nis           VARCHAR(20)     NOT NULL UNIQUE COMMENT 'Nomor Induk Siswa',
  nama          VARCHAR(100)    NOT NULL,
  kelas         ENUM('X','XI','XII') NOT NULL,
  jurusan       ENUM('IPA','IPS','TKJ','RPL','AK','PM') NOT NULL,
  tahun_ajaran  VARCHAR(9)      NOT NULL COMMENT 'format: 2024/2025',
  no_hp         VARCHAR(15)     DEFAULT NULL,
  alamat        TEXT            DEFAULT NULL,
  foto          VARCHAR(255)    DEFAULT NULL,
  status        ENUM('aktif','tidak_aktif','lulus') NOT NULL DEFAULT 'aktif',
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_kelas_jurusan (kelas, jurusan),
  INDEX idx_tahun_ajaran (tahun_ajaran),
  INDEX idx_status (status)
) ENGINE=InnoDB;

-- ============================================================
--  TABLE: tagihan_spp
--  Tagihan per siswa per bulan per tahun
-- ============================================================
CREATE TABLE tagihan_spp (
  id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  siswa_id      INT UNSIGNED    NOT NULL,
  bulan         TINYINT UNSIGNED NOT NULL COMMENT '1=Januari .. 12=Desember',
  tahun         YEAR            NOT NULL,
  nominal       DECIMAL(12,0)   NOT NULL DEFAULT 200000,
  status        ENUM('belum_bayar','lunas','cicilan') NOT NULL DEFAULT 'belum_bayar',
  keterangan    VARCHAR(255)    DEFAULT NULL,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tagihan (siswa_id, bulan, tahun),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_status (status),
  INDEX idx_bulan_tahun (bulan, tahun)
) ENGINE=InnoDB;

-- ============================================================
--  TABLE: pembayaran
--  Riwayat pembayaran — sesuai PembayaranFragment.kt
-- ============================================================
CREATE TABLE pembayaran (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  tagihan_id      INT UNSIGNED    NOT NULL,
  siswa_id        INT UNSIGNED    NOT NULL,
  admin_id        INT UNSIGNED    NOT NULL COMMENT 'petugas yang memproses',
  jumlah_bayar    DECIMAL(12,0)   NOT NULL,
  metode_bayar    ENUM('tunai','transfer','qris') NOT NULL DEFAULT 'tunai',
  no_rekening     VARCHAR(30)     DEFAULT NULL COMMENT 'jika transfer',
  keterangan      TEXT            DEFAULT NULL,
  tanggal_bayar   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  bukti_bayar     VARCHAR(255)    DEFAULT NULL COMMENT 'path foto bukti',
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (tagihan_id) REFERENCES tagihan_spp(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (siswa_id)   REFERENCES siswa(id)       ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (admin_id)   REFERENCES admin(id)       ON DELETE RESTRICT ON UPDATE CASCADE,
  INDEX idx_siswa_id (siswa_id),
  INDEX idx_tanggal (tanggal_bayar)
) ENGINE=InnoDB;

-- ============================================================
--  TABLE: pengaturan
--  Konfigurasi global (nominal SPP, nama sekolah, dll)
-- ============================================================
CREATE TABLE pengaturan (
  id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  kunci       VARCHAR(50)     NOT NULL UNIQUE,
  nilai       TEXT            NOT NULL,
  keterangan  VARCHAR(255)    DEFAULT NULL,
  updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- ============================================================
--  SEED DATA: admin default
--  password: admin123  (bcrypt hash)
-- ============================================================
INSERT INTO admin (username, password, nama_lengkap, email, role) VALUES
('superadmin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Administrator', 'superadmin@sekolah.sch.id', 'superadmin'),
('bendahara',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Bendahara Sekolah',   'bendahara@sekolah.sch.id',  'bendahara'),
('admin',      '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin Sekolah',       'admin@sekolah.sch.id',       'admin');

-- ============================================================
--  SEED DATA: siswa (sesuai Siswarepository.kt)
-- ============================================================
INSERT INTO siswa (nis, nama, kelas, jurusan, tahun_ajaran, no_hp, alamat) VALUES
('2401001', 'Andi Pratama',   'X',   'IPA', '2024/2025', '081234567890', 'Jl. Merdeka No.1'),
('2401002', 'Budi Santoso',   'X',   'IPS', '2024/2025', '081234567891', 'Jl. Pahlawan No.2'),
('2401003', 'Citra Dewi',     'X',   'IPA', '2024/2025', '081234567892', 'Jl. Kenangan No.3'),
('2401004', 'Dina Fitriani',  'XI',  'IPA', '2024/2025', '081234567893', 'Jl. Mawar No.4'),
('2401005', 'Eko Wahyudi',    'XI',  'IPS', '2024/2025', '081234567894', 'Jl. Melati No.5'),
('2401006', 'Fani Rahayu',    'XI',  'IPA', '2024/2025', '081234567895', 'Jl. Anggrek No.6'),
('2401007', 'Gilang Permana', 'XII', 'IPA', '2024/2025', '081234567896', 'Jl. Dahlia No.7'),
('2401008', 'Hana Oktaviani', 'XII', 'IPS', '2024/2025', '081234567897', 'Jl. Flamboyan No.8'),
('2401009', 'Irwan Setiawan', 'XII', 'IPA', '2024/2025', '081234567898', 'Jl. Raya No.9'),
('2401010', 'Juwita Sari',    'X',   'IPS', '2024/2025', '081234567899', 'Jl. Nusa No.10');

-- ============================================================
--  SEED DATA: pengaturan global
-- ============================================================
INSERT INTO pengaturan (kunci, nilai, keterangan) VALUES
('nama_sekolah',    'SMA Negeri 1 Contoh',  'Nama sekolah'),
('nominal_spp',     '200000',               'Nominal SPP per bulan (Rupiah)'),
('tahun_ajaran',    '2024/2025',             'Tahun ajaran aktif'),
('semester',        'Genap',                 'Semester aktif'),
('alamat_sekolah',  'Jl. Pendidikan No.1',  'Alamat sekolah'),
('telp_sekolah',    '(021) 1234567',         'Nomor telepon sekolah'),
('logo_sekolah',    'logo.png',              'Nama file logo');

COMMIT;
