# #region TranslateRunListRoutesModule [C:3] [TYPE Module] [SEMANTICS fastapi, translate, api, download, search]
# @BRIEF Translation Run listing, detail, and CSV download routes (cross-job).
# @LAYER API


from fastapi import Depends, HTTPException, Query, status
from sqlalchemy.orm import Session

from ....core.config_manager import ConfigManager
from ....core.database import get_db
from ....core.logger import logger
from ....dependencies import get_config_manager, get_current_user, has_permission
from ....plugins.translate.events import TranslationEventLog
from ....plugins.translate.orchestrator import TranslationOrchestrator
from ....schemas.auth import User
from ._router import router

# ============================================================
# List Runs (cross-job)
# ============================================================

# #region list_runs [C:4] [TYPE Function]
# @BRIEF List all runs with cross-job filtering and pagination.
# @PRE  User has translate.history.view permission.
# @POST  Returns paginated list of runs.
@router.get("/runs")
async def list_runs(
    job_id: str | None = Query(None),
    run_status: str | None = Query(None),
    trigger_type: str | None = Query(None),
    created_by: str | None = Query(None),
    date_from: str | None = Query(None),
    date_to: str | None = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_user),
    _ = Depends(has_permission("translate.history", "VIEW")),
    db: Session = Depends(get_db),
):
    """List all runs with cross-job filtering and pagination."""
    logger.reason(f"list_runs — User: {current_user.username}", extra={"src": "translate_routes"})
    try:
        from ....models.translate import TranslationRun
        query = db.query(TranslationRun)

        if job_id:
            query = query.filter(TranslationRun.job_id == job_id)
        if run_status:
            query = query.filter(TranslationRun.status == run_status)
        if trigger_type:
            query = query.filter(TranslationRun.trigger_type == trigger_type)
        if created_by:
            query = query.filter(TranslationRun.created_by == created_by)
        if date_from:
            try:
                from datetime import datetime
                dt_from = datetime.fromisoformat(date_from)
                query = query.filter(TranslationRun.created_at >= dt_from)
            except ValueError:
                logger.debug("Could not parse date_from: %s", date_from)
        if date_to:
            try:
                from datetime import datetime
                dt_to = datetime.fromisoformat(date_to)
                query = query.filter(TranslationRun.created_at <= dt_to)
            except ValueError:
                logger.debug("Could not parse date_to: %s", date_to)

        total = query.count()
        runs = (
            query.order_by(TranslationRun.created_at.desc())
            .offset((page - 1) * page_size)
            .limit(page_size)
            .all()
        )

        items = []
        for r in runs:
            # Gather per-language statistics from the run's language_stats relationship
            lang_stats = r.language_stats or []
            target_languages = [ls.language_code for ls in lang_stats]
            total_translated = sum((ls.translated_rows or 0) for ls in lang_stats)
            language_stats = {
                ls.language_code: {
                    "total_rows": ls.total_rows or 0,
                    "translated_rows": ls.translated_rows or 0,
                    "failed_rows": ls.failed_rows or 0,
                    "skipped_rows": ls.skipped_rows or 0,
                    "token_count": ls.token_count or 0,
                    "estimated_cost": ls.estimated_cost or 0.0,
                }
                for ls in lang_stats
            }

            items.append({
                "id": r.id,
                "job_id": r.job_id,
                "status": r.status,
                "trigger_type": r.trigger_type,
                "started_at": r.started_at.isoformat() if r.started_at else None,
                "completed_at": r.completed_at.isoformat() if r.completed_at else None,
                "error_message": r.error_message,
                "total_records": r.total_records or 0,
                "successful_records": r.successful_records or 0,
                "failed_records": r.failed_records or 0,
                "skipped_records": r.skipped_records or 0,
                "insert_status": r.insert_status,
                "superset_execution_id": r.superset_execution_id,
                "config_hash": r.config_hash,
                "dict_snapshot_hash": r.dict_snapshot_hash,
                "created_by": r.created_by,
                "created_at": r.created_at.isoformat() if r.created_at else None,
                "target_languages": target_languages,
                "total_translated": total_translated,
                "language_stats": language_stats,
            })

        return {"items": items, "total": total, "page": page, "page_size": page_size}
    except Exception as e:
        logger.explore("list_runs failed", extra={"src": "translate_routes", "error": str(e)})
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail=str(e))
# #endregion list_runs


