"""Qualifier configuration, fees and per-registration qualifier details."""
from datetime import datetime
from flask import request

from nexora.database.connection import db, execute, query
from nexora.utils.helpers import amount_value
from nexora.utils.formatting import format_qualifier_parts, split_venue_location
from nexora.utils.team import parse_team_members
from nexora.services.pricing import discount_active, discount_pct_value, get_discount_type


def qualifier_members_for_registration(reg_row):
    """Return a list of dicts: one per playable seat (leader + group members).

    member_number 1 is always the leader / solo player; group members follow.
    Solo registrations yield a single seat (member_number 1).
    """
    keys = reg_row.keys()
    leader_cnic = reg_row["cnic"] or "" if "cnic" in keys else ""
    leader_name = (reg_row["leader_name"] or "") if "leader_name" in keys else ""
    if not leader_name:
        leader_name = (reg_row["name"] or "") if "name" in keys else ""
    if not leader_name:
        leader_name = (reg_row["full_name"] or "") if "full_name" in keys else ""
    email_val = (reg_row["email"] or "") if "email" in keys else ""
    phone_val = (reg_row["phone"] or "") if "phone" in keys else ""
    seats = [{"member_number": 1, "name": leader_name,
              "email": email_val, "phone": phone_val, "cnic": leader_cnic, "role": "Leader"}]
    entry_type = ((reg_row["entry_type"] or "").lower()) if "entry_type" in keys else ""
    if entry_type == "group":
        members_text = (reg_row["members"] or "") if "members" in keys else ""
        for member_number, member in enumerate(parse_team_members(members_text), start=2):
            if not any(member.values()):
                continue
            seats.append({"member_number": member_number, "name": member["name"],
                          "email": member["email"], "phone": member["phone"], "cnic": member.get("cnic", ""), "role": "Member"})
    return seats


def qualifier_entry_list(details):
    """Return per-qualifier sections for ticket rendering.

    [
      {
        "qualifier": {…}, "qualifier_date", "qualifier_time", "venue", "city",
        "members": [ { "entry": {…}, "member_number", "player_name", "role", "ticket_token", "ticket_serial", "entry_status" }, … ]
      }, …
    ]

    Only returns entries when qualifiers are selected and exist in DB.
    Works for both pre-configured qualifiers and on-the-fly created ones.
    """
    game_id = details.get("game_id")
    q_ids = [x.strip() for x in (details.get("selected_qualifiers") or "").split(",") if x.strip().isdigit()]
    if not q_ids:
        return []
    q_rows = query(
        "SELECT * FROM qualifiers WHERE id IN ({}) ORDER BY qualifier_number".format(",".join("?" for _ in q_ids)),
        [int(x) for x in q_ids],
    )
    quals = [dict(q) for q in q_rows]
    entries = {
        (e["qualifier_id"], e["member_number"]): dict(e)
        for e in query(
            "SELECT * FROM qualifier_entry_tickets WHERE registration_id = ?",
            (details["id"],),
        )
    }
    result = []
    for qual in quals:
        seats = qualifier_members_for_registration(details)
        members = []
        for seat in seats:
            entry = entries.get((qual["id"], seat["member_number"]))
            if not entry:
                continue
            members.append({
                "entry": entry,
                "member_number": seat["member_number"],
                "player_name": seat["name"],
                "player_email": seat.get("email") or entry.get("player_email") or "",
                "player_phone": seat.get("phone") or entry.get("player_phone") or "",
                "player_cnic": seat.get("cnic") or "",
                "role": seat["role"],
                "ticket_token": entry["ticket_token"],
                "ticket_serial": entry["ticket_serial"],
                "entry_status": (entry["entry_status"] or "pending").lower(),
            })
        result.append({
            "qualifier": qual,
            "qualifier_name": qual["qualifier_name"],
            "qualifier_number": qual["qualifier_number"],
            "qualifier_date": qual.get("qualifier_date", ""),
            "qualifier_time": qual.get("qualifier_time", ""),
            "qualifier_end_time": qual.get("qualifier_end_time", ""),
            "venue_name": qual.get("location", ""),
            "venue_address": qual.get("venue_address", ""),
            "members": members,
        })
    return result


