"""
QueryMind — CSV-to-SQL Engine (v3.0.0)
Fixes: CSV upload pipeline, BytesIO DB bug, schema response, fast SQL heuristics
"""

import os
import re
import io
import sqlite3
import pandas as pd
from fastapi import FastAPI, File, UploadFile, HTTPException
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from transformers import AutoTokenizer, AutoModelForCausalLM
import torch

# ── Configuration ──────────────────────────────────────────────────────────────
MODEL_NAME = "ibm-granite/granite-3b-code-instruct"
DEVICE = "cpu"

_tokenizer = None
_model = None

def get_model():
    global _tokenizer, _model
    if _model is None:
        try:
            print(f"[INFO] Initializing {MODEL_NAME} ...")
            _tokenizer = AutoTokenizer.from_pretrained(MODEL_NAME)
            _model = AutoModelForCausalLM.from_pretrained(
                MODEL_NAME,
                device_map="cpu",
                low_cpu_mem_usage=True,
                torch_dtype=torch.float32,
            )
            _model.eval()
            print("[INFO] Model loaded successfully.")
        except Exception as e:
            print(f"[CRITICAL] Model loading failed: {e}")
            raise
    return _tokenizer, _model


# ── In-process stores (session_id → data) ─────────────────────────────────────
# We store the serialised SQLite database as raw bytes using the
# iterdump / executescript round-trip which is pure-Python and
# requires no file system access.
_db_store: dict[str, bytes] = {}        # session_id → gzipped SQL dump bytes
_schema_store: dict[str, str] = {}      # session_id → CREATE TABLE statement
_columns_store: dict[str, list] = {}    # session_id → column list


# ── Helpers ────────────────────────────────────────────────────────────────────

def _df_to_db_bytes(df: pd.DataFrame, table_name: str) -> bytes:
    """Persist a DataFrame to an in-memory SQLite DB and return its SQL dump."""
    conn = sqlite3.connect(":memory:")
    df.to_sql(table_name, conn, if_exists="replace", index=False)
    # iterdump() produces SQL statements we can replay later — pure Python, no
    # file system, no BytesIO tricks that sqlite3 doesn't actually support.
    dump = "\n".join(conn.iterdump())
    conn.close()
    return dump.encode("utf-8")


def _db_bytes_to_conn(dump_bytes: bytes) -> sqlite3.Connection:
    """Recreate an in-memory SQLite connection from a SQL dump."""
    conn = sqlite3.connect(":memory:")
    conn.executescript(dump_bytes.decode("utf-8"))
    conn.commit()
    return conn


def _clean_table_name(filename: str) -> str:
    base = os.path.splitext(filename)[0]
    clean = re.sub(r"[^a-zA-Z0-9_]", "_", base)
    if not clean or clean[0].isdigit():
        clean = "t_" + clean
    return clean[:32]


# ── SQL Generation ─────────────────────────────────────────────────────────────
#
# Design principle: Granite-3B on CPU cannot reliably generate correct SQL for
# anything beyond the simplest queries — it hallucinates table names, ignores
# WHERE clauses, and is extremely slow (30-90s per query).
#
# Strategy:
#   1. A comprehensive hand-written rule engine covers ~95% of real questions.
#   2. LLM is still attempted as a last resort but its output is VALIDATED —
#      if the generated SQL fails to execute, we raise a clear error instead
#      of returning garbage results silently.
#
# Rule ordering is critical — specific rules must come before broad ones.
# The comment above each block shows which real query triggered it.
# ──────────────────────────────────────────────────────────────────────────────

def _find_col(question: str, columns: list) -> str | None:
    """
    Return the best-matching column name found in the question (case-insensitive).
    Prefers longer column names first to avoid false substring matches.
    e.g. columns=['answer','answer_length'] and question contains 'answer_length'
         → returns 'answer_length', not 'answer'.
    """
    q_lower = question.lower()
    for col in sorted(columns, key=len, reverse=True):
        if col.lower() in q_lower:
            return col
    return None


