"""Prepaid wallet (pay as you go): customers add money through PhonePe; every job is paid from the balance.

Profit on every job
    cost   = GPU seconds x the Space's hourly price x COST_IDLE_FACTOR      (the job's share of the always-on GPU)
           + what the language model (Groq) charged for the job
    price  = cost x (1 + PRICE_MARGIN %)  grossed up so that, after GST (PRICE_GST %) and the gateway's fee
             (PAY_FEE_PCT % + GST on that fee), what stays with the business is exactly cost x (1 + margin):
                 price x (1 / (1 + GST) - fee) = cost x (1 + margin)          (never below PRICE_MIN_INR)
Before a job starts, an amount is RESERVED from the balance (about twice the usual price of such a job). The job
then runs inside that amount: its Groq spending is capped and it stops starting new rounds at a deadline, so its
cost cannot exceed what the reservation covers. When it ends, the actual price is charged (never more than the
reservation) and the rest is released. A job that fails is not charged.

All money is kept in paise (integers).
"""
from __future__ import annotations

import math
import os
import secrets
import threading
import time
from datetime import datetime, timezone

import auth as A
import db


def _f(k, d):
    try:
        return float(os.getenv(k, str(d)))
    except ValueError:
        return float(d)


GPU_HOUR_USD = _f("COST_GPU_HOUR_USD", 0.40)          # Hugging Face Nvidia T4 small
IDLE_FACTOR = max(1.0, _f("COST_IDLE_FACTOR", 5))     # each busy GPU second carries this many seconds of the bill
MARGIN = _f("PRICE_MARGIN", 300)                      # profit, % of cost
GST = _f("PRICE_GST", 18)
FEE = _f("PAY_FEE_PCT", 2.0)                          # payment gateway fee, % of the amount paid ...
FEE_GST = _f("PAY_FEE_GST", 18)                       # ... plus GST on that fee
USD_INR = _f("PRICE_USD_INR", 85)
MIN_INR = _f("PRICE_MIN_INR", 10)
HOLD_X = _f("WALLET_HOLD_X", 2.0)                     # reservation = this x the usual price of the job
TOPUPS = [int(x) for x in os.getenv("WALLET_TOPUPS", "100,250,500,1000").split(",") if x.strip()]
TOPUP_MIN, TOPUP_MAX = int(_f("WALLET_TOPUP_MIN", 50)), int(_f("WALLET_TOPUP_MAX", 20000))
OVERHEAD_S = _f("WALLET_OVERHEAD_S", 120)             # work after the deadline (final check, proofread, report)
_OVERHEAD = {"detector": 20, "proofread": 60}           # ... much less for a detector report or a proofread

_KEEP = 1 / (1 + GST / 100) - FEE / 100 * (1 + FEE_GST / 100)   # share of the price that stays with the business
_lock = threading.RLock()


# ---------------------------------------------------------------------------------------------------- prices
def gpu_usd(seconds):
    return max(0.0, float(seconds or 0)) * GPU_HOUR_USD / 3600 * IDLE_FACTOR


def price_paise(cost_usd, margin=None):
    """The price (incl. GST) that leaves cost x (1 + margin) after GST and the gateway fee."""
    m = MARGIN if margin is None else float(margin)
    inr = max(0.0, float(cost_usd)) * (1 + m / 100) / _KEEP * USD_INR
    return int(max(MIN_INR, math.ceil(inr))) * 100


def cost_covered(paise):
    """The largest job cost (US$) a price of `paise` still makes the full margin on."""
    return paise / 100 / USD_INR * _KEEP / (1 + MARGIN / 100)


