import type { IndexConfig, ModelType } from '@travetto/model';
import { ModelRegistryIndex } from '@travetto/model';
import { isModelIndexedIndex } from '@travetto/model-indexed';
import { isModelQueryIndex, ModelQueryUtil, type QueryIndexConfig, type SortClause, type WhereClause } from '@travetto/model-query';
import { type Class, castTo, JSONUtil, RuntimeError } from '@travetto/runtime';
import { DataUtil, type SchemaFieldConfig, SchemaRegistryIndex } from '@travetto/schema';

import { SQLModelSchemaUtil } from './schema.ts';
import type { JSONSqlPathMode, ResolvedPathContext, SchemaContext, TableContext } from './types.ts';

export interface TransactionStatements {
  begin: string;
  beginNested: string;
  isolate: string;
  rollback: string;
  rollbackNested: string;
  commit: string;
  commitNested: string;
}

interface QueryClause {
  sql?: string;
  parameters?: Record<string, unknown>;
}

type IdentificationPath = string;

function extractQueryIndexPathAndDirection(indexField: Record<string, unknown>): { path: string[]; sortDirection: 1 | -1 | true } {
  const path: string[] = [];
  let current: unknown = indexField;
  while (typeof current === 'object' && current !== null) {
    const keys = Object.keys(current);
    if (keys.length === 0) {
      break;
    }
    const key = keys[0];
    if (key.includes('.')) {
      path.push(...key.split('.'));
    } else {
      path.push(key);
    }
    current = castTo<Record<string, unknown>>(current)[key];
  }
  const sortDirection = castTo<1 | -1 | true>(current ?? 1);
  return { path, sortDirection };
}

/**
 * Abstract ANSI SQL-99 Dialect base implementation.
 * Pure SQL text generator and query builder (does not execute queries or hold connection state).
 */
export abstract class AbstractANSI99Dialect {
  static TRANSACTION_STATEMENTS: TransactionStatements = {
    begin: 'BEGIN;',
    beginNested: 'SAVEPOINT $1;',
    isolate: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED;',
    rollback: 'ROLLBACK;',
    rollbackNested: 'ROLLBACK TO $1;',
    commit: 'COMMIT;',
    commitNested: 'RELEASE SAVEPOINT $1;'
  };

  static SCHEMA_CACHE = new Map<Class, SchemaContext<unknown>>();

  returningSupport = false;
  suggestLikeOperator = 'LIKE';
  transactionStatements: TransactionStatements = AbstractANSI99Dialect.TRANSACTION_STATEMENTS;
  abstract getComplexColumnType(field: SchemaFieldConfig): string;

  getComplexColumnValue(field: SchemaFieldConfig, value: unknown): unknown {
    return value === null || value === undefined ? null : JSONUtil.toUTF8(value);
  }

  escapeIdentifier(name: string): string {
    return `"${name.replaceAll('"', '""')}"`;
  }

  escapeLiteral(value: string): string {
    return value.replaceAll("'", "''");
  }

  getPlaceholder(index: number): string {
    return '?';
  }

  abstract getColumnType(fieldConfiguration: SchemaFieldConfig): string;
  abstract compileJsonIndexPath(columnName: string, jsonPath: string[], mode: JSONSqlPathMode): string;
  abstract compileArrayAll(context: ResolvedPathContext, identifier: string, value: unknown[]): { sql: string; formatted: unknown };
  abstract compileArrayEquals(context: ResolvedPathContext, identifier: string, values: unknown): { sql: string; formatted: unknown };
  abstract compileArrayAny(context: ResolvedPathContext, identifier: string, values: unknown[]): { sql: string; formatted: unknown };
  abstract compileArrayExists(context: ResolvedPathContext, identifier?: string): { sql: string };
  abstract compileArrayRegex(context: ResolvedPathContext, identifier: string, value: RegExp | string): { sql: string; formatted: unknown };

  abstract getRegexOperator(caseInsensitive: boolean): string;
  abstract formatRegex(source: string, caseInsensitive: boolean): string;
  abstract castColumn(sqlPath: string, type: Class): string;

  compileJsonEquality?(sqlPath: string, identifier: string): string;
  shiftPlaceholders?(whereSQL: string, offset: number): string;

  buildSqlPath<T extends ModelType>(tableContext: TableContext<T>, path: string[], mode: JSONSqlPathMode): string {
    const firstSegment = path[0];
    const escapedFirst = this.escapeIdentifier(firstSegment);
    if (tableContext.simpleFields.has(firstSegment)) {
      if (path.length > 1) {
        throw new RuntimeError(
          `Cannot traverse nested properties under simple column "${firstSegment}" in table "${tableContext.tableName}"`,
          {
            category: 'data'
          }
        );
      }
      return escapedFirst;
    } else {
      const nestedSegments = path.slice(1);
      if (nestedSegments.length === 0) {
        return escapedFirst;
      }
      return this.compileJsonIndexPath(escapedFirst, nestedSegments, mode);
    }
  }

