"""Client management — a logical view spanning three tables.

A "client" is identified by its MAC address, which is used verbatim as the RADIUS
``username``. One client touches three tables:

    radcheck      username = MAC, Cleartext-Password := MAC   (auth)
    radusergroup  username = MAC, groupname = <group>         (VLAN/group membership)
    customers     username = MAC, mac_address = MAC, status   (billing/metadata)

This router hides that fan-out behind mac_address + group + status.
"""
from fastapi import APIRouter, Depends
from pydantic import ValidationError
from sqlalchemy import and_, delete, func, or_, select, update
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from ..database import get_db
from ..errors import APIError
from ..models import Customer, RadadminClient, RadCheck, RadGroupReply, RadUserGroup
from ..pagination import Page, PageParams
from ..schemas import (
    ClientCreate,
    ClientEdit,
    ClientImportError,
    ClientImportRequest,
    ClientImportResult,
    ClientOut,
)

router = APIRouter(prefix="/client", tags=["client"])


def _group_exists(db: Session, group: str) -> bool:
    stmt = select(RadGroupReply.id).where(RadGroupReply.groupname == group).limit(1)
    return db.execute(stmt).first() is not None


def _client_exists(db: Session, mac: str) -> bool:
    stmt = select(Customer.id).where(Customer.username == mac).limit(1)
    return db.execute(stmt).first() is not None


def _stage_client(db: Session, cli: ClientCreate) -> None:
    """Add a client's four rows to the session — radcheck + radusergroup (RADIUS),
    customers (status), and radadmin_clients (name/phone/alias metadata).

    Does not commit — the caller controls the transaction boundary.
    """
    mac = cli.mac_address
    status = "paid"
    db.add(RadCheck(username=mac, attribute="Cleartext-Password", op=":=", value=mac))
    db.add(RadUserGroup(username=mac, groupname=cli.group, priority=1))
    db.add(Customer(username=mac, mac_address=mac, status=status))
    # radadmin_clients mirrors the client's group/status too, so the row is a full
    # record that survives (frozen) after the RADIUS/billing rows are hard-deleted.
    db.add(RadadminClient(
        mac_address=mac, name=cli.name, phone=cli.phone, alias=cli.alias,
        groupname=cli.group, status=status,
    ))


@router.get("/", response_model=Page[ClientOut])
def list_clients(
    page: PageParams = Depends(),
    search: str | None = None,
    status: str | None = None,
    db: Session = Depends(get_db),
):
    """List clients — MAC, group and status, joined from customers + radusergroup.

    Optional filters:
      - ``search``  case-insensitive substring match across MAC, name, phone, alias.
      - ``status``  exact match on billing status (new/paid/unpaid).
    """
    base = (
        select(
            Customer.mac_address, RadUserGroup.groupname, Customer.status,
            RadadminClient.name, RadadminClient.phone, RadadminClient.alias,
        )
        .outerjoin(RadUserGroup, RadUserGroup.username == Customer.username)
        .outerjoin(
            RadadminClient,
            and_(RadadminClient.mac_address == Customer.username, RadadminClient.deleted_at.is_(None)),
        )
    )
    # Count over the same joins so filtered totals drive pagination correctly.
    count_stmt = (
        select(func.count())
        .select_from(Customer)
        .outerjoin(
            RadadminClient,
            and_(RadadminClient.mac_address == Customer.username, RadadminClient.deleted_at.is_(None)),
        )
    )

    if search:
        term = f"%{search.strip()}%"
        cond = or_(
            Customer.mac_address.ilike(term),
            RadadminClient.name.ilike(term),
            RadadminClient.phone.ilike(term),
            RadadminClient.alias.ilike(term),
        )
        base = base.where(cond)
        count_stmt = count_stmt.where(cond)
    if status:
        base = base.where(Customer.status == status)
        count_stmt = count_stmt.where(Customer.status == status)

    total = db.execute(count_stmt).scalar_one()
    rows = db.execute(base.order_by(Customer.id.desc()).limit(page.limit).offset(page.offset)).all()
    items = [
        ClientOut(mac_address=mac, group=gn, status=st, name=nm, phone=ph, alias=al)
        for mac, gn, st, nm, ph, al in rows
    ]
    return Page(total=int(total), limit=page.limit, offset=page.offset, items=items)


@router.get("/{mac_address}", response_model=ClientOut)
def get_client(mac_address: str, db: Session = Depends(get_db)):
    mac = mac_address.strip().upper().replace(":", "-")
    stmt = (
        select(
            Customer.mac_address, RadUserGroup.groupname, Customer.status,
            RadadminClient.name, RadadminClient.phone, RadadminClient.alias,
        )
        .outerjoin(RadUserGroup, RadUserGroup.username == Customer.username)
        .outerjoin(
            RadadminClient,
            and_(RadadminClient.mac_address == Customer.username, RadadminClient.deleted_at.is_(None)),
        )
        .where(Customer.username == mac)
    )
    row = db.execute(stmt).first()
    if row is None:
        raise APIError(status_code=404, detail=f"Client '{mac}' not found")
    return ClientOut(
        mac_address=row[0], group=row[1], status=row[2],
        name=row[3], phone=row[4], alias=row[5],
    )


@router.post("/add", response_model=ClientOut, status_code=201)
def add_client(payload: ClientCreate, db: Session = Depends(get_db)):
    """Register a client: create its radcheck, radusergroup and customer rows."""
    mac = payload.mac_address

    if not _group_exists(db, payload.group):
        raise APIError(status_code=400, detail=f"Group '{payload.group}' not found — create it first")
    if _client_exists(db, mac):
        raise APIError(status_code=409, detail=f"Client '{mac}' already exists")

    _stage_client(db, payload)
    db.commit()
    return ClientOut(
        mac_address=mac, group=payload.group, status="paid",
        name=payload.name, phone=payload.phone, alias=payload.alias,
    )


