#!/usr/bin/env python3
"""One-time fix: invalidate shadow billing charges for cycles with 0 posts.

Marks billing_log entries as 'invalid_empty_scan' where the scan returned
no data (Apify 403/402/empty). Then prints the corrected billing summary.
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent / "src"))

from agent_samochodowy.config import Settings


def main() -> None:
    settings = Settings()
    db = settings.db_path
    conn = sqlite3.connect(db)

    # --- Step 1: Show current state ---
    total = conn.execute(
        "SELECT COUNT(*), COALESCE(SUM(tokens), 0) FROM billing_log WHERE action_type = 'scan'"
    ).fetchone()
    print(f"BEFORE: {total[0]} scan charges, {total[1]:.1f} tokens total")

    empty_cycles = conn.execute(
        "SELECT id FROM cycle_log WHERE posts_fetched = 0"
    ).fetchall()
    empty_ids = [str(r[0]) for r in empty_cycles]

    if not empty_ids:
        print("No empty cycles found. Nothing to fix.")
        conn.close()
        return

    placeholders = ",".join("?" for _ in empty_ids)
    bad = conn.execute(
        f"SELECT COUNT(*), COALESCE(SUM(tokens), 0) FROM billing_log "
        f"WHERE cycle_id IN ({placeholders}) AND action_type = 'scan' "
        f"AND result != 'invalid_empty_scan'",
        empty_ids,
    ).fetchone()
    print(f"TO INVALIDATE: {bad[0]} charges, {bad[1]:.1f} tokens from {len(empty_ids)} empty cycles")

    # --- Step 2: Mark invalid ---
    conn.execute(
        f"UPDATE billing_log SET result = 'invalid_empty_scan' "
        f"WHERE cycle_id IN ({placeholders}) AND action_type = 'scan' "
        f"AND result != 'invalid_empty_scan'",
        empty_ids,
    )
    conn.commit()

    # --- Step 3: Show corrected state ---
    valid_scans = conn.execute(
        "SELECT COUNT(*), COALESCE(SUM(tokens), 0) FROM billing_log "
        "WHERE action_type = 'scan' AND result != 'invalid_empty_scan'"
    ).fetchone()
    valid_leads = conn.execute(
        "SELECT COUNT(*), COALESCE(SUM(tokens), 0) FROM billing_log "
        "WHERE action_type = 'lead' AND result != 'invalid_empty_scan'"
    ).fetchone()
    invalid = conn.execute(
        "SELECT COUNT(*), COALESCE(SUM(tokens), 0) FROM billing_log "
        "WHERE result = 'invalid_empty_scan'"
    ).fetchone()

    print()
    print("=== CORRECTED BILLING SUMMARY ===")
    print(f"  Valid scans:       {valid_scans[0]} charges, {valid_scans[1]:.1f} tokens")
    print(f"  Valid leads:       {valid_leads[0]} charges, {valid_leads[1]:.1f} tokens")
    print(f"  REAL total:        {valid_scans[1] + valid_leads[1]:.1f} tokens")
    print(f"  Invalidated:       {invalid[0]} charges, {invalid[1]:.1f} tokens (empty scans)")
    print()

    # --- Step 4: Per-day breakdown ---
    print("=== PER-DAY BREAKDOWN (valid only) ===")
    rows = conn.execute("""
        SELECT DATE(timestamp) as day,
               SUM(CASE WHEN action_type='scan' THEN tokens ELSE 0 END) as scan_tokens,
               SUM(CASE WHEN action_type='lead' THEN tokens ELSE 0 END) as lead_tokens,
               COUNT(*) as charges
        FROM billing_log
        WHERE result NOT IN ('invalid_empty_scan', 'HALTED')
        GROUP BY day ORDER BY day
    """).fetchall()
    for r in rows:
        print(f"  {r[0]}: scans={r[1]:.1f} leads={r[2]:.1f} total={r[1]+r[2]:.1f} ({r[3]} charges)")

    conn.close()
    print("\nDone.")


if __name__ == "__main__":
    main()