from collections.abc import Callable, Sequence
from typing import Any, Literal

import sqlalchemy as sa
import sqlalchemy.exc
from pydantic.alias_generators import to_camel, to_snake

TransformTo = Callable[[str], str] | Literal["camelcase", "snakecase"]


def apply_order_by(
    query: sa.Select[Any], *, order_by: Sequence[str] | None
) -> sa.Select[Any]:
    if not order_by:
        return query
    for value in order_by:
        column, direction, nulls = normalize_order_by(value, transform_to="snakecase")
        order_exp: sa.UnaryExpression[Any] = (
            sa.asc(column) if direction == "ASC" else sa.desc(column)
        )
        if nulls == "NULLS FIRST":
            order_exp = order_exp.nulls_first()
        elif nulls == "NULLS LAST":
            order_exp = order_exp.nulls_last()
        query = query.order_by(order_exp)
    return query


def normalize_order_by(
    value: str, transform_to: TransformTo = "snakecase"
) -> tuple[str, str, str]:
    parts = value.rsplit(".", maxsplit=2)
    if len(parts) == 1:
        column, direction, nulls = parts[0], "ASC", ""
    elif len(parts) == 2:
        column, direction, nulls = parts[0], parts[1].upper(), ""
    else:
        column, direction, nulls = (
            parts[0],
            parts[1].upper(),
            to_snake(parts[2]).replace("_", " ").upper(),
        )

    # Validate direction
    if direction not in ("ASC", "DESC"):
        direction = "ASC"

    # Transform column
    if transform_to == "camelcase":
        column = to_camel(column)
    elif transform_to == "snakecase":
        column = to_snake(column)
    elif callable(transform_to):
        column = transform_to(column)

    # Validate nulls
    if nulls != "" and nulls not in ("NULLS FIRST", "NULLS LAST"):
        nulls = ""

    return column, direction.upper(), nulls.upper()


def check_db_alive(url: sa.URL | str) -> bool:
    engine = sa.create_engine(url)
    try:
        with engine.connect() as conn:
            conn.execute(sa.text("SELECT 1"))
    except sqlalchemy.exc.OperationalError:
        return False
    finally:
        engine.dispose()
    return True