def registration_qualifier_details(row):
    """Return formatted qualifier info for a registration row (dict or Row).

    Each entry: {id, qualifier_number, qualifier_name, fee, qualifier_date,
    location, date_display, time_display, venue, city}.
    """
    selected = (row.get("selected_qualifiers") if hasattr(row, "get") else row["selected_qualifiers"]) or ""
    q_ids = [x.strip() for x in selected.split(",") if x.strip().isdigit()]
    if not q_ids:
        return []
    q_rows = query(
        "SELECT * FROM qualifiers WHERE id IN ({}) ORDER BY qualifier_number".format(",".join("?" for _ in q_ids)),
        [int(x) for x in q_ids],
    )
    out = []
    for q in q_rows:
        d = dict(q)
        d["date_display"] = d.get("qualifier_date", "")
        end_str = f" - {d['qualifier_end_time']}" if d.get("qualifier_end_time") else ""
        d["time_display"] = f"{d.get('qualifier_time', '')}{end_str}" if d.get("qualifier_time") else ""
        d["venue_name"] = d.get("location", "")
        d["venue_address"] = d.get("venue_address", "")
        out.append(d)
    return out


# ── Qualifier helpers ────────────────────────────────────────────────

def get_game_qualifiers(game_id):
    """Return active qualifiers for a game ordered by qualifier_number."""
    return query(
        "SELECT * FROM qualifiers WHERE game_id = ? AND active = 1 ORDER BY qualifier_number, sort_order, id",
        (game_id,),
    )


def qualifiers_enabled(game):
    """Check if qualifiers are enabled for a game."""
    if not game or "qualifiers_enabled" not in game.keys():
        return False
    return str(game["qualifiers_enabled"]) == "1"


def qualifier_fee_type(game):
    """Return 'separate' or 'package'."""
    if not game or "qualifier_fee_type" not in game.keys():
        return "separate"
    return game["qualifier_fee_type"] or "separate"


