from __future__ import annotations

from datetime import datetime
from pathlib import Path
from typing import Annotated

from fastapi import APIRouter, Depends, File, Form, HTTPException, UploadFile
from sqlmodel import Session, select

from app.api.deps import get_session
from app.models.core import (
    ActivityEvent,
    AnalyticsDailyAggregate,
    AnalyticsImportBatch,
    AnalyticsImportFile,
    AnalyticsPostSnapshot,
    CreativeChange,
    ContentScript,
    ExternalPost,
    PostDraft,
    Theme,
    VideoAsset,
    WebsiteCompanion,
)
from app.services.analytics_service import analytics_insights, analytics_overview, import_youtube_csv_files, latest_snapshots
from app.services.analytics_mapping import auto_link_high_confidence as run_auto_link_high_confidence, high_confidence_preview, mapping_candidates_for
from app.services.analytics_youtube_csv import YOUTUBE_CHART, YOUTUBE_DAILY_TOTALS, YOUTUBE_VIDEO_TABLE, file_hash, parse_csv_by_type

router = APIRouter(prefix="/analytics", tags=["analytics"])


@router.post("/import/youtube-csv")
def import_youtube_csv(
    files: Annotated[list[UploadFile], File(alias="files")],
    provider: str = Form("youtube"),
    account_id: str | None = Form(None),
    snapshot_at: datetime | None = Form(None),
    source_label: str = Form("manual"),
    is_demo: bool = Form(False),
    import_batch_label: str | None = Form(None),
    session: Session = Depends(get_session),
):
    payload = []
    for upload in files:
        payload.append((upload.filename or "analytics.csv", upload.file.read()))
    if not payload:
        raise HTTPException(status_code=400, detail="No files supplied")
    return import_youtube_csv_files(session, payload, provider=provider, account_id=account_id, snapshot_at=snapshot_at, source_label=source_label, is_demo=is_demo, import_batch_label=import_batch_label)


@router.post("/import-csv")
def legacy_import_csv(
    file: UploadFile = File(...),
    provider: str = "youtube",
    session: Session = Depends(get_session),
):
    return import_youtube_csv_files(
        session,
        [(file.filename or "analytics.csv", file.file.read())],
        provider=provider,
    )


YOUTUBE_TYPE_LABELS = {
    YOUTUBE_CHART: "Diagrammdaten",
    YOUTUBE_DAILY_TOTALS: "Gesamtwerte",
    YOUTUBE_VIDEO_TABLE: "Tabellendaten",
}
YOUTUBE_CANONICAL_NAMES = {
    "Diagrammdaten": "260606_Diagrammdaten.csv",
    "Gesamtwerte": "260606_Gesamtwerte.csv",
    "Tabellendaten": "260606_Tabellendaten.csv",
}


def _resolve_import_folder(folder: str) -> Path:
    root = Path(folder).expanduser()
    if not root.is_absolute():
        root = Path.cwd().parent / root if Path.cwd().name == "backend" else Path.cwd() / root
    return root.resolve()


def _csv_detection_status(root: Path) -> dict:
    files = []
    by_label: dict[str, list[dict]] = {}
    for path in sorted(root.glob("*.csv")) if root.exists() else []:
        content = path.read_bytes()
        parsed = parse_csv_by_type(content)
        label = YOUTUBE_TYPE_LABELS.get(parsed.detected_type, "Unknown")
        item = {
            "file_name": path.name,
            "path": str(path),
            "size": path.stat().st_size,
            "sha256": file_hash(content),
            "detected_type": parsed.detected_type,
            "label": label,
            "rows": parsed.row_count,
            "data_rows": len(parsed.rows),
            "warnings": list(parsed.warnings),
            "errors": list(parsed.errors),
            "canonical_name": YOUTUBE_CANONICAL_NAMES.get(label),
            "is_canonical": path.name == YOUTUBE_CANONICAL_NAMES.get(label),
        }
        files.append(item)
        if label != "Unknown":
            by_label.setdefault(label, []).append(item)
    recognized = {}
    warnings = []
    for label, items in by_label.items():
        items = sorted(items, key=lambda item: (item["is_canonical"], item["data_rows"], item["size"]), reverse=True)
        chosen = items[0]
        recognized[label] = chosen
        for duplicate in items[1:]:
            warnings.append(f"{duplicate['file_name']}: also detected as {label}; using {chosen['file_name']} for import")
    missing = [label for label in ["Diagrammdaten", "Gesamtwerte", "Tabellendaten"] if label not in recognized]
    return {"folder": str(root), "files": files, "recognized": recognized, "missing": missing, "warnings": warnings}