  formatJsonPath(jsonPath: string[]): string {
    return jsonPath.map(segment => (/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(segment) ? segment : `"${segment.replaceAll('"', '\\"')}"`)).join('.');
  }

  compileIndexPath(context: TableContext, path: string[], mode: JSONSqlPathMode): string {
    return this.resolvePath(context, path, mode).sqlPath;
  }

  getCreateIndexSQL<T extends ModelType>(context: TableContext, indexConfig: IndexConfig<string, T> | QueryIndexConfig<T>): string {
    const { tableName, cls: modelClass } = context;
    const indexName = ['idx', tableName, indexConfig.name.toLowerCase().replaceAll('-', '_')].join('_');

    if (isModelQueryIndex(indexConfig)) {
      const indexFields = indexConfig.fields.map(field => {
        const { path, sortDirection } = extractQueryIndexPathAndDirection(castTo(field));
        const isAscending = typeof sortDirection === 'number' ? sortDirection === 1 : !sortDirection;

        const expression = this.compileIndexPath(context, path, 'createIndex');
        const formattedExpression = path.length > 1 ? `(${expression})` : expression;
        return `${formattedExpression} ${isAscending ? 'ASC' : 'DESC'}`;
      });

      return `CREATE ${indexConfig.unique ? 'UNIQUE ' : ''}INDEX ${this.escapeIdentifier(indexName)} ON ${this.escapeIdentifier(context.tableName)} (${indexFields.join(', ')});`;
    } else if (isModelIndexedIndex(indexConfig)) {
      const allFields = [...indexConfig.keyTemplate, ...indexConfig.sortTemplate];
      const indexFields = allFields.map(({ path, value }) => {
        const expression = this.compileIndexPath(context, path, 'createIndex');
        const formattedExpression = path.length > 1 ? `(${expression})` : expression;
        return `${formattedExpression} ${value === -1 ? 'DESC' : 'ASC'}`;
      });

      const isUnique = 'unique' in indexConfig && indexConfig.unique;
      return `CREATE ${isUnique ? 'UNIQUE ' : ''}INDEX ${this.escapeIdentifier(indexName)} ON ${this.escapeIdentifier(context.tableName)} (${indexFields.join(', ')});`;
    }

    throw new RuntimeError(`Unsupported index configuration for class ${modelClass.name}`);
  }

  getCreateTableSQL(context: TableContext): string {
    const idType = this.getColumnType(castTo({ name: 'id', type: String }));
    const columnDefinitions: string[] = [`${this.escapeIdentifier('id')} ${idType} PRIMARY KEY`];

    for (const field of context.simpleFields.values()) {
      if (field.name === 'id') {
        continue;
      }
      const columnType = this.getColumnType(field);
      const isNotNullClause = SQLModelSchemaUtil.isColumnNotNull(context, field.name) ? ' NOT NULL' : '';
      columnDefinitions.push(`${this.escapeIdentifier(field.name)} ${columnType}${isNotNullClause}`);
    }

    for (const field of context.complexFields.values()) {
      const columnType = this.getComplexColumnType(field);
      const isNotNullClause = SQLModelSchemaUtil.isColumnNotNull(context, field.name) ? ' NOT NULL' : '';
      columnDefinitions.push(`${this.escapeIdentifier(field.name)} ${columnType}${isNotNullClause}`);
    }

    return `
CREATE TABLE ${this.escapeIdentifier(context.tableName)} (
  ${columnDefinitions.join(',\n  ')}
);
`.trim();
  }

  getCreateTableIndexSQLs(context: TableContext): string[] {
    const indexes = ModelRegistryIndex.getIndices(context.cls) || [];
    return indexes.map(indexConfig => this.getCreateIndexSQL(context, indexConfig));
  }

  getAddColumnSQL(context: TableContext, columnName: string, columnType: string): string {
    return `ALTER TABLE ${this.escapeIdentifier(context.tableName)} ADD COLUMN ${this.escapeIdentifier(columnName)} ${columnType};`;
  }

  abstract getUpsertSQL(
    context: TableContext,
    columns: string[],
    placeholders: string[],
    conflictTarget: string[],
    updates: string[]
  ): string;

