"""Sync and import endpoints."""

from __future__ import annotations

import logging
import time
from io import BytesIO

from fastapi import APIRouter, Request, HTTPException, UploadFile, File, Form
from pydantic import BaseModel

from database.local_pg import LocalPG
from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry
from sync.excel_import import ExcelImporter, list_sheets

logger = logging.getLogger(__name__)

router = APIRouter(tags=["sync"])


class SyncRequest(BaseModel):
    connection_id: str
    source_schema: str
    source_table: str
    local_table: str | None = None


@router.post("/sync")
async def sync_table(request: Request, body: SyncRequest):
    """Start a sync job (background). Returns the job immediately.

    The sync runs asynchronously — poll GET /sync/jobs/{id} for progress.
    """
    db = request.app.state.db
    settings = request.app.state.settings

    conn = await db.fetch_one(
        "SELECT * FROM v3q_config.database_connections WHERE id = :id",
        {"id": body.connection_id},
    )
    if not conn:
        raise HTTPException(404, "Connection not found")

    # Look up sync_filter from table_registry if it exists
    sync_filter = None
    table_reg = await db.fetch_one(
        """SELECT sync_filter FROM v3q_config.table_registry
           WHERE connection_id = :cid AND source_schema = :ss AND source_table = :st""",
        {"cid": body.connection_id, "ss": body.source_schema, "st": body.source_table},
    )
    if table_reg and table_reg.get("sync_filter"):
        sync_filter = table_reg["sync_filter"].strip()

    # Get or create the job manager
    job_mgr = _get_job_manager(request)

    # Create job record
    job = await job_mgr.create_job(
        connection_id=body.connection_id,
        connection_name=conn["name"],
        db_type=conn["db_type"],
        source_schema=body.source_schema,
        source_table=body.source_table,
        local_table=body.local_table,
    )

    logger.info(
        f"[API] Sync job created: {job['id']} for {conn['name']}.{body.source_schema}.{body.source_table} "
        f"(batch_size={settings.sync.batch_size:,}, filter={sync_filter!r})"
    )

    # Launch in background
    from local_llm.ollama_client import OllamaClient
    ollama = OllamaClient(settings.ollama)

    await job_mgr.start_job(
        job_id=str(job["id"]),
        connection_string=conn["connection_string"],
        db_type=conn["db_type"],
        source_schema=body.source_schema,
        source_table=body.source_table,
        local_table=body.local_table,
        connection_id=body.connection_id,
        source_system=conn.get("source_system"),
        batch_size=settings.sync.batch_size,
        ollama=ollama,
        sync_filter=sync_filter,
    )

    return {
        "job_id": str(job["id"]),
        "status": "pending",
        "message": f"Sync started for {body.source_schema}.{body.source_table}",
    }


@router.get("/sync/jobs")
async def list_sync_jobs(request: Request, limit: int = 50):
    """List all sync jobs (most recent first)."""
    job_mgr = _get_job_manager(request)
    jobs = await job_mgr.list_jobs(limit=limit)
    return {"jobs": jobs, "count": len(jobs)}


@router.get("/sync/jobs/{job_id}")
async def get_sync_job(request: Request, job_id: str):
    """Get a sync job's current status and progress."""
    job_mgr = _get_job_manager(request)
    job = await job_mgr.get_job(job_id)
    if not job:
        raise HTTPException(404, "Job not found")
    return job


@router.post("/sync/jobs/{job_id}/cancel")
async def cancel_sync_job(request: Request, job_id: str):
    """Cancel a running sync job."""
    job_mgr = _get_job_manager(request)
    cancelled = await job_mgr.cancel_job(job_id)
    if cancelled:
        return {"status": "cancelled", "job_id": job_id}
    raise HTTPException(400, "Job is not running or already finished")


def _get_job_manager(request: Request):
    """Get or create the SyncJobManager singleton on app state."""
    if not hasattr(request.app.state, "sync_job_manager"):
        from sync.sync_jobs import SyncJobManager
        request.app.state.sync_job_manager = SyncJobManager(request.app.state.db)
    return request.app.state.sync_job_manager


# ============================================================
# Excel/CSV import (unchanged)
# ============================================================

@router.post("/import/sheets")
async def get_file_sheets(file: UploadFile = File(...)):
    """Get sheet names from an uploaded Excel file."""
    import tempfile, os
    suffix = "." + file.filename.rsplit(".", 1)[-1] if file.filename else ".xlsx"
    with tempfile.NamedTemporaryFile(delete=False, suffix=suffix) as tmp:
        content = await file.read()
        tmp.write(content)
        tmp_path = tmp.name

    try:
        sheets = list_sheets(tmp_path)
        return {"sheets": sheets, "filename": file.filename}
    finally:
        os.unlink(tmp_path)


@router.post("/import")
async def import_file(
    request: Request,
    file: UploadFile = File(...),
    table_name: str = Form(...),
    sheet_name: str = Form(default="0"),
):
    """Import an Excel or CSV file into PostgreSQL."""
    db = request.app.state.db
    settings = request.app.state.settings
    local_pg = LocalPG(db)
    tr = TableRegistry(db)
    cr = ColumnRegistry(db)

    from local_llm.ollama_client import OllamaClient
    ollama = OllamaClient(settings.ollama)
    importer = ExcelImporter(db, local_pg, tr, cr, ollama=ollama)

    content = await file.read()
    file_buffer = BytesIO(content)

    # Parse sheet_name: try int first, then use as string
    try:
        sheet = int(sheet_name)
    except ValueError:
        sheet = sheet_name

    try:
        result = await importer.import_file(
            file_path=file_buffer,
            table_name=table_name,
            sheet_name=sheet,
            original_filename=file.filename,
        )
        return result
    except Exception as e:
        logger.error(f"Import failed: {e}", exc_info=True)
        raise HTTPException(500, f"Import failed: {str(e)}")
