"""Per-company reports: every run of one company's backup jobs over a period, as JSON (for the
printable report page) or CSV. Read-only, like the dashboard, so any signed-in user may view them."""

import csv
import io
import re
import sqlite3
from datetime import UTC, datetime, timedelta
from typing import Any
from urllib.parse import quote
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError

from fastapi import APIRouter, Depends, HTTPException, Query, Request, Response

from . import db, repo
from .timeutil import DB_FORMAT, to_db, utcnow

router = APIRouter(prefix="/api/v1")

CSV_COLUMNS = ("Company", "Job", "Source", "Started", "Finished", "Duration (seconds)", "Status",
               "Size (bytes)", "Message")


@router.get("/tenants")
def list_tenants(conn: sqlite3.Connection = Depends(db.get_db)):
    """Companies that have jobs, for choosing a report."""
    rows = conn.execute(
        """
        SELECT t.name, COUNT(j.id) AS jobs FROM tenants t JOIN jobs j ON j.tenant_id = t.id
        GROUP BY t.id ORDER BY t.name COLLATE NOCASE
        """
    ).fetchall()
    return {"tenants": [dict(r) for r in rows]}


def _load_report(request: Request, conn: sqlite3.Connection, tenant: str, days: int,
                 all_messages: bool = False) -> dict[str, Any]:
    retention = request.app.state.settings.retention_days
    if days > retention:
        raise HTTPException(422, f"Reports can cover at most {retention} days: older runs aren't kept")
    row = conn.execute("SELECT id, name FROM tenants WHERE name = ?", (tenant.strip(),)).fetchone()  # NOCASE
    if row is None:
        raise HTTPException(404, f"No company named {tenant!r}")

    now = utcnow()
    since = now - timedelta(days=days)
    jobs = repo.list_tenant_jobs(conn, row["id"])
    runs_by_job: dict[int, list[dict[str, Any]]] = {job["id"]: [] for job in jobs}
    # By default only warnings' and failures' messages: successes all say much the same, and an
    # hourly job has thousands of runs in 90 days
    for run in conn.execute(
        """
        SELECT r.job_id, r.status, r.started_at, r.finished_at,
               unixepoch(r.finished_at) - unixepoch(r.started_at) AS duration_seconds, r.size_bytes,
               CASE WHEN ? OR r.status IN ('warning', 'failed') THEN r.message END AS message
        FROM runs r JOIN jobs j ON j.id = r.job_id
        WHERE j.tenant_id = ? AND r.started_at >= ?
        ORDER BY r.started_at, r.id
        """,
        (all_messages, row["id"], to_db(since)),
    ):
        run = dict(run)
        runs_by_job[run.pop("job_id")].append(run)
    # When the dashboard first knew of each job: its earliest run (collectors backfill history), or
    # when it was first reported if earlier. Days before then are "not monitored", not "no backup".
    tracked = dict(conn.execute(
        """
        SELECT j.id, min(j.created_at, coalesce((SELECT MIN(r.started_at) FROM runs r WHERE r.job_id = j.id), j.created_at))
        FROM jobs j WHERE j.tenant_id = ?
        """,
        (row["id"],),
    ).fetchall())
    for job in jobs:
        job["runs"] = runs_by_job[job["id"]]
        job["tracked_since"] = tracked[job["id"]]
    return {
        "tenant": row["name"],
        "days": days,
        "since": to_db(since),
        "until": to_db(now),
        "generated_at": to_db(now),
        "jobs": jobs,
    }


@router.get("/reports/tenant")
def tenant_report(
    request: Request,
    tenant: str = Query(..., min_length=1),
    days: int = Query(30, ge=1),
    conn: sqlite3.Connection = Depends(db.get_db),
):
    return _load_report(request, conn, tenant, days)


def _safe_cell(value: Any) -> Any:
    """Defuse text that a spreadsheet would run as a formula. Job names and messages come from the
    backup servers, so a value like '=HYPERLINK(...)' must stay text."""
    if isinstance(value, str) and value[:1] in ("=", "+", "-", "@", "\t", "\r"):
        return "'" + value
    return value


@router.get("/reports/tenant.csv")
def tenant_report_csv(
    request: Request,
    tenant: str = Query(..., min_length=1),
    days: int = Query(30, ge=1),
    tz: str = Query("UTC", description="IANA time zone for the times, e.g. America/Edmonton"),
    conn: sqlite3.Connection = Depends(db.get_db),
):
    try:
        zone = ZoneInfo(tz)
    except (ZoneInfoNotFoundError, ValueError):
        raise HTTPException(422, f"Unknown time zone {tz!r}") from None
    report = _load_report(request, conn, tenant, days, all_messages=True)

    def local(value: str | None) -> str:
        if not value:
            return ""
        return datetime.strptime(value, DB_FORMAT).replace(tzinfo=UTC).astimezone(zone).strftime("%Y-%m-%d %H:%M:%S")

    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow([c.replace("Started", f"Started ({tz})").replace("Finished", f"Finished ({tz})") for c in CSV_COLUMNS])
    for job in report["jobs"]:
        for run in job["runs"]:
            writer.writerow([_safe_cell(v) for v in (
                report["tenant"], job["job_name"], job["source_system"],
                local(run["started_at"]), local(run["finished_at"]),
                run["duration_seconds"] if run["duration_seconds"] is not None else "",
                run["status"], run["size_bytes"] if run["size_bytes"] is not None else "",
                run["message"] or "",
            )])

    slug = re.sub(r"[^A-Za-z0-9]+", "-", report["tenant"]).strip("-").lower() or "company"
    filename = f"backup-report-{slug}-{datetime.now(zone):%Y-%m-%d}.csv"
    return Response(
        # BOM so Excel reads it as UTF-8 (accents, en dashes in job names)
        content="" + out.getvalue(),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename=\"{filename}\"; filename*=UTF-8''{quote(filename)}"},
    )