def _format_validation_error(exc: ValidationError) -> str:
    """Turn a pydantic ValidationError into a short, human message."""
    parts = []
    for err in exc.errors():
        loc = ".".join(str(p) for p in err["loc"])
        parts.append(f"{loc}: {err['msg']}" if loc else err["msg"])
    return "; ".join(parts)


@router.post("/import", response_model=ClientImportResult)
def import_clients(payload: ClientImportRequest, db: Session = Depends(get_db)):
    """Bulk-import clients from parsed CSV rows.

    Every row is validated with the same rules as ``/client/add`` (MAC/phone
    normalization, required fields, group-exists, duplicate MAC — both against the
    DB and within the file). Errors are collected per row rather than failing the
    batch. With ``dry_run`` nothing is written, so the UI can preview and confirm;
    otherwise the valid rows are inserted best-effort (each in its own commit).
    """
    errors: list[ClientImportError] = []
    valid: list[tuple[int, ClientCreate]] = []
    seen_macs: set[str] = set()

    for idx, raw in enumerate(payload.devices, start=1):
        try:
            cli = ClientCreate(**raw.model_dump())
        except ValidationError as exc:
            errors.append(ClientImportError(row=idx, mac=raw.mac_address, detail=_format_validation_error(exc)))
            continue

        mac = cli.mac_address
        if mac in seen_macs:
            errors.append(ClientImportError(row=idx, mac=mac, detail="Duplicate MAC within file"))
            continue
        if not _group_exists(db, cli.group):
            errors.append(ClientImportError(row=idx, mac=mac, detail=f"Group '{cli.group}' not found"))
            continue
        if _client_exists(db, mac):
            errors.append(ClientImportError(row=idx, mac=mac, detail=f"Client '{mac}' already exists"))
            continue

        seen_macs.add(mac)
        valid.append((idx, cli))

    created = 0
    if not payload.dry_run:
        for idx, cli in valid:
            _stage_client(db, cli)
            try:
                db.commit()
                created += 1
            except IntegrityError:
                db.rollback()
                errors.append(ClientImportError(row=idx, mac=cli.mac_address, detail="Insert failed (integrity error)"))

    return ClientImportResult(
        total=len(payload.devices),
        valid=len(valid),
        created=created,
        dry_run=payload.dry_run,
        errors=sorted(errors, key=lambda e: e.row),
    )


@router.post("/edit", response_model=ClientOut)
def edit_client(payload: ClientEdit, db: Session = Depends(get_db)):
    """Edit a client — any subset of group, status, name, phone, alias."""
    mac = payload.mac_address
    customer = db.execute(select(Customer).where(Customer.username == mac)).scalar_one_or_none()
    if customer is None:
        raise APIError(status_code=404, detail=f"Client '{mac}' not found")

    # The live metadata row mirrors group/status/name/phone/alias; created at add,
    # but create it here too in case a legacy client predates the mirror.
    meta = db.execute(
        select(RadadminClient).where(
            RadadminClient.mac_address == mac, RadadminClient.deleted_at.is_(None)
        )
    ).scalar_one_or_none()
    if meta is None:
        meta = RadadminClient(mac_address=mac, groupname=None, status=customer.status)
        db.add(meta)

    if payload.group is not None:
        if not _group_exists(db, payload.group):
            raise APIError(status_code=400, detail=f"Group '{payload.group}' not found")
        db.execute(
            update(RadUserGroup).where(RadUserGroup.username == mac).values(groupname=payload.group)
        )
        meta.groupname = payload.group

    if payload.status is not None:
        customer.status = payload.status
        meta.status = payload.status

    if payload.name is not None:
        meta.name = payload.name
    if payload.phone is not None:
        meta.phone = payload.phone
    if payload.alias is not None:
        meta.alias = payload.alias

    db.commit()

    group = db.execute(
        select(RadUserGroup.groupname).where(RadUserGroup.username == mac).limit(1)
    ).scalar_one_or_none()
    meta = db.execute(
        select(RadadminClient).where(
            RadadminClient.mac_address == mac, RadadminClient.deleted_at.is_(None)
        )
    ).scalar_one_or_none()
    return ClientOut(
        mac_address=mac, group=group, status=customer.status,
        name=meta.name if meta else None,
        phone=meta.phone if meta else None,
        alias=meta.alias if meta else None,
    )


@router.delete("/{mac_address}", status_code=204)
def delete_client(mac_address: str, db: Session = Depends(get_db)):
    """Delete a client: hard-remove its radcheck, radusergroup and customers rows,
    but only *soft* delete its radadmin_clients metadata (name/phone/alias) by
    stamping deleted_at, so it can be restored/referenced later. Soft-deleted rows
    are never returned by the API, and the client's MAC may be freely re-added."""
    mac = mac_address.strip().upper().replace(":", "-")
    if not _client_exists(db, mac):
        raise APIError(status_code=404, detail=f"Client '{mac}' not found")

    # radadmin_clients already mirrors group/status (kept in sync on add/edit), so
    # deleting just hard-removes the RADIUS/billing rows and soft-deletes metadata;
    # the mirrored group/status stay frozen on the row for reference.
    db.execute(delete(RadCheck).where(RadCheck.username == mac))
    db.execute(delete(RadUserGroup).where(RadUserGroup.username == mac))
    db.execute(delete(Customer).where(Customer.username == mac))
    db.execute(
        update(RadadminClient)
        .where(RadadminClient.mac_address == mac, RadadminClient.deleted_at.is_(None))
        .values(deleted_at=func.now())
    )
    db.commit()