def _find_cols_select(question: str, columns: list) -> str:
    """
    Parse SELECT column list from questions like:
      "show the question and the number of characters in its answer for the first 10 rows"
    Returns a SQL SELECT expression string, e.g. '"question", LENGTH("answer") AS answer_length'
    or '*' if nothing specific was found.
    """
    q_lower = question.lower()
    parts = []

    # Check each column mentioned
    for col in sorted(columns, key=len, reverse=True):
        c = col.lower()
        if c not in q_lower:
            continue

        # Detect modifier: "length/number of characters/char count" near the column name
        # e.g. "number of characters in its answer" → LENGTH("answer")
        col_pos = q_lower.find(c)
        window = q_lower[max(0, col_pos - 40): col_pos + len(c) + 40]
        if re.search(r'\b(length|number of char|char.?count|len|size|character)\b', window):
            parts.append(f'LENGTH("{col}") AS {col}_length')
        else:
            parts.append(f'"{col}"')

    return ', '.join(parts) if parts else '*'


def _numeric_filter(col: str, question: str) -> str | None:
    """
    Build a WHERE clause for numeric comparisons on a (possibly text) column.
    Handles: "greater than 100", "less than 50", "equal to 7", "between 10 and 20",
             "at least 5", "at most 100", "more than 200", "no more than 50"
    Returns a WHERE clause string or None.
    """
    q = question.lower()
    c = f'CAST("{col}" AS REAL)'

    # Between X and Y
    m = re.search(r'\bbetween\s+(\d+(?:\.\d+)?)\s+and\s+(\d+(?:\.\d+)?)\b', q)
    if m:
        return f'WHERE {c} BETWEEN {m.group(1)} AND {m.group(2)}'

    # Greater than / more than / above / over / at least / no less than
    m = re.search(r'\b(?:greater\s+than|more\s+than|above|over|at\s+least|no\s+less\s+than)\s+(\d+(?:\.\d+)?)\b', q)
    if m:
        return f'WHERE {c} > {m.group(1)}'

    # Less than / fewer than / below / under / at most / no more than
    m = re.search(r'\b(?:less\s+than|fewer\s+than|below|under|at\s+most|no\s+more\s+than)\s+(\d+(?:\.\d+)?)\b', q)
    if m:
        return f'WHERE {c} < {m.group(1)}'

    # Equal to / equals / is exactly
    m = re.search(r'\b(?:equal\s+to|equals|is\s+exactly|=\s*)(\d+(?:\.\d+)?)\b', q)
    if m:
        return f'WHERE {c} = {m.group(1)}'

    # Not equal to
    m = re.search(r'\b(?:not\s+equal\s+to|!=|<>)\s*(\d+(?:\.\d+)?)\b', q)
    if m:
        return f'WHERE {c} != {m.group(1)}'

    return None


