#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
服务器端: 按 script_name AND site_name 双维度统计 gov_raw 发布日期窗口计数 (单次扫描)
输出: /tmp/disdb_stats.json
  {
    "by_script": {script_name: {total, d7, d30, d180, d365}},
    "by_site":   {site_name: {total, d7, d30, d180, d365, script_name}}
  }
日期窗口: publish_date >= today - X 天 (动态)
"""
import json
import sqlite3
from datetime import date, timedelta

DB = '/root/search.db'
today = date.today()
d7 = (today - timedelta(days=7)).isoformat()
d30 = (today - timedelta(days=30)).isoformat()
d180 = (today - timedelta(days=180)).isoformat()
d365 = (today - timedelta(days=365)).isoformat()

conn = sqlite3.connect(DB, timeout=60)
conn.execute('PRAGMA busy_timeout=60000')
conn.execute('PRAGMA query_only=ON')

# 单次全表扫描: 按 script_name + site_name 分组, 一个 GROUP BY 算 5 窗口
rows = conn.execute(
    """
    SELECT script_name, site_name,
           COUNT(*) AS total,
           SUM(CASE WHEN publish_date >= ? THEN 1 ELSE 0 END) AS d7,
           SUM(CASE WHEN publish_date >= ? THEN 1 ELSE 0 END) AS d30,
           SUM(CASE WHEN publish_date >= ? THEN 1 ELSE 0 END) AS d180,
           SUM(CASE WHEN publish_date >= ? THEN 1 ELSE 0 END) AS d365
    FROM gov_raw
    WHERE script_name != '' AND publish_date IS NOT NULL AND publish_date != ''
    GROUP BY script_name, site_name
    """,
    (d7, d30, d180, d365)
).fetchall()

by_script = {}
by_site = {}
for sn, site, total, d7c, d30c, d180c, d365c in rows:
    stat = {'total': total, 'd7': d7c or 0, 'd30': d30c or 0, 'd180': d180c or 0, 'd365': d365c or 0}
    key_sn = sn
    key_site = site or ''
    if key_sn not in by_script:
        by_script[key_sn] = {'total': 0, 'd7': 0, 'd30': 0, 'd180': 0, 'd365': 0}
    for k in stat:
        by_script[key_sn][k] += stat[k]
    by_site[key_site] = dict(stat)
    by_site[key_site]['script_name'] = sn

conn.close()
out = {'date': today.isoformat(), 'by_script': by_script, 'by_site': by_site}
with open('/tmp/disdb_stats.json', 'w', encoding='utf-8') as f:
    json.dump(out, f, ensure_ascii=False)

print(f'today={today.isoformat()} | scripts={len(by_script)} | sites={len(by_site)} | total={sum(v["total"] for v in by_script.values())}')