-- ============================================================
-- DATABASE: SISTEM PENDAMPINGAN SEKOLAH SMP
-- Dinas Pendidikan Kabupaten Malang
-- ============================================================

CREATE DATABASE IF NOT EXISTS `pendamping_sekolah`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `pendamping_sekolah`;

-- -----------------------------------------------------------
-- TABEL: users
-- -----------------------------------------------------------
CREATE TABLE `users` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama`       VARCHAR(100) NOT NULL,
  `username`   VARCHAR(50)  NOT NULL UNIQUE,
  `email`      VARCHAR(100) NOT NULL UNIQUE,
  `password`   VARCHAR(255) NOT NULL,
  `role`       ENUM('admin','dinas','sekolah') NOT NULL DEFAULT 'dinas',
  `sekolah_id` INT UNSIGNED NULL COMMENT 'FK ke tabel sekolah jika role=sekolah',
  `aktif`      TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: profil_pendamping
-- -----------------------------------------------------------
CREATE TABLE `profil_pendamping` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama_lengkap`   VARCHAR(150) NOT NULL,
  `nip`            VARCHAR(30)  NULL,
  `jabatan`        VARCHAR(100) NOT NULL,
  `unit_kerja`     VARCHAR(150) NOT NULL DEFAULT 'Dinas Pendidikan Kabupaten Malang',
  `pangkat`        VARCHAR(100) NULL,
  `golongan`       VARCHAR(10)  NULL,
  `pendidikan`     VARCHAR(100) NULL,
  `telepon`        VARCHAR(20)  NULL,
  `email`          VARCHAR(100) NULL,
  `alamat`         TEXT         NULL,
  `foto`           VARCHAR(255) NULL,
  `tentang`        TEXT         NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: riwayat_tugas
-- -----------------------------------------------------------
CREATE TABLE `riwayat_tugas` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `profil_id`      INT UNSIGNED NOT NULL,
  `jabatan`        VARCHAR(150) NOT NULL,
  `unit_kerja`     VARCHAR(150) NOT NULL,
  `tahun_mulai`    YEAR         NOT NULL,
  `tahun_selesai`  YEAR         NULL COMMENT 'NULL = masih menjabat',
  `keterangan`     TEXT         NULL,
  FOREIGN KEY (`profil_id`) REFERENCES `profil_pendamping`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: sekolah
-- -----------------------------------------------------------
CREATE TABLE `sekolah` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `npsn`            VARCHAR(20)  NOT NULL UNIQUE,
  `nama_sekolah`    VARCHAR(200) NOT NULL,
  `jenis`           ENUM('SMP Negeri','SMP Swasta') NOT NULL,
  `kecamatan`       VARCHAR(100) NOT NULL,
  `alamat`          TEXT         NOT NULL,
  `kepala_sekolah`  VARCHAR(150) NOT NULL,
  `nip_kepsek`      VARCHAR(30)  NULL,
  `telepon`         VARCHAR(20)  NULL,
  `email`           VARCHAR(100) NULL,
  `website`         VARCHAR(150) NULL,
  `akreditasi`      ENUM('A','B','C','Belum Terakreditasi') NULL,
  `jumlah_guru`     INT NULL,
  `jumlah_siswa`    INT NULL,
  `logo`            VARCHAR(255) NULL,
  `aktif`           TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: program_kerja
-- -----------------------------------------------------------
CREATE TABLE `program_kerja` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tahun_ajaran`    VARCHAR(20) NOT NULL,
  `nama_program`    VARCHAR(200) NOT NULL,
  `deskripsi`       TEXT         NULL,
  `target_kegiatan` INT NOT NULL DEFAULT 1,
  `waktu_mulai`     DATE         NOT NULL,
  `waktu_selesai`   DATE         NOT NULL,
  `status`          ENUM('Belum Mulai','Berjalan','Selesai') NOT NULL DEFAULT 'Belum Mulai',
  `keterangan`      TEXT         NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: agenda_kegiatan
-- -----------------------------------------------------------
CREATE TABLE `agenda_kegiatan` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tanggal`       DATE         NOT NULL,
  `waktu_mulai`   TIME         NULL,
  `waktu_selesai` TIME         NULL,
  `nama_kegiatan` VARCHAR(200) NOT NULL,
  `tempat`        VARCHAR(200) NOT NULL,
  `penyelenggara` VARCHAR(150) NULL,
  `keterangan`    TEXT         NULL,
  `status`        ENUM('Terjadwal','Selesai','Dibatalkan') NOT NULL DEFAULT 'Terjadwal',
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: kegiatan_pendampingan
-- -----------------------------------------------------------
CREATE TABLE `kegiatan_pendampingan` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sekolah_id`        INT UNSIGNED NOT NULL,
  `program_kerja_id`  INT UNSIGNED NULL,
  `tanggal_kegiatan`  DATE         NOT NULL,
  `nama_kegiatan`     VARCHAR(200) NOT NULL,
  `materi`            TEXT         NOT NULL,
  `sasaran`           VARCHAR(200) NOT NULL,
  `peserta`           INT          NULL,
  `hasil_kegiatan`    TEXT         NOT NULL,
  `narasi`            LONGTEXT     NULL,
  `tindak_lanjut`     TEXT         NULL,
  `status`            ENUM('Draft','Terkirim','Disetujui') NOT NULL DEFAULT 'Draft',
  `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`sekolah_id`)       REFERENCES `sekolah`(`id`)       ON DELETE RESTRICT,
  FOREIGN KEY (`program_kerja_id`) REFERENCES `program_kerja`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: foto_kegiatan
