"""Registration data assembly and admin list filtering.""" from flask import request from nexora.database.connection import query from nexora.utils.helpers import amount_value from nexora.utils.formatting import pretty_dt from nexora.utils.team import parse_team_members, team_roster_text, team_total_count from nexora.services.pricing import registration_effective_price from nexora.services.qualifiers import calc_qualifier_fee from nexora.services.ticket_codes import make_ticket_serial def _money_text(value): return f"Rs. {float(value or 0):,.0f}" def registration_details(registration_id): row = query( """ SELECT registrations.*, COALESCE(games.title, 'Selected game') AS game_title, COALESCE(games.platform, '-') AS platform, COALESCE(games.individual_price, '') AS individual_price, COALESCE(games.group_price, '') AS group_price, COALESCE(games.price_for_2, '') AS price_for_2, COALESCE(games.price_for_3, '') AS price_for_3, COALESCE(games.price_for_4, '') AS price_for_4, COALESCE(games.discount_pct, '') AS discount_pct, COALESCE(games.discount_from, '') AS discount_from, COALESCE(games.discount_to, '') AS discount_to, COALESCE(games.team_label, '') AS team_label, COALESCE(games.registration_label, '') AS registration_label, COALESCE(games.venue, '') AS game_venue, COALESCE(games.venue_address, '') AS game_venue_address, COALESCE(games.event_date, '') AS game_date, COALESCE(games.event_time, '') AS game_time, COALESCE(games.event_end_time, '') AS game_event_end_time, COALESCE(games.is_free, '0') AS is_free FROM registrations LEFT JOIN games ON games.id = registrations.game_id WHERE registrations.id = ? """, (registration_id,), one=True, ) if not row: return None entry_type = (row["entry_type"] or "individual").lower() display_member_count = team_total_count(row["members"], entry_type) ticket_number = row["ticket_serial"] or make_ticket_serial(row["id"], row["game_title"]) live_base_price = registration_effective_price(row, entry_type, display_member_count) selected_q_ids = [int(qid) for qid in (row["selected_qualifiers"] if "selected_qualifiers" in row.keys() else "").split(",") if qid.strip().isdigit()] qualifier_pricing = calc_qualifier_fee(row, selected_q_ids) if selected_q_ids else { "total_fee": "Rs. 0", "total_value": 0.0, "count": 0, "items": [], "total_original_fee": "Rs. 0", "total_discount_fee": "Rs. 0", "discount_active": False, "discount_pct": 0, "breakdown": "", } saved_q_fee_str = (row["qualifier_fee"] or "").strip() if "qualifier_fee" in row.keys() else "" if selected_q_ids and saved_q_fee_str and saved_q_fee_str != qualifier_pricing.get("total_fee", ""): qualifier_pricing = { "total_fee": saved_q_fee_str, "total_value": amount_value(saved_q_fee_str), "count": len(selected_q_ids), "items": [{ "name": "Qualifier Entry (Historical)", "original_fee": saved_q_fee_str, "discount_fee": "Rs. 0", "final_fee": saved_q_fee_str }], "total_original_fee": saved_q_fee_str, "total_discount_fee": "Rs. 0", "discount_active": False, "discount_pct": 0, "breakdown": f"Frozen historical fee: {saved_q_fee_str}", "is_historical": True, } live_base_value = amount_value(live_base_price["discounted"]) qualifier_value = float(qualifier_pricing.get("total_value") or 0) # Overwrite base_price with frozen values if available saved_orig = (row["original_price_saved"] or "").strip() if "original_price_saved" in row.keys() else "" saved_disc = (row["discounted_price_saved"] or "").strip() if "discounted_price_saved" in row.keys() else "" base_price = { "original": saved_orig if saved_orig else live_base_price["original"], "discounted": saved_disc if saved_disc else live_base_price["discounted"], "active": (amount_value(saved_orig) > amount_value(saved_disc)) if saved_orig and saved_disc else live_base_price["active"] } base_value = amount_value(base_price["discounted"]) # When qualifiers enabled, qualifier fee IS the entry fee — no game fee on top if selected_q_ids and qualifier_value > 0: computed_payable = _money_text(qualifier_value) else: computed_payable = _money_text(base_value + qualifier_value) # Use frozen total_payable (game price + qualifier fee both frozen at registration time) saved_payable = (row["total_payable_saved"] or "").strip() if "total_payable_saved" in row.keys() else "" payable_amount = saved_payable if saved_payable else computed_payable amount_paid = (row["amount_paid"] or "").strip() if "amount_paid" in row.keys() else "" payment_status = (row["payment_status"] or "unpaid").lower() if not amount_paid: amount_paid = payable_amount if payment_status == "paid" else "Rs. 0" payable_value = amount_value(payable_amount) paid_value = amount_value(amount_paid) remaining_value = max(0, payable_value - paid_value) if payment_status == "paid": remaining_amount = "Rs. 0" elif payment_status == "under_verification": remaining_amount = "Pending" else: remaining_amount = f"Rs. {remaining_value:,.0f}" if payable_value else "Not recorded" # Graceful fallback for old records snap_orig = (row.get("original_amount") or "").strip() if getattr(row, "get", lambda k: None)("original_amount") else saved_orig snap_disc = (row.get("discount_amount") or "").strip() if getattr(row, "get", lambda k: None)("discount_amount") else "" if not snap_disc and snap_orig and saved_payable: diff = max(0, amount_value(snap_orig) - amount_value(saved_payable)) snap_disc = f"Rs. {diff:,.0f}" if diff else "Rs. 0" members = parse_team_members(row["members"]) return { "id": row["id"], "ref": f"NX-{row['id']:04d}", "name": row["full_name"], "email": row["email"], "phone": row["phone"], "cnic": row["cnic"], "institution_type": row["institution_type"] or "University", "university": row["university"], "roll_no": row["roll_no"] or "Not provided", "entry_type": entry_type.title(), "game": row["game_title"], "platform": row["platform"], "price": base_price["discounted"], "original_price": base_price["original"], "has_discount": base_price["active"], "base_price": base_price["discounted"], "base_original_price": base_price["original"], "base_discount_amount": _money_text(max(0, amount_value(base_price["original"]) - base_value)), "snapshot_original": snap_orig or saved_payable or payable_amount, "snapshot_discount": snap_disc or "Rs. 0", "snapshot_type": (row.get("discount_type_at_registration") or "").strip() if getattr(row, "get", lambda k: None)("discount_type_at_registration") else "", "snapshot_val": (row.get("discount_value_at_registration") or "").strip() if getattr(row, "get", lambda k: None)("discount_value_at_registration") else "", "fee_breakdown": ( (f"Qualifier fee: {payable_amount}" + ( " (" + ", ".join([ f"{item['name']}: {item['original_fee']}" + (f" - {item['discount_fee']} off" if qualifier_pricing.get("discount_active") else "") + f" = {item['final_fee']}" for item in qualifier_pricing.get("items", []) ]) + ")" if not qualifier_pricing.get("is_historical") else "" )) if selected_q_ids else f"Game/team fee: {base_price['discounted']}" + (f" after discount from {base_price['original']}" if base_price["active"] else "") + f"; Total: {payable_amount}" ), "team_name": row["team_name"] or "Individual entry", "leader_name": row["leader_name"] or row["full_name"], "members": row["members"] or "No team members added", "member_count": display_member_count, "team_roster": team_roster_text(row), "team_member_names": [member["name"] for member in members if member["name"]], "payment_method": row["payment_method"], "payment_status": payment_status.replace("_", " ").title(), "total_payable": payable_amount, "amount_paid": amount_paid, "remaining_amount": remaining_amount, "transaction_reference": (row["transaction_reference"] or "Not provided") if "transaction_reference" in row.keys() else "Not provided", "created_at": pretty_dt(row["created_at"]), "ticket_issued_at": pretty_dt(row["ticket_issued_at"] or row["verified_at"] or row["created_at"]) if "ticket_issued_at" in row.keys() else pretty_dt(row["verified_at"] or row["created_at"]), "ticket_number": ticket_number, "ticket_id": f"NX-{row['id']:04d}", "team_id": f"TEAM-{row['id']:06d}" if entry_type == "group" else "Individual", "ticket_token": row["ticket_token"], "entry_status": (row["entry_status"] or "pending").lower() if "entry_status" in row.keys() else "pending", "verified_at": pretty_dt(row["verified_at"]) if row["verified_at"] else "", "selected_qualifiers": row["selected_qualifiers"] if "selected_qualifiers" in row.keys() else "", "qualifier_count": int(row["qualifier_count"] or 0) if "qualifier_count" in row.keys() else 0, "qualifier_fee": (row["qualifier_fee"] if "qualifier_fee" in row.keys() and row["qualifier_fee"] else qualifier_pricing.get("total_fee", "")) if selected_q_ids else (row["qualifier_fee"] if "qualifier_fee" in row.keys() else ""), "qualifier_pricing": qualifier_pricing, "game_id": row["game_id"], "game_venue": row["game_venue"] if "game_venue" in row.keys() else "", "game_venue_address": row["game_venue_address"] if "game_venue_address" in row.keys() else "", "game_date": row["game_date"] if "game_date" in row.keys() else "", "game_time": row["game_time"] if "game_time" in row.keys() else "", "game_event_end_time": row["game_event_end_time"] if "game_event_end_time" in row.keys() else "", "team_label": row["team_label"], "registration_label": row["registration_label"], } def build_registration_filters(): """Read filter args and return (where_sql, args, active_filters).""" status = request.args.get("status", "").strip() search = request.args.get("q", "").strip() game_id = request.args.get("game_id", type=int) qualifier_id = request.args.get("qualifier_id", type=int) team = request.args.get("team", "").strip() leader = request.args.get("leader", "").strip() player = request.args.get("player", "").strip() venue = request.args.get("venue", "").strip() date_from = request.args.get("date_from", "").strip() date_to = request.args.get("date_to", "").strip() clauses, args = [], [] entry_type = request.args.get("entry_type", "").strip().lower() payment_method = request.args.get("payment_method", "").strip() checkin_status = request.args.get("checkin_status", "").strip().lower() if status in ("paid", "unpaid", "under_verification", "partial", "refunded"): clauses.append("registrations.payment_status = ?") args.append(status) if entry_type in ("individual", "group"): clauses.append("registrations.entry_type = ?") args.append(entry_type) if payment_method: clauses.append("registrations.payment_method = ?") args.append(payment_method) if checkin_status in ("pending", "approved", "rejected"): clauses.append("COALESCE(registrations.entry_status, 'pending') = ?") args.append(checkin_status) if search: clauses.append( "(registrations.full_name LIKE ? OR registrations.email LIKE ?" " OR registrations.phone LIKE ? OR registrations.team_name LIKE ?" " OR registrations.leader_name LIKE ? OR registrations.members LIKE ?" " OR registrations.ticket_serial LIKE ? OR registrations.transaction_reference LIKE ?" " OR EXISTS (SELECT 1 FROM qualifier_entry_tickets qet WHERE qet.registration_id = registrations.id" " AND (qet.player_name LIKE ? OR qet.ticket_serial LIKE ?)))" ) like = f"%{search}%" args.extend([like, like, like, like, like, like, like, like, like, like]) if game_id: clauses.append("registrations.game_id = ?") args.append(game_id) if qualifier_id: clauses.append( "EXISTS (SELECT 1 FROM qualifier_entry_tickets qet" " WHERE qet.registration_id = registrations.id AND qet.qualifier_id = ?)" ) args.append(qualifier_id) if team: clauses.append("registrations.team_name LIKE ?") args.append(f"%{team}%") if leader: clauses.append("(registrations.leader_name LIKE ? OR registrations.full_name LIKE ?)") like = f"%{leader}%" args.extend([like, like]) if player: clauses.append( "(registrations.full_name LIKE ? OR registrations.members LIKE ?" " OR EXISTS (SELECT 1 FROM qualifier_entry_tickets qet" " WHERE qet.registration_id = registrations.id AND qet.player_name LIKE ?))" ) like = f"%{player}%" args.extend([like, like, like]) if venue: clauses.append( "EXISTS (SELECT 1 FROM qualifier_entry_tickets qet" " JOIN qualifiers q ON q.id = qet.qualifier_id" " WHERE qet.registration_id = registrations.id AND q.location LIKE ?)" ) args.append(f"%{venue}%") if date_from: clauses.append("date(registrations.created_at) >= date(?)") args.append(date_from) if date_to: clauses.append("date(registrations.created_at) <= date(?)") args.append(date_to) where = (" WHERE " + " AND ".join(clauses)) if clauses else "" email_status = request.args.get("email_status", "").strip().lower() filters = { "status": status, "q": search, "game_id": game_id or "", "qualifier_id": qualifier_id or "", "team": team, "leader": leader, "player": player, "venue": venue, "date_from": date_from, "date_to": date_to, "entry_type": entry_type, "payment_method": payment_method, "checkin_status": checkin_status, "email_status": email_status, } return where, tuple(args), filters REGISTRATION_SELECT = """ SELECT registrations.*, COALESCE(games.title, 'Deleted game') AS game_title, COALESCE(games.platform, '-') AS platform, COALESCE(games.event_date, '') AS game_date, COALESCE(games.event_time, '') AS game_time, COALESCE(games.venue, '') AS game_venue, COALESCE(games.venue_address, '') AS game_venue_address FROM registrations LEFT JOIN games ON games.id = registrations.game_id """