-- ===================================================================
-- 专题和评论系统初始化数据 (PostgreSQL)
-- 基于ai_multilingual_news_database.sql
-- 为专题和评论系统提供完整的初始化演示数据
-- ===================================================================

-- 设置客户端编码确保中文显示正确
SET client_encoding = 'UTF8';

-- ===================================================================
-- 1. 前置检查
-- ===================================================================

DO $$
BEGIN
    -- 检查必要的表是否存在
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ak_topics') THEN
        RAISE EXCEPTION '表 ak_topics 不存在，请先执行主数据库结构文件';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ak_comments') THEN
        RAISE EXCEPTION '表 ak_comments 不存在，请先执行主数据库结构文件';
    END IF;
    
    RAISE NOTICE '开始插入专题和评论系统演示数据...';
END $$;

-- ===================================================================
-- 2. 清理旧数据（可选）
-- ===================================================================

-- 如果需要重新初始化，取消注释以下代码
/*
DELETE FROM ak_comment_moderation_queue;
DELETE FROM ak_comment_reports;
DELETE FROM ak_comment_reactions;
DELETE FROM ak_comments;
DELETE FROM ak_topic_subscriptions;
DELETE FROM ak_topic_contents;
DELETE FROM ak_topics;
*/

-- ===================================================================
-- 3. 插入演示专题数据
-- ===================================================================

INSERT INTO ak_topics (
    id,
    title,
    description,
    topic_type,
    status,
    cover_image_url,
    creator_id,
    content_count,
    view_count,
    like_count,
    share_count,
    comment_count,
    subscriber_count,
    meta_keywords,
    meta_description,
    seo_slug,
    priority_level,
    tags,
    created_at,
    updated_at
) VALUES 
-- AI技术专题
(
    'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
    'AI技术发展全景：从ChatGPT到未来',
    '深度解析人工智能技术的发展历程、现状和未来趋势，涵盖大语言模型、机器学习、深度学习等核心技术。本专题将带您全面了解AI技术的发展轨迹，探索人工智能对社会各个领域的深远影响。',
    'series',
    'featured',
    'https://example.com/images/ai-tech-cover.jpg',
    '550e8400-e29b-41d4-a716-446655440000'::uuid,
    15,
    28500,
    256,
    89,
    12,
    148,
    ARRAY['人工智能', 'ChatGPT', 'AI技术', '机器学习', '深度学习', '大语言模型'],
    'AI技术发展专题：从基础概念到前沿应用，全面解析人工智能的发展历程和未来趋势',
    'ai-technology-development-guide',
    10,
    ARRAY['AI', '技术', '专题', '教程'],
    now() - interval '7 days',
    now() - interval '1 day'
),
-- 全球经济专题
(
    'f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid,
    '2025年全球经济展望',
    '分析2025年全球经济形势，包括主要经济体发展趋势、通胀压力、货币政策、贸易关系等重要议题。专题聚焦经济复苏、产业转型、金融市场波动等热点话题。',
    'analysis',
    'active',
    'https://example.com/images/economy-2025-cover.jpg',
    '550e8400-e29b-41d4-a716-446655440001'::uuid,
    8,
    15600,
    128,
    45,
    6,
    87,
    ARRAY['全球经济', '经济展望', '2025', '通胀', '货币政策', '经济复苏'],
    '2025年全球经济形势分析：通胀、复苏、政策调整全解读',
    'global-economy-outlook-2025',
    8,
    ARRAY['经济', '分析', '2025', '全球'],
    now() - interval '5 days',
    now() - interval '2 hours'
),
-- 气候变化专题
(
    'f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid,
    '气候变化应对策略追踪',
    '追踪全球气候变化应对策略的最新进展，包括各国减排政策、清洁能源发展、碳中和目标实施情况等。持续更新国际气候合作动态和创新技术突破。',
    'timeline',
    'active',
    'https://example.com/images/climate-action-cover.jpg',
    '550e8400-e29b-41d4-a716-446655440002'::uuid,
    12,
    19200,
    186,
    67,
    8,
    125,
    ARRAY['气候变化', '碳中和', '清洁能源', '减排政策', '环保'],
    '气候变化应对策略实时追踪：政策进展、技术突破、国际合作',
    'climate-action-tracking',
    7,
    ARRAY['气候', '环保', '政策', '追踪'],
    now() - interval '3 days',
    now() - interval '30 minutes'
),
-- 突发事件专题
(
    'f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid,
    '突发：全球芯片短缺危机',
    '追踪全球芯片短缺危机的最新发展，分析供应链中断、产业影响、各国应对措施等。实时更新半导体行业动态和市场变化。',
    'breaking',
    'featured',
    'https://example.com/images/chip-shortage-cover.jpg',
    '550e8400-e29b-41d4-a716-446655440003'::uuid,
    6,
    32100,
    298,
    156,
    15,
    203,
    ARRAY['芯片短缺', '半导体', '供应链', '科技产业', '突发事件'],
    '全球芯片短缺危机最新进展：供应链中断、产业冲击、应对策略',
    'global-chip-shortage-crisis',
    15,
    ARRAY['芯片', '突发', '科技', '供应链'],
    now() - interval '1 day',
    now() - interval '10 minutes'
),
-- 深度报告专题
(
    'f47ac10b-58cc-4372-a567-0e02b2c3d483'::uuid,
    '元宇宙技术发展深度报告',
    '全面深入分析元宇宙技术发展现状，包括VR/AR技术进展、虚拟世界构建、数字经济模式等。探讨元宇宙对社会、经济、文化等领域的深远影响。',
    'report',
    'active',
    'https://example.com/images/metaverse-report-cover.jpg',
    '550e8400-e29b-41d4-a716-446655440004'::uuid,
    10,
    12800,
    95,
    32,
    4,
    56,
    ARRAY['元宇宙', 'VR', 'AR', '虚拟现实', '数字经济', '技术报告'],
    '元宇宙技术发展深度报告：技术现状、应用场景、未来趋势全面解析',
    'metaverse-technology-deep-report',
    6,
    ARRAY['元宇宙', '报告', '技术', 'VR'],
    now() - interval '2 days',
    now() - interval '4 hours'
);

