-- Migration 001: Create users table
-- Requirements: 1.1, 3.1, 3.4
-- Design: Data Models -> 1. USERS

CREATE TABLE IF NOT EXISTS users (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username        VARCHAR(64)  NOT NULL,
    email           VARCHAR(255) NULL,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('ADMIN','OPERATOR') NOT NULL DEFAULT 'OPERATOR',
    is_active       TINYINT(1)   NOT NULL DEFAULT 1,
    last_login_at   DATETIME(3)  NULL,
    created_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at      DATETIME(3)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_users_username (username),
    KEY idx_users_role_active (role, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;