"""Staging table change detection during sync.

Strategy:
1. RENAME existing table to {name}_prev
2. Caller inserts fresh data into new table (existing batched code)
3. Compare _prev vs new on configured key/watch columns → write sync_events
4. DROP _prev table

The _prev table exists only during comparison (seconds).
"""

from __future__ import annotations

import json
import logging

from database.connection_manager import ConnectionManager
from notifications.watch_config import WatchConfig

logger = logging.getLogger(__name__)


class ChangeDetector:
    """Detect column-level changes between old and new sync data."""

    def __init__(self, cm: ConnectionManager, watch_config: WatchConfig) -> None:
        self._cm = cm
        self._watch = watch_config

    async def prepare_staging(self, local_table: str) -> bool:
        """Rename existing table to {name}_prev before new data insert.

        Returns True if a _prev table was created (table existed), False otherwise.
        """
        exists = await self._cm.table_exists("v3q_data", local_table)
        if not exists:
            return False

        prev_table = f"{local_table}_prev"
        # Drop any leftover _prev table
        await self._cm.execute_write(f'DROP TABLE IF EXISTS v3q_data."{prev_table}"')
        await self._cm.execute_write(
            f'ALTER TABLE v3q_data."{local_table}" RENAME TO "{prev_table}"'
        )
        logger.info(f"Staged: v3q_data.{local_table} → v3q_data.{prev_table}")
        return True

    async def detect_changes(self, table_id: str, local_table: str) -> int:
        """Compare _prev vs new table and write sync_events.

        Returns count of events written.
        """
        has_config = await self._watch.has_watch_config(table_id)
        if not has_config:
            logger.debug(f"No watch columns configured for {local_table}, skipping change detection")
            await self._cleanup_prev(local_table)
            return 0

        key_cols = await self._watch.get_key_columns(table_id)
        watch_cols = await self._watch.get_all_watched_column_names(table_id)
        doc_number_col = await self._watch.get_doc_number_column(table_id)

        if not key_cols:
            logger.warning(f"No key columns configured for {local_table}, skipping change detection")
            await self._cleanup_prev(local_table)
            return 0

        # Filter watch_cols to exclude key columns
        value_cols = [c for c in watch_cols if c not in key_cols]
        if not value_cols:
            logger.warning(f"No non-key watch columns for {local_table}, skipping")
            await self._cleanup_prev(local_table)
            return 0

        prev_table = f"{local_table}_prev"

        # Verify _prev table exists
        prev_exists = await self._cm.table_exists("v3q_data", prev_table)
        if not prev_exists:
            logger.debug(f"No _prev table for {local_table}, skipping (first sync)")
            return 0

        events_written = 0

        # Detect CHANGED rows: exist in both, but watched values differ
        events_written += await self._detect_changed_rows(
            table_id, local_table, prev_table, key_cols, value_cols, doc_number_col
        )

        # Detect ADDED rows: in new but not in _prev
        events_written += await self._detect_added_rows(
            table_id, local_table, prev_table, key_cols, value_cols, doc_number_col
        )

        # Detect REMOVED rows: in _prev but not in new
        events_written += await self._detect_removed_rows(
            table_id, local_table, prev_table, key_cols, value_cols, doc_number_col
        )

        await self._cleanup_prev(local_table)

        logger.info(f"Change detection for {local_table}: {events_written} events")
        return events_written

    async def _detect_changed_rows(
        self, table_id: str, new_table: str, prev_table: str,
        key_cols: list[str], value_cols: list[str], doc_number_col: str | None = None,
    ) -> int:
        """Find rows that exist in both tables but have different watched values."""
        join_cond = " AND ".join(
            f'n."{k}" = p."{k}"' for k in key_cols
        )
        # Build a CASE for each value column to check for difference
        change_checks = " OR ".join(
            f'n."{c}" IS DISTINCT FROM p."{c}"' for c in value_cols
        )

        # Include doc_number column in SELECT if configured and not already in key/value cols
        extra_select = ""
        if doc_number_col and doc_number_col not in key_cols and doc_number_col not in value_cols:
            extra_select = f', n."{doc_number_col}" as "__doc_number"'

        # Fetch rows where at least one watched column changed
        query = f"""
            SELECT {', '.join(f'n."{k}"' for k in key_cols)},
                   {', '.join(f'n."{c}" as "new_{c}", p."{c}" as "old_{c}"' for c in value_cols)}{extra_select}
            FROM v3q_data."{new_table}" n
            JOIN v3q_data."{prev_table}" p ON {join_cond}
            WHERE {change_checks}
        """
        rows = await self._cm.execute(query)

        count = 0
        for row in rows:
            row_key = {k: str(row[k]) if row[k] is not None else None for k in key_cols}
            doc_number = self._extract_doc_number(row, doc_number_col, key_cols)
            for col in value_cols:
                old_val = row[f"old_{col}"]
                new_val = row[f"new_{col}"]
                if old_val != new_val:
                    await self._write_event(
                        table_id, new_table, row_key, col,
                        str(old_val) if old_val is not None else None,
                        str(new_val) if new_val is not None else None,
                        "changed", doc_number,
                    )
                    count += 1
        return count

    async def _detect_added_rows(
        self, table_id: str, new_table: str, prev_table: str,
        key_cols: list[str], value_cols: list[str], doc_number_col: str | None = None,
    ) -> int:
        """Find rows in new table that don't exist in prev."""
        join_cond = " AND ".join(
            f'n."{k}" = p."{k}"' for k in key_cols
        )
        null_check = " AND ".join(f'p."{k}" IS NULL' for k in key_cols)

        # Include doc_number column if configured and not already selected
        all_select_cols = list(key_cols) + list(value_cols)
        extra_select = ""
        if doc_number_col and doc_number_col not in all_select_cols:
            extra_select = f', n."{doc_number_col}"'

        query = f"""
            SELECT {', '.join(f'n."{k}"' for k in key_cols)},
                   {', '.join(f'n."{c}"' for c in value_cols)}{extra_select}
            FROM v3q_data."{new_table}" n
            LEFT JOIN v3q_data."{prev_table}" p ON {join_cond}
            WHERE {null_check}
        """
        rows = await self._cm.execute(query)

        count = 0
        for row in rows:
            row_key = {k: str(row[k]) if row[k] is not None else None for k in key_cols}
            doc_number = self._extract_doc_number(row, doc_number_col, key_cols)
            for col in value_cols:
                val = row[col]
                if val is not None:
                    await self._write_event(
                        table_id, new_table, row_key, col,
                        None, str(val), "added", doc_number,
                    )
                    count += 1
        return count

    async def _detect_removed_rows(
        self, table_id: str, new_table: str, prev_table: str,
        key_cols: list[str], value_cols: list[str], doc_number_col: str | None = None,
    ) -> int:
        """Find rows in prev that don't exist in new table."""
        join_cond = " AND ".join(
            f'p."{k}" = n."{k}"' for k in key_cols
        )
        null_check = " AND ".join(f'n."{k}" IS NULL' for k in key_cols)

        # Include doc_number column if configured and not already selected
        all_select_cols = list(key_cols) + list(value_cols)
        extra_select = ""
        if doc_number_col and doc_number_col not in all_select_cols:
            extra_select = f', p."{doc_number_col}"'

        query = f"""
            SELECT {', '.join(f'p."{k}"' for k in key_cols)},
                   {', '.join(f'p."{c}"' for c in value_cols)}{extra_select}
            FROM v3q_data."{prev_table}" p
            LEFT JOIN v3q_data."{new_table}" n ON {join_cond}
            WHERE {null_check}
        """
        rows = await self._cm.execute(query)

        count = 0
        for row in rows:
            row_key = {k: str(row[k]) if row[k] is not None else None for k in key_cols}
            doc_number = self._extract_doc_number(row, doc_number_col, key_cols)
            for col in value_cols:
                val = row[col]
                if val is not None:
                    await self._write_event(
                        table_id, new_table, row_key, col,
                        str(val), None, "removed", doc_number,
                    )
                    count += 1
        return count

    def _extract_doc_number(
        self, row: dict, doc_number_col: str | None, key_cols: list[str],
    ) -> str | None:
        """Extract the doc_number value from a row using the configured column."""
        if not doc_number_col:
            return None
        # Check direct column name first, then aliased as __doc_number
        val = row.get(doc_number_col) or row.get("__doc_number")
        if val is not None:
            return str(val).strip() or None
        # Fallback: doc_number_col might be a key column available directly
        if doc_number_col in key_cols:
            val = row.get(doc_number_col)
            return str(val).strip() if val is not None else None
        return None

    async def _write_event(
        self, table_id: str, local_table: str, row_key: dict,
        column_name: str, old_value: str | None, new_value: str | None,
        change_type: str, doc_number: str | None = None,
    ) -> None:
        """Write a single sync_event row."""
        await self._cm.execute_write(
            """INSERT INTO v3q_data.sync_events
               (table_id, local_table, row_key, column_name, old_value, new_value, change_type, doc_number)
               VALUES (:table_id, :local_table, :row_key, :column_name, :old_value, :new_value, :change_type, :doc_number)""",
            {
                "table_id": table_id,
                "local_table": local_table,
                "row_key": json.dumps(row_key),
                "column_name": column_name,
                "old_value": old_value,
                "new_value": new_value,
                "change_type": change_type,
                "doc_number": doc_number,
            },
        )

    async def _cleanup_prev(self, local_table: str) -> None:
        prev_table = f"{local_table}_prev"
        await self._cm.execute_write(f'DROP TABLE IF EXISTS v3q_data."{prev_table}"')
        logger.debug(f"Dropped staging table v3q_data.{prev_table}")
