/**
 * Type-safe Schema Definition
 *
 * Provides helpers for defining schemas that work with the query builder.
 * Also includes adapters for Drizzle ORM schemas.
 */

import type {
  TableDefinition,
  ColumnDefinition,
  InferTableRow,
  InferInsertRow,
} from './types.js'

// ============================================================================
// Column Type Definitions
// ============================================================================

/**
 * PostgreSQL column types mapped to TypeScript types
 */
export type PgTypeMap = {
  // Numeric types
  'int2': number
  'int4': number
  'int8': bigint
  'float4': number
  'float8': number
  'numeric': number
  'serial': number
  'bigserial': bigint

  // String types
  'text': string
  'varchar': string
  'char': string
  'uuid': string
  'citext': string

  // Boolean
  'boolean': boolean

  // Date/time
  'date': Date | string
  'time': string
  'timestamp': Date
  'timestamptz': Date
  'interval': string

  // JSON
  'json': unknown
  'jsonb': unknown

  // Binary
  'bytea': Uint8Array

  // Arrays (generalized)
  'array': unknown[]

  // Other
  'inet': string
  'cidr': string
  'macaddr': string
}

export type PgType = keyof PgTypeMap

// ============================================================================
// Column Builder
// ============================================================================

/**
 * Column builder for type-safe column definitions
 */
class ColumnBuilder<
  TName extends string,
  TType,
  TNotNull extends boolean = false,
  TDefault extends boolean = false,
> {
  private _name: TName
  private _notNull: TNotNull = false as TNotNull
  private _hasDefault: TDefault = false as TDefault

  constructor(name: TName, private _type: TType) {
    this._name = name
  }

  /**
   * Mark column as NOT NULL
   */
  notNull(): ColumnBuilder<TName, TType, true, TDefault> {
    const builder = new ColumnBuilder<TName, TType, true, TDefault>(this._name, this._type)
    builder._notNull = true as true
    builder._hasDefault = this._hasDefault
    return builder
  }

  /**
   * Mark column as having a default value
   */
  default<TValue extends TType>(_value: TValue): ColumnBuilder<TName, TType, TNotNull, true> {
    const builder = new ColumnBuilder<TName, TType, TNotNull, true>(this._name, this._type)
    builder._notNull = this._notNull
    builder._hasDefault = true as true
    return builder
  }

  /**
   * Mark as primary key (implies NOT NULL)
   */
  primaryKey(): ColumnBuilder<TName, TType, true, TDefault> {
    return this.notNull()
  }

  /**
   * Build the column definition
   */
  build(): ColumnDefinition<TName, TType, TNotNull, TDefault> {
    return {
      name: this._name,
      dataType: this._type as TType,
      notNull: this._notNull,
      hasDefault: this._hasDefault,
      _columnBrand: 'column',
    }
  }
}

// ============================================================================
// Column Type Helpers
// ============================================================================

/**
 * Create a serial (auto-incrementing integer) column
 */
export function serial<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, number, true, true>(name, 0 as number)
    .notNull()
    .default(0)
}

/**
 * Create a bigserial (auto-incrementing bigint) column
 */
export function bigserial<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, bigint, true, true>(name, BigInt(0))
    .notNull()
    .default(BigInt(0))
}

/**
 * Create an integer column
 */
export function integer<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, number>(name, 0 as number)
}

/**
 * Create a bigint column
 */
export function bigint<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, bigint>(name, BigInt(0))
}

/**
 * Create a text column
 */
export function text<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, string>(name, '' as string)
}

/**
 * Create a varchar column
 */
export function varchar<TName extends string>(name: TName, _length?: number) {
  return new ColumnBuilder<TName, string>(name, '' as string)
}

/**
 * Create a boolean column
 */
export function boolean<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, boolean>(name, false as boolean)
}

/**
 * Create a timestamp column
 */
export function timestamp<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, Date>(name, new Date() as Date)
}

/**
 * Create a timestamp with timezone column
 */
export function timestamptz<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, Date>(name, new Date() as Date)
}

/**
 * Create a date column
 */
export function date<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, Date | string>(name, '' as Date | string)
}

/**
 * Create a time column
 */
export function time<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, string>(name, '' as string)
}

/**
 * Create a UUID column
 */
export function uuid<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, string>(name, '' as string)
}

/**
 * Create a JSON column
 */
export function json<TName extends string, TData = unknown>(name: TName) {
  return new ColumnBuilder<TName, TData>(name, undefined as TData)
}

/**
 * Create a JSONB column
 */
export function jsonb<TName extends string, TData = unknown>(name: TName) {
  return new ColumnBuilder<TName, TData>(name, undefined as TData)
}

/**
 * Create a numeric/decimal column
 */
export function numeric<TName extends string>(name: TName, _precision?: number, _scale?: number) {
  return new ColumnBuilder<TName, number>(name, 0 as number)
}

/**
 * Create a real (float4) column
 */
export function real<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, number>(name, 0 as number)
}

/**
 * Create a double precision (float8) column
 */
export function doublePrecision<TName extends string>(name: TName) {
  return new ColumnBuilder<TName, number>(name, 0 as number)
}

// ============================================================================
// Table Definition
// ============================================================================

