-- Phase 2 — Authentication
-- Run against a database that already has Phase 1's schema.sql applied.
-- Adds: auth_tokens (remember-me), and per-user brute-force lockout tracking
-- on users (additive columns only — no existing data is touched).

SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE users
    ADD COLUMN failed_login_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER password_hash,
    ADD COLUMN locked_until DATETIME NULL AFTER failed_login_attempts;

CREATE TABLE IF NOT EXISTS auth_tokens (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     INT UNSIGNED NOT NULL,
    selector    CHAR(12) NOT NULL,
    token_hash  CHAR(64) NOT NULL,
    expires_at  DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_auth_tokens_selector (selector),
    KEY idx_auth_tokens_user (user_id),
    CONSTRAINT fk_auth_tokens_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
