"""Routes: admin exports"""
import io
import re
from datetime import datetime
from flask import Response, flash, redirect, request, url_for

from nexora.config import UPLOAD_DIR
from nexora.core import app
from nexora.database.connection import db, query
from nexora.utils.formatting import pretty_dt
from nexora.utils.team import parse_team_members, team_roster_text, team_total_count
from nexora.services.images import static_file_abs
from nexora.services.pricing import discount_active, discount_pct_value
from nexora.services.ticket_codes import make_ticket_serial, make_ticket_token
from nexora.services.registrations import (
    REGISTRATION_SELECT,
    build_registration_filters,
    registration_details,
)
from nexora.services.qualifiers import qualifier_entry_list
from nexora.services.qualifier_entries import compact_qualifier_export_text
from nexora.services.tickets import registration_team_id, ticket_export_values, ticket_member_roster
from nexora.security.permissions import admin_required, permission_required
from nexora.database.schema import (
    ensure_member_tickets_for_registration,
    ensure_qualifier_entry_tickets_for_registration,
)


EXPORT_HEADERS = [
    "ID", "Ticket", "Player / Contact", "Game", "Type", "Team / Team ID", "Members",
    "Qualifiers", "Total", "Paid", "Remaining", "Fee Breakdown", "Payment", "Check-In", "Registered",
]


def _export_rows(order_by="games.title, registrations.id DESC"):
    where, args, filters = build_registration_filters()
    rows = list(query(REGISTRATION_SELECT + where + f" ORDER BY {order_by}", args))
    email_filter = filters.get("email_status", "")
    if email_filter:
        filtered = []
        for row in rows:
            email_count_row = query(
                "SELECT COUNT(*) AS c FROM email_logs WHERE registration_id = ?",
                (row["id"],),
                one=True,
            )
            email_count = email_count_row["c"] if email_count_row else 0
            if email_filter == "sent" and email_count > 0:
                filtered.append(row)
            elif email_filter == "not_sent" and email_count == 0:
                filtered.append(row)
            elif email_filter == "multiple" and email_count >= 2:
                filtered.append(row)
        rows = filtered
    return rows, filters


def _filter_summary_text(filters):
    """Human-readable line of the active filters shown on exported PDFs."""
    from xml.sax.saxutils import escape as _esc
    labels = {
        "q": "Search", "game_id": "Game", "status": "Payment Status",
        "qualifier_id": "Qualifier", "team": "Team", "leader": "Team Leader",
        "player": "Player", "venue": "Venue",
        "entry_type": "Type", "payment_method": "Payment Method",
        "checkin_status": "Check-In", "date_from": "From", "date_to": "To",
    }
    parts = []
    for key, label in labels.items():
        value = str(filters.get(key) or "").strip()
        if not value:
            continue
        pretty = value.replace("_", " ").title() if key in ("status", "checkin_status") else value
        parts.append(f"{label}: {_esc(pretty)}")
    return "Filters - " + (" | ".join(parts) if parts else "None (all registrations)")


