"""Orchestrate the PSP alvará scrape → diff → auto-register + alert.

Flow per run:
  1. Scrape current listing for the requested alvará type.
  2. Insert an alvara_snapshots row for the run (for audit / debugging).
  3. Upsert each row into alvara_companies (bump last_seen_at, clear
     removed_at if the company had disappeared and returned).
  4. Mark as removed any alvara_companies that weren't touched this run.
  5. For freshly detected NIPCs (first_seen in this run), auto-create a
     `monitoring_type='related'` row in companies if not already known,
     so the new outfit surfaces in the Concorrentes → Relacionadas tab.
"""
import logging
import time
from datetime import datetime, timezone

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.scrapers.psp_alvara import scrape_alvara_list

logger = logging.getLogger(__name__)


async def sync_alvara_type(
    session: AsyncSession, alvara_type: str = "A"
) -> dict:
    """Run a full scrape+diff cycle. Returns summary counts."""
    started = time.monotonic()
    run_start = datetime.now(tz=timezone.utc)

    rows: list[dict] = []
    error: str | None = None
    try:
        rows = await scrape_alvara_list(alvara_type)
    except Exception as e:
        error = str(e)[:500]
        logger.exception("PSP scrape failed")

    duration_ms = int((time.monotonic() - started) * 1000)

    snap_id = (
        await session.execute(
            text(
                """
                INSERT INTO alvara_snapshots
                    (alvara_type, fetched_at, total_count, success,
                     error_message, duration_ms)
                VALUES (:t, :at, :n, :ok, :err, :ms)
                RETURNING id::text
                """
            ),
            {
                "t": alvara_type, "at": run_start, "n": len(rows),
                "ok": error is None, "err": error, "ms": duration_ms,
            },
        )
    ).scalar_one()
    await session.commit()

    if error is not None:
        return {
            "snapshot_id": snap_id, "alvara_type": alvara_type,
            "count": 0, "new": 0, "removed": 0, "error": error,
        }

    # Upsert every row, tracking which were newly inserted (first_seen_at
    # equals the row's created timestamp).
    new_nipcs: list[tuple[str, str | None]] = []
    for r in rows:
        res = await session.execute(
            text(
                """
                INSERT INTO alvara_companies
                    (nipc, alvara_type, alvara_number, nome, morada,
                     localidade, telefone, email,
                     first_seen_at, last_seen_at, removed_at)
                VALUES (:nipc, :at, :an, :nm, :mo, :lo, :tel, :em,
                        :run, :run, NULL)
                ON CONFLICT (nipc, alvara_type) DO UPDATE SET
                    last_seen_at  = EXCLUDED.last_seen_at,
                    removed_at    = NULL,
                    alvara_number = COALESCE(EXCLUDED.alvara_number, alvara_companies.alvara_number),
                    nome          = COALESCE(EXCLUDED.nome, alvara_companies.nome),
                    morada        = COALESCE(EXCLUDED.morada, alvara_companies.morada),
                    localidade    = COALESCE(EXCLUDED.localidade, alvara_companies.localidade),
                    telefone      = COALESCE(EXCLUDED.telefone, alvara_companies.telefone),
                    email         = COALESCE(EXCLUDED.email, alvara_companies.email)
                RETURNING (xmax = 0) AS is_new
                """
            ),
            {
                "nipc": r["nipc"], "at": alvara_type,
                "an": r.get("alvara_number"), "nm": r.get("nome"),
                "mo": r.get("morada"), "lo": r.get("localidade"),
                "tel": r.get("telefone"), "em": r.get("email"),
                "run": run_start,
            },
        )
        is_new = res.scalar()
        if is_new:
            new_nipcs.append((r["nipc"], r.get("nome")))

    # Mark as removed anything we didn't see this run. Use last_seen_at
    # strictly less than this run's start — rows we touched above have
    # last_seen_at == run_start.
    removed = (
        await session.execute(
            text(
                """
                UPDATE alvara_companies
                SET removed_at = :run
                WHERE alvara_type = :at
                  AND last_seen_at < :run
                  AND removed_at IS NULL
                RETURNING nipc
                """
            ),
            {"at": alvara_type, "run": run_start},
        )
    ).all()

    # Auto-create companies rows for freshly detected NIPCs. We use
    # monitoring_type='related' + monitored=false so they show up in the
    # Concorrentes → Relacionadas tab without spamming active monitoring.
    for nipc, nome in new_nipcs:
        if not nome:
            continue
        await session.execute(
            text(
                """
                INSERT INTO companies
                    (nif, legal_name, monitoring_type, monitored,
                     active, status)
                VALUES (:nif, :nm, 'related', false, true, 'active')
                ON CONFLICT (nif) DO NOTHING
                """
            ),
            {"nif": nipc, "nm": nome[:200]},
        )

    await session.commit()

    logger.info(
        "PSP alvará %s sync: %d rows, %d new, %d removed (%dms)",
        alvara_type, len(rows), len(new_nipcs), len(removed), duration_ms,
    )
    return {
        "snapshot_id": snap_id,
        "alvara_type": alvara_type,
        "count": len(rows),
        "new": [{"nipc": n, "nome": nm} for n, nm in new_nipcs],
        "removed": [r[0] for r in removed],
        "duration_ms": duration_ms,
    }