-- ============================================================================
-- 自動生成的資料表 Schema
-- 生成時間: 2025-10-01 14:49:58
-- 警告: 此檔案由 generate_schema_from_models.py 自動生成
--       請勿手動編輯！
-- ============================================================================

USE db_A060;

-- User
DROP TABLE IF EXISTS `dt_users`;
CREATE TABLE IF NOT EXISTS `dt_users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(100) NOT NULL COMMENT 'AD帳號',
    `display_name` VARCHAR(200) NOT NULL COMMENT '顯示名稱',
    `email` VARCHAR(255) NOT NULL COMMENT '電子郵件',
    `department` VARCHAR(100) COMMENT '部門',
    `is_admin` BOOL DEFAULT 0 COMMENT '是否為管理員',
    `last_login` DATETIME COMMENT '最後登入時間',
    `created_at` DATETIME COMMENT '建立時間',
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    INDEX `ix_dt_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SysUser
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE IF NOT EXISTS `sys_user` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL COMMENT '登入帳號',
    `password_hash` VARCHAR(512) COMMENT '密碼雜湊 (如果需要本地儲存)',
    `email` VARCHAR(255) NOT NULL COMMENT '電子郵件',
    `display_name` VARCHAR(255) COMMENT '顯示名稱',
    `api_user_id` VARCHAR(255) COMMENT 'API 回傳的使用者 ID',
    `api_access_token` TEXT COMMENT 'API 回傳的 access_token',
    `api_token_expires_at` DATETIME COMMENT 'API Token 過期時間',
    `auth_method` ENUM('API','LDAP') DEFAULT 'API' COMMENT '認證方式',
    `last_login_at` DATETIME COMMENT '最後登入時間',
    `last_login_ip` VARCHAR(45) COMMENT '最後登入 IP',
    `login_count` INTEGER DEFAULT 0 COMMENT '登入次數',
    `login_success_count` INTEGER DEFAULT 0 COMMENT '成功登入次數',
    `login_fail_count` INTEGER DEFAULT 0 COMMENT '失敗登入次數',
    `is_active` BOOL DEFAULT 1 COMMENT '是否啟用',
    `is_locked` BOOL DEFAULT 0 COMMENT '是否鎖定',
    `locked_until` DATETIME COMMENT '鎖定至何時',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- LoginLog
DROP TABLE IF EXISTS `login_logs`;
CREATE TABLE IF NOT EXISTS `login_logs` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL COMMENT '登入帳號',
    `auth_method` ENUM('API','LDAP') NOT NULL COMMENT '認證方式',
    `login_success` BOOL NOT NULL COMMENT '是否成功',
    `error_message` TEXT COMMENT '錯誤訊息（失敗時）',
    `ip_address` VARCHAR(45) COMMENT 'IP 地址',
    `user_agent` TEXT COMMENT '瀏覽器資訊',
    `api_response_summary` JSON COMMENT 'API 回應摘要',
    `login_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '登入時間',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TranslationJob
DROP TABLE IF EXISTS `dt_translation_jobs`;
CREATE TABLE IF NOT EXISTS `dt_translation_jobs` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `job_uuid` VARCHAR(36) NOT NULL COMMENT '任務唯一識別碼',
    `user_id` INTEGER NOT NULL COMMENT '使用者ID',
    `original_filename` VARCHAR(500) NOT NULL COMMENT '原始檔名',
    `file_extension` VARCHAR(10) NOT NULL COMMENT '檔案副檔名',
    `file_size` BIGINT NOT NULL COMMENT '檔案大小(bytes)',
    `file_path` VARCHAR(1000) NOT NULL COMMENT '檔案路徑',
    `source_language` VARCHAR(50) COMMENT '來源語言',
    `target_languages` JSON NOT NULL COMMENT '目標語言陣列',
    `status` ENUM('PENDING','PROCESSING','COMPLETED','FAILED','RETRY') DEFAULT 'PENDING' COMMENT '任務狀態',
    `progress` NUMERIC(5, 2) DEFAULT 0.0 COMMENT '處理進度(%)',
    `retry_count` INTEGER DEFAULT 0 COMMENT '重試次數',
    `error_message` TEXT COMMENT '錯誤訊息',
    `total_tokens` INTEGER DEFAULT 0 COMMENT '總token數',
    `total_cost` NUMERIC(10, 4) DEFAULT 0.0 COMMENT '總成本',
    `conversation_id` VARCHAR(100) COMMENT 'Dify對話ID，用於維持翻譯上下文',
    `processing_started_at` DATETIME COMMENT '開始處理時間',
    `completed_at` DATETIME COMMENT '完成時間',
    `created_at` DATETIME COMMENT '建立時間',
    `updated_at` DATETIME ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    `deleted_at` DATETIME COMMENT '軟刪除時間',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_dt_translation_jobs_user_id` FOREIGN KEY (`user_id`) REFERENCES `dt_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- JobFile
DROP TABLE IF EXISTS `dt_job_files`;
CREATE TABLE IF NOT EXISTS `dt_job_files` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `job_id` INTEGER NOT NULL COMMENT '任務ID',
    `file_type` ENUM('source','translated') NOT NULL COMMENT '檔案類型',
    `language_code` VARCHAR(50) 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 COMMENT '建立時間',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_dt_job_files_job_id` FOREIGN KEY (`job_id`) REFERENCES `dt_translation_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SystemLog
DROP TABLE IF EXISTS `dt_system_logs`;
CREATE TABLE IF NOT EXISTS `dt_system_logs` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `level` ENUM('DEBUG','INFO','WARNING','ERROR','CRITICAL') NOT NULL COMMENT '日誌等級',
    `module` VARCHAR(100) NOT NULL COMMENT '模組名稱',
    `user_id` INTEGER COMMENT '使用者ID',
    `job_id` INTEGER COMMENT '任務ID',
    `message` TEXT NOT NULL COMMENT '日誌訊息',
    `extra_data` JSON COMMENT '額外資料',
    `created_at` DATETIME COMMENT '建立時間',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_dt_system_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `dt_users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dt_system_logs_job_id` FOREIGN KEY (`job_id`) REFERENCES `dt_translation_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TranslationCache
