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

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','direktur') NOT NULL DEFAULT 'admin',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE letters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  letter_number VARCHAR(100) NULL UNIQUE,
  subject VARCHAR(255) NOT NULL,
  recipient VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  letter_date DATE NOT NULL,
  signature_method ENUM('electronic','wet_ink') NOT NULL DEFAULT 'electronic',
  status ENUM('draft','pending_approval','approved','pending_signature','final_electronic','final_wet_ink','cancelled','superseded') NOT NULL DEFAULT 'draft',
  public_token CHAR(64) NOT NULL UNIQUE,
  content_sha256 CHAR(64) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  signed_at DATETIME NULL,
  final_file VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_letters_creator FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_letters_approver FOREIGN KEY (approved_by) REFERENCES users(id),
  INDEX idx_letters_status(status), INDEX idx_letters_date(letter_date)
) ENGINE=InnoDB;

CREATE TABLE letter_sequences (
  sequence_year SMALLINT UNSIGNED PRIMARY KEY,
  last_number INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  letter_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  details JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_letter FOREIGN KEY (letter_id) REFERENCES letters(id) ON DELETE SET NULL,
  INDEX idx_audit_letter(letter_id)
) ENGINE=InnoDB;