-- -----------------------------------------------------------
CREATE TABLE `foto_kegiatan` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kegiatan_id`   INT UNSIGNED NOT NULL,
  `nama_file`     VARCHAR(255) NOT NULL,
  `keterangan`    VARCHAR(200) NULL,
  `urutan`        INT NOT NULL DEFAULT 0,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`kegiatan_id`) REFERENCES `kegiatan_pendampingan`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: file_laporan
-- -----------------------------------------------------------
CREATE TABLE `file_laporan` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kegiatan_id`  INT UNSIGNED NOT NULL,
  `nama_file`    VARCHAR(255) NOT NULL,
  `nama_asli`    VARCHAR(255) NOT NULL,
  `ukuran`       INT          NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`kegiatan_id`) REFERENCES `kegiatan_pendampingan`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: dokumen
-- -----------------------------------------------------------
CREATE TABLE `dokumen` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sekolah_id`   INT UNSIGNED NULL COMMENT 'NULL = dokumen umum/dinas',
  `nama_dokumen` VARCHAR(200) NOT NULL,
  `jenis`        ENUM('SK','Surat Edaran','Pedoman','Juknis','Form','Laporan','Lainnya') NOT NULL,
  `deskripsi`    TEXT         NULL,
  `nama_file`    VARCHAR(255) NOT NULL,
  `nama_asli`    VARCHAR(255) NOT NULL,
  `ukuran`       INT          NULL,
  `tahun`        YEAR         NULL,
  `publik`       TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`sekolah_id`) REFERENCES `sekolah`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: pengembangan_diri
-- -----------------------------------------------------------
CREATE TABLE `pengembangan_diri` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `jenis_kegiatan`    ENUM('Diklat','Seminar','Workshop','Webinar','Bimtek','Lokakarya','Lainnya') NOT NULL,
  `judul_kegiatan`    VARCHAR(250) NOT NULL,
  `penyelenggara`     VARCHAR(200) NOT NULL,
  `tempat`            VARCHAR(200) NULL,
  `tanggal_mulai`     DATE         NOT NULL,
  `tanggal_selesai`   DATE         NOT NULL,
  `jumlah_jp`         INT          NULL COMMENT 'Jam Pelajaran',
  `nomor_sertifikat`  VARCHAR(100) NULL,
  `tanggal_sertifikat` DATE        NULL,
  `resume_materi`     LONGTEXT     NULL,
  `sertifikat_file`   VARCHAR(255) NULL,
  `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- TABEL: audit_log (keamanan)
-- -----------------------------------------------------------
CREATE TABLE `audit_log` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `aksi`       VARCHAR(100) NOT NULL,
  `tabel`      VARCHAR(50)  NULL,
  `record_id`  INT          NULL,
  `deskripsi`  TEXT         NULL,
  `ip_address` VARCHAR(45)  NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- INDEX PERFORMA
-- -----------------------------------------------------------
CREATE INDEX idx_kegiatan_sekolah   ON kegiatan_pendampingan(sekolah_id);
CREATE INDEX idx_kegiatan_tanggal   ON kegiatan_pendampingan(tanggal_kegiatan);
CREATE INDEX idx_agenda_tanggal     ON agenda_kegiatan(tanggal);
CREATE INDEX idx_dokumen_sekolah    ON dokumen(sekolah_id);
CREATE INDEX idx_users_sekolah      ON users(sekolah_id);

-- -----------------------------------------------------------
-- DATA AWAL: Admin
-- Password: Admin@2024 (bcrypt hash)
-- -----------------------------------------------------------
INSERT INTO `users` (`nama`, `username`, `email`, `password`, `role`) VALUES
('Administrator Sistem', 'admin', 'admin@disdik-malang.go.id',
 '$2y$12$LM7dHBvFQzNyMKJ9Qvg5IOk.fJxVxEWzW7vKYlWHgGp1.Rt0qRHCO', 'admin');

INSERT INTO `profil_pendamping` (`nama_lengkap`, `jabatan`, `unit_kerja`, `pangkat`, `golongan`) VALUES
('Nama Pendamping', 'Pengawas Sekolah / Pendamping SMP', 'Dinas Pendidikan Kabupaten Malang', 'Pembina', 'IV/a');