  normalizeIndexDefinition(sql: string): string {
    return sql
      .toLowerCase()
      .replaceAll('"', '')
      .replaceAll("'", '')
      .replaceAll('`', '')
      .replaceAll(' ', '')
      .replaceAll('asc', '')
      .replaceAll('desc', '')
      .replaceAll('btree', '')
      .replaceAll('public.', '')
      .replaceAll('::text', '')
      .replaceAll('(', '')
      .replaceAll(')', '');
  }

  abstract getTableExistsQuery(context: TableContext): { sql: string; parameters?: unknown[] };
  abstract parseTableExistsResult(records: unknown[]): boolean;
  abstract getExistingColumnsQuery(context: TableContext): { sql: string; parameters?: unknown[] };
  abstract parseExistingColumns(records: unknown[]): Map<string, string>;
  abstract getExistingIndexesQuery(context: TableContext): { sql: string; parameters?: unknown[] };
  abstract parseExistingIndexes(records: unknown[]): Map<string, string>;
  abstract getDropIndexSQL(context: TableContext, indexName: string): string;
  abstract getTruncateTableSQL(context: TableContext): string;
  abstract isTableNotFoundError(error: unknown): boolean;

  getDropTableSQL(context: TableContext): string {
    return `DROP TABLE IF EXISTS ${this.escapeIdentifier(context.tableName)};`;
  }

  getAlterColumnTypeSQL?(context: TableContext, columnName: string, columnType: string, existingType: string): string | undefined;

  // Query Compilation
  static #combineResults(results: QueryClause[], operator: string): QueryClause {
    const filtered = results.filter(result => !!result.sql);