@router.get("/youtube-folder-status")
def youtube_folder_status(folder: str = "storage/imports/youtube/260606"):
    root = _resolve_import_folder(folder)
    if not root.exists():
        raise HTTPException(status_code=404, detail=f"Folder not found: {root}")
    return _csv_detection_status(root)


@router.post("/import/youtube-folder")
def import_youtube_folder(
    folder: str = "storage/imports/youtube/260606",
    source_label: str = "Youtube 260606",
    provider: str = "youtube",
    session: Session = Depends(get_session),
):
    root = _resolve_import_folder(folder)
    if not root.exists():
        raise HTTPException(status_code=404, detail=f"Folder not found: {root}")
    status = _csv_detection_status(root)
    recognized = status["recognized"]
    warnings = list(status["warnings"])
    if "Tabellendaten" not in recognized:
        raise HTTPException(status_code=400, detail={
            "message": "Tabellendaten CSV is required because it contains video-level analytics.",
            "folder": str(root),
            "recognized": recognized,
            "missing": status["missing"],
            "files": status["files"],
        })
    if "Diagrammdaten" not in recognized:
        warnings.append("Diagrammdaten.csv missing; chart-level CSV skipped. Video results can still be imported.")
    if "Gesamtwerte" not in recognized:
        warnings.append("Gesamtwerte.csv missing; daily views summary unavailable.")
    ordered_labels = ["Diagrammdaten", "Gesamtwerte", "Tabellendaten"]
    files = [(recognized[label]["file_name"], Path(recognized[label]["path"]).read_bytes()) for label in ordered_labels if label in recognized]
    result = import_youtube_csv_files(session, files, provider=provider, source_label=source_label, is_demo=False, import_batch_label=source_label)
    result["recognized_files"] = {label: recognized[label] for label in recognized}
    result["missing_files"] = status["missing"]
    result["warnings"] = [*warnings, *result.get("warnings", [])]
    if warnings:
        batch = session.get(AnalyticsImportBatch, result["batch"]["id"])
        if batch:
            batch.warnings_json = [*warnings, *(batch.warnings_json or [])]
            batch.status = "imported_with_warnings" if batch.status == "imported" else batch.status
            session.add(batch)
            session.add(ActivityEvent(entity_type="analytics_import", entity_id=batch.id, event_type="csv_canonical_import", label="YouTube CSV import with content detection", payload_json={"folder": str(root), "recognized": result["recognized_files"], "missing": result["missing_files"], "warnings": result["warnings"]}))
            session.commit()
            session.refresh(batch)
            result["batch"] = batch.model_dump(mode="json")
    return result


@router.get("/imports")
def list_imports(session: Session = Depends(get_session)):
    batches = session.exec(select(AnalyticsImportBatch).order_by(AnalyticsImportBatch.imported_at.desc())).all()
    files = session.exec(select(AnalyticsImportFile)).all()
    by_batch: dict[str, list[dict]] = {}
    for item in files:
        by_batch.setdefault(item.batch_id, []).append(item.model_dump(mode="json"))
    return {"items": [{**batch.model_dump(mode="json"), "files": by_batch.get(batch.id, [])} for batch in batches]}


