-- =============================================================================
-- Migration 000: Create Users Table
-- Module 0 — Authentication
-- Run BEFORE migrations 001–012
-- =============================================================================

CREATE TABLE IF NOT EXISTS `users` (
    `id`                       INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`                     VARCHAR(150)    NOT NULL,
    `email`                    VARCHAR(255)    NOT NULL,
    `password`                 VARCHAR(255)    NOT NULL COMMENT 'Argon2id hash',
    `email_verified`           TINYINT(1)      NOT NULL DEFAULT 0,
    `remember_token`           VARCHAR(100)    NULL     COMMENT 'SHA-256 hash of remember-me cookie value',
    `remember_token_expires_at` DATETIME       NULL,
    `failed_login_attempts`    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `locked_until`             DATETIME        NULL     COMMENT 'Account locked until this datetime',
    `last_login_at`            DATETIME        NULL,
    `last_login_ip`            VARCHAR(45)     NULL     COMMENT 'IPv4 or IPv6',
    `created_at`               DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`               DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`               DATETIME        NULL     COMMENT 'Soft delete',

    PRIMARY KEY (`id`),
    UNIQUE  INDEX `uq_users_email`          (`email`),
    INDEX         `idx_users_remember_token` (`remember_token`),
    INDEX         `idx_users_deleted_at`     (`deleted_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Registered users — Module 0 Authentication';

-- =============================================================================
-- Migration 000b: Create Verification Tokens Table
-- =============================================================================

CREATE TABLE IF NOT EXISTS `verification_tokens` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED NOT NULL,
    `token_hash`  VARCHAR(64)  NOT NULL COMMENT 'SHA-256 hex of the raw token',
    `expires_at`  DATETIME     NOT NULL,
    `used`        TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_vt_user_id`    (`user_id`),
    INDEX `idx_vt_token_hash` (`token_hash`),
    INDEX `idx_vt_expires_at` (`expires_at`),

    CONSTRAINT `fk_vt_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Email verification tokens — Module 0 Authentication';

-- =============================================================================
-- Migration 000c: Create Password Resets Table
-- =============================================================================

CREATE TABLE IF NOT EXISTS `password_resets` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED NOT NULL,
    `email`       VARCHAR(255) NOT NULL,
    `token_hash`  VARCHAR(64)  NOT NULL COMMENT 'SHA-256 hex of the raw token',
    `expires_at`  DATETIME     NOT NULL,
    `used`        TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_pr_email`      (`email`),
    INDEX `idx_pr_token_hash` (`token_hash`),
    INDEX `idx_pr_expires_at` (`expires_at`),

    CONSTRAINT `fk_pr_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Password reset tokens — Module 0 Authentication';

-- =============================================================================
-- Migration 000d: Rate Limiting Table
-- =============================================================================

CREATE TABLE IF NOT EXISTS `rate_limit_attempts` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `key_hash`    VARCHAR(64)     NOT NULL COMMENT 'SHA-256 of IP or email',
    `action`      VARCHAR(50)     NOT NULL COMMENT 'login | register | reset',
    `attempted_at` DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_rl_key_action`   (`key_hash`, `action`),
    INDEX `idx_rl_attempted_at` (`attempted_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Brute-force rate limiting attempts — Module 0 Authentication';