/**
 * Type-safe Query Builder Types
 *
 * Core types for building type-safe SQL queries with full TypeScript inference.
 * These types ensure that queries are validated at compile time.
 */

import type { Row as _Row } from '../types.js'

// ============================================================================
// Column and Table Definition Types
// ============================================================================

/**
 * Base column definition with type information
 */
export interface ColumnDefinition<
  TName extends string = string,
  TType = unknown,
  TNotNull extends boolean = boolean,
  TDefault extends boolean = boolean,
> {
  readonly name: TName
  readonly dataType: TType
  readonly notNull: TNotNull
  readonly hasDefault: TDefault
  readonly _columnBrand: 'column'
}

/**
 * Create a column definition with inferred types
 */
export type Column<
  TName extends string,
  TType,
  TNotNull extends boolean = false,
  TDefault extends boolean = false,
> = ColumnDefinition<TName, TType, TNotNull, TDefault>

/**
 * Table definition with typed columns
 */
export interface TableDefinition<
  TName extends string = string,
  TColumns extends Record<string, ColumnDefinition> = Record<string, ColumnDefinition>,
> {
  readonly tableName: TName
  readonly columns: TColumns
  readonly _tableBrand: 'table'
}

/**
 * Infer the row type from a table definition
 */
export type InferTableRow<T extends TableDefinition> = {
  [K in keyof T['columns']]: InferColumnType<T['columns'][K]>
}

/**
 * Infer the TypeScript type from a column definition
 */
export type InferColumnType<T extends ColumnDefinition> =
  T['notNull'] extends true
    ? T['dataType']
    : T['dataType'] | null

/**
 * Infer insert type (required fields only)
 */
export type InferInsertRow<T extends TableDefinition> = {
  [K in keyof T['columns'] as RequiredInsertColumn<T['columns'][K]> extends true ? K : never]: T['columns'][K]['dataType']
} & {
  [K in keyof T['columns'] as RequiredInsertColumn<T['columns'][K]> extends true ? never : K]?: T['columns'][K]['dataType'] | null
}

type RequiredInsertColumn<T extends ColumnDefinition> =
  T['notNull'] extends true
    ? T['hasDefault'] extends true
      ? false
      : true
    : false

// ============================================================================
// Expression Types
// ============================================================================

/**
 * SQL expression with type information
 */
export interface SQLExpression<TType = unknown> {
  readonly _type: TType
  readonly _brand: 'sql_expression'
  toSQL(): { sql: string; params: unknown[] }
}

/**
 * Column reference expression
 */
export interface ColumnRef<
  TTable extends string = string,
  TColumn extends string = string,
  TType = unknown,
> extends SQLExpression<TType> {
  readonly table: TTable
  readonly column: TColumn
  readonly _brand: 'sql_expression'
}

/**
 * Literal value expression
 */
export interface LiteralExpr<TType = unknown> extends SQLExpression<TType> {
  readonly value: TType
}

/**
 * Binary comparison expression
 */
export interface ComparisonExpr<TType = boolean> extends SQLExpression<TType> {
  readonly left: SQLExpression
  readonly operator: ComparisonOperator
  readonly right: SQLExpression
}

export type ComparisonOperator =
  | '='
  | '!='
  | '<'
  | '<='
  | '>'
  | '>='
  | 'LIKE'
  | 'ILIKE'
  | 'IN'
  | 'NOT IN'
  | 'IS NULL'
  | 'IS NOT NULL'

/**
 * Logical expression (AND, OR, NOT)
 */
export interface LogicalExpr extends SQLExpression<boolean> {
  readonly operator: 'AND' | 'OR' | 'NOT'
  readonly expressions: SQLExpression<boolean>[]
}

// ============================================================================
// Aggregate Expression Types
// ============================================================================

/**
 * Aggregate function expression
 */
export interface AggregateExpr<TType = unknown> extends SQLExpression<TType> {
  readonly fn: 'COUNT' | 'SUM' | 'AVG' | 'MIN' | 'MAX'
  readonly column: SQLExpression | '*'
  readonly distinct?: boolean
}

// ============================================================================
// SELECT Query Types
// ============================================================================

/**
 * Selection specification - what columns to select
 */
export type SelectionSpec<T extends TableDefinition> =
  | '*'
  | (keyof T['columns'])[]
  | Record<string, SQLExpression | keyof T['columns']>

/**
 * Infer result type from selection spec
 */
export type InferSelectResult<
  T extends TableDefinition,
  TSelection extends SelectionSpec<T>,
> = TSelection extends '*'
  ? InferTableRow<T>
  : TSelection extends (keyof T['columns'])[]
    ? Pick<InferTableRow<T>, TSelection[number]>
    : TSelection extends Record<string, SQLExpression | keyof T['columns']>
      ? {
          [K in keyof TSelection]: TSelection[K] extends SQLExpression<infer U>
            ? U
            : TSelection[K] extends keyof T['columns']
              ? InferColumnType<T['columns'][TSelection[K]]>
              : never
        }
      : never