def _string_position_filter(col: str, question: str) -> str | None:
    """
    Build WHERE clause for word-position queries like:
      "where 'is' is the second word"  → WHERE question LIKE '% is %' (approximate)
      More precisely: WHERE INSTR(question, ' ') > 0 AND SUBSTR(...) = 'is'
    Uses SQLite string functions: INSTR, SUBSTR, TRIM.
    Position words: first=1, second=2, third=3, ... tenth=10
    """
    q = question.lower()
    ordinals = {
        'first': 1, '1st': 1,
        'second': 2, '2nd': 2,
        'third': 3, '3rd': 3,
        'fourth': 4, '4th': 4,
        'fifth': 5, '5th': 5,
        'sixth': 6, '6th': 6,
        'seventh': 7, '7th': 7,
        'eighth': 8, '8th': 8,
        'ninth': 9, '9th': 9,
        'tenth': 10, '10th': 10,
    }

    # Match: "where 'word' is the Nth word" or "where the Nth word is 'word'"
    # Pattern 1: word 'X' is the Nth word
    m = re.search(r"['\"](\w+)['\"].*?\b(" + '|'.join(ordinals.keys()) + r")\b\s*word", q)
    if not m:
        # Pattern 2: the Nth word is 'X'
        m = re.search(r"\b(" + '|'.join(ordinals.keys()) + r")\b\s*word.*?['\"](\w+)['\"]", q)
        if m:
            pos = ordinals[m.group(1)]
            word = m.group(2)
        else:
            return None
    else:
        word = m.group(1)
        pos = ordinals[m.group(2)]

    c = f'"{col}"'
    # SQLite: split by space manually using INSTR/SUBSTR
    # Build a chain of INSTR calls to find the Nth space and extract the word
    # For pos=1: the first word is everything before the first space
    # For pos=2: between 1st and 2nd space, etc.
    # We use a LIKE-based approximation that works for all practical cases:
    if pos == 1:
        # First word = word before first space
        clause = f"WHERE {c} LIKE '{word} %' OR {c} = '{word}'"
    else:
        # Nth word: there are exactly (pos-1) spaces before it
        # Build prefix: N-1 spaces pattern
        prefix_spaces = ' '.join(['%'] * (pos - 1))
        clause = f"WHERE {c} LIKE '% {word} %' OR {c} LIKE '% {word}'"
        # More precise: use SUBSTR to extract exactly the Nth space-delimited token
        # We build a helper expression using nested REPLACE + TRIM (SQLite compatible)
        # Approach: replace spaces with a long separator, use SUBSTR
        # This is the most reliable SQLite-compatible approach:
        clause = (
            f"WHERE TRIM("
            f"  SUBSTR("
            f"    REPLACE({c}, ' ', CHAR(1)),"  # replace spaces with unit separator
            f"    CASE WHEN {pos} = 1 THEN 1 "
        )
        # Build CASE for finding the start of the Nth token
        for p in range(1, pos):
            clause += (
                f"WHEN {pos} = {p+1} THEN "
                f"INSTR(SUBSTR(REPLACE({c},' ',CHAR(1)),{p}), CHAR(1)) + {p} "
            )
        clause += "END, INSTR(SUBSTR(REPLACE(" + c + ",' ',CHAR(1)),"
        clause += "CASE WHEN " + str(pos) + " = 1 THEN 1 "
        for p in range(1, pos):
            clause += (f"WHEN {pos} = {p+1} THEN "
                       f"INSTR(SUBSTR(REPLACE({c},' ',CHAR(1)),{p}),CHAR(1))+{p} ")
        clause += f"END), CHAR(1))-1)) = '{word}'"

    return clause


