CREATE DATABASE IF NOT EXISTS spk_polisi;
USE spk_polisi;

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
);