def breakdown(seconds, llm_usd):
    gpu = gpu_usd(seconds)
    cost = gpu + max(0.0, float(llm_usd or 0))
    p = price_paise(cost)
    inr = p / 100
    gst = inr - inr / (1 + GST / 100)
    fee = inr * FEE / 100 * (1 + FEE_GST / 100)
    return dict(seconds=round(float(seconds or 0), 1), gpu_usd=gpu, llm_usd=float(llm_usd or 0), cost_usd=cost,
                paise=p, inr=inr, gst_inr=gst, fee_inr=fee, profit_inr=inr - gst - fee - cost * USD_INR)


# usual work of a job: (seconds, Groq US$) for a document of `words` words, measured on the live Space
_USUAL = {
    # fast engine: 1,830 words ~ 90 s, ~$0.20; a 22,000-word thesis part ~ 590 s, ~$1.10 (with a checker report)
    "fast": lambda w: (40 + 0.03 * w, min(0.11 * w, 200 + 0.05 * w) / 1000),
    "smart": lambda w: (50 + 0.04 * w, 0.012 * w / 1000),     # Smart engine: 4,044 words ~ 210 s, ~$0.04
    "detector": lambda w: (5 + 0.003 * w, 0.0),
    "proofread": lambda w: (20 + 0.01 * w, 0.004 * w / 1000),
    "other": lambda w: (30 + 0.03 * w, 0.02 * w / 1000),
    "turnitin": lambda w: (0, _f("REILAA_COST_USD", 3.99)),   # one Reilaa report credit, whatever the length
}
FIXED = {"turnitin"}                                    # a known price per run: reserved and charged exactly


def usual(kind, words):
    secs, llm = _USUAL.get(kind, _USUAL["other"])(max(0, int(words or 0)))
    return secs, llm


# margin of fixed-price runs: a Turnitin report is bought for $3.99, so the general margin makes it expensive
MARGINS = {"turnitin": _f("REILAA_MARGIN", MARGIN)}


# a set price per run (₹, incl. GST); never below the break-even price, so every run stays in profit
SET_PRICE = {"turnitin": _f("REILAA_PRICE_INR", 550)}


def usual_price(kind, words):
    secs, llm = usual(kind, words)
    if SET_PRICE.get(kind):
        floor = price_paise(gpu_usd(secs) + llm, 0)    # cost + GST + fee, no profit
        return max(int(round(SET_PRICE[kind])) * 100, floor)
    return price_paise(gpu_usd(secs) + llm, MARGINS.get(kind))


def plan_job(kind, words, available_paise, time_budget_s):
    """What to reserve and the limits the job runs within. Returns dict(hold, deadline_s, groq_cap) or
    dict(short=paise needed) when the balance cannot cover the job."""
    secs, llm = usual(kind, words)
    need = max(usual_price(kind, words), int(MIN_INR * 100))
    if kind in FIXED:
        if available_paise < need:
            return dict(short=need, usual=need)
        return dict(hold=need, deadline_s=float(time_budget_s), groq_cap=0.0, usual=need, tail_s=0)
    deadline = min(float(time_budget_s), max(60.0, 2 * secs + 60))      # twice the usual time, plus a minute
    hold = min(int(available_paise), max(int(need * HOLD_X), need))
    tail = _OVERHEAD.get(kind, OVERHEAD_S)
    while True:
        cap = cost_covered(hold) - gpu_usd(deadline + tail)
        if cap >= 1.5 * llm or hold >= available_paise:
            break
        hold = min(int(available_paise), hold + need)
    if available_paise < need or cap < llm:
        floor = price_paise(gpu_usd(deadline + tail) + 1.5 * llm)
        return dict(short=max(need, floor), usual=need)
    return dict(hold=hold, deadline_s=deadline, groq_cap=max(0.0, cap), usual=need, tail_s=tail)


def inr(paise):
    v = paise / 100
    return f"₹{v:,.0f}" if abs(v - round(v)) < 0.005 else f"₹{v:,.2f}"


