"""Generic, schema-agnostic CRUD helpers for integration-test setup/teardown.

These functions build parameterised SQL from plain dicts, so they work against
any table without the library knowing its schema. Table names, seed builders
and domain fixtures stay in each repo; only these primitives live here.

The emitted SQL is **MySQL/MariaDB-specific** (backtick-quoted identifiers,
``%s`` placeholders and ``LAST_INSERT_ID()``), so use these with
:class:`test_fixtures.mysql.MySQLConnection` — not the Neo4j or Snowflake
connections this package also provides. They operate on any connection exposing
the ``execute`` and ``fetchone`` surface below.
"""

from typing import Any, Protocol

from .models import ExecuteResult


class Connection(Protocol):
    """Minimal connection surface the CRUD helpers depend on."""

    def fetchone(
        self, sql: str, params: tuple[Any, ...] | None = None
    ) -> dict[str, Any] | None: ...

    def execute(
        self, sql: str, params: tuple[Any, ...] | None = None
    ) -> ExecuteResult: ...


def _ident(name: str) -> str:
    """Backtick-quote an SQL identifier (table or column name).

    Identifiers cannot be passed as query parameters, so they are quoted
    instead. Empty names and names containing a backtick are rejected, both to
    prevent injection and to avoid emitting invalid SQL.
    """
    if not name or '`' in name:
        raise ValueError(f'Invalid SQL identifier: {name!r}')
    return f'`{name}`'


def _where(conditions: dict[str, Any]) -> tuple[str, tuple[Any, ...]]:
    """Build a ``WHERE`` clause and its params from a conditions dict.

    ``None`` values render as ``IS NULL`` (not ``= NULL``, which never matches).
    """
    clauses = []
    params = []
    for column, value in conditions.items():
        if value is None:
            clauses.append(f'{_ident(column)} IS NULL')
        else:
            clauses.append(f'{_ident(column)} = %s')
            params.append(value)
    if not clauses:
        return '', ()
    return ' WHERE ' + ' AND '.join(clauses), tuple(params)


def get_entity(
    conn: Connection, table: str, conditions: dict[str, Any]
) -> dict[str, Any] | None:
    """Return a single row from *table* matching *conditions*, or ``None``."""
    where, params = _where(conditions)
    return conn.fetchone(f'SELECT * FROM {_ident(table)}{where}', params)


def insert_entity(
    conn: Connection,
    table: str,
    values: dict[str, Any],
    *,
    id_column: str | None = None,
) -> dict[str, Any] | None:
    """Insert a row into *table* and optionally return the inserted row.

    When *id_column* is given the inserted row is fetched back and returned:
    by the supplied value if *values* contains *id_column*, otherwise via
    ``LAST_INSERT_ID()`` (auto-increment keys). Returns ``None`` when
    *id_column* is omitted.
    """
    if not values:
        raise ValueError('insert_entity requires at least one column value')
    columns = list(values)
    column_sql = ', '.join(_ident(column) for column in columns)
    placeholders = ', '.join(['%s'] * len(columns))
    conn.execute(
        f'INSERT INTO {_ident(table)} ({column_sql}) VALUES ({placeholders})',
        tuple(values[column] for column in columns),
    )
    if id_column is None:
        return None
    if id_column in values:
        return get_entity(conn, table, {id_column: values[id_column]})
    return conn.fetchone(
        f'SELECT * FROM {_ident(table)} WHERE {_ident(id_column)} = LAST_INSERT_ID()'
    )


def update_entity(
    conn: Connection,
    table: str,
    conditions: dict[str, Any],
    values: dict[str, Any],
) -> int:
    """Update rows in *table* matching *conditions*. Returns rows affected."""
    if not values:
        raise ValueError('update_entity requires at least one column value')
    if not conditions:
        raise ValueError(
            'update_entity requires conditions to avoid a full-table update'
        )
    set_sql = ', '.join(f'{_ident(column)} = %s' for column in values)
    where, where_params = _where(conditions)
    result = conn.execute(
        f'UPDATE {_ident(table)} SET {set_sql}{where}',
        tuple(values.values()) + where_params,
    )
    return result.rowcount


def delete_entity(conn: Connection, table: str, conditions: dict[str, Any]) -> int:
    """Delete rows from *table* matching *conditions*. Returns rows affected.

    Requires non-empty *conditions* so an accidental full-table delete can't
    happen. FK-safe teardown (children before parents) is the caller's
    responsibility: call ``delete_entity`` for child tables first.
    """
    if not conditions:
        raise ValueError(
            'delete_entity requires conditions to avoid a full-table delete'
        )
    where, params = _where(conditions)
    result = conn.execute(f'DELETE FROM {_ident(table)}{where}', params)
    return result.rowcount


def clone_row(
    conn: Connection,
    table: str,
    conditions: dict[str, Any],
    *,
    overrides: dict[str, Any] | None = None,
    id_column: str | None = None,
) -> dict[str, Any] | None:
    """Copy the row in *table* matching *conditions*, applying *overrides*.

    The source row is read, merged with *overrides*, and re-inserted. When
    *id_column* is given it is dropped from the copy (unless overridden) so the
    database assigns a fresh key, and the inserted row is returned.
    """
    source = get_entity(conn, table, conditions)
    if source is None:
        raise LookupError(f'No row in {table!r} matching {conditions!r} to clone')
    overrides = overrides or {}
    new_values = {**source, **overrides}
    if id_column is not None and id_column not in overrides:
        new_values.pop(id_column, None)
    return insert_entity(conn, table, new_values, id_column=id_column)