-- Migration 001: Create users table
-- Eclyss Platform Phase 1

CREATE TABLE IF NOT EXISTS users (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uuid          CHAR(36)        NOT NULL,
    username      VARCHAR(32)     NOT NULL,
    email         VARCHAR(255)    NOT NULL,
    phone         VARCHAR(20)     NOT NULL,
    phone_hash    VARCHAR(64)     NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    display_name  VARCHAR(80)     NOT NULL,
    bio           TEXT            DEFAULT NULL,
    avatar_path   VARCHAR(500)    DEFAULT NULL,
    avatar_thumb  VARCHAR(500)    DEFAULT NULL,
    language      VARCHAR(10)     DEFAULT 'en',
    theme         ENUM('dark','light','system') DEFAULT 'dark',
    status        ENUM('active','suspended','deleted') DEFAULT 'active',
    last_seen     DATETIME        DEFAULT NULL,
    is_online     TINYINT(1)      DEFAULT 0,
    push_token    VARCHAR(500)    DEFAULT NULL,
    otp_secret    VARCHAR(32)     DEFAULT NULL,
    email_verified TINYINT(1)    DEFAULT 0,
    phone_verified TINYINT(1)    DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at    DATETIME        DEFAULT NULL,
    UNIQUE KEY uq_uuid (uuid),
    UNIQUE KEY uq_username (username),
    UNIQUE KEY uq_email (email),
    UNIQUE KEY uq_phone (phone),
    INDEX idx_phone_hash (phone_hash),
    INDEX idx_status (status),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;