"""
HEAD OR TAIL — data layer (PostgreSQL).

All money moves go through ledger_move() so every transfer is mirrored in the
dual-entry ledger. Balances and ledger are always updated in the SAME
transaction, so they can never drift.
"""

import hashlib
import secrets
import datetime
import json
import psycopg2
from psycopg2.pool import ThreadedConnectionPool
from psycopg2.extras import RealDictCursor
import config

_POOL = None


def init_pool():
    global _POOL
    if _POOL is None:
        _POOL = ThreadedConnectionPool(
            config.DB_POOL_MIN, config.DB_POOL_MAX, dsn=config.DB_DSN
        )


class conn_ctx:
    """Borrow a pooled connection; commit on success, rollback on error."""
    def __enter__(self):
        self.conn = _POOL.getconn()
        return self.conn

    def __exit__(self, exc_type, exc, tb):
        if exc_type is None:
            self.conn.commit()
        else:
            self.conn.rollback()
        _POOL.putconn(self.conn)


def q(sql, params=None, fetch=None):
    """Convenience for simple autonomous queries. fetch in {None,'one','all'}."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute(sql, params or ())
        if fetch == "one":
            return cur.fetchone()
        if fetch == "all":
            return cur.fetchall()
        return None


# ---------------- helpers ----------------
def pw_hash(p):
    return hashlib.sha256(p.encode()).hexdigest()


def now():
    return datetime.datetime.now(datetime.timezone.utc)


def new_id(prefix):
    return prefix + secrets.token_hex(4)


def ledger_move(cur, txn_type, debit_type, debit_id, credit_type, credit_id,
                amount, round_no=None, ref=""):
    """Record one dual-entry transfer. Caller manages balance columns itself."""
    cur.execute(
        "INSERT INTO ledger (txn_type,debit_type,debit_id,credit_type,credit_id,"
        "amount,round_no,ref) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",
        (txn_type, debit_type, debit_id, credit_type, credit_id,
         amount, round_no, ref),
    )


# ---------------- bootstrap ----------------
def bootstrap_admin():
    """Create the default admin once, if none exists."""
    with conn_ctx() as c:
        cur = c.cursor()
        cur.execute("SELECT count(*) FROM admins")
        if cur.fetchone()[0] == 0:
            cur.execute(
                "INSERT INTO admins (id,username,password_hash,balance) "
                "VALUES (%s,%s,%s,%s)",
                ("admin", config.DEFAULT_ADMIN_USER,
                 pw_hash(config.DEFAULT_ADMIN_PASS), config.DEFAULT_ADMIN_BALANCE),
            )


def load_last_round():
    row = q("SELECT last_round FROM game_state WHERE id=1", fetch="one")
    return row["last_round"] if row else 1000


def save_last_round(rnd):
    q("UPDATE game_state SET last_round=%s WHERE id=1", (rnd,))


# ---------------- auth ----------------
def admin_login(username, password):
    return q("SELECT id,username,balance FROM admins WHERE username=%s AND "
             "password_hash=%s AND active=TRUE",
             (username, pw_hash(password)), fetch="one")


def agent_login(username, password):
    return q("SELECT id,username,balance,admin_id FROM agents WHERE username=%s "
             "AND password_hash=%s AND active=TRUE",
             (username, pw_hash(password)), fetch="one")


def cashier_login(username, password):
    return q("SELECT id,username,real_balance,agent_id,shop_name FROM cashiers "
             "WHERE username=%s AND password_hash=%s AND active=TRUE",
             (username, pw_hash(password)), fetch="one")


def cashier_by_terminal(token):
    return q("SELECT cashier_id FROM terminals WHERE token=%s AND active=TRUE",
             (token,), fetch="one")


# ---------------- money chain ----------------
def topup_agent(admin_id, agent_id, amount):
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT balance FROM admins WHERE id=%s FOR UPDATE", (admin_id,))
        a = cur.fetchone()
        if not a or a["balance"] < amount:
            return {"success": False, "error": "Insufficient admin balance"}
        cur.execute("SELECT id FROM agents WHERE id=%s AND admin_id=%s",
                    (agent_id, admin_id))
        if not cur.fetchone():
            return {"success": False, "error": "Not your agent"}
        cur.execute("UPDATE admins SET balance=balance-%s WHERE id=%s", (amount, admin_id))
        cur.execute("UPDATE agents SET balance=balance+%s WHERE id=%s", (amount, agent_id))
        ledger_move(cur, "topup_agent", "admin", admin_id, "agent", agent_id, amount)
        return {"success": True}


def topup_cashier(agent_id, cashier_id, amount):
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT balance FROM agents WHERE id=%s FOR UPDATE", (agent_id,))
        a = cur.fetchone()
        if not a or a["balance"] < amount:
            return {"success": False, "error": "Insufficient agent balance"}
        cur.execute("SELECT id FROM cashiers WHERE id=%s AND agent_id=%s",
                    (cashier_id, agent_id))
        if not cur.fetchone():
            return {"success": False, "error": "Not your cashier"}
        cur.execute("UPDATE agents SET balance=balance-%s WHERE id=%s", (amount, agent_id))
        cur.execute("UPDATE cashiers SET real_balance=real_balance+%s WHERE id=%s",
                    (amount, cashier_id))
        ledger_move(cur, "topup_cashier", "agent", agent_id, "cashier", cashier_id, amount)
        return {"success": True}


def create_agent(admin_id, username, password, phone=""):
    aid = new_id("A")
    try:
        with conn_ctx() as c:
            cur = c.cursor()
            cur.execute(
                "INSERT INTO agents (id,username,password_hash,phone,admin_id) "
                "VALUES (%s,%s,%s,%s,%s)",
                (aid, username, pw_hash(password), phone, admin_id),
            )
        return {"success": True, "agent_id": aid}
    except psycopg2.IntegrityError:
        return {"success": False, "error": "Username already exists"}


def create_cashier(agent_id, username, password, shop_name="", phone=""):
    """Cashiers are always born under an agent (never unassigned)."""
    cid = new_id("C")
    try:
        with conn_ctx() as c:
            cur = c.cursor()
            cur.execute(
                "INSERT INTO cashiers (id,username,password_hash,agent_id,shop_name,phone)"
                " VALUES (%s,%s,%s,%s,%s,%s)",
                (cid, username, pw_hash(password), agent_id, shop_name, phone),
            )
        return {"success": True, "cashier_id": cid}
    except psycopg2.IntegrityError:
        return {"success": False, "error": "Username already exists"}


def reassign_cashier(admin_id, cashier_id, to_agent_id):
    """
    Move a cashier to a new agent. Allowed ONLY when no shift is open.
    Cashier keeps their balance (logged as a transfer). Past bets keep their
    original agent_id (history pins to the old agent). Future bets use the new.
    """
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT agent_id,real_balance FROM cashiers WHERE id=%s FOR UPDATE",
                    (cashier_id,))
        ca = cur.fetchone()
        if not ca:
            return {"success": False, "error": "Cashier not found"}
        cur.execute("SELECT id FROM agents WHERE id=%s", (to_agent_id,))
        if not cur.fetchone():
            return {"success": False, "error": "Target agent not found"}
        cur.execute("SELECT count(*) AS n FROM shifts WHERE cashier_id=%s AND status='open'",
                    (cashier_id,))
        if cur.fetchone()["n"] > 0:
            return {"success": False, "error": "Close the cashier's shift before reassigning"}
        from_agent = ca["agent_id"]
        if from_agent == to_agent_id:
            return {"success": False, "error": "Already managed by that agent"}
        cur.execute("UPDATE cashiers SET agent_id=%s WHERE id=%s", (to_agent_id, cashier_id))
        cur.execute(
            "INSERT INTO cashier_reassignments "
            "(cashier_id,from_agent_id,to_agent_id,balance_at_move,admin_id) "
            "VALUES (%s,%s,%s,%s,%s)",
            (cashier_id, from_agent, to_agent_id, ca["real_balance"], admin_id),
        )
        ledger_move(cur, "reassign_transfer", "agent", from_agent, "agent", to_agent_id,
                    ca["real_balance"], ref="cashier " + cashier_id)
        return {"success": True, "moved_balance": float(ca["real_balance"])}


def gen_activation_code(agent_id, cashier_id):
    code = "".join(secrets.choice("ABCDEFGHJKLMNPQRSTUVWXYZ23456789") for _ in range(8))
    with conn_ctx() as c:
        cur = c.cursor()
        cur.execute("SELECT id FROM cashiers WHERE id=%s AND agent_id=%s",
                    (cashier_id, agent_id))
        if not cur.fetchone():
            return {"success": False, "error": "Not your cashier"}
        cur.execute(
            "INSERT INTO activation_codes (code_hash,cashier_id,agent_id) VALUES (%s,%s,%s)",
            (hashlib.sha256(code.encode()).hexdigest(), cashier_id, agent_id),
        )
    return {"success": True, "code": code}


def activate_terminal(code):
    h = hashlib.sha256(code.encode()).hexdigest()
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT id,cashier_id FROM activation_codes WHERE code_hash=%s "
                    "AND used=FALSE", (h,))
        row = cur.fetchone()
        if not row:
            return {"success": False, "error": "Invalid or used code"}
        cur.execute("UPDATE activation_codes SET used=TRUE WHERE id=%s", (row["id"],))
        token = secrets.token_hex(32)
        cur.execute("INSERT INTO terminals (token,cashier_id) VALUES (%s,%s)",
                    (token, row["cashier_id"]))
        return {"success": True, "token": token, "cashier_id": row["cashier_id"]}


# ---------------- player sessions & funding ----------------
def fund_player(cashier_id, session_id, amount):
    """Cashier funds a player's REAL balance from the cashier's drawer."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT real_balance FROM cashiers WHERE id=%s FOR UPDATE", (cashier_id,))
        ca = cur.fetchone()
        if not ca or ca["real_balance"] < amount:
            return {"success": False, "error": "Insufficient cashier balance"}
        cur.execute("SELECT id FROM player_sessions WHERE id=%s", (session_id,))
        if not cur.fetchone():
            cur.execute("INSERT INTO player_sessions (id,cashier_id,real_balance) "
                        "VALUES (%s,%s,0)", (session_id, cashier_id))
        cur.execute("UPDATE cashiers SET real_balance=real_balance-%s WHERE id=%s",
                    (amount, cashier_id))
        cur.execute("UPDATE player_sessions SET real_balance=real_balance+%s WHERE id=%s",
                    (amount, session_id))
        ledger_move(cur, "fund_player", "cashier", cashier_id, "player", session_id, amount)
        cur.execute("SELECT real_balance,fund_balance FROM player_sessions WHERE id=%s",
                    (session_id,))
        r = cur.fetchone()
        return {"success": True, "real_balance": float(r["real_balance"]),
                "fund_balance": float(r["fund_balance"])}


