import logging
from typing import Any

from semantic_models.schemas import SemanticModelSpec

logger = logging.getLogger(__name__)

MAX_PROMPT_SAMPLE_VALUES = 3
MAX_PROMPT_SAMPLE_VALUE_CHARS = 30
MAX_PROMPT_SAMPLE_COLUMNS_PER_ENTITY = 40


def _format_sample_values(values: list[Any]) -> str:
    preview: list[str] = []
    for value in values[:MAX_PROMPT_SAMPLE_VALUES]:
        text = str(value)
        if len(text) > MAX_PROMPT_SAMPLE_VALUE_CHARS:
            text = text[: MAX_PROMPT_SAMPLE_VALUE_CHARS - 3] + "..."
        preview.append(f"'{text}'")

    if len(values) > MAX_PROMPT_SAMPLE_VALUES:
        preview.append(f"... (+{len(values) - MAX_PROMPT_SAMPLE_VALUES} more)")

    return ", ".join(preview)


def generate_sql(
    question: str,
    entities: list[dict],
    relationships: list[dict],
    glossary_terms: list[dict],
    dialect: str,
    llm_id: str,
    corrections: list[dict] | None = None,
    vocabulary_terms: list[dict] | None = None,
    golden_queries: list[dict] | None = None,
    model_instructions: str | None = None,
    trace_span: Any | None = None,
) -> dict[str, Any]:
    """
    Generate SQL from natural language using focused schema context.

    This tool builds a prompt with only the selected entities (not full spec)
    and calls the LLM to generate SQL.

    Args:
        question: User's natural language question
        entities: List of entity details (from get_entity_attributes tool)
        relationships: List of relationship details (from get_relationships tool)
        glossary_terms: List of glossary terms for business context
        dialect: SQL dialect (e.g., "postgresql", "snowflake")
        llm_id: LLM ID to use for generation
        corrections: Value corrections from resolve_values tool
        vocabulary_terms: List of general vocabulary terms for business context
        golden_queries: List of example question/SQL pairs for few-shot learning
        model_instructions: Model-level instructions

    Returns:
        {"sql": "SELECT ..."}
    """
    from semantic_models.services.sql_generation import call_llm_with_trace, clean_sql_response

    logger.info("=" * 60)
    logger.info("TOOL: generate_sql")
    logger.info("=" * 60)
    logger.info("  question: %s", question[:80] + "..." if len(question) > 80 else question)
    logger.info("  entities: %s", [e.get("entity_id") for e in entities])
    logger.info("  relationships: %d", len(relationships))
    logger.info("  glossary_terms: %d", len(glossary_terms) if glossary_terms else 0)
    logger.info("  vocabulary_terms: %d", len(vocabulary_terms) if vocabulary_terms else 0)
    logger.info("  golden_queries: %d", len(golden_queries) if golden_queries else 0)
    logger.info("  corrections: %d", len(corrections) if corrections else 0)
    logger.info("  dialect: %s", dialect)
    logger.info("  llm_id: %s", llm_id)
    sample_value_columns = 0
    sample_value_count = 0
    for entity in entities:
        for attr in entity.get("attributes", []):
            values = attr.get("manualValues") or []
            if values and attr.get("resolveInUserRequests", False):
                sample_value_columns += 1
                sample_value_count += len(values)
    logger.info(
        "  sample_values: %d columns / %d values",
        sample_value_columns,
        sample_value_count,
    )

    # Build focused prompt
    prompt = _build_focused_prompt(
        question=question,
        entities=entities,
        relationships=relationships,
        glossary_terms=glossary_terms,
        vocabulary_terms=vocabulary_terms,
        golden_queries=golden_queries,
        corrections=corrections,
        model_instructions=model_instructions,
        dialect=dialect,
    )

    logger.info("  Built prompt: %d characters", len(prompt))
    logger.info("  SQL generation prompt:\n%s", prompt)
    logger.info("  Calling LLM for SQL generation...")

    span = trace_span
    if span:
        span.attributes["llm_id"] = llm_id
        span.attributes["dialect"] = dialect
        span.inputs["question"] = question
        span.inputs["entity_ids"] = [e.get("entity_id") or e.get("name") for e in entities]
        span.inputs["corrections"] = corrections or []
        span.inputs["glossary_terms"] = glossary_terms or []
        span.inputs["prompt"] = prompt

    try:
        llm_response, llm_trace = call_llm_with_trace(llm_id, prompt)
        sql = clean_sql_response(llm_response)

        if span and llm_trace:
            span.append_trace(llm_trace)

        logger.info("  LLM response received: %d characters", len(llm_response))
        logger.info("  Generated SQL:")
        for line in sql.split("\n")[:10]:  # Log first 10 lines
            logger.info("    %s", line)
        if sql.count("\n") > 10:
            logger.info("    ... (%d more lines)", sql.count("\n") - 10)

        logger.info("TOOL RESULT: generate_sql produced SQL (%d chars)", len(sql))
        if span:
            span.outputs["llm_response"] = llm_response
            span.outputs["sql"] = sql
        return {"sql": sql}

    except Exception as e:
        logger.warning("TOOL RESULT: generate_sql FAILED: %s", e)
        if span:
            span.attributes["status"] = "error"
            span.outputs["error"] = str(e)
        return {
            "sql": None,
            "error": str(e),
        }