@router.get("/imports/{batch_id}")
def get_import(batch_id: str, session: Session = Depends(get_session)):
    batch = session.get(AnalyticsImportBatch, batch_id)
    if not batch:
        raise HTTPException(status_code=404, detail="Import not found")
    files = session.exec(select(AnalyticsImportFile).where(AnalyticsImportFile.batch_id == batch_id)).all()
    daily = session.exec(select(AnalyticsDailyAggregate).where(AnalyticsDailyAggregate.batch_id == batch_id).order_by(AnalyticsDailyAggregate.metric_date)).all()
    snapshots = session.exec(select(AnalyticsPostSnapshot).where(AnalyticsPostSnapshot.batch_id == batch_id).order_by(AnalyticsPostSnapshot.views.desc())).all()
    return {
        "batch": batch.model_dump(mode="json"),
        "files": [f.model_dump(mode="json") for f in files],
        "daily": [d.model_dump(mode="json") for d in daily],
        "snapshots": [s.model_dump(mode="json") for s in snapshots],
    }


@router.delete("/imports/{batch_id}")
def delete_import(batch_id: str, session: Session = Depends(get_session)):
    batch = session.get(AnalyticsImportBatch, batch_id)
    if not batch:
        raise HTTPException(status_code=404, detail="Import not found")
    for model in [AnalyticsImportFile, AnalyticsDailyAggregate, AnalyticsPostSnapshot]:
        for item in session.exec(select(model).where(model.batch_id == batch_id)).all():
            session.delete(item)
    session.delete(batch)
    session.commit()
    return {"ok": True}


@router.get("/overview")
def overview(session: Session = Depends(get_session)):
    data = analytics_overview(session)
    data["insights"] = analytics_insights(session)
    return data


@router.get("/daily")
def daily(session: Session = Depends(get_session)):
    rows = session.exec(select(AnalyticsDailyAggregate).order_by(AnalyticsDailyAggregate.metric_date)).all()
    return {"items": [r.model_dump(mode="json") for r in rows]}


@router.get("/videos")
def videos(session: Session = Depends(get_session), sort: str = "views"):
    items = [s.model_dump(mode="json") for s in latest_snapshots(session)]
    items.sort(key=lambda x: x.get(sort) or 0, reverse=True)
    return {"items": items}


@router.get("/videos/{external_post_id}")
def video_detail(external_post_id: str, session: Session = Depends(get_session)):
    post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
    snapshots = session.exec(
        select(AnalyticsPostSnapshot)
        .where(AnalyticsPostSnapshot.external_post_id == external_post_id)
        .order_by(AnalyticsPostSnapshot.snapshot_at)
    ).all()
    if not post and not snapshots:
        raise HTTPException(status_code=404, detail="Analytics post not found")
    return {
        "post": post.model_dump(mode="json") if post else None,
        "snapshots": [s.model_dump(mode="json") for s in snapshots],
    }


@router.get("/topics")
def topics(session: Session = Depends(get_session)):
    posts = session.exec(select(ExternalPost)).all()
    snapshots = latest_snapshots(session)
    by_external = {s.external_post_id: s for s in snapshots}
    grouped: dict[str, list[AnalyticsPostSnapshot]] = {}
    for post in posts:
        if post.topic_id and post.external_post_id in by_external:
            grouped.setdefault(post.topic_id, []).append(by_external[post.external_post_id])
    items = []
    for topic_id, rows in grouped.items():
        topic = session.get(Theme, topic_id)
        views = sum(r.views or 0 for r in rows)
        retention_values = [r.retention_proxy_pct for r in rows if r.retention_proxy_pct is not None]
        subs_values = [r.subscribers_per_1000_views for r in rows if r.subscribers_per_1000_views is not None]
        items.append({
            "topic_id": topic_id,
            "title": topic.title if topic else topic_id,
            "video_count": len(rows),
            "views": views,
            "avg_retention_proxy": sum(retention_values) / len(retention_values) if retention_values else None,
            "subscribers_per_1000_views": sum(subs_values) / len(subs_values) if subs_values else None,
            "best_title": max(rows, key=lambda r: r.views or 0).title if rows else None,
        })
    return {"items": sorted(items, key=lambda item: item["views"], reverse=True)}