def get_jackpot():
    r = q("SELECT amount FROM jackpot WHERE id=1", fetch="one")
    return float(r["amount"]) if r else 0.0


# ---------------- shifts ----------------
def current_shift(cashier_id):
    """Return the cashier's open shift row, or None."""
    return q("SELECT id,start_time,start_balance,agent_id FROM shifts "
             "WHERE cashier_id=%s AND status='open' ORDER BY start_time DESC LIMIT 1",
             (cashier_id,), fetch="one")


def open_shift(cashier_id):
    """Open a shift if none is open (idempotent). Returns the open shift."""
    existing = current_shift(cashier_id)
    if existing:
        return {"success": True, "shift_id": existing["id"],
                "start_time": existing["start_time"], "reused": True}
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT real_balance,agent_id FROM cashiers WHERE id=%s", (cashier_id,))
        ci = cur.fetchone()
        if not ci:
            return {"success": False, "error": "Unknown cashier"}
        cur.execute("INSERT INTO shifts (cashier_id,agent_id,start_balance,status) "
                    "VALUES (%s,%s,%s,'open') RETURNING id,start_time",
                    (cashier_id, ci["agent_id"], ci["real_balance"]))
        row = cur.fetchone()
        return {"success": True, "shift_id": row["id"],
                "start_time": row["start_time"], "reused": False}


