"""Persistent sync job tracking.

Creates a job record in v3q_config.sync_jobs, runs sync in background,
and updates progress at every phase so the frontend can poll status.
"""

from __future__ import annotations

import asyncio
import logging
import time
from datetime import datetime, timezone
from typing import Any

from database.connection_manager import ConnectionManager
from database.local_pg import LocalPG
from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry

logger = logging.getLogger(__name__)


class SyncJobManager:
    """Manages background sync jobs with persistent state in v3q_config.sync_jobs."""

    def __init__(self, cm: ConnectionManager) -> None:
        self._cm = cm
        self._running: dict[str, asyncio.Task] = {}

    async def create_job(
        self,
        connection_id: str,
        connection_name: str,
        db_type: str,
        source_schema: str,
        source_table: str,
        local_table: str | None = None,
    ) -> dict:
        """Create a pending sync job record. Returns the job row."""
        logger.info(
            f"[SYNC-JOB] Creating job: {connection_name}.{source_schema}.{source_table} "
            f"(db_type={db_type}, local_table={local_table})"
        )
        row = await self._cm.fetch_one(
            """
            INSERT INTO v3q_config.sync_jobs
                (connection_id, connection_name, db_type, source_schema, source_table, local_table, status)
            VALUES (:connection_id, :connection_name, :db_type, :source_schema, :source_table, :local_table, 'pending')
            RETURNING *
            """,
            {
                "connection_id": connection_id,
                "connection_name": connection_name,
                "db_type": db_type,
                "source_schema": source_schema,
                "source_table": source_table,
                "local_table": local_table,
            },
        )
        logger.info(f"[SYNC-JOB] Job created: {row['id']}")
        return row

    async def start_job(
        self,
        job_id: str,
        connection_string: str,
        db_type: str,
        source_schema: str,
        source_table: str,
        local_table: str | None,
        connection_id: str,
        source_system: str | None,
        batch_size: int,
        ollama: Any = None,
        sync_filter: str | None = None,
    ) -> None:
        """Launch the sync as a background asyncio task."""
        task = asyncio.create_task(
            self._run_sync(
                job_id=job_id,
                connection_string=connection_string,
                db_type=db_type,
                source_schema=source_schema,
                source_table=source_table,
                local_table=local_table,
                connection_id=connection_id,
                source_system=source_system,
                batch_size=batch_size,
                ollama=ollama,
                sync_filter=sync_filter,
            )
        )
        self._running[job_id] = task

        def _cleanup(t: asyncio.Task):
            self._running.pop(job_id, None)

        task.add_done_callback(_cleanup)

    async def _update_job(self, job_id: str, **fields) -> None:
        """Update job fields in the database."""
        fields["updated_at"] = datetime.now(timezone.utc)
        set_parts = ", ".join(f"{k} = :{k}" for k in fields)
        fields["id"] = job_id

        # Log phase transitions and key progress updates
        if "phase" in fields:
            detail = fields.get("phase_detail", "")
            logger.info(f"[SYNC-JOB] {job_id[:8]}: phase={fields['phase']} — {detail}")
        elif "rows_inserted" in fields:
            logger.debug(f"[SYNC-JOB] {job_id[:8]}: rows_inserted={fields['rows_inserted']:,}")

        try:
            await self._cm.execute_write(
                f"UPDATE v3q_config.sync_jobs SET {set_parts} WHERE id = :id",
                fields,
            )
        except Exception as e:
            logger.warning(f"[SYNC-JOB] Failed to update job {job_id}: {e}")

    async def _run_sync(
        self,
        job_id: str,
        connection_string: str,
        db_type: str,
        source_schema: str,
        source_table: str,
        local_table: str | None,
        connection_id: str,
        source_system: str | None,
        batch_size: int,
        ollama: Any = None,
        sync_filter: str | None = None,
    ) -> None:
        """Execute the full sync pipeline, updating the job record at each phase."""
        from database.remote_executor import RemoteExecutor
        from database.schema_reader import SchemaReader
        from sync.table_sync import TableSync, map_column_type
        from notifications.change_detector import ChangeDetector
        from notifications.watch_config import WatchConfig

        import pandas as pd

        sync_start = time.perf_counter()
        await self._update_job(
            job_id, status="running", started_at=datetime.now(timezone.utc),
            phase="connecting", phase_detail="Connecting to remote database...",
        )
        logger.info(f"[SYNC-JOB] {job_id}: Starting {source_schema}.{source_table}")

        try:
            executor = RemoteExecutor(connection_string, db_type)
            reader = SchemaReader(executor, db_type)
            local_pg = LocalPG(self._cm)
            tr = TableRegistry(self._cm)
            cr = ColumnRegistry(self._cm)
            watch_config = WatchConfig(self._cm)
            change_detector = ChangeDetector(self._cm, watch_config)

            local_name = local_table or f"{source_schema}_{source_table}".lower()

            # Phase 1: Schema read
            await self._update_job(
                job_id, phase="schema_read",
                phase_detail=f"Reading schema for {source_schema}.{source_table}...",
            )
            columns = await reader.get_columns(source_schema, source_table)
            if not columns:
                raise ValueError(f"No columns found for {source_schema}.{source_table}")

            await self._update_job(
                job_id, total_columns=len(columns),
                phase_detail=f"Found {len(columns)} columns",
            )

            # Phase 2: Type mapping + table creation
            pg_columns = []
            for col in columns:
                pg_type = map_column_type(col.data_type, db_type, col)
                pg_columns.append({"name": col.name, "type": pg_type})

            await self._update_job(
                job_id, phase="staging",
                phase_detail=f"Preparing local table v3q_data.{local_name}...",
            )

            has_prev = await change_detector.prepare_staging(local_name)
            if has_prev:
                await local_pg.create_table_from_columns(local_name, pg_columns)
            else:
                table_exists = await self._cm.table_exists("v3q_data", local_name)
                if table_exists:
                    await local_pg.truncate_table(local_name)
                else:
                    await local_pg.create_table_from_columns(local_name, pg_columns)

            # Phase 3: Data transfer (the long part)
            await self._update_job(
                job_id, phase="fetching",
                phase_detail=f"Fetching data from remote ({len(columns)} columns, batch_size={batch_size:,})...",
            )

            q = '`' if db_type == 'mysql' else '"'
            col_list = ", ".join(f'{q}{c.name}{q}' for c in columns)
            select_query = f'SELECT {col_list} FROM {q}{source_schema}{q}.{q}{source_table}{q}'
            if sync_filter:
                select_query += f' WHERE {sync_filter}'
                logger.info(f"[SYNC-JOB] {job_id}: Applying sync filter: {sync_filter}")

            total_rows = 0
            batch_num = 0
            t_data = time.perf_counter()

            async for batch, _ in executor.fetch_batched(select_query, batch_size):
                if not batch:
                    continue
                batch_num += 1

                # Update: fetching progress
                total_rows_fetched = total_rows + len(batch)
                fetch_elapsed = time.perf_counter() - t_data
                fetch_rate = total_rows_fetched / fetch_elapsed if fetch_elapsed > 0 else 0

                await self._update_job(
                    job_id,
                    phase="inserting",
                    rows_fetched=total_rows_fetched,
                    batches_done=batch_num,
                    fetch_rate=round(fetch_rate, 1),
                    phase_detail=(
                        f"Batch {batch_num}: inserting {len(batch):,} rows "
                        f"(total fetched: {total_rows_fetched:,}, {fetch_rate:,.0f} rows/s)..."
                    ),
                )

                df = pd.DataFrame(batch)
                inserted = await local_pg.insert_dataframe(local_name, df)
                total_rows += inserted

                insert_elapsed = time.perf_counter() - t_data
                insert_rate = total_rows / insert_elapsed if insert_elapsed > 0 else 0

                await self._update_job(
                    job_id,
                    rows_inserted=total_rows,
                    insert_rate=round(insert_rate, 1),
                    phase="fetching",
                    phase_detail=(
                        f"Batch {batch_num} done: {total_rows:,} rows inserted "
                        f"({insert_rate:,.0f} rows/s), fetching next..."
                    ),
                )

            data_elapsed = time.perf_counter() - t_data
            logger.info(
                f"[SYNC-JOB] {job_id}: Data transfer done: {total_rows:,} rows "
                f"in {batch_num} batches, {data_elapsed:.1f}s"
            )

            # Phase 4: Registry update
            await self._update_job(
                job_id, phase="registering",
                phase_detail=f"Updating table and column registry ({total_rows:,} rows)...",
            )

            existing = await tr.get_by_local_table(local_name)
            if not existing and connection_id and source_schema:
                existing = await tr.get_by_source(connection_id, source_schema, source_table)
            if existing:
                await tr.update(
                    str(existing["id"]),
                    local_table=local_name,
                    row_count=total_rows,
                    is_synced=True,
                    last_synced_at=datetime.now(timezone.utc),
                )
                table_id = str(existing["id"])
            else:
                reg = await tr.create(
                    connection_id=connection_id,
                    source_schema=source_schema,
                    source_table=source_table,
                    local_table=local_name,
                    source_system=source_system,
                    row_count=total_rows,
                    is_synced=True,
                )
                table_id = str(reg["id"])

            await cr.delete_by_table(table_id)
            for col in columns:
                pg_type = map_column_type(col.data_type, db_type, col)
                await cr.create(
                    table_id=table_id,
                    column_name=col.name,
                    data_type=pg_type,
                    nullable=col.nullable,
                    is_primary_key=col.is_primary_key,
                    max_length=col.max_length,
                    numeric_precision=col.numeric_precision,
                    numeric_scale=col.numeric_scale,
                )

            # Phase 5: Change detection
            sync_event_count = 0
            if has_prev:
                await self._update_job(
                    job_id, phase="change_detection",
                    phase_detail="Comparing with previous data for change detection...",
                )
                try:
                    sync_event_count = await change_detector.detect_changes(table_id, local_name)
                except Exception as e:
                    logger.warning(f"[SYNC-JOB] Change detection failed: {e}")
                    await change_detector._cleanup_prev(local_name)

            # Phase 6: Column enrichment
            if ollama:
                await self._update_job(
                    job_id, phase="enriching",
                    phase_detail="Auto-enriching column descriptions with local LLM...",
                )
                try:
                    from local_llm.column_enricher import ColumnEnricher
                    enricher = ColumnEnricher(ollama, self._cm, cr)
                    domain_hints = await self._load_domain_hints()
                    await enricher.enrich_table(
                        table_id=table_id,
                        local_table=local_name,
                        source_system=source_system,
                        domain_hints=domain_hints,
                    )
                except Exception as e:
                    logger.warning(f"[SYNC-JOB] Enrichment failed (non-critical): {e}")

            # Done
            total_elapsed = time.perf_counter() - sync_start
            final_rate = total_rows / total_elapsed if total_elapsed > 0 else 0

            await self._update_job(
                job_id,
                status="completed",
                phase="done",
                phase_detail=(
                    f"Completed: {total_rows:,} rows, {len(columns)} columns, "
                    f"{batch_num} batches, {total_elapsed:.0f}s total "
                    f"({final_rate:,.0f} rows/s)"
                ),
                rows_fetched=total_rows,
                rows_inserted=total_rows,
                local_table=local_name,
                finished_at=datetime.now(timezone.utc),
            )
            logger.info(
                f"[SYNC-JOB] {job_id}: COMPLETED {source_schema}.{source_table} → "
                f"{total_rows:,} rows in {total_elapsed:.0f}s"
            )

        except asyncio.CancelledError:
            await self._update_job(
                job_id, status="cancelled",
                phase_detail="Job was cancelled",
                finished_at=datetime.now(timezone.utc),
            )
            logger.warning(f"[SYNC-JOB] {job_id}: Cancelled")
            raise

        except Exception as e:
            total_elapsed = time.perf_counter() - sync_start
            await self._update_job(
                job_id,
                status="failed",
                error_message=str(e),
                phase_detail=f"Failed after {total_elapsed:.0f}s: {e}",
                finished_at=datetime.now(timezone.utc),
            )
            logger.error(f"[SYNC-JOB] {job_id}: FAILED after {total_elapsed:.0f}s: {e}", exc_info=True)

    async def _load_domain_hints(self) -> str | None:
        try:
            import json
            row = await self._cm.fetch_one(
                "SELECT value FROM v3q_config.app_settings WHERE key = 'domain_knowledge'",
            )
            if row:
                data = json.loads(row["value"]) if isinstance(row["value"], str) else row["value"]
                if isinstance(data, list):
                    return "\n".join(f"- {item}" for item in data)
        except Exception:
            pass
        return None

    async def get_job(self, job_id: str) -> dict | None:
        return await self._cm.fetch_one(
            "SELECT * FROM v3q_config.sync_jobs WHERE id = :id",
            {"id": job_id},
        )

    async def list_jobs(self, limit: int = 50) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_config.sync_jobs ORDER BY created_at DESC LIMIT :limit",
            {"limit": limit},
        )

    async def cancel_job(self, job_id: str) -> bool:
        task = self._running.get(job_id)
        if task and not task.done():
            logger.warning(f"[SYNC-JOB] Cancelling running task {job_id}")
            task.cancel()
            return True
        # If not in memory but still 'running' in DB, mark as cancelled
        job = await self.get_job(job_id)
        if job and job["status"] == "running":
            logger.warning(f"[SYNC-JOB] Marking orphaned job {job_id} as cancelled")
            await self._update_job(
                job_id, status="cancelled",
                phase_detail="Cancelled (process may have restarted)",
                finished_at=datetime.now(timezone.utc),
            )
            return True
        logger.info(f"[SYNC-JOB] Cancel requested for {job_id} but job is not running (status={job['status'] if job else 'not found'})")
        return False

    async def cleanup_stale_jobs(self) -> int:
        """Mark any 'running' or 'pending' jobs as failed on startup (server restart recovery)."""
        result = await self._cm.execute(
            """
            UPDATE v3q_config.sync_jobs
            SET status = 'failed',
                error_message = 'Server restarted during sync',
                phase_detail = 'Failed: server restarted during sync',
                finished_at = now(),
                updated_at = now()
            WHERE status IN ('running', 'pending')
            RETURNING id
            """,
        )
        count = len(result) if result else 0
        if count:
            logger.warning(f"[SYNC-JOB] Cleaned up {count} stale job(s) from previous run")
        return count
