reference.py

# -*- coding: utf-8 -*-
"""
DSL reference content and examples for Zeko SQL Builder.
Organized by topic: queries, joins, WHERE, aggregations.
"""

from __future__ import annotations

from typing import List, Tuple


def get_query_examples() -> List[Tuple[str, str, str]]:
    """Returns list of (title, kotlin_code, sql_result) tuples."""
    return [
        (
            "Simple SELECT",
            'Query().fields("*").from("user").toSql()\n'
            'Query().fields("id", "name", "age").from("user").toSql()',
            "SELECT * FROM user\n"
            "SELECT id, name, age FROM user",
        ),
        (
            "SELECT with WHERE",
            'Query().fields("id", "name", "age")\n'
            '    .from("user")\n'
            '    .where("name" eq "Bat Man")\n'
            "    .toSql()",
            "SELECT id, name, age FROM user WHERE name = 'Bat Man'",
        ),
        (
            "Complex WHERE with subqueries",
            'Query().fields("id", "name", "age")\n'
            '    .from("user")\n'
            "    .where(\n"
            '        "name" eq "Bat Man" and\n'
            '        ("id" greater 1) and\n'
            '        sub(("name" like "%bat") or ("name" like "man%")) and\n'
            '        isNotNull("nickname")\n'
            "    ).toSql()",
            "SELECT id, name, age FROM user\n"
            "WHERE name = 'Bat Man' AND id > 1\n"
            "AND (name LIKE '%bat' OR name LIKE 'man%')\n"
            "AND nickname IS NOT NULL",
        ),
        (
            "INSERT query",
            'Query().table("user")\n'
            '    .insert("name", "age", "email")\n'
            "    .toSql()",
            "INSERT INTO user (name, age, email) VALUES (?, ?, ?)",
        ),
        (
            "UPDATE query",
            'Query().table("user")\n'
            '    .update("name", "age")\n'
            '    .where("id" eq 1)\n'
            "    .toSql()",
            "UPDATE user SET name = ?, age = ? WHERE id = 1",
        ),
        (
            "DELETE query",
            'Query().table("user")\n'
            '    .delete()\n'
            '    .where("status" eq 0)\n'
            "    .toSql()",
            "DELETE FROM user WHERE status = 0",
        ),
        (
            "ORDER BY and LIMIT",
            'Query().fields("id", "name")\n'
            '    .from("user")\n'
            '    .order("name", "id DESC")\n'
            "    .limit(10, 20)\n"
            "    .toSql()",
            "SELECT id, name FROM user ORDER BY name, id DESC LIMIT 10 OFFSET 20",
        ),
    ]


def get_dsl_reference() -> List[Tuple[str, List[Tuple[str, str, str]]]]:
    """Returns list of (section_title, [(method, description, example)]) tuples."""
    return [
        (
            "Query Construction",
            [
                ("Query()", "Create a new query builder", "val q = Query()"),
                (".fields(...)", "Specify SELECT fields", '.fields("id", "name")'),
                (".from(table)", "Set the FROM table", '.from("user")'),
                (".table(name)", "Set table context for multi-table queries", '.table("user")'),
                (".toSql()", "Generate the SQL string", "val sql = q.toSql()"),
                (".toPreparedSql()", "Generate parameterized SQL", "val (sql, params) = q.toPreparedSql()"),
            ],
        ),
        (
            "WHERE Conditions",
            [
                ("eq", "Equals (=)", '"name" eq "John"'),
                ("neq", "Not equals (!=)", '"status" neq 0'),
                ("greater", "Greater than (>)", '"age" greater 18'),
                ("greaterEq", "Greater than or equal (>=)", '"score" greaterEq 50'),
                ("less", "Less than (<)", '"price" less 100'),
                ("lessEq", "Less than or equal (<=)", '"qty" lessEq 10'),
                ("like", "LIKE pattern match", '"name" like "%john%"'),
                ("notLike", "NOT LIKE pattern match", '"name" notLike "%test%"'),
                ("isNull(field)", "IS NULL check", 'isNull("deleted_at")'),
                ("isNotNull(field)", "IS NOT NULL check", 'isNotNull("email")'),
                ("inList", "IN list", '"id" inList arrayOf(1, 2, 3)'),
                ("notInList", "NOT IN list", '"id" notInList arrayOf(4, 5)'),
                ("between", "BETWEEN range", '"age" between Pair(18, 65)'),
                ("regexp", "REGEXP match", '"code" regexp "^[A-Z]{3}"'),
                ("sub(...)", "Group nested conditions", 'sub(cond1 or cond2)'),
            ],
        ),
        (
            "Modifiers",
            [
                (".where(...)", "Add WHERE clause", '.where("id" eq 1)'),
                (".order(...)", "Add ORDER BY", '.order("name", "id DESC")'),
                (".limit(n, offset)", "Add LIMIT with offset", ".limit(10, 20)"),
                (".groupBy(...)", "Add GROUP BY", '.groupBy("role_id")'),
                (".having(...)", "Add HAVING clause", ".having(count(\"id\") greater 5)"),
                (".distinct()", "Add DISTINCT", ".distinct()"),
            ],
        ),
        (
            "DML Operations",
            [
                (".insert(...)", "Build INSERT statement", '.insert("name", "age")'),
                (".update(...)", "Build UPDATE statement", '.update("name", "age")'),
                (".delete()", "Build DELETE statement", ".delete()"),
            ],
        ),
    ]


