/**
 * Type-safe SQL Expression Builders
 *
 * Provides builder functions for creating type-safe SQL expressions.
 * All comparisons are validated at compile time.
 */

import type {
  SQLExpression,
  ColumnRef,
  ComparisonOperator,
  AggregateExpr,
  TableDefinition,
  ColumnRefs,
} from './types.js'

// ============================================================================
// Expression Implementation
// ============================================================================

/**
 * Internal implementation of SQL expression
 */
class SQLExpressionImpl<TType = unknown> implements SQLExpression<TType> {
  readonly _type!: TType
  readonly _brand = 'sql_expression' as const

  constructor(
    private sqlGen: () => { sql: string; params: unknown[] }
  ) {}

  toSQL(): { sql: string; params: unknown[] } {
    return this.sqlGen()
  }
}

/**
 * Column reference implementation
 */
class ColumnRefImpl<
  TTable extends string,
  TColumn extends string,
  TType,
> extends SQLExpressionImpl<TType> implements ColumnRef<TTable, TColumn, TType> {
  readonly table: TTable
  readonly column: TColumn

  constructor(table: TTable, column: TColumn) {
    super(() => ({
      sql: `"${this.table}"."${this.column}"`,
      params: [],
    }))
    this.table = table
    this.column = column
  }
}

/**
 * Literal value implementation
 */
class LiteralImpl<TType> extends SQLExpressionImpl<TType> {
  readonly value: TType

  constructor(value: TType, private paramIndex: () => number) {
    super(() => ({
      sql: `$${this.paramIndex()}`,
      params: [this.value],
    }))
    this.value = value
  }
}

// ============================================================================
// Comparison Operators
// ============================================================================

/**
 * Create an equality comparison (=)
 */
export function eq<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '=', right)
}

/**
 * Create an inequality comparison (!=)
 */
export function ne<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '!=', right)
}

/**
 * Create a less than comparison (<)
 */
export function lt<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '<', right)
}

/**
 * Create a less than or equal comparison (<=)
 */
export function lte<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '<=', right)
}

/**
 * Create a greater than comparison (>)
 */
export function gt<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '>', right)
}

/**
 * Create a greater than or equal comparison (>=)
 */
export function gte<TType>(
  left: SQLExpression<TType>,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return createComparison(left, '>=', right)
}

/**
 * Create a LIKE comparison
 */
export function like(
  column: SQLExpression<string>,
  pattern: string
): SQLExpression<boolean> {
  return createComparison(column, 'LIKE', pattern)
}

/**
 * Create a case-insensitive LIKE comparison (ILIKE)
 */
export function ilike(
  column: SQLExpression<string>,
  pattern: string
): SQLExpression<boolean> {
  return createComparison(column, 'ILIKE', pattern)
}

/**
 * Create an IN comparison
 */
export function inArray<TType>(
  column: SQLExpression<TType>,
  values: TType[]
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    const placeholders = values.map((_, i) => `$${colSql.params.length + i + 1}`).join(', ')
    return {
      sql: `${colSql.sql} IN (${placeholders})`,
      params: [...colSql.params, ...values],
    }
  })
}

/**
 * Create a NOT IN comparison
 */
export function notInArray<TType>(
  column: SQLExpression<TType>,
  values: TType[]
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    const placeholders = values.map((_, i) => `$${colSql.params.length + i + 1}`).join(', ')
    return {
      sql: `${colSql.sql} NOT IN (${placeholders})`,
      params: [...colSql.params, ...values],
    }
  })
}

/**
 * Create an IS NULL comparison
 */
export function isNull<TType>(
  column: SQLExpression<TType | null>
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    return {
      sql: `${colSql.sql} IS NULL`,
      params: colSql.params,
    }
  })
}

/**
 * Create an IS NOT NULL comparison
 */
export function isNotNull<TType>(
  column: SQLExpression<TType | null>
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    return {
      sql: `${colSql.sql} IS NOT NULL`,
      params: colSql.params,
    }
  })
}

/**
 * Create a BETWEEN comparison
 */
export function between<TType>(
  column: SQLExpression<TType>,
  min: TType,
  max: TType
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    return {
      sql: `${colSql.sql} BETWEEN $${colSql.params.length + 1} AND $${colSql.params.length + 2}`,
      params: [...colSql.params, min, max],
    }
  })
}

/**
 * Create a NOT BETWEEN comparison
 */
export function notBetween<TType>(
  column: SQLExpression<TType>,
  min: TType,
  max: TType
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const colSql = column.toSQL()
    return {
      sql: `${colSql.sql} NOT BETWEEN $${colSql.params.length + 1} AND $${colSql.params.length + 2}`,
      params: [...colSql.params, min, max],
    }
  })
}

