#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""check_yingquan_db.py —— 库内体检 + 附件列抽查"""
import sqlite3

c = sqlite3.connect("/root/search.db", timeout=20)
c.execute("PRAGMA busy_timeout=20000")
S = "颍泉区人民政府-公告公示"

for r in c.execute("SELECT site_name, script_name, COUNT(*) FROM gov_raw WHERE site_name=? GROUP BY site_name, script_name", (S,)):
    print("site_name =", r[0])
    print("script    =", r[1])
    print("条数      =", r[2])
print("日期范围  =", c.execute("SELECT MIN(publish_date)||' ~ '||MAX(publish_date) FROM gov_raw WHERE site_name=?", (S,)).fetchone()[0])
print("空正文    =", c.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=? AND (content IS NULL OR TRIM(content)='')", (S,)).fetchone()[0])
print("重复URL   =", c.execute("SELECT COUNT(*) FROM (SELECT page_url FROM gov_raw WHERE site_name=? GROUP BY page_url HAVING COUNT(*)>1)", (S,)).fetchone()[0])
print("平均正文  =", int(c.execute("SELECT AVG(LENGTH(content)) FROM gov_raw WHERE site_name=?", (S,)).fetchone()[0] or 0))
print("含<p>     =", c.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=? AND content LIKE '%<p%'", (S,)).fetchone()[0])
print("含表格    =", c.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=? AND content LIKE '%<table%'", (S,)).fetchone()[0])
print("有附件列  =", c.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name=? AND attachments IS NOT NULL AND attachments!=''", (S,)).fetchone()[0])
print("FTS同步   =", c.execute("SELECT COUNT(*) FROM gov_search WHERE rowid IN (SELECT id FROM gov_raw WHERE site_name=?)", (S,)).fetchone()[0])
print("group_name=", c.execute("SELECT DISTINCT group_name FROM gov_raw WHERE site_name=?", (S,)).fetchall())
print("category  =", c.execute("SELECT DISTINCT category FROM gov_raw WHERE site_name=?", (S,)).fetchall())
print()
print("=== 附件列样例（前 3 条的 attachments）===")
for r in c.execute("SELECT title, attachments FROM gov_raw WHERE site_name=? AND attachments IS NOT NULL AND attachments!='' LIMIT 3", (S,)):
    print(" ", r[0][:34])
    print("   ", (r[1] or "")[:220])
print()
print("=== 日期分布（按月）===")
for r in c.execute("SELECT substr(publish_date,1,7) m, COUNT(*) FROM gov_raw WHERE site_name=? GROUP BY m ORDER BY m DESC LIMIT 14", (S,)):
    print("  ", r[0], r[1])
c.close()