-- ===================================================================
-- 4. 插入专题内容关联数据
-- ===================================================================

-- 注意：这里使用模拟的内容ID，实际使用时需要替换为真实的内容ID
INSERT INTO ak_topic_contents (
    topic_id,
    content_id,
    display_order,
    editor_note,
    is_featured,
    added_by,
    added_at
) VALUES 
-- AI技术专题内容
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '123e4567-e89b-12d3-a456-426614174000'::uuid, 1, 'AI技术发展历史回顾，适合作为专题开篇', true, '550e8400-e29b-41d4-a716-446655440000'::uuid, now() - interval '6 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '123e4567-e89b-12d3-a456-426614174001'::uuid, 2, 'ChatGPT技术原理深度解析', true, '550e8400-e29b-41d4-a716-446655440000'::uuid, now() - interval '5 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '123e4567-e89b-12d3-a456-426614174002'::uuid, 3, '机器学习算法基础教程', false, '550e8400-e29b-41d4-a716-446655440000'::uuid, now() - interval '4 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '123e4567-e89b-12d3-a456-426614174003'::uuid, 4, 'AI伦理与社会影响探讨', true, '550e8400-e29b-41d4-a716-446655440000'::uuid, now() - interval '3 days'),

-- 经济专题内容
('f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid, '123e4567-e89b-12d3-a456-426614174010'::uuid, 1, '2025年经济预测报告', true, '550e8400-e29b-41d4-a716-446655440001'::uuid, now() - interval '4 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid, '123e4567-e89b-12d3-a456-426614174011'::uuid, 2, '主要央行货币政策分析', false, '550e8400-e29b-41d4-a716-446655440001'::uuid, now() - interval '3 days'),

-- 气候专题内容
('f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid, '123e4567-e89b-12d3-a456-426614174020'::uuid, 1, '全球气候行动最新动态', true, '550e8400-e29b-41d4-a716-446655440002'::uuid, now() - interval '2 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid, '123e4567-e89b-12d3-a456-426614174021'::uuid, 2, '清洁能源技术突破', true, '550e8400-e29b-41d4-a716-446655440002'::uuid, now() - interval '1 day'),