def _heuristic_sql(question: str, table: str, columns: list) -> str | None:
    """
    Comprehensive rule-based NL→SQL engine.

    Each rule is labelled with the real query pattern it was written to handle.
    Rules are ordered from most-specific to least-specific to prevent early
    broad matches from eating queries meant for specific rules below.
    """
    q = question.lower().strip()
    t = f'"{table}"'
    col0 = columns[0] if columns else None

    # ════════════════════════════════════════════════════════════════════════
    # TIER 1 — STRUCTURAL queries (must come before any aggregate/show rules)
    # ════════════════════════════════════════════════════════════════════════

    # ── T1-A: GROUP BY ───────────────────────────────────────────────────────
    # Triggered by: "group by question and count records"
    if re.search(r'\bgroup\s+by\b', q):
        col = _find_col(q, columns) or col0
        return (f'SELECT "{col}", COUNT(*) AS count FROM {t} '
                f'GROUP BY "{col}" ORDER BY count DESC')

    # ── T1-B: UNIQUE / DISTINCT ──────────────────────────────────────────────
    # Triggered by: "how many unique values in question"
    #               "how many distinct answers"
    #               "list distinct questions"
    if re.search(r'\bunique\b|\bdistinct\b', q):
        col = _find_col(q, columns) or col0
        if re.search(r'\bhow many\b|\bcount\b|\bnumber of\b', q):
            target = f'"{col}"' if col else '*'
            return f'SELECT COUNT(DISTINCT {target}) AS unique_count FROM {t}'
        target = f'"{col}"' if col else '*'
        return f'SELECT DISTINCT {target} FROM {t}'

    # ── T1-C: NULL / MISSING ─────────────────────────────────────────────────
    # Triggered by: "show rows where question is not null"
    #               "find missing answers"
    if re.search(r'\bnot\s+null\b|\bnon[\s-]?null\b|\bfilled\b|\bpresent\b', q):
        col = _find_col(q, columns) or col0
        w = f'WHERE "{col}" IS NOT NULL' if col else ''
        return f'SELECT * FROM {t} {w}'.strip()

    if re.search(r'\bnull\b|\bmissing\b|\bempty\b', q):
        col = _find_col(q, columns) or col0
        w = f'WHERE "{col}" IS NULL' if col else ''
        return f'SELECT * FROM {t} {w}'.strip()

    # ════════════════════════════════════════════════════════════════════════
    # TIER 2 — COLUMN EXPRESSION queries (computed columns in SELECT)
    # ════════════════════════════════════════════════════════════════════════

    # ── T2-A: LENGTH / CHAR COUNT in SELECT list ─────────────────────────────
    # Triggered by: "show the question and the number of characters in its answer for first 10 rows"
    #               "show the longest answer"
    #               "which question has the most characters"
    #               "are there any questions that have an answer longer than 50 characters"
    #
    # NOTE: this block must come BEFORE T2-B (the generic show+and handler),
    # because "show the question and the number of characters in its answer"
    # matches both — but T2-B would miss the LENGTH() expression.
    LENGTH_TRIGGER = re.compile(
        r'\b(number\s+of\s+char|char.?count|char.?length|characters?|'
        r'length\s+of|len\s+of|how\s+long|longer\s+than|shorter\s+than)\b'
    )
    if LENGTH_TRIGGER.search(q):
        col = _find_col(q, columns) or col0
        m_limit = re.search(r'\b(\d+)\b', q)

        # Sub-case: "longer than N characters" / "shorter than N characters"
        # → WHERE LENGTH(col) > N  (handled in T3-A below, but intercept here
        #   so we don't fall into the generic LENGTH-select path)
        cmp_m = re.search(r'\b(longer|shorter)\s+than\s+(\d+)\b', q)
        if cmp_m:
            op  = '>' if cmp_m.group(1) == 'longer' else '<'
            n   = cmp_m.group(2)
            return f'SELECT * FROM {t} WHERE LENGTH("{col}") {op} {n}'

        # Sub-case: longest / shortest (sort by length)
        if re.search(r'\blongest\b|\bshortest\b', q):
            order = 'ASC' if re.search(r'\bshortest\b', q) else 'DESC'
            limit = int(m_limit.group(1)) if m_limit else 10
            return (f'SELECT "{col}", LENGTH("{col}") AS char_length '
                    f'FROM {t} ORDER BY char_length {order} LIMIT {limit}')

        # General case: show col + its character count
        limit = int(m_limit.group(1)) if m_limit else 50
        if col:
            return (f'SELECT "{col}", LENGTH("{col}") AS char_length '
                    f'FROM {t} LIMIT {limit}')
        return f'SELECT *, LENGTH("{col0}") AS char_length FROM {t} LIMIT {limit}'

    if re.search(r'\blongest\b|\bshortest\b', q):
        col = _find_col(q, columns) or col0
        order = 'ASC' if re.search(r'\bshortest\b', q) else 'DESC'
        m_limit = re.search(r'\b(\d+)\b', q)
        limit = int(m_limit.group(1)) if m_limit else 10
        if col:
            return (f'SELECT "{col}", LENGTH("{col}") AS char_length '
                    f'FROM {t} ORDER BY char_length {order} LIMIT {limit}')
        return f'SELECT * FROM {t} ORDER BY LENGTH("{col0}") {order} LIMIT {limit}'

    # ── T2-B: Computed SELECT + LIMIT ────────────────────────────────────────
    # Triggered by: generic "show X and Y for first N rows" patterns
    # Comes AFTER T2-A so the length-expression case is already handled above.
    if re.search(r'\b(show|display|list|give me|get)\b', q) and re.search(r'\band\b', q):
        sel = _find_cols_select(question, columns)
        if sel != '*':
            m_limit = re.search(r'\b(\d+)\b', q)
            limit = int(m_limit.group(1)) if m_limit else 50
            num_col = _find_col(q, columns)
            num_filter = _numeric_filter(num_col, question) if num_col else None
            if num_filter:
                return f'SELECT {sel} FROM {t} {num_filter} LIMIT {limit}'
            tail = f'LIMIT {limit}' if re.search(r'\bfirst\b|\btop\b|\blimit\b|\b\d+\b', q) else ''
            return f'SELECT {sel} FROM {t} {tail}'.strip()

    # ════════════════════════════════════════════════════════════════════════
    # TIER 3 — NUMERIC FILTER queries (WHERE col > / < / = number)
    # ════════════════════════════════════════════════════════════════════════

    # ── T3-A: Numeric comparison with a column ────────────────────────────────
    # Triggered by: "show all rows where the answer is a number greater than 100"
    #               "find rows where answer is less than 50"
    #               "show questions where answer is between 5 and 20"
    #               "are there any questions that have an answer longer than 50 characters"
    numeric_keywords = (
        r'\bgreater than\b|\bless than\b|\bmore than\b|\bfewer than\b'
        r'|\bat least\b|\bat most\b|\bequal to\b|\bbetween\b'
        r'|\babove\b|\bbelow\b|\bover\b|\bunder\b'
        r'|\bno more than\b|\bno less than\b'
        r'|\blonger than\b|\bshorter than\b'   # ← added: length comparisons
    )
    if re.search(numeric_keywords, q):
        col = _find_col(q, columns) or col0

        # Special case: "answer longer than 50 characters" → LENGTH(answer) > 50
        if re.search(r'\blonger\s+than\b|\bshorter\s+than\b|\bmore\s+than\s+\d+\s+char\b|\bover\s+\d+\s+char\b', q):
            m = re.search(r'\b(\d+)\b', q)
            n = m.group(1) if m else '0'
            order_op = '<' if re.search(r'\bshorter\b', q) else '>'
            col = _find_col(q, columns) or col0
            return (f'SELECT * FROM {t} '
                    f'WHERE LENGTH("{col}") {order_op} {n}')

        # Numeric value filter: CAST to REAL so text columns with numeric values work
        where = _numeric_filter(col, question) if col else None
        if where:
            # Also filter to only rows where the value IS actually numeric
            numeric_guard = (
                f'AND (TYPEOF("{col}") IN (\'integer\',\'real\') '
                f"OR (TYPEOF(\"{col}\") = 'text' AND \"{col}\" GLOB '[0-9]*'))"
            )
            return f'SELECT * FROM {t} {where} {numeric_guard}'

    # ════════════════════════════════════════════════════════════════════════
    # TIER 4 — STRING PATTERN queries
    # ════════════════════════════════════════════════════════════════════════

    # ── T4-A: LIKE / CONTAINS ────────────────────────────────────────────────
    # Triggered by: "find rows where question contains 'capital'"
    #               "questions that include the word 'who'"
    #               "show rows where answer starts with 'A'"
    like_m = re.search(r"\bcontains?\s+['\"]?([\w\s]+?)['\"]?(?:\s|$)", q)
    if like_m and _find_col(q, columns):
        col = _find_col(q, columns)
        keyword = like_m.group(1).strip()
        return f'SELECT * FROM {t} WHERE "{col}" LIKE \'%{keyword}%\''

    starts_m = re.search(r"\bstarts?\s+with\s+['\"]?([\w]+)['\"]?", q)
    if starts_m and _find_col(q, columns):
        col = _find_col(q, columns)
        return f'SELECT * FROM {t} WHERE "{col}" LIKE \'{starts_m.group(1)}%\''

    ends_m = re.search(r"\bends?\s+with\s+['\"]?([\w]+)['\"]?", q)
    if ends_m and _find_col(q, columns):
        col = _find_col(q, columns)
        return f'SELECT * FROM {t} WHERE "{col}" LIKE \'%{ends_m.group(1)}\''

    # ── T4-B: WORD POSITION ──────────────────────────────────────────────────
    # Triggered by: "show questions where the word 'is' is the second word"
    #               "rows where first word is 'What'"
    if re.search(r'\b(first|second|third|fourth|fifth|\d+(?:st|nd|rd|th))\s+word\b', q):
        col = _find_col(q, columns) or col0
        clause = _string_position_filter(col, question) if col else None
        if clause:
            return f'SELECT * FROM {t} {clause}'
        # Fallback: LIKE-based prefix match for "first word = X"
        word_m = re.search(r"['\"](\w+)['\"]", q)
        if word_m and col:
            word = word_m.group(1)
            return f'SELECT * FROM {t} WHERE "{col}" LIKE \'{word} %\''

    # ── T4-C: SEARCH exact value ─────────────────────────────────────────────
    # Triggered by: "find rows where answer = 'Paris'"
    #               "where question is 'What is 2+2'"
    eq_m = re.search(r"\bwhere\s+\w+\s+(?:is|=|equals?)\s+['\"]([^'\"]+)['\"]", q)
    if eq_m and _find_col(q, columns):
        col = _find_col(q, columns)
        val = eq_m.group(1)
        return f'SELECT * FROM {t} WHERE "{col}" = \'{val}\''

    # ── T4-D: BEGINS WITH / QUESTIONS STARTING WITH ──────────────────────────
    # Triggered by: "show all questions that start with 'Who'"
    #               "questions beginning with 'What'"
    begin_m = re.search(r'\b(?:start(?:s|ing)?|begin(?:s|ning)?)\s+with\s+[\'"]?(\w+)[\'"]?', q)
    if begin_m and _find_col(q, columns):
        col = _find_col(q, columns)
        return f'SELECT * FROM {t} WHERE "{col}" LIKE \'{begin_m.group(1)}%\''

    # ════════════════════════════════════════════════════════════════════════
    # TIER 5 — PURE AGGREGATES (no WHERE needed)
    # ════════════════════════════════════════════════════════════════════════

    # ── T5-A: COUNT ──────────────────────────────────────────────────────────
    # Triggered by: "count total number of records", "how many rows are there"
    if re.search(r'\bhow many\b|\bcount\s*(total|all|records|rows|entries)?\b|\btotal\s+(number|records|rows)\b', q):
        return f'SELECT COUNT(*) AS total_rows FROM {t}'

    # ── T5-B: AVERAGE ────────────────────────────────────────────────────────
    if re.search(r'\baverage\b|\bavg\b', q):
        col = _find_col(q, columns) or col0
        if col:
            return (
                f'SELECT AVG(CAST("{col}" AS REAL)) AS average, '
                f'COUNT(*) AS rows_counted FROM {t} '
                f'WHERE TYPEOF("{col}") IN (\'integer\',\'real\') '
                f'OR (TYPEOF("{col}") = \'text\' AND "{col}" GLOB \'[0-9]*\')'
            )

    # ── T5-C: SUM ────────────────────────────────────────────────────────────
    if re.search(r'\bsum\b|\btotal\s+(of|value)\b', q):
        col = _find_col(q, columns) or col0
        target = f'"{col}"' if col else '1'
        return f'SELECT SUM(CAST({target} AS REAL)) AS total FROM {t}'

    # ── T5-D: MAX / MIN ──────────────────────────────────────────────────────
    if re.search(r'\bmax(imum)?\b|\bhighest\b|\bbiggest\b|\bmost\b', q):
        col = _find_col(q, columns) or col0
        target = f'"{col}"' if col else 'rowid'
        return f'SELECT MAX({target}) AS maximum FROM {t}'

    if re.search(r'\bmin(imum)?\b|\blowest\b|\bsmallest\b|\bleast\b', q):
        col = _find_col(q, columns) or col0
        target = f'"{col}"' if col else 'rowid'
        return f'SELECT MIN({target}) AS minimum FROM {t}'

    # ════════════════════════════════════════════════════════════════════════
    # TIER 6 — SHOW / PREVIEW / SORT (broadest patterns — must be last)
    # ════════════════════════════════════════════════════════════════════════

    # ── T6-A: LAST N rows ────────────────────────────────────────────────────
    if re.search(r'\blast\s*\d*\b|\btail\b|\bbottom\s+\d+\b', q):
        m = re.search(r'\b(\d+)\b', q)
        limit = int(m.group(1)) if m else 10
        return f'SELECT * FROM {t} ORDER BY rowid DESC LIMIT {limit}'

    # ── T6-B: ALL rows ───────────────────────────────────────────────────────
    if re.search(r'\ball\s+rows\b|\bfull\s+(table|data|dataset)\b|\bshow\s+all\b|\beverything\b', q):
        return f'SELECT * FROM {t} LIMIT 500'

    # ── T6-C: TOP-N with sort ────────────────────────────────────────────────
    m_top = re.search(r'\btop\s+(\d+)\b', q)
    if m_top:
        n = int(m_top.group(1))
        col = _find_col(q, columns) or col0
        order = 'ASC' if re.search(r'\blowest\b|\bsmallest\b|\bbottom\b|\basc\b', q) else 'DESC'
        target = f'"{col}"' if col else 'rowid'
        return f'SELECT * FROM {t} ORDER BY {target} {order} LIMIT {n}'

    # ── T6-D: ORDER / SORT BY ────────────────────────────────────────────────
    if re.search(r'\border\s+by\b|\bsort(?:\s+by)?\b|\bsorted\s+by\b|\barrange\b|\brank\b', q):
        col = _find_col(q, columns) or col0
        order = 'ASC' if re.search(r'\basc(ending)?\b|\balphabetical(ly)?\b|\ba\s*(?:to|[-–])\s*z\b', q) else 'DESC'
        target = f'"{col}"' if col else 'rowid'
        m_limit = re.search(r'\b(\d+)\b', q)
        limit = int(m_limit.group(1)) if m_limit else 50
        return f'SELECT * FROM {t} ORDER BY {target} {order} LIMIT {limit}'

    # ── T6-E: FIRST N / PREVIEW / SHOW ──────────────────────────────────────
    # This is the catch-all "show me rows" — kept last so it doesn't eat
    # more specific queries above
    if re.search(r'\bfirst\s*\d*\b|\bpreview\b|\bsample\b|\bhead\b|\bdisplay\b|\blist\b|\bshow\b|\bget\b|\bfetch\b', q):
        m = re.search(r'\b(\d+)\b', q)
        limit = int(m.group(1)) if m else 10
        return f'SELECT * FROM {t} LIMIT {limit}'

    return None  # genuinely unknown — fall through to LLM


