"""Reports — saved multi-source report templates (app/crud/report_builder.py).
Managing them needs the Reports module's create/edit/delete; running one
needs Reports view, plus one of the template's roles when it names any, plus
read access to every source a section uses (checked per section).

Public Portal users list, open, run and export only the reports their Portal
role grants (Portal Roles › Reports, action_matrix["report_templates"]). A
granted report's datasource and form sections need no separate grant — the
grant is the administrator's choice of what that role may see — but the
dataset engine still applies record access policies and limits form rows to
the user's own vendor. A data query section still needs the query to allow
the Portal role.

A Portal role that includes the Report Builder (the whole-feature "reports"
grant) also lets its users build their own reports (vendor_id set): only
they see them, and those read only the sources their role grants. Every run
and export takes this run's own extra filters, columns and headings
(ReportRunRequest.overrides)."""

import asyncio
import csv
import io
import re
import uuid
from datetime import datetime

from fastapi import APIRouter, Depends, HTTPException, status
from fastapi.responses import StreamingResponse
from openpyxl import Workbook
from openpyxl.styles import Font
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4, landscape
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from xml.sax.saxutils import escape

from app.core.database import get_db
from app.core.timezones import localize_rows, tenant_zone
from app.core.permissions import check_portal_action, resolve_action_matrix, resolve_portal_grants
from app.core.security import AuthContext, get_auth_context
from app.crud.audit import write_audit_log
from app.crud.change_log import check_fresh
from app.crud.report_builder import ReportError, run_report
from app.models.report_template import ReportTemplate
from app.routers.datasets import _require_query_access
from app.schemas.report_template import (
    ReportPreviewRequest,
    ReportRunRequest,
    ReportRunResult,
    ReportTemplateCreate,
    ReportTemplateRead,
    ReportTemplateUpdate,
)

router = APIRouter(prefix="/report-templates", tags=["reports"])


async def _get(db: AsyncSession, tenant_id: uuid.UUID, report_id: uuid.UUID) -> ReportTemplate:
    report = (await db.execute(select(ReportTemplate).where(ReportTemplate.tenant_id == tenant_id, ReportTemplate.id == report_id))).scalar_one_or_none()
    if report is None:
        raise HTTPException(status_code=404, detail="Report not found.")
    return report


async def _viewer(ctx: AuthContext = Depends(get_auth_context), db: AsyncSession = Depends(get_db)) -> AuthContext:
    """Reports view for internal users; Portal users are checked per report."""
    if ctx.vendor_id is not None:
        return ctx
    matrix, admin = await resolve_action_matrix(db, ctx.tenant_id, ctx.user_id)
    if not admin and "view" not in matrix.get("reports", []):
        raise HTTPException(status_code=403, detail="Your role doesn't have 'view' access to reports.")
    return ctx


async def _portal_granted(db: AsyncSession, ctx: AuthContext) -> set[str]:
    return (await resolve_portal_grants(db, ctx.tenant_id, ctx.user_id)).get("report_templates", set())


def _owns(ctx: AuthContext, report: ReportTemplate) -> bool:
    """A Portal user's own report (built in the Portal's Report Builder)."""
    return ctx.vendor_id is not None and report.vendor_id == ctx.vendor_id and report.created_by == ctx.user_id


def _designer(action: str):
    """Who may build reports: Studio users with the Reports module's
    `action`; Portal users whose role includes the Report Builder (the
    whole-feature "reports" grant) — for their own reports only."""

    async def checker(ctx: AuthContext = Depends(get_auth_context), db: AsyncSession = Depends(get_db)) -> AuthContext:
        if ctx.vendor_id is not None:
            grants = await resolve_portal_grants(db, ctx.tenant_id, ctx.user_id)
            if not grants.get("reports"):
                raise HTTPException(status_code=403, detail="Your portal role doesn't include the Report Builder.")
            return ctx
        matrix, admin = await resolve_action_matrix(db, ctx.tenant_id, ctx.user_id)
        if not admin and action not in matrix.get("reports", []):
            raise HTTPException(status_code=403, detail=f"Your role doesn't have '{action}' access to reports.")
        return ctx

    return checker