@app.route("/admin/registrations/export")
@admin_required
@permission_required("export_excel_reports")
def export_registrations():
    # Exports MUST honour the exact same active filters as the Registrations
    # page — identical SQL built by the identical helper, so the file can
    # never contain more/different rows than the screen shows.
    rows, _filters = _export_rows()
    stamp = datetime.now().strftime("%Y%m%d_%H%M")

    from openpyxl import Workbook
    from openpyxl.drawing.image import Image as ExcelImage
    from openpyxl.formatting.rule import FormulaRule
    from openpyxl.styles import Alignment, Font, PatternFill
    from openpyxl.worksheet.datavalidation import DataValidation
    from openpyxl.utils import get_column_letter
    import io

    wb = Workbook()
    wb.remove(wb.active)
    games = query("SELECT id, title, max_members, team_label, registration_label FROM games WHERE deleted_at = '' ORDER BY title")
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    _logo_ref = (settings["site_logo"] if settings and "site_logo" in settings.keys() else "") or ""
    logo_path = static_file_abs(_logo_ref) or (UPLOAD_DIR / _logo_ref)
    header_fill = PatternFill("solid", fgColor="FF7F1D1D")
    title_fill = PatternFill("solid", fgColor="FF18070B")
    status_fills = {
        "Paid": PatternFill("solid", fgColor="FFC6EFCE"), "Unpaid": PatternFill("solid", fgColor="FFFFC7CE"),
        "Under Verification": PatternFill("solid", fgColor="FFFFEB9C"),
        "Partial": PatternFill("solid", fgColor="FFFCE4D6"), "Refunded": PatternFill("solid", fgColor="FFBDD7EE"),
        "Checked In": PatternFill("solid", fgColor="FFC6EFCE"), "Rejected": PatternFill("solid", fgColor="FFFFC7CE"),
    }
    widths = [12, 26, 34, 22, 14, 28, 64, 44, 16, 14, 16, 42, 18, 18, 22]
    game_rows = {game["id"]: [row for row in rows if row["game_id"] == game["id"]] for game in games}
    games = [game for game in games if game_rows.get(game["id"])]
    if not games:
        games = [{"id": None, "title": "Filtered Results", "max_members": "1", "team_label": "", "registration_label": ""}]
        game_rows = {None: rows}

    for game in games:
        sheet_headers = EXPORT_HEADERS[:]
        game_label = game["registration_label"] or game["team_label"] or "Team / Nick Name"
        sheet_headers[5] = game_label
        title = re.sub(r"[\\/*?:\[\]]", "", game["title"])[:31] or "Game"
        ws = wb.create_sheet(title=title)
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(sheet_headers))
        heading = ws.cell(1, 1, f"Nexora Esports Championship - {game['title']} Registrations")
        heading.fill = title_fill
        heading.font = Font(color="FFFFFFFF", bold=True, size=15)
        heading.alignment = Alignment(horizontal="center", vertical="center")
        ws.row_dimensions[1].height = 28
        if logo_path.is_file():
            try:
                # Convert through PIL to PNG bytes so any format (webp/jfif)
                # embeds cleanly regardless of extension support.
                from PIL import Image as PILImage
                logo_buf = io.BytesIO()
                with PILImage.open(logo_path) as pil_logo:
                    pil_logo.convert("RGBA").save(logo_buf, format="PNG")
                logo_buf.seek(0)
                logo = ExcelImage(logo_buf)
                logo.width, logo.height = 42, 26
                ws.add_image(logo, "A1")
            except Exception as exc:
                print(f"Excel logo could not be added: {exc}")
        ws.append([])
        ws.append(sheet_headers)
        for cell in ws[3]:
            cell.fill = header_fill
            cell.font = Font(color="FFFFFFFF", bold=True, size=10)
            cell.alignment = Alignment(vertical="center", horizontal="center", wrap_text=True)
        ws.row_dimensions[3].height = 34
        ws.freeze_panes = "A4"
        for row in game_rows.get(game["id"], []):
            ws.append(ticket_export_values(row))
            excel_row = ws.max_row
            money_cols = {sheet_headers.index(name) + 1 for name in ("Total", "Paid", "Remaining")}
            text_cols = {sheet_headers.index(name) + 1 for name in ("ID", "Ticket", "Player / Contact") if name in sheet_headers}
            text_cols.add(6)  # Team / Team ID column (index 5 in header, col 6 in sheet)
            for col in range(1, len(sheet_headers) + 1):
                cell = ws.cell(excel_row, col)
                cell.alignment = Alignment(vertical="top", wrap_text=True)
                if col in text_cols:
                    cell.number_format = "@"
                elif col in money_cols:
                    cell.number_format = "@"
                if row["payment_status"] == "paid":
                    cell.fill = status_fills["Paid"]
                elif row["payment_status"] == "unpaid":
                    cell.fill = status_fills["Unpaid"]
                elif row["payment_status"] == "under_verification":
                    cell.fill = status_fills["Under Verification"]
            for col in (sheet_headers.index("Payment") + 1, sheet_headers.index("Check-In") + 1):
                value = ws.cell(excel_row, col).value
                if value in status_fills:
                    ws.cell(excel_row, col).fill = status_fills[value]
                    ws.cell(excel_row, col).font = Font(bold=True)
            member_text = str(ws.cell(excel_row, sheet_headers.index("Members") + 1).value or "")
            qualifier_text = str(ws.cell(excel_row, sheet_headers.index("Qualifiers") + 1).value or "")
            contact_text = str(ws.cell(excel_row, sheet_headers.index("Player / Contact") + 1).value or "")
            ws.row_dimensions[excel_row].height = max(36, min(180, 15 * (member_text.count("\n") + qualifier_text.count("\n") + contact_text.count("\n") + 3)))
        for index, width in enumerate(widths, start=1):
            ws.column_dimensions[get_column_letter(index)].width = width
        ws.auto_filter.ref = f"A3:{get_column_letter(len(sheet_headers))}{max(ws.max_row, 3)}"
        payment_column = sheet_headers.index("Payment") + 1
        checkin_column = sheet_headers.index("Check-In") + 1
        payment_letter = get_column_letter(payment_column)
        checkin_letter = get_column_letter(checkin_column)
        validation_end_row = max(ws.max_row, 1000)
        payment_range = f"{payment_letter}4:{payment_letter}{validation_end_row}"
        checkin_range = f"{checkin_letter}4:{checkin_letter}{validation_end_row}"
        validation = DataValidation(type="list", formula1='"Under Verification,Paid,Unpaid"', allow_blank=False)
        ws.add_data_validation(validation)
        validation.add(payment_range)
        checkin_validation = DataValidation(type="list", formula1='"Not Arrived,Checked In,Rejected"', allow_blank=False)
        ws.add_data_validation(checkin_validation)
        checkin_validation.add(checkin_range)
        if ws.max_row >= 4:
            full_range = f"A4:{get_column_letter(len(sheet_headers))}{ws.max_row}"
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Paid"'], fill=status_fills["Paid"]),
            )
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Unpaid"'], fill=status_fills["Unpaid"]),
            )
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Under Verification"'], fill=status_fills["Under Verification"]),
            )

    def add_simple_sheet(title, headers, data_rows, widths):
        ws = wb.create_sheet(title=title[:31])
        ws.append(headers)
        for cell in ws[1]:
            cell.fill = header_fill
            cell.font = Font(color="FFFFFFFF", bold=True, size=10)
            cell.alignment = Alignment(vertical="center", horizontal="center", wrap_text=True)
        ws.freeze_panes = "A2"
        for data_row in data_rows:
            ws.append(data_row)
            for cell in ws[ws.max_row]:
                cell.alignment = Alignment(vertical="top", wrap_text=True)
                cell.number_format = "@"
        for index, width in enumerate(widths, start=1):
            ws.column_dimensions[get_column_letter(index)].width = width
        ws.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{max(ws.max_row, 1)}"

    player_rows = []
    qualifier_rows = []
    for row in rows:
        details = registration_details(row["id"])
        if not details:
            continue
        for player in ticket_member_roster(details):
            player_rows.append([
                f"NX-{row['id']:04d}",
                row["game_title"],
                row["team_name"] or "Individual",
                registration_team_id(row) or "Individual",
                "Leader / Solo" if int(player["number"]) == 1 else f"Member {player['number']}",
                player.get("name") or "",
                player.get("email") or "",
                player.get("phone") or "",
                player.get("cnic") or "",
                player.get("ticket_id") or player.get("serial") or "",
                (row["payment_status"] or "").replace("_", " ").title(),
                details.get("total_payable", ""),
                details.get("amount_paid", ""),
                details.get("remaining_amount", ""),
            ])
        q_sections = qualifier_entry_list(details)
        selected_qid = request.args.get("qualifier_id", type=int)
        if selected_qid:
            q_sections = [sec for sec in q_sections if int(sec["qualifier"]["id"]) == selected_qid]
        for sec in q_sections:
            when = (sec.get("qualifier_date") or "TBD") + (f" {sec.get('qualifier_time')}" if sec.get("qualifier_time") else "")
            place = sec.get("venue") or "TBD"
            if sec.get("city"):
                place += f", {sec['city']}"
            for member in sec["members"]:
                qualifier_rows.append([
                    f"NX-{row['id']:04d}",
                    row["game_title"],
                    row["team_name"] or "Individual",
                    registration_team_id(row) or "Individual",
                    f"Q{sec['qualifier_number']}",
                    sec["qualifier_name"],
                    when,
                    place,
                    member.get("player_name") or "",
                    member.get("player_email") or "",
                    member.get("player_cnic") or "",
                    member.get("role") or "",
                    member.get("ticket_serial") or "",
                    (member.get("entry_status") or "pending").replace("_", " ").title(),
                    details.get("qualifier_fee") or "Rs. 0",
                ])

    add_simple_sheet(
        "Players Detail",
        ["ID", "Game", "Team", "Team ID", "Role", "Name", "Email", "Phone", "CNIC", "Ticket ID", "Payment", "Total", "Paid", "Remaining"],
        player_rows,
        [12, 22, 22, 18, 18, 24, 34, 18, 20, 20, 18, 16, 14, 16],
    )
    add_simple_sheet(
        "Qualifier Passes",
        ["ID", "Game", "Team", "Team ID", "Qualifier", "Name", "Date / Time", "Venue / Location", "Player", "Email", "CNIC", "Role", "Ticket ID", "Status", "Qualifier Fee"],
        qualifier_rows,
        [12, 22, 22, 18, 14, 28, 24, 34, 24, 34, 20, 14, 20, 16, 16],
    )

    stream = io.BytesIO()
    wb.save(stream)
    stream.seek(0)
    return Response(
        stream.getvalue(),
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={
            "Content-Disposition": f"attachment; filename=nexora_registrations_{stamp}.xlsx"
        },
    )


