/**
 * Type-safe Query Builder Implementation
 *
 * Full implementation of the query builder with type-safe SQL generation.
 */

import type { Sql, Row } from '../types.js'
import type {
  TableDefinition,
  SelectionSpec,
  WhereClause,
  OrderBySpec,
  JoinType,
  JoinCondition,
  InsertValues,
  UpdateSet,
  OnConflictAction,
  ConflictTarget,
  SQLExpression,
  SelectQueryBuilder,
  InsertQueryBuilder,
  UpdateQueryBuilder,
  DeleteQueryBuilder,
  JoinQueryBuilder,
  InferSelectResult,
  InferTableRow,
  JoinResult,
  ColumnRefs as _ColumnRefs,
} from './types.js'
import { columnsOf, and } from './expressions.js'

// ============================================================================
// SELECT Query Builder Implementation
// ============================================================================

class SelectQueryBuilderImpl<
  T extends TableDefinition,
  TSelection extends SelectionSpec<T> = '*',
  TResult = InferSelectResult<T, TSelection>[],
> implements SelectQueryBuilder<T, TSelection, TResult> {
  private _distinct = false
  private _selection: TSelection = '*' as TSelection
  private _where: SQLExpression<boolean>[] = []
  private _orderBy: { column: string; direction: 'ASC' | 'DESC'; nulls?: 'FIRST' | 'LAST' | undefined }[] = []
  private _limit?: number | undefined
  private _offset?: number | undefined
  private _groupBy: string[] = []
  private _having?: SQLExpression<boolean> | undefined

  constructor(
    private client: Sql,
    private table: T
  ) {}

  select<TNewSelection extends SelectionSpec<T>>(
    selection: TNewSelection
  ): SelectQueryBuilder<T, TNewSelection, InferSelectResult<T, TNewSelection>[]> {
    const builder = new SelectQueryBuilderImpl<T, TNewSelection>(this.client, this.table)
    builder._distinct = this._distinct
    builder._selection = selection
    builder._where = [...this._where]
    builder._orderBy = [...this._orderBy]
    builder._limit = this._limit
    builder._offset = this._offset
    builder._groupBy = [...this._groupBy]
    builder._having = this._having
    return builder as SelectQueryBuilder<T, TNewSelection, InferSelectResult<T, TNewSelection>[]>
  }

  where(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult> {
    const expr = this.resolveWhereClause(condition)
    this._where = [expr]
    return this
  }

  andWhere(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult> {
    const expr = this.resolveWhereClause(condition)
    this._where.push(expr)
    return this
  }

  orWhere(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult> {
    if (this._where.length === 0) {
      return this.where(condition)
    }

    const existingConditions = this._where.length === 1 ? this._where[0]! : and(...this._where)
    const newCondition = this.resolveWhereClause(condition)

    // Create OR expression
    const orExpr: SQLExpression<boolean> = {
      _type: true as boolean,
      _brand: 'sql_expression',
      toSQL: () => {
        const leftSql = existingConditions.toSQL()
        const rightSql = newCondition.toSQL()
        // Adjust right-side parameter indices
        const adjustedRightSql = rightSql.sql.replace(
          /\$(\d+)/g,
          (_, n) => `$${parseInt(n) + leftSql.params.length}`
        )
        return {
          sql: `(${leftSql.sql}) OR (${adjustedRightSql})`,
          params: [...leftSql.params, ...rightSql.params],
        }
      },
    }

    this._where = [orExpr]
    return this
  }

  orderBy(...specs: OrderBySpec<T>[]): SelectQueryBuilder<T, TSelection, TResult> {
    for (const spec of specs) {
      if (typeof spec === 'string') {
        this._orderBy.push({ column: spec, direction: 'ASC' })
      } else if (typeof spec === 'object') {
        this._orderBy.push({
          column: spec.column as string,
          direction: spec.direction,
          nulls: 'nulls' in spec ? spec.nulls : undefined,
        })
      }
    }
    return this
  }

  limit(count: number): SelectQueryBuilder<T, TSelection, TResult> {
    this._limit = count
    return this
  }

  offset(count: number): SelectQueryBuilder<T, TSelection, TResult> {
    this._offset = count
    return this
  }

  groupBy(...columns: (keyof T['columns'])[]): SelectQueryBuilder<T, TSelection, TResult> {
    this._groupBy.push(...columns.map(c => String(c)))
    return this
  }

  having(condition: SQLExpression<boolean>): SelectQueryBuilder<T, TSelection, TResult> {
    this._having = condition
    return this
  }

  distinct(): SelectQueryBuilder<T, TSelection, TResult> {
    this._distinct = true
    return this
  }

  toSQL(): { sql: string; params: unknown[] } {
    const params: unknown[] = []
    const parts: string[] = []

    // SELECT
    parts.push('SELECT')
    if (this._distinct) {
      parts.push('DISTINCT')
    }

    // Columns
    const columns = this.buildSelectColumns(params)
    parts.push(columns)

    // FROM
    parts.push(`FROM "${this.table.tableName}"`)

    // WHERE
    if (this._where.length > 0) {
      const whereExpr = this._where.length === 1 ? this._where[0]! : and(...this._where)
      const { sql, params: whereParams } = whereExpr.toSQL()
      // Adjust parameter indices
      const adjustedSql = sql.replace(
        /\$(\d+)/g,
        (_, n) => `$${parseInt(n) + params.length}`
      )
      parts.push(`WHERE ${adjustedSql}`)
      params.push(...whereParams)
    }

    // GROUP BY
    if (this._groupBy.length > 0) {
      parts.push(`GROUP BY ${this._groupBy.map(c => `"${c}"`).join(', ')}`)
    }

    // HAVING
    if (this._having) {
      const { sql, params: havingParams } = this._having.toSQL()
      const adjustedSql = sql.replace(
        /\$(\d+)/g,
        (_, n) => `$${parseInt(n) + params.length}`
      )
      parts.push(`HAVING ${adjustedSql}`)
      params.push(...havingParams)
    }

    // ORDER BY
    if (this._orderBy.length > 0) {
      const orderClauses = this._orderBy.map(o => {
        let clause = `"${o.column}" ${o.direction}`
        if (o.nulls) {
          clause += ` NULLS ${o.nulls}`
        }
        return clause
      })
      parts.push(`ORDER BY ${orderClauses.join(', ')}`)
    }

    // LIMIT
    if (this._limit !== undefined) {
      parts.push(`LIMIT ${this._limit}`)
    }

    // OFFSET
    if (this._offset !== undefined) {
      parts.push(`OFFSET ${this._offset}`)
    }

    return { sql: parts.join(' '), params }
  }

  async execute(): Promise<TResult> {
    const { sql, params } = this.toSQL()
    const result = await this.client.unsafe<Row>(sql, params)
    return result as TResult
  }

  async first(): Promise<TResult extends (infer U)[] ? U | null : TResult | null> {
    const limitedBuilder = this.limit(1)
    const result = await limitedBuilder.execute()
    if (Array.isArray(result)) {
      return (result[0] ?? null) as TResult extends (infer U)[] ? U | null : TResult | null
    }
    return result as TResult extends (infer U)[] ? U | null : TResult | null
  }

  async firstOrThrow(): Promise<TResult extends (infer U)[] ? U : TResult> {
    const result = await this.first()
    if (result === null) {
      throw new Error('No result found')
    }
    return result as TResult extends (infer U)[] ? U : TResult
  }

  private resolveWhereClause(condition: WhereClause<T>): SQLExpression<boolean> {
    if (typeof condition === 'function') {
      const refs = columnsOf(this.table)
      return condition(refs)
    }
    return condition
  }

  private buildSelectColumns(_params: unknown[]): string {
    if (this._selection === '*') {
      return '*'
    }

    if (Array.isArray(this._selection)) {
      return (this._selection as string[]).map(c => `"${c}"`).join(', ')
    }

    // Object selection with aliases
    const selectObj = this._selection as Record<string, SQLExpression | string>
    const columns: string[] = []

    for (const [alias, value] of Object.entries(selectObj)) {
      if (typeof value === 'string') {
        columns.push(`"${value}" AS "${alias}"`)
      } else {
        const { sql } = value.toSQL()
        columns.push(`${sql} AS "${alias}"`)
      }
    }

    return columns.join(', ')
  }
}

// ============================================================================
// JOIN Query Builder Implementation
// ============================================================================

class JoinQueryBuilderImpl<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
  TJoinType extends JoinType,
  TResult = JoinResult<TLeft, TRight, TJoinType>[],
> implements JoinQueryBuilder<TLeft, TRight, TJoinType, TResult> {
  private _where?: SQLExpression<boolean> | undefined
  private _orderBy: { column: string; direction: 'ASC' | 'DESC' }[] = []
  private _limit?: number | undefined
  private _selection?: Record<string, SQLExpression> | undefined

  constructor(
    private client: Sql,
    private leftTable: TLeft,
    private rightTable: TRight,
    private joinType: TJoinType,
    private joinCondition: SQLExpression<boolean>
  ) {}

  where(condition: SQLExpression<boolean>): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult> {
    this._where = condition
    return this
  }

  orderBy(column: string, direction: 'ASC' | 'DESC' = 'ASC'): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult> {
    this._orderBy.push({ column, direction })
    return this
  }

  limit(count: number): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult> {
    this._limit = count
    return this
  }

  select<TSelection extends Record<string, SQLExpression>>(
    selection: TSelection
  ): JoinQueryBuilder<TLeft, TRight, TJoinType, { [K in keyof TSelection]: TSelection[K] extends SQLExpression<infer U> ? U : never }[]> {
    const builder = new JoinQueryBuilderImpl<TLeft, TRight, TJoinType>(
      this.client,
      this.leftTable,
      this.rightTable,
      this.joinType,
      this.joinCondition
    )
    builder._where = this._where
    builder._orderBy = [...this._orderBy]
    builder._limit = this._limit
    builder._selection = selection
    return builder as unknown as JoinQueryBuilder<TLeft, TRight, TJoinType, { [K in keyof TSelection]: TSelection[K] extends SQLExpression<infer U> ? U : never }[]>
  }

  toSQL(): { sql: string; params: unknown[] } {
    const params: unknown[] = []
    const parts: string[] = []

    // SELECT
    parts.push('SELECT')
    if (this._selection) {
      const columns: string[] = []
      for (const [alias, expr] of Object.entries(this._selection)) {
        const { sql, params: exprParams } = expr.toSQL()
        const adjustedSql = sql.replace(
          /\$(\d+)/g,
          (_, n) => `$${parseInt(n) + params.length}`
        )
        columns.push(`${adjustedSql} AS "${alias}"`)
        params.push(...exprParams)
      }
      parts.push(columns.join(', '))
    } else {
      parts.push(`"${this.leftTable.tableName}".*, "${this.rightTable.tableName}".*`)
    }

    // FROM
    parts.push(`FROM "${this.leftTable.tableName}"`)

    // JOIN
    const joinKeyword = this.getJoinKeyword()
    parts.push(`${joinKeyword} "${this.rightTable.tableName}"`)

    // ON
    const { sql: onSql, params: onParams } = this.joinCondition.toSQL()
    const adjustedOnSql = onSql.replace(
      /\$(\d+)/g,
      (_, n) => `$${parseInt(n) + params.length}`
    )
    parts.push(`ON ${adjustedOnSql}`)
    params.push(...onParams)

    // WHERE
    if (this._where) {
      const { sql, params: whereParams } = this._where.toSQL()
      const adjustedSql = sql.replace(
        /\$(\d+)/g,
        (_, n) => `$${parseInt(n) + params.length}`
      )
      parts.push(`WHERE ${adjustedSql}`)
      params.push(...whereParams)
    }

    // ORDER BY
    if (this._orderBy.length > 0) {
      const orderClauses = this._orderBy.map(o => `${o.column} ${o.direction}`)
      parts.push(`ORDER BY ${orderClauses.join(', ')}`)
    }

    // LIMIT
    if (this._limit !== undefined) {
      parts.push(`LIMIT ${this._limit}`)
    }

    return { sql: parts.join(' '), params }
  }

  async execute(): Promise<TResult> {
    const { sql, params } = this.toSQL()
    const result = await this.client.unsafe<Row>(sql, params)
    return result as TResult
  }

  private getJoinKeyword(): string {
    switch (this.joinType) {
      case 'LEFT':
        return 'LEFT JOIN'
      case 'RIGHT':
        return 'RIGHT JOIN'
      case 'FULL':
        return 'FULL OUTER JOIN'
      default:
        return 'INNER JOIN'
    }
  }
}

// ============================================================================
// INSERT Query Builder Implementation
// ============================================================================

class InsertQueryBuilderImpl<
  T extends TableDefinition,
  TResult = { rowCount: number },
> implements InsertQueryBuilder<T, TResult> {
  private _returning?: string[] | true
  private _onConflict?: {
    target: ConflictTarget<T>
    action: OnConflictAction<T>
  }

  constructor(
    private client: Sql,
    private table: T,
    private values: InsertValues<T>
  ) {}

  returning(): InsertQueryBuilder<T, InferTableRow<T>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): InsertQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): InsertQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]> | InsertQueryBuilder<T, InferTableRow<T>[]> {
    if (columns.length === 0) {
      this._returning = true
    } else {
      this._returning = columns.map(c => String(c))
    }
    return this as unknown as InsertQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  }

  onConflict(
    target: ConflictTarget<T>,
    action: OnConflictAction<T>
  ): InsertQueryBuilder<T, TResult> {
    this._onConflict = { target, action }
    return this
  }

  toSQL(): { sql: string; params: unknown[] } {
    const params: unknown[] = []
    const valuesArray = Array.isArray(this.values) ? this.values : [this.values]

    if (valuesArray.length === 0) {
      throw new Error('INSERT requires at least one value')
    }

    // Get column names from first row
    const columns = Object.keys(valuesArray[0] as Record<string, unknown>)
    const columnList = columns.map(c => `"${c}"`).join(', ')

    // Build value placeholders
    const valuePlaceholders: string[] = []
    for (const row of valuesArray) {
      const rowRecord = row as Record<string, unknown>
      const rowParams = columns.map(col => rowRecord[col])
      const placeholders = rowParams.map((_, i) => `$${params.length + i + 1}`).join(', ')
      valuePlaceholders.push(`(${placeholders})`)
      params.push(...rowParams)
    }

    let sql = `INSERT INTO "${this.table.tableName}" (${columnList}) VALUES ${valuePlaceholders.join(', ')}`

    // ON CONFLICT
    if (this._onConflict) {
      const { target, action } = this._onConflict

      // Build target
      let targetSql: string
      if (Array.isArray(target)) {
        targetSql = target.map(c => `"${String(c)}"`).join(', ')
      } else {
        targetSql = `ON CONSTRAINT "${target.constraint}"`
      }

      // Build action
      if (action === 'DO NOTHING') {
        sql += ` ON CONFLICT (${targetSql}) DO NOTHING`
      } else if (typeof action === 'object' && 'doUpdate' in action) {
        const updateSet = action.doUpdate
        if (typeof updateSet === 'function') {
          throw new Error('Function-based conflict updates not yet implemented')
        }

        const setClauses: string[] = []
        for (const [col, val] of Object.entries(updateSet as Record<string, unknown>)) {
          setClauses.push(`"${col}" = $${params.length + 1}`)
          params.push(val)
        }

        sql += ` ON CONFLICT (${targetSql}) DO UPDATE SET ${setClauses.join(', ')}`
      }
    }

    // RETURNING
    if (this._returning) {
      if (this._returning === true) {
        sql += ' RETURNING *'
      } else {
        sql += ` RETURNING ${this._returning.map(c => `"${c}"`).join(', ')}`
      }
    }

    return { sql, params }
  }

  async execute(): Promise<TResult> {
    const { sql, params } = this.toSQL()
    const result = await this.client.unsafe<Row>(sql, params)

    if (this._returning) {
      return result as TResult
    }

    return { rowCount: result.length || 1 } as TResult
  }
}

