-- Create Database
CREATE DATABASE IF NOT EXISTS skp_sister;
USE skp_sister;

-- Drop Tables if Exist to Ensure Clean Initialization
DROP TABLE IF EXISTS skp_lampiran;
DROP TABLE IF EXISTS skp_perilaku_kerja;
DROP TABLE IF EXISTS skp_hasil_kerja;
DROP TABLE IF EXISTS skp_dokumen;
DROP TABLE IF EXISTS skp_periode;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS penilai;
DROP TABLE IF EXISTS dosen;
DROP TABLE IF EXISTS sister_config;

-- 1. Table for Dosen
CREATE TABLE dosen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_sdm VARCHAR(255) UNIQUE NULL,
    nidn VARCHAR(20) UNIQUE NULL,
    nuptk VARCHAR(30) UNIQUE NULL,
    nip VARCHAR(30) UNIQUE NULL,
    nama VARCHAR(150) NOT NULL,
    pangkat_golongan VARCHAR(50),
    jabatan VARCHAR(50),
    unit_kerja VARCHAR(100),
    email VARCHAR(100),
    gelar_depan VARCHAR(30) DEFAULT NULL,
    gelar_belakang VARCHAR(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Table for Penilai (Pejabat Penilai Kinerja)
CREATE TABLE penilai (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(30) UNIQUE NOT NULL,
    nama VARCHAR(150) NOT NULL,
    pangkat_golongan VARCHAR(50),
    jabatan VARCHAR(50),
    unit_kerja VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Table for User Authentication
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('dosen', 'penilai', 'admin') NOT NULL,
    associated_id INT DEFAULT NULL -- maps to dosen.id or penilai.id based on role
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Table for SKP Periods
CREATE TABLE skp_periode (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL, -- e.g., 'Semester Ganjil 2025/2026', 'Tahun Akademik 2025/2026'
    tahun INT NOT NULL,
    rentang_waktu VARCHAR(100) NOT NULL, -- e.g., "01 Januari 2025 s/d 31 Desember 2025"
    tempat_ttd VARCHAR(50) DEFAULT 'Bandung',
    tanggal_ttd_rencana VARCHAR(50) DEFAULT '05 Februari 2025',
    tanggal_ttd_evaluasi VARCHAR(50) DEFAULT '02 Januari 2026',
    is_active TINYINT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Table for SKP Documents
CREATE TABLE skp_dokumen (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dosen_id INT NOT NULL,
    penilai_id INT NOT NULL,
    periode_id INT NOT NULL,
    akreditasi_institusi VARCHAR(20) DEFAULT 'Unggul',
    predikat_kinerja VARCHAR(50) DEFAULT 'Baik',
    rating_hasil_kerja VARCHAR(50) DEFAULT 'Sesuai Ekspektasi',
    rating_perilaku VARCHAR(50) DEFAULT 'Sesuai Ekspektasi',
    catatan_rekomendasi TEXT,
    status VARCHAR(20) DEFAULT 'DRAFT', -- 'DRAFT', 'SUBMITTED', 'EVALUATED'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dosen_id) REFERENCES dosen(id) ON DELETE CASCADE,
    FOREIGN KEY (penilai_id) REFERENCES penilai(id) ON DELETE CASCADE,
    FOREIGN KEY (periode_id) REFERENCES skp_periode(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Table for SKP Hasil Kerja (Utama & Tambahan)
CREATE TABLE skp_hasil_kerja (
    id INT AUTO_INCREMENT PRIMARY KEY,
    skp_id INT NOT NULL,
    kategori ENUM('Utama', 'Tambahan') NOT NULL,
    rencana_hasil_kerja TEXT NOT NULL,
    indikator_kinerja TEXT NOT NULL,
    target TEXT NOT NULL,
    realisasi TEXT,
    umpan_balik TEXT,
    FOREIGN KEY (skp_id) REFERENCES skp_dokumen(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Table for SKP Perilaku Kerja (Core Values BerAKHLAK)
CREATE TABLE skp_perilaku_kerja (
    id INT AUTO_INCREMENT PRIMARY KEY,
    skp_id INT NOT NULL,
    aspek VARCHAR(100) NOT NULL, -- e.g., 'Berorientasi Pelayanan', 'Akuntabel', etc.
    deskripsi_aspek TEXT,
    ekspektasi_khusus TEXT,
    umpan_balik TEXT,
    FOREIGN KEY (skp_id) REFERENCES skp_dokumen(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Table for SKP Lampiran (Sumber Daya, Pertanggungjawaban, Konsekuensi)
CREATE TABLE skp_lampiran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    skp_id INT NOT NULL,
    tipe ENUM('Sumber Daya', 'Pertanggungjawaban', 'Konsekuensi') NOT NULL,
    deskripsi TEXT NOT NULL,
    FOREIGN KEY (skp_id) REFERENCES skp_dokumen(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Table for SISTER Config
CREATE TABLE sister_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    base_url VARCHAR(255) DEFAULT 'https://sister.kemdikbud.go.id/api',
    client_id VARCHAR(100) DEFAULT '',
    client_secret VARCHAR(100) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- SEED INITIAL DATA
-- =========================================================================

-- Seed SISTER Config
INSERT INTO sister_config (base_url, client_id, client_secret)
VALUES ('https://sister-api.kemdiktisaintek.go.id/ws.php/1.0', 'client_12345', 'secret_67890');

-- Seed Active SKP Periods (Tahun Kalender, Tahun Akademik, & Semesters)
INSERT INTO skp_periode (id, nama, tahun, rentang_waktu, tempat_ttd, tanggal_ttd_rencana, tanggal_ttd_evaluasi, is_active)
VALUES 
(1, 'Tahun Kalender 2024', 2024, '01 September 2024 s/d 31 Agustus 2025', 'Bandung', '05 Februari 2025', '02 Januari 2026', 1),
(2, 'Tahun Akademik 2024/2025', 2024, '01 September 2024 s/d 31 Agustus 2025', 'Bandung', '05 September 2024', '31 Agustus 2025', 1),
(3, 'Tahun Akademik 2025/2026', 2025, '01 September 2025 s/d 31 Agustus 2026', 'Bandung', '05 September 2025', '31 Agustus 2026', 1),
(4, 'Semester Ganjil 2024/2025', 2024, '01 September 2024 s/d 28 Februari 2025', 'Bandung', '10 September 2024', '28 Februari 2025', 1),
(5, 'Semester Genap 2024/2025', 2025, '01 Maret 2025 s/d 31 Agustus 2025', 'Bandung', '10 Maret 2025', '31 Agustus 2025', 1),
(6, 'Semester Ganjil 2025/2026', 2025, '01 September 2025 s/d 28 Februari 2026', 'Bandung', '10 September 2025', '28 Februari 2026', 1),
(7, 'Semester Genap 2025/2026', 2026, '01 Maret 2026 s/d 31 Agustus 2026', 'Bandung', '10 Maret 2026', '31 Agustus 2026', 1),
(8, 'Tahun Kalender 2025', 2025, '01 September 2025 s/d 31 Agustus 2026', 'Bandung', '05 Februari 2026', '02 Januari 2027', 1),
(9, 'Semester Ganjil 2026/2027', 2026, '01 September 2026 s/d 28 Februari 2027', 'Bandung', '10 September 2026', '28 Februari 2027', 1),
(10, 'Semester Genap 2026/2027', 2027, '01 Maret 2027 s/d 31 Agustus 2027', 'Bandung', '10 Maret 2027', '31 Agustus 2027', 1),
(11, 'Semester Ganjil 2027/2028', 2027, '01 September 2027 s/d 29 Februari 2028', 'Bandung', '10 September 2027', '28 Februari 2028', 1),
(12, 'Semester Genap 2027/2028', 2028, '01 Maret 2028 s/d 31 Agustus 2028', 'Bandung', '10 Maret 2028', '31 Agustus 2028', 1),
(13, 'Semester Ganjil 2028/2029', 2028, '01 September 2028 s/d 28 Februari 2029', 'Bandung', '10 September 2028', '28 Februari 2029', 1),
(14, 'Semester Genap 2028/2029', 2029, '01 Maret 2029 s/d 31 Agustus 2029', 'Bandung', '10 Maret 2029', '31 Agustus 2029', 1),
(15, 'Semester Ganjil 2029/2030', 2029, '01 September 2029 s/d 28 Februari 2030', 'Bandung', '10 September 2029', '28 Februari 2030', 1),
(16, 'Semester Genap 2029/2030', 2030, '01 Maret 2030 s/d 31 Agustus 2030', 'Bandung', '10 Maret 2030', '31 Agustus 2030', 1),
(17, 'Tahun Kalender 2026', 2026, '01 Januari 2026 s/d 31 Desember 2026', 'Bandung', '05 Januari 2026', '31 Desember 2026', 1),
(18, 'Tahun Kalender 2027', 2027, '01 Januari 2027 s/d 31 Desember 2027', 'Bandung', '05 Januari 2027', '31 Desember 2027', 1),
(19, 'Tahun Kalender 2028', 2028, '01 Januari 2028 s/d 31 Desember 2028', 'Bandung', '05 Januari 2028', '31 Desember 2028', 1),
(20, 'Tahun Kalender 2029', 2029, '01 Januari 2029 s/d 31 Desember 2029', 'Bandung', '05 Januari 2029', '31 Desember 2029', 1);

-- Seed Lecturer (Dosen) - Antonius Ngadiran (populated dynamically from SISTER Live Roster on first fetch)
-- Re-map user associated_id to 6 (Antonius Ngadiran)
INSERT INTO users (username, password, role, associated_id)
VALUES 
('dosen', '$2a$10$hL0wN1Zf.3gQ2F9f37.2qO4Qf8pXgR.eU1lF4j0zC45i4x3wz7tBq', 'dosen', 6), -- password: 'password123'
('pimpinan', '$2a$10$hL0wN1Zf.3gQ2F9f37.2qO4Qf8pXgR.eU1lF4j0zC45i4x3wz7tBq', 'penilai', 1); -- password: 'password123'

-- =========================================================================
-- PDDIKTI NEO FEEDER SCHEMAS
-- =========================================================================

CREATE TABLE IF NOT EXISTS feeder_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  base_url VARCHAR(255) NOT NULL,
  kode_pt VARCHAR(50) NOT NULL,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS feeder_kelas_mahasiswa (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kode_mata_kuliah VARCHAR(100) NOT NULL,
  nama_mata_kuliah VARCHAR(255) NOT NULL,
  nama_kelas VARCHAR(100) NOT NULL,
  jumlah_mahasiswa INT NOT NULL,
  semester_id VARCHAR(50) NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_class (semester_id, kode_mata_kuliah, nama_kelas)
);

INSERT INTO feeder_config (id, base_url, kode_pt, username, password)
VALUES (1, 'http://103.119.55.175:8100/ws/live2.php', '042025', 'siskavitrisiapddikti@gmail.com', 'Siskabaik!')
ON DUPLICATE KEY UPDATE
  base_url = VALUES(base_url),
  kode_pt = VALUES(kode_pt),
  username = VALUES(username),
  password = VALUES(password);