-- ============================================================================
-- SisPak Adiksi Media Sosial — Skema & Seed Database
-- Sumber desain: RENCANA_SISTEM.md §7 (9 tabel)
-- Engine: MySQL 8 (InnoDB, utf8mb4) — dev: Laragon, produksi: IDwebhost
--
-- Impor: mysql -u root -p < sql/database.sql   (atau via phpMyAdmin Import)
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Buat database bila belum ada (sesuaikan nama dengan DB_NAME di .env)
CREATE DATABASE IF NOT EXISTS sispak_adiksi DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sispak_adiksi;

DROP TABLE IF EXISTS jawaban;
DROP TABLE IF EXISTS hasil_identifikasi;
DROP TABLE IF EXISTS konsultasi;
DROP TABLE IF EXISTS bobot_cf;
DROP TABLE IF EXISTS pernyataan;
DROP TABLE IF EXISTS gejala;
DROP TABLE IF EXISTS setting_sistem;
DROP TABLE IF EXISTS siswa;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Akun admin / guru BK (juga dipakai untuk akun siswa dengan role='siswa')
CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama          VARCHAR(100)  NOT NULL,
  username      VARCHAR(50)   NOT NULL UNIQUE,
  password_hash VARCHAR(255)  NOT NULL,
  role          ENUM('admin','siswa') NOT NULL DEFAULT 'admin',
  last_login    DATETIME      NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Data siswa
