185 lines
9.6 KiB
SQL
185 lines
9.6 KiB
SQL
-- ============================================================================
|
||
-- 自動生成的資料表 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;
|