// ============================================================================
// Logical Operators
// ============================================================================

/**
 * Create an AND expression
 */
export function and(
  ...expressions: SQLExpression<boolean>[]
): SQLExpression<boolean> {
  if (expressions.length === 0) {
    return new SQLExpressionImpl<boolean>(() => ({ sql: 'TRUE', params: [] }))
  }
  if (expressions.length === 1) {
    return expressions[0]!
  }

  return new SQLExpressionImpl<boolean>(() => {
    const parts: string[] = []
    const params: unknown[] = []

    for (const expr of expressions) {
      const { sql, params: exprParams } = expr.toSQL()
      // Adjust parameter placeholders
      const adjustedSql = sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + params.length}`)
      parts.push(`(${adjustedSql})`)
      params.push(...exprParams)
    }

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

/**
 * Create an OR expression
 */
export function or(
  ...expressions: SQLExpression<boolean>[]
): SQLExpression<boolean> {
  if (expressions.length === 0) {
    return new SQLExpressionImpl<boolean>(() => ({ sql: 'FALSE', params: [] }))
  }
  if (expressions.length === 1) {
    return expressions[0]!
  }

  return new SQLExpressionImpl<boolean>(() => {
    const parts: string[] = []
    const params: unknown[] = []

    for (const expr of expressions) {
      const { sql, params: exprParams } = expr.toSQL()
      // Adjust parameter placeholders
      const adjustedSql = sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + params.length}`)
      parts.push(`(${adjustedSql})`)
      params.push(...exprParams)
    }

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

/**
 * Create a NOT expression
 */
export function not(
  expression: SQLExpression<boolean>
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const { sql, params } = expression.toSQL()
    return {
      sql: `NOT (${sql})`,
      params,
    }
  })
}

// ============================================================================
// Aggregate Functions
// ============================================================================

/**
 * Create a COUNT aggregate
 */
export function count(): AggregateExpr<number>
export function count<TType>(column: SQLExpression<TType>): AggregateExpr<number>
export function count<TType>(column?: SQLExpression<TType>): AggregateExpr<number> {
  return createAggregate('COUNT', column ?? '*')
}

/**
 * Create a COUNT DISTINCT aggregate
 */
export function countDistinct<TType>(column: SQLExpression<TType>): AggregateExpr<number> {
  return createAggregate('COUNT', column, true)
}

/**
 * Create a SUM aggregate
 */
export function sum(column: SQLExpression<number>): AggregateExpr<number> {
  return createAggregate('SUM', column)
}

/**
 * Create an AVG aggregate
 */
export function avg(column: SQLExpression<number>): AggregateExpr<number> {
  return createAggregate('AVG', column)
}

/**
 * Create a MIN aggregate
 */
export function min<TType>(column: SQLExpression<TType>): AggregateExpr<TType> {
  return createAggregate('MIN', column)
}

/**
 * Create a MAX aggregate
 */
export function max<TType>(column: SQLExpression<TType>): AggregateExpr<TType> {
  return createAggregate('MAX', column)
}

// ============================================================================
// String Functions
// ============================================================================

/**
 * Create a CONCAT expression
 */
