"""Site-department ⨝ local-department endpoints (binding reconciliation)."""
from __future__ import annotations

from datetime import timedelta
from typing import Any

from fastapi import Body, Depends, HTTPException, Query
from sqlalchemy import func, or_, select
from sqlalchemy.orm import Session

from govcrawler.models import (
    CrawlSite,
    CrawlTarget,
    LocalDepartment,
    SiteDepartment,
)
from govcrawler.repositories import depts as depts_repo
from govcrawler.repositories import sites as sites_repo
from govcrawler.timeutil import now_cn_naive

from ._common import (
    _normalize_bool,
    _normalize_int,
    _normalize_str,
    _session,
    iso_cn,
    router,
)


@router.get("/api/departments")
def list_departments(
    site: str | None = Query(None, description="crawl_site.site_code"),
    binding: str | None = Query(None, description="pending|mapped|city_level|cross_dept|external_ref"),
    q: str | None = Query(None, description="substring of dept_path or display name"),
    limit: int = Query(200, ge=1, le=5000),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    """List site_department rows joined to their crawl_site + local_department.

    The UI uses this to audit `pending` dept_paths (not yet mapped to an OA
    local_dept_id) and to spot `cross_dept` / `city_level` bindings that
    intentionally carry no local_dept_id.
    """
    stmt = (
        select(
            SiteDepartment,
            CrawlSite.site_code.label("site_code"),
            CrawlSite.site_name.label("site_name"),
            LocalDepartment.dept_name.label("local_dept_name"),
            LocalDepartment.full_name.label("local_dept_full_name"),
            func.count(CrawlTarget.id).label("target_count"),
        )
        .select_from(SiteDepartment)
        .join(CrawlSite, CrawlSite.id == SiteDepartment.site_id)
        .join(
            LocalDepartment,
            LocalDepartment.dept_id == SiteDepartment.local_dept_id,
            isouter=True,
        )
        .join(
            CrawlTarget,
            CrawlTarget.site_department_id == SiteDepartment.id,
            isouter=True,
        )
        .group_by(
            SiteDepartment.id, CrawlSite.site_code, CrawlSite.site_name,
            LocalDepartment.dept_name, LocalDepartment.full_name,
        )
        .order_by(CrawlSite.site_code, SiteDepartment.dept_path)
        .limit(limit)
    )
    if site:
        stmt = stmt.where(CrawlSite.site_code == site)
    if binding:
        stmt = stmt.where(SiteDepartment.dept_binding == binding)
    if q:
        like = f"%{q}%"
        stmt = stmt.where(
            or_(
                SiteDepartment.dept_path.ilike(like),
                SiteDepartment.dept_display_name.ilike(like),
            )
        )
    rows = s.execute(stmt).all()
    return {
        "count": len(rows),
        "items": [
            {
                "id": r.SiteDepartment.id,
                "site_code": r.site_code,
                "site_name": r.site_name,
                "dept_path": r.SiteDepartment.dept_path,
                "dept_binding": r.SiteDepartment.dept_binding,
                "dept_display_name": r.SiteDepartment.dept_display_name,
                "local_dept_id": r.SiteDepartment.local_dept_id,
                "local_dept_name": r.local_dept_name,
                "local_dept_full_name": r.local_dept_full_name,
                "detect_status": r.SiteDepartment.detect_status,
                "enabled": r.SiteDepartment.enabled,
                "target_count": int(r.target_count or 0),
            }
            for r in rows
        ],
    }


@router.get("/api/departments/{dept_row_id}/health")
def department_health(
    dept_row_id: int,
    s: Session = Depends(_session),
) -> dict[str, Any]:
    row = depts_repo.get_by_id(s, dept_row_id)
    if row is None:
        raise HTTPException(404, "site_department not found")

    now = now_cn_naive()
    targets = list(
        s.execute(
            select(CrawlTarget, CrawlSite.site_code, CrawlSite.site_name, LocalDepartment.dept_name)
            .join(CrawlSite, CrawlSite.id == CrawlTarget.site_id)
            .join(LocalDepartment, LocalDepartment.dept_id == CrawlTarget.dept_id, isouter=True)
            .where(CrawlTarget.site_department_id == dept_row_id)
            .order_by(CrawlTarget.target_code)
        ).all()
    )

    items = []
    stale_count = 0
    for target, site_code, site_name, local_dept_name in targets:
        cadence_days = int(target.expected_cadence_days or 30)
        stale = False
        stale_days = None
        if target.last_article_time is not None:
            delta = now - target.last_article_time
            stale = delta > timedelta(days=3 * cadence_days)
            stale_days = round(delta.total_seconds() / 86400, 1)
        elif target.last_crawled_at is not None:
            delta = now - target.last_crawled_at
            stale = delta > timedelta(days=3 * cadence_days)
            stale_days = round(delta.total_seconds() / 86400, 1)
        if stale:
            stale_count += 1
        items.append({
            "target_id": target.id,
            "target_code": target.target_code,
            "target_name": target.target_name,
            "site_code": site_code,
            "site_name": site_name,
            "dept_id": target.dept_id,
            "local_dept_name": local_dept_name,
            "channel_name": target.channel_name,
            "channel_path": target.channel_path,
            "content_category": target.content_category,
            "expected_cadence_days": cadence_days,
            "last_crawled_at": iso_cn(target.last_crawled_at),
            "last_article_time": iso_cn(target.last_article_time),
            "enabled": target.enabled,
            "stale": stale,
            "stale_days": stale_days,
        })

    return {
        "department": {
            "id": row.id,
            "site_code": row.site.site_code if row.site else None,
            "site_name": row.site.site_name if row.site else None,
            "dept_path": row.dept_path,
            "dept_binding": row.dept_binding,
            "dept_display_name": row.dept_display_name,
            "local_dept_id": row.local_dept_id,
            "local_dept_name": row.local_dept.dept_name if row.local_dept else None,
            "target_count": len(items),
            "stale_count": stale_count,
        },
        "items": items,
    }


@router.post("/api/departments")
def create_department_binding(
    payload: dict[str, Any] = Body(...),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    site_code = _normalize_str(payload.get("site_code"))
    dept_path = _normalize_str(payload.get("dept_path"))
    dept_binding = _normalize_str(payload.get("dept_binding")) or "pending"
    dept_display_name = _normalize_str(payload.get("dept_display_name"))
    local_dept_id = payload.get("local_dept_id")
    enabled = _normalize_bool(payload.get("enabled"), default=True)
    if not site_code or not dept_path:
        raise HTTPException(400, "site_code and dept_path are required")
    if local_dept_id is not None:
        local_dept_id = _normalize_int(local_dept_id, field="local_dept_id", minimum=1)
        if s.get(LocalDepartment, local_dept_id) is None:
            raise HTTPException(404, f"local_department not found: {local_dept_id}")
    site = sites_repo.get_by_code(s, site_code)
    if site is None:
        raise HTTPException(404, f"site not found: {site_code}")
    try:
        row = depts_repo.upsert(
            s,
            site_id=site.id,
            dept_path=dept_path,
            dept_binding=dept_binding,
            local_dept_id=local_dept_id,
            dept_display_name=dept_display_name,
            enabled=enabled,
        )
    except ValueError as e:
        raise HTTPException(400, str(e))
    s.commit()
    s.refresh(row)
    return {
        "department": {
            "id": row.id,
            "site_code": site.site_code,
            "dept_path": row.dept_path,
            "dept_binding": row.dept_binding,
            "dept_display_name": row.dept_display_name,
            "local_dept_id": row.local_dept_id,
            "enabled": row.enabled,
        }
    }


@router.put("/api/departments/{dept_row_id}")
def update_department_binding(
    dept_row_id: int,
    payload: dict[str, Any] = Body(...),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    row = depts_repo.get_by_id(s, dept_row_id)
    if row is None:
        raise HTTPException(404, "site_department not found")
    if "local_dept_id" in payload and payload.get("local_dept_id") is not None:
        local_dept_id = _normalize_int(payload.get("local_dept_id"), field="local_dept_id", minimum=1)
        if s.get(LocalDepartment, local_dept_id) is None:
            raise HTTPException(404, f"local_department not found: {local_dept_id}")
    else:
        local_dept_id = payload.get("local_dept_id") if "local_dept_id" in payload else row.local_dept_id

    dept_binding = _normalize_str(payload.get("dept_binding")) if "dept_binding" in payload else row.dept_binding
    dept_display_name = _normalize_str(payload.get("dept_display_name")) if "dept_display_name" in payload else row.dept_display_name
    enabled = _normalize_bool(payload.get("enabled"), default=row.enabled) if "enabled" in payload else row.enabled
    try:
        depts_repo.upsert(
            s,
            site_id=row.site_id,
            dept_path=row.dept_path,
            dept_binding=dept_binding or "pending",
            local_dept_id=local_dept_id,
            dept_display_name=dept_display_name,
            enabled=enabled,
        )
    except ValueError as e:
        raise HTTPException(400, str(e))
    s.commit()
    s.refresh(row)
    return {
        "department": {
            "id": row.id,
            "site_code": row.site.site_code if row.site else None,
            "dept_path": row.dept_path,
            "dept_binding": row.dept_binding,
            "dept_display_name": row.dept_display_name,
            "local_dept_id": row.local_dept_id,
            "enabled": row.enabled,
        }
    }


@router.delete("/api/departments/{dept_row_id}")
def delete_department(
    dept_row_id: int, s: Session = Depends(_session),
) -> dict[str, Any]:
    row = depts_repo.get_by_id(s, dept_row_id)
    if row is None:
        raise HTTPException(404, "site_department not found")
    # crawl_target.site_department_id is ondelete=CASCADE — refuse if any
    # target still references this dept, so operators don't accidentally wipe
    # a whole batch of targets by clicking one button.
    target_count = s.scalar(
        select(func.count())
        .select_from(CrawlTarget)
        .where(CrawlTarget.site_department_id == row.id)
    ) or 0
    if target_count > 0:
        raise HTTPException(
            409,
            f"department has {target_count} target(s); reassign or delete "
            "them first.",
        )
    s.delete(row)
    s.commit()
    return {"deleted": True, "id": dept_row_id}