from typing import Iterable, Optional, Sequence
from sqlalchemy import or_, and_, asc, desc, func
from sqlalchemy.sql.elements import BinaryExpression
from sqlalchemy.sql.schema import Column


def tokenized_ilike_filter(tokens: Sequence[str], columns: Sequence[Column]) -> Optional[BinaryExpression]:
    """Build an AND-of-ORs case-insensitive LIKE filter across columns for each token.

    Example: AND(OR(col1 ILIKE %t1%, col2 ILIKE %t1%), OR(col1 ILIKE %t2%, ...))
    Returns None when tokens or columns are empty.
    """
    if not tokens or not columns:
        return None
    per_token_clauses = []
    for term in tokens:
        term = str(term or "").strip()
        if not term:
            continue
        per_token_clauses.append(or_(*[c.ilike(f"%{term}%") for c in columns]))
    if not per_token_clauses:
        return None
    return and_(*per_token_clauses)


def apply_pagination(query, skip: int, limit: int):
    """Apply offset/limit pagination to a SQLAlchemy query in a DRY way."""
    return query.offset(skip).limit(limit)


def paginate_with_total(query, skip: int, limit: int, include_total: bool):
    """Return (items, total|None) applying pagination and optionally counting total.

    This avoids duplicating count + pagination logic at each endpoint.
    """
    total_count = query.count() if include_total else None
    items = apply_pagination(query, skip, limit).all()
    return items, total_count


def apply_sorting(query, sort_by: Optional[str], sort_dir: Optional[str], allowed: dict[str, list[Column]]):
    """Apply case-insensitive sorting per a whitelist of allowed fields.

    allowed: mapping from field name -> list of columns to sort by, in priority order.
    For string columns, compares using lower(column) for stable ordering.
    Unknown sort_by falls back to the first key in allowed.
    sort_dir: "asc" or "desc" (default asc)
    """
    if not allowed:
        return query
    normalized_sort_by = (sort_by or next(iter(allowed.keys()))).lower()
    normalized_sort_dir = (sort_dir or "asc").lower()
    is_desc = normalized_sort_dir == "desc"

    columns = allowed.get(normalized_sort_by)
    if not columns:
        columns = allowed.get(next(iter(allowed.keys())))
    if not columns:
        return query

    order_exprs = []
    for col in columns:
        try:
            expr = func.lower(col) if getattr(col.type, "python_type", str) is str else col
        except Exception:
            expr = col
        order_exprs.append(desc(expr) if is_desc else asc(expr))
    if order_exprs:
        query = query.order_by(*order_exprs)
    return query