# ---------------------------------------------------------------------------------------------------- ledger
def _db():
    con = A._db()
    if db.IS_MYSQL:
        return con
    con.execute("CREATE TABLE IF NOT EXISTS wallet_ledger (id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "ts TEXT DEFAULT CURRENT_TIMESTAMP, email TEXT, paise INTEGER, kind TEXT, ref TEXT UNIQUE, "
                "note TEXT, tool TEXT, words INTEGER, seconds REAL, llm_usd REAL, cost_usd REAL)")
    con.execute("CREATE TABLE IF NOT EXISTS wallet_holds (ref TEXT PRIMARY KEY, ts TEXT DEFAULT CURRENT_TIMESTAMP, "
                "email TEXT, paise INTEGER, tool TEXT, status TEXT DEFAULT 'active')")
    con.execute("CREATE TABLE IF NOT EXISTS wallet_topups (order_id TEXT PRIMARY KEY, "
                "ts TEXT DEFAULT CURRENT_TIMESTAMP, email TEXT, paise INTEGER, state TEXT DEFAULT 'CREATED', "
                "done_ts TEXT, checks INTEGER DEFAULT 0)")
    con.execute("CREATE TABLE IF NOT EXISTS report_orders (order_id TEXT PRIMARY KEY, "
                "ts TEXT DEFAULT CURRENT_TIMESTAMP, email TEXT, paise INTEGER, units INTEGER DEFAULT 1, "
                "file_key TEXT, state TEXT DEFAULT 'CREATED', done_ts TEXT, used_ts TEXT, refund_paise INTEGER DEFAULT 0)")
    con.execute("CREATE TABLE IF NOT EXISTS job_costs (id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "ts TEXT DEFAULT CURRENT_TIMESTAMP, email TEXT, tool TEXT, words INTEGER, seconds REAL, "
                "llm_usd REAL, cost_usd REAL, charged_paise INTEGER DEFAULT 0, how TEXT)")
    return con


def _now():
    return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")


def balance(email):
    with _db() as con:
        return int(con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_ledger WHERE email=?",
                               ((email or "").lower(),)).fetchone()[0])


def held(email):
    with _db() as con:
        return int(con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_holds WHERE email=? AND status='active'",
                               ((email or "").lower(),)).fetchone()[0])


def available(email):
    return balance(email) - held(email)


def reserve(email, paise, tool=""):
    """Reserve money for a job; returns the reservation's reference, or None if the balance is short."""
    e = (email or "").lower()
    with _lock, _db() as con:
        bal = con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_ledger WHERE email=?", (e,)).fetchone()[0]
        hold = con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_holds WHERE email=? AND status='active'",
                           (e,)).fetchone()[0]
        if bal - hold < paise or paise <= 0:
            return None
        ref = "J" + secrets.token_hex(6).upper()
        con.execute("INSERT INTO wallet_holds (ref, email, paise, tool) VALUES (?,?,?,?)", (ref, e, int(paise), tool))
    A.request_sync()
    return ref


def settle(ref, seconds, llm_usd, words=0, name=""):
    """Charge a finished job its actual price (at most what was reserved) and release the rest. Returns the
    breakdown with the amount charged."""
    b = breakdown(seconds, llm_usd)
    with _lock, _db() as con:
        row = con.execute("SELECT email, paise, tool, status FROM wallet_holds WHERE ref=?", (ref,)).fetchone()
        if not row or row[3] != "active":
            return dict(b, charged=0)
        email, hold, tool = row[0], int(row[1]), row[2]
        charged = min(b["paise"], hold)
        con.execute("UPDATE wallet_holds SET status='settled' WHERE ref=?", (ref,))
        con.execute("INSERT INTO wallet_ledger (email, paise, kind, ref, note, tool, words, seconds, llm_usd, "
                    "cost_usd) VALUES (?,?,?,?,?,?,?,?,?,?)",
                    (email, -charged, "charge", ref, (name or "")[:120], tool, int(words or 0), b["seconds"],
                     b["llm_usd"], b["cost_usd"]))
    A.request_sync()
    if b["paise"] > hold:
        print(f"[wallet] {ref}: price {inr(b['paise'])} above the reservation {inr(hold)}; charged {inr(hold)}",
              flush=True)
    return dict(b, charged=charged)


