app.py

# Adaptive SQL Trainer — Domain Randomized with OpenAI (Gradio + SQLite)
# - OpenAI randomizes a domain and questions (fallback dataset if unavailable).
# - 3–4 related tables with seed rows installed in SQLite.
# - Students practice SELECT, WHERE, JOINs (INNER/LEFT), aliases, views, CTAS/SELECT INTO.
# - Validator enforces columns only when the prompt asks; otherwise focuses on rows.
# - ERD shows tables only (no edges) and bolds the tables you reference while typing.
# - Domain picker now round-robins across: bookstore, retail sales, wholesaler, sales tax, oil & gas wells, marketing.

import os
import re
import json
import time
import random
import sqlite3
import threading
from datetime import datetime, timezone
from typing import List, Dict, Any, Tuple, Optional, Set

import gradio as gr
import pandas as pd

# -------------------- OpenAI (optional) --------------------
OPENAI_AVAILABLE = True
DEFAULT_MODEL = os.getenv("OPENAI_MODEL")  # optional override
try:
    from openai import OpenAI
    _client = OpenAI()  # requires OPENAI_API_KEY
except Exception:
    OPENAI_AVAILABLE = False
    _client = None

def _candidate_models():
    base = [
        (DEFAULT_MODEL or "").strip() or None,
        "gpt-4o-mini",
        "gpt-4o",
        "gpt-4.1-mini",
    ]
    seen = set()
    return [m for m in base if m and (m not in seen and not seen.add(m))]

# -------------------- ERD drawing (headless) --------------------
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
from matplotlib.patches import Rectangle
from io import BytesIO
from PIL import Image

PLOT_FIGSIZE = (7.6, 3.8)
PLOT_DPI = 120
PLOT_HEIGHT = 300

def _fig_to_pil(fig) -> Image.Image:
    buf = BytesIO()
    fig.tight_layout()
    fig.savefig(buf, format="png", dpi=PLOT_DPI, bbox_inches="tight")
    plt.close(fig)
    buf.seek(0)
    return Image.open(buf)

def draw_dynamic_erd(
    schema: Dict[str, Any],
    highlight_tables: Optional[Set[str]] = None,
    highlight_edges: Optional[Set[Tuple[str, str]]] = None,  # kept for API compatibility; ignored
) -> Image.Image:
    """
    Draw tables (no FK edges). If highlight_tables provided, those table borders are bolded.
    """
    highlight_tables = set(highlight_tables or [])

    tables = schema.get("tables", [])
    fig, ax = plt.subplots(figsize=PLOT_FIGSIZE); ax.axis("off")
    if not tables:
        ax.text(0.5, 0.5, "No tables to diagram.", ha="center", va="center")
        return _fig_to_pil(fig)

    # Layout tables horizontally
    n = len(tables)
    margin = 0.03
    width = (1 - margin * (n + 1)) / max(n, 1)
    height = 0.70
    y = 0.20

    # Draw table boxes + columns (no edges)
    for i, t in enumerate(tables):
        tx = margin + i * (width + margin)

        # Border weight highlights usage
        lw = 2.0 if t["name"] in highlight_tables else 1.2
        ax.add_patch(Rectangle((tx, y), width, height, fill=False, lw=lw))
        ax.text(tx + 0.01, y + height - 0.04, t["name"], fontsize=10, ha="left", va="top", weight="bold")

        yy = y + height - 0.09
        pkset = set(t.get("pk", []) or [])
        fk_map: Dict[str, List[Tuple[str, str]]] = {}
        for fk in t.get("fks", []) or []:
            ref_tbl = fk.get("ref_table", "")
            for c, rc in zip(fk.get("columns", []) or [], fk.get("ref_columns", []) or []):
                fk_map.setdefault(c, []).append((ref_tbl, rc))

        for col in t.get("columns", []):
            nm = col.get("name", "")
            tag = ""
            if nm in pkset:
                tag = " (PK)"
            if nm in fk_map:
                ref = fk_map[nm][0]
                tag = f" (FK→{ref[0]}.{ref[1]})" if not tag else tag.replace(")", f", FK→{ref[0]}.{ref[1]})")
            ax.text(tx + 0.016, yy, f"{nm}{tag}", fontsize=9, ha="left", va="top")
            yy -= 0.055

    ax.text(0.5, 0.06, f"Domain: {schema.get('domain','unknown')}", fontsize=9, ha="center")
    return _fig_to_pil(fig)

# Parse JOINs from SQL to highlight tables (edges ignored now)
JOIN_TBL_RE = re.compile(r"\b(?:from|join)\s+([a-z_]\w*)(?:\s+(?:as\s+)?([a-z_]\w*))?", re.IGNORECASE)
EQ_ON_RE = re.compile(r"([a-z_]\w*)\.[a-z_]\w*\s*=\s*([a-z_]\w*)\.[a-z_]\w*", re.IGNORECASE)
USING_RE = re.compile(r"\bjoin\s+([a-z_]\w*)(?:\s+(?:as\s+)?([a-z_]\w*))?\s+using\s*\(", re.IGNORECASE)

def sql_highlights(sql: str, schema: Dict[str, Any]) -> Tuple[Set[str], Set[Tuple[str, str]]]:
    if not sql:
        return set(), set()

    low = " ".join(sql.strip().split())
    alias_to_table: Dict[str, str] = {}
    join_order: List[str] = []

    for m in JOIN_TBL_RE.finditer(low):
        table = m.group(1)
        alias = m.group(2) or table
        alias_to_table[alias] = table
        join_order.append(alias)

    # Edges are computed but not used by the renderer anymore
    edges: Set[Tuple[str, str]] = set()
    for a1, a2 in EQ_ON_RE.findall(low):
        t1 = alias_to_table.get(a1, a1)
        t2 = alias_to_table.get(a2, a2)
        if t1 != t2:
            edges.add((t1, t2))

    if USING_RE.search(low) and len(join_order) >= 2:
        for i in range(1, len(join_order)):
            t_left = alias_to_table.get(join_order[i-1], join_order[i-1])
            t_right = alias_to_table.get(join_order[i], join_order[i])
            if t_left != t_right:
                edges.add((t_left, t_right))

    used_tables = {alias_to_table.get(a, a) for a in join_order}
    schema_tables = {t["name"] for t in schema.get("tables", [])}
    edges = {(a, b) for (a, b) in edges if a in schema_tables and b in schema_tables}
    used_tables = {t for t in used_tables if t in schema_tables}
    return used_tables, edges

# -------------------- SQL keyword highlighting (preview) --------------------
def _html_escape(s: str) -> str:
    return s.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")

# Multi-word phrases first (to avoid double-highlighting)
PHRASE_CLASSES = [
    (r"\bgroup\s+by\b", "kw-group"),
    (r"\border\s+by\b", "kw-order"),
    (r"\bleft\s+join\b", "kw-join"),
    (r"\binner\s+join\b", "kw-join"),
    (r"\bright\s+join\b", "kw-join"),
    (r"\bfull\s+(?:outer\s+)?join\b", "kw-join"),
    (r"\bcreate\s+view\b", "kw-ddl"),
    (r"\bcreate\s+table\b", "kw-ddl"),
    (r"\binsert\s+into\b", "kw-ddl"),
    (r"\bselect\s+into\b", "kw-ddl"),
    (r"\bprimary\s+key\b", "kw-ddl"),
    (r"\bforeign\s+key\b", "kw-ddl"),
]

TOKEN_CLASSES = [
    (r"\bselect\b", "kw-core"),
    (r"\bfrom\b", "kw-core"),
    (r"\bwhere\b", "kw-core"),
    (r"\bjoin\b", "kw-join"),
    (r"\bon\b", "kw-join"),
    (r"\busing\b", "kw-join"),
    (r"\bhaving\b", "kw-group"),
    (r"\blimit\b", "kw-order"),
    (r"\boffset\b", "kw-order"),
    (r"\bdistinct\b", "kw-core"),
    (r"\bcase\b", "kw-core"),
    (r"\bwhen\b", "kw-core"),
    (r"\bthen\b", "kw-core"),
    (r"\belse\b", "kw-core"),
    (r"\bend\b", "kw-core"),
    (r"\band\b", "kw-core"),
    (r"\bor\b", "kw-core"),
    (r"\bnot\b", "kw-core"),
    (r"\bin\b", "kw-core"),
    (r"\bis\b", "kw-core"),
    (r"\bnull\b", "kw-core"),
    (r"\blike\b", "kw-core"),
    (r"\bbetween\b", "kw-core"),
    (r"\bunion\b", "kw-core"),
    (r"\ball\b", "kw-core"),
    (r"\bupdate\b", "kw-ddl"),
    (r"\bdelete\b", "kw-ddl"),
    (r"\bview\b", "kw-ddl"),
    (r"\btable\b", "kw-ddl"),
    (r"\binto\b", "kw-ddl"),
    (r"\bvalues\b", "kw-ddl"),
    (r"\bcreate\b", "kw-ddl"),
]

def highlight_sql_html(sql: str) -> str:
    if not sql:
        return ""
    txt = _html_escape(sql)
    # Phrases first
    for pat, cls in PHRASE_CLASSES:
        txt = re.sub(pat, lambda m: f'<span class="kw {cls}">{m.group(0).upper()}</span>', txt, flags=re.IGNORECASE)
    # Single tokens
    for pat, cls in TOKEN_CLASSES:
        txt = re.sub(pat, lambda m: f'<span class="kw {cls}">{m.group(0).upper()}</span>', txt, flags=re.IGNORECASE)
    return f'<div class="sql-preview"><pre><code>{txt}</code></pre></div>'

# -------------------- SQLite + locking --------------------
DB_DIR = "/data" if os.path.exists("/data") else "."
DB_PATH = os.path.join(DB_DIR, "sql_trainer_dynamic.db")
EXPORT_DIR = "."
RANDOM_SEED = int(os.getenv("RANDOM_SEED", "7"))
random.seed(RANDOM_SEED)
SYS_RAND = random.SystemRandom()

DB_LOCK = threading.RLock()

def connect_db():
    con = sqlite3.connect(DB_PATH, check_same_thread=False)
    con.execute("PRAGMA journal_mode=WAL;")
    con.execute("PRAGMA synchronous=NORMAL;")
    con.execute("PRAGMA foreign_keys = ON;")
    return con

CONN = connect_db()

