-- 全新的文件翻譯系統資料庫架構
-- 方案 A: dt_users 用於業務功能，sys_user 用於登入記錄
-- API name 格式: 姓名+email，email 作為主要識別鍵
-- Created: 2025-10-01

-- 1. 建立 dt_users 表 (業務功能使用)
CREATE TABLE dt_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL COMMENT 'API name (姓名+email格式)',
    display_name VARCHAR(255) NOT NULL COMMENT 'API name (姓名+email格式)',
    email VARCHAR(255) NOT NULL UNIQUE COMMENT '電子郵件 (主要識別鍵)',
    department VARCHAR(100) COMMENT '部門/職位',
    is_admin BOOLEAN DEFAULT FALSE COMMENT '是否為管理員',
    last_login DATETIME COMMENT '最後登入時間',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',

    INDEX idx_email (email),
    INDEX idx_username_email (username, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='使用者資訊表';

-- 2. 建立 dt_translation_jobs 表 (翻譯工作)
CREATE TABLE dt_translation_jobs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL COMMENT '關聯到 dt_users.id',
    job_name VARCHAR(255) NOT NULL COMMENT '工作名稱',
    source_lang VARCHAR(10) NOT NULL COMMENT '來源語言',
    target_lang VARCHAR(10) NOT NULL COMMENT '目標語言',
    file_type ENUM('DOCX', 'PPTX', 'PDF', 'TXT') NOT NULL COMMENT '檔案類型',
    status ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED') DEFAULT 'PENDING' COMMENT '工作狀態',
    progress INT DEFAULT 0 COMMENT '進度百分比',
    total_pages INT DEFAULT 0 COMMENT '總頁數',
    processed_pages INT DEFAULT 0 COMMENT '已處理頁數',
    cost DECIMAL(10,4) DEFAULT 0 COMMENT '翻譯成本',
    error_message TEXT COMMENT '錯誤訊息',
    conversation_id VARCHAR(255) COMMENT 'Dify 對話 ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    completed_at DATETIME COMMENT '完成時間',

    FOREIGN KEY (user_id) REFERENCES dt_users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='翻譯工作表';

-- 3. 建立 dt_job_files 表 (工作檔案)
CREATE TABLE dt_job_files (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT NOT NULL COMMENT '關聯到 dt_translation_jobs.id',
    file_type ENUM('source', 'translated') NOT NULL COMMENT '檔案類型',
    original_filename VARCHAR(255) NOT NULL COMMENT '原始檔名',
    stored_filename VARCHAR(255) NOT NULL COMMENT '儲存檔名',
    file_path VARCHAR(500) NOT NULL COMMENT '檔案路徑',
    file_size BIGINT DEFAULT 0 COMMENT '檔案大小',
    mime_type VARCHAR(100) COMMENT 'MIME 類型',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',

    FOREIGN KEY (job_id) REFERENCES dt_translation_jobs(id) ON DELETE CASCADE,
    INDEX idx_job_id (job_id),
    INDEX idx_file_type (file_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工作檔案表';

-- 4. 建立 dt_translation_cache 表 (翻譯快取)
CREATE TABLE dt_translation_cache (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    content_hash VARCHAR(64) NOT NULL COMMENT '內容雜湊',
    source_lang VARCHAR(10) NOT NULL COMMENT '來源語言',
    target_lang VARCHAR(10) NOT NULL COMMENT '目標語言',
    source_text TEXT NOT NULL COMMENT '來源文字',
    translated_text TEXT NOT NULL COMMENT '翻譯文字',
    quality_score DECIMAL(3,2) DEFAULT 0.00 COMMENT '品質分數',
    hit_count INT DEFAULT 0 COMMENT '命中次數',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    last_used_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最後使用時間',

    UNIQUE KEY uk_content_lang (content_hash, source_lang, target_lang),
    INDEX idx_last_used (last_used_at),
    INDEX idx_hit_count (hit_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='翻譯快取表';

-- 5. 建立 dt_api_usage_stats 表 (API 使用統計)
CREATE TABLE dt_api_usage_stats (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL COMMENT '關聯到 dt_users.id',
    job_id BIGINT COMMENT '關聯到 dt_translation_jobs.id',
    api_name VARCHAR(50) NOT NULL COMMENT 'API 名稱',
    request_count INT DEFAULT 1 COMMENT '請求次數',
    token_used INT DEFAULT 0 COMMENT '使用的 token 數',
    cost DECIMAL(10,4) DEFAULT 0 COMMENT '成本',
    response_time_ms INT DEFAULT 0 COMMENT '回應時間(毫秒)',
    status ENUM('SUCCESS', 'FAILED', 'TIMEOUT') DEFAULT 'SUCCESS' COMMENT '狀態',
    error_message TEXT COMMENT '錯誤訊息',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    date_key DATE GENERATED ALWAYS AS (DATE(created_at)) STORED COMMENT '日期鍵',

    FOREIGN KEY (user_id) REFERENCES dt_users(id) ON DELETE CASCADE,
    FOREIGN KEY (job_id) REFERENCES dt_translation_jobs(id) ON DELETE SET NULL,
    INDEX idx_user_date (user_id, date_key),
    INDEX idx_api_name (api_name),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API 使用統計表';

-- 6. 建立 dt_system_logs 表 (系統日誌)
CREATE TABLE dt_system_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL') NOT NULL COMMENT '日誌級別',
    category VARCHAR(50) NOT NULL COMMENT '日誌分類',
    message TEXT NOT NULL COMMENT '日誌訊息',
    user_id INT COMMENT '關聯到 dt_users.id',
    job_id BIGINT COMMENT '關聯到 dt_translation_jobs.id',
    extra_data JSON COMMENT '額外資料',
    ip_address VARCHAR(45) COMMENT 'IP 地址',
    user_agent TEXT COMMENT '用戶代理',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    date_key DATE GENERATED ALWAYS AS (DATE(created_at)) STORED COMMENT '日期鍵',

    FOREIGN KEY (user_id) REFERENCES dt_users(id) ON DELETE SET NULL,
    FOREIGN KEY (job_id) REFERENCES dt_translation_jobs(id) ON DELETE SET NULL,
    INDEX idx_level_category (level, category),
    INDEX idx_user_date (user_id, date_key),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系統日誌表';

-- 7. 建立 dt_notifications 表 (通知)
CREATE TABLE dt_notifications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL COMMENT '關聯到 dt_users.id',
    type ENUM('INFO', 'SUCCESS', 'WARNING', 'ERROR') NOT NULL COMMENT '通知類型',
    title VARCHAR(255) NOT NULL COMMENT '通知標題',
    message TEXT NOT NULL COMMENT '通知內容',
    is_read BOOLEAN DEFAULT FALSE COMMENT '是否已讀',
    data JSON COMMENT '額外資料',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    read_at DATETIME COMMENT '已讀時間',

    FOREIGN KEY (user_id) REFERENCES dt_users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, is_read),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通知表';

-- 8. 保持現有的 sys_user 表 (專門用於登入記錄)
-- sys_user 表已存在，透過 email 與 dt_users 關聯

-- 9. 重新命名 login_logs 為 dt_login_logs
RENAME TABLE login_logs TO dt_login_logs;

-- 10. 為 dt_login_logs 添加與 dt_users 的關聯
ALTER TABLE dt_login_logs
ADD COLUMN user_id INT COMMENT '關聯到 dt_users.id',
ADD INDEX idx_user_id (user_id),
ADD FOREIGN KEY fk_dt_login_logs_user_id (user_id) REFERENCES dt_users(id) ON DELETE SET NULL;

-- 11. 插入預設管理員使用者
INSERT INTO dt_users (username, display_name, email, department, is_admin)
VALUES ('ymirliu ymirliu@panjit.com.tw', 'ymirliu ymirliu@panjit.com.tw', 'ymirliu@panjit.com.tw', 'IT', TRUE);

-- 12. 驗證架構建立
SELECT 'Tables created:' as status;
SHOW TABLES LIKE 'dt_%';