CREATE TABLE siswa (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NULL,
  nis        VARCHAR(20)  NULL UNIQUE,
  nama       VARCHAR(100) NOT NULL,
  kelas      VARCHAR(10)  NOT NULL,
  jk         ENUM('L','P') NOT NULL,
  no_hp      VARCHAR(20)  NULL,
  email      VARCHAR(100) NULL,
  updated_at DATETIME     NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_siswa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Gejala (knowledge base)
CREATE TABLE gejala (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kode      VARCHAR(5)   NOT NULL UNIQUE,
  deskripsi VARCHAR(255) NOT NULL,
  is_active TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Pernyataan kuesioner (3 item per gejala)
CREATE TABLE pernyataan (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gejala_id INT UNSIGNED NOT NULL,
  kode      VARCHAR(5)   NOT NULL UNIQUE,
  teks      VARCHAR(255) NOT NULL,
  urutan    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1)   NOT NULL DEFAULT 1,
  CONSTRAINT fk_pernyataan_gejala FOREIGN KEY (gejala_id) REFERENCES gejala(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Bobot CF pakar (1 baris per gejala) — keputusan K-1/K-2
CREATE TABLE bobot_cf (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gejala_id   INT UNSIGNED NOT NULL UNIQUE,
  skala_pakar TINYINT UNSIGNED NOT NULL,          -- skala proposal: 1..4 (1 = Sangat Yakin)
  cf_nilai    DECIMAL(5,4) NOT NULL,              -- ternormalisasi 0..1 : (4 - skala)/3
  alasan      VARCHAR(255) NULL,
  updated_by  INT UNSIGNED NULL,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_bobot_gejala FOREIGN KEY (gejala_id) REFERENCES gejala(id) ON DELETE CASCADE,
  CONSTRAINT fk_bobot_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Sesi konsultasi
CREATE TABLE konsultasi (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  siswa_id     INT UNSIGNED NOT NULL,
  mulai_pada   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  selesai_pada DATETIME NULL,
  status       ENUM('draft','selesai','dibatalkan') NOT NULL DEFAULT 'draft',
  CONSTRAINT fk_konsultasi_siswa FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Jawaban per pernyataan
CREATE TABLE jawaban (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  konsultasi_id INT UNSIGNED NOT NULL,
  pernyataan_id INT UNSIGNED NOT NULL,
  nilai         TINYINT UNSIGNED NOT NULL,        -- 1..4
  cf_user       DECIMAL(5,4) NOT NULL,            -- (nilai - 1)/3 (keputusan K-1)
  UNIQUE KEY uq_jawaban (konsultasi_id, pernyataan_id),
  CONSTRAINT fk_jawaban_sesi FOREIGN KEY (konsultasi_id) REFERENCES konsultasi(id) ON DELETE CASCADE,
  CONSTRAINT fk_jawaban_pernyataan FOREIGN KEY (pernyataan_id) REFERENCES pernyataan(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Hasil identifikasi
CREATE TABLE hasil_identifikasi (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  konsultasi_id   INT UNSIGNED NOT NULL UNIQUE,
  siswa_id        INT UNSIGNED NOT NULL,
  cf_per_gejala   JSON NOT NULL,                  -- {"G01":0.3333, ...}
  cf_akhir        DECIMAL(5,4) NOT NULL,
  persentase      DECIMAL(5,2) NOT NULL,
  kategori        ENUM('rendah','sedang','tinggi') NOT NULL,
  saran           TEXT NULL,
  dihasilkan_pada DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_hasil_sesi FOREIGN KEY (konsultasi_id) REFERENCES konsultasi(id) ON DELETE CASCADE,
  CONSTRAINT fk_hasil_siswa FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  INDEX idx_siswa_tgl (siswa_id, dihasilkan_pada)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Pengaturan sistem (threshold K-4 + saran per kategori)
CREATE TABLE setting_sistem (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kunci      VARCHAR(50)  NOT NULL UNIQUE,
  nilai      VARCHAR(255) NOT NULL,
  keterangan VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Akun (GANTI password sebelum deploy produksi!)
INSERT INTO users (id, nama, username, password_hash, role) VALUES
(1, 'Guru BK Admin',  'admin',  '$2y$10$ZBWWwAG22QYsR7i0/o7ci.hfdBTqTr1qbGkP/5Vu7CO.XgBC2qoLq', 'admin'),
(2, 'Siswa Uji Coba', 'siswa01','$2y$10$KdrQPfw4u7sCc55MSRxT6umiONXc8ANIme/8tjM2tUdjoWF9CwhCu', 'siswa');

-- Siswa uji (Tahap 0 — untuk uji role; data riil menyusul Tahap 4)
INSERT INTO siswa (id, user_id, nis, nama, kelas, jk) VALUES
(1, 2, '20220304039', 'Siswa Uji Coba', 'XII A', 'L');

-- Gejala (sesuai proposal Tabel 3.3)
INSERT INTO gejala (id, kode, deskripsi, is_active) VALUES
(1, 'G01', 'Sulit mengontrol atau mengurangi penggunaan media sosial.', 1),
(2, 'G02', 'Merasa gelisah atau tidak nyaman ketika tidak dapat mengakses media sosial.', 1),
(3, 'G03', 'Menghabiskan waktu lebih lama di media sosial daripada yang direncanakan.', 1),
(4, 'G04', 'Mengabaikan tugas sekolah atau tanggung jawab karena penggunaan media sosial.', 1),
(5, 'G05', 'Mengurangi waktu istirahat atau tidur untuk menggunakan media sosial.', 1);

-- Pernyataan kuesioner P1..P15 (teks persis proposal Tabel 3.6)
INSERT INTO pernyataan (id, gejala_id, kode, teks, urutan, is_active) VALUES
(1, 1, 'P1',  'Saya sulit mengurangi waktu penggunaan media sosial.', 1, 1),
(2, 1, 'P2',  'Saya sering menggunakan media sosial lebih lama dari yang saya rencanakan.', 2, 1),
(3, 1, 'P3',  'Saya tetap membuka media sosial meskipun sudah berniat berhenti.', 3, 1),
(4, 2, 'P4',  'Saya merasa gelisah ketika tidak dapat membuka media sosial.', 1, 1),
(5, 2, 'P5',  'Saya sering memikirkan media sosial saat tidak menggunakannya.', 2, 1),
(6, 2, 'P6',  'Saya merasa tidak nyaman apabila tidak mengetahui informasi terbaru di media sosial.', 3, 1),
(7, 3, 'P7',  'Saya sering lupa waktu ketika menggunakan media sosial.', 1, 1),
(8, 3, 'P8',  'Saya menghabiskan waktu lebih lama di media sosial daripada yang saya rencanakan.', 2, 1),
(9, 3, 'P9',  'Saya sering menunda berhenti menggunakan media sosial.', 3, 1),
(10, 4, 'P10', 'Saya pernah menunda mengerjakan tugas sekolah karena menggunakan media sosial.', 1, 1),
(11, 4, 'P11', 'Saya lebih memilih membuka media sosial daripada menyelesaikan tugas sekolah.', 2, 1),
(12, 4, 'P12', 'Penggunaan media sosial menyebabkan tugas sekolah saya terlambat diselesaikan.', 3, 1),
(13, 5, 'P13', 'Saya sering menggunakan media sosial hingga larut malam.', 1, 1),
(14, 5, 'P14', 'Saya mengurangi waktu tidur karena menggunakan media sosial.', 2, 1),
(15, 5, 'P15', 'Saya tetap menggunakan media sosial meskipun sudah waktunya beristirahat.', 3, 1);

-- Bobot CF pakar — keputusan K-2: cf_nilai = (4 - skala_pakar)/3
INSERT INTO bobot_cf (id, gejala_id, skala_pakar, cf_nilai, alasan, updated_by) VALUES
(1, 1, 3, 0.3333, 'Kesulitan mengontrol penggunaan media sosial merupakan indikator utama adiksi.', 1),
(2, 2, 2, 0.6667, 'Rasa gelisah saat tidak dapat mengakses media sosial menunjukkan ketergantungan.', 1),
(3, 3, 2, 0.6667, 'Penggunaan melebihi waktu yang direncanakan merupakan tanda perilaku berlebihan.', 1),
(4, 4, 1, 1.0000, 'Mengabaikan tugas sekolah menunjukkan gangguan fungsi akademik (indikator sangat kuat).', 1),
(5, 5, 1, 1.0000, 'Berkurangnya waktu tidur menunjukkan dampak nyata terhadap kesehatan (indikator sangat kuat).', 1);

-- Pengaturan sistem — keputusan K-4
INSERT INTO setting_sistem (kunci, nilai, keterangan) VALUES
('threshold_rendah_max', '40',  'Batas atas kategori rendah (persen, tidak termasuk)'),
('threshold_sedang_max', '70',  'Batas atas kategori sedang (persen, termasuk)'),
('saran_rendah',  'Tingkat adiksi media sosial Anda tergolong RENDAH. Pertahankan pola penggunaan yang sehat: tetap perhatikan waktu layar, tidur, dan kegiatan belajar Anda.', 'Tampil di halaman hasil'),
('saran_sedang',  'Tingkat adiksi media sosial Anda tergolong SEDANG. Mulailah membatasi waktu penggunaan harian, atur jadwal bermain media sosial, dan sempatkan kegiatan di dunia nyata. Bila diperlukan, konsultasikan dengan guru BK.', 'Tampil di halaman hasil'),
('saran_tinggi',  'Tingkat adiksi media sosial Anda tergolong TINGGI. Segera kurangi intensitas penggunaan, libatkan orang tua dan guru BK untuk pendampingan, dan cari bantuan profesional bila gejala berlanjut.', 'Tampil di halaman hasil');