from src.spider_data import get_schema_ddl, build_prompt, format_chat, SYSTEM_PROMPT

def test_get_schema_ddl_returns_create_statements(tmp_path):
    import sqlite3
    db = tmp_path / "t.sqlite"
    con = sqlite3.connect(db)
    con.execute("CREATE TABLE singer (id INTEGER PRIMARY KEY, name TEXT)")
    con.commit(); con.close()
    ddl = get_schema_ddl(str(db))
    assert "CREATE TABLE" in ddl and "singer" in ddl and "name" in ddl

def test_build_prompt_has_schema_and_question():
    p = build_prompt("CREATE TABLE t(x INT)", "How many rows?")
    assert "CREATE TABLE t(x INT)" in p and "How many rows?" in p and SYSTEM_PROMPT in p

def test_format_chat_shape():
    m = format_chat("CREATE TABLE t(x INT)", "q?", "SELECT COUNT(*) FROM t;")
    assert m["messages"][0]["role"] == "user"
    assert m["messages"][1]["role"] == "assistant"
    assert m["messages"][1]["content"] == "SELECT COUNT(*) FROM t;"