type ColumnBuilderMap = Record<string, ColumnBuilder<string, unknown, boolean, boolean>>

type ExtractColumnDefs<T extends ColumnBuilderMap> = {
  [K in keyof T]: T[K] extends ColumnBuilder<infer TName, infer TType, infer TNotNull, infer TDefault>
    ? ColumnDefinition<TName & string, TType, TNotNull, TDefault>
    : never
}

/**
 * Define a table with type-safe columns
 */
export function defineTable<
  TName extends string,
  TColumns extends ColumnBuilderMap,
>(
  name: TName,
  columns: TColumns
): TableDefinition<TName, ExtractColumnDefs<TColumns>> {
  const columnDefs = {} as ExtractColumnDefs<TColumns>

  for (const [key, builder] of Object.entries(columns)) {
    columnDefs[key as keyof TColumns] = builder.build() as ExtractColumnDefs<TColumns>[keyof TColumns]
  }

  return {
    tableName: name,
    columns: columnDefs,
    _tableBrand: 'table',
  }
}

// ============================================================================
// Drizzle ORM Adapter
// ============================================================================

/**
 * Options for converting Drizzle schema to query builder format
 */
export interface DrizzleAdapterOptions {
  /**
   * Transform column names (e.g., from snake_case to camelCase)
   */
  transformColumnNames?: (name: string) => string
}

/**
 * Convert a Drizzle table to a query builder TableDefinition
 *
 * This enables using Drizzle schemas with the standalone query builder.
 *
 * @example
 * ```typescript
 * import { users } from './drizzle-schema'
 * import { fromDrizzleTable, createQueryBuilder } from 'postgres.do/query-builder'
 *
 * const usersTable = fromDrizzleTable(users)
 * const qb = createQueryBuilder(sql)
 *
 * // Type-safe queries using Drizzle schema
 * const results = await qb.select().from(usersTable).execute()
 * ```
 */
export function fromDrizzleTable<TDrizzleTable extends object>(
  drizzleTable: TDrizzleTable,
  options?: DrizzleAdapterOptions
): TableDefinition {
  // Extract table name - Drizzle stores it in Symbol.for('drizzle:Name')
  // or in the table config
  let tableName = 'unknown'

  // Try different ways to get the table name
  if ('_' in drizzleTable) {
    const config = (drizzleTable as { _: { name?: string | undefined } })._
    if (config?.name !== undefined) {
      tableName = config.name
    }
  }

  // Also check for table symbol
  const tableSymbol = Symbol.for('drizzle:Name')
  if (tableSymbol in drizzleTable) {
    const symbolValue = (drizzleTable as Record<symbol, string | undefined>)[tableSymbol]
    if (symbolValue !== undefined) {
      tableName = symbolValue
    }
  }

  // Extract columns from Drizzle table
  const columns: Record<string, ColumnDefinition> = {}

  for (const [key, value] of Object.entries(drizzleTable)) {
    if (key.startsWith('_') || typeof value !== 'object' || value === null) {
      continue
    }

    // Check if this is a Drizzle column
    const columnObj = value as Record<string, unknown>
    if ('name' in columnObj && 'dataType' in columnObj) {
      const colName = options?.transformColumnNames
        ? options.transformColumnNames(String(columnObj.name))
        : String(columnObj.name)

      // Determine TypeScript type from Drizzle data type
      const dataType = columnObj.dataType as string
      const notNull = columnObj.notNull === true
      const hasDefault = columnObj.hasDefault === true || columnObj.default !== undefined

      columns[key] = {
        name: colName,
        dataType: mapDrizzleType(dataType),
        notNull,
        hasDefault,
        _columnBrand: 'column',
      }
    }
  }

  return {
    tableName,
    columns,
    _tableBrand: 'table',
  }
}

/**
 * Map Drizzle data type to JavaScript type
 */
function mapDrizzleType(drizzleType: string): unknown {
  switch (drizzleType) {
    case 'string':
    case 'text':
    case 'varchar':
    case 'char':
    case 'uuid':
      return '' as string

    case 'number':
    case 'integer':
    case 'serial':
    case 'smallint':
    case 'real':
    case 'double':
    case 'numeric':
      return 0 as number

    case 'bigint':
    case 'bigserial':
      return BigInt(0) as bigint

    case 'boolean':
      return false as boolean

    case 'date':
    case 'timestamp':
      return new Date() as Date

    case 'json':
    case 'jsonb':
      return {} as unknown

    case 'buffer':
    case 'bytea':
      return new Uint8Array() as Uint8Array

    default:
      return undefined as unknown
  }
}

// ============================================================================
// Type Inference Helpers
// ============================================================================

/**
 * Infer the select result type for a table
 */
export type SelectResult<T extends TableDefinition> = InferTableRow<T>

/**
 * Infer the insert type for a table
 */
export type InsertType<T extends TableDefinition> = InferInsertRow<T>

/**
 * Get column keys for a table
 */
export type ColumnKeys<T extends TableDefinition> = keyof T['columns']

// ============================================================================
// Re-export types
// ============================================================================

export type {
  TableDefinition,
  ColumnDefinition,
  InferTableRow,
  InferInsertRow,
}