# ============================================================
# Run Detail
# ============================================================

# #region get_run_detail [C:4] [TYPE Function]
# @BRIEF Get detailed run info with config_snapshot, records, events.
# @PRE  User has translate.history.view permission.
# @POST  Returns run detail with records and events.
@router.get("/runs/{run_id}/detail")
async def get_run_detail(
    run_id: str,
    current_user: User = Depends(get_current_user),
    _ = Depends(has_permission("translate.history", "VIEW")),
    db: Session = Depends(get_db),
    config_manager: ConfigManager = Depends(get_config_manager),
):
    """Get detailed run info with config_snapshot, records, events."""
    logger.reason(f"get_run_detail — Run: {run_id}, User: {current_user.username}", extra={"src": "translate_routes"})
    try:
        orch = TranslationOrchestrator(db, config_manager, current_user.username)
        status_info = orch.get_run_status(run_id)
        records = orch.get_run_records(run_id, page=1, page_size=50)
        events = TranslationEventLog(db).query_events(run_id=run_id)
        event_summary = TranslationEventLog(db).get_run_event_summary(run_id)

        # Get config snapshot from run
        from ....models.translate import TranslationRun
        run = db.query(TranslationRun).filter(TranslationRun.id == run_id).first()
        config_snapshot = run.config_snapshot if run else None

        return {
            **status_info,
            "config_snapshot": config_snapshot,
            "config_hash": run.config_hash if run else None,
            "records": records,
            "events": events,
            "event_invariants": event_summary,
        }
    except ValueError as e:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=str(e))
# #endregion get_run_detail


# ============================================================
# Download Skipped CSV
# ============================================================

# #region download_skipped_csv [C:4] [TYPE Function]
# @BRIEF Download a CSV of skipped translation records from a run.
# @PRE User has translate.job.view permission.
# @POST Returns CSV file of skipped records.
# @COMPLEXITY 4

@router.get("/runs/{run_id}/skipped.csv")
async def download_skipped_csv(
    run_id: str,
    current_user: User = Depends(get_current_user),
    _ = Depends(has_permission("translate.job", "VIEW")),
    db: Session = Depends(get_db),
):
    """Download skipped translation records as CSV."""
    logger.reason(f"download_skipped_csv — Run: {run_id}, User: {current_user.username}", extra={"src": "translate_routes"})
    try:
        import csv
        import io

        from fastapi.responses import StreamingResponse

        from ....models.translate import TranslationRecord

        records = (
            db.query(TranslationRecord)
            .filter(
                TranslationRecord.run_id == run_id,
                TranslationRecord.status == "SKIPPED",
            )
            .all()
        )

        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow(["id", "source_sql", "target_sql", "source_object_type",
                         "source_object_id", "source_object_name", "error_message"])
        for rec in records:
            writer.writerow([
                rec.id, rec.source_sql, rec.target_sql, rec.source_object_type,
                rec.source_object_id, rec.source_object_name, rec.error_message,
            ])

        output.seek(0)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename=skipped_{run_id}.csv"},
        )
    except Exception as e:
        logger.explore("download_skipped_csv failed", extra={"src": "translate_routes", "error": str(e)})
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail=str(e))
# #endregion download_skipped_csv


# ============================================================
# Download Failed CSV (uninserted records from failed runs)
# ============================================================

