"""Qualifier entry tickets: lookup, verification and gate decisions.""" from datetime import datetime from flask import jsonify, session, url_for from nexora.config import ADMIN_USERNAME from nexora.database.connection import execute, query from nexora.utils.formatting import format_qualifier_parts, pretty_dt, split_venue_location from nexora.services.registrations import REGISTRATION_SELECT, registration_details def qualifier_ticket_reference_text(registration_id, qualifier_id=None): qualifier_clause = " AND e.qualifier_id = ?" if qualifier_id else "" args = (registration_id, qualifier_id) if qualifier_id else (registration_id,) rows = query( """SELECT q.qualifier_number, q.qualifier_name, e.member_number, e.player_name, e.role, e.ticket_serial, e.entry_status FROM qualifier_entry_tickets e LEFT JOIN qualifiers q ON q.id = e.qualifier_id WHERE e.registration_id = ? """ + qualifier_clause + """ ORDER BY q.qualifier_number, e.member_number, e.id""", args, ) if not rows: return "" lines = [] for row in rows: q_label = f"Q{row['qualifier_number']} {row['qualifier_name']}".strip() status = (row["entry_status"] or "pending").replace("_", " ").title() lines.append(f"{q_label} | {row['role']} {row['player_name']} | Ticket ID: {row['ticket_serial']} | {status}") return "\n".join(lines) def find_qualifier_entry_by_code(code): """Resolve an NQ: token to its qualifier entry ticket with related data.""" token = code if ":" in token: token = token.split(":", 1)[1] entry = query( "SELECT * FROM qualifier_entry_tickets WHERE ticket_token = ? OR UPPER(ticket_serial) = ?", (token, token.upper()), one=True, ) if not entry: return None reg = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (entry["registration_id"],), one=True) qual = query("SELECT * FROM qualifiers WHERE id = ?", (entry["qualifier_id"],), one=True) if not reg or not qual: return None return {"entry": dict(entry), "registration": dict(reg), "qualifier": dict(qual)} def qualifier_sequence_warning(entry, reg, qual): current_number = int(qual.get("qualifier_number") or 0) if current_number <= 1: return "" previous = query( """SELECT q.qualifier_number, q.qualifier_name, e.entry_status, e.ticket_serial FROM qualifier_entry_tickets e JOIN qualifiers q ON q.id = e.qualifier_id WHERE e.registration_id = ? AND e.member_number = ? AND q.qualifier_number < ? ORDER BY q.qualifier_number""", (reg["id"], entry["member_number"], current_number), ) flagged = [ f"Q{row['qualifier_number']} {row['qualifier_name']}: {(row['entry_status'] or 'pending').title()} ({row['ticket_serial']})" for row in previous if (row["entry_status"] or "pending").lower() != "approved" ] if not flagged: return "" return "Earlier qualifier not approved. Review before entry: " + "; ".join(flagged) 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 = [ { "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 "", } for row in rows ] approved = sum(item["status"] == "approved" for item in items) rejected = sum(item["status"] == "rejected" for item in items) return { "total": len(items), "approved": approved, "rejected": rejected, "pending": len(items) - approved - rejected, "items": items, } def qualifier_entry_payload(qrecord, status, message): """Build the scanner-facing payload for a qualifier entry QR.""" entry = qrecord["entry"] reg = qrecord["registration"] qual = qrecord["qualifier"] parts = format_qualifier_parts(qual.get("qualifier_date", "")) venue, city = split_venue_location(qual.get("location", "")) game_title = reg["game_title"] if "game_title" in reg.keys() else "" player = entry["player_name"] or reg["leader_name"] or reg["full_name"] entry_status = (entry["entry_status"] or "pending").lower() sequence_warning = qualifier_sequence_warning(entry, reg, qual) details = registration_details(reg["id"]) payable = details["total_payable"] if details else "" paid = details["amount_paid"] if details else (reg["amount_paid"] if "amount_paid" in reg.keys() else "") due = details["remaining_amount"] if details else "" qualifier_summary = _qualifier_progress(reg["id"]) member_summary = _member_ticket_statuses(reg["id"]) member_approved = sum(item["status"] == "approved" for item in member_summary) member_rejected = sum(item["status"] == "rejected" for item in member_summary) return { "ok": True, "kind": "qualifier", "status": status, "message": message, "title": f"Q{qual['qualifier_number']} - {qual['qualifier_name']}", "name": player, "player_name": player, "email": entry.get("player_email") or reg["email"] if "email" in reg.keys() else "", "phone": reg["phone"] if "phone" in reg.keys() else "", "cnic": entry.get("player_cnic") or reg["cnic"] if "cnic" in reg.keys() else "", "game": game_title, "platform": reg["platform"] if "platform" in reg.keys() else "", "entry_type": f"Qualifier Q{qual['qualifier_number']}", "ref": f"NX-{int(reg['id']):04d}", "group_leader": reg["leader_name"] or reg["full_name"], "team_name": reg["team_name"] if "team_name" in reg.keys() else "", "ticket_number": entry["ticket_serial"], "ticket_status": entry_status.title(), "ticket_serial": entry["ticket_serial"], "token": entry.get("ticket_token") or entry["ticket_serial"], "rejection_reason": entry["rejection_reason"] or entry["entry_note"] or "", "payment_status": (reg["payment_status"] or "").replace("_", " ").title(), "payment_status_raw": (reg["payment_status"] or "").lower(), "amount_paid": paid, "total_payable": payable, "payment_due": due, "total_members": len(member_summary), "team_checked_in": member_approved, "team_rejected": member_rejected, "team_remaining": len(member_summary) - member_approved - member_rejected, "member_progress": member_summary, "qualifier_progress": qualifier_summary, "qualifier_total": qualifier_summary["total"], "qualifier_approved": qualifier_summary["approved"], "qualifier_rejected": qualifier_summary["rejected"], "qualifier_pending": qualifier_summary["pending"], "proof_url": url_for("registration_proof", reg_id=reg["id"]) if ("proof_image" in reg.keys() and reg["proof_image"]) else "", "qualifier_number": qual["qualifier_number"], "qualifier_name": qual["qualifier_name"], "qualifier_date": parts["date"], "qualifier_time": parts["time"], "venue": venue, "city": city, "member_number": entry["member_number"], "role": entry["role"], "team_leader": reg["leader_name"] or reg["full_name"], "ticket_id": entry["ticket_serial"], "registration_id": reg["id"], "entry_id": entry["id"], "qualifier_id": qual["id"], "entry_status": entry_status, "entry_marked_at": pretty_dt(entry["entry_marked_at"]) if entry["entry_marked_at"] else "", "checkin_gate": entry["checkin_gate"], "checked_in_by": entry["checked_in_by"], "sequence_warning": sequence_warning, "view_url": url_for("registration_view", reg_id=reg["id"]), } def _verify_qualifier_entry(qrecord): """Scanner lookup for a qualifier entry QR (never modifies records).""" entry = qrecord["entry"] reg = qrecord["registration"] entry_status = (entry["entry_status"] or "pending").lower() payment_status = (reg["payment_status"] or "unpaid").lower() if entry_status == "approved": status, message = "already", ( f"Already Checked In Qualifier: Q{int(qrecord['qualifier'].get('qualifier_number') or 0)} " f"Player: {entry['player_name']} Ticket ID: {entry['ticket_serial']}" ) elif entry_status == "rejected": reason_text = entry.get("rejection_reason") or entry.get("entry_note") or "No reason provided" status, message = "rejected", f"ENTRY REJECTED - {reason_text}" elif payment_status in ("unpaid", "under_verification"): status, message = "payment_required", "PAYMENT NOT VERIFIED" else: if payment_status == "paid": status, message = "paid_ready", "PAID — QUALIFIER ENTRY READY" else: status, message = "found", "Qualifier Ticket Found - Decide Entry" warning = qualifier_sequence_warning(entry, reg, qrecord["qualifier"]) if warning: status, message = "suspicious", warning data = qualifier_entry_payload(qrecord, status, message) data["payment_status_raw"] = payment_status data["entry_status_raw"] = entry_status return jsonify(data) def _qualifier_entry_decision(qrecord, decision, reason, gate, override, override_reason): """Approve/reject a single (qualifier x member) entry. Independent per qualifier — checking in Q1 never touches Q2/Q3/Q4.""" entry = qrecord["entry"] reg = qrecord["registration"] entry_status = (entry["entry_status"] or "pending").lower() payment_status = (reg["payment_status"] or "unpaid").lower() marked_at = datetime.now().isoformat(timespec="seconds") if decision in ("approved", "approved_team") and payment_status in ("unpaid", "under_verification") and not override: return jsonify({ "ok": False, "status": "payment_required", "message": "Cannot approve entry: payment is " + payment_status.replace("_", " ").title() + ". Confirm payment first." }), 403 if decision == "rejected": if not reason: return jsonify({"ok": False, "status": "invalid", "message": "Select a rejection reason before rejecting this ticket."}), 400 if entry_status == "rejected" and not override: return jsonify(qualifier_entry_payload(qrecord, "already", ( f"Already Checked In Qualifier: Q{int(qrecord['qualifier'].get('qualifier_number') or 0)} " f"Player: {entry['player_name']} Ticket ID: {entry['ticket_serial']}" ))), 409 execute( """UPDATE qualifier_entry_tickets SET entry_status = 'rejected', entry_marked_at = ?, entry_note = ?, rejection_reason = ?, rejected_by = ?, rejected_at = ?, checkin_gate = ? WHERE id = ?""", (marked_at, reason, reason, session.get("admin_username", ADMIN_USERNAME), marked_at, gate or "", entry["id"]), ) fresh = find_qualifier_entry_by_code(entry["ticket_token"]) return jsonify(qualifier_entry_payload(fresh or qrecord, "rejected", "Entry Rejected")) # approved / approved_team if entry_status == "approved" and not override: return jsonify(qualifier_entry_payload(qrecord, "already", ( f"Already Checked In Qualifier: Q{int(qrecord['qualifier'].get('qualifier_number') or 0)} " f"Player: {entry['player_name']} Ticket ID: {entry['ticket_serial']}" ))), 409 if entry_status == "approved" and override and not override_reason: return jsonify({"ok": False, "status": "invalid", "message": "An admin override reason is required."}), 400 staff = session.get("admin_username", ADMIN_USERNAME) execute( """UPDATE qualifier_entry_tickets SET entry_status = 'approved', entry_marked_at = ?, checkin_gate = ?, checked_in_by = ?, override_by = ?, override_at = ?, override_reason = ?, rejected_by = '', rejected_at = '', rejection_reason = '' WHERE id = ?""", (marked_at, gate or "Main Gate", staff, staff if override else "", marked_at if override else "", override_reason or "" if override else "", entry["id"]), ) execute( """INSERT INTO ticket_audit_log (registration_id, ticket_serial, action, previous_status, new_status, reason, gate_location, staff_username, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", (reg["id"], entry["ticket_serial"], "qualifier_override_approved" if override else "qualifier_approved", entry_status, "approved", override_reason or "", gate or "Main Gate", staff, marked_at), ) fresh = find_qualifier_entry_by_code(entry["ticket_token"]) message = "Qualifier Entry Approved" if not override else "Qualifier Entry Approved by Admin Override" return jsonify(qualifier_entry_payload(fresh or qrecord, "approved", message)) def compact_qualifier_export_text(details, qualifier_id=None, include_refs=True): if not details: return "None" q_count = int(details.get("qualifier_count", 0) or 0) if q_count <= 0: return "None" selected = [x.strip() for x in (details.get("selected_qualifiers") or "").split(",") if x.strip().isdigit()] if qualifier_id: selected = [x for x in selected if int(x) == int(qualifier_id)] pricing = details.get("qualifier_pricing") or {} fee_type = pricing.get("fee_type") or "separate" total_fee = pricing.get("total_fee") or details.get("qualifier_fee") or "Rs. 0" lines = [f"Package: {total_fee}" if fee_type == "package" else f"Fee: {total_fee}"] if selected: placeholders = ",".join("?" * len(selected)) q_rows = query( f"SELECT id, qualifier_number, qualifier_name, fee, qualifier_date, location FROM qualifiers WHERE id IN ({placeholders}) ORDER BY qualifier_number", [int(x) for x in selected], ) for qr in q_rows: dt_parts = format_qualifier_parts(qr["qualifier_date"]) venue, city = split_venue_location(qr["location"]) parts = [f"Q{qr['qualifier_number']} {qr['qualifier_name']}"] if fee_type != "package" and qr["fee"]: parts.append(str(qr["fee"])) if venue and venue != "TBD": parts.append(f"Venue: {venue}") if city: parts.append(f"Location: {city}") if dt_parts["date"]: parts.append(f"Date: {dt_parts['date']}") if dt_parts["time"]: parts.append(f"Time: {dt_parts['time']}") lines.append(" | ".join(parts)) if include_refs: refs = qualifier_ticket_reference_text(details["id"], qualifier_id) if refs: lines.append("Ticket References:") lines.append(refs) return "\n".join(lines)