-- =================================================================================
-- SKRIP DATABASE MYSQL - SISTEM EVALUASI KINERJA PIMPINAN CAMPUS (IKI BANDUNG)
-- Dibuat khusus untuk mendukung integrasi backend, pelaporan mandiri, dan integrasi data
-- =================================================================================

CREATE DATABASE IF NOT EXISTS msdmikim_nilaipimpinan;
USE msdmikim_nilaipimpinan;

-- 1. TABEL PIMPINAN (Leaders)
-- Menyimpan nama-nama pimpinan yang dievaluasi (Rektor, Wakil Rektor I-III, Pendeta Kampus)
CREATE TABLE IF NOT EXISTS pimpinan (
    id VARCHAR(50) PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    jabatan VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. TABEL ADMINISTRATOR (Admin Users)
-- Menyimpan kredensial otentikasi administrator portal evaluasi
CREATE TABLE IF NOT EXISTS admin_users (
    id VARCHAR(50) PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    nama_lengkap VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL, -- Menyimpan password terenkripsi (misal: bcrypt)
    role VARCHAR(50) DEFAULT 'Evaluator',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. TABEL RESPONDEN / TARGET UNDANGAN WHATSAPP (WA Invitations)
-- Menyimpan daftar target peserta sivitas akademika untuk distribusi tautan kuesioner otomatis
CREATE TABLE IF NOT EXISTS undangan_wa (
    id VARCHAR(50) PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    no_hp VARCHAR(50) NOT NULL,
    role ENUM('Dosen', 'Tenaga Kependidikan') NOT NULL,
    unit VARCHAR(255) NOT NULL,
    sent_count INT DEFAULT 0,
    last_sent_at DATETIME DEFAULT NULL,
    is_completed TINYINT(1) DEFAULT 0,
    completed_at DATETIME DEFAULT NULL,
    draft_data LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. TABEL RESPONS / HASIL KUESIONER (Evaluation Responses)
-- Menyimpan metadata dasar pengisian kuesioner. Bersifat anonim tanpa relasi langsung ke pengisi undangan.
CREATE TABLE IF NOT EXISTS respons_evaluasi (
    id VARCHAR(50) PRIMARY KEY,
    timestamp DATETIME NOT NULL,
    status ENUM('Dosen', 'Tenaga Kependidikan') NOT NULL,
    unit VARCHAR(255) NOT NULL,
    pimpinan_id VARCHAR(50) NOT NULL,
    skor_rata_rata DECIMAL(3, 2) NOT NULL,
    aspek_kekuatan TEXT DEFAULT NULL,       -- Harapan/kelebihan pimpinan (Open text)
    aspek_perbaikan TEXT DEFAULT NULL,      -- Ranah perbaikan (Open text)
    rekomendasi_saran TEXT DEFAULT NULL,    -- Saran untuk institusi (Open text)
    respondent_id VARCHAR(100) DEFAULT NULL, -- ID Responden / Token unik (satu link = 1 responden)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pimpinan_id) REFERENCES pimpinan(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT uq_respondent_pimpinan UNIQUE (respondent_id, pimpinan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. TABEL DETAIL NILAI PER PERTANYAAN (Evaluation Scores)
-- Menghubungkan respons_evaluasi dengan nilai-nilai per butir pertanyaan (1 s.d. 25)
CREATE TABLE IF NOT EXISTS detail_skor_pertanyaan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    respons_id VARCHAR(50) NOT NULL,
    nomor_pertanyaan INT NOT NULL,  -- Angka antara 1 s.d. 25
    skor INT NOT NULL CHECK (skor BETWEEN 1 AND 5),
    FOREIGN KEY (respons_id) REFERENCES respons_evaluasi(id) ON DELETE CASCADE,
    UNIQUE KEY uq_respons_pertanyaan (respons_id, nomor_pertanyaan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. TABEL UNIT / PRODI (Units & Study Programs)
-- Menyimpan pilihan unit kerja atau program studi responden untuk dropdown dinamis
CREATE TABLE IF NOT EXISTS unit_prodi (
    id VARCHAR(50) PRIMARY KEY,
    nama VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. TABEL ASPEK KUESIONER (Questionnaire Aspects)
CREATE TABLE IF NOT EXISTS kuesioner_aspek (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. TABEL PERTANYAAN KUESIONER (Questionnaire Questions)
CREATE TABLE IF NOT EXISTS kuesioner_pertanyaan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    aspect_id INT NOT NULL,
    text TEXT NOT NULL,
    FOREIGN KEY (aspect_id) REFERENCES kuesioner_aspek(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




-- =================================================================================
-- SEEDING DATA AWAL (Initial Seed Data)
-- Memasukkan default data setara dengan kondisi mock-data pada aplikasi React
-- =================================================================================

-- Tambah Data Aspek Kuesioner
INSERT INTO kuesioner_aspek (id, title, description) VALUES
(1, 'Aspek Kepemimpinan Visioner', 'Menilai kemampuan pimpinan dalam menetapkan visi yang jelas, menerjemahkan menjadi aksi nyata, serta mengambil keputusan strategis.'),
(2, 'Aspek Komunikasi dan Keterbukaan', 'Menilai keterbukaan pimpinan, kemampuan menerima umpan balik/kritik, serta membangun hubungan komunikasi yang sehat.'),
(3, 'Aspek Tata Kelola dan Manajerial', 'Menilai kompetensi profesional dalam mengelola jalannya organisasi, efisiensi sumber daya, serta penyelesaian konflik.'),
(4, 'Aspek Pelayanan dan Relasi', 'Menilai orientasi melayani kepada seluruh civitas akademika, tenggang rasa, dan kemampuan menciptakan suasana kerja kondusif.'),
(5, 'Aspek Integritas dan Keteladanan', 'Menilai kejujuran, keadilan dalam bersikap, penerapan etika, disiplin, dan kemampuan pimpinan menjaga nama baik institusi.'),
(6, 'Aspek Pengembangan Institusi', 'Menilai komitmen pimpinan terhadap penjaminan mutu, inovasi program baru, kolaborasi eksternal, dan keberlanjutan institusi.')
ON DUPLICATE KEY UPDATE title=VALUES(title), description=VALUES(description);

-- Tambah Data Pertanyaan Kuesioner
INSERT INTO kuesioner_pertanyaan (id, aspect_id, text) VALUES
(1, 1, 'Pimpinan memiliki visi yang jelas bagi pengembangan institusi/fakultas.'),
(2, 1, 'Pimpinan mampu menerjemahkan visi menjadi program kerja yang nyata.'),
(3, 1, 'Pimpinan menunjukkan arah kebijakan yang konsisten.'),
(4, 1, 'Pimpinan mampu mengambil keputusan strategis secara tepat.'),
(5, 2, 'Pimpinan menyampaikan informasi secara jelas dan terbuka.'),
(6, 2, 'Pimpinan terbuka terhadap kritik dan saran.'),
(7, 2, 'Pimpinan membangun komunikasi yang baik dengan civitas akademika.'),
(8, 2, 'Pimpinan responsif terhadap persoalan yang disampaikan.'),
(9, 3, 'Pimpinan menjalankan tata kelola yang profesional.'),
(10, 3, 'Pimpinan mampu mengelola sumber daya secara efektif.'),
(11, 3, 'Kebijakan yang dibuat pimpinan dilaksanakan secara konsisten.'),
(12, 3, 'Pimpinan mampu menyelesaikan masalah organisasi dengan baik.'),
(13, 4, 'Pimpinan menunjukkan sikap melayani kepada civitas akademika.'),
(14, 4, 'Pimpinan memperhatikan kebutuhan dosen dan tenaga kependidikan.'),
(15, 4, 'Pimpinan membangun suasana kerja yang kondusif.'),
(16, 4, 'Pimpinan menghargai perbedaan pendapat.'),
(17, 5, 'Pimpinan menunjukkan integritas dalam menjalankan tugas.'),
(18, 5, 'Pimpinan menjadi teladan dalam etika dan disiplin kerja.'),
(19, 5, 'Pimpinan bersikap adil dalam pengambilan keputusan.'),
(20, 5, 'Pimpinan menjaga nama baik institusi.'),
(21, 6, 'Pimpinan mendukung peningkatan mutu akademik.'),
(22, 6, 'Pimpinan mendorong inovasi dan pengembangan program.'),
(23, 6, 'Pimpinan mendukung kerja sama eksternal institusi.'),
(24, 6, 'Pimpinan memiliki perhatian terhadap keberlanjutan institusi.')
ON DUPLICATE KEY UPDATE text=VALUES(text);

-- Tambah Data Pimpinan Utama (Sesuai Struktur Baru)
INSERT INTO pimpinan (id, nama, jabatan, department) VALUES
('L1', 'Rektor', 'Rektor', 'Rektorat'),
('L2', 'Wakil Rektor I', 'Wakil Rektor I', 'Akademik'),
('L3', 'Wakil Rektor II', 'Wakil Rektor II', 'Keuangan & Administrasi Umum'),
('L4', 'Wakil Rektor III', 'Wakil Rektor III', 'Kemahasiswaan & Kerjasama'),
('L5', 'Pendeta Kampus', 'Pendeta Kampus', 'Spiritualitas & Kerohanian')
ON DUPLICATE KEY UPDATE nama=VALUES(nama), jabatan=VALUES(jabatan), department=VALUES(department);

-- Tambah Data Default Admin
-- password_hash disii hash text simulasi (untuk produksi silakan ganti dengan hash bcrypt riil)
INSERT INTO admin_users (id, username, nama_lengkap, password_hash, role) VALUES
('A1', 'admin', 'Administrator Utama', '$2b$10$UCWm1UPeUhI55mmyhDVY7./v2XOdQRkq.13NzM0uULEeGf6DxJn9G', 'Super Admin'),
('A2', 'rektorat', 'Sekretariat Rektorat', '$2b$10$UCWm1UPeUhI55mmyhDVY7./v2XOdQRkq.13NzM0uULEeGf6DxJn9G', 'Evaluator')
ON DUPLICATE KEY UPDATE nama_lengkap=VALUES(nama_lengkap);

-- Tambah Data Undangan WhatsApp (Sivitas Akademika Target)
INSERT INTO undangan_wa (id, nama, no_hp, role, unit, sent_count) VALUES
('INV-1', 'Asep Suryana, M.Kes.', '6281234567890', 'Dosen', 'Program Studi S1 Farmasi', 0),
('INV-2', 'Dewi Lestari, M.Kep.', '6282198765432', 'Dosen', 'Program Studi S1 Keperawatan', 0),
('INV-3', 'Agus Gunawan', '6285711223344', 'Tenaga Kependidikan', 'Biro Administrasi Akademik (BAA)', 0),
('INV-4', 'Rita Sugiarto, S.E.', '629677889900', 'Tenaga Kependidikan', 'Bagian Keuangan & Staff BAU', 0)
ON DUPLICATE KEY UPDATE nama=VALUES(nama), no_hp=VALUES(no_hp), role=VALUES(role), unit=VALUES(unit);

-- Tambah Data Pilihan Unit Kerja / Program Studi
INSERT INTO unit_prodi (id, nama) VALUES
('U-1', 'Program Studi S1 Farmasi'),
('U-2', 'Program Studi S1 Keperawatan'),
('U-3', 'Biro Administrasi Akademik (BAA)'),
('U-4', 'Bagian Keuangan & Staff BAU'),
('U-5', 'Program Studi S1 Kebidanan'),
('U-6', 'Biro Kepegawaian / HRD')
ON DUPLICATE KEY UPDATE nama=VALUES(nama);




-- =================================================================================
-- CONTOH KATA KUNCI QUERY UNTUK VISUALISASI DASBOR (Useful Report Queries)
-- =================================================================================

-- A. Query menghitung nilai rata-rata per Pimpinan
-- SELECT 
--     p.jabatan AS pimpinan,
--     p.nama AS nama_pimpinan,
--     COUNT(r.id) AS total_opini,
--     ROUND(AVG(r.skor_rata_rata), 2) AS rata_rata_indeks_kinerja
-- FROM pimpinan p
-- LEFT JOIN respons_evaluasi r ON p.id = r.pimpinan_id
-- GROUP BY p.id;

-- B. Query breakdown pencapaian skor per butir pertanyaan untuk Pimpinan tertentu
-- SELECT 
--     d.nomor_pertanyaan,
--     ROUND(AVG(d.skor), 2) AS rata_skor_pertanyaan
-- FROM respons_evaluasi r
-- JOIN detail_skor_pertanyaan d ON r.id = d.respons_id
-- WHERE r.pimpinan_id = 'L1' -- Ganti dengan ID Pimpinan target
-- GROUP BY d.nomor_pertanyaan
-- ORDER BY d.nomor_pertanyaan;