-- ============================================
-- DATABASE SISTEM KKN UMUS 2026
-- Jalankan di: cPanel > phpMyAdmin
-- Database: lppm_kkn2026
-- ============================================

SET NAMES utf8mb4;
SET time_zone = '+07:00';

-- Tabel Users (Admin, DPL, Mahasiswa)
CREATE TABLE IF NOT EXISTS `kkn_users` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `username`      VARCHAR(100) NOT NULL UNIQUE,
  `email`         VARCHAR(150) NOT NULL UNIQUE,
  `password`      VARCHAR(255) NOT NULL,
  `role`          ENUM('admin','dpl','mahasiswa') NOT NULL,
  `status`        ENUM('pending','active','nonactive') DEFAULT 'pending',
  `token`         VARCHAR(100) DEFAULT NULL,
  `token_expires` DATETIME DEFAULT NULL,
  `created_at`    DATETIME DEFAULT CURRENT_TIMESTAMP,
  `last_login`    DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel DPL
CREATE TABLE IF NOT EXISTS `kkn_dpl` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT DEFAULT NULL,
  `nama`       VARCHAR(150) NOT NULL,
  `nidn`       VARCHAR(20) NOT NULL UNIQUE,
  `email`      VARCHAR(150) NOT NULL,
  `kecamatan`  VARCHAR(100) NOT NULL,
  `kabupaten`  VARCHAR(100) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `kkn_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel Mahasiswa
CREATE TABLE IF NOT EXISTS `kkn_mahasiswa` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT DEFAULT NULL,
  `nama`        VARCHAR(150) NOT NULL,
  `nim`         VARCHAR(20) NOT NULL UNIQUE,
  `prodi`       VARCHAR(100) NOT NULL,
  `email`       VARCHAR(150) NOT NULL,
  `kecamatan`   VARCHAR(100) NOT NULL,
  `kabupaten`   VARCHAR(100) NOT NULL,
  `dpl_id`      INT DEFAULT NULL,
  `created_at`  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `kkn_users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`dpl_id`) REFERENCES `kkn_dpl`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel Logbook
CREATE TABLE IF NOT EXISTS `kkn_logbook` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `mahasiswa_id`    INT NOT NULL,
  `minggu`          TINYINT NOT NULL COMMENT '1-6',
  `kegiatan_no`     TINYINT NOT NULL COMMENT '1-7',
  `nama_kegiatan`   VARCHAR(255) DEFAULT NULL,
  `waktu`           VARCHAR(100) DEFAULT NULL,
  `deskripsi`       TEXT DEFAULT NULL,
  `bukti_link`      VARCHAR(500) DEFAULT NULL,
  `updated_at`      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_logbook` (`mahasiswa_id`, `minggu`, `kegiatan_no`),
  FOREIGN KEY (`mahasiswa_id`) REFERENCES `kkn_mahasiswa`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel Penilaian DPL
CREATE TABLE IF NOT EXISTS `kkn_penilaian` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `mahasiswa_id` INT NOT NULL,
  `dpl_id`       INT NOT NULL,
  -- K1: Kedisiplinan & Kehadiran (bobot 15%)
  `k1_1`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Persentase kehadiran',
  `k1_2`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Kelengkapan logbook',
  -- K2: Observasi & Perencanaan (bobot 20%)
  `k2_1`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Kelengkapan data observasi',
  `k2_2`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Jumlah masalah teridentifikasi',
  -- K3: Pelaksanaan Program (bobot 25%)
  `k3_1`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Realisasi peserta',
  `k3_2`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Kelengkapan dokumentasi',
  -- K4: Luaran (bobot 25%)
  `k4_1`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Artikel jurnal pengabdian',
  `k4_2`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Artikel media massa',
  `k4_3`         DECIMAL(5,2) DEFAULT 0   COMMENT 'Bonus: produk inovasi',
  `k4_4`         DECIMAL(5,2) DEFAULT 0   COMMENT 'Bonus: digitalisasi layanan',
  -- K5: Sikap & Kompetensi (bobot 15%)
  `k5_1`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Kemampuan komunikasi',
  `k5_2`         DECIMAL(5,2) DEFAULT NULL COMMENT 'Berpikir kritis & problem solving',
  -- Nilai akhir (otomatis)
  `nilai_k1`     DECIMAL(5,2) DEFAULT 0,
  `nilai_k2`     DECIMAL(5,2) DEFAULT 0,
  `nilai_k3`     DECIMAL(5,2) DEFAULT 0,
  `nilai_k4`     DECIMAL(5,2) DEFAULT 0,
  `nilai_k5`     DECIMAL(5,2) DEFAULT 0,
  `nilai_akhir`  DECIMAL(5,2) DEFAULT 0,
  `catatan`      TEXT DEFAULT NULL,
  `updated_at`   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_penilaian` (`mahasiswa_id`, `dpl_id`),
  FOREIGN KEY (`mahasiswa_id`) REFERENCES `kkn_mahasiswa`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`dpl_id`) REFERENCES `kkn_dpl`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- DATA MASTER DPL (dari file Excel)
-- ============================================
INSERT INTO `kkn_dpl` (`nama`, `nidn`, `email`, `kecamatan`, `kabupaten`) VALUES
('Abdul Bashar, S.TP., M.TP.',      '0614129102', '', 'Bantarbolang', 'Pemalang'),
('Agyztia Premana, M.Kom',          '0611039301', '', 'Moga',         'Pemalang'),
('Amelia Sholehah, S.Pd., M.M.',    '0630129202', '', 'Babakan',      'Cirebon'),
('Diah Ratnasari, S.Pd. M.Gizi',    '0617089201', '', 'Pagerbarang',  'Tegal'),
('Dr. Abdul Khamid, S.T., M.T.',    '0615077203', '', 'Warungpring',  'Pemalang'),
('Farhan Saefudin Wahid, M.Pd.',    '0609018802', '', 'Losari',       'Cirebon'),
('M. Erwin Dwi Listyanto, M.M.',    '0615059701', '', 'Mundu',        'Cirebon'),
('M. Syaifulloh, M.M.',             '0623108602', '', 'Jatinegara',   'Tegal'),
('Novi Yulianti, M.Pd.',            '0421079101', '', 'Larangan',     'Brebes'),
('Nur Afridah, M.M.',               '0612037309', '', 'Bulakamba',    'Brebes'),
('Robert Rizki Yono, M.Pd.',        '0627039103', '', 'Margasari',    'Tegal'),
('Suci Nur Utami, S.PT., M.Si.',    '0618078701', '', 'Brebes',       'Brebes');

-- Admin default
INSERT INTO `kkn_users` (`username`, `email`, `password`, `role`, `status`) VALUES
('admin_kkn', 'lppm@umus.ac.id', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');
-- Password default: kkn2026# (akan di-hash ulang saat install)