"""Attachments — read-only list of downloaded files."""
from __future__ import annotations

from typing import Any

from fastapi import Depends, Query
from sqlalchemy import desc, select
from sqlalchemy.orm import Session

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

from ._common import _session, router


@router.get("/api/attachments")
def list_attachments(
    site: str | None = Query(None, description="crawl_site.site_code"),
    target: str | None = Query(None, description="crawl_target.target_code"),
    ext: str | None = Query(None, description="file extension, e.g. pdf"),
    q: str | None = Query(None, description="substring of file_name"),
    limit: int = Query(100, ge=1, le=500),
    s: Session = Depends(_session),
) -> dict[str, Any]:
    stmt = (
        select(
            Attachment,
            Article.title.label("article_title"),
            Article.url.label("article_url"),
            CrawlSite.site_code.label("site_code"),
            CrawlTarget.target_code.label("target_code"),
        )
        .select_from(Attachment)
        .join(Article, Article.id == Attachment.article_id)
        .join(CrawlSite, CrawlSite.id == Article.site_id, isouter=True)
        .join(CrawlTarget, CrawlTarget.id == Article.target_id, isouter=True)
        .order_by(desc(Attachment.id))
        .limit(limit)
    )
    if site:
        stmt = stmt.where(CrawlSite.site_code == site)
    if target:
        stmt = stmt.where(CrawlTarget.target_code == target)
    if ext:
        stmt = stmt.where(Attachment.file_ext == ext.lstrip("."))
    if q:
        stmt = stmt.where(Attachment.file_name.ilike(f"%{q}%"))
    rows = s.execute(stmt).all()
    return {
        "count": len(rows),
        "items": [
            {
                "id": r.Attachment.id,
                "article_id": r.Attachment.article_id,
                "article_title": r.article_title,
                "article_url": r.article_url,
                "site_code": r.site_code,
                "target_code": r.target_code,
                "file_name": r.Attachment.file_name,
                "file_ext": r.Attachment.file_ext,
                "size_bytes": r.Attachment.size_bytes,
                "file_path": r.Attachment.file_path,
                "file_hash": r.Attachment.file_hash,
                "downloaded_at": r.Attachment.downloaded_at.isoformat()
                if r.Attachment.downloaded_at else None,
            }
            for r in rows
        ],
    }