@app.route("/admin/registrations/export/pdf")
@admin_required
@permission_required("export_excel_reports")
def export_registrations_pdf():
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import landscape, A4
    from reportlab.lib.styles import getSampleStyleSheet
    from reportlab.lib.units import mm
    from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle
    from xml.sax.saxutils import escape

    rows, active_filters = _export_rows()
    stamp = datetime.now().strftime("%Y%m%d_%H%M")
    stream = io.BytesIO()
    doc = SimpleDocTemplate(
        stream,
        pagesize=landscape(A4),
        rightMargin=9 * mm,
        leftMargin=9 * mm,
        topMargin=10 * mm,
        bottomMargin=10 * mm,
    )
    styles = getSampleStyleSheet()

    body = styles["BodyText"].clone("export-cell")
    body.fontSize = 6.4
    body.leading = 7.6
    body.wordWrap = "CJK"

    small = body.clone("export-small")
    small.fontSize = 7.8
    small.leading = 10
    small.textColor = colors.HexColor("#475569")

    hs = styles["Heading2"].clone("export-header")
    hs.fontSize = 6.5
    hs.leading = 7.8
    hs.textColor = colors.white
    hs.fontName = "Helvetica-Bold"

    def cell(text, style=body):
        t = str(text or "").strip()
        return Paragraph(escape(t).replace("\n", "<br/>"), style)

    def status_text(row):
        return "Checked In" if row["entry_status"] == "approved" else ("Rejected" if row["entry_status"] == "rejected" else "Not Arrived")

    def compact_players(details):
        lines = []
        for player in ticket_member_roster(details):
            role = "Leader/Solo" if int(player["number"]) == 1 else f"M{player['number']}"
            lines.append(
                f"{role}: {player.get('name') or '-'} | {player.get('email') or '-'} | "
                f"{player.get('phone') or '-'} | CNIC: {player.get('cnic') or '-'} | {player.get('ticket_id') or player.get('serial') or '-'}"
            )
        return "\n".join(lines)

    def compact_qualifiers(details):
        q_sections = qualifier_entry_list(details)
        selected_qid = request.args.get("qualifier_id", type=int)
        if selected_qid:
            q_sections = [sec for sec in q_sections if int(sec["qualifier"]["id"]) == selected_qid]
        lines = []
        for sec in q_sections:
            when = (sec.get("qualifier_date") or "TBD") + (f" {sec.get('qualifier_time')}" if sec.get("qualifier_time") else "")
            place = sec.get("venue") or "TBD"
            if sec.get("city"):
                place += f", {sec['city']}"
            lines.append(f"Q{sec['qualifier_number']} {sec['qualifier_name']} | {when} | {place}")
            for member in sec["members"]:
                lines.append(
                    f"  {member.get('role') or '-'}: {member.get('player_name') or '-'} | "
                    f"{member.get('ticket_serial') or '-'} | {(member.get('entry_status') or 'pending').title()}"
                )
        return "\n".join(lines) if lines else "None"

    page_w = landscape(A4)[0] - 18 * mm
    story = [
        Paragraph("Nexora Registrations", styles["Title"]),
        Paragraph(_filter_summary_text(active_filters), small),
        Spacer(1, 4 * mm),
    ]

    if not rows:
        story.append(Paragraph("No registrations matched the selected filters.", body))
    else:
        headers = ["Reg / Game", "Player / Team", "Players & Ticket IDs", "Qualifier Passes", "Payment", "Status"]
        data = [[cell(h, hs) for h in headers]]
        for row in rows:
            details = registration_details(row["id"])
            if not details:
                continue
            team_label = details.get("registration_label") or details.get("team_label") or "Team"
            data.append([
                cell(f"{details['ref']}\n{row['game_title']}\n{row['ticket_serial'] or make_ticket_serial(row['id'])}"),
                cell(
                    f"{row['full_name']}\n{row['email']}\n{row['phone']}\nCNIC: {row['cnic'] or '-'}\n"
                    f"{team_label}: {row['team_name'] or 'Individual'}\n{registration_team_id(row) or 'Individual'}"
                ),
                cell(compact_players(details)),
                cell(compact_qualifiers(details)),
                cell(
                    f"Actual: {details.get('base_original_price', '')}\n"
                    f"Discounted: {details.get('base_price', '')}\n"
                    f"Qualifier: {details.get('qualifier_fee') or 'Rs. 0'}\n"
                    + (
                        "\n".join([f" - {item['name']}: {item['original_fee']}" + (f" - {item['discount_fee']} ({details.get('qualifier_pricing', {}).get('discount_pct', '0')}% off)" if details.get('qualifier_pricing', {}).get('discount_active') else "") + f" = {item['final_fee']}" for item in details.get('qualifier_pricing', {}).get('items', [])]) + "\n"
                        if details.get('qualifier_pricing', {}).get('count') and not details.get('qualifier_pricing', {}).get('is_historical') else ""
                    ) +
                    f"Total: {details.get('total_payable', '')}\n"
                    f"Paid: {details.get('amount_paid', '')}\n"
                    f"Remaining: {details.get('remaining_amount', '')}"
                ),
                cell(
                    f"Payment: {(row['payment_status'] or '').replace('_', ' ').title()}\n"
                    f"Entry: {status_text(row)}\n"
                    f"Registered: {pretty_dt(row['created_at'])}"
                ),
            ])

        table = Table(
            data,
            repeatRows=1,
            colWidths=[24 * mm, 43 * mm, 66 * mm, 63 * mm, 38 * mm, page_w - 234 * mm],
        )
        table.setStyle(TableStyle([
            ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#111827")),
            ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
            ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#d8e0ec")),
            ("VALIGN", (0, 0), (-1, -1), "TOP"),
            ("TOPPADDING", (0, 0), (-1, -1), 3),
            ("BOTTOMPADDING", (0, 0), (-1, -1), 3),
            ("LEFTPADDING", (0, 0), (-1, -1), 3),
            ("RIGHTPADDING", (0, 0), (-1, -1), 3),
            ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.HexColor("#ffffff"), colors.HexColor("#f8fafc")]),
        ]))
        for idx, row in enumerate(rows, start=1):
            fill = {
                "paid": colors.HexColor("#e5f6ef"),
                "unpaid": colors.HexColor("#fde2e2"),
                "under_verification": colors.HexColor("#fff3bf"),
            }.get((row["payment_status"] or "").lower())
            if fill:
                table.setStyle(TableStyle([("BACKGROUND", (0, idx), (-1, idx), fill)]))
        story.append(table)

    doc.build(story)
    stream.seek(0)
    return Response(
        stream.getvalue(),
        mimetype="application/pdf",
        headers={"Content-Disposition": f"attachment; filename=nexora_registrations_{stamp}.pdf"},
    )


