# 专题和评论系统SQL部署指南

本文档包含了多语言AI资讯系统中专题和评论功能的完整SQL部署指南。

## 📋 部署文件清单

### 1. 主数据库结构文件
- **文件名**: `ai_multilingual_news_database.sql`
- **用途**: 创建完整的数据库结构，包括专题和评论系统
- **包含内容**:
  - 所有基础表结构
  - 专题系统表 (ak_topics, ak_topic_contents, ak_topic_subscriptions)
  - 评论系统表 (ak_comments, ak_comment_reactions, ak_comment_reports, ak_comment_moderation_queue)
  - 索引、约束、触发器
  - 基础配置数据

### 2. 演示数据文件
- **文件名**: `topics_comments_init_data.sql`
- **用途**: 插入专题和评论系统的演示数据
- **包含内容**:
  - 5个不同类型的演示专题
  - 多层级评论结构
  - 点赞、举报、审核数据
  - 统计数据更新

### 3. 修复版演示数据（备用）
- **文件名**: `demo_topics_comments_data_fixed.sql`
- **用途**: 兼容性更好的演示数据版本
- **特点**: 移除了一些高级PostgreSQL语法，提高兼容性

## 🚀 部署步骤

### 第一步：创建数据库结构
```bash
# 执行主数据库结构文件
psql -U username -d database_name -f ai_multilingual_news_database.sql
```

### 第二步：插入演示数据
```bash
# 执行演示数据文件
psql -U username -d database_name -f topics_comments_init_data.sql
```

### 第三步：验证部署
```sql
-- 检查专题表
SELECT COUNT(*) FROM ak_topics;

-- 检查评论表
SELECT COUNT(*) FROM ak_comments;

-- 检查数据完整性
SELECT 
    t.title,
    t.content_count,
    t.comment_count,
    t.subscriber_count
FROM ak_topics t
ORDER BY t.priority_level DESC;
```

## 📊 数据库表结构概览

### 专题系统表

#### ak_topics (专题表)
- **主要字段**: id, title, description, topic_type, status, creator_id
- **统计字段**: content_count, view_count, like_count, comment_count, subscriber_count
- **SEO字段**: meta_keywords, meta_description, seo_slug
- **分类**: 8种专题类型 (breaking, trending, series, analysis, guide, interview, report, timeline)

#### ak_topic_contents (专题内容关联表)
- **关联**: topic_id -> ak_topics, content_id -> ak_contents
- **排序**: display_order
- **标识**: is_featured, editor_note

#### ak_topic_subscriptions (专题订阅表)
- **关联**: topic_id -> ak_topics, user_id
- **设置**: notification_enabled
- **统计**: subscribed_at, last_notified_at

### 评论系统表

#### ak_comments (评论表)
- **目标**: target_type ('content'/'topic'), target_id
- **层级**: parent_id, level (0-2), thread_path
- **内容**: content, content_html, author_id, author_name
- **统计**: like_count, dislike_count, reply_count
- **质量**: quality_score, sentiment_score, ai_analysis
- **审核**: status, moderation_flags, is_pinned

#### ak_comment_reactions (评论反应表)
- **反应**: like, dislike, love, laugh, angry, sad
- **唯一**: (comment_id, user_id, reaction_type)

#### ak_comment_reports (评论举报表)
- **类型**: spam, inappropriate, harassment, misinformation, copyright
- **流程**: pending -> reviewed -> resolved/dismissed

#### ak_comment_moderation_queue (审核队列表)
- **AI支持**: ai_risk_score, ai_recommendations
- **分配**: assigned_to, priority_level
- **状态**: pending -> in_progress -> completed

### 收藏系统表

#### ak_content_favorites (内容收藏表)
- **目标**: target_type ('content'/'topic'), target_id
- **分类**: folder_id, notes, tags
- **权限**: is_public
- **关联**: user_id

#### ak_favorite_folders (收藏夹表)
- **基本**: name, description, icon, color
- **属性**: is_default, is_public, item_count
- **排序**: display_order
- **统计**: 触发器自动维护item_count

#### ak_favorite_shares (收藏夹分享表)
- **分享**: share_code, share_type, access_password
- **统计**: view_count, clone_count
- **有效期**: expire_at, is_active

### 转发分享系统表

