-- =====================================================================
-- SISTEM PENGELOLAAN SURAT
-- Database Schema (MySQL)
-- =====================================================================
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE IF NOT EXISTS db_pengelolaan_surat CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_pengelolaan_surat;

-- ---------------------------------------------------------------------
-- MASTER: JABATAN
-- ---------------------------------------------------------------------
CREATE TABLE jabatan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_jabatan VARCHAR(100) NOT NULL,
    keterangan VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- MASTER: INSTANSI (pengirim / tujuan surat)
-- ---------------------------------------------------------------------
CREATE TABLE instansi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_instansi VARCHAR(150) NOT NULL,
    alamat TEXT DEFAULT NULL,
    telepon VARCHAR(30) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    kontak_person VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- MASTER: JENIS SURAT
-- ---------------------------------------------------------------------
CREATE TABLE jenis_surat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_jenis VARCHAR(100) NOT NULL,
    kode VARCHAR(20) DEFAULT NULL COMMENT 'kode untuk format nomor surat otomatis',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- USERS (Multiuser: administrator, kepala_sekolah, tata_usaha, staff)
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL COMMENT 'password_hash()',
    nama_lengkap VARCHAR(150) NOT NULL,
    email VARCHAR(100) DEFAULT NULL,
    role ENUM('administrator','kepala_sekolah','tata_usaha','staff') NOT NULL DEFAULT 'staff',
    jabatan_id INT DEFAULT NULL,
    instansi_id INT DEFAULT NULL,
    foto VARCHAR(255) DEFAULT NULL,
    status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_jabatan FOREIGN KEY (jabatan_id) REFERENCES jabatan(id) ON DELETE SET NULL,
    CONSTRAINT fk_users_instansi FOREIGN KEY (instansi_id) REFERENCES instansi(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- SURAT MASUK
-- ---------------------------------------------------------------------
CREATE TABLE surat_masuk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    no_agenda VARCHAR(50) NOT NULL UNIQUE COMMENT 'nomor agenda otomatis, mis: 001/SM/VII/2026',
    no_surat VARCHAR(100) NOT NULL COMMENT 'nomor surat asli dari instansi pengirim',
    tanggal_surat DATE NOT NULL,
    tanggal_terima DATE NOT NULL,
    instansi_id INT NOT NULL COMMENT 'instansi pengirim',
    jenis_surat_id INT DEFAULT NULL,
    perihal VARCHAR(255) NOT NULL,
    sifat_surat ENUM('biasa','penting','segera','rahasia') NOT NULL DEFAULT 'biasa',
    file_path VARCHAR(255) DEFAULT NULL COMMENT 'path file PDF',
    status_disposisi ENUM('belum','menunggu','diproses','selesai') NOT NULL DEFAULT 'belum',
    keterangan TEXT DEFAULT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_sm_instansi FOREIGN KEY (instansi_id) REFERENCES instansi(id) ON DELETE RESTRICT,
    CONSTRAINT fk_sm_jenis FOREIGN KEY (jenis_surat_id) REFERENCES jenis_surat(id) ON DELETE SET NULL,
    CONSTRAINT fk_sm_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- SURAT KELUAR
-- ---------------------------------------------------------------------
CREATE TABLE surat_keluar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    no_surat VARCHAR(50) NOT NULL UNIQUE COMMENT 'nomor surat otomatis, mis: 045/SK/SMA-X/VII/2026',
    tanggal_surat DATE NOT NULL,
    instansi_tujuan_id INT NOT NULL,
    jenis_surat_id INT DEFAULT NULL,
    perihal VARCHAR(255) NOT NULL,
    sifat_surat ENUM('biasa','penting','segera','rahasia') NOT NULL DEFAULT 'biasa',
    isi_ringkas TEXT DEFAULT NULL,
    file_path VARCHAR(255) DEFAULT NULL COMMENT 'path file PDF',
    status_persetujuan ENUM('draft','menunggu','disetujui','ditolak') NOT NULL DEFAULT 'draft',
    disetujui_oleh INT DEFAULT NULL,
    tanggal_persetujuan DATETIME DEFAULT NULL,
    catatan_persetujuan VARCHAR(255) DEFAULT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_sk_instansi FOREIGN KEY (instansi_tujuan_id) REFERENCES instansi(id) ON DELETE RESTRICT,
    CONSTRAINT fk_sk_jenis FOREIGN KEY (jenis_surat_id) REFERENCES jenis_surat(id) ON DELETE SET NULL,
    CONSTRAINT fk_sk_approver FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_sk_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- NOMOR SURAT KELUAR COUNTER (bantu generate nomor otomatis per tahun)
-- ---------------------------------------------------------------------
CREATE TABLE counter_nomor_surat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tahun INT NOT NULL,
    jenis ENUM('surat_masuk','surat_keluar') NOT NULL,
    nomor_terakhir INT NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_tahun_jenis (tahun, jenis)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- DISPOSISI (surat masuk -> ditugaskan ke user)
-- ---------------------------------------------------------------------
CREATE TABLE disposisi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    surat_masuk_id INT NOT NULL,
    dari_user_id INT NOT NULL COMMENT 'yang mengirim disposisi',
    ke_user_id INT NOT NULL COMMENT 'yang menerima disposisi',
    instruksi TEXT NOT NULL COMMENT 'instruksi/catatan disposisi',
    status ENUM('menunggu','diproses','selesai') NOT NULL DEFAULT 'menunggu',
    tanggal_disposisi DATETIME DEFAULT CURRENT_TIMESTAMP,
    tanggal_selesai DATETIME DEFAULT NULL,
    catatan_selesai TEXT DEFAULT NULL,
    CONSTRAINT fk_disp_surat FOREIGN KEY (surat_masuk_id) REFERENCES surat_masuk(id) ON DELETE CASCADE,
    CONSTRAINT fk_disp_dari FOREIGN KEY (dari_user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_disp_ke FOREIGN KEY (ke_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- LOG AKTIVITAS (opsional, untuk audit trail sederhana)
-- ---------------------------------------------------------------------
CREATE TABLE log_aktivitas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    aktivitas VARCHAR(255) NOT NULL,
    modul VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SEED DATA AWAL
-- =====================================================================

INSERT INTO jabatan (nama_jabatan, keterangan) VALUES
('Kepala Sekolah', 'Pimpinan tertinggi sekolah'),
('Wakil Kepala Sekolah', 'Wakil pimpinan sekolah'),
('Kepala Tata Usaha', 'Penanggung jawab administrasi'),
('Staff Tata Usaha', 'Pelaksana administrasi surat'),
('Guru', 'Tenaga pendidik');

INSERT INTO instansi (nama_instansi, alamat, telepon, email) VALUES
('Dinas Pendidikan Kota', 'Jl. Pendidikan No. 1', '021-1234567', 'dinaspendidikan@kota.go.id'),
('SMA Negeri Mitra', 'Jl. Merdeka No. 10', '021-7654321', 'smanmitra@sch.id'),
('Komite Sekolah', 'Jl. Sekolah No. 5', '021-1112223', 'komite@sekolah.id');

INSERT INTO jenis_surat (nama_jenis, kode) VALUES
('Surat Undangan', 'UND'),
('Surat Edaran', 'SE'),
('Surat Keputusan', 'SK'),
('Surat Pemberitahuan', 'PEMB'),
('Surat Permohonan', 'MHN'),
('Surat Keterangan', 'KET');

-- Password default untuk semua akun di bawah: "password123"
-- Hash bcrypt valid (kompatibel dengan password_verify() PHP)
INSERT INTO users (username, password, nama_lengkap, email, role, jabatan_id, instansi_id, status) VALUES
('admin', '$2b$12$XAy1WQIW72AKryS5ZPAhTODFzMgeLDTe2NaUJVYVoFtah5QiIjxyy', 'Administrator Sistem', 'admin@sekolah.id', 'administrator', 3, NULL, 'aktif'),
('kepsek', '$2b$12$XAy1WQIW72AKryS5ZPAhTODFzMgeLDTe2NaUJVYVoFtah5QiIjxyy', 'Drs. Ahmad Kepala Sekolah', 'kepsek@sekolah.id', 'kepala_sekolah', 1, NULL, 'aktif'),
('tatausaha', '$2b$12$XAy1WQIW72AKryS5ZPAhTODFzMgeLDTe2NaUJVYVoFtah5QiIjxyy', 'Siti Tata Usaha', 'tu@sekolah.id', 'tata_usaha', 3, NULL, 'aktif'),
('staff', '$2b$12$XAy1WQIW72AKryS5ZPAhTODFzMgeLDTe2NaUJVYVoFtah5QiIjxyy', 'Budi Staff', 'staff@sekolah.id', 'staff', 4, NULL, 'aktif');