#!/usr/bin/env python3
"""Persist AGI HUNT report runs in a local SQLite database."""

import argparse
import json
import sqlite3
from pathlib import Path


SCHEMA = """
CREATE TABLE IF NOT EXISTS agihunt_ai_reports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  report_day TEXT NOT NULL,
  schedule_day TEXT NOT NULL,
  run_slot TEXT NOT NULL,
  fetched_at TEXT NOT NULL,
  generated_at TEXT,
  html_url TEXT,
  raw_json TEXT,
  summary_markdown TEXT,
  wecom_sent INTEGER NOT NULL DEFAULT 0,
  wecom_response TEXT,
  error_code TEXT,
  error_message TEXT,
  UNIQUE(schedule_day, run_slot)
);
"""


def read_text(path):
    return Path(path).read_text(encoding="utf-8") if path else None


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--database", required=True)
    parser.add_argument("--report-json")
    parser.add_argument("--summary-file")
    parser.add_argument("--report-day", required=True)
    parser.add_argument("--schedule-day", required=True)
    parser.add_argument("--run-slot", required=True, choices=("morning", "noon", "evening"))
    parser.add_argument("--fetched-at", required=True)
    parser.add_argument("--wecom-response")
    parser.add_argument("--wecom-sent", action="store_true")
    parser.add_argument("--error-code")
    parser.add_argument("--error-message")
    args = parser.parse_args()

    raw_text = read_text(args.report_json)
    raw = json.loads(raw_text) if raw_text else {}
    summary = read_text(args.summary_file)
    database = Path(args.database)
    database.parent.mkdir(parents=True, exist_ok=True)

    with sqlite3.connect(database) as connection:
        connection.execute(SCHEMA)
        connection.execute(
            """
            INSERT INTO agihunt_ai_reports (
              report_day, schedule_day, run_slot, fetched_at, generated_at,
              html_url, raw_json, summary_markdown, wecom_sent, wecom_response,
              error_code, error_message
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(schedule_day, run_slot) DO UPDATE SET
              report_day=excluded.report_day,
              fetched_at=excluded.fetched_at,
              generated_at=excluded.generated_at,
              html_url=excluded.html_url,
              raw_json=excluded.raw_json,
              summary_markdown=excluded.summary_markdown,
              wecom_sent=excluded.wecom_sent,
              wecom_response=excluded.wecom_response,
              error_code=excluded.error_code,
              error_message=excluded.error_message
            """,
            (
                args.report_day,
                args.schedule_day,
                args.run_slot,
                args.fetched_at,
                raw.get("generated_at"),
                raw.get("html_url"),
                raw_text,
                summary,
                1 if args.wecom_sent else 0,
                args.wecom_response,
                args.error_code,
                args.error_message,
            ),
        )
        connection.commit()

    print("agihunt report stored")


if __name__ == "__main__":
    main()