"""The pool: one row per organisation per period in the tenant's own ai_credit_periods, moved only here, under
a row lock. The rate a call is priced at is looked up in the platform's master DB by the caller and passed in.

    open_period     the row for now, created from the entitlement when it is the first call of the period
    reserve         hold an estimate before the model runs; refused when the pool cannot cover it
    settle          price what really ran, write the ledger row, release the hold, note marks crossed
    release         let a hold go when nothing ran
    expire_holds    let go of holds whose call never reported back
"""
from __future__ import annotations

import json
from datetime import datetime, timedelta, timezone
from typing import Optional, Tuple

from .allowance import Entitlement
from .types import MARKS, Charge, Period, Rate

HOLD_MINUTES = 15
_PERIOD_COLUMNS = "id, organisation_id, period_start, period_end, allowance, purchased, used, reserved"


def _t(schema: str, table: str) -> str:
    return f'"{schema}".{table}'


def _period(row) -> Period:
    return Period(str(row[0]), str(row[1]), row[2], row[3], row[4], row[5], row[6], row[7])


def open_period(conn, schema: str, entitlement: Entitlement, lock: bool = False) -> Period:
    row = conn.execute(
        f"INSERT INTO {_t(schema, 'ai_credit_periods')} (organisation_id, subscription_id, period_start, period_end, allowance) "
        f"VALUES (%s, %s, %s, %s, %s) "
        f"ON CONFLICT (organisation_id, period_start) DO UPDATE SET allowance = EXCLUDED.allowance, subscription_id = EXCLUDED.subscription_id, updated_at = now() "
        f"RETURNING {_PERIOD_COLUMNS}",
        (entitlement.organisation_id, entitlement.subscription_id, entitlement.start, entitlement.end,
         entitlement.allowance)).fetchone()
    if lock:
        row = conn.execute(f"SELECT {_PERIOD_COLUMNS} FROM {_t(schema, 'ai_credit_periods')} WHERE id = %s FOR UPDATE",
                           (row[0],)).fetchone()
    return _period(row)


def tell_once(conn, schema: str, period_id: str, mark: int) -> bool:
    """Marks `mark` as told for this period; False when it already was. So a refusal tells the owners
    "used up" exactly once, and a later charge crossing the same mark tells nobody again."""
    with conn.transaction():
        row = conn.execute(f"UPDATE {_t(schema, 'ai_credit_periods')} SET notices_sent = notices_sent || to_jsonb(%s::int) "
                           f"WHERE id = %s AND NOT notices_sent @> to_jsonb(%s::int) RETURNING id", (mark, period_id, mark)).fetchone()
    return row is not None


def reserve(conn, schema: str, entitlement: Entitlement, rate: Optional[Rate], product: str, feature: str, type: str,
            model_key: str, input_estimate: int, output_estimate: int) -> Tuple[Optional[str], int, Period, str]:
    """(reservation id, estimate, period, reason). The id is None when refused or nothing was held; reason says why."""
    with conn.transaction():
        period = open_period(conn, schema, entitlement, lock=True)
        if rate is None:
            return None, 0, period, "no_rate"
        estimate = rate.price(input_estimate, 0, output_estimate)
        if period.remaining < estimate:
            return None, estimate, period, "used_up"
        if estimate == 0:
            return None, 0, period, ""
        hold = conn.execute(
            f"INSERT INTO {_t(schema, 'ai_credit_reservations')} (period_id, product, feature, type, model_key, estimate, expires_at) "
            f"VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING id",
            (period.id, product, feature, type, model_key, estimate,
             datetime.now(timezone.utc) + timedelta(minutes=HOLD_MINUTES))).fetchone()[0]
        conn.execute(f"UPDATE {_t(schema, 'ai_credit_periods')} SET reserved = reserved + %s, updated_at = now() WHERE id = %s",
                     (estimate, period.id))
        after = Period(period.id, period.organisation_id, period.start, period.end, period.allowance,
                       period.purchased, period.used, period.reserved + estimate)
        return str(hold), estimate, after, ""


