"""Master column registry + subscriptions + batch subscribe."""
from __future__ import annotations

from typing import Any

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

from govcrawler.models import CrawlSite, CrawlTarget, MasterColumnRegistry
from govcrawler.repositories import targets as targets_repo

from ._common import (
    _build_registry_subscription_preview,
    _get_registry_row,
    _normalize_int,
    _session,
    iso_cn,
    router,
)


@router.get("/api/column-registry")
def column_registry(
    adapter: str | None = Query(None),
    site: str | None = Query(None, description="crawl_site.site_code"),
    q: str | None = Query(None, description="substring of column_name/path"),
    topic: str | None = Query(None, description="substring match in topic_tags"),
    subscribed: str | None = Query(None, description="all|yes|no"),
    limit: int = Query(200, ge=1, le=1000),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    stmt = (
        select(
            MasterColumnRegistry,
            CrawlSite.site_code.label("site_code"),
            CrawlSite.site_name.label("site_name"),
            CrawlTarget.target_code.label("target_code"),
            CrawlTarget.target_name.label("target_name"),
        )
        .select_from(MasterColumnRegistry)
        .join(CrawlSite, CrawlSite.id == MasterColumnRegistry.site_id)
        .join(
            CrawlTarget,
            CrawlTarget.id == MasterColumnRegistry.subscribed_target_id,
            isouter=True,
        )
        .order_by(
            CrawlSite.site_code,
            MasterColumnRegistry.column_name,
            MasterColumnRegistry.column_id,
        )
        .limit(limit)
    )
    if adapter:
        stmt = stmt.where(MasterColumnRegistry.adapter_id == adapter)
    if site:
        stmt = stmt.where(CrawlSite.site_code == site)
    if q:
        like = f"%{q}%"
        stmt = stmt.where(
            or_(
                MasterColumnRegistry.column_name.ilike(like),
                MasterColumnRegistry.column_path.ilike(like),
                MasterColumnRegistry.column_id.ilike(like),
            )
        )
    if topic:
        stmt = stmt.where(
            func.lower(func.cast(MasterColumnRegistry.topic_tags, String)).contains(topic.lower())
        )
    if subscribed == "yes":
        stmt = stmt.where(MasterColumnRegistry.subscribed_target_id.isnot(None))
    elif subscribed == "no":
        stmt = stmt.where(MasterColumnRegistry.subscribed_target_id.is_(None))
    rows = s.execute(stmt).all()
    return {
        "count": len(rows),
        "items": [
            {
                "id": r.MasterColumnRegistry.id,
                "adapter_id": r.MasterColumnRegistry.adapter_id,
                "site_code": r.site_code,
                "site_name": r.site_name,
                "column_id": r.MasterColumnRegistry.column_id,
                "column_name": r.MasterColumnRegistry.column_name,
                "column_path": r.MasterColumnRegistry.column_path,
                "admin_level": r.MasterColumnRegistry.admin_level,
                "topic_tags": r.MasterColumnRegistry.topic_tags or [],
                "post_count": r.MasterColumnRegistry.post_count,
                "last_seen_at": iso_cn(r.MasterColumnRegistry.last_seen_at),
                "active": r.MasterColumnRegistry.active,
                "subscribed_target_id": r.MasterColumnRegistry.subscribed_target_id,
                "target_code": r.target_code,
                "target_name": r.target_name,
            }
            for r in rows
        ],
    }


@router.get("/api/subscriptions")
def subscriptions(
    site: str | None = Query(None, description="crawl_site.site_code"),
    q: str | None = Query(None, description="substring of target_code / column_name / target_name"),
    limit: int = Query(200, ge=1, le=1000),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    stmt = (
        select(
            MasterColumnRegistry,
            CrawlSite.site_code.label("site_code"),
            CrawlSite.site_name.label("site_name"),
            CrawlTarget.target_code.label("target_code"),
            CrawlTarget.target_name.label("target_name"),
            CrawlTarget.channel_path.label("channel_path"),
            CrawlTarget.content_category.label("content_category"),
            CrawlTarget.enabled.label("target_enabled"),
        )
        .select_from(MasterColumnRegistry)
        .join(CrawlSite, CrawlSite.id == MasterColumnRegistry.site_id)
        .join(CrawlTarget, CrawlTarget.id == MasterColumnRegistry.subscribed_target_id)
        .order_by(CrawlSite.site_code, CrawlTarget.target_code)
        .limit(limit)
    )
    if site:
        stmt = stmt.where(CrawlSite.site_code == site)
    if q:
        like = f"%{q}%"
        stmt = stmt.where(
            or_(
                CrawlTarget.target_code.ilike(like),
                CrawlTarget.target_name.ilike(like),
                MasterColumnRegistry.column_name.ilike(like),
            )
        )
    rows = s.execute(stmt).all()
    return {
        "count": len(rows),
        "items": [
            {
                "registry_id": r.MasterColumnRegistry.id,
                "site_code": r.site_code,
                "site_name": r.site_name,
                "column_id": r.MasterColumnRegistry.column_id,
                "column_name": r.MasterColumnRegistry.column_name,
                "column_path": r.MasterColumnRegistry.column_path,
                "topic_tags": r.MasterColumnRegistry.topic_tags or [],
                "target_code": r.target_code,
                "target_name": r.target_name,
                "channel_path": r.channel_path,
                "content_category": r.content_category,
                "enabled": bool(r.target_enabled),
            }
            for r in rows
        ],
    }


@router.post("/api/column-registry/batch-preview")
def batch_preview_registry_subscriptions(
    payload: dict[str, Any] = Body(...),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    ids = payload.get("registry_ids")
    if not isinstance(ids, list) or not ids:
        raise HTTPException(400, "registry_ids must be a non-empty array")
    if len(ids) > 200:
        raise HTTPException(400, "registry_ids must be <= 200")
    items = []
    for raw_id in ids:
        registry_id = _normalize_int(raw_id, field="registry_id", minimum=1)
        assert registry_id is not None
        registry, site = _get_registry_row(s, registry_id)
        items.append(_build_registry_subscription_preview(s=s, registry=registry, site=site, payload=payload))
    return {"count": len(items), "items": items}


@router.post("/api/column-registry/{registry_id}/subscribe")
def subscribe_registry_row(
    registry_id: int,
    payload: dict[str, Any] = Body(default={}),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    registry, site = _get_registry_row(s, registry_id)
    preview = _build_registry_subscription_preview(s=s, registry=registry, site=site, payload=payload)
    if not preview["ready"]:
        raise HTTPException(400, "; ".join(preview["warnings"]) or "subscription preview is not ready")

    existing = targets_repo.get_by_code(s, preview["target_code"])
    if existing is not None and registry.subscribed_target_id not in {None, existing.id}:
        raise HTTPException(409, f"target_code already exists: {preview['target_code']}")

    target = targets_repo.upsert_by_code(
        s,
        target_code=preview["target_code"],
        site_id=site.id,
        site_department_id=preview["site_department_id"],
        target_name=preview["target_name"],
        entry_url=preview["entry_url"],
        sample_article_url=preview["sample_article_url"],
        dept_id=preview["dept_id"],
        channel_name=preview["channel_name"],
        channel_path=preview["channel_path"],
        content_category=preview["content_category"],
        content_subcategory=preview["content_subcategory"],
        expected_cadence_days=preview["expected_cadence_days"],
        interval_sec=preview["interval_sec"],
        enabled=preview["enabled"],
    )
    s.flush()
    registry.subscribed_target_id = target.id
    s.commit()
    s.refresh(target)
    s.refresh(registry)
    return {
        "subscription": {
            "registry_id": registry.id,
            "target_id": target.id,
            "target_code": target.target_code,
            "site_code": site.site_code,
            "column_id": registry.column_id,
        }
    }


@router.post("/api/column-registry/batch-subscribe")
def batch_subscribe_registry_rows(
    payload: dict[str, Any] = Body(...),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    ids = payload.get("registry_ids")
    if not isinstance(ids, list) or not ids:
        raise HTTPException(400, "registry_ids must be a non-empty array")
    previews: list[tuple[MasterColumnRegistry, CrawlSite, dict[str, Any]]] = []
    for raw_id in ids:
        registry_id = _normalize_int(raw_id, field="registry_id", minimum=1)
        assert registry_id is not None
        registry, site = _get_registry_row(s, registry_id)
        preview = _build_registry_subscription_preview(s=s, registry=registry, site=site, payload=payload)
        if not preview["ready"]:
            raise HTTPException(
                400,
                f"registry_id={registry.id} not ready: {'; '.join(preview['warnings'])}",
            )
        existing = targets_repo.get_by_code(s, preview["target_code"])
        if existing is not None and registry.subscribed_target_id not in {None, existing.id}:
            raise HTTPException(409, f"target_code already exists: {preview['target_code']}")
        previews.append((registry, site, preview))

    created = []
    for registry, site, preview in previews:
        target = targets_repo.upsert_by_code(
            s,
            target_code=preview["target_code"],
            site_id=site.id,
            site_department_id=preview["site_department_id"],
            target_name=preview["target_name"],
            entry_url=preview["entry_url"],
            sample_article_url=preview["sample_article_url"],
            dept_id=preview["dept_id"],
            channel_name=preview["channel_name"],
            channel_path=preview["channel_path"],
            content_category=preview["content_category"],
            content_subcategory=preview["content_subcategory"],
            expected_cadence_days=preview["expected_cadence_days"],
            interval_sec=preview["interval_sec"],
            enabled=preview["enabled"],
        )
        s.flush()
        registry.subscribed_target_id = target.id
        created.append({
            "registry_id": registry.id,
            "target_id": target.id,
            "target_code": target.target_code,
            "site_code": site.site_code,
            "column_id": registry.column_id,
        })
    s.commit()
    return {"count": len(created), "items": created}