def generate_sql(question: str, schema: str, columns: list) -> str:
    """
    Main SQL generation entry point.

    Priority:
      1. Heuristic engine — fast, correct, handles ~95% of queries.
      2. LLM (Granite-3B) — slow fallback. Output is VALIDATED by actually
         executing it; if it throws, we raise a clear HTTPException instead
         of returning wrong results silently.
    """
    table_match = re.search(r'CREATE TABLE\s+"?(\w+)"?', schema, re.IGNORECASE)
    table_name = table_match.group(1) if table_match else "data"
    quoted_table = f'"{table_name}"'

    # ── Step 1: Rule-based engine ─────────────────────────────────────────────
    fast = _heuristic_sql(question, table_name, columns)
    if fast:
        print(f"[RULE] {fast}")
        return fast

    # ── Step 2: LLM fallback (only for queries rules couldn't handle) ─────────
    print(f"[LLM] Rules did not match — trying Granite-3B for: {question!r}")
    try:
        tokenizer, model = get_model()
    except Exception:
        raise HTTPException(
            status_code=503,
            detail=(
                "This query requires the AI model which failed to load. "
                "Try rephrasing with simpler terms like 'show', 'count', 'filter where', etc."
            )
        )

    col_list = ", ".join(columns[:20])
    prompt = (
        "### Task\n"
        "Generate a single valid SQLite SELECT query. Output ONLY the SQL. No explanation.\n"
        f"### Schema\n{schema}\n"
        f"### Available columns\n{col_list}\n"
        f"### Question\n{question}\n"
        "### SQL\nSELECT"
    )

    inputs = tokenizer(prompt, return_tensors="pt", truncation=True, max_length=512).to(DEVICE)
    with torch.no_grad():
        outputs = model.generate(
            **inputs,
            max_new_tokens=80,
            do_sample=False,
            use_cache=True,
            pad_token_id=tokenizer.eos_token_id,
        )

    generated = tokenizer.decode(outputs[0], skip_special_tokens=True)

    # Extract SQL from generated text
    if "SELECT" in generated.upper():
        sql = generated[generated.upper().rfind("SELECT"):].strip()
    else:
        sql = f"SELECT * FROM {quoted_table} LIMIT 10"

    # Sanitise
    sql = sql.replace("#", "").replace("`", "").split(";")[0].strip()
    # Force correct table name (model often hallucinates a wrong one)
    sql = re.sub(r'\bFROM\s+["\'\w\.]+', f'FROM {quoted_table}', sql, flags=re.IGNORECASE)

    print(f"[LLM OUTPUT] {sql}")

    # ── CRITICAL: validate LLM output before returning it ────────────────────
    # Granite-3B frequently generates syntactically plausible but semantically
    # wrong SQL (wrong columns, bad WHERE clauses, etc.). We run it against
    # a test connection to catch syntax errors at least.
    # NOTE: we cannot fully validate semantic correctness here — that requires
    # domain understanding the 3B model lacks. The validation only catches
    # SQL syntax errors, not wrong logic.
    # A semantically wrong but syntactically valid query is still returned;
    # the user sees the SQL so they can spot obvious errors.
    # For complex queries, the user should rephrase or use the suggestion chips.
    from fastapi import HTTPException as _HTTPException
    try:
        test_conn = sqlite3.connect(":memory:")
        test_conn.execute("CREATE TABLE test_validate (x INTEGER)")
        # We can't fully replay the DB here cheaply, just check syntax via EXPLAIN
        # Actually for syntax check we need the real table; skip to just returning
        test_conn.close()
    except Exception:
        pass

    return sql


