"""Build schema context string for Claude API.

CRITICAL: This module assembles the metadata prompt that Claude sees.
It reads table_registry, column_registry, cross_db_joins, and query_patterns
to build a structured text describing available tables, columns, types,
business meanings, and join hints.

**NO DATA VALUES EVER APPEAR IN THIS OUTPUT.**
"""

from __future__ import annotations

import re

from database.connection_manager import ConnectionManager

# Patterns that look like concrete data values — strip from descriptions before sending to Claude
_EXAMPLE_VALUE_PATTERNS = [
    re.compile(r"\(e\.?g\.?\s*[^)]*\)", re.IGNORECASE),  # (e.g. $250.00) or (eg ...)
    re.compile(r"[\$€£]\s*[\d,]+\.?\d*"),                 # $250.00, €1,000
    re.compile(r"\b\d{1,3}(?:,\d{3})+(?:\.\d+)?\b"),      # 1,234,567.89
]


def _sanitize_description(text: str) -> str:
    """Remove example data values from a description so it passes the security gateway."""
    for pat in _EXAMPLE_VALUE_PATTERNS:
        text = pat.sub("", text)
    # Clean up leftover whitespace
    return re.sub(r"\s{2,}", " ", text).strip()


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

    async def build(self, relevant_tables: list[str] | None = None) -> str:
        """Build the full schema context string for Claude.

        Args:
            relevant_tables: If provided, only include these tables (by local_table name).
                             If None, include all synced/active tables.

        Returns:
            A structured text description of the database schema — metadata only.
        """
        parts = [
            "You are a SQL expert. Generate PostgreSQL-compatible SELECT queries based on the schema below.",
            "All tables are in the 'v3q_data' schema. Always prefix table names with 'v3q_data.'",
            "ONLY generate SELECT statements. Never generate INSERT, UPDATE, DELETE, or DDL.",
            "",
            "=== AVAILABLE TABLES ===",
            "",
        ]

        # Fetch tables
        if relevant_tables:
            placeholders = ", ".join(f"'{t}'" for t in relevant_tables)
            tables = await self._cm.execute(f"""
                SELECT * FROM v3q_config.table_registry
                WHERE local_table IN ({placeholders})
                ORDER BY source_system, source_table
            """)
        else:
            tables = await self._cm.execute("""
                SELECT * FROM v3q_config.table_registry
                WHERE is_synced = true OR local_table IS NOT NULL
                ORDER BY source_system, source_table
            """)

        if not tables:
            parts.append("No tables are currently available.")
            return "\n".join(parts)

        for table in tables:
            table_id = str(table["id"])
            local_name = table.get("local_table") or table["source_table"]
            display = table.get("display_name") or local_name
            system = table.get("source_system") or "unknown"
            desc = table.get("business_description") or ""
            row_count = table.get("row_count")

            parts.append(f"TABLE: v3q_data.\"{local_name}\"")
            parts.append(f"  Source: {system} / {table.get('source_schema', '')}.{table['source_table']}")
            if display != local_name:
                parts.append(f"  Display Name: {display}")
            if desc:
                parts.append(f"  Description: {desc}")
            if row_count is not None:
                parts.append(f"  Approximate rows: {row_count:,}")

            # Fetch columns for this table
            columns = await self._cm.execute(
                "SELECT * FROM v3q_config.column_registry WHERE table_id = :tid ORDER BY column_name",
                {"tid": table_id},
            )

            parts.append("  COLUMNS:")
            for col in columns:
                col_line = f"    - {col['column_name']} ({col['data_type']}"
                if col.get("max_length"):
                    col_line += f", max_length={col['max_length']}"
                if col.get("numeric_precision"):
                    col_line += f", precision={col['numeric_precision']}"
                    if col.get("numeric_scale"):
                        col_line += f", scale={col['numeric_scale']}"
                col_line += ")"
                if col.get("is_primary_key"):
                    col_line += " [PK]"
                if not col.get("nullable", True):
                    col_line += " [NOT NULL]"
                if col.get("display_name"):
                    col_line += f" — {_sanitize_description(col['display_name'])}"
                if col.get("business_description"):
                    col_line += f" — {_sanitize_description(col['business_description'])}"
                parts.append(col_line)

            parts.append("")

        # Fetch join definitions
        joins = await self._cm.execute("""
            SELECT
                j.*,
                lt.local_table AS left_table,
                rt.local_table AS right_table
            FROM v3q_config.cross_db_joins j
            JOIN v3q_config.table_registry lt ON lt.id = j.left_table_id
            JOIN v3q_config.table_registry rt ON rt.id = j.right_table_id
        """)

        if joins:
            parts.append("=== JOIN RELATIONSHIPS ===")
            parts.append("")
            for j in joins:
                parts.append(
                    f"  v3q_data.\"{j['left_table']}\".\"{j['left_column']}\" "
                    f"{j['join_type']} JOIN "
                    f"v3q_data.\"{j['right_table']}\".\"{j['right_column']}\""
                )
                if j.get("description"):
                    parts.append(f"    Note: {j['description']}")
            parts.append("")

        # Fetch relevant query patterns
        query_patterns = await self._cm.execute("""
            SELECT nl_question, sql_template
            FROM v3q_config.query_patterns
            ORDER BY success_count DESC, last_used_at DESC
            LIMIT 10
        """)

        if query_patterns:
            parts.append("=== EXAMPLE QUERY PATTERNS (previously successful) ===")
            parts.append("")
            for qp in query_patterns:
                parts.append(f"  Q: {qp['nl_question']}")
                parts.append(f"  SQL: {qp['sql_template']}")
                parts.append("")

        # Include domain-specific knowledge if available
        try:
            import json
            dk_row = await self._cm.fetch_one(
                "SELECT value FROM v3q_config.app_settings WHERE key = 'domain_knowledge'",
            )
            if dk_row:
                items = json.loads(dk_row["value"]) if isinstance(dk_row["value"], str) else dk_row["value"]
                if isinstance(items, list) and items:
                    parts.append("=== DOMAIN-SPECIFIC KNOWLEDGE ===")
                    parts.append("Important context about this database:")
                    parts.append("")
                    for item in items:
                        parts.append(f"  - {item}")
                    parts.append("")
        except Exception:
            pass  # Non-critical

        return "\n".join(parts)
