"""Auto-detect join columns by name similarity across tables."""

from __future__ import annotations

import logging
from typing import Any

from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class RelationshipInference:
    """Infer potential join relationships between tables by analyzing column names."""

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

    async def infer_relationships(self) -> list[dict]:
        """Scan all registered columns and find potential join candidates.

        Strategy:
        1. Columns with identical names in different tables (e.g., CustomerID)
        2. Columns matching pattern: TableName + ID (e.g., customer_id → customers.id)
        3. Common ERP patterns (DocEntry, DocNum, CardCode, ItemCode, etc.)
        """
        # Get all columns with their table info
        columns = await self._cm.execute("""
            SELECT
                cr.id AS column_id,
                cr.table_id,
                cr.column_name,
                cr.data_type,
                cr.is_primary_key,
                tr.local_table,
                tr.source_table,
                tr.source_system
            FROM v3q_config.column_registry cr
            JOIN v3q_config.table_registry tr ON tr.id = cr.table_id
        """)

        # Build index by column name
        by_name: dict[str, list[dict]] = {}
        for col in columns:
            name_lower = col["column_name"].lower()
            by_name.setdefault(name_lower, []).append(col)

        inferred = []
        seen_pairs = set()

        # Strategy 1: Exact name matches across different tables
        for name, cols in by_name.items():
            if len(cols) < 2:
                continue
            # Find the one that's a PK (if any)
            pk_cols = [c for c in cols if c["is_primary_key"]]
            fk_cols = [c for c in cols if not c["is_primary_key"]]

            if pk_cols and fk_cols:
                for pk in pk_cols:
                    for fk in fk_cols:
                        if pk["table_id"] == fk["table_id"]:
                            continue
                        pair_key = tuple(sorted([str(pk["table_id"]), str(fk["table_id"])]))
                        if pair_key in seen_pairs:
                            continue
                        seen_pairs.add(pair_key)
                        inferred.append({
                            "left_table_id": str(pk["table_id"]),
                            "left_column": pk["column_name"],
                            "right_table_id": str(fk["table_id"]),
                            "right_column": fk["column_name"],
                            "confidence": 0.9,
                            "reason": f"Exact column name match: {name}",
                        })
            elif len(cols) == 2:
                # Both non-PK but same name
                pair_key = tuple(sorted([str(cols[0]["table_id"]), str(cols[1]["table_id"])]))
                if pair_key not in seen_pairs and cols[0]["table_id"] != cols[1]["table_id"]:
                    seen_pairs.add(pair_key)
                    inferred.append({
                        "left_table_id": str(cols[0]["table_id"]),
                        "left_column": cols[0]["column_name"],
                        "right_table_id": str(cols[1]["table_id"]),
                        "right_column": cols[1]["column_name"],
                        "confidence": 0.7,
                        "reason": f"Same column name in different tables: {name}",
                    })

        # Strategy 2: Common ERP key patterns
        erp_keys = [
            "docentry", "docnum", "cardcode", "itemcode", "bpcode",
            "ordernumber", "invoicenumber", "customerid", "vendorid",
            "supplierid", "productid", "articleid", "sku",
        ]
        for key in erp_keys:
            if key in by_name and len(by_name[key]) >= 2:
                cols = by_name[key]
                for i, c1 in enumerate(cols):
                    for c2 in cols[i + 1:]:
                        if c1["table_id"] == c2["table_id"]:
                            continue
                        pair_key = tuple(sorted([str(c1["table_id"]), str(c2["table_id"])]))
                        if pair_key in seen_pairs:
                            continue
                        seen_pairs.add(pair_key)
                        inferred.append({
                            "left_table_id": str(c1["table_id"]),
                            "left_column": c1["column_name"],
                            "right_table_id": str(c2["table_id"]),
                            "right_column": c2["column_name"],
                            "confidence": 0.85,
                            "reason": f"Common ERP key pattern: {key}",
                        })

        return sorted(inferred, key=lambda x: x["confidence"], reverse=True)

    async def save_inferred(self, relationships: list[dict]) -> int:
        """Save inferred relationships to cross_db_joins table."""
        saved = 0
        for rel in relationships:
            try:
                await self._cm.execute_write("""
                    INSERT INTO v3q_config.cross_db_joins
                        (left_table_id, left_column, right_table_id, right_column,
                         join_type, confidence, is_auto_detected, description)
                    VALUES (:lt, :lc, :rt, :rc, 'INNER', :conf, true, :desc)
                    ON CONFLICT DO NOTHING
                """, {
                    "lt": rel["left_table_id"],
                    "lc": rel["left_column"],
                    "rt": rel["right_table_id"],
                    "rc": rel["right_column"],
                    "conf": rel["confidence"],
                    "desc": rel["reason"],
                })
                saved += 1
            except Exception as e:
                logger.warning(f"Failed to save relationship: {e}")
        return saved