@router.get("/insights")
def insights(session: Session = Depends(get_session)):
    return {"items": analytics_insights(session)}


CHANGE_LABELS = {
    "title": "Title changed",
    "caption": "Caption changed",
    "description": "Description changed",
    "hashtags": "Tags changed",
    "schedule": "Schedule changed",
    "topic": "Topic changed",
    "thumbnail": "Thumbnail changed",
    "hook": "Hook changed",
    "opening": "Opening changed",
    "cta": "CTA changed",
    "other": "Change noted",
}


def _change_label(change_type: str | None) -> str:
    return CHANGE_LABELS.get(change_type or "other", "Change noted")


def _delta_label(delta: int | None, trend: str | None) -> str:
    if delta is None:
        return "new"
    if delta > 0:
        return f"+{delta} since last import"
    if delta == 0:
        return "no change since last import"
    return f"{delta} since last import"


def _next_action(snapshot: AnalyticsPostSnapshot, latest_change: CreativeChange | None) -> str:
    views = snapshot.views or 0
    retention = snapshot.retention_proxy_pct or 0
    subs = snapshot.subscribers_delta or 0
    if subs > 0:
        return "Make follow-up"
    if views < 50 and retention >= 10:
        return "Improve title"
    if views < 50:
        return "Check hook"
    if latest_change:
        return "Watch next import"
    return "Review result"


def _post_for_snapshot(session: Session, snapshot: AnalyticsPostSnapshot) -> ExternalPost | None:
    return session.exec(select(ExternalPost).where(ExternalPost.external_post_id == snapshot.external_post_id)).first()


def _topic_title(session: Session, topic_id: str | None) -> str | None:
    if not topic_id:
        return None
    topic = session.get(Theme, topic_id)
    return topic.title if topic else None


def _display_name(session: Session, snapshot: AnalyticsPostSnapshot, post: ExternalPost | None, video: VideoAsset | None) -> tuple[str, str | None]:
    youtube_title = None
    if video:
        draft = session.exec(select(PostDraft).where(PostDraft.video_asset_id == video.id, PostDraft.provider == "youtube")).first()
        youtube_title = draft.title if draft else None
    if video and video.working_title:
        return video.working_title, youtube_title or snapshot.title or (post.title if post else None)
    if youtube_title:
        return youtube_title, snapshot.title or (post.title if post else None)
    if post and post.title:
        return post.title.strip(), f"YouTube ID: {post.external_post_id}"
    if snapshot.title:
        return snapshot.title.strip(), f"YouTube ID: {snapshot.external_post_id}"
    return "Untitled YouTube video", f"YouTube ID: {snapshot.external_post_id}"


def _latest_change_for(session: Session, video_id: str | None, post: ExternalPost | None) -> CreativeChange | None:
    query = select(CreativeChange).order_by(CreativeChange.changed_at.desc())
    if video_id:
        found = session.exec(query.where(CreativeChange.video_asset_id == video_id)).first()
        if found:
            return found
    if post:
        return session.exec(query.where(CreativeChange.external_post_id == post.id)).first()
    return None