def _build_focused_prompt(
    question: str,
    entities: list[dict],
    relationships: list[dict],
    glossary_terms: list[dict],
    vocabulary_terms: list[dict] | None,
    golden_queries: list[dict] | None,
    corrections: list[dict] | None,
    model_instructions: str | None,
    dialect: str,
) -> str:
    """Build prompt with focused schema context.

    Builds a prompt from pre-selected entities and related context only,
    to keep the SQL generation scope small and relevant.
    """
    from datetime import date

    from semantic_models.services.sql_execution import resolve_dataset_sql_table
    from semantic_models.utils import quote_identifier

    sections = []

    # Context
    today = date.today()
    sections.append(f"## CONTEXT\nToday's date: {today.strftime('%Y-%m-%d')} ({today.strftime('%A, %B %d, %Y')})\n")

    # General instructions
    if model_instructions:
        sections.append(f"## General INSTRUCTIONS\n{model_instructions}\n")

    # Semantic model (focused)
    sections.append("## SEMANTIC MODEL\n")

    for entity in entities:
        sections.append(f"### Entity: {entity.get('name', entity.get('entity_id'))}")
        if entity.get("description"):
            sections.append(f"Description: {entity['description']}")
        # Resolve the actual SQL table name
        source_info = entity.get("source_info", {})
        dataset_ref = source_info.get("datasetRef", "")
        project_key, dataset_name = ("", "")
        if dataset_ref and "." in dataset_ref:
            project_key, dataset_name = dataset_ref.split(".", 1)
        if project_key and dataset_name:
            sql_table = resolve_dataset_sql_table(project_key, dataset_name, dialect)
        else:
            sql_table = dataset_name or entity.get("entity_id")
        sections.append(f"SQL Table: {sql_table}\n")

        # Attributes
        attributes = entity.get("attributes", [])
        physical_cols = []
        computed_attrs = []
        for attr in attributes:
            if attr.get("type") == "COLUMN":
                physical_cols.append(attr)
            else:
                computed_attrs.append(attr)

        if physical_cols:
            sections.append("Columns (physical - use exactly as shown with quotes):")
            inline_sample_columns = 0
            for attr in physical_cols:
                column_name = attr.get("column") or attr.get("name")
                quoted_col = quote_identifier(column_name, dialect)
                desc = f" - {attr['description']}" if attr.get("description") else ""
                instr = f"\n      Note: {attr['llmInstructions']}" if attr.get("llmInstructions") else ""
                values = attr.get("manualValues") or []
                source = (attr.get("sampleValuesSource") or "").upper()
                values_hint = ""
                if (
                    values
                    and attr.get("resolveInUserRequests", False)
                    and inline_sample_columns < MAX_PROMPT_SAMPLE_COLUMNS_PER_ENTITY
                ):
                    source_hint = f" [{source.lower()}]" if source else ""
                    values_hint = f"\n      Sample values{source_hint}: {_format_sample_values(values)}"
                    inline_sample_columns += 1
                sections.append(f"  - {quoted_col} ({attr.get('dssType', 'TEXT')}){desc}{instr}{values_hint}")

        if computed_attrs:
            sections.append("\nComputed Attributes (use the SQL expression directly, NOT as column reference):")
            for attr in computed_attrs:
                expr = attr.get("pseudoSQLExpression") or attr.get("name")
                desc = f" - {attr['description']}" if attr.get("description") else ""
                instr = f"\n      Note: {attr['llmInstructions']}" if attr.get("llmInstructions") else ""
                sections.append(f"  - {attr.get('name')}: `{expr}`{desc}{instr}")

        # Metrics
        metrics = entity.get("metrics", [])
        if metrics:
            sections.append("\nMetrics (use these expressions for aggregations):")
            for metric in metrics:
                desc = f" - {metric['description']}" if metric.get("description") else ""
                instr = f"\n      Note: {metric['llmInstructions']}" if metric.get("llmInstructions") else ""
                sections.append(f"  - {metric.get('name')}: `{metric.get('pseudoSQLExpression')}`{desc}{instr}")

        # Filters
        filters = entity.get("filters", [])
        if filters:
            sections.append("\nFilters (use these in WHERE clause):")
            for filt in filters:
                desc = f" - {filt['description']}" if filt.get("description") else ""
                instr = f"\n      Note: {filt['llmInstructions']}" if filt.get("llmInstructions") else ""
                sections.append(f"  - {filt.get('name')}: `{filt.get('pseudoSQLExpression')}`{desc}{instr}")

        sections.append("")

    # Relationships
    if relationships:
        sections.append("## RELATIONSHIPS (join hints - pseudo-SQL, may be invalid)")
        for rel in relationships:
            desc = f" - {rel.get('description')}" if rel.get("description") else ""
            sections.append(
                f"  {rel.get('firstEntity')} ↔ {rel.get('secondEntity')}: `{rel.get('pseudoSQLExpression')}`{desc}"
            )
        sections.append("")

    # Glossary
    if glossary_terms:
        sections.append("## GLOSSARY (business vocabulary)")
        sections.append(
            "These are business term definitions. Synonyms are alternative NAMES users might say, "
            "NOT valid data values. Use the mapping to understand which schema element the term refers to.\n"
        )
        for term in glossary_terms:
            # Build mapping string from new binding structure
            entity_name = term.get("entity_name", term.get("entity_id", ""))
            target_type = term.get("target_type", "")
            target_name = term.get("target_name")

            if target_type == "entity":
                mapping_str = f" [maps to: entity {entity_name}]"
            elif target_type == "attribute" and target_name:
                mapping_str = f" [maps to: {entity_name}.{target_name}]"
            elif target_type == "metric" and target_name:
                mapping_str = f" [maps to: metric {entity_name}.{target_name}]"
            elif target_type == "filter" and target_name:
                mapping_str = f" [maps to: filter {entity_name}.{target_name}]"
            else:
                mapping_str = ""

            synonyms = term.get("synonyms") or []
            syn_str = f" (synonyms: {', '.join(synonyms)})" if synonyms else ""
            desc = f" - {term['description']}" if term.get("description") else ""
            sections.append(f'  - "{term["term"]}"{syn_str}{desc}{mapping_str}')
        sections.append("")

    # Vocabulary (general terms not bound to schema elements)
    if vocabulary_terms:
        sections.append("## VOCABULARY (general business terms)")
        sections.append(
            "These are general business terms that provide context but are NOT mapped to specific schema elements.\n"
        )
        for term in vocabulary_terms:
            synonyms = term.get("synonyms") or []
            syn_str = f" (synonyms: {', '.join(synonyms)})" if synonyms else ""
            desc = f" - {term['description']}" if term.get("description") else ""
            sections.append(f'  - "{term["term"]}"{syn_str}{desc}')
        sections.append("")

    # Value context
    if corrections:
        value_matches = [c for c in corrections if c.get("source") == "index"]
        if value_matches:
            sections.append("## VALUE CONTEXT")
            sections.append("The following terms from the question were matched to actual data values.")
            sections.append(
                "Match process: LLM interprets user term → search index (exact, then fuzzy, then semantic).\n"
            )
            for c in value_matches:
                entity_attr = f"{c.get('entity_id')}.{c.get('attribute')}"
                match_type = c.get("match_type", "unknown")
                score = c.get("score", 0)
                llm_inferred = c.get("llm_inferred", False)

                if llm_inferred:
                    sections.append(
                        f"  - \"{c.get('original')}\" → LLM interpreted as '{c.get('resolved')}' "
                        f"→ confirmed in {entity_attr} ({match_type} match, {score:.0f}%)"
                    )
                else:
                    sections.append(
                        f"  - \"{c.get('original')}\" → matched '{c.get('resolved')}' "
                        f"in {entity_attr} ({match_type} match, {score:.0f}%)"
                    )
            sections.append("\nIMPORTANT: Use the matched values exactly as shown in SQL WHERE clauses.\n")

    # Rules
    dialect_upper = dialect.upper() if dialect and dialect != "generic" else "SQL"
    sections.append(f"""## RULES
1. Generate valid {dialect_upper} SQL
2. For physical columns: use table_alias.column_name with column names exactly as shown (preserving quotes)
3. For computed attributes: use the SQL expression directly in SELECT/GROUP BY
4. For metrics: use the expression for aggregations
5. For filters: use the expression in WHERE clauses
6. If VALUE CONTEXT is provided, use those exact matched values in SQL literals
7. If sample values are listed on a column, use them as allowed literals when relevant
8. Never use glossary terms as SQL literal values - they map to schema elements
9. Return ONLY the raw SQL query - no explanations, no markdown, no code fences
10. Use table aliases for clarity
""")

    # Examples (few-shot learning from golden queries)
    if golden_queries:
        sections.append("## EXAMPLES")
        sections.append("Here are examples of questions and their expected SQL queries:\n")
        for i, gq in enumerate(golden_queries, 1):
            sections.append(f"### Example {i}")
            sections.append(f"Question: {gq['question']}")
            sections.append(f"SQL:\n```sql\n{gq['sql']}\n```\n")
        sections.append("")

    # Question
    sections.append(f"""## QUESTION
{question}

## SQL:""")

    return "\n".join(sections)


