import pytest
from jinja2sql import Jinja2SQL

from fansifter_common.adapters.db.jinja2sql_filters import (
    orderby_ci_filter,
    orderby_filter,
)


@pytest.fixture()
def j2sql() -> Jinja2SQL:
    j2sql = Jinja2SQL()
    j2sql.register_filter("orderby", orderby_filter, bind=True)
    return j2sql


@pytest.fixture()
def j2sql_ci() -> Jinja2SQL:
    j2sql = Jinja2SQL()
    j2sql.register_filter("orderby_ci", orderby_ci_filter, bind=True)
    return j2sql


def test_order_by(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby }}",
        context={"order_by": "email"},
    )

    assert sql == "SELECT * FROM users ORDER BY email ASC"


def test_order_by_with_direction(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby }}",
        context={"order_by": "email.desc"},
    )

    assert sql == "SELECT * FROM users ORDER BY email DESC"


def test_order_by_with_direction_nulls(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby }}",
        context={"order_by": "email.desc.nulls_first"},
    )

    assert sql == "SELECT * FROM users ORDER BY email DESC NULLS FIRST"


def test_order_by_multiple_columns(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby }}",
        context={
            "order_by": [
                "email",
                "age.desc.nulls_last",
            ]
        },
    )

    assert sql == "SELECT * FROM users ORDER BY email ASC, age DESC NULLS LAST"


def test_order_by_with_transform(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby }}",
        context={"order_by": "firstName.desc"},
    )

    assert sql == "SELECT * FROM users ORDER BY first_name DESC"


def test_order_by_ci(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby_ci(ci_fields=['email']) }}",
        context={"order_by": "email"},
    )

    assert sql == "SELECT * FROM users ORDER BY email COLLATE 'und-ci-ai' ASC"


def test_order_by_ci_with_direction(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby_ci(ci_fields=['email']) }}",
        context={"order_by": "email.desc"},
    )

    assert sql == "SELECT * FROM users ORDER BY email COLLATE 'und-ci-ai' DESC"


def test_order_by_ci_with_direction_nulls(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby_ci(ci_fields=['email']) }}",
        context={"order_by": "email.desc.nulls_first"},
    )

    assert (
        sql == "SELECT * FROM users ORDER BY email COLLATE 'und-ci-ai' DESC NULLS FIRST"
    )


def test_order_by_ci_applies_collate_only_to_listed_fields(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby_ci(ci_fields=['email']) }}",
        context={
            "order_by": [
                "email",
                "age.desc.nulls_last",
            ]
        },
    )

    assert (
        sql
        == "SELECT * FROM users ORDER BY email COLLATE 'und-ci-ai' ASC, age DESC NULLS LAST"
    )


def test_order_by_ci_multiple_ci_fields(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users"
        " ORDER BY {{ order_by | orderby_ci(ci_fields=['email', 'name']) }}",
        context={"order_by": ["email", "name.desc", "age"]},
    )

    assert sql == (
        "SELECT * FROM users ORDER BY"
        " email COLLATE 'und-ci-ai' ASC,"
        " name COLLATE 'und-ci-ai' DESC,"
        " age ASC"
    )


def test_order_by_ci_with_transform(j2sql_ci: Jinja2SQL) -> None:
    sql, params = j2sql_ci.from_string(
        "SELECT * FROM users"
        " ORDER BY {{ order_by | orderby_ci(ci_fields=['first_name']) }}",
        context={"order_by": "firstName.desc"},
    )

    assert sql == "SELECT * FROM users ORDER BY first_name COLLATE 'und-ci-ai' DESC"


def test_order_by_delegates_to_ci_when_ci_fields_given(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby(ci_fields=['email']) }}",
        context={"order_by": ["email.desc", "age"]},
    )

    assert sql == "SELECT * FROM users ORDER BY email COLLATE 'und-ci-ai' DESC, age ASC"


def test_order_by_ignores_empty_ci_fields(j2sql: Jinja2SQL) -> None:
    sql, params = j2sql.from_string(
        "SELECT * FROM users ORDER BY {{ order_by | orderby(ci_fields=[]) }}",
        context={"order_by": "email.desc"},
    )

    assert sql == "SELECT * FROM users ORDER BY email DESC"