def init_progress_tables(con: sqlite3.Connection):
    with DB_LOCK:
        cur = con.cursor()
        cur.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id TEXT PRIMARY KEY,
                name TEXT,
                created_at TEXT
            )
        """)
        cur.execute("""
            CREATE TABLE IF NOT EXISTS attempts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id TEXT,
                question_id TEXT,
                category TEXT,
                correct INTEGER,
                sql_text TEXT,
                timestamp TEXT,
                time_taken REAL,
                difficulty INTEGER,
                source TEXT,
                notes TEXT
            )
        """)
        cur.execute("""
            CREATE TABLE IF NOT EXISTS session_meta (
                id INTEGER PRIMARY KEY CHECK (id=1),
                domain TEXT,
                schema_json TEXT
            )
        """)
        con.commit()

init_progress_tables(CONN)

# -------------------- Built-in fallback domain packs --------------------
# (packs identical to your last version; omitted comments for brevity)
FALLBACK_PACKS: Dict[str, Dict[str, Any]] = {}
# --- Bookstore (existing) ---
FALLBACK_PACKS["bookstore"] = {
    "schema": {
        "domain": "bookstore",
        "tables": [
            {
                "name": "authors",
                "pk": ["author_id"],
                "columns": [
                    {"name":"author_id","type":"INTEGER"},
                    {"name":"name","type":"TEXT"},
                    {"name":"country","type":"TEXT"},
                    {"name":"birth_year","type":"INTEGER"},
                ],
                "fks": [],
                "rows": [
                    {"author_id":1,"name":"Isaac Asimov","country":"USA","birth_year":1920},
                    {"author_id":2,"name":"Ursula K. Le Guin","country":"USA","birth_year":1929},
                    {"author_id":3,"name":"Haruki Murakami","country":"Japan","birth_year":1949},
                    {"author_id":4,"name":"Chinua Achebe","country":"Nigeria","birth_year":1930},
                    {"author_id":5,"name":"Jane Austen","country":"UK","birth_year":1775},
                    {"author_id":6,"name":"J.K. Rowling","country":"UK","birth_year":1965},
                    {"author_id":7,"name":"Yuval Noah Harari","country":"Israel","birth_year":1976},
                    {"author_id":8,"name":"New Author","country":"Nowhere","birth_year":1990},
                ],
            },
            {
                "name": "bookstores",
                "pk": ["store_id"],
                "columns": [
                    {"name":"store_id","type":"INTEGER"},
                    {"name":"name","type":"TEXT"},
                    {"name":"city","type":"TEXT"},
                    {"name":"state","type":"TEXT"},
                ],
                "fks": [],
                "rows": [
                    {"store_id":1,"name":"Downtown Books","city":"Oklahoma City","state":"OK"},
                    {"store_id":2,"name":"Harbor Books","city":"Seattle","state":"WA"},
                    {"store_id":3,"name":"Desert Pages","city":"Phoenix","state":"AZ"},
                ],
            },
            {
                "name": "books",
                "pk": ["book_id"],
                "columns": [
                    {"name":"book_id","type":"INTEGER"},
                    {"name":"title","type":"TEXT"},
                    {"name":"author_id","type":"INTEGER"},
                    {"name":"store_id","type":"INTEGER"},
                    {"name":"category","type":"TEXT"},
                    {"name":"price","type":"REAL"},
                    {"name":"published_year","type":"INTEGER"},
                ],
                "fks": [
                    {"columns":["author_id"],"ref_table":"authors","ref_columns":["author_id"]},
                    {"columns":["store_id"],"ref_table":"bookstores","ref_columns":["store_id"]},
                ],
                "rows": [
                    {"book_id":101,"title":"Foundation","author_id":1,"store_id":1,"category":"Sci-Fi","price":14.99,"published_year":1951},
                    {"book_id":102,"title":"I, Robot","author_id":1,"store_id":1,"category":"Sci-Fi","price":12.50,"published_year":1950},
                    {"book_id":103,"title":"The Left Hand of Darkness","author_id":2,"store_id":2,"category":"Sci-Fi","price":16.00,"published_year":1969},
                    {"book_id":104,"title":"A Wizard of Earthsea","author_id":2,"store_id":2,"category":"Fantasy","price":11.50,"published_year":1968},
                    {"book_id":105,"title":"Norwegian Wood","author_id":3,"store_id":3,"category":"Fiction","price":18.00},
                    {"book_id":106,"title":"Kafka on the Shore","author_id":3,"store_id":1,"category":"Fiction","price":21.00},
                    {"book_id":107,"title":"Things Fall Apart","author_id":4,"store_id":1,"category":"Fiction","price":10.00},
                    {"book_id":108,"title":"Pride and Prejudice","author_id":5,"store_id":2,"category":"Fiction","price":9.00},
                    {"book_id":109,"title":"Harry Potter and the Sorcerer's Stone","author_id":6,"store_id":3,"category":"Children","price":22.00},
                    {"book_id":110,"title":"Harry Potter and the Chamber of Secrets","author_id":6,"store_id":3,"category":"Children","price":23.00},
                    {"book_id":111,"title":"Sapiens","author_id":7,"store_id":1,"category":"History","price":26.00},
                    {"book_id":112,"title":"Homo Deus","author_id":7,"store_id":2,"category":"History","price":28.00},
                ],
            },
        ]
    },
    "questions": [
        {"id":"B_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"Select all rows and columns from `authors`.",
         "answer_sql":["SELECT * FROM authors;"]},
        {"id":"B_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"Show `title` and `price` from `books`.",
         "answer_sql":["SELECT title, price FROM books;"]},
        {"id":"B_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"List Sci‑Fi books under $15 (show title, price).",
         "answer_sql":["SELECT title, price FROM books WHERE category='Sci-Fi' AND price < 15;"]},
        {"id":"B_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Using aliases `b` and `a`, join `books` to `authors` and show `b.title` and `a.name` as `author_name`.",
         "answer_sql":["SELECT b.title, a.name AS author_name FROM books b JOIN authors a ON b.author_id=a.author_id;"],
         "requires_aliases":True,"required_aliases":["a","b"]},
        {"id":"B_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Inner join `books` and `bookstores`. Return `title`, `name` as `store`.",
         "answer_sql":["SELECT b.title, s.name AS store FROM books b INNER JOIN bookstores s ON b.store_id=s.store_id;"]},
        {"id":"B_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"List each author and their number of books (include authors with zero): columns `name`, `book_count`.",
         "answer_sql":["SELECT a.name, COUNT(b.book_id) AS book_count FROM authors a LEFT JOIN books b ON a.author_id=b.author_id GROUP BY a.name;"]},
        {"id":"B_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create a view `vw_pricy` with `title`, `price` for books priced > 25.",
         "answer_sql":["CREATE VIEW vw_pricy AS SELECT title, price FROM books WHERE price > 25;"]},
        {"id":"B_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create a table `cheap_books` containing books priced < 12. Use CTAS or SELECT INTO.",
         "answer_sql":[
             "CREATE TABLE cheap_books AS SELECT * FROM books WHERE price < 12;",
             "SELECT * INTO cheap_books FROM books WHERE price < 12;"
         ]},
    ]
}
# --- Retail sales ---
FALLBACK_PACKS["retail sales"] = {
    "schema": {
        "domain": "retail sales",
        "tables": [
            {"name":"customers","pk":["customer_id"],
             "columns":[
                 {"name":"customer_id","type":"INTEGER"},
                 {"name":"name","type":"TEXT"},
                 {"name":"city","type":"TEXT"},
                 {"name":"state","type":"TEXT"}],
             "fks":[],
             "rows":[
                 {"customer_id":1,"name":"Ava Reed","city":"Seattle","state":"WA"},
                 {"customer_id":2,"name":"Mason Ortiz","city":"Portland","state":"OR"},
                 {"customer_id":3,"name":"Noah Patel","city":"Phoenix","state":"AZ"},
                 {"customer_id":4,"name":"Emma Kim","city":"San Diego","state":"CA"},
                 {"customer_id":5,"name":"Olivia Park","city":"Dallas","state":"TX"},
                 {"customer_id":6,"name":"Liam Gray","city":"Denver","state":"CO"},
                 {"customer_id":7,"name":"Sophia Lee","city":"Boston","state":"MA"},
                 {"customer_id":8,"name":"Elijah Hall","city":"Miami","state":"FL"}]},
            {"name":"products","pk":["product_id"],
             "columns":[
                 {"name":"product_id","type":"INTEGER"},
                 {"name":"product_name","type":"TEXT"},
                 {"name":"category","type":"TEXT"},
                 {"name":"price","type":"REAL"}],
             "fks":[],
             "rows":[
                 {"product_id":101,"product_name":"Coffee Maker","category":"Home","price":49.99},
                 {"product_id":102,"product_name":"Electric Kettle","category":"Home","price":29.99},
                 {"product_id":103,"product_name":"Headphones","category":"Electronics","price":79.00},
                 {"product_id":104,"product_name":"USB-C Cable","category":"Electronics","price":9.99},
                 {"product_id":105,"product_name":"Notebook","category":"Stationery","price":3.49},
                 {"product_id":106,"product_name":"Desk Lamp","category":"Home","price":19.99},
                 {"product_id":107,"product_name":"T-Shirt","category":"Clothing","price":15.00},
                 {"product_id":108,"product_name":"Sneakers","category":"Clothing","price":65.00}]},
            {"name":"orders","pk":["order_id"],
             "columns":[
                 {"name":"order_id","type":"INTEGER"},
                 {"name":"customer_id","type":"INTEGER"},
                 {"name":"order_date","type":"TEXT"}],
             "fks":[{"columns":["customer_id"],"ref_table":"customers","ref_columns":["customer_id"]}],
             "rows":[
                 {"order_id":201,"customer_id":1,"order_date":"2024-01-05"},
                 {"order_id":202,"customer_id":2,"order_date":"2024-01-07"},
                 {"order_id":203,"customer_id":1,"order_date":"2024-01-12"},
                 {"order_id":204,"customer_id":3,"order_date":"2024-02-01"},
                 {"order_id":205,"customer_id":4,"order_date":"2024-02-10"},
                 {"order_id":206,"customer_id":5,"order_date":"2024-03-02"},
                 {"order_id":207,"customer_id":6,"order_date":"2024-03-03"},
                 {"order_id":208,"customer_id":7,"order_date":"2024-03-09"},
                 {"order_id":209,"customer_id":8,"order_date":"2024-03-15"},
                 {"order_id":210,"customer_id":3,"order_date":"2024-03-20"}]},
            {"name":"order_items","pk":["order_id","product_id"],
             "columns":[
                 {"name":"order_id","type":"INTEGER"},
                 {"name":"product_id","type":"INTEGER"},
                 {"name":"qty","type":"INTEGER"},
                 {"name":"unit_price","type":"REAL"}],
             "fks":[
                 {"columns":["order_id"],"ref_table":"orders","ref_columns":["order_id"]},
                 {"columns":["product_id"],"ref_table":"products","ref_columns":["product_id"]}],
             "rows":[
                 {"order_id":201,"product_id":101,"qty":1,"unit_price":49.99},
                 {"order_id":201,"product_id":104,"qty":2,"unit_price":9.99},
                 {"order_id":202,"product_id":107,"qty":3,"unit_price":15.00},
                 {"order_id":203,"product_id":103,"qty":1,"unit_price":79.00},
                 {"order_id":203,"product_id":105,"qty":5,"unit_price":3.49},
                 {"order_id":204,"product_id":102,"qty":2,"unit_price":29.99},
                 {"order_id":205,"product_id":108,"qty":1,"unit_price":65.00},
                 {"order_id":206,"product_id":106,"qty":2,"unit_price":19.99},
                 {"order_id":207,"product_id":104,"qty":4,"unit_price":9.99},
                 {"order_id":208,"product_id":101,"qty":1,"unit_price":49.99},
                 {"order_id":209,"product_id":107,"qty":2,"unit_price":15.00},
                 {"order_id":210,"product_id":103,"qty":1,"unit_price":79.00}]}] },
    "questions":[
        {"id":"RS_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"Show everything from `customers`.",
         "answer_sql":["SELECT * FROM customers;"]},
        {"id":"RS_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"List product name and price from `products`.",
         "answer_sql":["SELECT product_name, price FROM products;"]},
        {"id":"RS_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"Orders placed in March 2024 (return `order_id`, `order_date`).",
         "answer_sql":["SELECT order_id, order_date FROM orders WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';"]},
        {"id":"RS_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Join `orders` (alias `o`) with `customers` (alias `c`) and show `o.order_id`, `c.name`.",
         "answer_sql":["SELECT o.order_id, c.name FROM orders o JOIN customers c ON o.customer_id=c.customer_id;"],
         "requires_aliases":True,"required_aliases":["o","c"]},
        {"id":"RS_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Inner join `order_items` with `products` to show items where qty ≥ 3. Return `product_name`, `qty`.",
         "answer_sql":["SELECT p.product_name, oi.qty FROM order_items oi INNER JOIN products p ON oi.product_id=p.product_id WHERE oi.qty >= 3;"]},
        {"id":"RS_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"Customers and their number of orders (include zero). Columns: `name`, `order_count`.",
         "answer_sql":["SELECT c.name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id=o.customer_id GROUP BY c.name;"]},
        {"id":"RS_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create view `vw_top_qty` with total quantity by product: `product_id`, `total_qty`.",
         "answer_sql":["CREATE VIEW vw_top_qty AS SELECT product_id, SUM(qty) AS total_qty FROM order_items GROUP BY product_id;"]},
        {"id":"RS_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create table `cheap_products` with products priced < 10.",
         "answer_sql":[
             "CREATE TABLE cheap_products AS SELECT * FROM products WHERE price < 10;",
             "SELECT * INTO cheap_products FROM products WHERE price < 10;"
         ]}
    ]
}
# --- Wholesaler ---
FALLBACK_PACKS["wholesaler"] = {
    "schema":{
        "domain":"wholesaler",
        "tables":[
            {"name":"suppliers","pk":["supplier_id"],
             "columns":[
                 {"name":"supplier_id","type":"INTEGER"},
                 {"name":"supplier_name","type":"TEXT"},
                 {"name":"country","type":"TEXT"}],
             "fks":[],
             "rows":[
                 {"supplier_id":1,"supplier_name":"Nordic Foods","country":"SE"},
                 {"supplier_id":2,"supplier_name":"Metro Trade","country":"DE"},
                 {"supplier_id":3,"supplier_name":"Pacific Imports","country":"US"},
                 {"supplier_id":4,"supplier_name":"Andes Supply","country":"CL"},
                 {"supplier_id":5,"supplier_name":"Sahara Wholesale","country":"MA"}]},
            {"name":"items","pk":["item_id"],
             "columns":[
                 {"name":"item_id","type":"INTEGER"},
                 {"name":"item_name","type":"TEXT"},
                 {"name":"unit_cost","type":"REAL"}],
             "fks":[],
             "rows":[
                 {"item_id":101,"item_name":"Olive Oil 1L","unit_cost":4.20},
                 {"item_id":102,"item_name":"Canned Tuna","unit_cost":1.10},
                 {"item_id":103,"item_name":"Basmati Rice 5kg","unit_cost":6.30},
                 {"item_id":104,"item_name":"Black Tea 200g","unit_cost":2.70},
                 {"item_id":105,"item_name":"Peanut Butter","unit_cost":3.00},
                 {"item_id":106,"item_name":"Tomato Paste","unit_cost":0.95},
                 {"item_id":107,"item_name":"Chickpeas 1kg","unit_cost":1.60},
                 {"item_id":108,"item_name":"Soy Sauce 500ml","unit_cost":2.10}]},
            {"name":"purchase_orders","pk":["po_id"],
             "columns":[
                 {"name":"po_id","type":"INTEGER"},
                 {"name":"supplier_id","type":"INTEGER"},
                 {"name":"po_date","type":"TEXT"}],
             "fks":[{"columns":["supplier_id"],"ref_table":"suppliers","ref_columns":["supplier_id"]}],
             "rows":[
                 {"po_id":201,"supplier_id":1,"po_date":"2024-01-10"},
                 {"po_id":202,"supplier_id":2,"po_date":"2024-01-18"},
                 {"po_id":203,"supplier_id":3,"po_date":"2024-02-05"},
                 {"po_id":204,"supplier_id":1,"po_date":"2024-02-22"},
                 {"po_id":205,"supplier_id":5,"po_date":"2024-03-01"},
                 {"po_id":206,"supplier_id":4,"po_date":"2024-03-07"}]},
            {"name":"po_lines","pk":["po_id","item_id"],
             "columns":[
                 {"name":"po_id","type":"INTEGER"},
                 {"name":"item_id","type":"INTEGER"},
                 {"name":"qty","type":"INTEGER"},
                 {"name":"line_cost","type":"REAL"}],
             "fks":[
                 {"columns":["po_id"],"ref_table":"purchase_orders","ref_columns":["po_id"]},
                 {"columns":["item_id"],"ref_table":"items","ref_columns":["item_id"]}],
             "rows":[
                 {"po_id":201,"item_id":101,"qty":200,"line_cost":840.0},
                 {"po_id":201,"item_id":106,"qty":500,"line_cost":475.0},
                 {"po_id":202,"item_id":103,"qty":120,"line_cost":756.0},
                 {"po_id":203,"item_id":102,"qty":600,"line_cost":660.0},
                 {"po_id":203,"item_id":104,"qty":150,"line_cost":405.0},
                 {"po_id":204,"item_id":105,"qty":180,"line_cost":540.0},
                 {"po_id":205,"item_id":107,"qty":300,"line_cost":480.0},
                 {"po_id":206,"item_id":108,"qty":250,"line_cost":525.0}]}]
    },
    "questions":[
        {"id":"W_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"Show all suppliers.",
         "answer_sql":["SELECT * FROM suppliers;"]},
        {"id":"W_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"Return `item_name` and `unit_cost` from `items`.",
         "answer_sql":["SELECT item_name, unit_cost FROM items;"]},
        {"id":"W_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"Items costing more than 3.00 (show name, cost).",
         "answer_sql":["SELECT item_name, unit_cost FROM items WHERE unit_cost > 3.00;"]},
        {"id":"W_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Using aliases `p` and `s`, show each `po_id` with `supplier_name`.",
         "answer_sql":["SELECT p.po_id, s.supplier_name FROM purchase_orders p JOIN suppliers s ON p.supplier_id=s.supplier_id;"],
         "requires_aliases":True,"required_aliases":["p","s"]},
        {"id":"W_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Inner join `po_lines` and `items`; list `item_name`, total qty per item.",
         "answer_sql":["SELECT i.item_name, SUM(l.qty) AS total_qty FROM po_lines l INNER JOIN items i ON l.item_id=i.item_id GROUP BY i.item_name;"]},
        {"id":"W_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"Suppliers and count of POs (include those with zero). Columns `supplier_name`, `po_count`.",
         "answer_sql":["SELECT s.supplier_name, COUNT(p.po_id) AS po_count FROM suppliers s LEFT JOIN purchase_orders p ON s.supplier_id=p.supplier_id GROUP BY s.supplier_name;"]},
        {"id":"W_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create view `vw_po_value` with `po_id` and total `line_cost` per PO.",
         "answer_sql":["CREATE VIEW vw_po_value AS SELECT po_id, SUM(line_cost) AS po_value FROM po_lines GROUP BY po_id;"]},
        {"id":"W_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create table `budget_items` where unit_cost < 2.00.",
         "answer_sql":[
             "CREATE TABLE budget_items AS SELECT * FROM items WHERE unit_cost < 2.00;",
             "SELECT * INTO budget_items FROM items WHERE unit_cost < 2.00;"
         ]}
    ]
}
# --- Sales tax ---
FALLBACK_PACKS["sales tax"] = {
    "schema":{
        "domain":"sales tax",
        "tables":[
            {"name":"jurisdictions","pk":["jurisdiction_id"],
             "columns":[
                 {"name":"jurisdiction_id","type":"INTEGER"},
                 {"name":"name","type":"TEXT"},
                 {"name":"state","type":"TEXT"}],
             "fks":[],
             "rows":[
                 {"jurisdiction_id":1,"name":"King County","state":"WA"},
                 {"jurisdiction_id":2,"name":"Multnomah","state":"OR"},
                 {"jurisdiction_id":3,"name":"Maricopa","state":"AZ"},
                 {"jurisdiction_id":4,"name":"Travis","state":"TX"},
                 {"jurisdiction_id":5,"name":"Denver","state":"CO"},
                 {"jurisdiction_id":6,"name":"Miami-Dade","state":"FL"}]},
            {"name":"tax_rates","pk":["rate_id"],
             "columns":[
                 {"name":"rate_id","type":"INTEGER"},
                 {"name":"jurisdiction_id","type":"INTEGER"},
                 {"name":"category","type":"TEXT"},
                 {"name":"rate","type":"REAL"}],
             "fks":[{"columns":["jurisdiction_id"],"ref_table":"jurisdictions","ref_columns":["jurisdiction_id"]}],
             "rows":[
                 {"rate_id":101,"jurisdiction_id":1,"category":"general","rate":0.102},
                 {"rate_id":102,"jurisdiction_id":2,"category":"general","rate":0.000},
                 {"rate_id":103,"jurisdiction_id":3,"category":"general","rate":0.056},
                 {"rate_id":104,"jurisdiction_id":4,"category":"general","rate":0.0825},
                 {"rate_id":105,"jurisdiction_id":5,"category":"general","rate":0.081},
                 {"rate_id":106,"jurisdiction_id":6,"category":"general","rate":0.070}]},
            {"name":"transactions","pk":["txn_id"],
             "columns":[
                 {"name":"txn_id","type":"INTEGER"},
                 {"name":"txn_date","type":"TEXT"},
                 {"name":"amount","type":"REAL"},
                 {"name":"category","type":"TEXT"},
                 {"name":"jurisdiction_id","type":"INTEGER"}],
             "fks":[{"columns":["jurisdiction_id"],"ref_table":"jurisdictions","ref_columns":["jurisdiction_id"]}],
             "rows":[
                 {"txn_id":201,"txn_date":"2024-01-03","amount":120.00,"category":"general","jurisdiction_id":1},
                 {"txn_id":202,"txn_date":"2024-01-05","amount":55.25,"category":"general","jurisdiction_id":2},
                 {"txn_id":203,"txn_date":"2024-01-10","amount":300.00,"category":"general","jurisdiction_id":3},
                 {"txn_id":204,"txn_date":"2024-02-01","amount":240.55,"category":"general","jurisdiction_id":4},
                 {"txn_id":205,"txn_date":"2024-02-14","amount":89.99,"category":"general","jurisdiction_id":5},
                 {"txn_id":206,"txn_date":"2024-03-02","amount":150.00,"category":"general","jurisdiction_id":6},
                 {"txn_id":207,"txn_date":"2024-03-09","amount":70.00,"category":"general","jurisdiction_id":1},
                 {"txn_id":208,"txn_date":"2024-03-15","amount":18.50,"category":"general","jurisdiction_id":2},
                 {"txn_id":209,"txn_date":"2024-03-20","amount":99.95,"category":"general","jurisdiction_id":3},
                 {"txn_id":210,"txn_date":"2024-03-25","amount":199.99,"category":"general","jurisdiction_id":4}]}]
    },
    "questions":[
        {"id":"TX_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"Show all records from `jurisdictions`.",
         "answer_sql":["SELECT * FROM jurisdictions;"]},
        {"id":"TX_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"Show `jurisdiction_id`, `rate` from `tax_rates`.",
         "answer_sql":["SELECT jurisdiction_id, rate FROM tax_rates;"]},
        {"id":"TX_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"Transactions over $150 (return `txn_id`, `amount`).",
         "answer_sql":["SELECT txn_id, amount FROM transactions WHERE amount > 150;"]},
        {"id":"TX_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Join `transactions` (`t`) with `jurisdictions` (`j`), returning `t.txn_id`, `j.name`.",
         "answer_sql":["SELECT t.txn_id, j.name FROM transactions t JOIN jurisdictions j ON t.jurisdiction_id=j.jurisdiction_id;"],
         "requires_aliases":True,"required_aliases":["t","j"]},
        {"id":"TX_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Compute tax for each transaction: return `txn_id`, `amount*rate` as `tax`.",
         "answer_sql":["SELECT t.txn_id, t.amount * r.rate AS tax FROM transactions t INNER JOIN tax_rates r ON t.jurisdiction_id=r.jurisdiction_id AND t.category=r.category;"]},
        {"id":"TX_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"Jurisdictions and count of transactions (include zero). Columns `name`, `txn_count`.",
         "answer_sql":["SELECT j.name, COUNT(t.txn_id) AS txn_count FROM jurisdictions j LEFT JOIN transactions t ON j.jurisdiction_id=t.jurisdiction_id GROUP BY j.name;"]},
        {"id":"TX_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create view `vw_high_txn` with transactions amount > 150.",
         "answer_sql":["CREATE VIEW vw_high_txn AS SELECT * FROM transactions WHERE amount > 150;"]},
        {"id":"TX_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create table `low_rate` with tax_rates where rate < 0.06.",
         "answer_sql":[
             "CREATE TABLE low_rate AS SELECT * FROM tax_rates WHERE rate < 0.06;",
             "SELECT * INTO low_rate FROM tax_rates WHERE rate < 0.06;"
         ]}
    ]
}
# --- Oil & gas wells ---
FALLBACK_PACKS["oil and gas wells"] = {
    "schema":{
        "domain":"oil and gas wells",
        "tables":[
            {"name":"wells","pk":["well_id"],
             "columns":[
                 {"name":"well_id","type":"INTEGER"},
                 {"name":"well_name","type":"TEXT"},
                 {"name":"location","type":"TEXT"},
                 {"name":"status","type":"TEXT"},
                 {"name":"depth","type":"INTEGER"}],
             "fks":[],
             "rows":[
                 {"well_id":1,"well_name":"Alpha-1","location":"TX-TRV","status":"producing","depth":12000},
                 {"well_id":2,"well_name":"Bravo-2","location":"TX-TRV","status":"shut-in","depth":10500},
                 {"well_id":3,"well_name":"Cedar-7","location":"OK-CAD","status":"producing","depth":9800},
                 {"well_id":4,"well_name":"Delta-3","location":"ND-WIL","status":"drilling","depth":7000},
                 {"well_id":5,"well_name":"Eagle-5","location":"CO-DNV","status":"producing","depth":8500},
                 {"well_id":6,"well_name":"Fox-9","location":"NM-LEA","status":"producing","depth":11000}]},
            {"name":"operators","pk":["operator_id"],
             "columns":[
                 {"name":"operator_id","type":"INTEGER"},
                 {"name":"name","type":"TEXT"},
                 {"name":"contact","type":"TEXT"}],
             "fks":[],
             "rows":[
                 {"operator_id":10,"name":"PetroMax","contact":"pmx@example.com"},
                 {"operator_id":11,"name":"BlueRock Energy","contact":"blue@example.com"},
                 {"operator_id":12,"name":"HighPlains LLC","contact":"hp@example.com"},
                 {"operator_id":13,"name":"Mesa Oil","contact":"mesa@example.com"}]},
            {"name":"well_operators","pk":["well_id","operator_id"],
             "columns":[
                 {"name":"well_id","type":"INTEGER"},
                 {"name":"operator_id","type":"INTEGER"},
                 {"name":"start_date","type":"TEXT"}],
             "fks":[
                 {"columns":["well_id"],"ref_table":"wells","ref_columns":["well_id"]},
                 {"columns":["operator_id"],"ref_table":"operators","ref_columns":["operator_id"]}],
             "rows":[
                 {"well_id":1,"operator_id":10,"start_date":"2023-01-01"},
                 {"well_id":2,"operator_id":10,"start_date":"2023-06-01"},
                 {"well_id":3,"operator_id":11,"start_date":"2022-03-15"},
                 {"well_id":4,"operator_id":12,"start_date":"2024-02-01"},
                 {"well_id":5,"operator_id":13,"start_date":"2022-10-10"},
                 {"well_id":6,"operator_id":11,"start_date":"2021-08-05"}]},
            {"name":"production","pk":["prod_id"],
             "columns":[
                 {"name":"prod_id","type":"INTEGER"},
                 {"name":"well_id","type":"INTEGER"},
                 {"name":"month","type":"TEXT"},
                 {"name":"oil_bbl","type":"REAL"},
                 {"name":"gas_mcf","type":"REAL"}],
             "fks":[{"columns":["well_id"],"ref_table":"wells","ref_columns":["well_id"]}],
             "rows":[
                 {"prod_id":1001,"well_id":1,"month":"2024-01","oil_bbl":1200,"gas_mcf":5000},
                 {"prod_id":1002,"well_id":1,"month":"2024-02","oil_bbl":1180,"gas_mcf":5100},
                 {"prod_id":1003,"well_id":3,"month":"2024-01","oil_bbl":900,"gas_mcf":3000},
                 {"prod_id":1004,"well_id":3,"month":"2024-02","oil_bbl":950,"gas_mcf":3100},
                 {"prod_id":1005,"well_id":5,"month":"2024-01","oil_bbl":600,"gas_mcf":2200},
                 {"prod_id":1006,"well_id":6,"month":"2024-01","oil_bbl":750,"gas_mcf":2600},
                 {"prod_id":1007,"well_id":2,"month":"2024-01","oil_bbl":0,"gas_mcf":0},
                 {"prod_id":1008,"well_id":4,"month":"2024-02","oil_bbl":100,"gas_mcf":400}]}]
    },
    "questions":[
        {"id":"OG_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"List all rows from `wells`.",
         "answer_sql":["SELECT * FROM wells;"]},
        {"id":"OG_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"Return `well_name`, `status` from `wells`.",
         "answer_sql":["SELECT well_name, status FROM wells;"]},
        {"id":"OG_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"Wells deeper than 10,000 ft (return `well_name`, `depth`).",
         "answer_sql":["SELECT well_name, depth FROM wells WHERE depth > 10000;"]},
        {"id":"OG_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Using `w` and `o`, show `w.well_name` with `o.name` (operator).",
         "answer_sql":["SELECT w.well_name, o.name FROM wells w JOIN well_operators wo ON w.well_id=wo.well_id JOIN operators o ON wo.operator_id=o.operator_id;"],
         "requires_aliases":True,"required_aliases":["w","o"]},
        {"id":"OG_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Join `production` to `wells` and return `well_name`, total `oil_bbl` per well.",
         "answer_sql":["SELECT w.well_name, SUM(p.oil_bbl) AS total_oil FROM production p INNER JOIN wells w ON p.well_id=w.well_id GROUP BY w.well_name;"]},
        {"id":"OG_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"List operators and count of wells (include operators with zero). Columns `name`, `well_count`.",
         "answer_sql":["SELECT o.name, COUNT(wo.well_id) AS well_count FROM operators o LEFT JOIN well_operators wo ON o.operator_id=wo.operator_id GROUP BY o.name;"]},
        {"id":"OG_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create view `vw_prod_jan` for January 2024 production.",
         "answer_sql":["CREATE VIEW vw_prod_jan AS SELECT * FROM production WHERE month='2024-01';"]},
        {"id":"OG_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create table `active_wells` for wells with status='producing'.",
         "answer_sql":[
             "CREATE TABLE active_wells AS SELECT * FROM wells WHERE status='producing';",
             "SELECT * INTO active_wells FROM wells WHERE status='producing';"
         ]}
    ]
}
# --- Marketing ---
FALLBACK_PACKS["marketing"] = {
    "schema":{
        "domain":"marketing",
        "tables":[
            {"name":"channels","pk":["channel_id"],
             "columns":[
                 {"name":"channel_id","type":"INTEGER"},
                 {"name":"channel_name","type":"TEXT"}],
             "fks":[],
             "rows":[
                 {"channel_id":1,"channel_name":"Search"},
                 {"channel_id":2,"channel_name":"Social"},
                 {"channel_id":3,"channel_name":"Email"},
                 {"channel_id":4,"channel_name":"Display"}]},
            {"name":"campaigns","pk":["campaign_id"],
             "columns":[
                 {"name":"campaign_id","type":"INTEGER"},
                 {"name":"campaign_name","type":"TEXT"},
                 {"name":"channel_id","type":"INTEGER"},
                 {"name":"start_date","type":"TEXT"},
                 {"name":"budget","type":"REAL"}],
             "fks":[{"columns":["channel_id"],"ref_table":"channels","ref_columns":["channel_id"]}],
             "rows":[
                 {"campaign_id":101,"campaign_name":"Spring Search","channel_id":1,"start_date":"2024-03-01","budget":5000},
                 {"campaign_id":102,"campaign_name":"Brand Social","channel_id":2,"start_date":"2024-03-05","budget":3000},
                 {"campaign_id":103,"campaign_name":"Welcome Email","channel_id":3,"start_date":"2024-03-07","budget":1000},
                 {"campaign_id":104,"campaign_name":"Retargeting","channel_id":4,"start_date":"2024-03-10","budget":2000},
                 {"campaign_id":105,"campaign_name":"Summer Search","channel_id":1,"start_date":"2024-06-01","budget":6000},
                 {"campaign_id":106,"campaign_name":"Promo Social","channel_id":2,"start_date":"2024-06-05","budget":3500}]},
            {"name":"ad_stats","pk":["campaign_id","day"],
             "columns":[
                 {"name":"campaign_id","type":"INTEGER"},
                 {"name":"day","type":"TEXT"},
                 {"name":"impressions","type":"INTEGER"},
                 {"name":"clicks","type":"INTEGER"},
                 {"name":"spend","type":"REAL"}],
             "fks":[{"columns":["campaign_id"],"ref_table":"campaigns","ref_columns":["campaign_id"]}],
             "rows":[
                 {"campaign_id":101,"day":"2024-03-12","impressions":10000,"clicks":500,"spend":200.0},
                 {"campaign_id":101,"day":"2024-03-13","impressions":12000,"clicks":600,"spend":230.0},
                 {"campaign_id":102,"day":"2024-03-12","impressions":8000,"clicks":400,"spend":150.0},
                 {"campaign_id":103,"day":"2024-03-12","impressions":5000,"clicks":250,"spend":80.0},
                 {"campaign_id":104,"day":"2024-03-12","impressions":7000,"clicks":210,"spend":110.0},
                 {"campaign_id":106,"day":"2024-06-12","impressions":9500,"clicks":520,"spend":190.0}]},
            {"name":"leads","pk":["lead_id"],
             "columns":[
                 {"name":"lead_id","type":"INTEGER"},
                 {"name":"campaign_id","type":"INTEGER"},
                 {"name":"source","type":"TEXT"},
                 {"name":"qualified","type":"INTEGER"},
                 {"name":"revenue","type":"REAL"}],
             "fks":[{"columns":["campaign_id"],"ref_table":"campaigns","ref_columns":["campaign_id"]}],
             "rows":[
                 {"lead_id":1,"campaign_id":101,"source":"LP1","qualified":1,"revenue":400},
                 {"lead_id":2,"campaign_id":101,"source":"LP2","qualified":0,"revenue":0},
                 {"lead_id":3,"campaign_id":102,"source":"FB","qualified":1,"revenue":250},
                 {"lead_id":4,"campaign_id":103,"source":"Email","qualified":1,"revenue":300},
                 {"lead_id":5,"campaign_id":104,"source":"DSP","qualified":0,"revenue":0},
                 {"lead_id":6,"campaign_id":106,"source":"FB","qualified":1,"revenue":500}]}]
    },
    "questions":[
        {"id":"M_Q01","category":"SELECT *","difficulty":1,
         "prompt_md":"Show all channels.",
         "answer_sql":["SELECT * FROM channels;"]},
        {"id":"M_Q02","category":"SELECT columns","difficulty":1,
         "prompt_md":"Return `campaign_name`, `budget` from `campaigns`.",
         "answer_sql":["SELECT campaign_name, budget FROM campaigns;"]},
        {"id":"M_Q03","category":"WHERE","difficulty":1,
         "prompt_md":"Campaigns with budget ≥ 3000 (show `campaign_name`, `budget`).",
         "answer_sql":["SELECT campaign_name, budget FROM campaigns WHERE budget >= 3000;"]},
        {"id":"M_Q04","category":"Aliases","difficulty":1,
         "prompt_md":"Join `campaigns` (`c`) with `channels` (`ch`) and show `c.campaign_name`, `ch.channel_name`.",
         "answer_sql":["SELECT c.campaign_name, ch.channel_name FROM campaigns c JOIN channels ch ON c.channel_id=ch.channel_id;"],
         "requires_aliases":True,"required_aliases":["c","ch"]},
        {"id":"M_Q05","category":"JOIN (INNER)","difficulty":2,
         "prompt_md":"Join `ad_stats` with `campaigns` and return `campaign_name`, total `clicks`.",
         "answer_sql":["SELECT c.campaign_name, SUM(s.clicks) AS total_clicks FROM ad_stats s INNER JOIN campaigns c ON s.campaign_id=c.campaign_id GROUP BY c.campaign_name;"]},
        {"id":"M_Q06","category":"JOIN (LEFT)","difficulty":2,
         "prompt_md":"Channels and number of campaigns (include channels with zero). Columns `channel_name`, `campaigns`.",
         "answer_sql":["SELECT ch.channel_name, COUNT(c.campaign_id) AS campaigns FROM channels ch LEFT JOIN campaigns c ON ch.channel_id=c.channel_id GROUP BY ch.channel_name;"]},
        {"id":"M_Q07","category":"VIEW","difficulty":2,
         "prompt_md":"Create view `vw_cost_per_click` with `campaign_id`, `day`, `spend/clicks` as `cpc` (avoid divide-by-zero).",
         "answer_sql":["CREATE VIEW vw_cost_per_click AS SELECT campaign_id, day, CASE WHEN clicks=0 THEN NULL ELSE spend*1.0/clicks END AS cpc FROM ad_stats;"]},
        {"id":"M_Q08","category":"CTAS / SELECT INTO","difficulty":2,
         "prompt_md":"Create table `qualified_leads` of leads where `qualified=1`.",
         "answer_sql":[
             "CREATE TABLE qualified_leads AS SELECT * FROM leads WHERE qualified=1;",
             "SELECT * INTO qualified_leads FROM leads WHERE qualified=1;"
         ]}
    ]
}

# Helpers to get a pack by domain (normalize key)
def get_fallback_pack_for(domain_name: str) -> Tuple[Dict[str,Any], List[Dict[str,Any]]]:
    key = (domain_name or "").strip().lower()
    for k, pack in FALLBACK_PACKS.items():
        if k.lower() == key:
            return pack["schema"], pack["questions"]
    # default to bookstore
    p = FALLBACK_PACKS["bookstore"]
    return p["schema"], p["questions"]

# -------------------- OpenAI prompts + parsing helpers --------------------
ALLOWED_DOMAINS = ["bookstore", "retail sales", "wholesaler", "sales tax", "oil and gas wells", "marketing"]

DOMAIN_CYCLE_POS = 0  # will be set after first install

def _domain_prompt(prev_domain: Optional[str], preferred_domain: Optional[str]) -> str:
    target = preferred_domain or "bookstore"
    extra = f" (previous domain was '{prev_domain}', do not reuse it)" if prev_domain else ""
    return f"""
