"""Tests for the database backend abstraction layer."""

from crystallise.db.backend import (
    BackendType,
    DatabaseBackend,
    convert_query_placeholders,
    convert_schema_syntax,
)


class TestDatabaseBackendSQLite:
    def test_memory_db_creates_successfully(self):
        db = DatabaseBackend(db_path=":memory:")
        assert db.backend_type == BackendType.SQLITE
        assert db.is_sqlite()
        assert not db.is_postgresql()
        db.close()

    def test_get_connection_context_manager(self):
        db = DatabaseBackend(db_path=":memory:")
        with db.get_connection() as conn:
            assert conn is not None
            conn.execute("SELECT 1")
        db.close()

    def test_execute_simple_query(self):
        db = DatabaseBackend(db_path=":memory:")
        with db.get_connection() as conn:
            conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)")
            conn.execute("INSERT INTO test (name) VALUES (?)", ("hello",))
            cursor = conn.execute("SELECT name FROM test")
            row = cursor.fetchone()
            assert row["name"] == "hello"
        db.close()

    def test_table_exists_returns_false_for_nonexistent(self):
        db = DatabaseBackend(db_path=":memory:")
        with db.get_connection() as conn:
            assert db.table_exists(conn, "nonexistent_table") is False
        db.close()

    def test_table_exists_returns_true_after_create(self):
        db = DatabaseBackend(db_path=":memory:")
        with db.get_connection() as conn:
            conn.execute("CREATE TABLE real_table (id INTEGER PRIMARY KEY)")
            assert db.table_exists(conn, "real_table") is True
        db.close()

    def test_execute_via_backend(self):
        db = DatabaseBackend(db_path=":memory:")
        with db.get_connection() as conn:
            db.execute(conn, "CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)")
            db.execute(conn, "INSERT INTO t (val) VALUES (?)", ("test",))
            cursor = db.execute(conn, "SELECT val FROM t")
            row = cursor.fetchone()
            assert row["val"] == "test"
        db.close()


class TestConvertQueryPlaceholders:
    def test_sqlite_is_identity(self):
        query = "SELECT * FROM table WHERE id = ? AND name = ?"
        result = convert_query_placeholders(query, BackendType.SQLITE)
        assert result == query

    def test_postgresql_converts_question_marks(self):
        query = "SELECT * FROM table WHERE id = ? AND name = ?"
        result = convert_query_placeholders(query, BackendType.POSTGRESQL)
        assert "?" not in result
        assert "%s" in result
        assert result == "SELECT * FROM table WHERE id = %s AND name = %s"

    def test_postgresql_preserves_strings(self):
        query = "SELECT * FROM t WHERE name = ? AND desc LIKE 'what?'"
        result = convert_query_placeholders(query, BackendType.POSTGRESQL)
        # The ? inside the string literal should NOT be converted
        assert "what?" in result
        # The parameter ? should be converted
        assert result.startswith("SELECT * FROM t WHERE name = %s")

    def test_postgresql_converts_datetime_now(self):
        query = "SELECT * FROM t WHERE created > datetime('now')"
        result = convert_query_placeholders(query, BackendType.POSTGRESQL)
        assert "CURRENT_TIMESTAMP" in result


class TestConvertSchemaSyntax:
    def test_postgresql_converts_autoincrement(self):
        schema = "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)"
        result = convert_schema_syntax(schema, BackendType.POSTGRESQL)
        assert "AUTOINCREMENT" not in result
        assert "SERIAL PRIMARY KEY" in result

    def test_postgresql_converts_datetime(self):
        schema = "CREATE TABLE test (id INTEGER, created DATETIME)"
        result = convert_schema_syntax(schema, BackendType.POSTGRESQL)
        assert "TIMESTAMP" in result
        assert "DATETIME" not in result

    def test_sqlite_converts_serial(self):
        schema = "CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT)"
        result = convert_schema_syntax(schema, BackendType.SQLITE)
        assert "INTEGER PRIMARY KEY AUTOINCREMENT" in result
        assert "SERIAL" not in result

    def test_sqlite_identity(self):
        schema = "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT)"
        result = convert_schema_syntax(schema, BackendType.SQLITE)
        assert result == schema