"""Ticket records, lookup by code, check-in/reject and scanner payloads."""
import re
from datetime import datetime
from flask import has_request_context, request, session, url_for

from nexora.config import ADMIN_USERNAME
from nexora.database.connection import db, execute, query
from nexora.utils.formatting import pretty_dt
from nexora.utils.team import parse_team_members, team_roster_text, team_total_count
from nexora.utils.validators import safe_table_name
from nexora.services.ticket_codes import make_ticket_serial, make_ticket_serial_number, make_ticket_token
from nexora.services.registrations import REGISTRATION_SELECT, registration_details
from nexora.services.qualifier_entries import compact_qualifier_export_text


def member_ticket_record(details, recipient):
    """Return the persisted participant ticket for solo, leader, or member."""
    member_number = int(recipient.get("member_number") or 1)
    stored = query(
        "SELECT id, ticket_serial, ticket_token FROM member_tickets WHERE registration_id = ? AND member_number = ?",
        (details["id"], member_number),
        one=True,
    )
    if stored:
        return dict(stored)

    with db() as conn:
        serial = _new_participant_serial(conn)
        conn.execute(
            """
            INSERT OR IGNORE INTO member_tickets
            (registration_id, member_number, member_name, member_email, member_cnic, ticket_serial, ticket_token, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                details["id"], member_number, recipient.get("name", ""), recipient.get("email", ""),
                recipient.get("cnic", ""), serial, make_ticket_token(), datetime.now().isoformat(timespec="seconds"),
            ),
        )
    stored = query(
        "SELECT id, ticket_serial, ticket_token FROM member_tickets WHERE registration_id = ? AND member_number = ?",
        (details["id"], member_number),
        one=True,
    )
    return dict(stored) if stored else {"id": None, "ticket_serial": serial, "ticket_token": ""}


def _new_participant_serial(conn):
    serial = make_ticket_serial_number()
    while _serial_exists(conn, serial):
        serial = make_ticket_serial_number()
    return serial


def _serial_exists(conn, serial):
    tables = ("registrations", "member_tickets", "qualifier_entry_tickets")
    for table in tables:
        row = conn.execute(f"SELECT 1 FROM {table} WHERE ticket_serial = ?", (serial,)).fetchone()
        if row:
            return True
    registry_exists = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'ticket_id_registry'"
    ).fetchone()
    if registry_exists:
        return bool(conn.execute("SELECT 1 FROM ticket_id_registry WHERE ticket_id = ?", (serial,)).fetchone())
    return False


def ticket_details_for_recipient(details, recipient):
    recipient_details = dict(details)
    record = member_ticket_record(details, recipient)
    recipient_details["ticket_number"] = record["ticket_serial"]
    recipient_details["ticket_token"] = record["ticket_token"]
    return recipient_details


def ticket_member_roster(details):
    """Every ticket holder on ONE registration, straight from the DB.

    Returns [{"number", "name", "serial", "token"}]. Entry 1 is a persisted
    participant ticket too; it never reuses the base registration token.
    """
    leader_record = member_ticket_record(details, {
        "role": "leader",
        "member_number": 1,
        "name": details.get("leader_name") or details.get("name") or "",
        "email": details.get("email") or "",
        "cnic": details.get("cnic") or "",
    })
    roster = [{
        "number": 1,
        "name": details.get("leader_name") or details.get("name") or "",
        "email": details.get("email") or "",
        "phone": details.get("phone") or "",
        "cnic": details.get("cnic") or "",
        "db_id": leader_record.get("id"),
        "serial": leader_record["ticket_serial"],
        "ticket_id": leader_record["ticket_serial"],
        "token": leader_record["ticket_token"],
    }]
    if (details.get("entry_type") or "").lower() != "group":
        return roster
    for member in parse_team_members(details.get("members")):
        number = len(roster) + 1
        if not (member["name"] or member["email"] or member["phone"]):
            continue
        record = member_ticket_record(details, {"role": "member", "member_number": number, "name": member["name"], "email": member["email"], "cnic": member.get("cnic", "")})
        roster.append({
            "number": number,
            "name": member["name"] or f"Member {number}",
            "email": member.get("email") or "",
            "phone": member.get("phone") or "",
            "cnic": member.get("cnic") or "",
            "db_id": record.get("id"),
            "serial": record["ticket_serial"],
            "ticket_id": record["ticket_serial"],
            "token": record["ticket_token"],
        })
    return roster


def ticket_qr_payload(details):
    """The QR holds ONLY the opaque token — never a URL.

    Earlier the QR encoded a link to the scanner page, so anyone who opened a
    ticket PDF on a phone could follow it. Now the code is meaningless outside
    the admin scanner, which is behind the login."""
    return f"NXT:{details['ticket_token']}"


def participant_ticket_qr_payload(details, member_number=1):
    record = member_ticket_record(details, {
        "role": "leader" if int(member_number or 1) == 1 else "member",
        "member_number": int(member_number or 1),
        "name": details.get("leader_name") or details.get("name") or "",
        "email": details.get("email") or "",
        "cnic": details.get("cnic") or "",
    })
    return f"NXT:{record['ticket_token']}" if record.get("ticket_token") else ticket_qr_payload(details)


def team_ticket_qr_items(details):
    """Return one opaque, persisted QR payload for every player on a team ticket."""
    leader = {
        "email": details["email"],
        "name": details["leader_name"] or details["name"],
        "cnic": details.get("cnic", ""),
        "role": "leader",
        "member_number": 1,
    }
    leader_ticket = member_ticket_record(details, leader)
    items = [{
        "name": leader["name"], "email": details["email"], "phone": details["phone"],
        "db_id": leader_ticket.get("id"),
        "ticket_serial": leader_ticket["ticket_serial"],
        "ticket_id": leader_ticket["ticket_serial"],
        "token": leader_ticket["ticket_token"],
    }]
    if details["entry_type"].lower() == "group":
        for member_number, member in enumerate(parse_team_members(details["members"]), start=2):
            if not member["name"]:
                continue
            recipient = {"name": member["name"], "email": member["email"], "cnic": member.get("cnic", ""), "role": "member", "member_number": member_number}
            member_ticket = member_ticket_record(details, recipient)
            items.append({
                "name": member["name"], "email": member["email"], "phone": member["phone"],
                "db_id": member_ticket.get("id"),
                "ticket_serial": member_ticket["ticket_serial"],
                "ticket_id": member_ticket["ticket_serial"],
                "token": member_ticket["ticket_token"],
            })
    return [item for item in items if item["token"]]


def find_registration_by_code(code):
    """Look a ticket up by QR token, ticket serial, or NX-#### reference,
    so the admin can type a ticket number when the camera is unavailable."""
    code = (code or "").strip()
    if not code:
        return None

    ticket = find_ticket_by_code(code)
    return ticket["registration"] if ticket else None


def find_ticket_by_code(code):
    """Resolve a QR/manual value to one ticket, not merely its registration.

    Group members have their own serial, QR token and entry state. The ticket
    token is opaque, so no personal data is embedded in a printable QR code.
    """
    code = (code or "").strip()
    if not code:
        return None

    member = query(
        "SELECT * FROM member_tickets WHERE ticket_token = ? OR UPPER(ticket_serial) = ?",
        (code, code.upper()), one=True,
    )
    if member:
        row = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (member["registration_id"],), one=True)
        if row:
            return {"registration": row, "ticket": member, "kind": "member"}

    row = query(REGISTRATION_SELECT + " WHERE registrations.ticket_token = ?", (code,), one=True)
    if row:
        return {"registration": row, "ticket": row, "kind": "registration"}

    row = query(
        REGISTRATION_SELECT + " WHERE UPPER(registrations.ticket_serial) = ?",
        (code.upper(),),
        one=True,
    )
    if row:
        return {"registration": row, "ticket": row, "kind": "registration"}

    # NX-0007 / nx0007 / plain 7 all resolve to registration id 7
    digits = re.sub(r"\D", "", code)
    if digits:
        row = query(
            REGISTRATION_SELECT + " WHERE registrations.id = ?", (int(digits),), one=True
        )
        if row:
            return {"registration": row, "ticket": row, "kind": "registration"}

    return None


def atomic_checkin(table, ticket_id, staff, gate, override=False, override_reason=""):
    """Atomically check in a ticket using UPDATE ... WHERE to prevent race conditions.
    Returns True if this call performed the check-in, False if already checked in."""
    safe_table_name(table)
    now = datetime.now().isoformat(timespec="seconds")
    with db() as conn:
        if override:
            cur = conn.execute(
                f"UPDATE {table} SET entry_status = 'approved', entry_marked_at = ?, checkin_gate = ?, checked_in_by = ?, override_by = ?, override_at = ?, override_reason = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
                (now, gate, staff, staff, now, override_reason, ticket_id),
            )
        else:
            cur = conn.execute(
                f"UPDATE {table} SET entry_status = 'approved', entry_marked_at = ?, checkin_gate = ?, checked_in_by = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
                (now, gate, staff, ticket_id),
            )
        conn.commit()
        return cur.rowcount > 0


def atomic_reject(table, ticket_id, staff, reason, gate=""):
    """Atomically reject a ticket using UPDATE ... WHERE to prevent race conditions."""
    safe_table_name(table)
    now = datetime.now().isoformat(timespec="seconds")
    with db() as conn:
        cur = conn.execute(
            f"UPDATE {table} SET entry_status = 'rejected', entry_marked_at = ?, entry_note = ?, rejection_reason = ?, rejected_by = ?, rejected_at = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
            (now, reason, reason, staff, now, ticket_id),
        )
        conn.commit()
        return cur.rowcount > 0


def group_checkin_summary(row):
    """Return team arrival counts without conflating one member with the whole team."""
    if (row["entry_type"] or "").lower() != "group":
        leader_ticket = query(
            "SELECT entry_status FROM member_tickets WHERE registration_id = ? AND member_number = 1",
            (row["id"],),
            one=True,
        )
        state = ((leader_ticket["entry_status"] if leader_ticket else row["entry_status"]) or "pending").lower()
        return {"total": 1, "checked_in": int(state == "approved"), "rejected": int(state == "rejected"), "remaining": int(state == "pending")}
    states = [ticket["entry_status"] or "pending" for ticket in query(
        "SELECT entry_status FROM member_tickets WHERE registration_id = ? ORDER BY member_number", (row["id"],)
    )]
    if not states:
        states = [row["entry_status"] or "pending"]
    states = [state.lower() for state in states]
    checked_in = sum(state == "approved" for state in states)
    rejected = sum(state == "rejected" for state in states)
    return {"total": len(states), "checked_in": checked_in, "rejected": rejected, "remaining": len(states) - checked_in - rejected}


def registration_team_id(row):
    return f"TEAM-{int(row['id']):06d}" if (row["entry_type"] or "").lower() == "group" else ""


def registration_member_names(row):
    names = [row["leader_name"] or row["full_name"]]
    if (row["entry_type"] or "").lower() == "group":
        names.extend(member["name"] for member in parse_team_members(row["members"]) if member["name"])
    return names


def member_ticket_statuses(registration_id):
    rows = query(
        """SELECT member_number, member_name, ticket_serial, entry_status, entry_marked_at,
                  checkin_gate, checked_in_by, rejection_reason, entry_note
           FROM member_tickets
           WHERE registration_id = ?
           ORDER BY member_number""",
        (registration_id,),
    )
    return [
        {
            "number": row["member_number"],
            "name": row["member_name"] or f"Member {row['member_number']}",
            "ticket_id": row["ticket_serial"],
            "status": (row["entry_status"] or "pending").lower(),
            "marked_at": pretty_dt(row["entry_marked_at"]) if row["entry_marked_at"] else "",
            "gate": row["checkin_gate"] or "",
            "checked_in_by": row["checked_in_by"] or "",
            "reason": row["rejection_reason"] or row["entry_note"] or "",
        }
        for row in rows
    ]


def qualifier_progress(registration_id):
    rows = query(
        """SELECT q.qualifier_number, q.qualifier_name, e.member_number, e.player_name,
                  e.ticket_serial, e.entry_status, e.entry_marked_at, e.checkin_gate,
                  e.checked_in_by, e.rejection_reason, e.entry_note
           FROM qualifier_entry_tickets e
           JOIN qualifiers q ON q.id = e.qualifier_id
           WHERE e.registration_id = ?
           ORDER BY q.qualifier_number, e.member_number, e.id""",
        (registration_id,),
    )
    items = []
    for row in rows:
        items.append({
            "qualifier_number": row["qualifier_number"],
            "qualifier_name": row["qualifier_name"],
            "member_number": row["member_number"],
            "player_name": row["player_name"] or f"Member {row['member_number']}",
            "ticket_id": row["ticket_serial"],
            "status": (row["entry_status"] or "pending").lower(),
            "marked_at": pretty_dt(row["entry_marked_at"]) if row["entry_marked_at"] else "",
            "gate": row["checkin_gate"] or "",
            "checked_in_by": row["checked_in_by"] or "",
            "reason": row["rejection_reason"] or row["entry_note"] or "",
        })
    total = len(items)
    approved = sum(item["status"] == "approved" for item in items)
    rejected = sum(item["status"] == "rejected" for item in items)
    pending = total - approved - rejected
    return {
        "total": total,
        "approved": approved,
        "rejected": rejected,
        "pending": pending,
        "items": items,
    }


def ticket_payload(ticket_record, status, message):
    """Build the JSON payload shown inside the scanner overlay."""
    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    is_member = ticket_record["kind"] == "member"
    entry_status = (ticket["entry_status"] or "pending").lower()
    team_summary = group_checkin_summary(row)
    holder_name = ticket["member_name"] if is_member else row["full_name"]
    holder_email = ticket["member_email"] if is_member else row["email"]
    checkin_gate = ticket["checkin_gate"] or ""
    checked_in_by = ticket["checked_in_by"] or ""
    marked_at = ticket["entry_marked_at"] or ""
    details = registration_details(row["id"])
    payable = details["total_payable"] if details else "Rs. 0"
    paid = details["amount_paid"] if details else "Rs. 0"
    due = details["remaining_amount"] if details else "Rs. 0"
    qualifier_summary = qualifier_progress(row["id"])
    return {
        "ok": True,
        "kind": ticket_record["kind"],
        "status": status,
        "message": message,
        "id": row["id"],
        "ticket_number": ticket["ticket_serial"] or make_ticket_serial(row["id"], row["game_title"]),
        "ref": f"NX-{row['id']:04d}",
        "name": holder_name or row["full_name"],
        "player_name": holder_name or row["full_name"],
        "email": holder_email or row["email"],
        "phone": row["phone"],
        "cnic": row["cnic"],
        "institution": row["university"],
        "game": row["game_title"],
        "platform": row["platform"],
        "entry_type": (row["entry_type"] or "").title(),
        "team_name": row["team_name"] or "",
        "team_id": registration_team_id(row),
        "team_members": registration_member_names(row),
        "group_leader": row["leader_name"] or row["full_name"],
        "total_members": team_summary["total"],
        "team_checked_in": team_summary["checked_in"],
        "team_rejected": team_summary["rejected"],
        "team_remaining": team_summary["remaining"],
        "member_progress": member_ticket_statuses(row["id"]),
        "qualifier_progress": qualifier_summary,
        "qualifier_total": qualifier_summary["total"],
        "qualifier_approved": qualifier_summary["approved"],
        "qualifier_rejected": qualifier_summary["rejected"],
        "qualifier_pending": qualifier_summary["pending"],
        "payment_status": (row["payment_status"] or "").title(),
        "amount_paid": paid,
        "payment_due": due,
        "total_payable": payable,
        "applied_at": pretty_dt(row["created_at"]),
        "verified_at": pretty_dt(row["verified_at"]) if row["verified_at"] else "",
        "entry_status": entry_status,
        "entry_marked_at": pretty_dt(marked_at) if marked_at else "",
        "ticket_status": "CHECKED IN" if entry_status == "approved" else ("REJECTED" if entry_status == "rejected" else ("VALID" if (row["payment_status"] or "").lower() == "paid" else "UNPAID")),
        "checkin_status": "Already Used" if entry_status == "approved" else "Unused",
        "checkin_gate": checkin_gate,
        "checked_in_by": checked_in_by,
        "rejection_reason": ticket["rejection_reason"] or ticket["entry_note"] or "",
        "proof_url": url_for("registration_proof", reg_id=row["id"]) if row["proof_image"] else "",
        "view_url": url_for("registration_view", reg_id=row["id"]),
}


def log_ticket_action(ticket_record, action, previous_status, new_status, reason="", gate=""):
    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    execute(
        """INSERT INTO ticket_audit_log
           (registration_id, ticket_serial, action, previous_status, new_status, reason, gate_location, staff_username, created_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (row["id"], ticket["ticket_serial"], action, previous_status, new_status,
         reason, gate, session.get("admin_username", ADMIN_USERNAME), datetime.now().isoformat(timespec="seconds")),
    )


def ticket_export_values(row):
    details = registration_details(row["id"])
    leader_ticket = None
    if details:
        leader_ticket = member_ticket_record(details, {
            "member_number": 1,
            "name": row["leader_name"] or row["full_name"],
            "email": row["email"],
            "cnic": row["cnic"] or "",
        })
    visible_ticket_id = (
        leader_ticket["ticket_serial"]
        if leader_ticket and leader_ticket.get("ticket_serial")
        else row["ticket_serial"] or make_ticket_serial(row["id"])
    )
    total_payable = str(details["total_payable"] if details else "")
    amount_paid = str(details["amount_paid"] if details else "")
    remaining_amount = str(details["remaining_amount"] if details else "")
    roster = ticket_member_roster(details) if details else []
    roles = []
    for player in roster:
        role = "Leader / Solo" if int(player["number"]) == 1 else f"Member {player['number']}"
        parts = [
            role,
            player.get("name") or "",
            f"Email: {player.get('email') or '-'}",
            f"Phone: {player.get('phone') or '-'}",
            f"CNIC: {player.get('cnic') or '-'}",
            f"Ticket ID: {player.get('ticket_id') or player.get('serial') or '-'}",
        ]
        roles.append(" | ".join(parts))
    roster_text = "\n".join([f"Total players: {len(roster) or 1}"] + roles)
    selected_filter_qid = request.args.get("qualifier_id", type=int) if has_request_context() else None
    qualifier_text = compact_qualifier_export_text(details, selected_filter_qid, include_refs=True)
    if not qualifier_text and details:
        game_schedule = ""
        if details.get("game_date"):
            game_schedule = f"Date: {details['game_date']}"
            if details.get("game_time"):
                game_schedule += f" {details['game_time']}"
        game_loc = ""
        if details.get("game_venue"):
            game_loc = f"Venue: {details['game_venue']}"
        if game_schedule or game_loc:
            qualifier_text = f"{game_schedule}\n{game_loc}".strip()
    return [
        f"NX-{row['id']:04d}",
        visible_ticket_id,
        f"{row['full_name']}\n{row['email']}\n{row['phone']}\nCNIC: {row['cnic'] or ''}",
        row["game_title"],
        (row["entry_type"] or "").title(),
        f"{row['team_name'] or 'Individual'}\n{registration_team_id(row)}",
        roster_text,
        qualifier_text,
        total_payable,
        amount_paid,
        remaining_amount,
        str(details.get("fee_breakdown", "") if details else ""),
        (row["payment_status"] or "").replace("_", " ").title(),
        "Checked In" if row["entry_status"] == "approved" else ("Rejected" if row["entry_status"] == "rejected" else "Not Arrived"),
        pretty_dt(row["created_at"]),
    ]