/**
 * WHERE clause type - accepts boolean expressions
 */
export type WhereClause<T extends TableDefinition> =
  | SQLExpression<boolean>
  | ((columns: ColumnRefs<T>) => SQLExpression<boolean>)

/**
 * Column references for a table
 */
export type ColumnRefs<T extends TableDefinition> = {
  [K in keyof T['columns']]: ColumnRef<T['tableName'], K & string, T['columns'][K]['dataType']>
}

/**
 * ORDER BY specification
 */
export type OrderBySpec<T extends TableDefinition> =
  | keyof T['columns']
  | { column: keyof T['columns']; direction: 'ASC' | 'DESC' }
  | { column: keyof T['columns']; direction: 'ASC' | 'DESC'; nulls?: 'FIRST' | 'LAST' }

// ============================================================================
// JOIN Types
// ============================================================================

/**
 * Join types
 */
export type JoinType = 'INNER' | 'LEFT' | 'RIGHT' | 'FULL'

/**
 * Join condition specification
 */
export type JoinCondition<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
> = SQLExpression<boolean> | {
  left: keyof TLeft['columns']
  right: keyof TRight['columns']
}

/**
 * Result type for joined tables
 */
export type JoinResult<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
  TJoinType extends JoinType,
> = TJoinType extends 'LEFT'
  ? InferTableRow<TLeft> & { [K in TRight['tableName']]: InferTableRow<TRight> | null }
  : TJoinType extends 'RIGHT'
    ? { [K in TLeft['tableName']]: InferTableRow<TLeft> | null } & InferTableRow<TRight>
    : TJoinType extends 'FULL'
      ? { [K in TLeft['tableName']]: InferTableRow<TLeft> | null } & { [K in TRight['tableName']]: InferTableRow<TRight> | null }
      : InferTableRow<TLeft> & InferTableRow<TRight>

/**
 * Combined table reference after join
 */
export interface JoinedTables<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
  TJoinType extends JoinType = 'INNER',
> {
  left: TLeft
  right: TRight
  joinType: TJoinType
  condition: SQLExpression<boolean>
}

// ============================================================================
// INSERT Types
// ============================================================================

/**
 * Insert values - single or array of rows
 */
export type InsertValues<T extends TableDefinition> =
  | InferInsertRow<T>
  | InferInsertRow<T>[]

/**
 * On conflict action
 */
export type OnConflictAction<T extends TableDefinition> =
  | 'DO NOTHING'
  | { doUpdate: Partial<InferInsertRow<T>> }
  | { doUpdate: (row: InferTableRow<T>) => Partial<InferInsertRow<T>> }

/**
 * Conflict target columns
 */
export type ConflictTarget<T extends TableDefinition> =
  | (keyof T['columns'])[]
  | { constraint: string }

// ============================================================================
// UPDATE Types
// ============================================================================

/**
 * Update set values
 */
export type UpdateSet<T extends TableDefinition> = Partial<{
  [K in keyof T['columns']]: T['columns'][K]['dataType'] | SQLExpression<T['columns'][K]['dataType']>
}>

// ============================================================================
// Query Builder Interfaces
// ============================================================================

/**
 * Base query builder interface
 */
export interface QueryBuilder<TResult = unknown> {
  /**
   * Get the generated SQL and parameters
   */
  toSQL(): { sql: string; params: unknown[] }

  /**
   * Execute the query and return results
   */
  execute(): Promise<TResult>
}

/**
 * SELECT query builder interface
 */
export interface SelectQueryBuilder<
  T extends TableDefinition,
  TSelection extends SelectionSpec<T> = '*',
  TResult = InferSelectResult<T, TSelection>[],