def settle(conn, schema: str, entitlement: Entitlement, rate: Optional[Rate], reservation_id: Optional[str], call_id: str,
           product: str, feature: str, type: str, model_key: str, input_units: int, cached_input_units: int,
           output_units: int) -> Charge:
    """Prices what ran and takes it off the pool. A call_id seen before changes nothing and says so."""
    with conn.transaction():
        seen = conn.execute(f"SELECT tokens FROM {_t(schema, 'ai_usage')} WHERE call_id = %s", (call_id,)).fetchone()
        period = open_period(conn, schema, entitlement, lock=True)
        if seen:
            return Charge(seen[0], period.remaining, None, repeated=True)
        if rate is None:
            raise LookupError(f"no enabled rate for {model_key} / {type} and no '*' fallback for {type}")
        tokens = rate.price(input_units, cached_input_units, output_units)
        held = 0
        if reservation_id:
            row = conn.execute(f"UPDATE {_t(schema, 'ai_credit_reservations')} SET status = 'settled' "
                               f"WHERE id = %s AND status = 'reserved' RETURNING estimate", (reservation_id,)).fetchone()
            held = row[0] if row else 0
        conn.execute(
            f"INSERT INTO {_t(schema, 'ai_usage')} (call_id, organisation_id, period_id, product, feature, type, model_key, rate_model_key, "
            f" input_units, cached_input_units, output_units, input_tokens_per_unit, output_tokens_per_unit, tokens, flagged) "
            f"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (call_id, period.organisation_id, period.id, product, feature, type, model_key, rate.model_key,
             input_units, cached_input_units, output_units, rate.input_tokens_per_unit, rate.output_tokens_per_unit,
             tokens, rate.fallback))
        before = period.used
        row = conn.execute(
            f"UPDATE {_t(schema, 'ai_credit_periods')} SET used = used + %s, reserved = GREATEST(0, reserved - %s), "
            f" used_by_type = used_by_type || jsonb_build_object(%s::text, coalesce((used_by_type ->> %s::text)::bigint, 0) + %s::bigint), "
            f" updated_at = now() WHERE id = %s RETURNING used, reserved, notices_sent",
            (tokens, held, type, type, tokens, period.id)).fetchone()
        used, reserved, told = row
        limit = period.allowance + period.purchased
        crossed = [m for m in MARKS if limit > 0 and before * 100 < limit * m <= used * 100 and m not in (told or [])]
        if crossed:
            conn.execute(f"UPDATE {_t(schema, 'ai_credit_periods')} SET notices_sent = %s WHERE id = %s",
                         (json.dumps(sorted(set(told or []) | set(crossed))), period.id))
        return Charge(tokens, limit - used - reserved, rate, marks_crossed=crossed)


def release(conn, schema: str, reservation_id: str) -> None:
    with conn.transaction():
        row = conn.execute(f"UPDATE {_t(schema, 'ai_credit_reservations')} SET status = 'released' "
                           f"WHERE id = %s AND status = 'reserved' RETURNING period_id, estimate", (reservation_id,)).fetchone()
        if row:
            conn.execute(f"UPDATE {_t(schema, 'ai_credit_periods')} SET reserved = GREATEST(0, reserved - %s), updated_at = now() "
                         f"WHERE id = %s", (row[1], row[0]))


def expire_holds(conn, schema: str) -> int:
    """Holds whose call never reported back within HOLD_MINUTES are let go. Returns how many."""
    with conn.transaction():
        rows = conn.execute(f"UPDATE {_t(schema, 'ai_credit_reservations')} SET status = 'expired' "
                            f"WHERE status = 'reserved' AND expires_at < now() RETURNING period_id, estimate").fetchall()
        for period_id, estimate in rows:
            conn.execute(f"UPDATE {_t(schema, 'ai_credit_periods')} SET reserved = GREATEST(0, reserved - %s), updated_at = now() "
                         f"WHERE id = %s", (estimate, period_id))
        return len(rows)


def summary(conn, schema: str, entitlement: Entitlement) -> dict:
    period = open_period(conn, schema, entitlement)
    by_type = conn.execute(f"SELECT used_by_type FROM {_t(schema, 'ai_credit_periods')} WHERE id = %s", (period.id,)).fetchone()[0]
    return {"allowance": period.allowance, "purchased": period.purchased, "used": period.used, "reserved": period.reserved,
            "remaining": period.remaining, "used_by_type": by_type or {}, "period_start": period.start, "period_end": period.end}
