"""Narrow standard attachment downloader API."""
from __future__ import annotations

from typing import Any

from fastapi import Depends, Query
from sqlalchemy import exists, func, select
from sqlalchemy.orm import Session

from govcrawler.models import Article, ArticleStandardMeta, Attachment, CrawlSite, CrawlTarget

from ._common import _session, router
from .articles import _standard_meta_payload


@router.get("/api/standard-attachments/pending")
def list_pending_standard_attachments(
    q: str | None = Query(None),
    limit: int = Query(100, ge=1, le=500),
    offset: int = Query(0, ge=0),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    filters = [
        CrawlSite.site_code == "openstd_samr",
        ~exists().where(Attachment.article_id == Article.id),
    ]
    if q:
        like = f"%{q.strip()}%"
        filters.append(
            Article.title.ilike(like)
            | Article.doc_no.ilike(like)
            | ArticleStandardMeta.standard_no.ilike(like)
        )

    base = (
        select(Article, ArticleStandardMeta, CrawlSite.site_code, CrawlTarget.target_code)
        .select_from(Article)
        .join(CrawlSite, CrawlSite.id == Article.site_id)
        .join(CrawlTarget, CrawlTarget.id == Article.target_id, isouter=True)
        .join(ArticleStandardMeta, ArticleStandardMeta.article_id == Article.id)
        .where(*filters)
    )
    total = s.scalar(
        select(func.count()).select_from(base.subquery())
    ) or 0
    rows = s.execute(
        base.order_by(Article.id.desc()).offset(offset).limit(limit)
    ).all()
    items = []
    for article, standard_meta, site_code, target_code in rows:
        items.append({
            "id": article.id,
            "title": article.title,
            "doc_no": article.doc_no,
            "publish_date": article.publish_date.isoformat() if article.publish_date else None,
            "effective_date": article.effective_date.isoformat() if article.effective_date else None,
            "open_category": article.open_category,
            "site_code": site_code,
            "target_code": target_code,
            "standard_meta": _standard_meta_payload(standard_meta),
        })
    return {"total": total, "items": items, "limit": limit, "offset": offset}