-- 插入知乎"中国梦"测试数据

-- 1. 先创建一个爬虫任务
INSERT INTO crawler_tasks (id, spider_name, schedule_type, status, priority, config)
VALUES
  ('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'once', 'completed', 5, '{"keyword": "中国梦"}'::jsonb)
ON CONFLICT DO NOTHING;

-- 2. 插入爬取到的数据
INSERT INTO crawled_data (task_id, source, url, title, content, author, metadata, fingerprint) VALUES
('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'https://www.zhihu.com/question/q001', '如何理解中国梦的深刻内涵？', '中国梦是实现中华民族伟大复兴的梦想，凝聚着几代中国人的夙愿，体现了中华民族和中国人民的整体利益...', '知乎用户A', '{"votes": 1250, "comments": 89, "keyword": "中国梦"}'::jsonb, encode(sha256('q001'::bytea), 'hex')),
('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'https://www.zhihu.com/question/q002', '中国梦与个人梦想的关系', '中国梦归根到底是人民的梦，必须紧紧依靠人民来实现。每个人的前途命运都与国家和民族的前途命运紧密相连...', '知乎用户B', '{"votes": 856, "comments": 45, "keyword": "中国梦"}'::jsonb, encode(sha256('q002'::bytea), 'hex')),
('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'https://www.zhihu.com/question/q003', '实现中国梦需要哪些努力？', '实现中国梦必须走中国道路，弘扬中国精神，凝聚中国力量。这是实现中国梦的根本路径...', '知乎用户C', '{"votes": 632, "comments": 28, "keyword": "中国梦"}'::jsonb, encode(sha256('q003'::bytea), 'hex')),
('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'https://www.zhihu.com/question/q004', '青年一代在实现中国梦中的作用', '青年是实现中国梦的生力军。当代青年应该树立远大理想，勇于担当时代责任，在实现中国梦的生动实践中放飞青春梦想...', '知乎用户D', '{"votes": 789, "comments": 52, "keyword": "中国梦"}'::jsonb, encode(sha256('q004'::bytea), 'hex')),
('11111111-1111-1111-1111-111111111111'::uuid, 'zhihu', 'https://www.zhihu.com/question/q005', '中国梦的历史维度与现实意义', '从历史维度看，中国梦承载着中华民族的百年夙愿。从现实意义看，它为当代中国发展指明了方向...', '知乎用户E', '{"votes": 445, "comments": 31, "keyword": "中国梦"}'::jsonb, encode(sha256('q005'::bytea), 'hex'))
ON CONFLICT (fingerprint) DO NOTHING;

-- 3. 输出统计信息
SELECT '=========== 爬取统计 ===========' as "报告";
SELECT
    '知乎 - 中国梦' as "爬取主题",
    COUNT(*) as "数据条数",
    SUM((metadata->>'votes')::int) as "总点赞数",
    SUM((metadata->>'comments')::int) as "总评论数",
    ROUND(AVG((metadata->>'votes')::int), 0) as "平均点赞"
FROM crawled_data
WHERE source = 'zhihu';

SELECT '=========== 数据详情 ===========' as "报告";
SELECT
    ROW_NUMBER() OVER (ORDER BY (metadata->>'votes')::int DESC) as "排名",
    title as "标题",
    author as "作者",
    (metadata->>'votes')::int as "点赞",
    (metadata->>'comments')::int as "评论"
FROM crawled_data
WHERE source = 'zhihu'
ORDER BY (metadata->>'votes')::int DESC;