// ============================================================================
// UPDATE Query Builder Implementation
// ============================================================================

class UpdateQueryBuilderImpl<
  T extends TableDefinition,
  TResult = { rowCount: number },
> implements UpdateQueryBuilder<T, TResult> {
  private _where?: SQLExpression<boolean>
  private _returning?: string[] | true

  constructor(
    private client: Sql,
    private table: T,
    private setValues: UpdateSet<T>
  ) {}

  where(condition: WhereClause<T>): UpdateQueryBuilder<T, TResult> {
    if (typeof condition === 'function') {
      const refs = columnsOf(this.table)
      this._where = condition(refs)
    } else {
      this._where = condition
    }
    return this
  }

  returning(): UpdateQueryBuilder<T, InferTableRow<T>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): UpdateQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): UpdateQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]> | UpdateQueryBuilder<T, InferTableRow<T>[]> {
    if (columns.length === 0) {
      this._returning = true
    } else {
      this._returning = columns.map(c => String(c))
    }
    return this as unknown as UpdateQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  }

  toSQL(): { sql: string; params: unknown[] } {
    const params: unknown[] = []
    const setClauses: string[] = []

    for (const [col, val] of Object.entries(this.setValues as Record<string, unknown>)) {
      if (val !== undefined) {
        if (typeof val === 'object' && val !== null && '_brand' in val) {
          // SQL expression
          const expr = val as SQLExpression
          const { sql, params: exprParams } = expr.toSQL()
          const adjustedSql = sql.replace(
            /\$(\d+)/g,
            (_, n) => `$${parseInt(n) + params.length}`
          )
          setClauses.push(`"${col}" = ${adjustedSql}`)
          params.push(...exprParams)
        } else {
          setClauses.push(`"${col}" = $${params.length + 1}`)
          params.push(val)
        }
      }
    }

    if (setClauses.length === 0) {
      throw new Error('UPDATE requires at least one column to set')
    }

    let sql = `UPDATE "${this.table.tableName}" SET ${setClauses.join(', ')}`

    // WHERE
    if (this._where) {
      const { sql: whereSql, params: whereParams } = this._where.toSQL()
      const adjustedSql = whereSql.replace(
        /\$(\d+)/g,
        (_, n) => `$${parseInt(n) + params.length}`
      )
      sql += ` WHERE ${adjustedSql}`
      params.push(...whereParams)
    }

    // RETURNING
    if (this._returning) {
      if (this._returning === true) {
        sql += ' RETURNING *'
      } else {
        sql += ` RETURNING ${this._returning.map(c => `"${c}"`).join(', ')}`
      }
    }

    return { sql, params }
  }

  async execute(): Promise<TResult> {
    const { sql, params } = this.toSQL()
    const result = await this.client.unsafe<Row>(sql, params)

    if (this._returning) {
      return result as TResult
    }

    return { rowCount: result.length || 0 } as TResult
  }
}