#### ak_content_shares (内容转发分享表)
- **目标**: target_type ('content'/'topic'/'comment'), target_id
- **类型**: share_type (forward/quote/repost/link)
- **平台**: share_platform (internal/wechat/weibo/twitter/facebook等)
- **转发链**: parent_share_id, share_level
- **统计**: reach_count, click_count, like_count, reshare_count
- **内容**: share_content, share_title
- **分析**: ip_address, user_agent, device_info, geo_location

#### ak_share_analytics (分享统计分析表)
- **维度**: target_type, target_id, date_recorded, platform
- **指标**: share_count, unique_sharers, total_reach, total_clicks
- **分析**: avg_share_level, viral_coefficient
- **聚合**: 按日期和平台统计

## 🔧 核心功能SQL

### 专题管理

#### 创建专题
```sql
INSERT INTO ak_topics (title, description, topic_type, status, creator_id)
VALUES ('专题标题', '专题描述', 'series', 'active', 'user_id');
```

#### 添加专题内容
```sql
INSERT INTO ak_topic_contents (topic_id, content_id, display_order, is_featured)
VALUES ('topic_id', 'content_id', 1, true);
```

#### 订阅专题
```sql
INSERT INTO ak_topic_subscriptions (topic_id, user_id, notification_enabled)
VALUES ('topic_id', 'user_id', true)
ON CONFLICT (topic_id, user_id) DO NOTHING;
```

### 评论管理

#### 发布顶级评论
```sql
INSERT INTO ak_comments (
    target_type, target_id, author_id, author_name, content, 
    level, thread_path
) VALUES (
    'content', 'content_id', 'user_id', '用户名', '评论内容',
    0, '0001'
);
```

#### 回复评论
```sql
INSERT INTO ak_comments (
    target_type, target_id, parent_id, author_id, author_name, content,
    level, thread_path
) VALUES (
    'content', 'content_id', 'parent_comment_id', 'user_id', '用户名', '回复内容',
    1, '0001.0001'
);
```

#### 点赞评论
```sql
INSERT INTO ak_comment_reactions (comment_id, user_id, reaction_type)
VALUES ('comment_id', 'user_id', 'like')
ON CONFLICT (comment_id, user_id, reaction_type) DO NOTHING;
```

#### 举报评论
```sql
INSERT INTO ak_comment_reports (comment_id, reporter_id, report_type, report_reason)
VALUES ('comment_id', 'reporter_id', 'inappropriate', '举报原因');
```

### 收藏管理

#### 创建收藏夹
```sql
INSERT INTO ak_favorite_folders (user_id, name, description, icon, color, is_default)
VALUES ('user_id', '收藏夹名称', '描述', 'star', '#FFD700', false);
```

#### 收藏内容/专题
```sql
INSERT INTO ak_content_favorites (
    user_id, target_type, target_id, folder_id, notes, tags, is_public
) VALUES (
    'user_id', 'content', 'content_id', 'folder_id', '收藏备注', 
    ARRAY['标签1', '标签2'], false
)
ON CONFLICT (user_id, target_type, target_id) DO NOTHING;
```

#### 分享收藏夹
```sql
INSERT INTO ak_favorite_shares (folder_id, shared_by, share_type, access_password)
VALUES ('folder_id', 'user_id', 'public', NULL);
```

### 转发分享管理

#### 分享内容
```sql
INSERT INTO ak_content_shares (
    user_id, target_type, target_id, share_type, share_platform, 
    share_content, original_author_id, share_level
) VALUES (
    'user_id', 'content', 'content_id', 'forward', 'wechat',
    '分享时的评论内容', 'original_author_id', 0
);
```

#### 转发链条
```sql
INSERT INTO ak_content_shares (
    user_id, target_type, target_id, share_type, share_platform,
    parent_share_id, share_level
) VALUES (
    'user_id', 'content', 'content_id', 'repost', 'internal',
    'parent_share_id', 1
);
```

#### 查询分享统计
```sql
SELECT 
    target_type,
    COUNT(*) as total_shares,
    COUNT(DISTINCT user_id) as unique_sharers,
    SUM(reach_count) as total_reach,
    AVG(viral_coefficient) as avg_viral_coefficient
FROM ak_content_shares
WHERE created_at >= now() - interval '7 days'
GROUP BY target_type;
```

## 📈 统计查询

