"""Case report assembly. Builds a structured section model from an investigation, then

renders to Markdown / JSON / CSV. PDF uses the same section model (render hook left for a

print pipeline). Pure assembly from query results keeps it testable and format-agnostic."""

from __future__ import annotations

import csv

import io

import json

from datetime import datetime

from sqlalchemy import func, select

from sqlalchemy.orm import Session

from app.models import (

    Camera, EvidenceFile, Investigation, Note, PlateRead, Sighting, Vehicle,

    WatchlistHit, LicensePlate,

)

from app.services.analytics import movement_summary

REPORT_SECTIONS = [

    "executive_summary", "investigation_overview", "plate_vehicle_summary",

    "sighting_timeline", "movement_summary", "watchlist_hits",

    "key_findings", "evidence_appendix", "analyst_notes", "source_attribution",

]

def _reads_for_investigation(db: Session, inv_id: str) -> list[dict]:

    rows = db.execute(

        select(PlateRead.plate_norm, PlateRead.read_ts, PlateRead.camera_id,

               PlateRead.confidence, PlateRead.lat, PlateRead.lon)

        .join(Sighting, Sighting.plate_read_id == PlateRead.id)

        .where(Sighting.investigation_id == inv_id)

    ).all()

    return [{"plate_norm": r[0], "read_ts": r[1], "camera_id": r[2],

             "confidence": float(r[3]) if r[3] is not None else None,

             "lat": r[4], "lon": r[5]} for r in rows]

def build_report(db: Session, investigation_id: str, sections: list[str] | None = None) -> dict:

    inv = db.get(Investigation, investigation_id)

    if inv is None:

        raise ValueError("investigation not found")

    sections = sections or REPORT_SECTIONS

    reads = _reads_for_investigation(db, investigation_id)

    plates = sorted({r["plate_norm"] for r in reads})

    out: dict = {"title": f"Investigation Report - {inv.title}",

                 "generated_at": None, "investigation_id": inv.id, "sections": {}}

    if "executive_summary" in sections:

        out["sections"]["executive_summary"] = (

            f"Investigation '{inv.title}' (status: {inv.status}, priority: {inv.priority}) "

            f"covers {len(reads)} attached plate reads across {len(plates)} distinct plate(s)."

        )

    if "investigation_overview" in sections:

        out["sections"]["investigation_overview"] = {

            "title": inv.title, "status": inv.status, "priority": inv.priority,

            "summary": inv.summary, "organization": inv.organization,

        }

    if "plate_vehicle_summary" in sections:

        out["sections"]["plate_vehicle_summary"] = _plate_vehicle_summary(db, plates)

    if "sighting_timeline" in sections:

        out["sections"]["sighting_timeline"] = [

            {"plate_norm": r["plate_norm"], "read_ts": r["read_ts"].isoformat(),

             "camera_id": r["camera_id"]}

            for r in sorted(reads, key=lambda r: r["read_ts"])

        ]

    if "movement_summary" in sections:

        movements = {}

        by_plate: dict[str, list[dict]] = {}

        for r in reads:

            by_plate.setdefault(r["plate_norm"], []).append(r)

        for p, rs in by_plate.items():

            m = movement_summary(p, rs)

            movements[p] = {"total_reads": m.total_reads, "distinct_cameras": m.distinct_cameras,

                            "first_seen": m.first_seen.isoformat() if m.first_seen else None,

                            "last_seen": m.last_seen.isoformat() if m.last_seen else None,

                            "anomalies": m.anomalies}

        out["sections"]["movement_summary"] = movements

    if "watchlist_hits" in sections:

        hits = db.execute(

            select(WatchlistHit.matched_on, WatchlistHit.created_at, PlateRead.plate_norm)

            .join(PlateRead, PlateRead.id == WatchlistHit.plate_read_id)

            .join(Sighting, Sighting.plate_read_id == PlateRead.id)

            .where(Sighting.investigation_id == investigation_id)

        ).all()

        out["sections"]["watchlist_hits"] = [

            {"plate_norm": h[2], "matched_on": h[0], "at": h[1].isoformat() if h[1] else None}

            for h in hits

        ]

    if "evidence_appendix" in sections:

        ev = db.scalars(select(EvidenceFile).where(

            EvidenceFile.investigation_id == investigation_id)).all()

        out["sections"]["evidence_appendix"] = [

            {"filename": e.filename, "sha256": e.sha256, "size_bytes": e.size_bytes,

             "description": e.description} for e in ev

        ]

    if "analyst_notes" in sections:

        notes = db.scalars(select(Note).where(Note.investigation_id == investigation_id)).all()

        out["sections"]["analyst_notes"] = [{"body": n.body, "pinned": n.pinned} for n in notes]

    if "source_attribution" in sections:

        out["sections"]["source_attribution"] = _source_attribution(db, investigation_id)

    if "key_findings" in sections:

        out["sections"]["key_findings"] = _key_findings(reads)

    return out

