"""Remote → local PostgreSQL table sync.

Copies selected tables from remote databases (SAP ECC, B1, etc.)
into local PostgreSQL v3q_data schema. All user queries then run locally.
Handles millions of rows via batched inserts.
"""

from __future__ import annotations

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

import pandas as pd

from database.connection_manager import ConnectionManager
from database.local_pg import LocalPG
from database.remote_executor import RemoteExecutor
from database.schema_reader import SchemaReader, ColumnInfo
from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry
from notifications.change_detector import ChangeDetector
from notifications.watch_config import WatchConfig

logger = logging.getLogger(__name__)

# Type mapping: source DB type → PostgreSQL type
_TYPE_MAP_SQLSERVER = {
    "nvarchar": "TEXT",
    "varchar": "TEXT",
    "nchar": "TEXT",
    "char": "TEXT",
    "ntext": "TEXT",
    "text": "TEXT",
    "xml": "TEXT",
    "int": "INTEGER",
    "bigint": "BIGINT",
    "smallint": "SMALLINT",
    "tinyint": "SMALLINT",
    "bit": "BOOLEAN",
    "float": "DOUBLE PRECISION",
    "real": "REAL",
    "decimal": "NUMERIC",
    "numeric": "NUMERIC",
    "money": "NUMERIC(19,4)",
    "smallmoney": "NUMERIC(10,4)",
    "datetime": "TIMESTAMP",
    "datetime2": "TIMESTAMP",
    "smalldatetime": "TIMESTAMP",
    "date": "DATE",
    "time": "TIME",
    "datetimeoffset": "TIMESTAMPTZ",
    "uniqueidentifier": "UUID",
    "binary": "BYTEA",
    "varbinary": "BYTEA",
    "image": "BYTEA",
}

_TYPE_MAP_HANA = {
    "nvarchar": "TEXT",
    "varchar": "TEXT",
    "nclob": "TEXT",
    "clob": "TEXT",
    "integer": "INTEGER",
    "bigint": "BIGINT",
    "smallint": "SMALLINT",
    "tinyint": "SMALLINT",
    "decimal": "NUMERIC",
    "double": "DOUBLE PRECISION",
    "real": "REAL",
    "date": "DATE",
    "time": "TIME",
    "timestamp": "TIMESTAMP",
    "seconddate": "TIMESTAMP",
    "boolean": "BOOLEAN",
    "blob": "BYTEA",
    "varbinary": "BYTEA",
}

_TYPE_MAP_MYSQL = {
    "varchar": "TEXT",
    "char": "TEXT",
    "text": "TEXT",
    "tinytext": "TEXT",
    "mediumtext": "TEXT",
    "longtext": "TEXT",
    "enum": "TEXT",
    "set": "TEXT",
    "int": "INTEGER",
    "integer": "INTEGER",
    "bigint": "BIGINT",
    "smallint": "SMALLINT",
    "tinyint": "SMALLINT",
    "mediumint": "INTEGER",
    "float": "REAL",
    "double": "DOUBLE PRECISION",
    "decimal": "NUMERIC",
    "numeric": "NUMERIC",
    "date": "DATE",
    "time": "TIME",
    "datetime": "TIMESTAMP",
    "timestamp": "TIMESTAMP",
    "year": "SMALLINT",
    "bit": "BOOLEAN",
    "boolean": "BOOLEAN",
    "bool": "BOOLEAN",
    "binary": "BYTEA",
    "varbinary": "BYTEA",
    "blob": "BYTEA",
    "tinyblob": "BYTEA",
    "mediumblob": "BYTEA",
    "longblob": "BYTEA",
    "json": "JSONB",
}

_TYPE_MAP_SYBASE = {
    "varchar": "TEXT",
    "char": "TEXT",
    "nchar": "TEXT",
    "nvarchar": "TEXT",
    "text": "TEXT",
    "unitext": "TEXT",
    "univarchar": "TEXT",
    "int": "INTEGER",
    "integer": "INTEGER",
    "bigint": "BIGINT",
    "smallint": "SMALLINT",
    "tinyint": "SMALLINT",
    "unsigned int": "BIGINT",
    "unsigned bigint": "BIGINT",
    "unsigned smallint": "INTEGER",
    "bit": "BOOLEAN",
    "float": "DOUBLE PRECISION",
    "real": "REAL",
    "double precision": "DOUBLE PRECISION",
    "decimal": "NUMERIC",
    "numeric": "NUMERIC",
    "money": "NUMERIC(19,4)",
    "smallmoney": "NUMERIC(10,4)",
    "datetime": "TIMESTAMP",
    "smalldatetime": "TIMESTAMP",
    "date": "DATE",
    "time": "TIME",
    "bigdatetime": "TIMESTAMP",
    "bigtime": "TIME",
    "binary": "BYTEA",
    "varbinary": "BYTEA",
    "image": "BYTEA",
}