def calc_qualifier_fee(game, selected_ids):
    """Calculate qualifier pricing.

    The qualifier fee is always the price for one qualifier entry. Team size,
    member tickets and QR-code count never multiply this fee.
    """
    if not game or not selected_ids:
        return {
            "total_fee": "Rs. 0", "total_value": 0.0, "count": 0, "breakdown": "",
            "total_original": 0.0, "total_discount": 0.0, "discount_pct": 0,
            "discount_active": False, "items": [],
        }

    # Normalize to a mapping so .get() works for both dicts and sqlite3.Row.
    if hasattr(game, "keys"):
        game = {k: game[k] for k in game.keys()}

    game_id = game.get("game_id") or game.get("id")
    qualifiers = query(
        "SELECT * FROM qualifiers WHERE id IN ({}) AND game_id = ?".format(
            ",".join("?" for _ in selected_ids)
        ),
        (*selected_ids, game_id),
    )
    count = len(qualifiers)
    # Warn if some selected qualifier IDs were not found (deleted or wrong game).
    found_ids = {q["id"] for q in qualifiers}
    missing = [sid for sid in selected_ids if sid not in found_ids]
    if missing:
        import logging as _log
        _log.getLogger("nexora").warning(
            "calc_qualifier_fee: qualifier IDs %s not found for game %s — skipped",
            missing, game_id,
        )
    if count == 0:
        return {
            "total_fee": "Rs. 0", "total_value": 0.0, "count": 0, "breakdown": "",
            "total_original": 0.0, "total_discount": 0.0, "discount_pct": 0,
            "discount_active": False, "items": [],
        }

    pct = discount_pct_value(game) if discount_active(game) else 0
    dtype = get_discount_type(game)
    fee_type = str(game.get("qualifier_fee_type") or "separate").lower()
    if fee_type == "package":
        original = max(0, amount_value(game.get("qualifier_package_fee") or "0"))
        try:
            package_size = max(1, int(game.get("qualifier_package_size") or 1))
        except (TypeError, ValueError):
            package_size = 1
        extra_count = max(0, count - package_size)
        additional_fee = max(0, amount_value(game.get("qualifier_additional_fee") or "0"))
        extra_original = additional_fee if extra_count > 0 else 0
        original_total = original + extra_original
        if dtype == "fixed":
            discount_amount = min(original_total, pct) if pct > 0 else 0.0
        else:
            discount_amount = original_total * pct / 100 if pct > 0 else 0.0
        final = max(0, original_total - discount_amount)
        item = {
            "id": 0,
            "qualifier_number": "",
            "name": f"Package ({min(count, package_size)} included"
                    + (f" + {extra_count} more" if extra_count else "")
                    + ")",
            "original_value": original_total,
            "discount_value": discount_amount,
            "final_value": final,
            "original_fee": f"Rs. {original_total:,.0f}",
            "discount_fee": f"Rs. {discount_amount:,.0f}",
            "final_fee": f"Rs. {final:,.0f}",
        }
        return {
            "total_fee": f"Rs. {final:,.0f}",
            "total_value": float(final),
            "count": count,
            "breakdown": f"Package includes {package_size} qualifier{'s' if package_size != 1 else ''}"
                         + (f"; additional Rs. {additional_fee:,.0f} for {extra_count} more" if extra_count else ""),
            "discount_applied": pct > 0,
            "discount_active": pct > 0,
            "discount_pct": pct,
            "total_original": original_total,
            "total_original_fee": f"Rs. {original_total:,.0f}",
            "total_discount": discount_amount,
            "total_discount_fee": f"Rs. {discount_amount:,.0f}",
            "items": [item] if original or final else [],
            "fee_type": "package",
            "package_size": package_size,
            "extra_count": extra_count,
            "additional_fee": f"Rs. {additional_fee:,.0f}",
        }

    items = []
    total_original = 0.0
    total_discount = 0.0
    total = 0.0
    for q in qualifiers:
        original = max(0, amount_value(q["fee"]))
        if dtype == "fixed":
            # For multiple separate qualifiers, distributing flat discount is tricky.
            # Usually flat discount applies to the total cart, not per item.
            # Wait, if they select 3 qualifiers and discount is Flat Rs. 100, do they get 100 off EACH or off TOTAL?
            # Game fee discount is flat off the bundle. Let's do it per qualifier since it's iterating.
            # No, if it's "separate", flat means per-qualifier flat.
            discount_amount = min(original, pct) if pct > 0 else 0.0
        else:
            discount_amount = original * pct / 100 if pct > 0 else 0.0
        final = max(0, original - discount_amount)
        total_original += original
        total_discount += discount_amount
        total += final
        items.append({
            "id": q["id"],
            "qualifier_number": q["qualifier_number"],
            "name": q["qualifier_name"],
            "original_value": original,
            "discount_value": discount_amount,
            "final_value": final,
            "original_fee": f"Rs. {original:,.0f}",
            "discount_fee": f"Rs. {discount_amount:,.0f}",
            "final_fee": f"Rs. {final:,.0f}",
        })

    breakdown = f"{count} qualifier(s) x Rs. {items[0]['final_value']:,.0f} per qualifier" if items else ""

    return {
        "total_fee": f"Rs. {total:,.0f}",
        "total_value": float(total),
        "count": count,
        "breakdown": breakdown,
        "discount_applied": pct > 0,
        "discount_active": pct > 0,
        "discount_pct": pct,
        "total_original": total_original,
        "total_original_fee": f"Rs. {total_original:,.0f}",
        "total_discount": total_discount,
        "total_discount_fee": f"Rs. {total_discount:,.0f}",
        "items": items,
        "fee_type": "separate",
    }