def get_join_reference() -> Tuple[List[Tuple[str, str, str]], str]:
    """Returns (join_types_list, full_example_code)."""
    join_types = [
        (".innerJoin(table)", "INNER JOIN", '.innerJoin("role").on("user_id = user.id")'),
        (".leftJoin(table)", "LEFT JOIN", '.leftJoin("address").on("user_id = user.id")'),
        (".rightJoin(table)", "RIGHT JOIN", '.rightJoin("orders").on("user_id = user.id")'),
        (".crossJoin(table)", "CROSS JOIN", '.crossJoin("config")'),
        (".fullJoin(table)", "FULL OUTER JOIN", '.fullJoin("audit").on("user_id = user.id")'),
        (".on(condition)", "ON clause for join", '.on("user_id = user.id")'),
    ]

    example = (
        'Query()\n'
        '    .table("user").fields("id", "name")\n'
        '    .table("role").fields("id", "role_name", "user.id = user_id")\n'
        '    .table("address").fields("id", "street1", "street2", "user.id = user_id")\n'
        '    .from("user")\n'
        '    .leftJoin("address").on("user_id = user.id")\n'
        '    .leftJoin("user_has_role").on("user_id = user.id")\n'
        '    .leftJoin("role").on("id = user_has_role.role_id")\n'
        "    .where(\n"
        '        "user.status" greater 0 or\n'
        '        ("user.id" notInList arrayOf(1, 2, 3))\n'
        "    )\n"
        '    .groupBy("role.id", "role.name")\n'
        "    .having(\n"
        '        sumGt("role.id", 2),\n'
        '        count("role.id") less 10\n'
        "    )\n"
        '    .order("user.id")\n'
        "    .limit(10, 20)\n"
        "    .toSql()"
    )

    return join_types, example


def get_aggregation_reference() -> Tuple[List[Tuple[str, str]], str]:
    """Returns (aggregation_functions_list, custom_aggregation_example)."""
    agg_funcs = [
        ("sum(field)", "SUM(field)"),
        ("count(field)", "COUNT(field)"),
        ("avg(field)", "AVG(field)"),
        ("min(field)", "MIN(field)"),
        ("max(field)", "MAX(field)"),
        ("sumEq(field, value)", "SUM(field) = value"),
        ("countEq(field, value)", "COUNT(field) = value"),
        ("avgEq(field, value)", "AVG(field) = value"),
        ("minEq(field, value)", "MIN(field) = value"),
        ("maxEq(field, value)", "MAX(field) = value"),
        ("sumGt(field, value)", "SUM(field) > value"),
        ("countGt(field, value)", "COUNT(field) > value"),
        ("avgGt(field, value)", "AVG(field) > value"),
        ("minGt(field, value)", "MIN(field) > value"),
        ("maxGt(field, value)", "MAX(field) > value"),
        ("sumLt(field, value)", "SUM(field) < value"),
        ("countLt(field, value)", "COUNT(field) < value"),
        ("avgLt(field, value)", "AVG(field) < value"),
        ("minLt(field, value)", "MIN(field) < value"),
        ("maxLt(field, value)", "MAX(field) < value"),
        ('agg(name, field, op, value)', "Custom aggregation function"),
    ]

    custom_example = (
        "You can define custom aggregation functions:\n\n"
        'fun regr_r2(field: String, value: Double): QueryBlock {\n'
        '    return agg("regr_r2", field, "=", value)\n'
        "}\n\n"
        'fun regr_r2_gt(field: String, value: Double): QueryBlock {\n'
        '    return agg("regr_r2", field, ">", value)\n'
        "}"
    )

    return agg_funcs, custom_example