_TYPE_MAPS = {
    "sqlserver": _TYPE_MAP_SQLSERVER,
    "sybase": _TYPE_MAP_SYBASE,
    "hana": _TYPE_MAP_HANA,
    "mysql": _TYPE_MAP_MYSQL,
}


def map_column_type(source_type: str, db_type: str, col: ColumnInfo) -> str:
    """Map a source database column type to PostgreSQL equivalent."""
    source_lower = source_type.lower().split("(")[0].strip()
    type_map = _TYPE_MAPS.get(db_type, _TYPE_MAP_SQLSERVER)

    pg_type = type_map.get(source_lower, "TEXT")

    # Preserve precision/scale for numeric types
    if pg_type == "NUMERIC" and col.numeric_precision:
        scale = col.numeric_scale or 0
        pg_type = f"NUMERIC({col.numeric_precision},{scale})"

    return pg_type


class TableSync:
    """Sync tables from remote databases into local PostgreSQL."""

    def __init__(
        self,
        cm: ConnectionManager,
        local_pg: LocalPG,
        table_registry: TableRegistry,
        column_registry: ColumnRegistry,
        batch_size: int = 10000,
        ollama: Any = None,
    ) -> None:
        self._cm = cm
        self._local = local_pg
        self._table_reg = table_registry
        self._col_reg = column_registry
        self._batch_size = batch_size
        self._ollama = ollama  # Optional: enables auto-enrichment of column descriptions
        self._watch_config = WatchConfig(cm)
        self._change_detector = ChangeDetector(cm, self._watch_config)

    async def sync_table(
        self,
        connection_string: str,
        db_type: str,
        source_schema: str,
        source_table: str,
        local_table: str | None = None,
        connection_id: str | None = None,
        source_system: str | None = None,
    ) -> dict:
        """Sync a single table from remote to local PostgreSQL.

        Steps:
        1. Read source schema (columns, types)
        2. Map types to PostgreSQL equivalents
        3. Create local table (or truncate if exists)
        4. Copy data in batches
        5. Register in table_registry + column_registry
        """
        sync_start = time.perf_counter()
        executor = RemoteExecutor(connection_string, db_type)
        reader = SchemaReader(executor, db_type)

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

        logger.info(
            f"[SYNC] ========== START {source_schema}.{source_table} → v3q_data.{local_name} "
            f"(db_type={db_type}, batch_size={self._batch_size:,}) =========="
        )

        # 1. Read source columns
        t_phase = time.perf_counter()
        columns = await reader.get_columns(source_schema, source_table)
        if not columns:
            raise ValueError(f"No columns found for {source_schema}.{source_table}")
        logger.info(
            f"[SYNC] Phase 1 — Schema read: {len(columns)} columns in {time.perf_counter() - t_phase:.2f}s"
        )
        for col in columns:
            logger.debug(f"[SYNC]   col: {col.name} ({col.data_type}, nullable={col.nullable}, pk={col.is_primary_key})")

        # 2. Map types
        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})
        logger.info(f"[SYNC] Phase 2 — Type mapping: {len(pg_columns)} columns mapped")

        # 3. Staging for change detection: rename existing → _prev, then create fresh
        t_phase = time.perf_counter()
        has_prev = await self._change_detector.prepare_staging(local_name)
        if has_prev:
            await self._local.create_table_from_columns(local_name, pg_columns)
            logger.info(f"[SYNC] Phase 3 — Staging: renamed → _prev, created fresh table ({time.perf_counter() - t_phase:.2f}s)")
        else:
            table_exists = await self._cm.table_exists("v3q_data", local_name)
            if table_exists:
                await self._local.truncate_table(local_name)
                logger.info(f"[SYNC] Phase 3 — Truncated existing table ({time.perf_counter() - t_phase:.2f}s)")
            else:
                await self._local.create_table_from_columns(local_name, pg_columns)
                logger.info(f"[SYNC] Phase 3 — Created new table ({time.perf_counter() - t_phase:.2f}s)")

        # 4. Copy data in batches
        t_data = time.perf_counter()
        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}'
        logger.info(f"[SYNC] Phase 4 — Data transfer starting: {select_query[:150]}...")

        total_rows = 0
        batch_num = 0
        async for batch, _ in executor.fetch_batched(select_query, self._batch_size):
            if batch:
                batch_num += 1
                t_batch = time.perf_counter()
                df = pd.DataFrame(batch)
                t_df = time.perf_counter()
                logger.info(
                    f"[SYNC]   Batch {batch_num}: DataFrame created ({len(df):,} rows x {len(df.columns)} cols) "
                    f"in {t_df - t_batch:.2f}s, inserting..."
                )
                inserted = await self._local.insert_dataframe(local_name, df)
                total_rows += inserted
                batch_elapsed = time.perf_counter() - t_batch
                overall_elapsed = time.perf_counter() - t_data
                overall_rate = total_rows / overall_elapsed if overall_elapsed > 0 else 0
                logger.info(
                    f"[SYNC]   Batch {batch_num} done: +{inserted:,} rows in {batch_elapsed:.2f}s | "
                    f"Running total: {total_rows:,} rows, {overall_elapsed:.1f}s elapsed, "
                    f"{overall_rate:,.0f} rows/s overall"
                )

        data_elapsed = time.perf_counter() - t_data
        data_rate = total_rows / data_elapsed if data_elapsed > 0 else 0
        logger.info(
            f"[SYNC] Phase 4 — Data transfer complete: {total_rows:,} rows in {batch_num} batches, "
            f"{data_elapsed:.1f}s, {data_rate:,.0f} rows/s"
        )

        # 5. Register in table_registry
        t_phase = time.perf_counter()
        existing = await self._table_reg.get_by_local_table(local_name)
        if not existing and connection_id and source_schema:
            existing = await self._table_reg.get_by_source(
                connection_id, source_schema, source_table
            )
        if existing:
            await self._table_reg.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 self._table_reg.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"])
        logger.info(f"[SYNC] Phase 5 — Registry updated (table_id={table_id}) in {time.perf_counter() - t_phase:.2f}s")

        # 6. Register columns
        t_phase = time.perf_counter()
        await self._col_reg.delete_by_table(table_id)
        for col in columns:
            pg_type = map_column_type(col.data_type, db_type, col)
            await self._col_reg.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,
            )
        logger.info(f"[SYNC] Phase 6 — {len(columns)} columns registered in {time.perf_counter() - t_phase:.2f}s")

        # 7. Change detection: compare _prev vs new table
        sync_event_count = 0
        if has_prev:
            t_phase = time.perf_counter()
            try:
                sync_event_count = await self._change_detector.detect_changes(table_id, local_name)
                logger.info(
                    f"[SYNC] Phase 7 — Change detection: {sync_event_count} changes in {time.perf_counter() - t_phase:.2f}s"
                )
            except Exception as e:
                logger.warning(f"[SYNC] Phase 7 — Change detection failed (non-critical): {e}", exc_info=True)
                await self._change_detector._cleanup_prev(local_name)

        # 8. Auto-enrich column descriptions using local LLM
        if self._ollama:
            t_phase = time.perf_counter()
            try:
                from local_llm.column_enricher import ColumnEnricher
                enricher = ColumnEnricher(self._ollama, self._cm, self._col_reg)
                domain_hints = await self._load_domain_hints()
                enriched = await enricher.enrich_table(
                    table_id=table_id,
                    local_table=local_name,
                    source_system=source_system,
                    domain_hints=domain_hints,
                )
                logger.info(f"[SYNC] Phase 8 — Auto-enriched {len(enriched)} columns in {time.perf_counter() - t_phase:.2f}s")
            except Exception as e:
                logger.warning(f"[SYNC] Phase 8 — Column enrichment failed (non-critical): {e}", exc_info=True)

        total_elapsed = time.perf_counter() - sync_start
        final_rate = total_rows / total_elapsed if total_elapsed > 0 else 0
        logger.info(
            f"[SYNC] ========== COMPLETE {source_schema}.{source_table} → v3q_data.{local_name}: "
            f"{total_rows:,} rows, {len(columns)} cols, {total_elapsed:.1f}s total, "
            f"{final_rate:,.0f} rows/s =========="
        )

        return {
            "local_table": local_name,
            "row_count": total_rows,
            "column_count": len(columns),
            "table_id": table_id,
            "sync_events": sync_event_count,
        }

    async def _load_domain_hints(self) -> str | None:
        """Load domain-specific knowledge from app_settings."""
        try:
            row = await self._cm.fetch_one(
                "SELECT value FROM v3q_config.app_settings WHERE key = 'domain_knowledge'",
            )
            if row:
                import json
                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)
                elif isinstance(data, str):
                    return data
        except Exception:
            pass
        return None
