-- Pacific Logistics · slice 00 · Login rate-limit tracking
-- Records each authentication attempt so LoginController can lock a
-- (ip, username) pair after N failed attempts inside a rolling window.
-- Retention: purge rows older than 30 days via cron or manual cleanup.

CREATE TABLE IF NOT EXISTS login_attempts (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ip           VARCHAR(45)  NOT NULL,
    username     VARCHAR(100) NOT NULL DEFAULT '',
    attempt_at   DATETIME     NOT NULL,
    success      TINYINT(1)   NOT NULL DEFAULT 0,
    user_agent   VARCHAR(255) NULL,
    PRIMARY KEY (id),
    KEY idx_ip_time         (ip, attempt_at),
    KEY idx_username_time   (username, attempt_at),
    KEY idx_attempt_at      (attempt_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