def close_shift(cashier_id):
    """Close the cashier's open shift, stamping end balance + time."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT id FROM shifts WHERE cashier_id=%s AND status='open' "
                    "ORDER BY start_time DESC LIMIT 1", (cashier_id,))
        sh = cur.fetchone()
        if not sh:
            return {"success": False, "error": "No open shift"}
        cur.execute("SELECT real_balance FROM cashiers WHERE id=%s", (cashier_id,))
        bal = cur.fetchone()["real_balance"]
        cur.execute("UPDATE shifts SET status='closed',end_balance=%s,end_time=now() "
                    "WHERE id=%s", (bal, sh["id"]))
        return {"success": True, "shift_id": sh["id"], "end_balance": float(bal)}


def get_house_profit():
    r = q("SELECT profit FROM house WHERE id=1", fetch="one")
    return float(r["profit"]) if r else 0.0


# ---------------- reports ----------------
def _cashier_stats(cur, cashier_id, since_sql="CURRENT_DATE"):
    """Aggregate one cashier's activity since the given moment (default: today)."""
    cur.execute(
        f"""SELECT
              COALESCE(SUM(CASE WHEN status<>'cancelled' THEN stake END),0) AS staked,
              COUNT(*) FILTER (WHERE status<>'cancelled')                    AS bets,
              COALESCE(SUM(CASE WHEN redeemed THEN win_amount END),0)        AS paid,
              COUNT(*) FILTER (WHERE status='win')                           AS wins
            FROM bets
            WHERE cashier_id=%s AND created_at >= {since_sql}""",
        (cashier_id,))
    return cur.fetchone()


