-- Migration 002: brute-force tracking for admin logins.
-- MySQL syntax (not MariaDB). Rows older than a day are pruned by the app.

CREATE TABLE IF NOT EXISTS admin_login_attempts (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ip_address   VARCHAR(45) NOT NULL,
    identifier   VARCHAR(190) NOT NULL,
    success      TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attempts_ip (ip_address, attempted_at),
    KEY idx_attempts_identifier (identifier, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;