234 lines
10 KiB
SQL
234 lines
10 KiB
SQL
-- AI 評審系統資料庫架構
|
|
-- 資料庫: db_AI_scoring
|
|
|
|
-- 建立資料庫 (如果不存在)
|
|
CREATE DATABASE IF NOT EXISTS `db_AI_scoring`
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE `db_AI_scoring`;
|
|
|
|
-- 1. 用戶表
|
|
CREATE TABLE `users` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`email` varchar(255) NOT NULL UNIQUE,
|
|
`username` varchar(100) NOT NULL UNIQUE,
|
|
`password_hash` varchar(255) NOT NULL,
|
|
`full_name` varchar(255) DEFAULT NULL,
|
|
`avatar_url` varchar(500) DEFAULT NULL,
|
|
`role` enum('admin', 'user') DEFAULT 'user',
|
|
`is_active` tinyint(1) DEFAULT 1,
|
|
`email_verified_at` timestamp NULL DEFAULT NULL,
|
|
`last_login_at` timestamp NULL DEFAULT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_email` (`email`),
|
|
KEY `idx_username` (`username`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. 用戶會話表
|
|
CREATE TABLE `user_sessions` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`user_id` bigint(20) unsigned NOT NULL,
|
|
`session_token` varchar(255) NOT NULL UNIQUE,
|
|
`ip_address` varchar(45) DEFAULT NULL,
|
|
`user_agent` text DEFAULT NULL,
|
|
`expires_at` timestamp NOT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_session_token` (`session_token`),
|
|
KEY `idx_expires_at` (`expires_at`),
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. 評分標準模板表
|
|
CREATE TABLE `criteria_templates` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`user_id` bigint(20) unsigned NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`is_default` tinyint(1) DEFAULT 0,
|
|
`is_public` tinyint(1) DEFAULT 0,
|
|
`total_weight` decimal(5,2) DEFAULT 100.00,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_is_default` (`is_default`),
|
|
KEY `idx_is_public` (`is_public`),
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. 評分項目表
|
|
CREATE TABLE `criteria_items` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`template_id` bigint(20) unsigned NOT NULL,
|
|
`name` varchar(255) NOT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`weight` decimal(5,2) NOT NULL,
|
|
`max_score` decimal(5,2) DEFAULT 10.00,
|
|
`sort_order` int(11) DEFAULT 0,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_template_id` (`template_id`),
|
|
KEY `idx_sort_order` (`sort_order`),
|
|
FOREIGN KEY (`template_id`) REFERENCES `criteria_templates`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. 評審專案表
|
|
CREATE TABLE `projects` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`user_id` bigint(20) unsigned NOT NULL,
|
|
`template_id` bigint(20) unsigned NOT NULL,
|
|
`title` varchar(255) NOT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`status` enum('draft', 'uploading', 'analyzing', 'completed', 'failed') DEFAULT 'draft',
|
|
`analysis_started_at` timestamp NULL DEFAULT NULL,
|
|
`analysis_completed_at` timestamp NULL DEFAULT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_template_id` (`template_id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_created_at` (`created_at`),
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`template_id`) REFERENCES `criteria_templates`(`id`) ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 6. 專案文件表
|
|
CREATE TABLE `project_files` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`project_id` bigint(20) unsigned NOT NULL,
|
|
`original_name` varchar(255) NOT NULL,
|
|
`file_name` varchar(255) NOT NULL,
|
|
`file_path` varchar(500) NOT NULL,
|
|
`file_size` bigint(20) unsigned NOT NULL,
|
|
`file_type` varchar(100) NOT NULL,
|
|
`mime_type` varchar(100) NOT NULL,
|
|
`upload_status` enum('uploading', 'completed', 'failed') DEFAULT 'uploading',
|
|
`upload_progress` decimal(5,2) DEFAULT 0.00,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_project_id` (`project_id`),
|
|
KEY `idx_file_type` (`file_type`),
|
|
KEY `idx_upload_status` (`upload_status`),
|
|
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 7. 專案網站連結表
|
|
CREATE TABLE `project_websites` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`project_id` bigint(20) unsigned NOT NULL,
|
|
`url` varchar(500) NOT NULL,
|
|
`title` varchar(255) DEFAULT NULL,
|
|
`description` text DEFAULT NULL,
|
|
`status` enum('pending', 'analyzing', 'completed', 'failed') DEFAULT 'pending',
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_project_id` (`project_id`),
|
|
KEY `idx_status` (`status`),
|
|
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 8. 評審記錄表
|
|
CREATE TABLE `evaluations` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`project_id` bigint(20) unsigned NOT NULL,
|
|
`overall_score` decimal(5,2) DEFAULT NULL,
|
|
`max_possible_score` decimal(5,2) DEFAULT 100.00,
|
|
`grade` varchar(10) DEFAULT NULL,
|
|
`analysis_duration` int(11) DEFAULT NULL COMMENT '分析耗時(秒)',
|
|
`ai_model_version` varchar(50) DEFAULT NULL,
|
|
`status` enum('pending', 'analyzing', 'completed', 'failed') DEFAULT 'pending',
|
|
`error_message` text DEFAULT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_project_id` (`project_id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_created_at` (`created_at`),
|
|
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 9. 評分結果明細表
|
|
CREATE TABLE `evaluation_scores` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`evaluation_id` bigint(20) unsigned NOT NULL,
|
|
`criteria_item_id` bigint(20) unsigned NOT NULL,
|
|
`score` decimal(5,2) NOT NULL,
|
|
`max_score` decimal(5,2) NOT NULL,
|
|
`weight` decimal(5,2) NOT NULL,
|
|
`weighted_score` decimal(5,2) NOT NULL,
|
|
`percentage` decimal(5,2) NOT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_evaluation_id` (`evaluation_id`),
|
|
KEY `idx_criteria_item_id` (`criteria_item_id`),
|
|
UNIQUE KEY `unique_evaluation_criteria` (`evaluation_id`, `criteria_item_id`),
|
|
FOREIGN KEY (`evaluation_id`) REFERENCES `evaluations`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`criteria_item_id`) REFERENCES `criteria_items`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 10. AI 評語和建議表
|
|
CREATE TABLE `evaluation_feedback` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`evaluation_id` bigint(20) unsigned NOT NULL,
|
|
`criteria_item_id` bigint(20) unsigned DEFAULT NULL,
|
|
`feedback_type` enum('overall', 'criteria', 'strength', 'improvement') NOT NULL,
|
|
`content` text NOT NULL,
|
|
`sort_order` int(11) DEFAULT 0,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_evaluation_id` (`evaluation_id`),
|
|
KEY `idx_criteria_item_id` (`criteria_item_id`),
|
|
KEY `idx_feedback_type` (`feedback_type`),
|
|
FOREIGN KEY (`evaluation_id`) REFERENCES `evaluations`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`criteria_item_id`) REFERENCES `criteria_items`(`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 11. 系統設定表
|
|
CREATE TABLE `system_settings` (
|
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`setting_key` varchar(100) NOT NULL UNIQUE,
|
|
`setting_value` text DEFAULT NULL,
|
|
`description` varchar(255) DEFAULT NULL,
|
|
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_setting_key` (`setting_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 插入預設評分標準模板
|
|
INSERT INTO `criteria_templates` (`user_id`, `name`, `description`, `is_default`, `is_public`, `total_weight`)
|
|
VALUES (1, '預設評分標準', '系統預設的評分標準模板', 1, 1, 100.00);
|
|
|
|
-- 插入預設評分項目
|
|
INSERT INTO `criteria_items` (`template_id`, `name`, `description`, `weight`, `max_score`, `sort_order`) VALUES
|
|
(1, '內容品質', '內容的準確性、完整性和專業度', 25.00, 10.00, 1),
|
|
(1, '視覺設計', '版面設計、色彩搭配和視覺效果', 20.00, 10.00, 2),
|
|
(1, '邏輯結構', '內容組織的邏輯性和條理性', 20.00, 10.00, 3),
|
|
(1, '創新性', '創意思維和獨特觀點的展現', 15.00, 10.00, 4),
|
|
(1, '實用性', '內容的實際應用價值和可操作性', 20.00, 10.00, 5);
|
|
|
|
-- 插入系統設定
|
|
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `description`) VALUES
|
|
('max_file_size', '104857600', '最大文件上傳大小(位元組)'),
|
|
('allowed_file_types', 'ppt,pptx,pdf,mp4,avi,mov,wmv,flv,webm', '允許上傳的文件類型'),
|
|
('ai_analysis_timeout', '300', 'AI 分析超時時間(秒)'),
|
|
('max_concurrent_analyses', '5', '最大並發分析數量'),
|
|
('default_grade_thresholds', '{"A":90,"B":80,"C":70,"D":60}', '預設等級閾值');
|
|
|
|
-- 建立索引優化查詢性能
|
|
CREATE INDEX `idx_projects_user_status` ON `projects` (`user_id`, `status`);
|
|
CREATE INDEX `idx_evaluations_project_status` ON `evaluations` (`project_id`, `status`);
|
|
CREATE INDEX `idx_project_files_project_status` ON `project_files` (`project_id`, `upload_status`);
|
|
CREATE INDEX `idx_criteria_items_template_order` ON `criteria_items` (`template_id`, `sort_order`);
|