export function concat(
  ...values: (SQLExpression<string> | string)[]
): SQLExpression<string> {
  return new SQLExpressionImpl<string>(() => {
    const parts: string[] = []
    const params: unknown[] = []

    for (const value of values) {
      if (typeof value === 'string') {
        parts.push(`$${params.length + 1}`)
        params.push(value)
      } else {
        const { sql, params: exprParams } = value.toSQL()
        const adjustedSql = sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + params.length}`)
        parts.push(adjustedSql)
        params.push(...exprParams)
      }
    }

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

/**
 * Create a LOWER expression
 */
export function lower(value: SQLExpression<string>): SQLExpression<string> {
  return new SQLExpressionImpl<string>(() => {
    const { sql, params } = value.toSQL()
    return {
      sql: `LOWER(${sql})`,
      params,
    }
  })
}

/**
 * Create an UPPER expression
 */
export function upper(value: SQLExpression<string>): SQLExpression<string> {
  return new SQLExpressionImpl<string>(() => {
    const { sql, params } = value.toSQL()
    return {
      sql: `UPPER(${sql})`,
      params,
    }
  })
}

/**
 * Create a TRIM expression
 */
export function trim(value: SQLExpression<string>): SQLExpression<string> {
  return new SQLExpressionImpl<string>(() => {
    const { sql, params } = value.toSQL()
    return {
      sql: `TRIM(${sql})`,
      params,
    }
  })
}

/**
 * Create a COALESCE expression
 */
export function coalesce<TType>(
  ...values: (SQLExpression<TType | null> | TType)[]
): SQLExpression<TType> {
  return new SQLExpressionImpl<TType>(() => {
    const parts: string[] = []
    const params: unknown[] = []

    for (const value of values) {
      if (typeof value === 'object' && value !== null && '_brand' in value) {
        const expr = value as SQLExpression<TType | null>
        const { sql, params: exprParams } = expr.toSQL()
        const adjustedSql = sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + params.length}`)
        parts.push(adjustedSql)
        params.push(...exprParams)
      } else {
        parts.push(`$${params.length + 1}`)
        params.push(value)
      }
    }

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

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

/**
 * Create a column reference
 */
export function col<
  TTable extends string,
  TColumn extends string,
  TType = unknown,
>(table: TTable, column: TColumn): ColumnRef<TTable, TColumn, TType> {
  return new ColumnRefImpl<TTable, TColumn, TType>(table, column)
}

/**
 * Create column references for a table definition
 */
export function columnsOf<T extends TableDefinition>(table: T): ColumnRefs<T> {
  const refs = {} as ColumnRefs<T>

  for (const key of Object.keys(table.columns)) {
    const columnDef = table.columns[key]!
    refs[key as keyof typeof refs] = new ColumnRefImpl(
      table.tableName,
      columnDef.name
    ) as ColumnRefs<T>[keyof typeof refs]
  }

  return refs
}

/**
 * Create a raw SQL expression (use with caution!)
 */
export function raw<TType = unknown>(
  sql: string,
  params: unknown[] = []
): SQLExpression<TType> {
  return new SQLExpressionImpl<TType>(() => ({ sql, params }))
}

/**
 * Create a literal value expression
 */
export function literal<TType>(value: TType): SQLExpression<TType> {
  let paramIdx = 1
  return new LiteralImpl(value, () => paramIdx++)
}

/**
 * Create a SQL expression from a template literal
 */
export function sql<TType = unknown>(
  strings: TemplateStringsArray,
  ...values: unknown[]
): SQLExpression<TType> {
  return new SQLExpressionImpl<TType>(() => {
    const parts: string[] = []
    const params: unknown[] = []

    for (let i = 0; i < strings.length; i++) {
      parts.push(strings[i]!)
      if (i < values.length) {
        const value = values[i]
        if (typeof value === 'object' && value !== null && '_brand' in value) {
          const expr = value as SQLExpression
          const { sql, params: exprParams } = expr.toSQL()
          const adjustedSql = sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + params.length}`)
          parts.push(adjustedSql)
          params.push(...exprParams)
        } else {
          parts.push(`$${params.length + 1}`)
          params.push(value)
        }
      }
    }

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

// ============================================================================
// Internal Helpers
// ============================================================================

function createComparison<TType>(
  left: SQLExpression<TType>,
  operator: ComparisonOperator,
  right: TType | SQLExpression<TType>
): SQLExpression<boolean> {
  return new SQLExpressionImpl<boolean>(() => {
    const leftSql = left.toSQL()
    let rightSql: { sql: string; params: unknown[] }

    if (typeof right === 'object' && right !== null && '_brand' in right) {
      const rightExpr = right as SQLExpression<TType>
      const { sql, params } = rightExpr.toSQL()
      // Adjust parameter indices
      rightSql = {
        sql: sql.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n) + leftSql.params.length}`),
        params,
      }
    } else {
      rightSql = {
        sql: `$${leftSql.params.length + 1}`,
        params: [right],
      }
    }

    return {
      sql: `${leftSql.sql} ${operator} ${rightSql.sql}`,
      params: [...leftSql.params, ...rightSql.params],
    }
  })
}

function createAggregate<TType>(
  fn: 'COUNT' | 'SUM' | 'AVG' | 'MIN' | 'MAX',
  column: SQLExpression | '*',
  distinct = false
): AggregateExpr<TType> {
  const baseExpr = new SQLExpressionImpl<TType>(() => {
    if (column === '*') {
      return {
        sql: `${fn}(*)`,
        params: [],
      }
    }

    const { sql, params } = column.toSQL()
    const distinctClause = distinct ? 'DISTINCT ' : ''
    return {
      sql: `${fn}(${distinctClause}${sql})`,
      params,
    }
  })

  // Create aggregate expression with required properties
  const aggregateExpr = baseExpr as unknown as AggregateExpr<TType>

  // Add aggregate properties
  Object.defineProperties(aggregateExpr, {
    fn: { value: fn, writable: false, enumerable: true },
    column: { value: column, writable: false, enumerable: true },
    distinct: { value: distinct, writable: false, enumerable: true },
  })

  return aggregateExpr
}