def settle_fixed(ref, cost_usd, words=0, name=""):
    """Charge a fixed-price run (e.g. a Turnitin report) exactly what was reserved for it."""
    with _lock, _db() as con:
        row = con.execute("SELECT email, paise, tool, status FROM wallet_holds WHERE ref=?", (ref,)).fetchone()
        if not row or row[3] != "active":
            return dict(charged=0)
        email, hold, tool = row[0], int(row[1]), row[2]
        con.execute("UPDATE wallet_holds SET status='settled' WHERE ref=?", (ref,))
        con.execute("INSERT INTO wallet_ledger (email, paise, kind, ref, note, tool, words, seconds, llm_usd, "
                    "cost_usd) VALUES (?,?,?,?,?,?,?,?,?,?)",
                    (email, -hold, "charge", ref, (name or "")[:120], tool, int(words or 0), 0, float(cost_usd),
                     float(cost_usd)))
    A.request_sync()
    return dict(charged=hold)


def settle_amount(ref, paise, cost_usd=0.0, words=0, name=""):
    """Charge a reserved run `paise` (at most what was reserved) and release the rest; e.g. a Turnitin report in parts
    where only some parts were produced."""
    with _lock, _db() as con:
        row = con.execute("SELECT email, paise, tool, status FROM wallet_holds WHERE ref=?", (ref,)).fetchone()
        if not row or row[3] != "active":
            return dict(charged=0)
        email, hold, tool = row[0], int(row[1]), row[2]
        charged = max(0, min(int(paise), hold))
        con.execute("UPDATE wallet_holds SET status='settled' WHERE ref=?", (ref,))
        if charged:
            con.execute("INSERT INTO wallet_ledger (email, paise, kind, ref, note, tool, words, seconds, llm_usd, "
                        "cost_usd) VALUES (?,?,?,?,?,?,?,?,?,?)",
                        (email, -charged, "charge", ref, (name or "")[:120], tool, int(words or 0), 0, float(cost_usd),
                         float(cost_usd)))
    A.request_sync()
    return dict(charged=charged)


def release(ref):
    """A job that failed or was stopped: nothing is charged."""
    with _lock, _db() as con:
        con.execute("UPDATE wallet_holds SET status='released' WHERE ref=? AND status='active'", (ref,))
    A.request_sync()


def release_stale():
    """Reservations of jobs that no longer exist (the server restarted while they ran): released."""
    with _lock, _db() as con:
        n = con.execute("UPDATE wallet_holds SET status='released' WHERE status='active'").rowcount
    return n


def record_cost(email, tool, words, seconds, llm_usd, charged_paise=0, how=""):
    """Every finished job, paid or free: what it cost (for the profit report)."""
    try:
        with _db() as con:
            con.execute("INSERT INTO job_costs (email, tool, words, seconds, llm_usd, cost_usd, charged_paise, how) "
                        "VALUES (?,?,?,?,?,?,?,?)",
                        ((email or "").lower(), tool, int(words or 0), float(seconds or 0), float(llm_usd or 0),
                         gpu_usd(seconds) + float(llm_usd or 0), int(charged_paise or 0), how))
    except Exception as err:
        print(f"[wallet] cost not recorded: {err}", flush=True)


def adjust(email, paise, note, source="admin"):
    """Admin: add (or, negative, remove) money, e.g. a refund or a bank transfer."""
    ref = f"A{int(time.time())}{secrets.token_hex(3).upper()}"
    with _lock, _db() as con:
        con.execute("INSERT INTO wallet_ledger (email, paise, kind, ref, note) VALUES (?,?,?,?,?)",
                    ((email or "").lower(), int(paise), "adjust", ref, f"{source}: {note}"[:200]))
    A.request_sync()
    return ref