async def _check_portal_sources(db: AsyncSession, ctx: AuthContext, sections: list[dict]) -> None:
    """A Portal user's own report may read only what their role grants."""
    if ctx.vendor_id is None:
        return
    for s in sections:
        refs = [] if s.get("kind") == "text" or not s.get("source_id") else [(s["source_type"], s["source_id"])]
        refs += [(j["source_type"], j["source_id"]) for j in s.get("joins") or [] if j.get("source_id")]
        for source_type, source_id in refs:
            try:
                await _require_query_access(db, ctx, source_type, uuid.UUID(str(source_id)))
            except HTTPException as exc:
                raise HTTPException(status_code=403, detail=f"{s.get('title') or s['id']}: your portal role doesn't grant that source.") from exc


async def _require_run(db: AsyncSession, ctx: AuthContext, report: ReportTemplate) -> None:
    if ctx.vendor_id is not None:
        if not _owns(ctx, report) and str(report.id).lower() not in await _portal_granted(db, ctx):
            raise HTTPException(status_code=404, detail="Report not found.")
        return
    allowed = {str(r) for r in report.role_ids or []}
    if not allowed:
        return
    from app.crud.access_policy import user_roles

    roles, admin = await user_roles(db, ctx.tenant_id, ctx.user_id)
    if not admin and not roles & allowed:
        raise HTTPException(status_code=403, detail="Your role can't run this report.")


async def _run(db: AsyncSession, ctx: AuthContext, template: dict, params: dict, overrides: dict | None = None, granted: bool = False) -> dict:
    """`granted`: a report granted to the caller's Portal role — its master and
    form sections need no separate grant (see the module docstring). A Portal
    user's own report gets no such pass."""

    async def check(source_type: str, source_id: uuid.UUID) -> None:
        if granted and source_type in ("master", "form"):
            return
        await _require_query_access(db, ctx, source_type, source_id)

    try:
        return await run_report(db, ctx.tenant_id, ctx.user_id, template, params, check, overrides)
    except ReportError as exc:
        raise HTTPException(status_code=422, detail=str(exc)) from exc


def _template_dict(report: ReportTemplate) -> dict:
    return {"name": report.name, "parameters": report.parameters, "sections": report.sections, "settings": report.settings}


def _overrides(payload) -> dict:
    return {k: v.model_dump(mode="json") for k, v in payload.overrides.items()}


# ---- CRUD -------------------------------------------------------------------------------------------


@router.get("", response_model=list[ReportTemplateRead])
async def list_reports(
    ctx: AuthContext = Depends(_viewer),
    db: AsyncSession = Depends(get_db),
) -> list[ReportTemplate]:
    reports = list((await db.execute(select(ReportTemplate).where(ReportTemplate.tenant_id == ctx.tenant_id).order_by(ReportTemplate.name))).scalars().all())
    if ctx.vendor_id is not None:
        granted = await _portal_granted(db, ctx)
        reports = [r for r in reports if str(r.id).lower() in granted or _owns(ctx, r)]
    return reports


@router.get("/{report_id}", response_model=ReportTemplateRead)
async def get_report(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(_viewer),
    db: AsyncSession = Depends(get_db),
) -> ReportTemplate:
    report = await _get(db, ctx.tenant_id, report_id)
    if ctx.vendor_id is not None:
        await _require_run(db, ctx, report)
    return report


@router.post("", response_model=ReportTemplateRead, status_code=status.HTTP_201_CREATED)
async def create_report(
    payload: ReportTemplateCreate,
    ctx: AuthContext = Depends(_designer("create")),
    db: AsyncSession = Depends(get_db),
) -> ReportTemplate:
    data = payload.model_dump(mode="json")
    await _check_portal_sources(db, ctx, data["sections"])
    report = ReportTemplate(
        tenant_id=ctx.tenant_id,
        name=data["name"].strip(),
        description=(data.get("description") or "").strip() or None,
        parameters=data["parameters"],
        sections=data["sections"],
        settings=data["settings"],
        # Role limits are a Studio setting; a Portal user's report is theirs alone.
        role_ids=data["role_ids"] if ctx.vendor_id is None else [],
        vendor_id=ctx.vendor_id,
        created_by=ctx.user_id,
        updated_by=ctx.user_id,
    )
    db.add(report)
    await db.flush()
    await db.refresh(report)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "report.create", "report_template", report.id, {"name": report.name})
    return report


