-- Migration 003: Create login_attempts table
-- Requirements: 18.5
-- Design: Data Models -> 2. LOGIN ATTEMPTS

CREATE TABLE IF NOT EXISTS login_attempts (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username        VARCHAR(64)  NOT NULL,
    ip_address      VARCHAR(45)  NOT NULL,
    succeeded       TINYINT(1)   NOT NULL,
    attempted_at    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    KEY idx_login_username_time (username, attempted_at),
    KEY idx_login_cleanup (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;