-- =====================================================================
-- SIMAP — Modul SMK: jurusan, Praktik Kerja Lapangan, dan Uji Kompetensi
--
--   mysql -u root -p simap_db < database/10_smk_pkl_ukk.sql
--
-- Tiga hal yang membedakan SMK dari jenjang lain dan tidak bisa
-- dipaksakan memakai tabel yang ada:
--
--   1. Siswa terbagi ke dalam kompetensi keahlian, bukan hanya rombel
--   2. PKL berlangsung berbulan-bulan di luar sekolah, dengan pembimbing
--      dari industri, jurnal harian, dan penilaian dua pihak
--   3. UKK dinilai penguji dari industri memakai unit kompetensi, dan
--      hasilnya berupa sertifikat, bukan sekadar angka rapor
-- =====================================================================

SET NAMES utf8mb4;

-- ------------------------------------------------------------- JURUSAN
CREATE TABLE IF NOT EXISTS jurusan (
  id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kode        VARCHAR(20) NOT NULL UNIQUE,
  nama        VARCHAR(120) NOT NULL COMMENT 'Kompetensi keahlian',
  bidang      VARCHAR(120) NULL COMMENT 'Bidang keahlian induk',
  kaprodi_id  INT UNSIGNED NULL,
  akreditasi  VARCHAR(5) NULL,
  deskripsi   TEXT NULL,
  is_aktif    TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_jur_guru FOREIGN KEY (kaprodi_id) REFERENCES guru(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kelas dan siswa menempel ke jurusan
ALTER TABLE kelas ADD COLUMN IF NOT EXISTS jurusan_id SMALLINT UNSIGNED NULL AFTER tingkat;
ALTER TABLE siswa ADD COLUMN IF NOT EXISTS jurusan_id SMALLINT UNSIGNED NULL AFTER status;

-- --------------------------------------------------- INDUSTRI MITRA
CREATE TABLE IF NOT EXISTS industri (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama       VARCHAR(150) NOT NULL,
  bidang     VARCHAR(120) NULL,
  alamat     VARCHAR(255) NULL,
  kota       VARCHAR(80) NULL,
  telepon    VARCHAR(30) NULL,
  email      VARCHAR(100) NULL,
  narahubung VARCHAR(120) NULL,
  jabatan_narahubung VARCHAR(80) NULL,
  kuota      SMALLINT NOT NULL DEFAULT 0,
  no_mou     VARCHAR(60) NULL,
  mou_mulai  DATE NULL,
  mou_selesai DATE NULL,
  catatan    TEXT NULL,
  is_aktif   TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------- PRAKTIK KERJA LAPANGAN
CREATE TABLE IF NOT EXISTS pkl (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id       INT UNSIGNED NOT NULL,
  industri_id    INT UNSIGNED NOT NULL,
  pembimbing_sekolah_id INT UNSIGNED NULL,
  pembimbing_industri   VARCHAR(120) NULL,
  jabatan_pembimbing    VARCHAR(80) NULL,
  bagian         VARCHAR(120) NULL COMMENT 'Unit atau divisi penempatan',
  mulai          DATE NOT NULL,
  selesai        DATE NOT NULL,
  no_surat       VARCHAR(60) NULL,
  status         ENUM('DIAJUKAN','BERJALAN','SELESAI','DIBATALKAN')
                 NOT NULL DEFAULT 'DIAJUKAN',
  -- Penilaian datang dari dua pihak; keduanya disimpan terpisah supaya
  -- sekolah dapat melihat bila keduanya jauh berbeda
  nilai_industri DECIMAL(5,2) NULL,
  nilai_sekolah  DECIMAL(5,2) NULL,
  nilai_akhir    DECIMAL(5,2) NULL,
  predikat       VARCHAR(20) NULL,
  catatan_industri TEXT NULL,
  catatan_sekolah  TEXT NULL,
  sertifikat     VARCHAR(255) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pkl (tahun_ajaran_id, siswa_id, industri_id),
  KEY idx_status (status, mulai),
  CONSTRAINT fk_pkl_siswa FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  CONSTRAINT fk_pkl_industri FOREIGN KEY (industri_id) REFERENCES industri(id),
  CONSTRAINT fk_pkl_guru FOREIGN KEY (pembimbing_sekolah_id) REFERENCES guru(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Jurnal harian selama PKL, diisi siswa lewat aplikasi
CREATE TABLE IF NOT EXISTS pkl_jurnal (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pkl_id     INT UNSIGNED NOT NULL,
  tanggal    DATE NOT NULL,
  jam_masuk  TIME NULL,
  jam_pulang TIME NULL,
  kegiatan   TEXT NOT NULL,
  kendala    TEXT NULL,
  kompetensi VARCHAR(255) NULL COMMENT 'Kompetensi yang dilatih hari itu',
  foto       VARCHAR(255) NULL,
  status     ENUM('MENUNGGU','DISETUJUI','DITOLAK') NOT NULL DEFAULT 'MENUNGGU',
  catatan_pembimbing TEXT NULL,
  diverifikasi_oleh INT UNSIGNED NULL,
  diverifikasi_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_jurnal_pkl (pkl_id, tanggal),
  CONSTRAINT fk_pj_pkl FOREIGN KEY (pkl_id) REFERENCES pkl(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kehadiran PKL terpisah dari absensi sekolah karena tempatnya berbeda
CREATE TABLE IF NOT EXISTS pkl_absensi (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pkl_id    INT UNSIGNED NOT NULL,
  tanggal   DATE NOT NULL,
  status    ENUM('HADIR','SAKIT','IZIN','ALPA') NOT NULL DEFAULT 'HADIR',
  keterangan VARCHAR(255) NULL,
  UNIQUE KEY uq_absen_pkl (pkl_id, tanggal),
  CONSTRAINT fk_pa_pkl FOREIGN KEY (pkl_id) REFERENCES pkl(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------- UJI KOMPETENSI KEAHLIAN (UKK)
CREATE TABLE IF NOT EXISTS ukk (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  jurusan_id    SMALLINT UNSIGNED NOT NULL,
  nama          VARCHAR(150) NOT NULL,
  skema         VARCHAR(120) NULL COMMENT 'Skema sertifikasi atau paket soal',
  paket_soal    VARCHAR(60) NULL,
  penyelenggara ENUM('MANDIRI','LSP_P1','LSP_P2','LSP_P3','INDUSTRI')
                NOT NULL DEFAULT 'MANDIRI',
  industri_id   INT UNSIGNED NULL COMMENT 'Mitra penguji bila melibatkan industri',
  penguji_internal_id INT UNSIGNED NULL,
  penguji_eksternal   VARCHAR(120) NULL,
  asal_penguji_eksternal VARCHAR(150) NULL,
  tanggal_mulai DATE NULL,
  tanggal_selesai DATE NULL,
  tempat        VARCHAR(150) NULL,
  kkm           DECIMAL(5,2) NOT NULL DEFAULT 70,
  status        ENUM('DRAFT','TERJADWAL','BERLANGSUNG','SELESAI')
                NOT NULL DEFAULT 'DRAFT',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ukk (tahun_ajaran_id, jurusan_id),
  CONSTRAINT fk_ukk_jurusan FOREIGN KEY (jurusan_id) REFERENCES jurusan(id) ON DELETE CASCADE,
  CONSTRAINT fk_ukk_industri FOREIGN KEY (industri_id) REFERENCES industri(id) ON DELETE SET NULL,
  CONSTRAINT fk_ukk_guru FOREIGN KEY (penguji_internal_id) REFERENCES guru(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Unit kompetensi yang diujikan
CREATE TABLE IF NOT EXISTS ukk_unit (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ukk_id   INT UNSIGNED NOT NULL,
  kode     VARCHAR(40) NULL COMMENT 'Kode unit kompetensi SKKNI bila ada',
  nama     VARCHAR(200) NOT NULL,
  bobot    DECIMAL(5,2) NOT NULL DEFAULT 1,
  urutan   SMALLINT NOT NULL DEFAULT 1,
  CONSTRAINT fk_uu_ukk FOREIGN KEY (ukk_id) REFERENCES ukk(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ukk_peserta (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ukk_id    INT UNSIGNED NOT NULL,
  siswa_id  INT UNSIGNED NOT NULL,
  no_peserta VARCHAR(40) NULL,
  nilai     DECIMAL(5,2) NULL,
  predikat  VARCHAR(20) NULL,
  -- Istilah resmi UKK memakai Kompeten dan Belum Kompeten, bukan lulus
  keputusan ENUM('KOMPETEN','BELUM_KOMPETEN','MENGULANG') NULL,
  no_sertifikat VARCHAR(60) NULL,
  tanggal_sertifikat DATE NULL,
  sertifikat VARCHAR(255) NULL,
  catatan   TEXT NULL,
  UNIQUE KEY uq_ukk_peserta (ukk_id, siswa_id),
  CONSTRAINT fk_up_ukk FOREIGN KEY (ukk_id) REFERENCES ukk(id) ON DELETE CASCADE,
  CONSTRAINT fk_up_siswa FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ukk_nilai (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  peserta_id INT UNSIGNED NOT NULL,
  unit_id    INT UNSIGNED NOT NULL,
  skor       DECIMAL(5,2) NULL,
  kompeten   TINYINT(1) NULL,
  catatan    VARCHAR(255) NULL,
  UNIQUE KEY uq_ukk_nilai (peserta_id, unit_id),
  CONSTRAINT fk_un_peserta FOREIGN KEY (peserta_id) REFERENCES ukk_peserta(id) ON DELETE CASCADE,
  CONSTRAINT fk_un_unit FOREIGN KEY (unit_id) REFERENCES ukk_unit(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------- IZIN
INSERT IGNORE INTO permissions (kode, nama, modul) VALUES
 ('smk.jurusan',  'Kelola jurusan dan industri mitra', 'smk'),
 ('pkl.kelola',   'Kelola penempatan PKL',             'smk'),
 ('pkl.nilai',    'Menilai dan memverifikasi PKL',     'smk'),
 ('ukk.kelola',   'Kelola uji kompetensi keahlian',    'smk'),
 ('ukk.nilai',    'Menilai uji kompetensi keahlian',   'smk');

INSERT IGNORE INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.modul = 'smk'
 WHERE r.kode IN ('SUPERADMIN','KEPALA_SEKOLAH');

INSERT IGNORE INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.kode IN ('pkl.kelola','ukk.kelola')
 WHERE r.kode IN ('TU','OPERATOR');

INSERT IGNORE INTO role_permission (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.kode IN ('pkl.nilai','ukk.nilai')
 WHERE r.kode IN ('GURU','WALI_KELAS');

INSERT IGNORE INTO setting (skey, svalue, tipe, grup, label) VALUES
 ('pkl_durasi_bulan', '6', 'number', 'sekolah', 'Lama PKL dalam bulan'),
 ('pkl_min_jurnal', '20', 'number', 'sekolah', 'Jurnal minimal agar PKL dinyatakan selesai'),
 ('ukk_bobot_industri', '60', 'number', 'sekolah', 'Bobot nilai penguji industri (persen)');

SELECT 'modul SMK siap' AS info,
       (SELECT COUNT(*) FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name IN ('jurusan','industri','pkl','pkl_jurnal','pkl_absensi',
                              'ukk','ukk_unit','ukk_peserta','ukk_nilai')) AS tabel_baru;