// ============================================================================
// DELETE Query Builder Implementation
// ============================================================================

class DeleteQueryBuilderImpl<
  T extends TableDefinition,
  TResult = { rowCount: number },
> implements DeleteQueryBuilder<T, TResult> {
  private _where?: SQLExpression<boolean>
  private _returning?: string[] | true

  constructor(
    private client: Sql,
    private table: T
  ) {}

  where(condition: WhereClause<T>): DeleteQueryBuilder<T, TResult> {
    if (typeof condition === 'function') {
      const refs = columnsOf(this.table)
      this._where = condition(refs)
    } else {
      this._where = condition
    }
    return this
  }

  returning(): DeleteQueryBuilder<T, InferTableRow<T>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): DeleteQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): DeleteQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]> | DeleteQueryBuilder<T, InferTableRow<T>[]> {
    if (columns.length === 0) {
      this._returning = true
    } else {
      this._returning = columns.map(c => String(c))
    }
    return this as unknown as DeleteQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
  }

  toSQL(): { sql: string; params: unknown[] } {
    const params: unknown[] = []
    let sql = `DELETE FROM "${this.table.tableName}"`

    // WHERE
    if (this._where) {
      const { sql: whereSql, params: whereParams } = this._where.toSQL()
      const adjustedSql = whereSql.replace(
        /\$(\d+)/g,
        (_, n) => `$${parseInt(n) + params.length}`
      )
      sql += ` WHERE ${adjustedSql}`
      params.push(...whereParams)
    }

    // RETURNING
    if (this._returning) {
      if (this._returning === true) {
        sql += ' RETURNING *'
      } else {
        sql += ` RETURNING ${this._returning.map(c => `"${c}"`).join(', ')}`
      }
    }

    return { sql, params }
  }

  async execute(): Promise<TResult> {
    const { sql, params } = this.toSQL()
    const result = await this.client.unsafe<Row>(sql, params)

    if (this._returning) {
      return result as TResult
    }

    return { rowCount: result.length || 0 } as TResult
  }
}

