-- ============================================================
-- Rubis KTO Auth System — Complete Schema Setup
-- Idempotent: safe to run on any database (uses IF NOT EXISTS)
-- ============================================================

-- 1. Mail logs table (for tracking OTP email delivery)
CREATE TABLE IF NOT EXISTS `d_mail_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `recipient` VARCHAR(200) NOT NULL,
    `subject` VARCHAR(500) NOT NULL,
    `body_preview` VARCHAR(200) DEFAULT NULL,
    `status` ENUM('sent', 'failed', 'queued') NOT NULL DEFAULT 'queued',
    `error` TEXT,
    `sent_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Login attempts table (for rate limiting)
CREATE TABLE IF NOT EXISTS `d_login_attempts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(45) NOT NULL,
    `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `username_tried` VARCHAR(100) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    INDEX `idx_ip` (`ip_address`),
    INDEX `idx_attempted_at` (`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. OTP codes table — fix columns for password_hash support
ALTER TABLE `codes`
    MODIFY COLUMN `code` VARCHAR(255) NOT NULL,
    MODIFY COLUMN `expire` DATETIME NOT NULL;

-- Add attempts column if it doesn't exist (for OTP brute-force protection)
SET @dbname = (SELECT DATABASE());
SET @exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'codes' AND COLUMN_NAME = 'attempts');
SET @sql = IF(@exists = 0,
    'ALTER TABLE codes ADD COLUMN attempts TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER code',
    'SELECT "attempts column already exists" AS status');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Delete any stale records with incompatible format (int → datetime migration)
DELETE FROM `codes` WHERE `expire` NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}';