# #region download_failed_csv [C:4] [TYPE Function]
# @BRIEF Download a CSV of successfully translated but uninserted records from a FAILED run.
# @PRE User has translate.job.view permission. Run must have insert_status=failed or status=FAILED.
# @POST Returns CSV file of records that were translated but not inserted into target DB.
# @RATIONALE When Superset SQL Lab INSERT fails (e.g., permission denied), users need to
#           recover the translated data. This CSV lets them manually import or retry.
# @REJECTED Exporting ALL records from a failed run was rejected — only SUCCESS records
#           (those with valid translations) are useful for recovery.

@router.get("/runs/{run_id}/failed.csv")
async def download_failed_csv(
    run_id: str,
    current_user: User = Depends(get_current_user),
    _ = Depends(has_permission("translate.job", "VIEW")),
    db: Session = Depends(get_db),
):
    """Download successfully translated but uninserted records as CSV (for failed runs)."""
    logger.reason(f"download_failed_csv — Run: {run_id}, User: {current_user.username}",
                  extra={"src": "translate_routes"})
    try:
        import csv
        import io
        import json

        from fastapi.responses import StreamingResponse
        from sqlalchemy.orm import joinedload

        from ....models.translate import TranslationRecord, TranslationRun

        run = db.query(TranslationRun).filter(TranslationRun.id == run_id).first()
        if not run:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Run not found")

        # Export only SUCCESS records — these are the ones that were translated
        # but could not be inserted into the target database.
        records = (
            db.query(TranslationRecord)
            .options(joinedload(TranslationRecord.languages))
            .filter(
                TranslationRecord.run_id == run_id,
                TranslationRecord.status == "SUCCESS",
            )
            .order_by(TranslationRecord.created_at)
            .all()
        )

        if not records:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="No successful records found for this run",
            )

        # Collect all language codes across records for CSV header
        language_codes: list[str] = []
        seen_codes: set[str] = set()
        for rec in records:
            for lang in (rec.languages or []):
                if lang.language_code and lang.language_code not in seen_codes:
                    language_codes.append(lang.language_code)
                    seen_codes.add(lang.language_code)

        output = io.StringIO()
        writer = csv.writer(output)

        # Header: identity + source + target + per-language + metadata
        header = [
            "record_id", "source_object_type", "source_object_id",
            "source_object_name", "source_sql", "target_sql",
            "source_data",
        ]
        header.extend(language_codes)
        header.append("error_message")
        writer.writerow(header)

        for rec in records:
            # Build per-language values map
            lang_values: dict[str, str] = {}
            for lang in (rec.languages or []):
                if lang.language_code:
                    lang_values[lang.language_code] = lang.final_value or lang.translated_value or ""

            # Flatten source_data JSON to string for CSV compatibility
            source_data_str = ""
            if rec.source_data:
                try:
                    source_data_str = json.dumps(rec.source_data, ensure_ascii=False)
                except (TypeError, ValueError):
                    source_data_str = str(rec.source_data)

            row = [
                rec.id,
                rec.source_object_type,
                rec.source_object_id,
                rec.source_object_name,
                rec.source_sql,
                rec.target_sql,
                source_data_str,
            ]
            row.extend(lang_values.get(code, "") for code in language_codes)
            row.append(rec.error_message or "")
            writer.writerow(row)

        # Append run-level failure metadata as a comment row
        writer.writerow([])
        writer.writerow(["# Run failure metadata:"])
        writer.writerow(["run_id", run.id])
        writer.writerow(["run_status", run.status])
        writer.writerow(["insert_status", run.insert_status or ""])
        writer.writerow(["run_error", run.error_message or ""])
        writer.writerow(["total_records", run.total_records or 0])
        writer.writerow(["successful_records", run.successful_records or 0])

        output.seek(0)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={
                "Content-Disposition": f"attachment; filename=failed_{run_id[:8]}.csv"
            },
        )
    except HTTPException:
        raise
    except Exception as e:
        logger.explore("download_failed_csv failed",
                       extra={"src": "translate_routes", "error": str(e)})
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail=str(e))
# #endregion download_failed_csv

# #endregion TranslateRunListRoutesModule
