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

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(80) NOT NULL DEFAULT 'reporter',
  is_active TINYINT(1) DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX idx_users_role(role)
);

CREATE TABLE audit_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NULL,
  action VARCHAR(120) NOT NULL,
  table_name VARCHAR(120) NOT NULL,
  record_id BIGINT NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  ip_address VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_created(created_at),
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);