"""
author: Yagnik Poshiya
organization: Webbrains Technologies Private Limited

Tests for SQL template parameter binding (CLAUDE.md §9 rule 3).

This is the code path where a bug becomes a SQL injection: values extracted
from a visitor's sentence are interpolated into a stored SQL template and then
executed against a customer's database.
"""

from __future__ import annotations

import os
import sqlite3
import sys

sys.path.insert(0, os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "src"))

from agent.orchestrator import (  # noqa: E402
    _bind_sql_template_params,
    _extract_sql_placeholders,
    _sql_literal,
)


def test_single_quotes_are_doubled():
    assert _sql_literal("O'Brien") == "'O''Brien'"


def test_injection_attempt_stays_inside_the_literal():
    hostile = "x'; DROP TABLE property; --"
    out = _sql_literal(hostile)
    # The closing quote the attacker supplied is doubled, so the statement
    # never terminates early and DROP stays inert text.
    assert out.startswith("'") and out.endswith("'")
    assert out.count("'") % 2 == 0
    assert "''" in out


def test_injection_through_a_bound_template_is_inert():
    sql = "SELECT * FROM property WHERE city = :city"
    bound, err = _bind_sql_template_params(sql, {"city": "x'; DROP TABLE property; --"})
    assert err is None
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE property (city TEXT)")
    con.execute("INSERT INTO property VALUES ('Vadodara')")
    con.execute(bound).fetchall()
    # The table survives: the payload was data, not syntax.
    assert con.execute("SELECT count(*) FROM property").fetchone()[0] == 1


def test_backslash_doubled_only_where_it_is_an_escape():
    value = r"C:\path"
    assert _sql_literal(value, "mysql") == r"'C:\\path'"
    assert _sql_literal(value, "mariadb") == r"'C:\\path'"
    # SQLite and Postgres treat backslash as an ordinary character; doubling it
    # there would silently store two backslashes.
    assert _sql_literal(value, "sqlite") == r"'C:\path'"


def test_backslash_round_trips_through_real_sqlite():
    value = r"C:\path\to\file"
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE t (v TEXT)")
    con.execute("INSERT INTO t VALUES (" + _sql_literal(value, "sqlite") + ")")
    assert con.execute("SELECT v FROM t").fetchone()[0] == value


def test_scalar_types():
    assert _sql_literal(None) == "NULL"
    assert _sql_literal(True) == "TRUE"
    assert _sql_literal(False) == "FALSE"
    assert _sql_literal(30) == "30"
    assert _sql_literal(3.5) == "3.5"


def test_missing_parameter_is_reported_not_guessed():
    _, err = _bind_sql_template_params("SELECT 1 WHERE a = :missing", {})
    assert err and "missing" in err.lower()


def test_all_placeholders_are_substituted():
    sql = "SELECT * FROM p WHERE city = :city AND price <= :max_price"
    bound, err = _bind_sql_template_params(sql, {"city": "Vadodara", "max_price": 3000000})
    assert err is None
    assert ":city" not in bound and ":max_price" not in bound
    assert "'Vadodara'" in bound and "3000000" in bound


def test_prefix_named_parameters_are_not_confused():
    # :city must not match inside :city_id, or the longer parameter gets a
    # mangled value spliced into it.
    sql = "SELECT * FROM p WHERE city = :city AND city_id = :city_id"
    bound, err = _bind_sql_template_params(sql, {"city": "Surat", "city_id": 7})
    assert err is None
    assert "'Surat'" in bound
    assert "= 7" in bound
    assert ":city" not in bound


def test_repeated_placeholder_is_replaced_everywhere():
    sql = "SELECT * FROM p WHERE a = :v OR b = :v"
    bound, err = _bind_sql_template_params(sql, {"v": "x"})
    assert err is None
    assert ":v" not in bound
    assert bound.count("'x'") == 2


def test_placeholder_extraction_ignores_postgres_casts():
    # "::text" is a cast, not a parameter called "text".
    assert "text" not in _extract_sql_placeholders("SELECT a::text FROM t WHERE b = :b")
    assert "b" in _extract_sql_placeholders("SELECT a::text FROM t WHERE b = :b")


def _main() -> int:
    tests = [(n, f) for n, f in sorted(globals().items()) if n.startswith("test_") and callable(f)]
    failed = 0
    for name, fn in tests:
        try:
            fn()
            print(f"  ✓ {name}")
        except AssertionError as exc:
            failed += 1
            print(f"  ✗ {name}: {exc or 'assertion failed'}")
    print(f"\n{len(tests) - failed} passed, {failed} failed")
    return 1 if failed else 0


if __name__ == "__main__":
    raise SystemExit(_main())
