-- Migration 012: admin account settings and durable sign-in.
-- Adds profile fields (nickname, avatar) to admins, a remember-me token table
-- (selector + hashed validator, so a leaked DB row cannot forge the cookie),
-- and single-use password-reset tokens. MySQL syntax (not MariaDB).

ALTER TABLE admins
    ADD COLUMN nickname VARCHAR(60) NULL AFTER full_name,
    ADD COLUMN avatar_path VARCHAR(255) NULL AFTER nickname;

CREATE TABLE admin_remember_tokens (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_id       INT UNSIGNED NOT NULL,
    selector       CHAR(24) NOT NULL,
    validator_hash CHAR(64) NOT NULL,
    expires_at     DATETIME NOT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_remember_selector (selector),
    KEY idx_remember_admin (admin_id),
    KEY idx_remember_expires (expires_at),
    CONSTRAINT fk_remember_admin FOREIGN KEY (admin_id)
        REFERENCES admins (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_password_resets (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_id   INT UNSIGNED NOT NULL,
    selector   CHAR(24) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used       TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_reset_selector (selector),
    KEY idx_reset_admin (admin_id),
    KEY idx_reset_expires (expires_at),
    CONSTRAINT fk_reset_admin FOREIGN KEY (admin_id)
        REFERENCES admins (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;