def history(email, n=20):
    with _db() as con:
        rows = con.execute("SELECT ts, kind, paise, note, tool FROM wallet_ledger WHERE email=? ORDER BY id DESC "
                           "LIMIT ?", ((email or "").lower(), n)).fetchall()
    return rows


# ---------------------------------------------------------------------------------------------------- top-ups
def start_topup(email, rupees):
    """Create a PhonePe payment for a top-up; returns the page to send the customer to."""
    import phonepe as PP
    rupees = int(rupees)
    if not TOPUP_MIN <= rupees <= TOPUP_MAX:
        raise ValueError(f"Add between ₹{TOPUP_MIN:,} and ₹{TOPUP_MAX:,}.")
    order = f"CSW{int(time.time())}{secrets.token_hex(4).upper()}"
    with _db() as con:
        con.execute("INSERT INTO wallet_topups (order_id, email, paise) VALUES (?,?,?)",
                    (order, (email or "").lower(), rupees * 100))
    A.request_sync()
    return PP.create_payment(order, rupees * 100, f"{PP.PUBLIC_URL}/?topup={order}", note=email)


def resolve_topup(order_id):
    """Ask PhonePe about a top-up and credit it once if it is paid in full. Returns (state, email, paise)."""
    import phonepe as PP
    with _db() as con:
        row = con.execute("SELECT email, paise, state FROM wallet_topups WHERE order_id=?", (order_id,)).fetchone()
    if not row:
        return "UNKNOWN", None, 0
    email, paise, state = row
    if state in ("COMPLETED", "FAILED"):
        return state, email, paise
    st = PP.order_status(order_id)
    new = str(st.get("state") or "PENDING").upper()
    with _lock, _db() as con:
        con.execute("UPDATE wallet_topups SET checks=checks+1 WHERE order_id=?", (order_id,))
        if new == "COMPLETED":
            if int(st.get("amount") or 0) != int(paise):
                print(f"[wallet] top-up {order_id}: PhonePe amount {st.get('amount')} != {paise}; not credited",
                      flush=True)
                return "MISMATCH", email, paise
            cur = con.execute("SELECT state FROM wallet_topups WHERE order_id=?", (order_id,)).fetchone()[0]
            if cur != "COMPLETED":                     # credited exactly once (ref is UNIQUE in the ledger too)
                con.execute("UPDATE wallet_topups SET state='COMPLETED', done_ts=? WHERE order_id=?",
                            (_now(), order_id))
                con.execute("INSERT OR IGNORE INTO wallet_ledger (email, paise, kind, ref, note) VALUES (?,?,?,?,?)",
                            (email, int(paise), "topup", order_id, "PhonePe"))
        elif new == "FAILED":
            con.execute("UPDATE wallet_topups SET state='FAILED', done_ts=? WHERE order_id=?", (_now(), order_id))
        else:
            con.execute("UPDATE wallet_topups SET state='PENDING' WHERE order_id=?", (order_id,))
    A.request_sync()
    return new, email, paise


# ---------------------------------------------------------------------------------------------------- report orders
# A Turnitin report is paid for directly on its page (PhonePe), not from the wallet. States: CREATED/PENDING (waiting
# for PhonePe) -> PAID (PhonePe says COMPLETED for the full amount) -> USED (the report was delivered). A paid order
# whose report could not be produced stays PAID: pressing the button again re-uses it, nobody pays twice.
def start_report_order(email, units, file_key):
    """Create a PhonePe payment for `units` reports; returns (order_id, paise, the page to pay on)."""
    import phonepe as PP
    units = max(1, int(units))
    paise = usual_price("turnitin", 0) * units
    order = f"CSR{int(time.time())}{secrets.token_hex(4).upper()}"
    with _db() as con:
        con.execute("INSERT INTO report_orders (order_id, email, paise, units, file_key) VALUES (?,?,?,?,?)",
                    (order, (email or "").lower(), paise, units, file_key or ""))
    A.request_sync()
    url = PP.create_payment(order, paise, f"{PP.PUBLIC_URL}{PP.RETURN_PATH}?order={order}", note=email,
                            message="CS AI Writing Suite Turnitin report")
    return order, paise, url


