-- Database: fortvault (atau nama database di Laragon / cPanel)
CREATE DATABASE IF NOT EXISTS `fortvault` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `fortvault`;

-- Table: users (Hanya menyimpan metadata otentikasi Zero-Knowledge)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `auth_hash` TEXT NOT NULL,
  `salt` TEXT NOT NULL,
  `totp_secret` TEXT NULL,
  `totp_enabled` TINYINT(1) DEFAULT 0,
  `last_totp_step` BIGINT NULL,
  `auth_version` TINYINT NOT NULL DEFAULT 2,
  `created_at` BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: login_attempts (Rate limiting anti-brute-force)
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `email` VARCHAR(191) PRIMARY KEY,
  `failed_count` INT NOT NULL DEFAULT 0,
  `locked_until` BIGINT NULL,
  `last_attempt_at` BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: sessions (User login session token; token disimpan sebagai SHA-256 hash)
CREATE TABLE IF NOT EXISTS `sessions` (
  `token` VARCHAR(191) PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `expires` BIGINT NOT NULL,
  `scope` VARCHAR(24) NOT NULL DEFAULT 'full',
  `device_hash` VARCHAR(64) NULL,
  `created_at` BIGINT NULL,
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: app_config (Server secret & konfigurasi internal)
CREATE TABLE IF NOT EXISTS `app_config` (
  `k` VARCHAR(64) PRIMARY KEY,
  `v` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: vault_items (Data brankas terenkripsi AES-GCM 256)
CREATE TABLE IF NOT EXISTS `vault_items` (
  `id` VARCHAR(191) PRIMARY KEY,
  `user_id` VARCHAR(191) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `folder` VARCHAR(100) NULL,
  `favorite` TINYINT(1) DEFAULT 0,
  `trashed` TINYINT(1) DEFAULT 0,
  `trashed_at` BIGINT NULL,
  `encrypted_data` LONGTEXT NOT NULL,
  `iv` VARCHAR(100) NOT NULL,
  `created_at` BIGINT NOT NULL,
  `updated_at` BIGINT NOT NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_category` (`category`),
  INDEX `idx_trashed` (`trashed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: recovery_keys (Emergency Kit recovery payload)
CREATE TABLE IF NOT EXISTS `recovery_keys` (
  `email` VARCHAR(191) PRIMARY KEY,
  `recovery_hash` TEXT NOT NULL,
  `wrapped_key` LONGTEXT NOT NULL,
  `wrap_iv` VARCHAR(100) NOT NULL,
  `wrap_salt` TEXT NOT NULL,
  `created_at` BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: folders (Folder kustom dinamis)
CREATE TABLE IF NOT EXISTS `folders` (
  `id` VARCHAR(191) PRIMARY KEY,
  `user_id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `created_at` BIGINT NOT NULL,
  INDEX `idx_folder_user` (`user_id`),
  UNIQUE KEY `uniq_user_folder_name` (`user_id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: one_time_secrets (Burn After Reading / Secret Sharing)
CREATE TABLE IF NOT EXISTS `one_time_secrets` (
  `id` VARCHAR(191) PRIMARY KEY,
  `creator_email` VARCHAR(191) NULL,
  `encrypted_data` LONGTEXT NOT NULL,
  `iv` VARCHAR(100) NOT NULL,
  `max_views` INT NOT NULL DEFAULT 1,
  `view_count` INT NOT NULL DEFAULT 0,
  `expires_at` BIGINT NOT NULL,
  `created_at` BIGINT NOT NULL,
  INDEX `idx_secret_expires` (`expires_at`),
  INDEX `idx_secret_creator` (`creator_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pending_users (Email verification — akun menunggu aktivasi)
CREATE TABLE IF NOT EXISTS `pending_users` (
  `email` VARCHAR(191) PRIMARY KEY,
  `auth_hash` TEXT NOT NULL,
  `auth_version` TINYINT NOT NULL DEFAULT 2,
  `salt` TEXT NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `otp_hash` VARCHAR(64) NULL,
  `failed_attempts` INT NOT NULL DEFAULT 0,
  `created_at` BIGINT NOT NULL,
  `expires_at` BIGINT NOT NULL,
  INDEX `idx_pending_token` (`token_hash`),
  INDEX `idx_pending_otp` (`otp_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: known_devices (Login alert fingerprint)
CREATE TABLE IF NOT EXISTS `known_devices` (
  `device_hash` VARCHAR(64) PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `ip` VARCHAR(64) NOT NULL,
  `user_agent` VARCHAR(255) NOT NULL,
  `first_seen` BIGINT NOT NULL,
  `last_seen` BIGINT NOT NULL,
  INDEX `idx_known_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: emergency_contacts (Protokol Kontak Darurat / Pewarisan Digital)
CREATE TABLE IF NOT EXISTS `emergency_contacts` (
  `id` VARCHAR(191) PRIMARY KEY,
  `owner_email` VARCHAR(191) NOT NULL,
  `grantee_email` VARCHAR(191) NOT NULL,
  `waiting_days` INT NOT NULL DEFAULT 30,
  `wrapped_key` LONGTEXT NOT NULL,
  `wrap_iv` VARCHAR(100) NOT NULL,
  `wrap_salt` TEXT NOT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'active',
  `requested_at` BIGINT NULL,
  `created_at` BIGINT NOT NULL,
  `updated_at` BIGINT NOT NULL,
  INDEX `idx_ec_owner` (`owner_email`),
  INDEX `idx_ec_grantee` (`grantee_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: admin_audit_log (Catatan audit aktivitas admin)
CREATE TABLE IF NOT EXISTS `admin_audit_log` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `admin_email` VARCHAR(191) NOT NULL,
  `action` VARCHAR(64) NOT NULL,
  `target` VARCHAR(191) NULL,
  `detail` TEXT NULL,
  `created_at` BIGINT NOT NULL,
  INDEX `idx_audit_admin` (`admin_email`),
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;