query_builder.py

# -*- coding: utf-8 -*-
"""
Interactive WHERE clause builder for Zeko SQL Builder.
Generates Kotlin DSL code from user-provided parameters.
"""

from __future__ import annotations

from typing import List, Tuple

from rich.console import Console
from rich.panel import Panel
from rich.prompt import Prompt
from rich.table import Table
from rich import box

OPERATORS = {
    "1": ("eq", "="),
    "2": ("neq", "!="),
    "3": ("greater", ">"),
    "4": ("greaterEq", ">="),
    "5": ("less", "<"),
    "6": ("lessEq", "<="),
    "7": ("like", "LIKE"),
    "8": ("notLike", "NOT LIKE"),
    "9": ("inList", "IN (...)"),
    "10": ("isNull", "IS NULL"),
    "11": ("isNotNull", "IS NOT NULL"),
}


def interactive_where_builder(console: Console) -> None:
    """Run an interactive WHERE clause builder session."""
    console.print(
        Panel(
            "[bold]Interactive WHERE Clause Builder[/bold]\n\n"
            "Build a SELECT query with WHERE conditions step by step.\n"
            "The tool generates both Kotlin DSL code and the expected SQL output.",
            border_style="cyan",
        )
    )

    table_name = Prompt.ask("Table name", default="user")
    fields_raw = Prompt.ask("Fields (comma-separated)", default="id, name, age")
    fields = [f.strip() for f in fields_raw.split(",") if f.strip()]

    conditions: List[Tuple[str, str, str]] = []

    while True:
        console.print("\n[bold]Add a WHERE condition:[/bold]")

        op_table = Table(show_header=False, box=box.SIMPLE, padding=(0, 1))
        op_table.add_column("Key", style="yellow", width=4)
        op_table.add_column("Operator", style="cyan")
        op_table.add_column("SQL", style="white")
        for key, (dsl_op, sql_op) in OPERATORS.items():
            op_table.add_row(key, dsl_op, sql_op)
        console.print(op_table)

        op_choice = Prompt.ask("Operator number", default="1")
        if op_choice not in OPERATORS:
            console.print("[red]Invalid operator.[/red]")
            continue

        dsl_op, sql_op = OPERATORS[op_choice]
        field_name = Prompt.ask("Field name", default="id")

        value = ""
        if dsl_op not in ("isNull", "isNotNull"):
            value = Prompt.ask("Value", default="1")

        conditions.append((field_name, dsl_op, value))

        more = Prompt.ask("Add another condition? (y/n)", default="n")
        if more.lower() != "y":
            break

    kotlin_fields = ", ".join(f'"{f}"' for f in fields)
    dsl_lines = [f'Query().fields({kotlin_fields})']
    dsl_lines.append(f'    .from("{table_name}")')

    if conditions:
        where_parts = []
        for field, op, val in conditions:
            if op in ("isNull", "isNotNull"):
                where_parts.append(f'{op}("{field}")')
            elif op == "inList":
                where_parts.append(f'"{field}" {op} arrayOf({val})')
            elif val.isdigit():
                where_parts.append(f'"{field}" {op} {val}')
            else:
                where_parts.append(f'"{field}" {op} "{val}"')

        if len(where_parts) == 1:
            dsl_lines.append(f"    .where({where_parts[0]})")
        else:
            combined = " and\n        ".join(where_parts)
            dsl_lines.append(f"    .where(\n        {combined}\n    )")

    dsl_lines.append("    .toSql()")
    dsl_code = "\n".join(dsl_lines)

    sql_parts = []
    for field, op, val in conditions:
        if op == "isNull":
            sql_parts.append(f"{field} IS NULL")
        elif op == "isNotNull":
            sql_parts.append(f"{field} IS NOT NULL")
        elif op == "eq":
            sql_parts.append(f"{field} = {_sql_val(val)}")
        elif op == "neq":
            sql_parts.append(f"{field} != {_sql_val(val)}")
        elif op == "greater":
            sql_parts.append(f"{field} > {_sql_val(val)}")
        elif op == "greaterEq":
            sql_parts.append(f"{field} >= {_sql_val(val)}")
        elif op == "less":
            sql_parts.append(f"{field} < {_sql_val(val)}")
        elif op == "lessEq":
            sql_parts.append(f"{field} <= {_sql_val(val)}")
        elif op == "like":
            sql_parts.append(f"{field} LIKE '{val}'")
        elif op == "notLike":
            sql_parts.append(f"{field} NOT LIKE '{val}'")
        elif op == "inList":
            sql_parts.append(f"{field} IN ({val})")

    sql_select = f"SELECT {', '.join(fields)} FROM {table_name}"
    if sql_parts:
        sql_select += " WHERE " + " AND ".join(sql_parts)

    console.print()
    console.print(Panel.fit(f"[bold]Generated Kotlin DSL:[/bold]\n\n{dsl_code}", border_style="magenta"))
    console.print()
    console.print(Panel.fit(f"[bold]Expected SQL:[/bold]\n\n{sql_select}", border_style="green"))


def _sql_val(val: str) -> str:
    """Format a value for SQL display (numeric vs string)."""
    try:
        float(val)
        return val
    except ValueError:
        return f"'{val}'"