smartclickhouse/ts/smartclickhouse.classes.querybuilder.ts

import type { ClickhouseHttpClient } from './smartclickhouse.classes.httpclient.js';
import { ClickhouseResultSet } from './smartclickhouse.classes.resultset.js';
import { escapeClickhouseValue } from './smartclickhouse.types.js';
import type { TComparisonOperator } from './smartclickhouse.types.js';

interface IWhereClause {
  connector: 'AND' | 'OR' | '';
  expression: string;
}

export class ClickhouseQueryBuilder<T extends Record<string, any>> {
  private selectColumns: string[] = ['*'];
  private whereClauses: IWhereClause[] = [];
  private orderByClauses: string[] = [];
  private groupByClauses: string[] = [];
  private havingClauses: string[] = [];
  private limitValue: number | null = null;
  private offsetValue: number | null = null;

  constructor(
    private tableName: string,
    private database: string,
    private httpClient: ClickhouseHttpClient,
  ) {}

  // ---- SELECT ----

  public select<K extends keyof T & string>(...columns: K[]): this {
    this.selectColumns = columns;
    return this;
  }

  public selectRaw(...expressions: string[]): this {
    this.selectColumns = expressions;
    return this;
  }

  // ---- WHERE ----

  public where<K extends keyof T & string>(
    column: K,
    operator: TComparisonOperator,
    value: any,
  ): this {
    this.whereClauses.push({
      connector: '',
      expression: this.buildCondition(column, operator, value),
    });
    return this;
  }

  public and<K extends keyof T & string>(
    column: K,
    operator: TComparisonOperator,
    value: any,
  ): this {
    this.whereClauses.push({
      connector: 'AND',
      expression: this.buildCondition(column, operator, value),
    });
    return this;
  }

  public or<K extends keyof T & string>(
    column: K,
    operator: TComparisonOperator,
    value: any,
  ): this {
    this.whereClauses.push({
      connector: 'OR',
      expression: this.buildCondition(column, operator, value),
    });
    return this;
  }

  public whereRaw(expression: string): this {
    this.whereClauses.push({
      connector: this.whereClauses.length > 0 ? 'AND' : '',
      expression,
    });
    return this;
  }

  // ---- ORDER BY ----

  public orderBy(column: (keyof T & string) | string, direction: 'ASC' | 'DESC' = 'ASC'): this {
    this.orderByClauses.push(`${column} ${direction}`);
    return this;
  }

  // ---- GROUP BY ----

  public groupBy<K extends keyof T & string>(...columns: K[]): this {
    this.groupByClauses.push(...columns);
    return this;
  }

  public having(expression: string): this {
    this.havingClauses.push(expression);
    return this;
  }

  // ---- LIMIT / OFFSET ----

  public limit(count: number): this {
    this.limitValue = count;
    return this;
  }

  public offset(count: number): this {
    this.offsetValue = count;
    return this;
  }

  // ---- EXECUTION ----

  public async execute(): Promise<ClickhouseResultSet<T>> {
    const sql = this.toSQL();
    const rows = await this.httpClient.queryTyped<T>(sql);
    return new ClickhouseResultSet<T>(rows);
  }

  public async first(): Promise<T | null> {
    this.limitValue = 1;
    const result = await this.execute();
    return result.first();
  }

  public async count(): Promise<number> {
    const savedSelect = this.selectColumns;
    this.selectColumns = ['count() as _count'];
    const sql = this.toSQL();
    this.selectColumns = savedSelect;
    const rows = await this.httpClient.queryTyped<{ _count: string }>(sql);
    return rows.length > 0 ? parseInt(rows[0]._count, 10) : 0;
  }

  public async toArray(): Promise<T[]> {
    const result = await this.execute();
    return result.toArray();
  }

  // ---- SQL GENERATION ----

  public toSQL(): string {
    const parts: string[] = [];

    parts.push(`SELECT ${this.selectColumns.join(', ')}`);
    parts.push(`FROM ${this.database}.${this.tableName}`);

    const whereClause = this.buildWhereClause();
    if (whereClause) {
      parts.push(`WHERE ${whereClause}`);
    }

    if (this.groupByClauses.length > 0) {
      parts.push(`GROUP BY ${this.groupByClauses.join(', ')}`);
    }

    if (this.havingClauses.length > 0) {
      parts.push(`HAVING ${this.havingClauses.join(' AND ')}`);
    }

    if (this.orderByClauses.length > 0) {
      parts.push(`ORDER BY ${this.orderByClauses.join(', ')}`);
    }

    if (this.limitValue !== null) {
      parts.push(`LIMIT ${this.limitValue}`);
    }

    if (this.offsetValue !== null) {
      parts.push(`OFFSET ${this.offsetValue}`);
    }

    parts.push('FORMAT JSONEachRow');

    return parts.join(' ');
  }

  /**
   * Build the WHERE clause string. Reused by ClickhouseTable for UPDATE/DELETE.
   */
  public buildWhereClause(): string {
    if (this.whereClauses.length === 0) return '';

    return this.whereClauses
      .map((clause, index) => {
        if (index === 0) return clause.expression;
        return `${clause.connector} ${clause.expression}`;
      })
      .join(' ');
  }

  // ---- PRIVATE ----

  private buildCondition(column: string, operator: TComparisonOperator, value: any): string {
    if (operator === 'IN' || operator === 'NOT IN') {
      const escapedValues = Array.isArray(value)
        ? `(${value.map(escapeClickhouseValue).join(', ')})`
        : escapeClickhouseValue(value);
      return `${column} ${operator} ${escapedValues}`;
    }

    if (operator === 'BETWEEN') {
      if (Array.isArray(value) && value.length === 2) {
        return `${column} BETWEEN ${escapeClickhouseValue(value[0])} AND ${escapeClickhouseValue(value[1])}`;
      }
      throw new Error('BETWEEN operator requires a two-element array value');
    }

    return `${column} ${operator} ${escapeClickhouseValue(value)}`;
  }
}