-- database.sql
CREATE DATABASE IF NOT EXISTS jobportal CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE jobportal;

-- tabel users (sederhana)
CREATE TABLE IF NOT EXISTS users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  unique_id VARCHAR(64) DEFAULT NULL,
  status_online ENUM('off','on') DEFAULT 'off',
  tgl_login DATETIME NULL,
  tgl_logout DATE NULL,
  jam_logout TIME NULL,
  last_activity DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- tabel auth_tokens untuk remember-me
CREATE TABLE IF NOT EXISTS auth_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  selector CHAR(16) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  INDEX (selector)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- contoh user (password = 'password123' hashed menggunakan password_hash())
-- Silakan gunakan PHP untuk membuat password_hash sebelum memasukkan user ini.