def resolve_report_order(order_id):
    """Ask PhonePe about a report order; PAID once it is paid in full. Returns (state, email, paise, units)."""
    import phonepe as PP
    with _db() as con:
        row = con.execute("SELECT email, paise, units, state FROM report_orders WHERE order_id=?",
                          (order_id,)).fetchone()
    if not row:
        return "UNKNOWN", None, 0, 0
    email, paise, units, state = row
    if state in ("PAID", "USED", "FAILED"):
        return state, email, paise, units
    st = PP.order_status(order_id)
    new = str(st.get("state") or "PENDING").upper()
    with _lock, _db() as con:
        if new == "COMPLETED":
            if int(st.get("amount") or 0) != int(paise):
                print(f"[wallet] report order {order_id}: PhonePe amount {st.get('amount')} != {paise}; not accepted",
                      flush=True)
                return "MISMATCH", email, paise, units
            con.execute("UPDATE report_orders SET state='PAID', done_ts=? WHERE order_id=? AND state NOT IN "
                        "('PAID','USED')", (_now(), order_id))
            new = "PAID"
        elif new == "FAILED":
            con.execute("UPDATE report_orders SET state='FAILED', done_ts=? WHERE order_id=?", (_now(), order_id))
        else:
            new = "PENDING"
            con.execute("UPDATE report_orders SET state='PENDING' WHERE order_id=?", (order_id,))
    A.request_sync()
    return new, email, paise, units


def find_paid_report(email, file_key, units):
    """A paid, not yet delivered order of this customer for this file (e.g. the page was reloaded after paying)."""
    who, key = (email or "").lower(), file_key or "-"
    with _db() as con:
        waiting = [r[0] for r in con.execute(
            "SELECT order_id FROM report_orders WHERE email=? AND file_key=? AND units=? AND state IN "
            "('CREATED','PENDING') AND ts >= datetime('now', '-3 hours')", (who, key, int(units))).fetchall()]
    for oid in waiting:                                 # paid in PhonePe but the page was closed before it knew
        try:
            resolve_report_order(oid)
        except Exception as err:
            print(f"[wallet] report order {oid} check failed: {err}", flush=True)
    with _db() as con:
        row = con.execute("SELECT order_id FROM report_orders WHERE email=? AND file_key=? AND units=? AND "
                          "state='PAID' ORDER BY ts DESC LIMIT 1", (who, key, int(units))).fetchone()
    return row[0] if row else None


def report_order(order_id):
    """(email, paise, units, state) of an order, or None."""
    with _db() as con:
        return con.execute("SELECT email, paise, units, state FROM report_orders WHERE order_id=?",
                           (order_id,)).fetchone()


def finish_report_order(order_id, units_ok):
    """The reports are delivered: the order is used up. Parts that could not be produced are owed back (refund_paise,
    for the admin to refund in PhonePe). Returns the paise earned."""
    row = report_order(order_id)
    if not row:
        return 0
    _, paise, units, _ = row
    ok = max(0, min(int(units_ok), units))
    owed = paise * (units - ok) // units
    with _lock, _db() as con:
        con.execute("UPDATE report_orders SET state='USED', used_ts=?, refund_paise=? WHERE order_id=?",
                    (_now(), owed, order_id))
    if owed:
        print(f"[wallet] ⚠️ report order {order_id}: {units - ok} of {units} part(s) not produced; refund {inr(owed)} "
              "due in PhonePe", flush=True)
    A.request_sync()
    return paise - owed


