-- Migration 001: Core Authentication, Sessions, Auditing, and Rate Limiting
-- Run on MariaDB 10.6+ / MySQL 8.0+

CREATE TABLE IF NOT EXISTS `users` (
    `id` CHAR(36) NOT NULL COMMENT 'UUID v4',
    `username` VARCHAR(50) NOT NULL COMMENT 'Unique user login handle',
    `email` VARCHAR(191) NOT NULL COMMENT 'Notification and recovery email',
    `password_hash` VARCHAR(255) NOT NULL COMMENT 'Argon2id or Bcrypt hash',
    `totp_secret` VARCHAR(255) DEFAULT NULL COMMENT 'Libsodium encrypted TOTP secret',
    `totp_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `recovery_codes` TEXT DEFAULT NULL COMMENT 'JSON array of 10 SHA-256 hashed single-use codes',
    `role` ENUM('owner', 'admin', 'editor', 'author') NOT NULL DEFAULT 'owner',
    `status` ENUM('active', 'suspended', 'locked') NOT NULL DEFAULT 'active',
    `failed_login_count` INT NOT NULL DEFAULT 0,
    `locked_until` DATETIME DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `last_login_ip_hash` CHAR(64) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_users_username` (`username`),
    UNIQUE KEY `uk_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `passkeys` (
    `id` CHAR(36) NOT NULL COMMENT 'UUID v4',
    `user_id` CHAR(36) NOT NULL,
    `credential_id` VARCHAR(255) NOT NULL,
    `public_key` TEXT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `counter` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `aaguid` VARCHAR(64) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_used_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_passkeys_cred_id` (`credential_id`),
    KEY `idx_passkeys_user` (`user_id`),
    CONSTRAINT `fk_passkeys_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(128) NOT NULL COMMENT 'SHA-256 of session ID',
    `user_id` CHAR(36) NOT NULL,
    `ip_hash` CHAR(64) NOT NULL,
    `user_agent` VARCHAR(255) NOT NULL,
    `payload` MEDIUMTEXT NOT NULL COMMENT 'Encrypted session payload',
    `last_activity` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_sessions_user` (`user_id`),
    KEY `idx_sessions_activity` (`last_activity`),
    CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_hash` CHAR(64) NOT NULL,
    `username_attempted` VARCHAR(100) NOT NULL,
    `attempted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `is_successful` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_attempts_ip_time` (`ip_hash`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` CHAR(36) DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50) DEFAULT NULL,
    `entity_id` VARCHAR(64) DEFAULT NULL,
    `details_json` TEXT DEFAULT NULL,
    `ip_hash` CHAR(64) NOT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_action_time` (`action`, `created_at`),
    KEY `idx_audit_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rate_limits` (
    `bucket_key` VARCHAR(128) NOT NULL,
    `tokens` DOUBLE NOT NULL,
    `last_refilled_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`bucket_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
