-- Buat database jika belum ada
CREATE DATABASE IF NOT EXISTS smpp_db;
USE smpp_db;

-- Hapus tabel jika sudah ada

-- Buat tabel claims
CREATE TABLE claims (
  id INT AUTO_INCREMENT PRIMARY KEY,
  claim_number VARCHAR(20) NOT NULL UNIQUE,
  user_id INT NOT NULL,
  jumlah_klaim DECIMAL(15, 2) NOT NULL,
  diagnosis TEXT NOT NULL,
  lokasi_berobat TEXT NOT NULL,
  status_claim ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  alasan_penolakan TEXT NULL,
  tanggal_klaim TIMESTAMP NOT NULL,
  verified_by VARCHAR(100) NULL,
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_claim_number (claim_number),
  INDEX idx_user_id (user_id),
  INDEX idx_status_claim (status_claim),
  INDEX idx_tanggal_klaim (tanggal_klaim)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel role_permissions untuk granular permission
CREATE TABLE IF NOT EXISTS role_permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('admin', 'petugas', 'karyawan') NOT NULL,
  permission VARCHAR(50) NOT NULL,
  allowed BOOLEAN NOT NULL DEFAULT 0,
  UNIQUE KEY (role, permission)
);