-- 建立系統使用者表 (sys_user)
-- 專門用於記錄帳號密碼和登入相關資訊
-- 不影響現有 users 表的權限管理功能
-- Created: 2025-10-01

CREATE TABLE IF NOT EXISTS sys_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- 帳號資訊
    username VARCHAR(255) NOT NULL COMMENT '登入帳號',
    password_hash VARCHAR(512) COMMENT '密碼雜湊 (如果需要本地儲存)',
    email VARCHAR(255) NOT NULL COMMENT '電子郵件',
    display_name VARCHAR(255) COMMENT '顯示名稱',

    -- API 認證資訊
    api_user_id VARCHAR(255) COMMENT 'API 回傳的使用者 ID',
    api_access_token TEXT COMMENT 'API 回傳的 access_token',
    api_token_expires_at TIMESTAMP NULL COMMENT 'API Token 過期時間',

    -- 登入相關
    auth_method ENUM('API', 'LDAP') DEFAULT 'API' COMMENT '認證方式',
    last_login_at TIMESTAMP NULL COMMENT '最後登入時間',
    last_login_ip VARCHAR(45) COMMENT '最後登入 IP',
    login_count INT DEFAULT 0 COMMENT '登入次數',
    login_success_count INT DEFAULT 0 COMMENT '成功登入次數',
    login_fail_count INT DEFAULT 0 COMMENT '失敗登入次數',

    -- 帳號狀態
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否啟用',
    is_locked BOOLEAN DEFAULT FALSE COMMENT '是否鎖定',
    locked_until TIMESTAMP NULL COMMENT '鎖定至何時',

    -- 審計欄位
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',

    -- 索引
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_api_user_id (api_user_id),
    INDEX idx_auth_method (auth_method),
    INDEX idx_last_login (last_login_at),
    INDEX idx_active_users (is_active, is_locked),

    -- 約束
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系統使用者表 - 帳號密碼登入記錄';

-- 建立登入記錄表 (簡化版)
CREATE TABLE IF NOT EXISTS login_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- 基本資訊
    username VARCHAR(255) NOT NULL COMMENT '登入帳號',
    auth_method ENUM('API', 'LDAP') NOT NULL COMMENT '認證方式',

    -- 登入結果
    login_success BOOLEAN NOT NULL COMMENT '是否成功',
    error_message TEXT COMMENT '錯誤訊息（失敗時）',

    -- 環境資訊
    ip_address VARCHAR(45) COMMENT 'IP 地址',
    user_agent TEXT COMMENT '瀏覽器資訊',

    -- API 回應 (可選，用於除錯)
    api_response_summary JSON COMMENT 'API 回應摘要',

    -- 時間
    login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '登入時間',

    -- 索引
    INDEX idx_username (username),
    INDEX idx_auth_method (auth_method),
    INDEX idx_login_success (login_success),
    INDEX idx_login_at (login_at),
    INDEX idx_username_time (username, login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登入記錄表';

-- 清理舊的認證相關表（如果存在）
DROP TABLE IF EXISTS auth_records;
DROP TABLE IF EXISTS token_refresh_logs;
DROP TABLE IF EXISTS login_history;