def _result_item(session: Session, snapshot: AnalyticsPostSnapshot) -> dict:
    post = _post_for_snapshot(session, snapshot)
    video = session.get(VideoAsset, snapshot.video_asset_id) if snapshot.video_asset_id else None
    latest_change = _latest_change_for(session, snapshot.video_asset_id, post)
    topic_id = post.topic_id if post else None
    topic_id = topic_id or (video.topic_id if video else None)
    display_title, title_subline = _display_name(session, snapshot, post, video)
    script = session.exec(select(ContentScript).where(ContentScript.video_asset_id == video.id)).first() if video else None
    website = session.exec(select(WebsiteCompanion).where(WebsiteCompanion.video_asset_id == video.id)).first() if video else None
    suggested = None if video else (mapping_candidates_for(snapshot.title, snapshot.external_post_id, limit=1) or [None])[0]
    website_slug = website.slug if website else (suggested.get("website_slug") if suggested else None)
    website_status = website.status if website else (suggested.get("website_status") if suggested else "no guide")
    return {
        "external_post_id": snapshot.external_post_id,
        "title": display_title,
        "title_subline": title_subline,
        "youtube_title": (post.title if post else None) or snapshot.title,
        "youtube_id": snapshot.external_post_id,
        "thumbnail_url": f"/api/videos/{video.id}/thumbnail" if video else None,
        "video_asset_id": video.id if video else None,
        "package_id": video.package_id if video else None,
        "content_script_id": script.id if script else None,
        "script_title": script.title if script else None,
        "topic_title": _topic_title(session, topic_id),
        "website_slug": website_slug,
        "website_status": website_status,
        "website_url": f"https://truetraceshorts.pages.dev/redflags/{website_slug}/" if website_slug and website_status in {"pushed", "guide exists"} else None,
        "suggested_match": suggested,
        "views": snapshot.views or 0,
        "views_delta": snapshot.views_delta,
        "views_delta_label": _delta_label(snapshot.views_delta, snapshot.trend),
        "ctr": snapshot.impression_ctr_pct,
        "retention_proxy": snapshot.retention_proxy_pct,
        "subscribers_delta": snapshot.subscribers_delta,
        "published_at": snapshot.published_at.isoformat() if snapshot.published_at else None,
        "trend": snapshot.trend or "new",
        "linked": bool(video),
        "latest_change": {
            "id": latest_change.id,
            "type": latest_change.change_type,
            "label": _change_label(latest_change.change_type),
            "changed_at": latest_change.changed_at.isoformat(),
        } if latest_change else None,
        "next_action": _next_action(snapshot, latest_change),
        "source_label": snapshot.source_label,
        "import_batch_label": snapshot.import_batch_label,
        "is_demo": snapshot.is_demo,
    }


@router.get("/simple")
def simple(session: Session = Depends(get_session)):
    results = [_result_item(session, s) for s in latest_snapshots(session)]
    total_views = sum(item["views"] for item in results)
    subscriber_delta = sum(item.get("subscribers_delta") or 0 for item in results)
    best = max(results, key=lambda item: item["views"], default=None)
    improvements = improvements_payload(session)["items"]
    biggest = max([i for i in improvements if i.get("delta") is not None], key=lambda item: item.get("delta") or 0, default=None)
    preview = high_confidence_preview(session)
    return {
        "kpis": {
            "total_views": total_views,
            "best_video": best["title"] if best else None,
            "biggest_improvement": biggest["summary"] if biggest else "No improvement measured yet",
            "subscriber_delta": subscriber_delta,
            "unlinked_count": len([item for item in results if not item["linked"]]),
        },
        "video_count": len(results),
        "mapping": {
            "analytics_videos": len(results),
            "linked": len([item for item in results if item["linked"]]),
            "unlinked": len([item for item in results if not item["linked"]]),
            "strong_matches": preview["count"],
        },
    }


@router.get("/video-results")
def video_results(session: Session = Depends(get_session), linked: str | None = None):
    items = [_result_item(session, s) for s in latest_snapshots(session)]
    if linked == "linked":
        items = [item for item in items if item["linked"]]
    elif linked == "unlinked":
        items = [item for item in items if not item["linked"]]
    return {"items": sorted(items, key=lambda item: item["views"], reverse=True)}