def validate_sql(
    sql: str,
    dialect: str,
    spec: SemanticModelSpec,
) -> dict[str, Any]:
    """
    Validate SQL syntax and security.

    Checks:
    - SQL is syntactically valid
    - Only SELECT statements allowed
    - All tables are in the semantic model

    Args:
        sql: The SQL query to validate
        dialect: SQL dialect for parsing
        spec: SemanticModelSpec

    Returns:
        {
            "valid": true,
            "error": null
        }
        or
        {
            "valid": false,
            "error": "Error message"
        }
    """
    from semantic_models.exceptions import ValidationError
    from semantic_models.services.sql_validation import validate_sql as validate_sql_service

    logger.info("=" * 60)
    logger.info("TOOL: validate_sql")
    logger.info("=" * 60)
    logger.info("  dialect: %s", dialect)
    logger.info("  SQL length: %d chars", len(sql))
    logger.info("  SQL preview: %s", sql[:100] + "..." if len(sql) > 100 else sql)

    try:
        validate_sql_service(sql, dialect, spec)
        logger.info("TOOL RESULT: validate_sql -> VALID")
        return {"valid": True, "error": None}
    except ValidationError as e:
        logger.warning("TOOL RESULT: validate_sql -> INVALID: %s", e)
        return {"valid": False, "error": str(e)}
    except Exception as e:
        logger.warning("TOOL RESULT: validate_sql -> ERROR: %s", e)
        return {"valid": False, "error": f"Unexpected error: {e}"}


