"""SnelStart connection state — PostgreSQL is the source of truth."""

from __future__ import annotations

import logging
from datetime import datetime, timezone
from urllib.parse import urlparse
from urllib.request import Request, urlopen

from sqlalchemy import select
from sqlalchemy.exc import IntegrityError

from app.config import settings
from app.db import SessionLocal, SnelStartConnectionRow
from app.services.snelstart.models import (
    CONNECTION_STATUSES,
    SnelStartConnectionInfo,
    SnelStartConnectionStatus,
)

logger = logging.getLogger(__name__)


def integration_enabled() -> bool:
    """True only when required SnelStart configuration is present."""
    return settings.snelstart_integration_enabled


def _normalize_status(raw: str | None) -> SnelStartConnectionStatus:
    value = (raw or "not_connected").strip().lower()
    if value not in CONNECTION_STATUSES:
        return "not_connected"
    return value  # type: ignore[return-value]


def _row_to_info(row: SnelStartConnectionRow) -> SnelStartConnectionInfo:
    status = _normalize_status(row.status)
    enabled = integration_enabled()
    connected = enabled and status == "connected"
    return SnelStartConnectionInfo(
        clientId=row.client_id,
        status=status,
        externalAdministrationId=row.external_administration_id,
        lastSyncAt=row.last_sync_at.isoformat() if row.last_sync_at else None,
        lastSyncError=row.last_sync_error,
        integrationEnabled=enabled,
        isConnected=connected,
    )


def ensure_connection_row(client_id: str, *, legacy_snelstart: bool = False) -> SnelStartConnectionRow:
    """Ensure a connection row exists for a client."""
    with SessionLocal() as session:
        row = session.scalar(
            select(SnelStartConnectionRow).where(SnelStartConnectionRow.client_id == client_id)
        )
        if row:
            session.expunge(row)
            return row

        # Legacy ledger boolean must not imply a real connection.
        status = "not_connected"
        if legacy_snelstart and integration_enabled():
            status = "pending"

        now = datetime.utcnow()
        row = SnelStartConnectionRow(
            client_id=client_id,
            status=status,
            created_at=now,
            updated_at=now,
        )
        session.add(row)
        try:
            session.commit()
        except IntegrityError:
            session.rollback()
            row = session.scalar(
                select(SnelStartConnectionRow).where(SnelStartConnectionRow.client_id == client_id)
            )
            if not row:
                raise
            session.expunge(row)
            return row
        session.refresh(row)
        session.expunge(row)
        return row


def get_connection(client_id: str, *, legacy_snelstart: bool = False) -> SnelStartConnectionInfo:
    row = ensure_connection_row(client_id, legacy_snelstart=legacy_snelstart)
    return _row_to_info(row)


def list_connections(*, client_ids: list[str] | None = None) -> list[SnelStartConnectionInfo]:
    with SessionLocal() as session:
        stmt = select(SnelStartConnectionRow)
        if client_ids:
            stmt = stmt.where(SnelStartConnectionRow.client_id.in_(client_ids))
        rows = session.scalars(stmt).all()
        return [_row_to_info(r) for r in rows]


def is_client_connected(client_id: str, *, legacy_snelstart: bool = False) -> bool:
    return get_connection(client_id, legacy_snelstart=legacy_snelstart).isConnected


def record_sync_attempt(
    client_id: str,
    *,
    error: str | None = None,
    success: bool = False,
) -> None:
    now = datetime.utcnow()
    with SessionLocal() as session:
        row = session.scalar(
            select(SnelStartConnectionRow).where(SnelStartConnectionRow.client_id == client_id)
        )
        if not row:
            row = SnelStartConnectionRow(client_id=client_id, status="not_connected", created_at=now)
            session.add(row)
        row.updated_at = now
        if success:
            row.last_sync_at = now
            row.last_sync_error = None
        elif error:
            row.last_sync_error = error[:2000]
        session.commit()


def sync_legacy_client_flags(clients: list) -> None:
    """Align ledger client flags with PostgreSQL connection rows.

    Read-only: missing rows stay not_connected. Workspace load must not insert.
    """
    if not clients:
        return
    client_ids = [client.id for client in clients if getattr(client, "id", None)]
    existing = {info.clientId: info for info in list_connections(client_ids=client_ids)}
    for client in clients:
        info = existing.get(client.id)
        if info:
            client.snelstartStatus = info.status
            client.snelstart = info.isConnected
        else:
            client.snelstartStatus = "not_connected"
            client.snelstart = False


def _api_host() -> str | None:
    raw = settings.snelstart_api_base_url.strip()
    if not raw:
        return None
    parsed = urlparse(raw if "://" in raw else f"https://{raw}")
    return parsed.netloc or None


def probe_snelstart_api() -> dict[str, object]:
    """
    Live reachability check of the configured SnelStart host.

    This is not authentication and does not invent a connected administration.
    """
    url = settings.snelstart_api_base_url.strip()
    if not url:
        return {
            "checked": False,
            "reachable": None,
            "httpStatus": None,
            "message": "SnelStart API is not configured.",
        }
    if "://" not in url:
        url = f"https://{url}"
    try:
        req = Request(url, method="GET", headers={"User-Agent": "Amanah-SnelStart-Probe"})
        with urlopen(req, timeout=5) as resp:  # noqa: S310 — operator-configured URL
            status = getattr(resp, "status", 200)
            return {
                "checked": True,
                "reachable": True,
                "httpStatus": status,
                "message": "SnelStart API host responded.",
            }
    except Exception as exc:
        logger.info("SnelStart live probe failed: %s", exc)
        return {
            "checked": True,
            "reachable": False,
            "httpStatus": None,
            "message": "Could not reach the SnelStart API host.",
        }


def firm_connection_overview() -> dict[str, object]:
    """Live Firm-side SnelStart status from config + PostgreSQL (no fake connections)."""
    from app.store import store

    enabled = integration_enabled()
    clients = store.snapshot().clients
    items: list[dict[str, object]] = []
    counts = {
        "clients": len(clients),
        "connected": 0,
        "pending": 0,
        "error": 0,
        "notConnected": 0,
    }
    for client in clients:
        info = get_connection(client.id, legacy_snelstart=bool(client.snelstart))
        row = info.to_public_dict()
        row["clientName"] = client.name
        items.append(row)
        if info.isConnected:
            counts["connected"] += 1
        elif info.status == "pending":
            counts["pending"] += 1
        elif info.status == "error":
            counts["error"] += 1
        else:
            counts["notConnected"] += 1

    if enabled:
        probe = probe_snelstart_api()
    else:
        probe = {
            "checked": False,
            "reachable": None,
            "httpStatus": None,
            "message": (
                "SnelStart integration is not configured. "
                "Live connection stays off until API credentials are set."
            ),
        }

    return {
        "live": True,
        "integrationEnabled": enabled,
        "configured": {
            "apiBaseUrl": bool(settings.snelstart_api_base_url.strip()),
            "clientId": bool(settings.snelstart_client_id.strip()),
            "clientSecret": bool(settings.snelstart_client_secret.strip()),
            "redirectUri": bool(settings.snelstart_redirect_uri.strip()),
        },
        "apiHost": _api_host(),
        "probe": probe,
        "counts": counts,
        "items": items,
        "checkedAt": datetime.now(timezone.utc).replace(microsecond=0).isoformat().replace("+00:00", "Z"),
    }