> extends QueryBuilder<TResult> {
  /**
   * Select specific columns or expressions
   */
  select<TNewSelection extends SelectionSpec<T>>(
    selection: TNewSelection
  ): SelectQueryBuilder<T, TNewSelection, InferSelectResult<T, TNewSelection>[]>

  /**
   * Add WHERE clause
   */
  where(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add AND condition to WHERE clause
   */
  andWhere(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add OR condition to WHERE clause
   */
  orWhere(condition: WhereClause<T>): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add ORDER BY clause
   */
  orderBy(...specs: OrderBySpec<T>[]): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add LIMIT clause
   */
  limit(count: number): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add OFFSET clause
   */
  offset(count: number): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add GROUP BY clause
   */
  groupBy(...columns: (keyof T['columns'])[]): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add HAVING clause
   */
  having(condition: SQLExpression<boolean>): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Add DISTINCT
   */
  distinct(): SelectQueryBuilder<T, TSelection, TResult>

  /**
   * Execute and return first result or null
   */
  first(): Promise<TResult extends (infer U)[] ? U | null : TResult | null>

  /**
   * Execute and return first result or throw
   */
  firstOrThrow(): Promise<TResult extends (infer U)[] ? U : TResult>
}

/**
 * JOIN query builder interface
 */
export interface JoinQueryBuilder<
  TLeft extends TableDefinition,
  TRight extends TableDefinition,
  TJoinType extends JoinType,
  TResult = JoinResult<TLeft, TRight, TJoinType>[],
> extends QueryBuilder<TResult> {
  /**
   * Add WHERE clause
   */
  where(condition: SQLExpression<boolean>): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult>

  /**
   * Add ORDER BY clause
   */
  orderBy(column: string, direction?: 'ASC' | 'DESC'): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult>

  /**
   * Add LIMIT clause
   */
  limit(count: number): JoinQueryBuilder<TLeft, TRight, TJoinType, TResult>

  /**
   * Select specific columns from joined tables
   */
  select<TSelection extends Record<string, SQLExpression>>(
    selection: TSelection
  ): JoinQueryBuilder<TLeft, TRight, TJoinType, { [K in keyof TSelection]: TSelection[K] extends SQLExpression<infer U> ? U : never }[]>
}

/**
 * INSERT query builder interface
 */
export interface InsertQueryBuilder<
  T extends TableDefinition,
  TResult = { rowCount: number },
> extends QueryBuilder<TResult> {
  /**
   * Add RETURNING clause
   */
  returning(): InsertQueryBuilder<T, InferTableRow<T>[]>

  /**
   * Add RETURNING clause with specific columns
   */
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): InsertQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>

  /**
   * Add ON CONFLICT clause
   */
  onConflict(
    target: ConflictTarget<T>,
    action: OnConflictAction<T>
  ): InsertQueryBuilder<T, TResult>
}

/**
 * UPDATE query builder interface
 */
export interface UpdateQueryBuilder<
  T extends TableDefinition,
  TResult = { rowCount: number },
> extends QueryBuilder<TResult> {
  /**
   * Add WHERE clause
   */
  where(condition: WhereClause<T>): UpdateQueryBuilder<T, TResult>

  /**
   * Add RETURNING clause
   */
  returning(): UpdateQueryBuilder<T, InferTableRow<T>[]>

  /**
   * Add RETURNING clause with specific columns
   */
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): UpdateQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
}

/**
 * DELETE query builder interface
 */
export interface DeleteQueryBuilder<
  T extends TableDefinition,
  TResult = { rowCount: number },
> extends QueryBuilder<TResult> {
  /**
   * Add WHERE clause
   */
  where(condition: WhereClause<T>): DeleteQueryBuilder<T, TResult>

  /**
   * Add RETURNING clause
   */
  returning(): DeleteQueryBuilder<T, InferTableRow<T>[]>

  /**
   * Add RETURNING clause with specific columns
   */
  returning<TColumns extends (keyof T['columns'])[]>(
    ...columns: TColumns
  ): DeleteQueryBuilder<T, Pick<InferTableRow<T>, TColumns[number]>[]>
}

// ============================================================================
// Query Factory Interface
// ============================================================================

/**
 * Main query builder factory interface
 */
export interface QueryBuilderFactory {
  /**
   * Start a SELECT query
   */
  select<T extends TableDefinition>(): {
    from(table: T): SelectQueryBuilder<T, '*'>
  }

  /**
   * Start a SELECT query with specific columns
   */
  select<T extends TableDefinition, TSelection extends SelectionSpec<T>>(
    selection: TSelection
  ): {
    from(table: T): SelectQueryBuilder<T, TSelection>
  }

  /**
   * Start an INSERT query
   */
  insert<T extends TableDefinition>(table: T): {
    values(values: InsertValues<T>): InsertQueryBuilder<T>
  }

  /**
   * Start an UPDATE query
   */
  update<T extends TableDefinition>(table: T): {
    set(values: UpdateSet<T>): UpdateQueryBuilder<T>
  }

  /**
   * Start a DELETE query
   */
  delete<T extends TableDefinition>(table: T): DeleteQueryBuilder<T>

  /**
   * Create a JOIN
   */
  join<
    TLeft extends TableDefinition,
    TRight extends TableDefinition,
  >(
    left: TLeft,
    right: TRight,
    condition: JoinCondition<TLeft, TRight>
  ): JoinQueryBuilder<TLeft, TRight, 'INNER'>

  /**
   * Create a LEFT JOIN
   */
  leftJoin<
    TLeft extends TableDefinition,
    TRight extends TableDefinition,
  >(
    left: TLeft,
    right: TRight,
    condition: JoinCondition<TLeft, TRight>
  ): JoinQueryBuilder<TLeft, TRight, 'LEFT'>

  /**
   * Create a RIGHT JOIN
   */
  rightJoin<
    TLeft extends TableDefinition,
    TRight extends TableDefinition,
  >(
    left: TLeft,
    right: TRight,
    condition: JoinCondition<TLeft, TRight>
  ): JoinQueryBuilder<TLeft, TRight, 'RIGHT'>

  /**
   * Create a FULL OUTER JOIN
   */
  fullJoin<
    TLeft extends TableDefinition,
    TRight extends TableDefinition,
  >(
    left: TLeft,
    right: TRight,
    condition: JoinCondition<TLeft, TRight>
  ): JoinQueryBuilder<TLeft, TRight, 'FULL'>
}