@router.get("/video-results/{external_post_id}")
def simple_video_detail(external_post_id: str, session: Session = Depends(get_session)):
    post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
    snapshots = session.exec(
        select(AnalyticsPostSnapshot).where(AnalyticsPostSnapshot.external_post_id == external_post_id).order_by(AnalyticsPostSnapshot.snapshot_at)
    ).all()
    if not post and not snapshots:
        raise HTTPException(status_code=404, detail="Analytics post not found")
    latest = snapshots[-1] if snapshots else None
    video = session.get(VideoAsset, (post.video_asset_id if post else None) or (latest.video_asset_id if latest else None)) if (post or latest) else None
    changes = []
    if video:
        changes.extend(session.exec(select(CreativeChange).where(CreativeChange.video_asset_id == video.id).order_by(CreativeChange.changed_at.desc())).all())
    if post:
        changes.extend(session.exec(select(CreativeChange).where(CreativeChange.external_post_id == post.id).order_by(CreativeChange.changed_at.desc())).all())
    snapshot_items = []
    previous_views = None
    for index, snapshot in enumerate(snapshots, start=1):
        delta = snapshot.views - previous_views if previous_views is not None and snapshot.views is not None else None
        snapshot_items.append({
            "label": f"Import {index}",
            "snapshot_at": snapshot.snapshot_at.isoformat(),
            "views": snapshot.views or 0,
            "views_delta": delta,
        })
        previous_views = snapshot.views
    recommendation = "Import more snapshots to see a pattern."
    if latest:
        recommendation = _next_action(latest, changes[0] if changes else None)
        if latest.subscribers_delta and latest.subscribers_delta > 0:
            recommendation = "This video gained subscribers: make a follow-up with the same promise."
        elif (latest.views or 0) < 50 and (latest.retention_proxy_pct or 0) >= 10:
            recommendation = "This video has low views but good retention: improve packaging/title."
    return {
        "summary": _result_item(session, latest) if latest else None,
        "post": post.model_dump(mode="json") if post else None,
        "video": video.model_dump(mode="json") if video else None,
        "snapshots": snapshot_items,
        "metrics": latest.model_dump(mode="json") if latest else None,
        "changes": [{**c.model_dump(mode="json"), "label": _change_label(c.change_type)} for c in changes[:20]],
        "recommendation": recommendation,
    }


def improvements_payload(session: Session) -> dict:
    items = []
    changes = session.exec(select(CreativeChange).order_by(CreativeChange.changed_at.desc())).all()
    for change in changes:
        post = session.get(ExternalPost, change.external_post_id) if change.external_post_id else None
        snapshots = []
        if post:
            snapshots = session.exec(select(AnalyticsPostSnapshot).where(AnalyticsPostSnapshot.external_post_id == post.external_post_id).order_by(AnalyticsPostSnapshot.snapshot_at)).all()
        elif change.video_asset_id:
            snapshots = session.exec(select(AnalyticsPostSnapshot).where(AnalyticsPostSnapshot.video_asset_id == change.video_asset_id).order_by(AnalyticsPostSnapshot.snapshot_at)).all()
        before = next((s for s in reversed(snapshots) if s.snapshot_at <= change.changed_at), None)
        after = next((s for s in snapshots if s.snapshot_at > change.changed_at), None) or (snapshots[-1] if snapshots else None)
        video = session.get(VideoAsset, change.video_asset_id) if change.video_asset_id else None
        title = (after.title if after else None) or (post.title if post else None) or (video.working_title if video else None) or "Untitled video"
        before_metric = before.views if before else None
        after_metric = after.views if after else None
        delta = after_metric - before_metric if before_metric is not None and after_metric is not None else None
        delta_pct = (delta / before_metric * 100) if delta is not None and before_metric else None
        status = "measured" if delta is not None else "needs_more_snapshots"
        interpretation = "More imports needed before judging this change."
        if delta is not None and delta > 0:
            interpretation = f"Views increased after the {_change_label(change.change_type).lower()}."
        elif delta is not None and delta <= 0:
            interpretation = f"No clear improvement yet after the {_change_label(change.change_type).lower()}."
        items.append({
            "id": change.id,
            "video_title": title,
            "change_type": change.change_type,
            "change_label": _change_label(change.change_type),
            "before_metric": before_metric,
            "after_metric": after_metric,
            "metric": "views",
            "delta": delta,
            "delta_pct": delta_pct,
            "status": status,
            "summary": f"{_change_label(change.change_type)} · {title}",
            "interpretation": interpretation,
            "change": change.model_dump(mode="json"),
        })
    return {"items": items}


