"""Auto-generate business descriptions for columns using the local LLM.

After a table is synced, the local LLM examines the column names and a sample
of actual data to infer what each column means in business terms.  This is
critical for SAP-style tables where column names are cryptic abbreviations
(e.g. KRAUS = DUNS number for suppliers, LOCCO = DUNS number for customers).

The descriptions are stored in column_registry.display_name and
column_registry.business_description, and are included in the metadata prompt
that Claude receives — giving Claude the context it needs to generate
correct SQL even with cryptic column names.
"""

from __future__ import annotations

import json
import logging
from typing import Any

from local_llm.ollama_client import OllamaClient
from knowledge.column_registry import ColumnRegistry
from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class ColumnEnricher:
    """Uses local LLM to auto-describe columns from sample data."""

    def __init__(
        self,
        ollama: OllamaClient,
        cm: ConnectionManager,
        column_registry: ColumnRegistry,
    ) -> None:
        self._ollama = ollama
        self._cm = cm
        self._col_reg = column_registry

    async def enrich_table(
        self,
        table_id: str,
        local_table: str,
        source_system: str | None = None,
        domain_hints: str | None = None,
    ) -> dict[str, str]:
        """Analyze a synced table and generate column descriptions.

        Args:
            table_id: The table_registry ID
            local_table: The local PG table name (in v3q_data schema)
            source_system: ERP source (sap_ecc, sap_b1, infor, etc.)
            domain_hints: Optional domain-specific hints (e.g. known column meanings)

        Returns:
            Dict mapping column_name → generated business_description
        """
        # Get existing column definitions
        columns = await self._col_reg.list_by_table(table_id)
        if not columns:
            return {}

        # Fetch a few sample rows from the local table
        sample_rows = await self._fetch_sample(local_table)
        if not sample_rows:
            logger.info(f"No sample data available for {local_table}, skipping enrichment")
            return {}

        # Build prompt with column info + sample data
        col_list = []
        for col in columns:
            desc = f"  - {col['column_name']} ({col['data_type']}"
            if col.get("is_primary_key"):
                desc += ", PK"
            if not col.get("nullable", True):
                desc += ", NOT NULL"
            desc += ")"
            col_list.append(desc)

        sample_text = json.dumps(sample_rows[:5], indent=2, default=str)

        system_context = (
            "You are a database analyst specializing in ERP systems "
            "(SAP ECC, SAP Business One, Infor, HubSpot). "
            "You are given column names and sample data from a business database table. "
            "Your job is to figure out what each column represents in business terms."
        )

        prompt = f"""Analyze these database columns and their sample data.
For each column, provide a short business-friendly description of what it contains.

Table: {local_table}
Source system: {source_system or 'unknown'}

Columns:
{chr(10).join(col_list)}

Sample data (first 5 rows):
{sample_text}
"""

        if domain_hints:
            prompt += f"\nDomain-specific knowledge:\n{domain_hints}\n"

        prompt += """
Respond with a JSON object mapping column_name to a short description (max 100 chars).
Include a "display_name" (max 30 chars, human-friendly name) for each column.
Format:
{
  "COLUMN_NAME": {"display_name": "Human Name", "description": "What this column contains"},
  ...
}

IMPORTANT:
- Pay special attention to cryptic SAP abbreviations (e.g. LIFNR=Vendor Number, KUNNR=Customer Number, BUKRS=Company Code, MANDT=Client/Mandant)
- Look at the actual data values to confirm your guesses
- If a column contains codes, mention what kind of codes they are
- If empty values are spaces instead of NULL, note that in the description
- Respond ONLY with valid JSON, no explanation."""

        try:
            response = await self._ollama.generate(
                prompt=prompt,
                system=system_context,
                temperature=0.1,
            )

            descriptions = self._parse_response(response)
            if not descriptions:
                return {}

            # Update column registry with generated descriptions
            updated = {}
            for col in columns:
                col_name = col["column_name"]
                if col_name in descriptions:
                    info = descriptions[col_name]
                    display = info.get("display_name", "")
                    desc = info.get("description", "")

                    # Only update if no existing description or if it's empty
                    update_fields: dict[str, Any] = {}
                    if desc and not col.get("business_description"):
                        update_fields["business_description"] = desc[:500]
                    if display and not col.get("display_name"):
                        update_fields["display_name"] = display[:100]

                    if update_fields:
                        await self._col_reg.update(str(col["id"]), **update_fields)
                        updated[col_name] = desc

            logger.info(f"Enriched {len(updated)} columns for table {local_table}")
            return updated

        except Exception as e:
            logger.warning(f"Column enrichment failed for {local_table}: {e}")
            return {}

    async def _fetch_sample(self, local_table: str, limit: int = 5) -> list[dict]:
        """Fetch sample rows from a local table."""
        try:
            rows = await self._cm.execute(
                f'SELECT * FROM v3q_data."{local_table}" LIMIT :limit',
                {"limit": limit},
            )
            return rows
        except Exception as e:
            logger.warning(f"Could not fetch sample from {local_table}: {e}")
            return []

    @staticmethod
    def _parse_response(response: str) -> dict:
        """Parse the LLM response as JSON, handling common formatting issues."""
        text = response.strip()

        # Remove markdown code blocks
        if text.startswith("```"):
            lines = text.split("\n")
            lines = lines[1:]
            if lines and lines[-1].strip().startswith("```"):
                lines = lines[:-1]
            text = "\n".join(lines).strip()

        try:
            return json.loads(text)
        except json.JSONDecodeError:
            # Try to find JSON within the response
            start = text.find("{")
            end = text.rfind("}") + 1
            if start >= 0 and end > start:
                try:
                    return json.loads(text[start:end])
                except json.JSONDecodeError:
                    pass
            return {}