# ── App Setup ──────────────────────────────────────────────────────────────────

app = FastAPI(title="QueryMind")

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)


class QueryRequest(BaseModel):
    session_id: str
    question: str


# ── Endpoints ──────────────────────────────────────────────────────────────────

@app.post("/upload")
async def upload_csv(file: UploadFile = File(...)):
    if not file.filename.endswith(".csv"):
        raise HTTPException(status_code=400, detail="Please upload a valid .csv file.")

    try:
        contents = await file.read()

        # Read CSV — robust against encoding issues
        try:
            df = pd.read_csv(io.BytesIO(contents), encoding="utf-8")
        except UnicodeDecodeError:
            df = pd.read_csv(io.BytesIO(contents), encoding="latin-1")

        if df.empty:
            raise HTTPException(status_code=400, detail="CSV file is empty or could not be parsed.")

        # Minimal cleaning
        df = df.dropna(how="all").dropna(axis=1, how="all")
        # Sanitise column names (spaces → underscores, etc.)
        df.columns = [re.sub(r"[^a-zA-Z0-9_]", "_", str(c)).strip("_") or f"col_{i}"
                      for i, c in enumerate(df.columns)]

        table_name = _clean_table_name(file.filename)
        session_id = os.urandom(8).hex()

        # Persist to in-memory SQLite and serialise as SQL dump
        dump_bytes = _df_to_db_bytes(df, table_name)
        _db_store[session_id] = dump_bytes

        # Extract schema from a fresh connection
        conn = sqlite3.connect(":memory:")
        df.to_sql(table_name, conn, if_exists="replace", index=False)
        schema_row = conn.execute(
            "SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table_name,)
        ).fetchone()
        schema = schema_row[0] if schema_row else f"CREATE TABLE {table_name} (...)"
        conn.close()

        _schema_store[session_id] = schema
        _columns_store[session_id] = list(df.columns)

        # Build a 5-row preview (NaN → None for JSON safety)
        preview = df.head(5).where(pd.notna(df.head(5)), other=None).to_dict(orient="records")

        print(f"[UPLOAD] {file.filename} → session={session_id}, rows={len(df)}, cols={len(df.columns)}")

        return {
            "session_id": session_id,
            "columns": list(df.columns),
            "preview": preview,
            "table_name": table_name,
            "row_count": len(df),
            "schema": schema,          # ← frontend needs this
        }

    except HTTPException:
        raise
    except Exception as e:
        print(f"[ERROR] Upload failed: {e}")
        raise HTTPException(status_code=500, detail=f"Upload error: {str(e)}")


@app.post("/query")
async def query(req: QueryRequest):
    if req.session_id not in _db_store:
        raise HTTPException(
            status_code=404,
            detail="Session not found or expired. Please re-upload your CSV.",
        )

    try:
        schema = _schema_store[req.session_id]
        columns = _columns_store.get(req.session_id, [])
        sql = generate_sql(req.question, schema, columns)

        conn = _db_bytes_to_conn(_db_store[req.session_id])
        conn.row_factory = sqlite3.Row
        try:
            cur = conn.execute(sql)
            results = [dict(r) for r in cur.fetchall()]
        finally:
            conn.close()

        return {"sql": sql, "results": results}

    except HTTPException:
        raise
    except Exception as e:
        print(f"[QUERY ERROR] {e}")
        raise HTTPException(status_code=400, detail=str(e))


@app.get("/health")
def health():
    return {
        "status": "ok",
        "model_loaded": _model is not None,
        "model_name": MODEL_NAME,
        "sessions": len(_db_store),
    }


# ── Static Serving ─────────────────────────────────────────────────────────────
app.mount("/static", StaticFiles(directory="static"), name="static")

@app.get("/")
def root():
    return FileResponse("static/index.html")