def _plate_vehicle_summary(db: Session, plates: list[str]) -> list[dict]:

    summary = []

    for p in plates:

        plate = db.scalar(select(LicensePlate).where(LicensePlate.plate_norm == p))

        veh = db.scalar(select(Vehicle).where(Vehicle.primary_plate_id == plate.id)) if plate else None

        summary.append({

            "plate_norm": p, "state": plate.state if plate else None,

            "make": veh.make if veh else None, "model": veh.model if veh else None,

            "color": veh.color if veh else None, "body_type": veh.body_type if veh else None,

        })

    return summary

def _source_attribution(db: Session, inv_id: str) -> list[dict]:

    rows = db.execute(

        select(PlateRead.source_id, func.count())

        .join(Sighting, Sighting.plate_read_id == PlateRead.id)

        .where(Sighting.investigation_id == inv_id)

        .group_by(PlateRead.source_id)

    ).all()

    return [{"source_id": r[0], "read_count": r[1]} for r in rows]

def _key_findings(reads: list[dict]) -> list[str]:

    findings: list[str] = []

    by_plate: dict[str, list[dict]] = {}

    for r in reads:

        by_plate.setdefault(r["plate_norm"], []).append(r)

    for p, rs in by_plate.items():

        m = movement_summary(p, rs)

        if m.anomalies:

            findings.append(f"{p}: {len(m.anomalies)} impossible-travel anomaly(ies) - possible cloned/misread plate.")

        if m.dwell:

            longest = max(m.dwell, key=lambda d: d.duration_s)

            if longest.duration_s > 0:

                findings.append(f"{p}: longest dwell {int(longest.duration_s)}s at camera {longest.camera_id}.")

    return findings or ["No automated anomalies detected in attached sightings."]

def render_markdown(report: dict) -> str:

    lines = [f"# {report['title']}", ""]

    for key, content in report["sections"].items():

        lines.append(f"## {key.replace('_', ' ').title()}")

        if isinstance(content, str):

            lines.append(content)

        elif isinstance(content, list):

            for item in content:

                lines.append(f"- {json.dumps(item, default=str) if isinstance(item, dict) else item}")

        elif isinstance(content, dict):

            for k, v in content.items():

                lines.append(f"- **{k}**: {json.dumps(v, default=str) if isinstance(v, (dict, list)) else v}")

        lines.append("")

    return "\n".join(lines)

def render_json(report: dict) -> str:

    return json.dumps(report, indent=2, default=str)

def render_csv_timeline(report: dict) -> str:

    buf = io.StringIO()

    w = csv.writer(buf)

    w.writerow(["plate_norm", "read_ts", "camera_id"])

    for row in report["sections"].get("sighting_timeline", []):

        w.writerow([row["plate_norm"], row["read_ts"], row["camera_id"]])

    return buf.getvalue()