def agent_cashiers(agent_id):
    """All cashiers under an agent, each with today's activity + balance."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT id,username,real_balance,shop_name,phone,active,created_at "
                    "FROM cashiers WHERE agent_id=%s ORDER BY created_at", (agent_id,))
        rows = cur.fetchall()
        out = []
        for r in rows:
            s = _cashier_stats(cur, r["id"])
            staked = float(s["staked"]); paid = float(s["paid"])
            out.append({
                "id": r["id"], "username": r["username"],
                "balance": float(r["real_balance"]), "shop": r["shop_name"] or "",
                "phone": r["phone"] or "", "active": r["active"],
                "bets_today": int(s["bets"]), "staked_today": staked,
                "paid_today": paid, "wins_today": int(s["wins"]),
                "profit_today": round(staked - paid, 2),
            })
        return out


def agent_overview(agent_id):
    """Agent's own balance + rolled-up totals across their cashiers (today)."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT username,balance FROM agents WHERE id=%s", (agent_id,))
        a = cur.fetchone()
        cur.execute("SELECT COALESCE(SUM(real_balance),0) s, COUNT(*) n "
                    "FROM cashiers WHERE agent_id=%s", (agent_id,))
        agg = cur.fetchone()
        return {
            "username": a["username"] if a else "",
            "balance": float(a["balance"]) if a else 0.0,
            "cashier_count": int(agg["n"]),
            "cashier_float_total": float(agg["s"]),
        }


def admin_agents(admin_id):
    """All agents under an admin, each with balance, cashier count, today's profit."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT id,username,balance,phone,active,created_at "
                    "FROM agents WHERE admin_id=%s ORDER BY created_at", (admin_id,))
        agents = cur.fetchall()
        out = []
        for a in agents:
            cur.execute("SELECT id FROM cashiers WHERE agent_id=%s", (a["id"],))
            cids = [r["id"] for r in cur.fetchall()]
            staked = paid = 0.0; bets = 0
            for cid in cids:
                s = _cashier_stats(cur, cid)
                staked += float(s["staked"]); paid += float(s["paid"]); bets += int(s["bets"])
            out.append({
                "id": a["id"], "username": a["username"], "balance": float(a["balance"]),
                "phone": a["phone"] or "", "active": a["active"],
                "cashier_count": len(cids), "bets_today": bets,
                "staked_today": round(staked, 2), "paid_today": round(paid, 2),
                "profit_today": round(staked - paid, 2),
            })
        return out


def admin_all_cashiers():
    """Flat list of every cashier with its agent, for the reassignment picker."""
    return q("SELECT c.id,c.username,c.real_balance,c.shop_name,c.agent_id,"
             "a.username AS agent_name FROM cashiers c JOIN agents a ON a.id=c.agent_id "
             "ORDER BY a.username,c.username", fetch="all")


def admin_overview():
    """Top-line totals across the whole platform."""
    with conn_ctx() as c:
        cur = c.cursor(cursor_factory=RealDictCursor)
        cur.execute("SELECT COALESCE(SUM(balance),0) s, COUNT(*) n FROM agents")
        ag = cur.fetchone()
        cur.execute("SELECT COALESCE(SUM(real_balance),0) s, COUNT(*) n FROM cashiers")
        ca = cur.fetchone()
        cur.execute("SELECT COALESCE(SUM(CASE WHEN status<>'cancelled' THEN stake END),0) staked, "
                    "COALESCE(SUM(CASE WHEN redeemed THEN win_amount END),0) paid, "
                    "COUNT(*) FILTER (WHERE status<>'cancelled') bets "
                    "FROM bets WHERE created_at >= CURRENT_DATE")
        today = cur.fetchone()
        staked = float(today["staked"]); paid = float(today["paid"])
        cur.execute("SELECT profit FROM house WHERE id=1")
        hp = cur.fetchone()
        cur.execute("SELECT amount FROM jackpot WHERE id=1")
        jp = cur.fetchone()
        return {
            "agent_count": int(ag["n"]), "agent_balance_total": float(ag["s"]),
            "cashier_count": int(ca["n"]), "cashier_float_total": float(ca["s"]),
            "bets_today": int(today["bets"]), "staked_today": round(staked, 2),
            "paid_today": round(paid, 2), "gross_today": round(staked - paid, 2),
            "house_profit": float(hp["profit"]) if hp else 0.0,
            "jackpot": float(jp["amount"]) if jp else 0.0,
        }