@app.route("/admin/registrations/import", methods=["POST"])
@admin_required
@permission_required("export_excel_reports")
def import_registrations():
    """Import registrations from an Excel file exported by this system."""
    file = request.files.get("import_file")
    if not file or not file.filename:
        flash("Please select an Excel (.xlsx) file to import.", "danger")
        return redirect(url_for("admin_registrations"))

    if not file.filename.lower().endswith(".xlsx"):
        flash("Only .xlsx files are supported. Please export from Excel first.", "danger")
        return redirect(url_for("admin_registrations"))

    try:
        from openpyxl import load_workbook
    except ImportError:
        flash("Excel support is not available on this server.", "danger")
        return redirect(url_for("admin_registrations"))

    try:
        wb = load_workbook(file, read_only=True, data_only=True)
    except Exception:
        flash("Could not read the Excel file. Please ensure it is a valid .xlsx file.", "danger")
        return redirect(url_for("admin_registrations"))

    games_list = query("SELECT id, title, min_members, max_members, is_free, qualifiers_enabled FROM games WHERE active = 1 AND deleted_at = ''")
    games = {}
    for g in games_list:
        key = g["title"].strip().lower()
        games[key] = dict(g)
        # Also index by title with slashes/dots stripped (Excel removes special chars from sheet names)
        clean = re.sub(r"[/\\*:?\[\]]", "", g["title"]).strip().lower()
        if clean != key:
            games[clean] = dict(g)

    imported = 0
    skipped = 0
    errors_list = []

    for ws in wb.worksheets:
        if ws.title in ['Players Detail', 'Qualifier Passes']:
            continue
        if ws.max_row is None or ws.max_row < 4:
            continue
        for row_num in range(4, ws.max_row + 1):
            cells = [str(ws.cell(row_num, col).value or "").strip() for col in range(1, 16)]
            nx_id = cells[0]
            old_ticket_serial = cells[1]
            player_contact = cells[2]
            game_title = cells[3]
            entry_type_raw = cells[4]
            team_info = cells[5]
            members_text = cells[6]
            qualifier_text = cells[7]
            total_payable = cells[8]
            amount_paid = cells[9]
            remaining = cells[10]
            payment_status_raw = cells[12]
            checkin_raw = cells[13]
            registered_raw = cells[14]

            if not player_contact or not game_title:
                skipped += 1
                continue

            game_lower = game_title.strip().lower()
            game = games.get(game_lower)
            if not game:
                errors_list.append(f"Row {row_num} ({ws.title}): Game '{game_title}' not found or inactive.")
                skipped += 1
                continue

            pc_lines = [l.strip() for l in player_contact.split("\n") if l.strip()]
            full_name = pc_lines[0] if pc_lines else ""
            email = pc_lines[1] if len(pc_lines) > 1 else ""
            phone = pc_lines[2] if len(pc_lines) > 2 else ""
            cnic = ""
            for pl in pc_lines:
                if pl.lower().startswith("cnic:"):
                    cnic = pl.split(":", 1)[1].strip()
                    break

            if not full_name or not email:
                errors_list.append(f"Row {row_num} ({ws.title}): Missing name or email.")
                skipped += 1
                continue

            team_name = ""
            if team_info:
                team_lines = [l.strip() for l in team_info.split("\n") if l.strip()]
                team_name = team_lines[0] if team_lines[0] != "Individual" else ""

            entry_type = "group" if (entry_type_raw.lower() == "group" or (team_name and members_text and "members" in members_text.lower())) else "individual"

            norm_members = ""
            if members_text and entry_type == "group":
                norm_members = members_text
                if "\n" in norm_members:
                    parts = norm_members.split("\n", 1)
                    norm_members = parts[1] if len(parts) > 1 else ""
                norm_members = norm_members.strip()

            selected_q_ids = []
            if qualifier_text and qualifier_text.lower() != "none":
                import re as _re
                q_matches = _re.findall(r"Q(\d+)\s", qualifier_text)
                if q_matches and game.get("qualifiers_enabled") == "1":
                    q_nums = [int(q) for q in q_matches]
                    q_rows = query(
                        "SELECT id, qualifier_number FROM qualifiers WHERE game_id = ? AND active = 1",
                        (game["id"],),
                    )
                    num_to_id = {q["qualifier_number"]: q["id"] for q in q_rows}
                    selected_q_ids = [num_to_id[n] for n in q_nums if n in num_to_id]

            payment_status = "under_verification"
            if payment_status_raw.lower() == "paid":
                payment_status = "paid"
            elif payment_status_raw.lower() == "unpaid":
                payment_status = "unpaid"
            elif payment_status_raw.lower() == "under verification":
                payment_status = "under_verification"

            entry_status = ""
            if checkin_raw.lower() == "checked in":
                entry_status = "approved"
            elif checkin_raw.lower() == "rejected":
                entry_status = "rejected"

            proof_waived = str(game.get("is_free", "")) == "1"
            is_full_discount = bool(discount_active(game) and discount_pct_value(game) >= 100)
            if is_full_discount:
                proof_waived = True

            try:
                with db() as conn:
                    existing = conn.execute(
                        "SELECT id FROM registrations WHERE email = ? AND game_id = ?",
                        (email.strip(), game["id"]),
                    ).fetchone()
                    if existing:
                        skipped += 1
                        continue

                    ticket_token = make_ticket_token()
                    now_ts = datetime.now().isoformat(timespec="seconds")
                    leader_name = full_name
                    team_size_val = 1
                    if entry_type == "group" and norm_members:
                        parsed_members = parse_team_members(norm_members)
                        team_size_val = 1 + len([m for m in parsed_members if any(m.values())])

                    cur = conn.execute(
                        """INSERT INTO registrations
                        (full_name, email, phone, cnic, institution_type, university, roll_no, entry_type,
                         game_id, team_name, leader_name, members, payment_method, payment_status,
                         proof_image, ticket_token, ticket_serial, created_at, amount_paid,
                         selected_qualifiers, qualifier_count, qualifier_fee)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                        (
                            full_name, email, phone, cnic,
                            "Open", "", "",
                            entry_type, game["id"],
                            team_name, leader_name, norm_members,
                            "admin_import", payment_status,
                            "", ticket_token, "",
                            registered_raw or now_ts,
                            amount_paid if amount_paid else ("Rs. 0" if proof_waived else ""),
                            ",".join(str(q) for q in selected_q_ids) if selected_q_ids else "",
                            str(len(selected_q_ids)),
                            "",
                        ),
                    )
                    reg_id = cur.lastrowid

                    new_serial = make_ticket_serial(reg_id, game["title"])
                    conn.execute(
                        "UPDATE registrations SET ticket_serial = ? WHERE id = ?",
                        (new_serial, reg_id),
                    )

                    if entry_status:
                        conn.execute(
                            "UPDATE registrations SET entry_status = ? WHERE id = ?",
                            (entry_status, reg_id),
                        )

                    ensure_member_tickets_for_registration(conn, reg_id)
                    ensure_qualifier_entry_tickets_for_registration(conn, reg_id)

                    conn.commit()
                    imported += 1

            except Exception as exc:
                old_ref = f" Old ticket ignored: {old_ticket_serial}." if old_ticket_serial else ""
                errors_list.append(f"Row {row_num} ({ws.title}): {str(exc)[:120]}{old_ref}")
                skipped += 1

    wb.close()

    if imported:
        flash(f"Successfully imported {imported} registration(s).", "success")
    if skipped:
        flash(f"Skipped {skipped} row(s).", "warning")
    if errors_list:
        for err in errors_list[:10]:
            flash(err, "danger")
        if len(errors_list) > 10:
            flash(f"... and {len(errors_list) - 10} more errors.", "danger")

    return redirect(url_for("admin_registrations"))