-- Database schema for SiDea
CREATE TABLE IF NOT EXISTS admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS warga (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nik VARCHAR(20) UNIQUE,
  nama_lengkap VARCHAR(100),
  jenis_kelamin ENUM('L','P'),
  alamat TEXT,
  no_hp VARCHAR(20),
  email VARCHAR(50),
  password VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS surat (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_warga INT,
  jenis_surat VARCHAR(100),
  tempat_tanggal_lahir VARCHAR(100),
  jenis_file VARCHAR(50),
  status ENUM('Pending','Diproses','Selesai') DEFAULT 'Pending',
  tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_warga) REFERENCES warga(id)
);
CREATE TABLE IF NOT EXISTS dokumen (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_surat INT,
  file_ktp VARCHAR(255),
  file_selfie VARCHAR(255),
  FOREIGN KEY (id_surat) REFERENCES surat(id)
);
CREATE TABLE IF NOT EXISTS berita (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(200),
  ringkas TEXT,
  tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- default admin user: admin / admin123 (please change!)
INSERT INTO admin (username, password) VALUES
('admin', '$2y$10$3lX3tCnz4gZ9s8Qv4NQv5uC5o6OIk8F8oO9cZq6zV9G8hM5o3xTzK') -- hash for 'admin123'
ON DUPLICATE KEY UPDATE username=username;

CREATE TABLE IF NOT EXISTS galeri (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(200),
  file VARCHAR(255),
  tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);