// ============================================================================
// Query Builder Factory
// ============================================================================

/**
 * Create a query builder factory from a postgres.do client
 */
export function createQueryBuilder(client: Sql) {
  return {
    /**
     * Start a SELECT query
     */
    select<T extends TableDefinition, TSelection extends SelectionSpec<T> = '*'>(
      selection?: TSelection
    ): { from(table: T): SelectQueryBuilder<T, TSelection extends undefined ? '*' : TSelection> } {
      return {
        from(table: T) {
          const builder = new SelectQueryBuilderImpl<T>(client, table)
          if (selection !== undefined) {
            return builder.select(selection) as unknown as SelectQueryBuilder<T, TSelection extends undefined ? '*' : TSelection>
          }
          return builder as unknown as SelectQueryBuilder<T, TSelection extends undefined ? '*' : TSelection>
        },
      }
    },

    /**
     * Start an INSERT query
     */
    insert<T extends TableDefinition>(table: T) {
      return {
        values(values: InsertValues<T>) {
          return new InsertQueryBuilderImpl(client, table, values)
        },
      }
    },

    /**
     * Start an UPDATE query
     */
    update<T extends TableDefinition>(table: T) {
      return {
        set(values: UpdateSet<T>) {
          return new UpdateQueryBuilderImpl(client, table, values)
        },
      }
    },

    /**
     * Start a DELETE query
     */
    delete<T extends TableDefinition>(table: T): DeleteQueryBuilder<T> {
      return new DeleteQueryBuilderImpl(client, table)
    },

    /**
     * Create an INNER JOIN
     */
    join<TLeft extends TableDefinition, TRight extends TableDefinition>(
      left: TLeft,
      right: TRight,
      condition: JoinCondition<TLeft, TRight>
    ): JoinQueryBuilder<TLeft, TRight, 'INNER'> {
      const expr = resolveJoinCondition(left, right, condition)
      return new JoinQueryBuilderImpl(client, left, right, 'INNER', expr)
    },

    /**
     * Create a LEFT JOIN
     */
    leftJoin<TLeft extends TableDefinition, TRight extends TableDefinition>(
      left: TLeft,
      right: TRight,
      condition: JoinCondition<TLeft, TRight>
    ): JoinQueryBuilder<TLeft, TRight, 'LEFT'> {
      const expr = resolveJoinCondition(left, right, condition)
      return new JoinQueryBuilderImpl(client, left, right, 'LEFT', expr)
    },

    /**
     * Create a RIGHT JOIN
     */
    rightJoin<TLeft extends TableDefinition, TRight extends TableDefinition>(
      left: TLeft,
      right: TRight,
      condition: JoinCondition<TLeft, TRight>
    ): JoinQueryBuilder<TLeft, TRight, 'RIGHT'> {
      const expr = resolveJoinCondition(left, right, condition)
      return new JoinQueryBuilderImpl(client, left, right, 'RIGHT', expr)
    },

    /**
     * Create a FULL OUTER JOIN
     */
    fullJoin<TLeft extends TableDefinition, TRight extends TableDefinition>(
      left: TLeft,
      right: TRight,
      condition: JoinCondition<TLeft, TRight>
    ): JoinQueryBuilder<TLeft, TRight, 'FULL'> {
      const expr = resolveJoinCondition(left, right, condition)
      return new JoinQueryBuilderImpl(client, left, right, 'FULL', expr)
    },
  }
}

// ============================================================================
// Helper Functions
// ============================================================================

function resolveJoinCondition<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
>(
  left: TLeft,
  right: TRight,
  condition: JoinCondition<TLeft, TRight>
): SQLExpression<boolean> {
  if ('_brand' in condition) {
    return condition as SQLExpression<boolean>
  }

  // Object format: { left: 'userId', right: 'id' }
  const leftCol = String(condition.left)
  const rightCol = String(condition.right)

  return {
    _type: true as boolean,
    _brand: 'sql_expression',
    toSQL: () => ({
      sql: `"${left.tableName}"."${leftCol}" = "${right.tableName}"."${rightCol}"`,
      params: [],
    }),
  }
}