Return ONLY a valid JSON object (no markdown, no prose).
You MUST set the top-level property "domain" to EXACTLY "{target}" (string match).{extra}

The JSON must have:
- "domain": "{target}"
- "tables": 3–4 table objects
- "questions": 8–12 question objects

Tables:
- SQLite-friendly. Use snake_case.
- Each table: name, pk (list), columns (list of {{name,type}}), fks (list of {{columns,ref_table,ref_columns}}), rows (8–15 small seed rows).

Questions:
- Categories among: "SELECT *", "SELECT columns", "WHERE", "Aliases",
  "JOIN (INNER)", "JOIN (LEFT)", "Aggregation", "VIEW", "CTAS / SELECT INTO".
- Include at least one LEFT JOIN, one VIEW, one CTAS or SELECT INTO.
- Provide 1–3 'answer_sql' strings per question.
- Prefer SQLite-compatible SQL. Do NOT use RIGHT/FULL OUTER JOIN.
"""

def _loose_json_parse(s: str) -> Optional[dict]:
    try:
        return json.loads(s)
    except Exception:
        pass
    start = s.find("{"); end = s.rfind("}")
    if start != -1 and end != -1 and end > start:
        try:
            return json.loads(s[start:end+1])
        except Exception:
            return None
    return None

_SQL_FENCE = re.compile(r"```sql(.*?)```", re.IGNORECASE | re.DOTALL)
_CODE_FENCE = re.compile(r"```(.*?)```", re.DOTALL)

def _strip_code_fences(txt: str) -> str:
    if txt is None: return ""
    m = _SQL_FENCE.findall(txt)
    if m: return "\n".join([x.strip() for x in m if x.strip()])
    m2 = _CODE_FENCE.findall(txt)
    if m2: return "\n".join([x.strip() for x in m2 if x.strip()])
    return txt.strip()

def _as_list_of_sql(val) -> List[str]:
    if val is None: return []
    if isinstance(val, str):
        s = _strip_code_fences(val)
        parts = [p.strip() for p in s.split("\n") if p.strip()]
        return parts or ([s] if s else [])
    if isinstance(val, list):
        out = []
        for v in val:
            if isinstance(v, str):
                s = _strip_code_fences(v)
                if s: out.append(s)
        return out
    return []

def _canon_question(q: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    if not isinstance(q, dict): return None
    cat = q.get("category") or q.get("type") or q.get("topic")
    prompt = q.get("prompt_md") or q.get("prompt") or q.get("question") or q.get("text")
    answer_sql = q.get("answer_sql") or q.get("answers") or q.get("solutions") or q.get("sql")
    diff = q.get("difficulty") or 1
    req_alias = bool(q.get("requires_aliases", False))
    req_aliases = q.get("required_aliases") or []
    cat = str(cat).strip() if cat is not None else ""
    prompt = str(prompt).strip() if prompt is not None else ""
    answers = _as_list_of_sql(answer_sql)
    if not cat or not prompt or not answers: return None
    known = {
        "SELECT *","SELECT columns","WHERE","Aliases",
        "JOIN (INNER)","JOIN (LEFT)","Aggregation","VIEW","CTAS / SELECT INTO"
    }
    if cat not in known:
        low = cat.lower()
        if "select *" in low: cat = "SELECT *"
        elif "columns" in low: cat = "SELECT columns"
        elif "where" in low or "filter" in low: cat = "WHERE"
        elif "alias" in low: cat = "Aliases"
        elif "left" in low: cat = "JOIN (LEFT)"
        elif "inner" in low or "join" in low: cat = "JOIN (INNER)"
        elif "agg" in low or "group" in low: cat = "Aggregation"
        elif "view" in low: cat = "VIEW"
        elif "into" in low or "ctas" in low: cat = "CTAS / SELECT INTO"
    if isinstance(req_aliases, str):
        req_aliases = [a.strip() for a in re.split(r"[,\s]+", req_aliases) if a.strip()]
    elif not isinstance(req_aliases, list):
        req_aliases = []

    # Soften ambiguous aggregation prompts so users know what to return
    if cat == "Aggregation":
        pl = (prompt or "").lower()
        if ("for each" in pl or "per " in pl) and not re.search(r"`[^`]+`", prompt or ""):
            prompt += "\n\n*Note:* Return the grouping column and the aggregate. If the wording says “for each …”, include entities with zero by using a LEFT JOIN."

    return {
        "id": str(q.get("id") or f"LLM_{int(time.time()*1000)}_{random.randint(100,999)}"),
        "category": cat,
        "difficulty": int(diff) if str(diff).isdigit() else 1,
        "prompt_md": prompt,
        "answer_sql": answers,
        "requires_aliases": bool(req_alias),
        "required_aliases": req_aliases,
    }

def _canon_tables(tables: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    out = []
    for t in (tables or []):
        if not isinstance(t, dict): continue
        name = str(t.get("name","")).strip()
        if not name: continue
        cols = t.get("columns") or []
        good_cols = []
        for c in cols:
            if not isinstance(c, dict): continue
            cname = str(c.get("name","")).strip()
            ctype = str(c.get("type","TEXT")).strip() or "TEXT"
            if cname: good_cols.append({"name": cname, "type": ctype})
        if not good_cols: continue
        pk = t.get("pk") or []
        if isinstance(pk, str): pk = [pk]
        fks = t.get("fks") or []
        rows = t.get("rows") or []
        out.append({
            "name": name,
            "pk": [str(x) for x in pk],
            "columns": good_cols,
            "fks": fks if isinstance(fks, list) else [],
            "rows": rows if isinstance(rows, list) else [],
        })
    return out

def llm_generate_domain_and_questions(prev_domain: Optional[str], preferred_domain: Optional[str]):
    if not OPENAI_AVAILABLE or not os.getenv("OPENAI_API_KEY"):
        return None, "OpenAI client not available or OPENAI_API_KEY missing.", None, {"accepted_questions":0,"dropped_questions":0}
    errors = []
    prompt = _domain_prompt(prev_domain, preferred_domain)
    for model in _candidate_models():
        try:
            try:
                chat = _client.chat.completions.create(
                    model=model,
                    messages=[{"role":"user","content": prompt}],
                    temperature=0.6,
                    response_format={"type":"json_object"}
                )
                data_text = chat.choices[0].message.content
            except TypeError:
                chat = _client.chat.completions.create(
                    model=model,
                    messages=[{"role":"system","content":"Return ONLY a JSON object. No markdown."},
                              {"role":"user","content": prompt}],
                    temperature=0.6
                )
                data_text = chat.choices[0].message.content
            obj_raw = _loose_json_parse(data_text or "")
            if not obj_raw:
                raise RuntimeError("Could not parse JSON from model output.")
            for k in ["domain","tables","questions"]:
                if k not in obj_raw:
                    raise RuntimeError(f"Missing key '{k}'")
            # Force domain to preferred
            if preferred_domain and (str(obj_raw.get("domain","")).strip().lower() != preferred_domain.strip().lower()):
                raise RuntimeError(f"Model returned domain '{obj_raw.get('domain')}', expected '{preferred_domain}'.")
            tables = _canon_tables(obj_raw.get("tables", []))
            if not tables: raise RuntimeError("No usable tables in LLM output.")
            obj_raw["tables"] = tables
            dropped = 0
            clean_qs = []
            for q in obj_raw.get("questions", []):
                cq = _canon_question(q)
                if not cq: dropped += 1; continue
                answers = [a for a in cq["answer_sql"] if " right join " not in a.lower() and " full " not in a.lower()]
                if not answers: dropped += 1; continue
                cq["answer_sql"] = answers
                clean_qs.append(cq)
            if not clean_qs:
                raise RuntimeError("No usable questions after canonicalization.")
            stats = {"accepted_questions": len(clean_qs), "dropped_questions": dropped}
            obj_raw["questions"] = clean_qs
            return obj_raw, None, model, stats
        except Exception as e:
            errors.append(f"{model}: {e}")
            continue
    return None, "; ".join(errors) if errors else "Unknown LLM error.", None, {"accepted_questions":0,"dropped_questions":0}

# -------------------- Install schema & prepare questions --------------------
def drop_existing_domain_tables(con: sqlite3.Connection, keep_internal=True):
    with DB_LOCK:
        cur = con.cursor()
        cur.execute("SELECT name, type FROM sqlite_master WHERE type IN ('table','view')")
        items = cur.fetchall()
        for name, typ in items:
            if keep_internal and name in ("users","attempts","session_meta"):
                continue
            try:
                cur.execute(f"DROP {typ.upper()} IF EXISTS {name}")
            except Exception:
                pass
        con.commit()

def install_schema(con: sqlite3.Connection, schema: Dict[str,Any]):
    drop_existing_domain_tables(con, keep_internal=True)
    with DB_LOCK:
        cur = con.cursor()
        for t in schema.get("tables", []):
            cols_sql = []
            pk = t.get("pk", [])
            for c in t.get("columns", []):
                cols_sql.append(f"{c['name']} {c.get('type','TEXT')}")
            if pk: cols_sql.append(f"PRIMARY KEY ({', '.join(pk)})")
            create_sql = f"CREATE TABLE {t['name']} ({', '.join(cols_sql)})"
            cur.execute(create_sql)
        for t in schema.get("tables", []):
            if not t.get("rows"): continue
            cols = [c["name"] for c in t.get("columns", [])]
            qmarks = ",".join(["?"]*len(cols))
            insert_sql = f"INSERT INTO {t['name']} ({', '.join(cols)}) VALUES ({qmarks})"
            for r in t["rows"]:
                if isinstance(r, dict): vals = [r.get(col, None) for col in cols]
                elif isinstance(r, (list, tuple)):
                    vals = list(r) + [None]*(len(cols)-len(r)); vals = vals[:len(cols)]
                else: continue
                cur.execute(insert_sql, vals)
        con.commit()
        cur.execute("INSERT OR REPLACE INTO session_meta(id, domain, schema_json) VALUES (1, ?, ?)",
                    (schema.get("domain","unknown"), json.dumps(schema)))
        con.commit()

def bootstrap_domain_with_llm_or_fallback(prev_domain: Optional[str], preferred_domain: str):
    obj, err, model_used, stats = llm_generate_domain_and_questions(prev_domain, preferred_domain)
    if obj is None:
        schema, questions = get_fallback_pack_for(preferred_domain)
        info = {"source":"fallback","model":None,"error":err,"accepted":0,"dropped":0}
        return schema, questions, info
    return obj, obj["questions"], {"source":"openai","model":model_used,"error":None,"accepted":stats["accepted_questions"],"dropped":stats["dropped_questions"]}

def install_schema_and_prepare_questions(prev_domain: Optional[str], preferred_domain: str):
    schema, questions, info = bootstrap_domain_with_llm_or_fallback(prev_domain, preferred_domain)
    install_schema(CONN, schema)
    if not questions:
        schema2, questions2 = get_fallback_pack_for(preferred_domain)
        install_schema(CONN, schema2)
        schema, questions, info = schema2, questions2, {"source":"fallback","model":None,"error":"No usable questions from LLM","accepted":0,"dropped":0}
    return schema, questions, info

# -------------------- Domain cycling --------------------
def _norm(s: str) -> str:
    return (s or "").strip().lower()

def _index_of_domain(name: str) -> int:
    low = _norm(name)
    for i, d in enumerate(ALLOWED_DOMAINS):
        if _norm(d) == low:
            return i
    return 0

def choose_next_domain(prev_domain: Optional[str]) -> str:
    global DOMAIN_CYCLE_POS
    if prev_domain is None:
        DOMAIN_CYCLE_POS = 0
        return ALLOWED_DOMAINS[DOMAIN_CYCLE_POS]
    idx = _index_of_domain(prev_domain)
    DOMAIN_CYCLE_POS = (idx + 1) % len(ALLOWED_DOMAINS)
    return ALLOWED_DOMAINS[DOMAIN_CYCLE_POS]

# -------------------- Initialize first domain --------------------
CURRENT_SCHEMA, CURRENT_QS, CURRENT_INFO = install_schema_and_prepare_questions(prev_domain=None, preferred_domain="bookstore")
DOMAIN_CYCLE_POS = _index_of_domain(CURRENT_SCHEMA.get("domain","bookstore"))

# -------------------- Progress + mastery --------------------
def upsert_user(con: sqlite3.Connection, user_id: str, name: str):
    with DB_LOCK:
        cur = con.cursor()
        cur.execute("SELECT user_id FROM users WHERE user_id = ?", (user_id,))
        if cur.fetchone() is None:
            cur.execute("INSERT INTO users (user_id, name, created_at) VALUES (?, ?, ?)",
                        (user_id, name, datetime.now(timezone.utc).isoformat()))
        else:
            cur.execute("UPDATE users SET name=? WHERE user_id=?", (name, user_id))
        con.commit()

CATEGORIES_ORDER = [
    "SELECT *", "SELECT columns", "WHERE", "Aliases",
    "JOIN (INNER)", "JOIN (LEFT)", "Aggregation", "VIEW", "CTAS / SELECT INTO"
]

def topic_stats(df_attempts: pd.DataFrame) -> pd.DataFrame:
    rows = []
    for cat in CATEGORIES_ORDER:
        sub = df_attempts[df_attempts["category"] == cat] if not df_attempts.empty else pd.DataFrame()
        att = int(sub.shape[0]) if not sub.empty else 0
        cor = int(sub["correct"].sum()) if not sub.empty else 0
        acc = float(cor / max(att, 1))
        rows.append({"category":cat,"attempts":att,"correct":cor,"accuracy":acc})
    return pd.DataFrame(rows)

def fetch_attempts(con: sqlite3.Connection, user_id: str) -> pd.DataFrame:
    with DB_LOCK:
        return pd.read_sql_query("SELECT * FROM attempts WHERE user_id=? ORDER BY id DESC", con, params=(user_id,))

def pick_next_question(user_id: str) -> Dict[str,Any]:
    pool = CURRENT_QS if CURRENT_QS else get_fallback_pack_for(CURRENT_SCHEMA.get("domain","bookstore"))[1]
    df = fetch_attempts(CONN, user_id)
    stats = topic_stats(df)
    stats = stats.sort_values(by=["accuracy","attempts"], ascending=[True, True]) if not stats.empty else stats
    weakest = stats.iloc[0]["category"] if not stats.empty else CATEGORIES_ORDER[0]
    cands = [q for q in pool if str(q.get("category","")).strip() == weakest] or pool
    return dict(random.choice(cands))

# -------------------- SQL execution & grading --------------------
def run_df(con: sqlite3.Connection, sql: str) -> pd.DataFrame:
    with DB_LOCK:
        return pd.read_sql_query(sql, con)

def rewrite_select_into(sql: str) -> Tuple[str, Optional[str]]:
    s = sql.strip().strip(";")
    if re.search(r"\bselect\b.+\binto\b.+\bfrom\b", s, flags=re.IGNORECASE|re.DOTALL):
        m = re.match(r"(?is)^\s*select\s+(.*?)\s+into\s+([A-Za-z_][A-Za-z0-9_]*)\s+from\s+(.*)$", s)
        if m:
            cols, tbl, rest = m.groups()
            return f"CREATE TABLE {tbl} AS SELECT {cols} FROM {rest}", tbl
    return sql, None

def detect_unsupported_joins(sql: str) -> Optional[str]:
    low = sql.lower()
    if " right join " in low:
        return "SQLite does not support RIGHT JOIN. Use LEFT JOIN in the opposite direction."
    if " full join " in low or " full outer join " in low:
        return "SQLite does not support FULL OUTER JOIN. Use LEFT JOIN plus UNION."
    if " ilike " in low:
        return "SQLite has no ILIKE. Use LOWER(col) LIKE LOWER('%pattern%')."
    return None

def detect_cartesian(con: sqlite3.Connection, sql: str, df_result: pd.DataFrame) -> Optional[str]:
    low = sql.lower()
    if " cross join " in low: return "Query uses CROSS JOIN (cartesian product). Ensure this is intended."
    comma_from = re.search(r"\bfrom\b\s+([a-z_]\w*)\s*,\s*([a-z_]\w*)", low)
    missing_on = (" join " in low) and (" on " not in low) and (" using " not in low) and (" natural " not in low)
    if comma_from or missing_on:
        try:
            with DB_LOCK:
                cur = con.cursor()
                if comma_from: t1, t2 = comma_from.groups()
                else:
                    m = re.search(r"\bfrom\b\s+([a-z_]\w*)", low); j = re.search(r"\bjoin\b\s+([a-z_]\w*)", low)
                    if not m or not j: return "Possible cartesian product: no join condition detected."
                    t1, t2 = m.group(1), j.group(1)
                cur.execute(f"SELECT COUNT(*) FROM {t1}"); n1 = cur.fetchone()[0]
                cur.execute(f"SELECT COUNT(*) FROM {t2}"); n2 = cur.fetchone()[0]
            prod = n1 * n2
            if len(df_result) == prod and prod > 0:
                return f"Result row count equals {n1}×{n2}={prod}. Likely cartesian product (missing join)."
        except Exception:
            return "Possible cartesian product: no join condition detected."
    return None

# Column enforcement policy — relax unless prompt makes it explicit
def should_enforce_columns(q: Dict[str, Any]) -> bool:
    """
    Enforce exact projection only when the prompt explicitly asks for columns,
    or when DDL creates a new object where column shape matters.
    """
    cat = (q.get("category") or "").strip()
    if cat in ("SELECT columns", "VIEW", "CTAS / SELECT INTO"):
        return True

    prompt = (q.get("prompt_md") or "")
    prompt_l = prompt.lower()

    # Signals that projection is explicitly requested
    if re.search(r"`[^`]+`", prompt):                     # backticked names
        return True
    if re.search(r"\((?:show|return|display)[^)]+\)", prompt_l):
        return True
    if re.search(r"\b(show|return|display|select)\b[^.]{0,120}\b(columns?|fields?|name|title|price|count|sum|avg|max|min)\b", prompt_l):
        return True

    return False

def _normalize_columns(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()
    out.columns = [str(c).strip().lower() for c in out.columns]
    return out

def results_equal_or_superset(df_student: pd.DataFrame, df_expected: pd.DataFrame) -> Tuple[bool, Optional[str]]:
    a = _normalize_columns(df_student); b = _normalize_columns(df_expected)
    if set(a.columns) == set(b.columns):
        a2 = a[sorted(a.columns)].sort_values(sorted(a.columns)).reset_index(drop=True)
        b2 = b[sorted(a.columns)].sort_values(sorted(a.columns)).reset_index(drop=True)
        return (a2.equals(b2), None)
    if set(b.columns).issubset(set(a.columns)):
        a_proj = a[b.columns]
        a2 = a_proj.sort_values(list(b.columns)).reset_index(drop=True)
        b2 = b.sort_values(list(b.columns)).reset_index(drop=True)
        if a2.equals(b2):
            return True, "extra_columns"
    return False, None

def results_equal_rowcount_only(df_student: pd.DataFrame, df_expected: pd.DataFrame) -> bool:
    return df_student.shape[0] == df_expected.shape[0]

def aliases_present(sql: str, required_aliases: List[str]) -> bool:
    low = re.sub(r"\s+", " ", (sql or "").lower())
    for al in (required_aliases or []):
        if f" {al}." not in low and f" as {al} " not in low:
            return False
    return True

def exec_student_sql(sql_text: str):
    if not sql_text or not sql_text.strip():
        return None, "Enter a SQL statement.", None, None
    sql_raw = sql_text.strip().rstrip(";")
    sql_rew, created_tbl = rewrite_select_into(sql_raw)
    note = "Rewrote `SELECT ... INTO` to `CREATE TABLE ... AS SELECT ...` for SQLite." if sql_rew != sql_raw else None
    unsup = detect_unsupported_joins(sql_rew)
    if unsup: return None, unsup, None, note
    try:
        low = sql_rew.lower()
        if low.startswith("select"):
            df = run_df(CONN, sql_rew)
            warn = detect_cartesian(CONN, sql_rew, df)
            return df, None, warn, note
        else:
            with DB_LOCK:
                cur = CONN.cursor()
                cur.execute(sql_rew); CONN.commit()
                if low.startswith("create view"):
                    m = re.match(r"(?is)^\s*create\s+view\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+(select.*)$", low)
                    name = m.group(2) if m else None
                    if name:
                        try: return pd.read_sql_query(f"SELECT * FROM {name}", CONN), None, None, note
                        except Exception: return None, "View created but could not be queried.", None, note
                if low.startswith("create table"):
                    tbl = created_tbl
                    if not tbl:
                        m = re.match(r"(?is)^\s*create\s+table\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low)
                        tbl = m.group(2) if m else None
                    if tbl:
                        try: return pd.read_sql_query(f"SELECT * FROM {tbl}", CONN), None, None, note
                        except Exception: return None, "Table created but could not be queried.", None, note
            return pd.DataFrame(), None, None, note
    except Exception as e:
        msg = str(e)
        if "no such table" in msg.lower(): return None, f"{msg}. Check table names for this randomized domain.", None, note
        if "no such column" in msg.lower(): return None, f"{msg}. Use correct column names or prefixes (alias.column).", None, note
        if "ambiguous column name" in msg.lower(): return None, f"{msg}. Qualify the column with a table alias.", None, note
        if "misuse of aggregate" in msg.lower() or "aggregate functions are not allowed in" in msg.lower():
            return None, f"{msg}. You might need a GROUP BY for non-aggregated columns.", None, note
        if "near \"into\"" in msg.lower() and "syntax error" in msg.lower():
            return None, "SQLite doesn’t support `SELECT ... INTO`. I can rewrite it automatically—try again.", None, note
        if "syntax error" in msg.lower():
            return None, f"Syntax error. Check commas, keywords, parentheses. Raw error: {msg}", None, note
        return None, f"SQL error: {msg}", None, note

def answer_df(answer_sql: List[str]) -> Optional[pd.DataFrame]:
    for sql in answer_sql:
        try:
            low = sql.strip().lower()
            if low.startswith("select"): return run_df(CONN, sql)
            if low.startswith("create view"):
                m = re.match(r"(?is)^\s*create\s+view\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low)
                view_name = m.group(2) if m else "vw_tmp"
                with DB_LOCK:
                    cur = CONN.cursor()
                    cur.execute(f"DROP VIEW IF EXISTS {view_name}")
                    cur.execute(sql); CONN.commit()
                return run_df(CONN, f"SELECT * FROM {view_name}")
            if low.startswith("create table"):
                m = re.match(r"(?is)^\s*create\s+table\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low)
                tbl = m.group(2) if m else None
                with DB_LOCK:
                    cur = CONN.cursor()
                    if tbl: cur.execute(f"DROP TABLE IF EXISTS {tbl}")
                    cur.execute(sql); CONN.commit()
                if tbl: return run_df(CONN, f"SELECT * FROM {tbl}")
        except Exception:
            continue
    return None

def _canonical_expects_left_join(q: Dict[str, Any]) -> bool:
    canon = " ".join(q.get("answer_sql", [])).lower()
    return " left join " in canon

def validate_answer(q: Dict[str,Any], student_sql: str, df_student: Optional[pd.DataFrame]) -> Tuple[bool, str]:
    df_expected = answer_df(q["answer_sql"])
    if df_expected is None:
        return (df_student is not None), f"**Explanation:** Your statement executed successfully for this task."
    if df_student is None:
        return False, f"**Explanation:** Expected data result differs."

    enforce_cols = should_enforce_columns(q)
    if enforce_cols:
        ok, note = results_equal_or_superset(df_student, df_expected)
        if ok and note == "extra_columns":
            return True, "**Note:** You returned extra columns. The rows match; try selecting only the requested columns next time."
        if ok:
            return True, "**Explanation:** Your result matches a canonical solution."
        return False, f"**Explanation:** Compare your result to a canonical solution."
    else:
        # Projection not enforced → compare coverage (row counts)
        if df_student.shape[0] == df_expected.shape[0]:
            return True, "**Explanation:** Columns weren’t specified for this task; row count matches the canonical answer."
        if df_student.shape[0] < df_expected.shape[0] and _canonical_expects_left_join(q):
            return False, "**Hint:** This task expects *every* entity (including those with zero related rows). Use a `LEFT JOIN` from the dimension table."
        return False, "**Explanation:** For this task we compared row counts (projection not enforced) and they didn’t match."

def log_attempt(user_id: str, qid: str, category: str, correct: bool, sql_text: str,
                time_taken: float, difficulty: int, source: str, notes: str):
    with DB_LOCK:
        cur = CONN.cursor()
        cur.execute("""
            INSERT INTO attempts (user_id, question_id, category, correct, sql_text, timestamp, time_taken, difficulty, source, notes)
            VALUES (?,?,?,?,?,?,?,?,?,?)
        """, (user_id, qid, category, int(correct), sql_text, datetime.now(timezone.utc).isoformat(),
              time_taken, difficulty, source, notes))
        CONN.commit()

# -------------------- UI callbacks --------------------
def start_session(name: str, session: dict):
    name = (name or "").strip()
    if not name:
        return (session,
                gr.update(value="Please enter your name to begin.", visible=True),
                gr.update(visible=False),
                gr.update(visible=False),
                draw_dynamic_erd(CURRENT_SCHEMA),
                gr.update(visible=False),
                pd.DataFrame(),
                pd.DataFrame())

    slug = "-".join(name.lower().split())
    user_id = slug[:64] if slug else f"user-{int(time.time())}"
    upsert_user(CONN, user_id, name)
    q = pick_next_question(user_id)
    session = {"user_id": user_id, "name": name, "qid": q["id"], "start_ts": time.time(), "q": q}

    prompt = q["prompt_md"]
    stats = topic_stats(fetch_attempts(CONN, user_id))
    erd = draw_dynamic_erd(CURRENT_SCHEMA)
    return (session,
            gr.update(value=f"**Question {q['id']}**\n\n{prompt}", visible=True),
            gr.update(visible=True),
            gr.update(value="", visible=True),
            erd,
            gr.update(visible=False),
            stats,
            pd.DataFrame())

def render_preview(sql_text: str, session: dict):
    if not session or "q" not in session:
        return gr.update(value="", visible=False), draw_dynamic_erd(CURRENT_SCHEMA)
    s = (sql_text or "").strip()
    if not s:
        return gr.update(value="", visible=False), draw_dynamic_erd(CURRENT_SCHEMA)
    hi_tables, hi_edges = sql_highlights(s, CURRENT_SCHEMA)
    erd = draw_dynamic_erd(CURRENT_SCHEMA, highlight_tables=hi_tables, highlight_edges=hi_edges)
    html = highlight_sql_html(s)
    return gr.update(value=html, visible=True), erd

def submit_answer(sql_text: str, session: dict):
    if not session or "user_id" not in session or "q" not in session:
        return gr.update(value="Start a session first.", visible=True), pd.DataFrame(), gr.update(visible=False), pd.DataFrame()
    user_id = session["user_id"]
    q = session["q"]
    elapsed = max(0.0, time.time() - session.get("start_ts", time.time()))
    df, err, warn, note = exec_student_sql(sql_text)
    details = []
    if note: details.append(f"ℹ️ {note}")
    if err:
        fb = f"❌ **Did not run**\n\n{err}"
        if details: fb += "\n\n" + "\n".join(details)
        log_attempt(user_id, q.get("id","?"), q.get("category","?"), False, sql_text, elapsed, int(q.get("difficulty",1)), "bank", " | ".join([err] + details))
        stats = topic_stats(fetch_attempts(CONN, user_id))
        return gr.update(value=fb, visible=True), pd.DataFrame(), gr.update(visible=True), stats
    alias_msg = None
    if q.get("requires_aliases") and not aliases_present(sql_text, q.get("required_aliases", [])):
        alias_msg = f"⚠️ This task asked for aliases {q.get('required_aliases', [])}. I didn’t detect them."
    is_correct, explanation = validate_answer(q, sql_text, df)
    if warn: details.append(f"⚠️ {warn}")
    if alias_msg: details.append(alias_msg)
    prefix = "✅ **Correct!**" if is_correct else "❌ **Not quite.**"
    feedback = prefix
    if details: feedback += "\n\n" + "\n".join(details)
    feedback += "\n\n" + explanation + "\n\n**One acceptable solution:**\n```sql\n" + q["answer_sql"][0].rstrip(";") + ";\n```"
    log_attempt(user_id, q["id"], q.get("category","?"), bool(is_correct), sql_text, elapsed, int(q.get("difficulty",1)), "bank", " | ".join(details))
    stats = topic_stats(fetch_attempts(CONN, user_id))
    return gr.update(value=feedback, visible=True), (df if df is not None else pd.DataFrame()), gr.update(visible=True), stats

def next_question(session: dict):
    if not session or "user_id" not in session:
        return session, gr.update(value="Start a session first.", visible=True), gr.update(visible=False), draw_dynamic_erd(CURRENT_SCHEMA), gr.update(visible=False)
    user_id = session["user_id"]
    q = pick_next_question(user_id)
    session["qid"] = q["id"]; session["q"] = q; session["start_ts"] = time.time()
    return session, gr.update(value=f"**Question {q['id']}**\n\n{q['prompt_md']}", visible=True), gr.update(value="", visible=True), draw_dynamic_erd(CURRENT_SCHEMA), gr.update(visible=False)

def show_hint(session: dict):
    if not session or "q" not in session:
        return gr.update(value="Start a session first.", visible=True)
    cat = session["q"].get("category","?")
    hint = {
        "SELECT *": "Use `SELECT * FROM table_name`.",
        "SELECT columns": "List columns: `SELECT col1, col2 FROM table_name`.",
        "WHERE": "Filter with `WHERE` and combine conditions using AND/OR.",
        "Aliases": "Use `table_name t` and qualify as `t.col`.",
        "JOIN (INNER)": "Join with `... INNER JOIN ... ON left.key = right.key`.",
        "JOIN (LEFT)": "LEFT JOIN keeps all rows from the left table.",
        "Aggregation": "Use aggregates and `GROUP BY` non-aggregated columns.",
        "VIEW": "`CREATE VIEW view_name AS SELECT ...`.",
        "CTAS / SELECT INTO": "SQLite uses `CREATE TABLE name AS SELECT ...`."
    }.get(cat, "Identify keys from the schema and join on them.")
    return gr.update(value=f"**Hint:** {hint}", visible=True)

def export_progress(user_name: str):
    slug = "-".join((user_name or "").lower().split())
    if not slug: return None
    user_id = slug[:64]
    with DB_LOCK:
        df = pd.read_sql_query("SELECT * FROM attempts WHERE user_id=? ORDER BY id DESC", CONN, params=(user_id,))
    os.makedirs(EXPORT_DIR, exist_ok=True)
    path = os.path.abspath(os.path.join(EXPORT_DIR, f"{user_id}_progress.csv"))
    (pd.DataFrame([{"info":"No attempts yet."}]) if df.empty else df).to_csv(path, index=False)
    return path

def _domain_status_md():
    if CURRENT_INFO.get("source","openai") == "openai":
        accepted = CURRENT_INFO.get("accepted",0); dropped = CURRENT_INFO.get("dropped",0)
        return (f"✅ **Domain via OpenAI** `{CURRENT_INFO.get('model','?')}` → **{CURRENT_SCHEMA.get('domain','?')}**. "
                f"Accepted questions: {accepted}, dropped: {dropped}.  \n"
                f"Tables: {', '.join(t['name'] for t in CURRENT_SCHEMA.get('tables', []))}.")
    err = CURRENT_INFO.get("error",""); err_short = (err[:160] + "…") if len(err) > 160 else err
    return f"⚠️ **OpenAI randomization unavailable** → using fallback **{CURRENT_SCHEMA.get('domain','?')}**.\n\n> Reason: {err_short}"

def list_tables_for_preview():
    df = run_df(CONN, """
        SELECT name FROM sqlite_master
        WHERE type in ('table','view')
          AND name NOT LIKE 'sqlite_%'
          AND name NOT IN ('users','attempts','session_meta')
        ORDER BY type, name
    """)
    return df["name"].tolist() if not df.empty else ["(no tables)"]

# Always reseed a question on randomize (creates a guest session if needed)
def regenerate_domain(session: dict):
    global CURRENT_SCHEMA, CURRENT_QS, CURRENT_INFO
    prev = CURRENT_SCHEMA.get("domain") if CURRENT_SCHEMA else None
    preferred = choose_next_domain(prev)
    CURRENT_SCHEMA, CURRENT_QS, CURRENT_INFO = install_schema_and_prepare_questions(prev_domain=prev, preferred_domain=preferred)
    erd = draw_dynamic_erd(CURRENT_SCHEMA)
    status = _domain_status_md()

    # Ensure a session (guest if needed)
    if not session or not session.get("user_id"):
        user_id = f"guest-{int(time.time())}"
        upsert_user(CONN, user_id, "Guest")
        session = {"user_id": user_id, "name": "Guest", "qid": None, "start_ts": time.time(), "q": None}

    # Seed next question for this session
    q = pick_next_question(session["user_id"])
    session.update({"qid": q["id"], "q": q, "start_ts": time.time()})

    stats = topic_stats(fetch_attempts(CONN, session["user_id"]))
    empty_df = pd.DataFrame()
    dd_update = gr.update(choices=list_tables_for_preview(), value=None)

    return (
        gr.update(value=status, visible=True),                 # regen_fb
        erd,                                                   # er_image
        gr.update(value=f"**Question {q['id']}**\n\n{q['prompt_md']}", visible=True),  # prompt_md
        gr.update(value="", visible=True),                     # sql_input
        dd_update,                                             # tbl_dd
        stats,                                                 # mastery_df
        empty_df,                                              # result_df
        session                                                # session_state
    )

def preview_table(tbl: str):
    try:
        if not tbl or tbl=="(no tables)":
            return pd.DataFrame()
        return run_df(CONN, f"SELECT * FROM {tbl} LIMIT 20")
    except Exception as e:
        return pd.DataFrame([{"error": str(e)}])

# -------------------- UI --------------------
SQL_PREVIEW_STYLES = """
<style>
:root {
  --sqlkw-core:  #1f6feb;  /* SELECT, FROM, WHERE */
  --sqlkw-join:  #b80672;  /* JOIN family */
  --sqlkw-group: #107c41;  /* GROUP BY / HAVING */
  --sqlkw-order: #6f42c1;  /* ORDER BY / LIMIT / OFFSET */
  --sqlkw-ddl:   #b26c12;  /* CREATE / INSERT / UPDATE / DELETE */
}
.sql-preview pre { white-space: pre-wrap; margin: 0; }
.sql-preview code {
  font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, "Liberation Mono", monospace;
  font-size: 13px;
}
.sql-preview .kw { font-weight: 700; }
.sql-preview .kw-core  { color: var(--sqlkw-core)  !important; }
.sql-preview .kw-join  { color: var(--sqlkw-join)  !important; }
.sql-preview .kw-group { color: var(--sqlkw-group) !important; }
.sql-preview .kw-order { color: var(--sqlkw-order) !important; }
.sql-preview .kw-ddl   { color: var(--sqlkw-ddl)   !important; }
</style>
"""


with gr.Blocks(title="Adaptive SQL Trainer — Randomized Domains") as demo:
    gr.HTML(SQL_PREVIEW_STYLES)
    gr.Markdown(
        """
        # 🧪 Adaptive SQL Trainer — Randomized Domains (SQLite)
        - Uses **OpenAI** (if configured) to randomize a domain (bookstore, retail sales, wholesaler,
          sales tax, oil & gas wells, marketing), generate **3–4 tables** and **8–12** questions.
        - Practice `SELECT`, `WHERE`, `JOIN` (INNER/LEFT), **aliases**, **views**, and **CTAS / SELECT INTO**.
        - **ERD shows tables only** and bolds the ones referenced in your SQL.
        """
    )

    with gr.Row():
        with gr.Column(scale=1):
            name_box = gr.Textbox(label="Your Name", placeholder="e.g., Jordan Alvarez")
            start_btn = gr.Button("Start / Resume Session", variant="primary")
            session_state = gr.State({"user_id": None, "name": None, "qid": None, "start_ts": None, "q": None})

            gr.Markdown("---")
            gr.Markdown("### Dataset Controls")
            regen_btn = gr.Button("🔀 Randomize Dataset (OpenAI)")
            regen_fb = gr.Markdown(_domain_status_md(), visible=True)

            gr.Markdown("---")
            gr.Markdown("### Instructor Tools")
            export_name = gr.Textbox(label="Export a student's progress (enter name)")
            export_btn = gr.Button("Export CSV")
            export_file = gr.File(label="Download progress")

            gr.Markdown("---")
            gr.Markdown("### Quick Table/View Preview (top 20 rows)")
            tbl_dd = gr.Dropdown(choices=list_tables_for_preview(), label="Pick table/view", interactive=True)
            tbl_btn = gr.Button("Preview")
            preview_df = gr.Dataframe(value=pd.DataFrame(), interactive=False)

        with gr.Column(scale=2):
            prompt_md = gr.Markdown(visible=False)
            sql_input = gr.Textbox(label="Your SQL", placeholder="Type SQL here (end ; optional).", lines=6, visible=False)
            preview_md = gr.HTML(visible=False)
            er_image = gr.Image(label="Entity Diagram", value=draw_dynamic_erd(CURRENT_SCHEMA), height=PLOT_HEIGHT)

            with gr.Row():
                submit_btn = gr.Button("Run & Submit", variant="primary")
                hint_btn = gr.Button("Hint")
                next_btn = gr.Button("Next Question ▶", visible=False)

            feedback_md = gr.Markdown("")

            gr.Markdown("---")
            gr.Markdown("### Your Progress by Category")
            mastery_df = gr.Dataframe(headers=["category","attempts","correct","accuracy"],
                                      col_count=(4,"dynamic"), row_count=(0,"dynamic"), interactive=False)

            gr.Markdown("---")
            gr.Markdown("### Result Preview")
            result_df = gr.Dataframe(value=pd.DataFrame(), interactive=False)

    start_btn.click(
        start_session,
        inputs=[name_box, session_state],
        outputs=[session_state, prompt_md, sql_input, preview_md, er_image, next_btn, mastery_df, result_df],
    )
    sql_input.change(
        render_preview,
        inputs=[sql_input, session_state],
        outputs=[preview_md, er_image],
    )
    submit_btn.click(
        submit_answer,
        inputs=[sql_input, session_state],
        outputs=[feedback_md, result_df, next_btn, mastery_df],
    )
    next_btn.click(
        next_question,
        inputs=[session_state],
        outputs=[session_state, prompt_md, sql_input, er_image, next_btn],
    )
    hint_btn.click(
        show_hint,
        inputs=[session_state],
        outputs=[feedback_md],
    )
    export_btn.click(
        export_progress,
        inputs=[export_name],
        outputs=[export_file],
    )
    regen_btn.click(
        regenerate_domain,
        inputs=[session_state],
        outputs=[regen_fb, er_image, prompt_md, sql_input, tbl_dd, mastery_df, result_df, session_state],
    )
    tbl_btn.click(
        preview_table,
        inputs=[tbl_dd],
        outputs=[preview_df]
    )

if __name__ == "__main__":
    demo.launch()