@router.patch("/{report_id}", response_model=ReportTemplateRead)
async def update_report(
    report_id: uuid.UUID,
    payload: ReportTemplateUpdate,
    ctx: AuthContext = Depends(_designer("edit")),
    db: AsyncSession = Depends(get_db),
) -> ReportTemplate:
    report = await _get(db, ctx.tenant_id, report_id)
    if ctx.vendor_id is not None and not _owns(ctx, report):
        raise HTTPException(status_code=404, detail="Report not found.")
    check_fresh(report, payload.expected_updated_at)
    data = payload.model_dump(mode="json", exclude_unset=True)
    data.pop("expected_updated_at", None)
    if ctx.vendor_id is not None:
        data.pop("role_ids", None)
    if "sections" in data:
        await _check_portal_sources(db, ctx, data["sections"])
    for key, value in data.items():
        if key == "name":
            value = value.strip()
        if key == "description":
            value = (value or "").strip() or None
        setattr(report, key, value)
    report.updated_by = ctx.user_id
    await db.flush()
    await db.refresh(report)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "report.update", "report_template", report.id, {"name": report.name, "changed": sorted(data)})
    return report


@router.post("/{report_id}/duplicate", response_model=ReportTemplateRead, status_code=status.HTTP_201_CREATED)
async def duplicate_report(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(_designer("create")),
    db: AsyncSession = Depends(get_db),
) -> ReportTemplate:
    source = await _get(db, ctx.tenant_id, report_id)
    if ctx.vendor_id is not None:
        # A Portal user copies a report they can see into one of their own —
        # which, being theirs, may read only the sources their role grants.
        await _require_run(db, ctx, source)
        await _check_portal_sources(db, ctx, source.sections)
    copy = ReportTemplate(
        tenant_id=ctx.tenant_id,
        name=f"{source.name} (copy)"[:200],
        description=source.description,
        parameters=source.parameters,
        sections=source.sections,
        settings=source.settings,
        role_ids=source.role_ids if ctx.vendor_id is None else [],
        vendor_id=ctx.vendor_id,
        created_by=ctx.user_id,
        updated_by=ctx.user_id,
    )
    db.add(copy)
    await db.flush()
    await db.refresh(copy)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "report.create", "report_template", copy.id, {"name": copy.name, "copied_from": str(source.id)})
    return copy


@router.delete("/{report_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_report(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(_designer("delete")),
    db: AsyncSession = Depends(get_db),
) -> None:
    report = await _get(db, ctx.tenant_id, report_id)
    if ctx.vendor_id is not None and not _owns(ctx, report):
        raise HTTPException(status_code=404, detail="Report not found.")
    await db.delete(report)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "report.delete", "report_template", report.id, {"name": report.name})


# ---- Running ----------------------------------------------------------------------------------------


@router.post("/preview", response_model=ReportRunResult)
async def preview_report(
    payload: ReportPreviewRequest,
    ctx: AuthContext = Depends(_designer("view")),
    db: AsyncSession = Depends(get_db),
) -> dict:
    """Runs the design on screen, unsaved."""
    template = payload.model_dump(mode="json", exclude={"params", "overrides"})
    await _check_portal_sources(db, ctx, template["sections"])
    return await _run(db, ctx, template, payload.params, _overrides(payload))


@router.post("/{report_id}/run", response_model=ReportRunResult)
async def run_saved_report(
    report_id: uuid.UUID,
    payload: ReportRunRequest,
    ctx: AuthContext = Depends(_viewer),
    db: AsyncSession = Depends(get_db),
) -> dict:
    report = await _get(db, ctx.tenant_id, report_id)
    await _require_run(db, ctx, report)
    return await _run(db, ctx, _template_dict(report), payload.params, _overrides(payload), granted=ctx.vendor_id is not None and not _owns(ctx, report))


def _filename(name: str, ext: str) -> str:
    return f"{re.sub(r'[^A-Za-z0-9_-]+', '_', name).strip('_') or 'report'}.{ext}"