def execute_sql(
    sql: str,
    spec: SemanticModelSpec,
    max_rows: int = 1000,
) -> dict[str, Any]:
    """
    Execute SQL against the semantic model's connection.

    Args:
        sql: The SQL query to execute
        spec: SemanticModelSpec
        max_rows: Maximum rows to return (default 1000)

    Returns:
        {
            "success": true,
            "columns": ["col1", "col2"],
            "rows": [[...], [...]],
            "row_count": 42,
            "truncated": false
        }
        or
        {
            "success": false,
            "error": "Error message"
        }
    """
    from semantic_models.exceptions import ExecutionError
    from semantic_models.services.sql_execution import execute_sql as execute_sql_service

    logger.info("=" * 60)
    logger.info("TOOL: execute_sql")
    logger.info("=" * 60)
    logger.info("  max_rows: %d", max_rows)
    logger.info("  SQL to execute:")
    for line in sql.split("\n")[:5]:  # Log first 5 lines
        logger.info("    %s", line)
    if sql.count("\n") > 5:
        logger.info("    ... (%d more lines)", sql.count("\n") - 5)

    try:
        logger.info("  Executing query...")
        columns, rows, row_count, truncated = execute_sql_service(spec, sql, max_rows)

        logger.info("  Query executed successfully!")
        logger.info("  Columns: %s", columns)
        logger.info("  Row count: %d", row_count)
        logger.info("  Truncated: %s", truncated)
        if rows:
            logger.info("  First row: %s", rows[0] if len(str(rows[0])) < 200 else str(rows[0])[:200] + "...")

        logger.info("TOOL RESULT: execute_sql -> SUCCESS (%d rows)", row_count)
        return {
            "success": True,
            "columns": columns,
            "rows": rows,
            "row_count": row_count,
            "truncated": truncated,
        }

    except ExecutionError as e:
        logger.warning("TOOL RESULT: execute_sql -> FAILED: %s", e)
        return {"success": False, "error": str(e)}
    except Exception as e:
        logger.warning("TOOL RESULT: execute_sql -> ERROR: %s", e)
        return {"success": False, "error": f"Unexpected error: {e}"}
