from pathlib import Path

from fastapi import APIRouter, HTTPException, Query, Request
from fastapi.responses import JSONResponse, PlainTextResponse, Response
from fastapi.templating import Jinja2Templates

from app.db import fetch_all
from app.services.reports_export import EXPORT_DATASETS, export_all_json, fetch_dataset, list_datasets, to_csv

router = APIRouter(prefix="/reports", tags=["reports"])
BASE_DIR = Path(__file__).resolve().parent.parent.parent
templates = Jinja2Templates(directory=str(BASE_DIR / "templates"))


def _iso_rows(rows: list) -> list:
    for row in rows:
        for key, val in list(row.items()):
            if hasattr(val, "isoformat"):
                row[key] = val.isoformat()
    return rows


@router.get("")
async def reports_page(request: Request):
    briefings, events = [], []
    try:
        briefings = _iso_rows(fetch_all(
            "SELECT id, content, generated_by, created_at FROM daily_briefings ORDER BY created_at DESC LIMIT 20"
        ))
    except Exception:
        briefings = []
    try:
        events = _iso_rows(fetch_all(
            """SELECT id, agent_name, event_type, title, status, created_at FROM agent_events
               WHERE event_type IN ('briefing','report','herman_chat') ORDER BY created_at DESC LIMIT 50"""
        ))
    except Exception:
        events = []
    datasets = list_datasets()
    return templates.TemplateResponse(
        "reports.html",
        {
            "request": request,
            "page_title": "Reports & Export",
            "briefings": briefings,
            "report_events": events,
            "datasets": datasets,
        },
    )


@router.get("/api/datasets")
async def reports_datasets():
    return JSONResponse({"items": list_datasets()})


@router.get("/api/export/{name}")
async def reports_export_one(name: str, format: str = Query("csv", pattern="^(csv|json)$"), limit: int = Query(10000, ge=1, le=50000)):
    if name not in EXPORT_DATASETS:
        raise HTTPException(404, "Dataset not found")
    try:
        rows = fetch_dataset(name, limit)
    except Exception as exc:
        raise HTTPException(500, str(exc)) from exc
    if format == "json":
        return JSONResponse({"dataset": name, "count": len(rows), "items": rows})
    csv_text = to_csv(rows)
    return PlainTextResponse(
        csv_text,
        media_type="text/csv",
        headers={"Content-Disposition": f'attachment; filename="foodlinkk_{name}.csv"'},
    )


@router.get("/api/export-all")
async def reports_export_all(format: str = Query("json", pattern="^(json)$"), limit: int = Query(3000, ge=100, le=10000)):
    bundle = export_all_json(limit)
    return JSONResponse(bundle, headers={"Content-Disposition": 'attachment; filename="foodlinkk_full_export.json"'})