# edited by glg
from typing import Any, Iterable, Tuple

from pypos.core.utils.sql_identifier_utils import quote_sql_identifier


def _normalize_in_values(
    values: Iterable[Any],
    *,
    cast_int: bool = False,
    positive_only: bool = False,
    unique: bool = False,
) -> list[Any]:
    normalized: list[Any] = []
    seen = set()
    for raw in values or []:
        value: Any = raw
        if cast_int:
            try:
                value = int(raw)
            except (TypeError, ValueError):
                continue
            if positive_only and int(value) <= 0:
                continue
        if unique:
            key = value
            if key in seen:
                continue
            seen.add(key)
        normalized.append(value)
    return normalized


def build_in_clause_params(
    values: Iterable[Any],
    *,
    cast_int: bool = False,
    positive_only: bool = False,
    unique: bool = False,
) -> Tuple[str, tuple[Any, ...]]:
    normalized = _normalize_in_values(
        values,
        cast_int=cast_int,
        positive_only=positive_only,
        unique=unique,
    )
    if not normalized:
        raise ValueError("IN clause values kosong.")
    placeholders = ",".join(["?"] * len(normalized))
    return placeholders, tuple(normalized)


def build_identifier_in_clause(
    identifier: str,
    values: Iterable[Any],
    *,
    cast_int: bool = False,
    positive_only: bool = False,
    unique: bool = False,
) -> Tuple[str, tuple[Any, ...]]:
    quoted_identifier = quote_sql_identifier(identifier, strict=True)
    placeholders, params = build_in_clause_params(
        values,
        cast_int=cast_int,
        positive_only=positive_only,
        unique=unique,
    )
    # nosec B608
    return f"{quoted_identifier} IN ({placeholders})", params


def build_sql_with_identifier_in_clause(
    sql_prefix: str,
    identifier: str,
    values: Iterable[Any],
    *,
    sql_suffix: str = "",
    cast_int: bool = False,
    positive_only: bool = False,
    unique: bool = False,
) -> Tuple[str, tuple[Any, ...]]:
    where_sql, params = build_identifier_in_clause(
        identifier,
        values,
        cast_int=cast_int,
        positive_only=positive_only,
        unique=unique,
    )
    prefix = str(sql_prefix or "").strip()
    if not prefix:
        raise ValueError("sql_prefix wajib diisi.")
    suffix = str(sql_suffix or "").strip()
    suffix_sql = f" {suffix}" if suffix else ""
    # nosec B608
    return f"{prefix} {where_sql}{suffix_sql}", params


def build_pragma_table_info_sql(table_name: str) -> str:
    quoted_table = quote_sql_identifier(table_name, strict=True)
    # nosec B608
    return f"PRAGMA table_info({quoted_table})"


def _normalize_where_sql(where_sql: str) -> str:
    text = str(where_sql or "").strip()
    if not text:
        return ""
    if text.upper().startswith("WHERE "):
        return text
    return f"WHERE {text}"


def build_sql_with_optional_where(
    sql_prefix: str,
    where_sql: str = "",
    *,
    sql_suffix: str = "",
) -> str:
    prefix = str(sql_prefix or "").strip()
    if not prefix:
        raise ValueError("sql_prefix wajib diisi.")
    where_clause = _normalize_where_sql(where_sql)
    where_part = f" {where_clause}" if where_clause else ""
    suffix = str(sql_suffix or "").strip()
    suffix_part = f" {suffix}" if suffix else ""
    # nosec B608
    return f"{prefix}{where_part}{suffix_part}"


def build_insert_sql(table_name: str, columns: Iterable[str]) -> str:
    quoted_table = quote_sql_identifier(table_name, strict=True)
    normalized_columns = [quote_sql_identifier(col, strict=True) for col in columns or []]
    if not normalized_columns:
        raise ValueError("columns wajib diisi.")
    columns_sql = ", ".join(normalized_columns)
    placeholders = ", ".join(["?"] * len(normalized_columns))
    return f"INSERT INTO {quoted_table} ({columns_sql}) VALUES ({placeholders})"  # nosec B608


def build_update_sql(
    table_name: str,
    set_columns: Iterable[str],
    *,
    where_sql: str = "",
) -> str:
    quoted_table = quote_sql_identifier(table_name, strict=True)
    normalized_columns = [quote_sql_identifier(col, strict=True) for col in set_columns or []]
    if not normalized_columns:
        raise ValueError("set_columns wajib diisi.")
    set_clause = ", ".join(f"{col} = ?" for col in normalized_columns)
    where_clause = _normalize_where_sql(where_sql)
    where_part = f" {where_clause}" if where_clause else ""
    return f"UPDATE {quoted_table} SET {set_clause}{where_part}"  # nosec B608


def render_sql_template(sql_template: str, **fragments: str) -> str:
    template = str(sql_template or "").strip()
    if not template:
        raise ValueError("sql_template wajib diisi.")
    normalized = {
        str(key): str(value or "").strip()
        for key, value in dict(fragments or {}).items()
    }
    # nosec B608
    return template.format(**normalized)