-- QCIM — User Access Module schema
-- MySQL 8.x / utf8mb4. Run once against the QCIM database.

CREATE TABLE IF NOT EXISTS users (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username        VARCHAR(64)     NOT NULL,
    full_name       VARCHAR(120)    NOT NULL DEFAULT '',
    email           VARCHAR(190)    NULL,
    password_hash   VARCHAR(255)    NOT NULL,
    role            ENUM('admin','manager','operator','viewer') NOT NULL DEFAULT 'operator',
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    force_password_change TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at   DATETIME        NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Throttling / lockout log. Keyed by both username and IP so neither
-- a username guess nor a single noisy IP can be used freely.
CREATE TABLE IF NOT EXISTS login_attempts (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username     VARCHAR(64)     NOT NULL,
    ip_address   VARCHAR(45)     NOT NULL,
    success      TINYINT(1)      NOT NULL DEFAULT 0,
    attempted_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attempts_username (username, attempted_at),
    KEY idx_attempts_ip (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed admin. Password is "ChangeMe123!" — change it on first login.
-- (bcrypt hash; PHP password_verify() accepts the $2b$ prefix.)
INSERT INTO users (username, full_name, email, password_hash, role, is_active, force_password_change)
VALUES (
    'admin',
    'QCIM Administrator',
    'vicentesandovals@gmail.com',
    '$2b$12$2op/8PpQBRvLBUxbkNbVyOXSA3nQMUq/fkQxrPW3Qs8eiO5TrE/j2',
    'admin',
    1,
    1
)
ON DUPLICATE KEY UPDATE username = username;