-- Login security tables and reporting queries.
-- Supports lockout after more than 3 failed attempts over 12 hours.

CREATE TABLE IF NOT EXISTS auth_login_attempts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username_input VARCHAR(100) NOT NULL,
    username_normalized VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    is_success TINYINT(1) NOT NULL DEFAULT 0,
    failure_reason VARCHAR(100) NOT NULL DEFAULT '',
    locked_out TINYINT(1) NOT NULL DEFAULT 0,
    user_agent VARCHAR(1024) NOT NULL DEFAULT '',
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_attempted_at (attempted_at),
    INDEX idx_username_ip_time (username_normalized, ip_address, attempted_at),
    INDEX idx_success (is_success)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_login_lockouts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username_normalized VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    lockout_until DATETIME NOT NULL,
    failed_count_at_lock INT UNSIGNED NOT NULL DEFAULT 0,
    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 uniq_username_ip (username_normalized, ip_address),
    INDEX idx_lockout_until (lockout_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Report: successful vs failed attempts in the last 30 days by day.
-- SELECT DATE(attempted_at) AS day,
--        SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) AS successful_attempts,
--        SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END) AS failed_attempts
-- FROM auth_login_attempts
-- WHERE attempted_at >= (NOW() - INTERVAL 30 DAY)
-- GROUP BY DATE(attempted_at)
-- ORDER BY day DESC;

-- Report: per-user summary in the last 30 days.
-- SELECT username_normalized,
--        SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) AS successful_attempts,
--        SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END) AS failed_attempts,
--        MAX(attempted_at) AS last_attempt_at
-- FROM auth_login_attempts
-- WHERE attempted_at >= (NOW() - INTERVAL 30 DAY)
-- GROUP BY username_normalized
-- ORDER BY failed_attempts DESC, successful_attempts DESC;

-- Report: currently active lockouts.
-- SELECT username_normalized, ip_address, lockout_until, failed_count_at_lock
-- FROM auth_login_lockouts
-- WHERE lockout_until > NOW()
-- ORDER BY lockout_until DESC;