-- iMeeting 数据库初始化脚本 (MySQL 5.7 兼容)
-- 基于 project.md v3

-- 设置数据库和字符集
-- 请在使用前手动创建数据库: CREATE DATABASE imeeting CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE imeeting;

-- 删除已存在的表 (用于重新执行脚本)
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `meeting_summaries`, `transcript_segments`, `audio_files`, `attachments`, `attendees`, `meetings`, `users`, `tags`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. 创建表结构

-- 用户表
CREATE TABLE `users` (
    `user_id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `caption` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 会议表
CREATE TABLE `meetings` (
    `meeting_id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT, -- 会议创建者
    `title` VARCHAR(255) NOT NULL,
    `meeting_time` TIMESTAMP NULL,
    `summary` TEXT, -- 以Markdown格式存储
    `tags` VARCHAR(1024) DEFAULT NULL, -- 以逗号分隔的标签字符串
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 参会人表 (关联用户)
CREATE TABLE `attendees` (
    `attendee_id` INT AUTO_INCREMENT PRIMARY KEY,
    `meeting_id` INT,
    `user_id` INT,
    UNIQUE KEY `uk_meeting_user` (`meeting_id`, `user_id`) -- 确保同一用户在同一会议中只出现一次
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 会议材料附件表
CREATE TABLE `attachments` (
    `attachment_id` INT AUTO_INCREMENT PRIMARY KEY,
    `meeting_id` INT,
    `file_name` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(512) NOT NULL, -- 存储路径或URL
    `file_type` VARCHAR(100),
    `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 音频文件与处理任务表
CREATE TABLE `audio_files` (
    `audio_id` INT AUTO_INCREMENT PRIMARY KEY,
    `meeting_id` INT,
    `file_name` VARCHAR(255),
    `file_path` VARCHAR(512) NOT NULL,
    `file_size` BIGINT DEFAULT NULL,
    `upload_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `processing_status` VARCHAR(20) DEFAULT 'uploaded', -- 'uploaded', 'processing', 'completed', 'failed'
    `error_message` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 转录任务表
CREATE TABLE `transcript_tasks` (
    `task_id` VARCHAR(100) PRIMARY KEY,
    `paraformer_task_id` VARCHAR(100) DEFAULT NULL,
    `meeting_id` INT NOT NULL,
    `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    `progress` INT DEFAULT 0, -- 0-100 进度百分比
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL,
    `error_message` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 转录内容表 (核心)
CREATE TABLE `transcript_segments` (
    `segment_id` INT AUTO_INCREMENT PRIMARY KEY,
    `meeting_id` INT,
    `speaker_id` INT, -- 解析出来的人员ID
    `speaker_tag` VARCHAR(50) NOT NULL, -- e.g., "Speaker A", "李雷"
    `start_time_ms` INT NOT NULL, -- 音频开始时间（毫秒）
    `end_time_ms` INT NOT NULL, -- 音频结束时间（毫秒）
    `text_content` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 标签表 (用于标签快速检索和颜色管理)
CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `color` varchar(7) DEFAULT '#409EFF',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 插入测试数据

-- 插入用户 (4名)
INSERT INTO `users` (`username`, `caption`, `email`, `password_hash`) VALUES
('user1', 'alice', 'alice@example.com', 'hashed_password_1'),
('user2', 'bob', 'bob@example.com', 'hashed_password_2'),
('user3', 'charlie', 'charlie@example.com', 'hashed_password_3'),
('user4', 'david', 'david@example.com', 'hashed_password_4');

-- 插入会议 (6条)
INSERT INTO `meetings` (`user_id`, `title`, `meeting_time`, `summary`, `tags`) VALUES
(1, 'Q3产品战略规划会', '2025-07-28 10:00:00', '# Q3产品战略规划会

## 核心议题
- **目标**: 确定Q3主要产品迭代方向。
- **讨论**: AI功能集成方案。

## 结论
- 推进AI摘要功能开发。', '产品,重要'),
(2, '“智慧大脑”项目技术评审', '2025-07-29 14:30:00', '技术方案已通过，部分细节待优化。', '技术'),
(1, '营销团队周会', '2025-07-30 09:00:00', '回顾上周数据，制定本周计划。', '营销'),
(3, '关于新版UI的设计评审', '2025-07-30 11:00:00', '## UI评审
- **优点**: 简洁、现代。
- **待办**: 调整登录页按钮颜色。', '设计'),
(4, '年度财务报告初审', '2025-07-31 15:00:00', NULL, NULL),
(2, '服务器架构升级讨论', '2025-08-01 16:00:00', '初步同意采用微服务架构。', '技术,重要');

-- 插入参会人
-- 会议1: Alice, Bob, Charlie
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (1, 1), (1, 2), (1, 3);
-- 会议2: Bob, David
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (2, 2), (2, 4);
-- 会议3: Alice, Charlie
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (3, 1), (3, 3);
-- 会议4: Charlie, Alice, David
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (4, 3), (4, 1), (4, 4);
-- 会议5: David, Bob
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (5, 4), (5, 2);
-- 会议6: Bob, Charlie, David
INSERT INTO `attendees` (`meeting_id`, `user_id`) VALUES (6, 2), (6, 3), (6, 4);

-- 插入会议材料
INSERT INTO `attachments` (`meeting_id`, `file_name`, `file_path`, `file_type`) VALUES
(1, 'Q3产品规划.pptx', '/uploads/meeting_1/q3_plan.pptx', 'application/vnd.openxmlformats-officedocument.presentationml.presentation'),
(2, '技术方案V2.pdf', '/uploads/meeting_2/tech_spec_v2.pdf', 'application/pdf');

-- 插入音频文件记录
INSERT INTO `audio_files` (`meeting_id`, `file_name`, `file_path`, `file_size`, `processing_status`) VALUES
(1, 'meeting_1_audio.mp3', '/uploads/audio/1/meeting_1_audio.mp3', 15728640, 'completed'),
(2, 'meeting_2_audio.wav', '/uploads/audio/2/meeting_2_audio.wav', 23456780, 'processing'),
(3, 'meeting_3_audio.m4a', '/uploads/audio/3/meeting_3_audio.m4a', 18923456, 'uploaded'),
(4, 'meeting_4_audio.mp3', '/uploads/audio/4/meeting_4_audio.mp3', 12345678, 'failed');

-- 插入转录任务记录
INSERT INTO `transcript_tasks` (`task_id`, `meeting_id`, `status`, `progress`, `created_at`) VALUES
('task-uuid-1', 1, 'completed', 100, '2025-07-28 10:05:00'),
('task-uuid-2', 2, 'processing', 45, '2025-07-29 14:35:00'),
('task-uuid-4', 4, 'failed', 0, '2025-07-30 11:05:00');

-- 插入转录内容 (为会议1)
INSERT INTO `transcript_segments` (`meeting_id`, `speaker_id`, `speaker_tag`, `start_time_ms`, `end_time_ms`, `text_content`) VALUES
(1, 0, '发言人 0', 5200, 9800, '好的，我们开始今天Q3的战略规划会。'),
(1, 1, '发言人 1', 10100, 15500, '我先同步一下上个季度的数据，我们的用户增长了20%，主要来自于新推出的移动端。'),
(1, 0, '发言人 0', 16000, 21300, '非常好。这个季度，我希望我们能重点讨论一下AI功能的集成，特别是会议摘要这部分。'),
(1, 2, '发言人 2', 21800, 28000, '我同意，自动摘要可以极大地提升用户体验，我这边已经做了一些初步的技术调研。');

-- 插入标签
INSERT INTO `tags` (`name`, `color`) VALUES
('产品', '#409EFF'),
('技术', '#67C23A'),
('营销', '#E6A23C'),
('设计', '#F56C6C'),
('重要', '#909399');

-- 3. 添加外键约束
ALTER TABLE `meetings` ADD CONSTRAINT `fk_meetings_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE;
ALTER TABLE `attendees` ADD CONSTRAINT `fk_attendees_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings`(`meeting_id`) ON DELETE CASCADE;
ALTER TABLE `attendees` ADD CONSTRAINT `fk_attendees_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE;
ALTER TABLE `attachments` ADD CONSTRAINT `fk_attachments_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings`(`meeting_id`) ON DELETE CASCADE;
ALTER TABLE `audio_files` ADD CONSTRAINT `fk_audio_files_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings`(`meeting_id`) ON DELETE CASCADE;
ALTER TABLE `transcript_tasks` ADD CONSTRAINT `fk_transcript_tasks_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings`(`meeting_id`) ON DELETE CASCADE;
ALTER TABLE `transcript_segments` ADD CONSTRAINT `fk_transcript_segments_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `meetings`(`meeting_id`) ON DELETE CASCADE;

-- 4. 添加索引优化查询性能
-- audio_files 表索引
ALTER TABLE `audio_files` ADD INDEX `idx_meeting_id` (`meeting_id`);
ALTER TABLE `audio_files` ADD INDEX `idx_task_id` (`task_id`);
ALTER TABLE `audio_files` ADD INDEX `idx_processing_status` (`processing_status`);

-- transcript_tasks 表索引
ALTER TABLE `transcript_tasks` ADD INDEX `idx_meeting_id` (`meeting_id`);
ALTER TABLE `transcript_tasks` ADD INDEX `idx_status` (`status`);
ALTER TABLE `transcript_tasks` ADD INDEX `idx_created_at` (`created_at`);

-- transcript_segments 表索引
ALTER TABLE `transcript_segments` ADD INDEX `idx_meeting_id` (`meeting_id`);
ALTER TABLE `transcript_segments` ADD INDEX `idx_speaker_id` (`speaker_id`);
ALTER TABLE `transcript_segments` ADD INDEX `idx_start_time` (`start_time_ms`);

-- meetings 表索引
ALTER TABLE `meetings` ADD INDEX `idx_user_id` (`user_id`);
ALTER TABLE `meetings` ADD INDEX `idx_meeting_time` (`meeting_time`);
ALTER TABLE `meetings` ADD INDEX `idx_created_at` (`created_at`);
ALTER TABLE `meetings` ADD INDEX `idx_tags` (`tags`(255));

-- attendees 表索引
ALTER TABLE `attendees` ADD INDEX `idx_meeting_id` (`meeting_id`);
ALTER TABLE `attendees` ADD INDEX `idx_user_id` (`user_id`);

-- attachments 表索引
ALTER TABLE `attachments` ADD INDEX `idx_meeting_id` (`meeting_id`);

-- tags 表索引
ALTER TABLE `tags` ADD INDEX `idx_name` (`name`);

-- 脚本结束
SELECT '数据库初始化脚本 (MySQL) 执行完毕。';

-- Knowledge Base Tables
CREATE TABLE IF NOT EXISTS `prompts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL UNIQUE COMMENT '提示词名称，保持唯一以方便管理',
  `tags` VARCHAR(255) COMMENT '标签，用于分类和搜索，多个标签用逗号分隔',
  `content` TEXT NOT NULL COMMENT '完整的提示词内容',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT='用于存储AI总结的提示词模板';

CREATE TABLE IF NOT EXISTS `knowledge_bases` (
  `kb_id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL COMMENT '标题',
  `content` TEXT NULL COMMENT '生成的知识库内容 (Markdown格式)',
  `creator_id` INT NOT NULL COMMENT '创建者用户ID',
  `is_shared` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否为共享知识库',
  `source_meeting_ids` VARCHAR(255) NULL COMMENT '内容来源的会议ID列表 (逗号分隔)',
  `tags` VARCHAR(255) NULL COMMENT '逗号分隔的标签',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`creator_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) COMMENT='知识库条目表';

CREATE TABLE IF NOT EXISTS `knowledge_base_tasks` (
  `task_id` VARCHAR(100) PRIMARY KEY COMMENT '业务任务唯一ID (UUID)',
  `user_id` INT NOT NULL COMMENT '发起任务的用户ID',
  `kb_id` INT NOT NULL COMMENT '关联的知识库条目ID',
  `user_prompt` TEXT NULL COMMENT '用户输入的提示词',
  `status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending' COMMENT '任务状态',
  `progress` INT DEFAULT 0 COMMENT '任务进度百分比 (0-100)',
  `error_message` TEXT NULL COMMENT '任务失败时的错误信息',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
  FOREIGN KEY (`kb_id`) REFERENCES `knowledge_bases`(`kb_id`) ON DELETE CASCADE
) COMMENT='知识库生成任务表';

CREATE TABLE IF NOT EXISTS `prompt_config` (
  `config_id` INT AUTO_INCREMENT PRIMARY KEY,
  `task_name` VARCHAR(100) UNIQUE NOT NULL COMMENT '任务名称',
  `prompt_id` INT NOT NULL COMMENT '关联的提示词模版ID',
  FOREIGN KEY (`prompt_id`) REFERENCES `prompts`(`id`)
) COMMENT='提示词配置表';

-- Initial data for prompt_config
INSERT INTO `prompt_config` (`task_name`, `prompt_id`) VALUES ('LLM_TASK', 1);
INSERT INTO `prompt_config` (`task_name`, `prompt_id`) VALUES ('KNOWLEDGE_TASK', 2);

-- You might need to insert prompts with id=1 and id=2 into the `prompts` table for this to work.
-- Example:
-- INSERT INTO `prompts` (`id`, `name`, `content`) VALUES (1, 'Default Meeting Summary', 'Please summarize the following meeting transcript...');
-- INSERT INTO `prompts` (`id`, `name`, `content`) VALUES (2, 'Default Knowledge Base Generation', 'Please generate a knowledge base article from the following text...');