def _cell(value: object) -> object:
    if isinstance(value, (dict, list)):
        return ", ".join(map(str, value)) if isinstance(value, list) else str(value)
    return value


def _in_zone(result: dict, tz) -> dict:
    """The run's times in the tenant's time zone — its rows' timestamps and
    when it was generated."""
    sections = [{**s, "rows": localize_rows(s["rows"], tz)} if s.get("rows") else s for s in result["sections"]]
    generated = result.get("generated_at")
    return {**result, "sections": sections, "generated_at": generated.astimezone(tz) if generated else generated}


def _label(section: dict, column: str) -> str:
    return (section.get("column_labels") or {}).get(column) or column


def _params_line(result: dict) -> str:
    return ", ".join(f"{k}: {v}" for k, v in result["params"].items() if v not in (None, ""))


def _csv(result: dict) -> io.BytesIO:
    """Every table section one after another, each under its title — the
    CSV a spreadsheet or another system can take in."""
    from app.routers.datasets import _csv_text

    out = io.StringIO()
    writer = csv.writer(out)
    writer.writerow([result["name"]])
    for k, v in result["params"].items():
        if v not in (None, ""):
            writer.writerow([k, _csv_text(v)])
    for i, s in enumerate(result["sections"], 1):
        title = s.get("title") or f"Section {i}"
        if s["kind"] == "text":
            continue
        writer.writerow([])
        if s["kind"] == "kpi":
            writer.writerow([title, "" if s.get("error") else _csv_text(s.get("value"))])
            continue
        writer.writerow([title])
        if s.get("error"):
            writer.writerow([s["error"]])
            continue
        writer.writerow([_label(s, c) for c in s["columns"]])
        for row in s["rows"]:
            writer.writerow([_csv_text(_cell(row.get(c))) for c in s["columns"]])
        if s.get("totals"):
            writer.writerow(["Total" if j == 0 and c not in s["totals"] else _csv_text(s["totals"].get(c)) for j, c in enumerate(s["columns"])])
    return io.BytesIO(("\ufeff" + out.getvalue()).encode("utf-8"))


def _xlsx(result: dict) -> io.BytesIO:
    wb = Workbook()
    summary = wb.active
    summary.title = "Summary"
    summary.append([result["name"]])
    summary["A1"].font = Font(bold=True, size=14)
    if result.get("subtitle"):
        summary.append([result["subtitle"]])
    if result.get("generated_at"):
        summary.append(["Generated", result["generated_at"].strftime("%Y-%m-%d %H:%M %Z")])
    for k, v in result["params"].items():
        if v not in (None, ""):
            summary.append([k, v])
    used: set[str] = {"Summary"}
    for i, s in enumerate(result["sections"], 1):
        title = s.get("title") or f"Section {i}"
        if s["kind"] == "text":
            if s.get("text"):
                summary.append([])
                summary.append([s["text"]])
            continue
        if s["kind"] == "kpi":
            summary.append([title, "—" if s.get("error") else s.get("value")])
            continue
        name = re.sub(r"[\[\]:*?/\\]", " ", title)[:28].strip() or f"Section {i}"
        while name in used:
            name = f"{name[:25]} {i}"
        used.add(name)
        ws = wb.create_sheet(name)
        if s.get("error"):
            ws.append([s["error"]])
            continue
        ws.append([_label(s, c) for c in s["columns"]])
        for cell in ws[1]:
            cell.font = Font(bold=True)
        for row in s["rows"]:
            ws.append([_cell(row.get(c)) for c in s["columns"]])
        if s.get("totals"):
            ws.append(["Total" if j == 0 and c not in s["totals"] else s["totals"].get(c) for j, c in enumerate(s["columns"])])
            for cell in ws[ws.max_row]:
                cell.font = Font(bold=True)
    buffer = io.BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    return buffer