def pending_topups(hours=3):
    with _db() as con:
        return [r[0] for r in con.execute(
            "SELECT order_id FROM wallet_topups WHERE state IN ('CREATED','PENDING') AND "
            "ts >= datetime('now', ?) ORDER BY ts", (f"-{int(hours)} hours",)).fetchall()]


def _poll_loop():
    """Top-ups whose customer closed the page before returning are settled from PhonePe's status anyway."""
    import phonepe as PP
    while True:
        time.sleep(60)
        if not PP.configured():
            continue
        for oid in pending_topups():
            try:
                resolve_topup(oid)
            except Exception as err:
                print(f"[wallet] top-up {oid} check failed: {err}", flush=True)
        with _db() as con:
            report_orders = [r[0] for r in con.execute(
                "SELECT order_id FROM report_orders WHERE state IN ('CREATED','PENDING') AND "
                "ts >= datetime('now', '-3 hours')").fetchall()]
        for oid in report_orders:
            try:
                resolve_report_order(oid)
            except Exception as err:
                print(f"[wallet] report order {oid} check failed: {err}", flush=True)


def start_background():
    n = release_stale()
    if n:
        print(f"[wallet] released {n} reservation(s) of jobs interrupted by a restart", flush=True)
    threading.Thread(target=_poll_loop, daemon=True).start()


# ---------------------------------------------------------------------------------------------------- profit report
def profit_report(days=30):
    """Money in and out over the last `days` days (₹)."""
    since = f"-{int(days)} days"
    with _db() as con:
        topups = con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_ledger WHERE kind='topup' AND "
                             "ts >= datetime('now', ?)", (since,)).fetchone()[0] / 100
        charged = -con.execute("SELECT COALESCE(SUM(paise),0) FROM wallet_ledger WHERE kind='charge' AND "
                               "ts >= datetime('now', ?)", (since,)).fetchone()[0] / 100
        jobs, llm, secs, paid_jobs = con.execute(
            "SELECT COUNT(*), COALESCE(SUM(llm_usd),0), COALESCE(SUM(seconds),0), "
            "COALESCE(SUM(charged_paise>0),0) FROM job_costs WHERE ts >= datetime('now', ?)", (since,)).fetchone()
    gst = charged - charged / (1 + GST / 100)
    fee = topups * FEE / 100 * (1 + FEE_GST / 100)
    groq = llm * USD_INR
    gpu_bill = days * 24 * GPU_HOUR_USD * USD_INR          # the GPU is billed every hour, busy or not
    net = charged - gst - fee - groq - gpu_bill
    per_job = (charged - gst) / paid_jobs if paid_jobs else 0
    breakeven = math.ceil(gpu_bill / max(1e-9, per_job - groq / max(1, jobs))) if paid_jobs else None
    return dict(days=days, topups=topups, charged=charged, gst=gst, fee=fee, groq=groq, gpu_bill=gpu_bill, net=net,
                jobs=jobs, paid_jobs=paid_jobs, gpu_hours_busy=secs / 3600, breakeven_jobs=breakeven)