### 专题统计
```sql
-- 专题概览
SELECT 
    t.title,
    t.topic_type,
    t.status,
    t.content_count,
    t.view_count,
    t.comment_count,
    t.subscriber_count,
    t.created_at
FROM ak_topics t
ORDER BY t.priority_level DESC, t.view_count DESC;

-- 热门专题
SELECT 
    t.title,
    t.view_count + t.like_count * 5 + t.comment_count * 3 as popularity_score
FROM ak_topics t
WHERE t.status = 'active'
ORDER BY popularity_score DESC
LIMIT 10;
```

### 评论统计
```sql
-- 评论层级结构
SELECT 
    c.id,
    c.level,
    c.thread_path,
    c.author_name,
    SUBSTRING(c.content, 1, 50) || '...' as preview,
    c.like_count,
    c.reply_count,
    c.created_at
FROM ak_comments c
WHERE c.target_type = 'content' AND c.target_id = 'content_id'
ORDER BY c.thread_path;

-- 评论质量分析
SELECT 
    c.author_name,
    AVG(c.quality_score) as avg_quality,
    AVG(c.sentiment_score) as avg_sentiment,
    COUNT(*) as comment_count,
    SUM(c.like_count) as total_likes
FROM ak_comments c
WHERE c.status = 'active'
GROUP BY c.author_name
HAVING COUNT(*) >= 3
ORDER BY avg_quality DESC;
```

## 🛡️ 安全和性能

### 索引优化
- 专题查询：topic_type, status, priority_level, view_count
- 评论查询：target_type + target_id, thread_path, author_id
- 反应查询：comment_id + reaction_type, user_id

### 数据完整性
- 外键约束确保数据一致性
- 唯一约束防止重复数据
- 触发器自动更新统计数据

### 审核机制
- AI自动评分和标记
- 人工审核队列
- 举报处理流程
- 软删除支持

## 🔄 维护和更新

### 定期维护SQL
```sql
-- 清理过期的软删除评论
DELETE FROM ak_comments 
WHERE deleted_at < now() - interval '90 days';

-- 更新专题统计
UPDATE ak_topics SET 
    content_count = (SELECT COUNT(*) FROM ak_topic_contents WHERE topic_id = ak_topics.id),
    comment_count = (SELECT COUNT(*) FROM ak_comments WHERE target_type = 'topic' AND target_id = ak_topics.id AND status = 'active'),
    subscriber_count = (SELECT COUNT(*) FROM ak_topic_subscriptions WHERE topic_id = ak_topics.id);

-- 分析评论质量趋势
SELECT 
    DATE_TRUNC('day', created_at) as date,
    AVG(quality_score) as avg_quality,
    COUNT(*) as comment_count
FROM ak_comments
WHERE created_at >= now() - interval '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY date;
```

### 性能监控
```sql
-- 查询慢查询
SELECT 
    schemaname,
    tablename,
    n_tup_ins + n_tup_upd + n_tup_del as total_writes,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE schemaname = 'public' 
AND tablename IN ('ak_topics', 'ak_comments', 'ak_comment_reactions')
ORDER BY total_writes DESC;
```

## 📝 注意事项

1. **UUID类型**: 所有ID字段使用UUID类型，确保全局唯一性
2. **时区处理**: 所有时间字段使用`TIMESTAMP WITH TIME ZONE`
3. **文本搜索**: 支持全文搜索的字段已建立GIN索引
4. **数组字段**: tags、keywords等使用PostgreSQL数组类型
5. **JSON字段**: 扩展属性使用JSONB类型存储
6. **软删除**: 支持软删除机制，数据不会物理删除
7. **触发器**: 自动维护统计数据，减少手动更新需求

## 🚨 故障排除

### 常见问题

1. **外键约束错误**: 确保引用的记录存在
2. **重复数据错误**: 检查唯一约束
3. **权限问题**: 确保数据库用户有足够权限
4. **性能问题**: 检查索引使用情况

### 数据恢复
```sql
-- 恢复误删的评论
UPDATE ak_comments 
SET status = 'active', deleted_at = NULL 
WHERE id = 'comment_id' AND status = 'deleted';

-- 重建统计数据
SELECT update_all_topic_stats();
SELECT update_all_comment_stats();
```

## 📞 支持

如果在部署过程中遇到问题，请检查：
1. PostgreSQL版本是否为15+
2. 必要的扩展是否已安装
3. 数据库用户权限是否足够
4. 字符编码是否设置为UTF-8

---

*本文档基于PostgreSQL 15+环境编写，建议在测试环境中先验证所有功能后再部署到生产环境。*