"""Exact administrative CSV selection, inside the caller's locked transaction.

CSV metadata never establishes ownership or replaces board identity. Every supplied
identity is a constraint; a failed strong identifier never falls back to an IP.
"""

from uuid import UUID

from fastapi import HTTPException

from app.discovery.repository import address, mac


def resolve_inventory_row(cur, row, row_number, selected_zone=None):
    def fail(message, status=409):
        raise HTTPException(status_code=status, detail=f"CSV row {row_number}: {message}")

    def field(names, normalize=lambda v: v):
        values = []
        for name in names:
            value = row.get(name)
            if value is not None and str(value).strip():
                try:
                    values.append(normalize(str(value).strip()))
                except (ValueError, HTTPException):
                    fail(f"invalid {names[0]}", 422)
        if len(set(values)) > 1:
            fail(f"conflicting {names[0]} columns")
        return values[0] if values else None

    def uuid(value):
        return str(UUID(value))

    mid = field(["Miner ID", "Miner UUID", "miner_id", "id"], uuid)
    zone = field(["Agent Zone ID", "Zone ID", "agent_zone_id", "zone_id"], uuid)
    site = field(["Site ID", "site_id"], uuid)
    ip = field(["IP", "IP Address", "ip", "ip_address"], address)
    current_mac = field(["MAC Addr", "MAC", "Mac", "mac", "mac_address"], mac)
    serial = field(["Serial", "Serial Number", "SN", "Miner SN", "miner_sn", "serial_number"])
    if selected_zone:
        try:
            selected_zone = uuid(selected_zone)
        except ValueError:
            fail("invalid selected Agent Zone ID", 422)
        if zone and zone != selected_zone:
            fail("row Zone disagrees with selected Zone")
        zone = selected_zone
    if zone:
        cur.execute("SELECT site_id::text FROM agent_zones WHERE id=%s", (zone,))
        zone_row = cur.fetchone()
        if not zone_row:
            fail("selected Zone does not exist", 422)
        if site and site != zone_row["site_id"]:
            fail("Site and Zone disagree")
    if not (mid or current_mac or serial or (ip and zone)):
        fail("provide Miner ID, current MAC, serial, or IP plus Agent Zone ID", 422)

    selectors = []
    if mid:
        selectors.append(("id=%s::uuid", mid))
    if current_mac:
        selectors.append(("regexp_replace(lower(current_mac), '[^0-9a-f]', '', 'g')=%s", current_mac.replace(":", "").lower()))
    if serial:
        selectors.append(("miner_sn=%s", serial))
    # IP is only a namespace-scoped selector when no stronger identity is supplied.
    if not selectors:
        selectors.append(("current_ip=%s::inet AND agent_zone_id=%s::uuid", (ip, zone)))

    candidates = {}
    for predicate, value in selectors:
        params = value if isinstance(value, tuple) else (value,)
        cur.execute(
            "SELECT id::text, site_id::text, agent_zone_id::text, "
            "host(current_ip) AS current_ip, current_mac, miner_sn, model, miner_type, firmware_version "
            "FROM miners WHERE " + predicate + " ORDER BY id LIMIT 2 FOR UPDATE",
            params,
        )
        matches = cur.fetchall()
        if len(matches) > 1:
            fail("ambiguous inventory identity; supply exact Miner ID and consistent identifiers")
        candidates.update({m["id"]: m for m in matches})
    if not candidates:
        return None
    if len(candidates) != 1:
        fail("identifiers select different inventory records")
    miner = next(iter(candidates.values()))
    if (
        (mid and mid != miner["id"])
        or (zone and zone != miner["agent_zone_id"])
        or (site and site != miner["site_id"])
        or (ip and ip != miner["current_ip"])
        or (serial and serial != miner["miner_sn"])
        or (current_mac and current_mac != mac(miner["current_mac"]))
    ):
        fail("identifiers disagree with current inventory; refresh selection before importing")
    return miner