import json
import os
import sqlite3
import time
from pathlib import Path

DB = "/root/aza-app/data/stripe_webhook.sqlite"
PID = "prac_883ddc21fb6a"
SUB = "wc_sub_1502"

print("=== 1. ai_topup_settings ===")
con = sqlite3.connect(f"file:{DB}?mode=ro", uri=True)
row = con.execute(
    """
    SELECT auto_topup_enabled, stripe_customer_id, default_payment_method_id,
           trigger_below_percent, monthly_limit_chf, topup_amount_chf, internal_credit_usd
    FROM ai_topup_settings WHERE practice_id=?
    """,
    (PID,),
).fetchone()
if row:
    print("enabled=", row[0])
    print("cus_ok=", bool(row[1] and str(row[1]).startswith("cus_")))
    print("pm_ok=", bool(row[2] and str(row[2]).startswith("pm_")))
    print("trigger=", row[3], "monthly_limit=", row[4], "chf=", row[5], "usd=", row[6])

print("\n=== 3. ledger counts ===")
for r in con.execute(
    "SELECT event_type, status, COUNT(1), COALESCE(SUM(amount_paid_chf),0) "
    "FROM ai_credit_ledger WHERE practice_id=? GROUP BY event_type, status",
    (PID,),
):
    print("ledger", r)

now = int(time.time())
day_ago = now - 86400
auto_24h = con.execute(
    """
    SELECT COUNT(1) FROM ai_credit_ledger
    WHERE practice_id=? AND event_type='auto_topup_purchase'
      AND status='succeeded' AND created_at >= ?
    """,
    (PID, day_ago),
).fetchone()[0]
print("auto_topup_succeeded_last_24h=", auto_24h)

print("\n=== license / budget inputs ===")
lic = con.execute(
    "SELECT subscription_id, lookup_key, customer_email, current_period_start, current_period_end "
    "FROM licenses WHERE practice_id=? LIMIT 1",
    (PID,),
).fetchone()
print("license_sub=", lic[0] if lic else None, "lookup=", lic[1] if lic else None)

used = con.execute(
    "SELECT COALESCE(SUM(estimated_cost_usd),0) FROM ai_usage_events "
    "WHERE subscription_id=? AND status='success'",
    (SUB,),
).fetchone()[0]
print("total_usage_usd=", round(float(used or 0), 4))

topups = con.execute(
    """
    SELECT COALESCE(SUM(amount_internal_usd),0) FROM ai_credit_ledger
    WHERE practice_id=? AND status='succeeded'
      AND event_type IN ('topup_purchase','auto_topup_purchase','adjustment','refund')
    """,
    (PID,),
).fetchone()[0]
print("extra_topups_usd=", round(float(topups or 0), 4))

con.close()

print("\n=== 4. ENV (names/values non-secret) ===")
env_path = Path("/root/aza-app/deploy/.env")
for key in ("AZA_AI_TOPUP_DRY_RUN", "AZA_AI_TOPUP_ALLOW_LIVE", "AZA_AI_AUTO_TOPUP_ENABLED"):
    val = None
    if env_path.exists():
        for line in env_path.read_text(encoding="utf-8").splitlines():
            if line.strip().startswith(key + "="):
                val = line.split("=", 1)[1].strip().strip('"').strip("'")
                break
    print(key, "=", val)