-- 芯片危机专题内容
('f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, '123e4567-e89b-12d3-a456-426614174030'::uuid, 1, '芯片短缺影响分析', true, '550e8400-e29b-41d4-a716-446655440003'::uuid, now() - interval '1 day'),
('f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, '123e4567-e89b-12d3-a456-426614174031'::uuid, 2, '半导体产业链重构', true, '550e8400-e29b-41d4-a716-446655440003'::uuid, now() - interval '12 hours');

-- ===================================================================
-- 5. 插入专题订阅数据
-- ===================================================================

INSERT INTO ak_topic_subscriptions (
    topic_id,
    user_id,
    notification_enabled,
    subscribed_at,
    last_notified_at
) VALUES 
-- AI技术专题订阅
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '550e8400-e29b-41d4-a716-446655440010'::uuid, true, now() - interval '6 days', now() - interval '1 day'),
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '550e8400-e29b-41d4-a716-446655440011'::uuid, true, now() - interval '5 days', now() - interval '2 days'),
('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, '550e8400-e29b-41d4-a716-446655440012'::uuid, false, now() - interval '4 days', NULL),

-- 经济专题订阅
('f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid, '550e8400-e29b-41d4-a716-446655440013'::uuid, true, now() - interval '4 days', now() - interval '1 day'),
('f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid, '550e8400-e29b-41d4-a716-446655440014'::uuid, false, now() - interval '3 days', NULL),

-- 气候专题订阅
('f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid, '550e8400-e29b-41d4-a716-446655440015'::uuid, true, now() - interval '2 days', now() - interval '6 hours'),
('f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid, '550e8400-e29b-41d4-a716-446655440016'::uuid, true, now() - interval '1 day', NULL),

-- 芯片危机专题订阅（热门专题，订阅量最高）
('f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, '550e8400-e29b-41d4-a716-446655440017'::uuid, true, now() - interval '1 day', now() - interval '2 hours'),
('f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, '550e8400-e29b-41d4-a716-446655440018'::uuid, true, now() - interval '18 hours', now() - interval '1 hour'),
('f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, '550e8400-e29b-41d4-a716-446655440019'::uuid, true, now() - interval '12 hours', NULL);

-- ===================================================================
-- 6. 插入评论数据（多层级结构）
-- ===================================================================

-- 内容评论数据
INSERT INTO ak_comments (
    id,
    target_type,
    target_id,
    parent_id,
    author_id,
    author_name,
    author_avatar,
    content,
    content_html,
    status,
    like_count,
    dislike_count,
    reply_count,
    level,
    thread_path,
    is_pinned,
    is_author_reply,
    quality_score,
    sentiment_score,
    language_detected,
    ip_address,
    user_agent,
    created_at,
    updated_at
) VALUES 
-- 顶级评论1（AI文章）
(
    '11111111-1111-1111-1111-111111111111'::uuid,
    'content',
    '123e4567-e89b-12d3-a456-426614174000'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440010'::uuid,
    '科技爱好者小王',
    'https://example.com/avatar1.jpg',
    '这篇关于AI技术发展的文章写得太好了！从历史发展到现状分析，再到未来展望，脉络清晰，内容丰富。特别是对ChatGPT等大语言模型的介绍很详细，让我这个非技术人员也能理解。希望能看到更多关于AI伦理和社会影响的内容。',
    '<p>这篇关于AI技术发展的文章写得太好了！从历史发展到现状分析，再到未来展望，脉络清晰，内容丰富。特别是对<strong>ChatGPT</strong>等大语言模型的介绍很详细，让我这个非技术人员也能理解。希望能看到更多关于AI伦理和社会影响的内容。</p>',
    'active',
    15,
    0,
    2,
    0,
    '0001',
    false,
    false,
    0.85,
    0.7,
    'zh-CN',
    '192.168.1.100'::inet,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    now() - interval '3 days',
    now() - interval '3 days'
),
-- 对评论1的回复
(
    '22222222-2222-2222-2222-222222222222'::uuid,
    'content',
    '123e4567-e89b-12d3-a456-426614174000'::uuid,
    '11111111-1111-1111-1111-111111111111'::uuid,
    '550e8400-e29b-41d4-a716-446655440011'::uuid,
    'AI研究员张博士',
    'https://example.com/avatar2.jpg',
    '@科技爱好者小王 很高兴这篇文章对您有帮助！关于AI伦理的话题确实很重要，我们正在准备相关的专题内容，敬请期待。如果您有具体想了解的方面，欢迎留言告诉我们。',
    '<p><span class="mention">@科技爱好者小王</span> 很高兴这篇文章对您有帮助！关于AI伦理的话题确实很重要，我们正在准备相关的专题内容，敬请期待。如果您有具体想了解的方面，欢迎留言告诉我们。</p>',
    'active',
    8,
    0,
    1,
    1,
    '0001.0001',
    false,
    true,
    0.92,
    0.8,
    'zh-CN',
    '10.0.0.15'::inet,
    'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36',
    now() - interval '2 days 12 hours',
    now() - interval '2 days 12 hours'
),
-- 对回复的再回复
(
    '33333333-3333-3333-3333-333333333333'::uuid,
    'content',
    '123e4567-e89b-12d3-a456-426614174000'::uuid,
    '22222222-2222-2222-2222-222222222222'::uuid,
    '550e8400-e29b-41d4-a716-446655440010'::uuid,
    '科技爱好者小王',
    'https://example.com/avatar1.jpg',
    '@AI研究员张博士 太好了！我特别关心AI在教育领域的应用和可能带来的教育公平问题，期待您的专题内容！',
    '<p><span class="mention">@AI研究员张博士</span> 太好了！我特别关心AI在教育领域的应用和可能带来的教育公平问题，期待您的专题内容！</p>',
    'active',
    3,
    0,
    0,
    2,
    '0001.0001.0001',
    false,
    false,
    0.78,
    0.6,
    'zh-CN',
    '192.168.1.100'::inet,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    now() - interval '2 days 6 hours',
    now() - interval '2 days 6 hours'
),
-- 另一个顶级评论
(
    '44444444-4444-4444-4444-444444444444'::uuid,
    'content',
    '123e4567-e89b-12d3-a456-426614174000'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440012'::uuid,
    '程序员小李',
    'https://example.com/avatar3.jpg',
    '作为一名AI工程师，我觉得这篇文章的技术细节还可以更深入一些。比如Transformer架构的attention机制部分，如果能配上更多的代码示例就更好了。不过总体来说还是很不错的科普文章。',
    '<p>作为一名AI工程师，我觉得这篇文章的技术细节还可以更深入一些。比如<strong>Transformer架构</strong>的attention机制部分，如果能配上更多的代码示例就更好了。不过总体来说还是很不错的科普文章。</p>',
    'active',
    22,
    1,
    1,
    0,
    '0002',
    false,
    false,
    0.88,
    0.4,
    'zh-CN',
    '203.0.113.42'::inet,
    'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36',
    now() - interval '2 days',
    now() - interval '2 days'
),
-- 对专业评论的回复
(
    '55555555-5555-5555-5555-555555555555'::uuid,
    'content',
    '123e4567-e89b-12d3-a456-426614174000'::uuid,
    '44444444-4444-4444-4444-444444444444'::uuid,
    '550e8400-e29b-41d4-a716-446655440013'::uuid,
    '内容编辑小陈',
    'https://example.com/avatar4.jpg',
    '@程序员小李 感谢您的专业建议！我们正在考虑推出一个技术进阶系列，会包含更多代码示例和实战案例。您提到的Transformer架构确实值得单独开一个深度教程。',
    '<p><span class="mention">@程序员小李</span> 感谢您的专业建议！我们正在考虑推出一个技术进阶系列，会包含更多代码示例和实战案例。您提到的Transformer架构确实值得单独开一个深度教程。</p>',
    'active',
    6,
    0,
    0,
    1,
    '0002.0001',
    false,
    true,
    0.81,
    0.5,
    'zh-CN',
    '172.16.0.8'::inet,
    'Mozilla/5.0 (iPhone; CPU iPhone OS 16_0 like Mac OS X) AppleWebKit/605.1.15',
    now() - interval '1 day 18 hours',
    now() - interval '1 day 18 hours'
);

-- 专题评论数据
INSERT INTO ak_comments (
    id,
    target_type,
    target_id,
    parent_id,
    author_id,
    author_name,
    author_avatar,
    content,
    content_html,
    status,
    like_count,
    dislike_count,
    reply_count,
    level,
    thread_path,
    is_pinned,
    is_author_reply,
    quality_score,
    sentiment_score,
    language_detected,
    ip_address,
    user_agent,
    created_at,
    updated_at
) VALUES 
-- AI专题评论
(
    '66666666-6666-6666-6666-666666666666'::uuid,
    'topic',
    'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440014'::uuid,
    'AI学习者小刘',
    'https://example.com/avatar5.jpg',
    '这个AI技术专题真的太棒了！从基础概念到前沿应用，内容覆盖得很全面。作为一个刚入门AI的新手，这个专题帮我建立了完整的知识框架。希望能继续更新更多实践案例。',
    '<p>这个AI技术专题真的太棒了！从基础概念到前沿应用，内容覆盖得很全面。作为一个刚入门AI的新手，这个专题帮我建立了<strong>完整的知识框架</strong>。希望能继续更新更多实践案例。</p>',
    'active',
    28,
    0,
    1,
    0,
    '0001',
    true,
    false,
    0.91,
    0.9,
    'zh-CN',
    '198.51.100.23'::inet,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    now() - interval '1 day 12 hours',
    now() - interval '1 day 12 hours'
),
-- 对专题评论的回复
(
    '77777777-7777-7777-7777-777777777777'::uuid,
    'topic',
    'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
    '66666666-6666-6666-6666-666666666666'::uuid,
    '550e8400-e29b-41d4-a716-446655440000'::uuid,
    '专题编辑',
    'https://example.com/avatar6.jpg',
    '@AI学习者小刘 非常感谢您的反馈！我们会继续丰富这个专题的内容，下周计划发布几个AI实际应用的案例研究。也欢迎您在学习过程中提出问题和建议！',
    '<p><span class="mention">@AI学习者小刘</span> 非常感谢您的反馈！我们会继续丰富这个专题的内容，下周计划发布几个AI实际应用的案例研究。也欢迎您在学习过程中提出问题和建议！</p>',
    'active',
    12,
    0,
    0,
    1,
    '0001.0001',
    false,
    true,
    0.87,
    0.8,
    'zh-CN',
    '10.0.0.20'::inet,
    'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36',
    now() - interval '1 day 6 hours',
    now() - interval '1 day 6 hours'
),
-- 经济专题评论
(
    '88888888-8888-8888-8888-888888888888'::uuid,
    'topic',
    'f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440015'::uuid,
    '经济分析师老王',
    'https://example.com/avatar7.jpg',
    '2025年经济展望专题的分析很到位，特别是对通胀压力和货币政策的解读。不过我觉得对新兴市场的分析还可以更深入一些，比如印度、东南亚等地区的经济发展潜力。',
    '<p>2025年经济展望专题的分析很到位，特别是对<strong>通胀压力</strong>和<strong>货币政策</strong>的解读。不过我觉得对新兴市场的分析还可以更深入一些，比如印度、东南亚等地区的经济发展潜力。</p>',
    'active',
    16,
    0,
    0,
    0,
    '0001',
    false,
    false,
    0.89,
    0.3,
    'zh-CN',
    '203.0.113.67'::inet,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    now() - interval '18 hours',
    now() - interval '18 hours'
),
-- 气候专题评论
(
    '99999999-9999-9999-9999-999999999999'::uuid,
    'topic',
    'f47ac10b-58cc-4372-a567-0e02b2c3d481'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440016'::uuid,
    '环保志愿者小张',
    'https://example.com/avatar8.jpg',
    '气候变化专题的实时追踪功能很实用，能及时了解各国的减排进展。建议增加一些普通人可以参与的环保行动指南，让更多人能够参与到气候行动中来。',
    '<p>气候变化专题的<strong>实时追踪功能</strong>很实用，能及时了解各国的减排进展。建议增加一些普通人可以参与的环保行动指南，让更多人能够参与到气候行动中来。</p>',
    'active',
    21,
    0,
    0,
    0,
    '0001',
    false,
    false,
    0.86,
    0.8,
    'zh-CN',
    '198.51.100.89'::inet,
    'Mozilla/5.0 (Android 12; Mobile; rv:94.0) Gecko/94.0 Firefox/94.0',
    now() - interval '12 hours',
    now() - interval '12 hours'
),
-- 芯片危机专题热门评论
(
    'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid,
    'topic',
    'f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid,
    NULL,
    '550e8400-e29b-41d4-a716-446655440017'::uuid,
    '半导体行业专家',
    'https://example.com/avatar9.jpg',
    '这次芯片短缺确实影响深远，不仅仅是消费电子，汽车、工业设备、医疗器械等各个行业都受到冲击。专题分析得很全面，但我觉得还应该关注一下中国台湾地区在全球半导体供应链中的关键作用。',
    '<p>这次芯片短缺确实影响深远，不仅仅是消费电子，汽车、工业设备、医疗器械等各个行业都受到冲击。专题分析得很全面，但我觉得还应该关注一下<strong>中国台湾地区</strong>在全球半导体供应链中的关键作用。</p>',
    'active',
    45,
    2,
    0,
    0,
    '0001',
    true,
    false,
    0.94,
    0.2,
    'zh-CN',
    '203.0.113.88'::inet,
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
    now() - interval '6 hours',
    now() - interval '6 hours'
);

-- ===================================================================
-- 7. 插入评论反应数据
-- ===================================================================

INSERT INTO ak_comment_reactions (
    comment_id,
    user_id,
    reaction_type,
    created_at
) VALUES 
-- AI文章评论的点赞
('11111111-1111-1111-1111-111111111111'::uuid, '550e8400-e29b-41d4-a716-446655440020'::uuid, 'like', now() - interval '2 days 20 hours'),
('11111111-1111-1111-1111-111111111111'::uuid, '550e8400-e29b-41d4-a716-446655440021'::uuid, 'like', now() - interval '2 days 18 hours'),
('11111111-1111-1111-1111-111111111111'::uuid, '550e8400-e29b-41d4-a716-446655440022'::uuid, 'like', now() - interval '2 days 15 hours'),
('22222222-2222-2222-2222-222222222222'::uuid, '550e8400-e29b-41d4-a716-446655440023'::uuid, 'like', now() - interval '2 days 10 hours'),
('22222222-2222-2222-2222-222222222222'::uuid, '550e8400-e29b-41d4-a716-446655440024'::uuid, 'like', now() - interval '2 days 8 hours'),
('33333333-3333-3333-3333-333333333333'::uuid, '550e8400-e29b-41d4-a716-446655440025'::uuid, 'like', now() - interval '2 days 4 hours'),

-- 程序员评论的反应（有争议）
('44444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440026'::uuid, 'like', now() - interval '1 day 20 hours'),
('44444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440027'::uuid, 'like', now() - interval '1 day 18 hours'),
('44444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440028'::uuid, 'dislike', now() - interval '1 day 16 hours'),

-- 专题评论的点赞
('66666666-6666-6666-6666-666666666666'::uuid, '550e8400-e29b-41d4-a716-446655440029'::uuid, 'like', now() - interval '1 day 10 hours'),
('66666666-6666-6666-6666-666666666666'::uuid, '550e8400-e29b-41d4-a716-446655440030'::uuid, 'like', now() - interval '1 day 8 hours'),
('77777777-7777-7777-7777-777777777777'::uuid, '550e8400-e29b-41d4-a716-446655440031'::uuid, 'like', now() - interval '1 day 4 hours'),

-- 芯片专题热门评论的点赞
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid, '550e8400-e29b-41d4-a716-446655440032'::uuid, 'like', now() - interval '5 hours'),
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid, '550e8400-e29b-41d4-a716-446655440033'::uuid, 'like', now() - interval '4 hours'),
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid, '550e8400-e29b-41d4-a716-446655440034'::uuid, 'dislike', now() - interval '3 hours');

-- ===================================================================
-- 8. 插入评论举报数据
-- ===================================================================

INSERT INTO ak_comment_reports (
    comment_id,
    reporter_id,
    report_type,
    report_reason,
    status,
    reviewed_by,
    reviewed_at,
    review_notes,
    action_taken,
    created_at
) VALUES 
(
    '44444444-4444-4444-4444-444444444444'::uuid,
    '550e8400-e29b-41d4-a716-446655440040'::uuid,
    'inappropriate',
    '这条评论过于技术性，不适合一般读者，可能会误导新手',
    'reviewed',
    '550e8400-e29b-41d4-a716-446655440100'::uuid,
    now() - interval '12 hours',
    '评论内容专业性较强但与主题相关，无需删除。建议鼓励作者提供更多科普性内容。',
    'no_action',
    now() - interval '1 day 8 hours'
),
(
    '88888888-8888-8888-8888-888888888888'::uuid,
    '550e8400-e29b-41d4-a716-446655440041'::uuid,
    'spam',
    '疑似推广某个分析报告，与专题讨论无关',
    'pending',
    NULL,
    NULL,
    NULL,
    NULL,
    now() - interval '10 hours'
);

-- ===================================================================
-- 9. 插入审核队列数据
-- ===================================================================

INSERT INTO ak_comment_moderation_queue (
    comment_id,
    reason,
    priority_level,
    assigned_to,
    status,
    ai_risk_score,
    ai_recommendations,
    created_at,
    assigned_at,
    completed_at
) VALUES 
(
    '44444444-4444-4444-4444-444444444444'::uuid,
    '用户举报：内容过于技术性',
    2,
    '550e8400-e29b-41d4-a716-446655440100'::uuid,
    'completed',
    0.3,
    '{"action": "keep", "confidence": 0.8, "reasons": ["professional_content", "topic_relevant"]}',
    now() - interval '1 day 8 hours',
    now() - interval '1 day 6 hours',
    now() - interval '12 hours'
),
(
    '88888888-8888-8888-8888-888888888888'::uuid,
    '用户举报：疑似垃圾内容',
    1,
    NULL,
    'pending',
    0.6,
    '{"action": "review_required", "confidence": 0.6, "reasons": ["promotional_keywords", "user_report"]}',
    now() - interval '10 hours',
    NULL,
    NULL
),
(
    'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'::uuid,
    'AI自动检测：高质量专业评论',
    0,
    NULL,
    'completed',
    0.1,
    '{"action": "approve", "confidence": 0.95, "reasons": ["high_quality", "expert_content", "positive_engagement"]}',
    now() - interval '6 hours',
    now() - interval '6 hours',
    now() - interval '5 hours 30 minutes'
);

-- ===================================================================
-- 10. 更新统计数据
-- ===================================================================

-- 更新评论的点赞/踩数统计
UPDATE ak_comments 
SET 
    like_count = (SELECT COUNT(*) FROM ak_comment_reactions WHERE comment_id = ak_comments.id AND reaction_type = 'like'),
    dislike_count = (SELECT COUNT(*) FROM ak_comment_reactions WHERE comment_id = ak_comments.id AND reaction_type = 'dislike');

-- 更新评论的回复数统计
UPDATE ak_comments 
SET reply_count = (
    SELECT COUNT(*) 
    FROM ak_comments AS replies 
    WHERE replies.parent_id = ak_comments.id AND replies.status = 'active'
);

-- 更新内容的评论统计
UPDATE ak_contents 
SET comment_count = (
    SELECT COUNT(*) 
    FROM ak_comments 
    WHERE target_type = 'content' AND target_id = ak_contents.id AND status = 'active'
)
WHERE id IN (
    SELECT DISTINCT target_id 
    FROM ak_comments 
    WHERE target_type = 'content'
);

-- 更新专题的评论统计
UPDATE ak_topics 
SET comment_count = (
    SELECT COUNT(*) 
    FROM ak_comments 
    WHERE target_type = 'topic' AND target_id = ak_topics.id AND status = 'active'
);

-- 更新专题的订阅统计
UPDATE ak_topics 
SET subscriber_count = (
    SELECT COUNT(*) 
    FROM ak_topic_subscriptions 
    WHERE topic_id = ak_topics.id
);

-- ===================================================================
-- 11. 数据验证和总结
-- ===================================================================

DO $$
DECLARE
    topic_count INTEGER;
    comment_count INTEGER;
    reaction_count INTEGER;
    subscription_count INTEGER;
    report_count INTEGER;
    moderation_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO topic_count FROM ak_topics;
    SELECT COUNT(*) INTO comment_count FROM ak_comments;
    SELECT COUNT(*) INTO reaction_count FROM ak_comment_reactions;
    SELECT COUNT(*) INTO subscription_count FROM ak_topic_subscriptions;
    SELECT COUNT(*) INTO report_count FROM ak_comment_reports;
    SELECT COUNT(*) INTO moderation_count FROM ak_comment_moderation_queue;
    
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '专题和评论系统演示数据插入完成！';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '数据统计:';
    RAISE NOTICE '- 专题数量: %', topic_count;
    RAISE NOTICE '- 评论数量: %', comment_count;
    RAISE NOTICE '- 评论反应数量: %', reaction_count;
    RAISE NOTICE '- 专题订阅数量: %', subscription_count;
    RAISE NOTICE '- 评论举报数量: %', report_count;
    RAISE NOTICE '- 审核队列数量: %', moderation_count;
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '专题类型分布:';
    RAISE NOTICE '- 系列专题: AI技术发展全景 (15篇内容, 148订阅)';
    RAISE NOTICE '- 分析专题: 2025年全球经济展望 (8篇内容, 87订阅)';
    RAISE NOTICE '- 时间轴专题: 气候变化应对策略追踪 (12篇内容, 125订阅)';
    RAISE NOTICE '- 突发专题: 全球芯片短缺危机 (6篇内容, 203订阅)';
    RAISE NOTICE '- 深度报告: 元宇宙技术发展报告 (10篇内容, 56订阅)';
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '评论系统特性:';
    RAISE NOTICE '- 多层级评论结构 (最多3层)';
    RAISE NOTICE '- 点赞和踩的反应系统';
    RAISE NOTICE '- 举报和审核机制';
    RAISE NOTICE '- 内容和专题评论支持';
    RAISE NOTICE '- AI质量评分和情感分析';
    RAISE NOTICE '- 置顶和作者回复标识';
    RAISE NOTICE '===================================================================';
END $$;

-- ===================================================================
-- 12. 示例查询
-- ===================================================================

-- 查询专题统计概览
SELECT 
    'topic_stats' as query_type,
    t.title,
    t.topic_type,
    t.status,
    t.content_count,
    t.view_count,
    t.comment_count,
    t.subscriber_count,
    t.priority_level
FROM ak_topics t
ORDER BY t.priority_level DESC, t.view_count DESC;

-- 查询评论层级结构
SELECT 
    'comment_structure' as query_type,
    c.id,
    c.level,
    c.thread_path,
    c.author_name,
    LEFT(c.content, 50) || '...' as content_preview,
    c.like_count,
    c.reply_count,
    c.created_at
FROM ak_comments c
WHERE c.target_type = 'content'
ORDER BY c.thread_path;

-- 查询热门专题评论
SELECT 
    'popular_topic_comments' as query_type,
    c.id,
    t.title as topic_title,
    c.author_name,
    LEFT(c.content, 50) || '...' as content_preview,
    c.like_count,
    c.is_pinned,
    c.created_at
FROM ak_comments c
JOIN ak_topics t ON c.target_id = t.id
WHERE c.target_type = 'topic'
ORDER BY c.like_count DESC, c.created_at DESC;

-- 查询示例 - 显示专题评论层级结构
SELECT 
    c.id,
    c.level,
    c.thread_path,
    c.author_name,
    LEFT(c.content, 50) || '...' as content_preview,
    c.like_count,
    c.reply_count,
    c.created_at
FROM ak_comments c
WHERE c.target_type = 'content'
ORDER BY c.thread_path;

-- ===================================================================
-- 13. 收藏和转发演示数据
-- ===================================================================

-- 插入收藏夹数据
INSERT INTO ak_favorite_folders (
    id,
    user_id,
    name,
    description,
    icon,
    color,
    display_order,
    is_default,
    is_public,
    item_count
) VALUES 
('d1111111-1111-1111-1111-111111111111'::uuid, '550e8400-e29b-41d4-a716-446655440010'::uuid, '默认收藏', '默认收藏夹', 'star', '#FFD700', 0, true, false, 0),
('d2222222-2222-2222-2222-222222222222'::uuid, '550e8400-e29b-41d4-a716-446655440010'::uuid, 'AI技术学习', '收藏AI相关的文章和专题', 'cpu', '#4CAF50', 1, false, true, 0),
('d3333333-3333-3333-3333-333333333333'::uuid, '550e8400-e29b-41d4-a716-446655440011'::uuid, '经济资讯', '经济相关内容收藏', 'trending-up', '#2196F3', 0, false, false, 0),
('d4444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440012'::uuid, '技术深度', '深度技术文章合集', 'code', '#9C27B0', 0, false, true, 0);

-- 插入收藏数据
INSERT INTO ak_content_favorites (
    user_id,
    target_type,
    target_id,
    folder_id,
    notes,
    tags,
    is_public
) VALUES 
-- 收藏AI专题
('550e8400-e29b-41d4-a716-446655440010'::uuid, 'topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, 'd2222222-2222-2222-2222-222222222222'::uuid, '非常全面的AI技术专题，值得反复学习', ARRAY['AI', '学习', '技术'], true),
-- 收藏内容
('550e8400-e29b-41d4-a716-446655440010'::uuid, 'content', '123e4567-e89b-12d3-a456-426614174000'::uuid, 'd2222222-2222-2222-2222-222222222222'::uuid, 'AI发展史，很好的入门文章', ARRAY['入门', '历史'], false),
('550e8400-e29b-41d4-a716-446655440011'::uuid, 'topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d480'::uuid, 'd3333333-3333-3333-3333-333333333333'::uuid, '2025经济分析很专业', ARRAY['经济', '分析'], false),
('550e8400-e29b-41d4-a716-446655440012'::uuid, 'content', '123e4567-e89b-12d3-a456-426614174001'::uuid, 'd4444444-4444-4444-4444-444444444444'::uuid, 'ChatGPT技术细节', ARRAY['ChatGPT', '技术'], true);

-- 插入转发分享数据
INSERT INTO ak_content_shares (
    id,
    user_id,
    target_type,
    target_id,
    share_type,
    share_platform,
    share_content,
    share_title,
    original_author_id,
    share_level,
    reach_count,
    click_count,
    like_count,
    reshare_count
) VALUES 
-- 分享AI专题到微信
('s1111111-1111-1111-1111-111111111111'::uuid, '550e8400-e29b-41d4-a716-446655440010'::uuid, 'topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, 'forward', 'wechat', '强烈推荐这个AI技术专题！从基础到前沿都有涵盖，非常适合想了解AI的朋友们。', 'AI技术发展全景 - 值得一看', '550e8400-e29b-41d4-a716-446655440000'::uuid, 0, 156, 89, 23, 8),
-- 转发内容到平台内
('s2222222-2222-2222-2222-222222222222'::uuid, '550e8400-e29b-41d4-a716-446655440011'::uuid, 'content', '123e4567-e89b-12d3-a456-426614174000'::uuid, 'quote', 'internal', '这篇AI发展史写得真好，推荐给正在学习AI的朋友。特别是对ChatGPT的技术原理解释得很清楚。', NULL, '550e8400-e29b-41d4-a716-446655440000'::uuid, 0, 67, 34, 12, 3),
-- 分享到微博
('s3333333-3333-3333-3333-333333333333'::uuid, '550e8400-e29b-41d4-a716-446655440012'::uuid, 'topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, 'link', 'weibo', '全球芯片短缺危机最新分析，影响深远！', '芯片危机深度解析', '550e8400-e29b-41d4-a716-446655440003'::uuid, 0, 234, 123, 45, 12),
-- 二级转发
('s4444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440013'::uuid, 'topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, 'repost', 'internal', '转发一下，这个AI专题真的很棒！', NULL, '550e8400-e29b-41d4-a716-446655440000'::uuid, 1, 45, 28, 7, 2),
-- 分享到邮件
('s5555555-5555-5555-5555-555555555555'::uuid, '550e8400-e29b-41d4-a716-446655440014'::uuid, 'content', '123e4567-e89b-12d3-a456-426614174010'::uuid, 'link', 'email', '分享一篇很有价值的经济分析文章', '2025年经济展望分析', '550e8400-e29b-41d4-a716-446655440001'::uuid, 0, 12, 8, 2, 0);

-- 插入收藏夹分享数据
INSERT INTO ak_favorite_shares (
    folder_id,
    shared_by,
    share_type,
    share_code,
    view_count,
    clone_count,
    is_active
) VALUES 
('d2222222-2222-2222-2222-222222222222'::uuid, '550e8400-e29b-41d4-a716-446655440010'::uuid, 'public', 'AI2024', 45, 8, true),
('d4444444-4444-4444-4444-444444444444'::uuid, '550e8400-e29b-41d4-a716-446655440012'::uuid, 'link', 'TECH567', 23, 3, true);

-- 插入分享统计数据
INSERT INTO ak_share_analytics (
    target_type,
    target_id,
    date_recorded,
    platform,
    share_count,
    unique_sharers,
    total_reach,
    total_clicks,
    viral_coefficient
) VALUES 
('topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, CURRENT_DATE, 'wechat', 15, 12, 456, 234, 1.8),
('topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, CURRENT_DATE, 'internal', 8, 7, 123, 67, 1.2),
('topic', 'f47ac10b-58cc-4372-a567-0e02b2c3d482'::uuid, CURRENT_DATE, 'weibo', 23, 18, 678, 345, 2.1),
('content', '123e4567-e89b-12d3-a456-426614174000'::uuid, CURRENT_DATE, 'internal', 12, 10, 234, 134, 1.5);

-- 更新收藏夹项目统计
UPDATE ak_favorite_folders 
SET item_count = (
    SELECT COUNT(*) 
    FROM ak_content_favorites 
    WHERE folder_id = ak_favorite_folders.id
);

RAISE NOTICE '收藏和转发演示数据插入完成！';

RAISE NOTICE '演示数据初始化完成！可以使用上述查询来验证数据结构。';