    if (filtered.length === 0) {
      return {};
    } else if (filtered.length === 1) {
      return filtered[0];
    } else {
      const fullOperator = ` ${operator} `;
      return {
        sql: `(${filtered.map(result => result.sql).join(fullOperator)})`,
        parameters: Object.assign({}, ...results.map(result => result.parameters))
      };
    }
  }

  compileWhere<T extends ModelType>(
    tableContext: TableContext<T>,
    where?: WhereClause<T>,
    checkExpiry = true
  ): {
    whereSQL?: string;
    parameters?: unknown[];
  } {
    const resolvedWhere = ModelQueryUtil.getWhereClause(tableContext.cls, where, checkExpiry);
    const compiled = this.#compileClause(tableContext, resolvedWhere);
    if (Object.entries(compiled.parameters ?? {}).length) {
      const parameters: unknown[] = [];
      const seen = new Map<string, string>();
      const sql = compiled
        .sql!.replace(/%%([^%]{0,200})%%/g, key => {
          if (!seen.has(key)) {
            parameters.push(compiled.parameters![key]);
            seen.set(key, this.getPlaceholder(parameters.length));
          }
          return seen.get(key)!;
        })
        .trim();
      return { whereSQL: sql, parameters };
    } else {
      return { whereSQL: compiled.sql?.trim() };
    }
  }

  compileSort<T extends ModelType>(tableContext: TableContext<T>, sort?: SortClause<T>[]): string {
    if (!sort || sort.length === 0) {
      return '';
    }
    const sortClauses = sort.map(sortClause => {
      const key = Object.keys(sortClause)[0];
      const direction = castTo<Record<string, 1 | -1>>(sortClause)[key];
      const path = key.split('.');
      const { sqlPath } = this.resolvePath(tableContext, path, 'orderBy');
      return `${sqlPath} ${direction === -1 ? 'DESC' : 'ASC'}`;
    });
    return sortClauses.length ? `ORDER BY ${sortClauses.join(', ')}` : '';
  }

  #resolveSchemaPath<T extends ModelType>(
    tableContext: TableContext<T>,
    path: string[]
  ): {
    leafField?: SchemaFieldConfig;
    arrayField?: SchemaFieldConfig;
    arraySegmentIndex?: number;
  } {
    const firstSegment = path[0];

    if (tableContext.simpleFields.has(firstSegment)) {
      if (path.length > 1) {
        throw new RuntimeError(
          `Cannot traverse nested properties under simple column "${firstSegment}" in table "${tableContext.tableName}"`,
          {
            category: 'data'
          }
        );
      }
      return { leafField: tableContext.simpleFields.get(firstSegment) };
    }

    let currentField: SchemaFieldConfig | undefined = tableContext.complexFields.get(firstSegment);
    let arrayField: SchemaFieldConfig | undefined = currentField?.array ? currentField : undefined;
    let arraySegmentIndex: number | undefined = currentField?.array ? 0 : undefined;
    let currentClass = currentField?.type;

    for (let pathIndex = 1; pathIndex < path.length; pathIndex += 1) {
      const segment = path[pathIndex];
      const subclassConfiguration = SchemaRegistryIndex.getOptional(currentClass!)?.get();
      currentField = subclassConfiguration?.fields[segment];
      if (currentField?.array && !arrayField) {
        arrayField = currentField;
        arraySegmentIndex = pathIndex;
      }
      currentClass = currentField?.type;
    }

    return {
      leafField: currentField,
      arrayField,
      arraySegmentIndex
    };
  }

  getSchemaSubPathMetadata(
    initialClass: Class | undefined,
    subPath: string[]
  ): Array<{ segment: string; fieldConfig?: SchemaFieldConfig; isArray: boolean; fieldClass?: Class }> {
    let currentClass = initialClass;
    return subPath.map(segment => {
      let fieldConfig: SchemaFieldConfig | undefined;
      let isArray = false;
      if (currentClass) {
        const classConfiguration = SchemaRegistryIndex.getOptional(currentClass)?.get();
        fieldConfig = classConfiguration?.fields[segment];
        if (fieldConfig) {
          isArray = !!fieldConfig.array;
          currentClass = fieldConfig.type;
        } else {
          currentClass = undefined;
        }
      }
      return { segment, fieldConfig, isArray, fieldClass: currentClass };
    });
  }

  resolvePath<T extends ModelType>(tableContext: TableContext<T>, path: string[], mode: JSONSqlPathMode): ResolvedPathContext {
    const firstSegment = path[0];

    if (tableContext.simpleFields.has(firstSegment)) {
      const { leafField } = this.#resolveSchemaPath(tableContext, path);
      return { sqlPath: this.buildSqlPath(tableContext, path, mode), leafField };
    }

    const { leafField, arrayField, arraySegmentIndex } = this.#resolveSchemaPath(tableContext, path);
    const sqlPath = this.buildSqlPath(tableContext, path, mode);

    const finalSqlPath = leafField && !leafField.array ? this.castColumn(sqlPath, leafField.type) : sqlPath;

    const arrayPath = arraySegmentIndex !== undefined ? path.slice(0, arraySegmentIndex + 1) : undefined;
    const subPath = arraySegmentIndex !== undefined ? path.slice(arraySegmentIndex + 1) : undefined;

    return {
      sqlPath: finalSqlPath,
      leafField,
      arrayField,
      arrayPath,
      subPath
    };
  }

  #compileClause<T extends ModelType>(
    tableContext: TableContext<T>,
    clause: WhereClause<T>,
    identificationPath: IdentificationPath = ''
  ): QueryClause {
    if (!clause) {
      return {};
    }
    if (ModelQueryUtil.has$And(clause)) {
      const compiled = clause.$and
        .map((item, index) => this.#compileClause(tableContext, item, `${identificationPath}_${index}`))
        .filter(Boolean);
      return AbstractANSI99Dialect.#combineResults(compiled, 'AND');
    } else if (ModelQueryUtil.has$Or(clause)) {
      const compiled = clause.$or
        .map((item, index) => this.#compileClause(tableContext, item, `${identificationPath}_${index}`))
        .filter(Boolean);
      return AbstractANSI99Dialect.#combineResults(compiled, 'OR');
    } else if (ModelQueryUtil.has$Not(clause)) {
      const compiled = this.#compileClause(tableContext, clause.$not, identificationPath);
      return compiled
        ? {
            sql: `NOT (${compiled.sql})`,
            parameters: compiled.parameters
          }
        : {};
    } else {
      return this.#compileSimple(tableContext, clause, [], identificationPath);
    }
  }

  #compileSimple<T extends ModelType>(
    tableContext: TableContext<T>,
    item: Record<string, unknown>,
    parentPath: string[] = [],
    identificationPath: IdentificationPath = ''
  ): QueryClause {
    if (!item) {
      return {};
    }
    const clauses: QueryClause[] = [];

    let index = 0;
    for (const [key, value] of Object.entries(item)) {
      index += 1;
      const currentPath = [...parentPath, key];
      const isPlainObject = DataUtil.isPlainObject(value);
      const firstKey = isPlainObject ? Object.keys(value)[0] : '';

      const nextIdentificationPath = `${identificationPath}__${index}`;

      if (isPlainObject) {
        if (firstKey.startsWith('$')) {
          clauses.push(this.#compileOperator(tableContext, currentPath, value as Record<string, unknown>, nextIdentificationPath));
        } else {
          clauses.push(this.#compileSimple(tableContext, value as Record<string, unknown>, currentPath, nextIdentificationPath));
        }
      } else {
        clauses.push(this.#compileOperator(tableContext, currentPath, { $eq: value }, nextIdentificationPath));
      }
    }

    return AbstractANSI99Dialect.#combineResults(clauses, 'AND');
  }

  #compileOperator<T extends ModelType>(
    tableContext: TableContext<T>,
    path: string[],
    operation: Record<string, unknown>,
    identificationPath: IdentificationPath = ''
  ): QueryClause {
    const resolvedContext = this.resolvePath(tableContext, path, 'read');
    const { sqlPath, leafField, arrayField } = resolvedContext;
    const effectiveArrayField = leafField?.array ? leafField : arrayField;
    const clauses: QueryClause[] = [];

    let index = 0;
    for (let [operator, value] of Object.entries(operation)) {
      index += 1;

      if (Array.isArray(value)) {
        value = value.map(valueItem => ModelQueryUtil.resolveComparator(valueItem));
      } else {
        value = ModelQueryUtil.resolveComparator(value);
      }

      const nestedIdentificationPath = `${identificationPath}_${index}`;
      const identifier = `%%${nestedIdentificationPath}%%`;

      let clause: QueryClause;

      if (effectiveArrayField) {
        if (operator === '$eq' || operator === '$ne') {
          const { sql, formatted } = this.compileArrayEquals(resolvedContext, identifier, value);
          const finalSql = operator === '$ne' ? `NOT(${sql})` : sql;
          clause = { parameters: { [identifier]: formatted }, sql: finalSql };
        } else if (operator === '$in' || operator === '$nin') {
          if (!Array.isArray(value) || value.length === 0) {
            clause = operator === '$in' ? { sql: '1=0' } : {};
          } else {
            const { sql, formatted } = this.compileArrayAny(resolvedContext, identifier, value);
            const finalSql = operator === '$nin' ? `NOT(${sql})` : sql;
            clause = { sql: finalSql, parameters: { [identifier]: formatted } };
          }
        } else if (operator === '$all') {
          if (!Array.isArray(value) || value.length === 0) {
            clause = { sql: '1=0' };
          } else {
            const { sql, formatted } = this.compileArrayAll(resolvedContext, identifier, value);
            clause = { sql, parameters: { [identifier]: formatted } };
          }
        } else if (operator === '$exists') {
          const { sql } = this.compileArrayExists(resolvedContext, identifier);
          const finalSql = !value ? `NOT(${sql})` : sql;
          clause = { sql: finalSql };
        } else if (operator === '$regex') {
          const { sql, formatted } = this.compileArrayRegex(resolvedContext, identifier, value as RegExp | string);
          clause = { sql, parameters: { [identifier]: formatted } };
        } else {
          throw new RuntimeError(`Operator "${operator}" is not supported for arrays`, { category: 'data' });
        }
      } else {
        if (operator === '$eq') {
          if (value === null || value === undefined) {
            clause = { sql: `${sqlPath} IS NULL` };
          } else {
            clause = {
              sql: `${sqlPath} = ${identifier}`,
              parameters: { [identifier]: value }
            };
          }
        } else if (operator === '$ne') {
          if (value === null || value === undefined) {
            clause = { sql: `${sqlPath} IS NOT NULL` };
          } else {
            clause = {
              sql: `${sqlPath} <> ${identifier}`,
              parameters: { [identifier]: value }
            };
          }
        } else if (operator === '$gt' || operator === '$gte' || operator === '$lt' || operator === '$lte') {
          const sqlOperator = operator === '$gt' ? '>' : operator === '$gte' ? '>=' : operator === '$lt' ? '<' : '<=';
          clause = {
            sql: `${sqlPath} ${sqlOperator} ${identifier}`,
            parameters: { [identifier]: value }
          };
        } else if (operator === '$in') {
          if (!Array.isArray(value) || value.length === 0) {
            clause = { sql: '1=0' };
          } else {
            const choices = value.map((valueItem, itemIndex) => {
              const innerIdentifier = `%%${nestedIdentificationPath}_${itemIndex}%%`;
              return [innerIdentifier, valueItem];
            });
            clause = {
              sql: `${sqlPath} IN (${choices.map(choice => choice[0]).join(', ')})`,
              parameters: Object.fromEntries(choices)
            };
          }
        } else if (operator === '$nin') {
          if (!Array.isArray(value) || value.length === 0) {
            clause = {};
          } else {
            const choices = value.map((valueItem, itemIndex) => {
              const innerIdentifier = `%%${nestedIdentificationPath}_${itemIndex}%%`;
              return [innerIdentifier, valueItem];
            });
            clause = {
              sql: `${sqlPath} NOT IN (${choices.map(choice => choice[0]).join(', ')})`,
              parameters: Object.fromEntries(choices)
            };
          }
        } else if (operator === '$exists') {
          clause = { sql: value ? `${sqlPath} IS NOT NULL` : `${sqlPath} IS NULL` };
        } else if (operator === '$regex') {
          const regex = value instanceof RegExp ? value : new RegExp(String(value));
          const caseInsensitive = regex.flags.includes('i');
          const regexOp = this.getRegexOperator(caseInsensitive);
          const regexSource = this.formatRegex(regex.source, caseInsensitive);
          clause = {
            parameters: { [identifier]: regexSource },
            sql: `${sqlPath} ${regexOp} ${identifier}`
          };
        } else {
          throw new RuntimeError(`Operator "${operator}" is not supported for scalar columns`, { category: 'data' });
        }
      }

      if (clause) {
        clauses.push(clause);
      }
    }
    return AbstractANSI99Dialect.#combineResults(clauses, 'AND');
  }

  // Statement Builders
  buildInsert<T extends ModelType>(tableContext: TableContext<T>, rawItem: Record<string, unknown>): { sql: string; values: unknown[] } {
    return this.buildInsertAll(tableContext, [rawItem]);
  }

  buildInsertAll<T extends ModelType>(
    tableContext: TableContext<T>,
    rawItems: Record<string, unknown>[]
  ): { sql: string; values: unknown[] } {
    if (rawItems.length === 0) {
      return { sql: '', values: [] };
    }

    const columns: string[] = [];
    for (const field of tableContext.simpleFields.values()) {
      columns.push(this.escapeIdentifier(field.name));
    }
    for (const field of tableContext.complexFields.values()) {
      columns.push(this.escapeIdentifier(field.name));
    }

    const values: unknown[] = [];
    const valueTuples: string[] = [];

    for (const rawItem of rawItems) {
      const tuplePlaceholders: string[] = [];
      for (const field of tableContext.simpleFields.values()) {
        tuplePlaceholders.push(this.getPlaceholder(values.length + 1));
        const value = rawItem[field.name];
        values.push(value === undefined || value === null ? null : value);
      }

      for (const field of tableContext.complexFields.values()) {
        tuplePlaceholders.push(this.getPlaceholder(values.length + 1));
        const value = rawItem[field.name];
        values.push(this.getComplexColumnValue(field, value));
      }

      valueTuples.push(`(${tuplePlaceholders.join(', ')})`);
    }

    const sql = `INSERT INTO ${this.escapeIdentifier(tableContext.tableName)} (${columns.join(', ')}) VALUES ${valueTuples.join(', ')};`;

    return { sql, values };
  }

  buildUpdateAll<T extends ModelType>(
    tableContext: TableContext<T>,
    rawItems: Record<string, unknown>[]
  ): { sql: string; values: unknown[] } {
    if (rawItems.length === 0) {
      return { sql: '', values: [] };
    }

    if (rawItems.length === 1) {
      const { whereSQL, parameters = [] } = this.compileWhere(tableContext, castTo({ id: rawItems[0].id }));
      return this.buildUpdate(tableContext, rawItems[0], whereSQL, parameters);
    }

    const simpleFieldsToUpdate = [...tableContext.simpleFields.values()].filter(field => field.name !== 'id');
    const complexFieldsToUpdate = [...tableContext.complexFields.values()];

    const setClauses: string[] = [];
    const values: unknown[] = [];

    const ids = rawItems.map(item => item.id);

    for (const field of simpleFieldsToUpdate) {
      const cases: string[] = [];
      for (const rawItem of rawItems) {
        const idPlaceholder = this.getPlaceholder(values.length + 1);
        values.push(rawItem.id);
        const valPlaceholder = this.getPlaceholder(values.length + 1);
        const val = rawItem[field.name];
        values.push(val === undefined || val === null ? null : val);
        cases.push(`WHEN ${idPlaceholder} THEN ${valPlaceholder}`);
      }
      setClauses.push(`${this.escapeIdentifier(field.name)} = CASE ${this.escapeIdentifier('id')} ${cases.join(' ')} END`);
    }

    for (const field of complexFieldsToUpdate) {
      const cases: string[] = [];
      for (const rawItem of rawItems) {
        const idPlaceholder = this.getPlaceholder(values.length + 1);
        values.push(rawItem.id);
        const valPlaceholder = this.getPlaceholder(values.length + 1);
        const val = rawItem[field.name];
        values.push(this.getComplexColumnValue(field, val));
        cases.push(`WHEN ${idPlaceholder} THEN ${valPlaceholder}`);
      }
      setClauses.push(`${this.escapeIdentifier(field.name)} = CASE ${this.escapeIdentifier('id')} ${cases.join(' ')} END`);
    }

    if (setClauses.length === 0) {
      setClauses.push(`${this.escapeIdentifier('id')} = ${this.escapeIdentifier('id')}`);
    }

    const whereIdPlaceholders = ids.map(idVal => {
      values.push(idVal);
      return this.getPlaceholder(values.length);
    });

    const tableName = this.escapeIdentifier(tableContext.tableName);
    const sql = `UPDATE ${tableName} SET ${setClauses.join(', ')} WHERE ${this.escapeIdentifier('id')} IN (${whereIdPlaceholders.join(', ')});`;

    return { sql, values };
  }

  buildUpdate<T extends ModelType>(
    tableContext: TableContext<T>,
    rawItem: Record<string, unknown>,
    whereSQL?: string,
    whereParameters: unknown[] = []
  ): { sql: string; values: unknown[] } {
    const sets: string[] = [];
    const values: unknown[] = [];

    for (const field of tableContext.simpleFields.values()) {
      if (field.name === 'id') {
        continue;
      }
      sets.push(`${this.escapeIdentifier(field.name)} = ${this.getPlaceholder(values.length + 1)}`);
      const value = rawItem[field.name];
      values.push(value === undefined || value === null ? null : value);
    }

    for (const field of tableContext.complexFields.values()) {
      sets.push(`${this.escapeIdentifier(field.name)} = ${this.getPlaceholder(values.length + 1)}`);
      const value = rawItem[field.name];
      values.push(this.getComplexColumnValue(field, value));
    }

    const shiftedWhereSQL = whereSQL && this.shiftPlaceholders ? this.shiftPlaceholders(whereSQL, values.length) : whereSQL;
    if (whereSQL) {
      values.push(...whereParameters);
    }

    const sql = `UPDATE ${this.escapeIdentifier(tableContext.tableName)} SET ${sets.join(', ')}${shiftedWhereSQL ? ` WHERE ${shiftedWhereSQL}` : ''};`;
    return { sql, values };
  }

  #buildUpdateSets<T extends ModelType>(tableContext: TableContext<T>, preparedData: Partial<T>): { sets: string[]; values: unknown[] } {
    const sets: string[] = [];
    const values: unknown[] = [];

    for (const [fieldName, value] of Object.entries(preparedData)) {
      const simpleField = tableContext.simpleFields.get(fieldName);
      if (simpleField) {
        sets.push(`${this.escapeIdentifier(fieldName)} = ${this.getPlaceholder(values.length + 1)}`);
        values.push(value === undefined || value === null ? null : value);
        continue;
      }

      const complexField = tableContext.complexFields.get(fieldName);
      if (complexField) {
        sets.push(`${this.escapeIdentifier(fieldName)} = ${this.getPlaceholder(values.length + 1)}`);
        values.push(this.getComplexColumnValue(complexField, value));
      }
    }

    return { sets, values };
  }

  compilePartialUpdate<T extends ModelType>(
    tableContext: TableContext<T>,
    preparedData: Partial<T>
  ): { sets: string[]; values: unknown[] } {
    return this.#buildUpdateSets(tableContext, preparedData);
  }

  buildPartialUpdate<T extends ModelType>(
    tableContext: TableContext<T>,
    preparedData: Partial<T>,
    whereSQL?: string,
    whereParameters: unknown[] = [],
    returning = false
  ): { sql: string; values: unknown[] } {
    const { sets, values } = this.#buildUpdateSets(tableContext, preparedData);

    const shiftedWhereSQL = whereSQL && this.shiftPlaceholders ? this.shiftPlaceholders(whereSQL, values.length) : whereSQL;
    if (whereSQL) {
      values.push(...whereParameters);
    }

    const returningClause = returning && this.returningSupport ? ' RETURNING *' : '';
    const sql = `UPDATE ${this.escapeIdentifier(tableContext.tableName)} SET ${sets.join(', ')}${shiftedWhereSQL ? ` WHERE ${shiftedWhereSQL}` : ''}${returningClause};`;

    return { sql, values };
  }

  buildUpsert<T extends ModelType>(
    tableContext: TableContext<T>,
    rawItem: Record<string, unknown>,
    conflictTarget: string[]
  ): { sql: string; values: unknown[] } {
    const columns: string[] = [];
    const values: unknown[] = [];
    const updates: string[] = [];

    for (const field of tableContext.simpleFields.values()) {
      columns.push(this.escapeIdentifier(field.name));
      const value = rawItem[field.name];
      values.push(value === undefined || value === null ? null : value);
      if (field.name !== 'id') {
        updates.push(`${this.escapeIdentifier(field.name)} = EXCLUDED.${this.escapeIdentifier(field.name)}`);
      }
    }

    for (const field of tableContext.complexFields.values()) {
      columns.push(this.escapeIdentifier(field.name));
      const value = rawItem[field.name];
      values.push(this.getComplexColumnValue(field, value));
      updates.push(`${this.escapeIdentifier(field.name)} = EXCLUDED.${this.escapeIdentifier(field.name)}`);
    }

    const placeholders = columns.map((_, index) => this.getPlaceholder(index + 1));
    const sql = this.getUpsertSQL(tableContext, columns, placeholders, conflictTarget, updates);

    return { sql, values };
  }

  buildSelect<T extends ModelType>(
    tableContext: TableContext<T>,
    options?: {
      whereSQL?: string;
      sortSQL?: string;
      limit?: number;
      offset?: number | string;
      columns?: string[];
    }
  ): string {
    const selectedColumns = options?.columns && options.columns.length > 0 ? options.columns.join(', ') : '*';
    const where = options?.whereSQL ? ` WHERE ${options.whereSQL}` : '';
    const sort = options?.sortSQL ? ` ${options.sortSQL}` : '';
    const limit = options?.limit !== undefined ? ` LIMIT ${options.limit}` : '';
    const offset = options?.offset !== undefined ? ` OFFSET ${options.offset}` : '';

    return `SELECT ${selectedColumns} FROM ${this.escapeIdentifier(tableContext.tableName)}${where}${sort}${limit}${offset};`;
  }

  buildDelete<T extends ModelType>(tableContext: TableContext<T>, whereSQL?: string): string {
    return `DELETE FROM ${this.escapeIdentifier(tableContext.tableName)}${whereSQL ? ` WHERE ${whereSQL}` : ''};`;
  }

  buildCount<T extends ModelType>(tableContext: TableContext<T>, whereSQL?: string): string {
    return `SELECT COUNT(*) as ${this.escapeIdentifier('total')} FROM ${this.escapeIdentifier(tableContext.tableName)}${whereSQL ? ` WHERE ${whereSQL}` : ''};`;
  }

  buildIndexSort<T extends ModelType>(
    tableContext: TableContext<T>,
    indexConfig: { sortTemplate: { path: string[]; value: number }[] }
  ): string {
    const sortClauses = indexConfig.sortTemplate.map(({ path, value }) => {
      const expression = this.compileIndexPath(tableContext, path, 'orderBy');
      return `${expression} ${value === -1 ? 'DESC' : 'ASC'}`;
    });
    return sortClauses.length ? `ORDER BY ${sortClauses.join(', ')}` : '';
  }

  buildFacet<T extends ModelType>(
    tableContext: TableContext<T>,
    resolvedContext: ResolvedPathContext,
    whereSQL?: string,
    limit?: number,
    offset?: number
  ): string {
    const isArray = Boolean(resolvedContext.leafField?.array || resolvedContext.arrayField);
    if (isArray) {
      return this.buildArrayFacet(tableContext, resolvedContext, whereSQL, limit, offset);
    }

    const sqlPath = resolvedContext.sqlPath;
    const keyClause = this.castColumn?.(sqlPath, String) ?? sqlPath;
    const countClause = this.castColumn?.('COUNT(*)', Number) ?? 'COUNT(*)';

    return `
SELECT ${keyClause} AS ${this.escapeIdentifier('key')}, ${countClause} AS ${this.escapeIdentifier('count')}
FROM ${this.escapeIdentifier(tableContext.tableName)}
WHERE ${sqlPath} IS NOT NULL
${whereSQL ? `AND ${whereSQL}` : ''}
GROUP BY ${sqlPath}
ORDER BY ${this.escapeIdentifier('count')} DESC
${limit !== undefined ? `LIMIT ${limit}` : ''}
${offset !== undefined ? `OFFSET ${offset}` : ''};`;
  }

  abstract buildArrayFacet<T extends ModelType>(
    tableContext: TableContext<T>,
    resolvedContext: ResolvedPathContext,
    whereSQL?: string,
    limit?: number,
    offset?: number
  ): string;

  buildFieldAggregate<T extends ModelType>(tableContext: TableContext<T>, sqlPath: string, isDate: boolean, whereSQL?: string): string {
    const where = whereSQL ? ` WHERE ${whereSQL}` : '';
    const fields = [
      `COUNT(${sqlPath}) AS ${this.escapeIdentifier('count')}`,
      `MIN(${sqlPath}) AS ${this.escapeIdentifier('min')}`,
      `MAX(${sqlPath}) AS ${this.escapeIdentifier('max')}`,
      ...(!isDate ? [`AVG(${sqlPath}) AS ${this.escapeIdentifier('avg')}`, `SUM(${sqlPath}) AS ${this.escapeIdentifier('sum')}`] : [])
    ];
    return `SELECT ${fields.join(', ')} FROM ${this.escapeIdentifier(tableContext.tableName)}${where};`;
  }
}