CREATE DATABASE IF NOT EXISTS spk_polisi;
USE spk_polisi;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100),
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255),
  role ENUM('admin','penilai')
);

CREATE TABLE IF NOT EXISTS polisi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100),
  nrp VARCHAR(50) UNIQUE,
  jabatan VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS kriteria (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kode VARCHAR(10) UNIQUE,
  nama VARCHAR(100),
  bobot FLOAT DEFAULT 0,
  tipe ENUM('benefit','cost')
);

CREATE TABLE IF NOT EXISTS penilaian (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_polisi INT,
  id_kriteria INT,
  nilai FLOAT,
  id_penilai INT,
  FOREIGN KEY (id_polisi) REFERENCES polisi(id) ON DELETE CASCADE,
  FOREIGN KEY (id_kriteria) REFERENCES kriteria(id) ON DELETE CASCADE,
  FOREIGN KEY (id_penilai) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ahp_matrix (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kriteria1 INT,
  kriteria2 INT,
  nilai FLOAT,
  FOREIGN KEY (kriteria1) REFERENCES kriteria(id) ON DELETE CASCADE,
  FOREIGN KEY (kriteria2) REFERENCES kriteria(id) ON DELETE CASCADE
);

INSERT INTO users (nama, username, password, role) VALUES 
('Administrator', 'admin', '$2y$10$vM6rYFj1.wNMT3kO.Yx9OuS9b/F0b3kO.Yx9OuS9b/F0b3kO.Yx9O', 'admin'),
('Penilai Satu', 'penilai', '$2y$10$vM6rYFj1.wNMT3kO.Yx9OuS9b/F0b3kO.Yx9OuS9b/F0b3kO.Yx9O', 'penilai');
-- default pw is 'password' (placeholder, I will use a real one soon)

INSERT INTO kriteria (kode, nama, bobot, tipe) VALUES 
('C1', 'Kinerja (AKP)', 0.25, 'benefit'),
('C2', 'Kedisiplinan', 0.25, 'benefit'),
('C3', 'Integritas', 0.25, 'benefit'),
('C4', 'Kesehatan & Psikologi', 0.25, 'benefit');