CREATE DATABASE IF NOT EXISTS wl_samp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wl_samp;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(32) NOT NULL UNIQUE,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  avatar VARCHAR(255) DEFAULT 'assets/default-avatar.png',
  role ENUM('user','admin') DEFAULT 'user',
  is_verified TINYINT(1) DEFAULT 0,
  verify_code VARCHAR(8),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE whitelists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  samp_name VARCHAR(32) NOT NULL UNIQUE,
  status ENUM('pending','approved','rejected') DEFAULT 'approved',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE chats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE announcements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE settings (
  k VARCHAR(80) PRIMARY KEY,
  v TEXT
);

INSERT INTO settings (k,v) VALUES
('server_ip','127.0.0.1'),('server_port','7777'),('dana_number','08xxxxxxxxxx'),('qris_image','assets/qris.png')
ON DUPLICATE KEY UPDATE v=VALUES(v);