@router.get("/improvements")
def improvements(session: Session = Depends(get_session)):
    return improvements_payload(session)


@router.get("/auto-link-preview")
def auto_link_preview(session: Session = Depends(get_session)):
    return high_confidence_preview(session)


@router.post("/auto-link-high-confidence")
def auto_link_high_confidence(session: Session = Depends(get_session)):
    return run_auto_link_high_confidence(session)


@router.get("/unlinked-posts")
def unlinked_posts(session: Session = Depends(get_session)):
    snapshots = latest_snapshots(session)
    items = []
    for snapshot in snapshots:
        post = _post_for_snapshot(session, snapshot)
        if snapshot.video_asset_id or (post and post.video_asset_id) or (post and post.mapping_status == "ignored"):
            continue
        candidates = mapping_candidates_for(snapshot.title, snapshot.external_post_id, limit=5)
        items.append({
            "external_post_id": snapshot.external_post_id,
            "title": snapshot.title,
            "views": snapshot.views or 0,
            "published_at": snapshot.published_at.isoformat() if snapshot.published_at else None,
            "youtube_id": snapshot.external_post_id,
            "imported_from": snapshot.import_batch_label or snapshot.source_label,
            "source_file": snapshot.source_file_name or (post.source_file_name if post else None),
            "candidates": candidates[:5],
        })
    return {"items": items}


@router.get("/changes")
def list_changes(session: Session = Depends(get_session), video_asset_id: str | None = None, external_post_id: str | None = None):
    q = select(CreativeChange).order_by(CreativeChange.changed_at.desc())
    if video_asset_id:
        q = q.where(CreativeChange.video_asset_id == video_asset_id)
    if external_post_id:
        post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
        if post:
            q = q.where(CreativeChange.external_post_id == post.id)
    return {"items": [{**c.model_dump(mode="json"), "label": _change_label(c.change_type)} for c in session.exec(q).all()]}


@router.post("/changes")
def create_change(payload: dict, session: Session = Depends(get_session)):
    post = None
    if payload.get("external_post_id"):
        post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == payload.get("external_post_id"))).first()
    change = CreativeChange(
        video_asset_id=payload.get("video_asset_id"),
        external_post_id=post.id if post else None,
        post_draft_id=payload.get("post_draft_id"),
        topic_id=payload.get("topic_id"),
        change_type=payload.get("change_type") or "other",
        before_text=payload.get("before_text"),
        after_text=payload.get("after_text"),
        change_note=payload.get("change_note") or payload.get("what_changed"),
        hypothesis=payload.get("hypothesis") or payload.get("why"),
        created_by=payload.get("created_by") or "creator",
        source=payload.get("source") or "manual",
    )
    session.add(change)
    session.commit()
    session.refresh(change)
    if change.video_asset_id:
        session.add(ActivityEvent(entity_type="video", entity_id=change.video_asset_id, event_type="creative_change", label=_change_label(change.change_type), payload_json={"change_id": change.id}))
    if change.post_draft_id:
        session.add(ActivityEvent(entity_type="post_draft", entity_id=change.post_draft_id, event_type="creative_change", label=_change_label(change.change_type), payload_json={"change_id": change.id}))
    session.commit()
    return {"ok": True, "change": {**change.model_dump(mode="json"), "label": _change_label(change.change_type)}}