def save_qualifier_rows(game_id):
    """Save qualifier rows from the admin form for a given game.

    Expects form fields:
      - qualifier_id[]   - existing qualifier id (blank for new rows)
      - qualifier_name[]
      - qualifier_fee[]
      - qualifier_location[]
      - qualifier_date[]
    Existing qualifiers are updated in place so their IDs stay stable,
    which keeps selected_qualifiers references on registrations valid.
    Qualifiers the admin removed from the form are deleted.
    """
    ids = [x.strip() for x in request.form.getlist("qualifier_id[]")]
    names = request.form.getlist("qualifier_name[]")
    fees = request.form.getlist("qualifier_fee[]")
    locations = request.form.getlist("qualifier_location[]")
    venue_addresses = request.form.getlist("qualifier_venue_address[]")
    dates = request.form.getlist("qualifier_date[]")
    times = request.form.getlist("qualifier_time[]")
    end_times = request.form.getlist("qualifier_end_time[]")
    now_ts = datetime.now().isoformat(timespec="seconds")
    submitted_ids = []
    new_ids = []
    saved_count = 0
    # Capture existing qualifier IDs before any changes, so we can clean up
    # qualifiers the admin removed from the form.
    prior_db_ids = set(
        r["id"]
        for r in query("SELECT id FROM qualifiers WHERE game_id = ?", (game_id,))
    )
    with db() as conn:
        for i, name in enumerate(names):
            name = (name or "").strip()
            if not name:
                continue
            fee = fees[i].strip() if i < len(fees) else ""
            location = locations[i].strip() if i < len(locations) else "TBD"
            venue_addr = venue_addresses[i].strip() if i < len(venue_addresses) else ""
            date_val = dates[i].strip() if i < len(dates) else ""
            time_val = times[i].strip() if i < len(times) else ""
            end_time_val = end_times[i].strip() if i < len(end_times) else ""

            qid = ids[i].strip() if i < len(ids) else ""
            if qid.isdigit():
                # Update existing qualifier, preserving its ID.
                qid = int(qid)
                submitted_ids.append(qid)
                conn.execute(
                    "UPDATE qualifiers SET qualifier_name = ?, fee = ?, qualifier_date = ?, qualifier_time = ?, qualifier_end_time = ?, location = ?, venue_address = ?, active = 1 WHERE id = ? AND game_id = ?",
                    (name, fee, date_val, time_val, end_time_val, location or "TBD", venue_addr, qid, game_id),
                )
            else:
                existing = conn.execute(
                    "SELECT COALESCE(MAX(qualifier_number), 0) FROM qualifiers WHERE game_id = ?",
                    (game_id,),
                ).fetchone()[0]
                cur = conn.execute(
                    "INSERT INTO qualifiers (game_id, qualifier_name, qualifier_number, fee, qualifier_date, qualifier_time, qualifier_end_time, sort_order, active, created_at, location, venue_address) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?)",
                    (game_id, name, int(existing or 0) + 1, fee, date_val, time_val, end_time_val, int(existing or 0) + 1, now_ts, location or "TBD", venue_addr),
                )
                new_ids.append(cur.lastrowid)
            saved_count += 1
        conn.commit()
    # Delete qualifiers that existed before but were removed from the form.
    # Keep = updated IDs (submitted_ids) + newly inserted IDs (new_ids).
    keep = set(submitted_ids) | set(new_ids)
    to_delete = prior_db_ids - keep
    if to_delete:
        placeholders = ",".join("?" for _ in to_delete)
        execute(
            f"DELETE FROM qualifiers WHERE game_id = ? AND id IN ({placeholders})",
            (game_id, *to_delete),
        )
    return saved_count