def _pdf(result: dict, orientation: str) -> io.BytesIO:
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=landscape(A4) if orientation == "landscape" else A4, title=result["name"], leftMargin=36, rightMargin=36, topMargin=36, bottomMargin=36)
    styles = getSampleStyleSheet()
    cell = styles["BodyText"].clone("cell", fontSize=8, leading=10)
    head = cell.clone("head", textColor=colors.white)
    story: list = [Paragraph(escape(result["name"]), styles["Title"])]
    if result.get("subtitle"):
        story.append(Paragraph(escape(result["subtitle"]), styles["Heading3"]))
    meta = " · ".join(x for x in [_params_line(result), result["generated_at"].strftime("Generated %Y-%m-%d %H:%M %Z") if result.get("generated_at") else ""] if x)
    if meta:
        story.append(Paragraph(escape(meta), styles["Italic"]))
    for s in result["sections"]:
        story.append(Spacer(1, 12))
        if s.get("title"):
            story.append(Paragraph(escape(s["title"]), styles["Heading2"]))
        if s["kind"] == "text":
            for para in (s.get("text") or "").split("\n\n"):
                story.append(Paragraph(escape(para).replace("\n", "<br/>"), styles["BodyText"]))
            continue
        if s.get("error"):
            story.append(Paragraph(escape(f"Couldn't show this section: {s['error']}"), styles["Italic"]))
            continue
        if s["kind"] == "kpi":
            story.append(Paragraph(f"<font size=20><b>{escape(str(s.get('value') if s.get('value') is not None else '—'))}</b></font>", styles["BodyText"]))
            continue
        cols = s["columns"]
        if not cols:
            story.append(Paragraph("No data.", styles["Italic"]))
            continue
        data = [[Paragraph(f"<b>{escape(_label(s, c))}</b>", head) for c in cols]]
        data += [[Paragraph(escape("" if r.get(c) is None else str(_cell(r.get(c)))), cell) for c in cols] for r in s["rows"]]
        if s.get("totals"):
            data.append([Paragraph(f"<b>{escape(str(s['totals'].get(c, 'Total' if j == 0 else '')))}</b>", cell) for j, c in enumerate(cols)])
        table = Table(data, repeatRows=1, colWidths=[doc.width / len(cols)] * len(cols))
        table.setStyle(
            TableStyle(
                [
                    ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#1e293b")),
                    ("GRID", (0, 0), (-1, -1), 0.5, colors.HexColor("#cbd5e1")),
                    ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#f8fafc")]),
                    ("VALIGN", (0, 0), (-1, -1), "TOP"),
                ]
            )
        )
        story.append(table)
        if s.get("total", 0) > len(s["rows"]):
            story.append(Paragraph(f"Showing {len(s['rows'])} of {s['total']} rows.", styles["Italic"]))
    doc.build(story)
    buffer.seek(0)
    return buffer


@router.post("/{report_id}/export.{fmt}")
async def export_report(
    report_id: uuid.UUID,
    fmt: str,
    payload: ReportRunRequest,
    ctx: AuthContext = Depends(_viewer),
    db: AsyncSession = Depends(get_db),
) -> StreamingResponse:
    if fmt not in ("xlsx", "pdf", "csv"):
        raise HTTPException(status_code=404, detail="Export as xlsx, pdf or csv.")
    report = await _get(db, ctx.tenant_id, report_id)
    await _require_run(db, ctx, report)
    # A granted report's export is the role's to allow (Portal Roles); one's own always is.
    if ctx.vendor_id is not None and not _owns(ctx, report):
        await check_portal_action(db, ctx, "report_templates", report.id, "export")
    result = await _run(db, ctx, _template_dict(report), payload.params, _overrides(payload), granted=ctx.vendor_id is not None and not _owns(ctx, report))
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "report.export", "report_template", report.id, {"name": report.name, "format": fmt})
    result = _in_zone(result, await tenant_zone(db, ctx.tenant_id))
    if fmt == "xlsx":
        body = await asyncio.to_thread(_xlsx, result)
        media = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    elif fmt == "csv":
        body = _csv(result)
        media = "text/csv; charset=utf-8"
    else:
        body = await asyncio.to_thread(_pdf, result, (report.settings or {}).get("orientation", "landscape"))
        media = "application/pdf"
    stamp = datetime.now().strftime("%Y%m%d")
    return StreamingResponse(body, media_type=media, headers={"Content-Disposition": f'attachment; filename="{_filename(f"{report.name}_{stamp}", fmt)}"'})