DROP TABLE IF EXISTS `dt_translation_cache`;
CREATE TABLE IF NOT EXISTS `dt_translation_cache` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `source_text_hash` VARCHAR(64) NOT NULL COMMENT '來源文字hash',
    `source_language` VARCHAR(50) NOT NULL COMMENT '來源語言',
    `target_language` VARCHAR(50) NOT NULL COMMENT '目標語言',
    `source_text` TEXT NOT NULL COMMENT '來源文字',
    `translated_text` TEXT NOT NULL COMMENT '翻譯文字',
    `created_at` DATETIME COMMENT '建立時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_cache` (`source_text_hash`, `source_language`, `target_language`),
    INDEX `idx_languages` (`source_language`, `target_language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- APIUsageStats
DROP TABLE IF EXISTS `dt_api_usage_stats`;
CREATE TABLE IF NOT EXISTS `dt_api_usage_stats` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NOT NULL COMMENT '使用者ID',
    `job_id` INTEGER COMMENT '任務ID',
    `api_endpoint` VARCHAR(200) NOT NULL COMMENT 'API端點',
    `prompt_tokens` INTEGER DEFAULT 0 COMMENT 'Prompt token數',
    `completion_tokens` INTEGER DEFAULT 0 COMMENT 'Completion token數',
    `total_tokens` INTEGER DEFAULT 0 COMMENT '總token數',
    `prompt_unit_price` NUMERIC(10, 8) DEFAULT 0.0 COMMENT '單價',
    `prompt_price_unit` VARCHAR(20) DEFAULT 'USD' COMMENT '價格單位',
    `cost` NUMERIC(10, 4) DEFAULT 0.0 COMMENT '成本',
    `response_time_ms` INTEGER DEFAULT 0 COMMENT '回應時間(毫秒)',
    `success` BOOL DEFAULT 1 COMMENT '是否成功',
    `error_message` TEXT COMMENT '錯誤訊息',
    `created_at` DATETIME COMMENT '建立時間',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_dt_api_usage_stats_user_id` FOREIGN KEY (`user_id`) REFERENCES `dt_users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dt_api_usage_stats_job_id` FOREIGN KEY (`job_id`) REFERENCES `dt_translation_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notification
DROP TABLE IF EXISTS `dt_notifications`;
CREATE TABLE IF NOT EXISTS `dt_notifications` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `notification_uuid` VARCHAR(36) NOT NULL COMMENT '通知唯一識別碼',
    `user_id` INTEGER NOT NULL COMMENT '使用者ID',
    `type` VARCHAR(20) NOT NULL DEFAULT 'info' COMMENT '通知類型',
    `title` VARCHAR(255) NOT NULL COMMENT '通知標題',
    `message` TEXT NOT NULL COMMENT '通知內容',
    `job_uuid` VARCHAR(36) COMMENT '關聯任務UUID',
    `link` VARCHAR(500) COMMENT '相關連結',
    `is_read` BOOL NOT NULL DEFAULT 0 COMMENT '是否已讀',
    `read_at` DATETIME COMMENT '閱讀時間',
    `created_at` DATETIME NOT NULL COMMENT '建立時間',
    `expires_at` DATETIME COMMENT '過期時間',
    `extra_data` JSON COMMENT '額外數據',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_dt_notifications_user_id` FOREIGN KEY (`user_id`) REFERENCES `dt_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;