# ---------------------------------------------------------------------------------------------------- pricing page
def pricing_html(free_note=""):
    """The Pricing page: pay as you go from a prepaid wallet, with the usual price of common jobs."""
    import html as _h
    rows = [("Real Turnitin report: AI-writing + similarity PDFs", "turnitin", 0),
            ("Smart Humanize, short paper", "fast", 1000),
            ("Smart Humanize, full paper", "fast", 2500),
            ("Smart Humanize, long paper", "fast", 5000),
            ("Smart Humanize, thesis chapter", "fast", 10000),
            ("AI detector report (beyond the free ones)", "detector", 3000),
            ("Proofread", "proofread", 3000)]
    body = "".join(f"<tr><td>{_h.escape(t)}</td><td>{'any length' if k in FIXED else f'{w:,} words'}</td>"
                   f"<td><b>{'' if k in FIXED else 'about '}{inr(usual_price(k, w))}</b></td></tr>" for t, k, w in rows)
    tops = " · ".join(inr(x * 100) for x in TOPUPS)
    return (f'<div class="cs-prices" style="max-width:900px;margin:0 auto;display:block">'
            f'<div class="cs-price cs-featured" style="max-width:none"><div class="cs-pop">Pay as you go</div>'
            f'<div class="cs-pname">Prepaid wallet</div><div class="cs-pval">from {inr(int(MIN_INR * 100))}'
            f'<small>/run</small></div><div class="cs-inr">Add money once, then every run is paid from your balance'
            f'</div><ul><li>Add {tops} or any amount from {inr(TOPUP_MIN * 100)}: UPI, cards, net banking '
            f'(PhonePe)</li><li>Each run is priced by what it actually used (its length and the work it needed)'
            f'</li><li>The usual price is shown before a run starts; you are never charged more than it reserved'
            f'</li><li>A run that fails or is stopped costs nothing</li><li>Your balance never expires</li>'
            f'<li>Prices include {GST:.0f}% GST</li></ul></div>'
            f'<table class="cs-wallet-table" style="width:100%;margin-top:14px;border-collapse:collapse">'
            f'<thead><tr><th style="text-align:left">Usual price of a run</th><th style="text-align:left">Length'
            f'</th><th style="text-align:left">Price</th></tr></thead><tbody>{body}</tbody></table>'
            f'<div class="cs-gauge-cap" style="text-align:left;margin-top:8px">{_h.escape(free_note)}</div></div>')


def status_html(email):
    if not email:
        return '<div class="cs-nudge">Sign in to see your wallet and add money.</div>'
    bal, hold = balance(email), held(email)
    extra = f" ({inr(hold)} reserved for a run in progress)" if hold else ""
    return f'<div class="cs-nudge">💳 Wallet balance: <b>{inr(bal)}</b>{extra}</div>'


def history_rows(email, n=15):
    names = {"topup": "Added (PhonePe)", "charge": "Run", "adjust": "Adjustment"}
    return [[(ts or "")[:16], names.get(k, k), inr(p), note or tool or ""] for ts, k, p, note, tool in
            history(email, n)]


def report_html(days=30):
    r = profit_report(days)
    tone = "cs-ok" if r["net"] >= 0 else "cs-bad"
    be = (f"About <b>{r['breakeven_jobs']:,}</b> paid runs in {days} days cover the GPU bill at the current mix."
          if r["breakeven_jobs"] else "No paid runs yet, so the break-even point cannot be worked out.")
    rows = [("Money added by customers (top-ups)", r["topups"]), ("Charged for runs", r["charged"]),
            ("GST in those charges (to the government)", -r["gst"]), ("PhonePe fees (estimate)", -r["fee"]),
            ("Groq (language model) for all runs", -r["groq"]),
            (f"GPU bill ({days} days x 24 h, busy or idle)", -r["gpu_bill"])]
    body = "".join(f"<tr><td>{k}</td><td style='text-align:right'>₹{v:,.0f}</td></tr>" for k, v in rows)
    return (f"<table style='width:100%;border-collapse:collapse'>{body}<tr><td><b>Profit after all costs</b></td>"
            f"<td style='text-align:right'><b>₹{r['net']:,.0f}</b></td></tr></table>"
            f"<div class='cs-authmsg {tone}'>{r['jobs']:,} runs ({r['paid_jobs']:,} paid), GPU busy "
            f"{r['gpu_hours_busy']:.1f} h. {be}</div>")


def admin_topups(n=50):
    with _db() as con:
        rows = con.execute("SELECT ts, email, paise, state, done_ts FROM wallet_topups ORDER BY ts DESC LIMIT ?",
                           (n,)).fetchall()
    return [[(r[0] or "")[:16], r[1], inr(r[2]), r[3], (r[4] or "")[:16]] for r in rows]