@router.post("/map-post")
def map_post(payload: dict, session: Session = Depends(get_session)):
    external_post_id = payload.get("external_post_id")
    video_asset_id = payload.get("video_asset_id")
    topic_id = payload.get("topic_id")
    post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
    if not post:
        raise HTTPException(status_code=404, detail="External post not found")
    if video_asset_id:
        video = session.get(VideoAsset, video_asset_id)
        if not video:
            raise HTTPException(status_code=404, detail="VideoAsset not found")
        post.video_asset_id = video.id
        post.topic_id = topic_id or video.topic_id
        post.series = video.series
        post.mapping_status = "linked"
    elif topic_id:
        post.topic_id = topic_id
        post.mapping_status = "family_only"
    session.add(post)
    session.add(ActivityEvent(entity_type="analytics_post", entity_id=post.id, event_type="linked", label="Analytics linked", payload_json={"external_post_id": external_post_id, "video_asset_id": video_asset_id, "topic_id": topic_id}))
    if video_asset_id:
        session.add(ActivityEvent(entity_type="video", entity_id=video_asset_id, event_type="analytics_linked", label="Analytics linked", payload_json={"external_post_id": external_post_id}))
    for snapshot in session.exec(select(AnalyticsPostSnapshot).where(AnalyticsPostSnapshot.external_post_id == external_post_id)).all():
        if video_asset_id:
            snapshot.video_asset_id = video_asset_id
        session.add(snapshot)
    session.commit()
    return {"ok": True, "post": post.model_dump(mode="json")}


@router.post("/map-post-family")
def map_post_family(payload: dict, session: Session = Depends(get_session)):
    external_post_id = payload.get("external_post_id")
    topic_id = payload.get("topic_id")
    if not external_post_id or not topic_id:
        raise HTTPException(status_code=400, detail="external_post_id and topic_id are required")
    post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
    if not post:
        raise HTTPException(status_code=404, detail="Analytics post not found")
    if not session.get(Theme, topic_id):
        raise HTTPException(status_code=404, detail="Content family not found")
    post.topic_id = topic_id
    post.mapping_status = "family_only"
    session.add(post)
    session.add(ActivityEvent(entity_type="analytics_post", entity_id=post.id, event_type="family_linked", label="Analytics linked to content family", payload_json={"external_post_id": external_post_id, "topic_id": topic_id}))
    session.commit()
    return {"ok": True, "post": {**post.model_dump(mode="json"), "mapping_status": post.mapping_status, "topic_id": post.topic_id}}


@router.post("/ignore-post")
def ignore_post(payload: dict, session: Session = Depends(get_session)):
    external_post_id = payload.get("external_post_id")
    reason = payload.get("reason") or "Ignored by creator"
    post = session.exec(select(ExternalPost).where(ExternalPost.external_post_id == external_post_id)).first()
    if not post:
        raise HTTPException(status_code=404, detail="Analytics post not found")
    post.mapping_status = "ignored"
    post.ignore_reason = reason
    post.ignored_at = datetime.now()
    session.add(post)
    session.add(ActivityEvent(entity_type="analytics_post", entity_id=post.id, event_type="ignored", label="Analytics mapping ignored", payload_json={"external_post_id": external_post_id, "reason": reason}))
    session.commit()
    return {"ok": True, "post": {**post.model_dump(mode="json"), "mapping_status": post.mapping_status, "ignore_reason": post.ignore_reason}}


@router.get("/ignored-posts")
def ignored_posts(session: Session = Depends(get_session)):
    posts = session.exec(select(ExternalPost).where(ExternalPost.mapping_status == "ignored").order_by(ExternalPost.updated_at.desc())).all()
    return {"items": [p.model_dump(mode="json") for p in posts]}


@router.post("/recompute")
def recompute(session: Session = Depends(get_session)):
    # Deltas are computed at import time. This endpoint is intentionally light for now.
    return {"ok": True, "message": "Analytics derived metrics are computed during import."}