-- audit3-followup (2026-05-29): MySQL-backed brute-force counter.
-- Replaces the per-process in-memory Maps in
-- src/lib/auth/{login-attempts,twofa-attempts}.ts.
--
-- Schema rationale: see prisma/schema.prisma model `BruteForceCounter`.
-- Additive change (new table). Zero-downtime.

CREATE TABLE `BruteForceCounter` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `bucket` VARCHAR(32) NOT NULL,
    `bucketKey` VARCHAR(255) NOT NULL,
    `failures` JSON NOT NULL,
    `lockedUntil` DATETIME(3) NULL,
    `windowMs` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `BruteForceCounter_bucket_bucketKey_key` (`bucket`, `bucketKey`),
    INDEX `BruteForceCounter_lockedUntil_idx` (`lockedUntil`),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;