/**
 * PGLite Transport for postgres.do
 *
 * Provides a local PostgreSQL implementation using PGLite WASM
 * that can be used as a transport layer for the postgres.do client.
 *
 * @example
 * ```typescript
 * import { postgres } from 'postgres.do'
 * import { createPGLiteTransport, PGLite } from 'postgres.do/pglite'
 *
 * // Create a PGLite instance (in-memory)
 * const pglite = new PGLite()
 * await pglite.waitReady
 *
 * // Create a postgres.do client with PGLite transport
 * const sql = postgres({ transport: createPGLiteTransport(pglite) })
 *
 * // Use like normal postgres.do client
 * const users = await sql`SELECT * FROM users`
 * ```
 */

import type { PGlite } from '@dotdo/pglite'
import type { Transport, Row, QueryResult, TransactionOptions } from '../types.js'

// Re-export PGLite for convenience
export { PGlite } from '@dotdo/pglite'

/**
 * PGLite transport configuration
 */
export interface PGLiteTransportConfig {
  /**
   * The PGLite instance to use for queries
   */
  pglite: PGlite

  /**
   * Whether to automatically initialize the PGLite instance
   * Default: true
   */
  autoInit?: boolean
}

/**
 * PGLite Transport implementation
 *
 * Provides a Transport interface compatible with the postgres.do client
 * that uses PGLite WASM for local PostgreSQL execution.
 */
export class PGLiteTransport implements Transport {
  private pglite: PGlite
  private autoInit: boolean
  private initialized = false

  constructor(config: PGLiteTransportConfig) {
    this.pglite = config.pglite
    this.autoInit = config.autoInit ?? true
  }

  /**
   * Ensure PGLite is ready before executing queries
   */
  private async ensureReady(): Promise<void> {
    if (this.initialized) return

    if (this.autoInit && this.pglite.waitReady) {
      await this.pglite.waitReady
    }

    this.initialized = true
  }

  /**
   * Execute a SQL query with optional parameters
   */
  async query<T extends Row = Row>(
    sql: string,
    params?: unknown[]
  ): Promise<QueryResult<T>> {
    await this.ensureReady()

    const result = await this.pglite.query<T>(sql, params)

    return {
      rows: result.rows,
      rowCount: result.affectedRows ?? result.rows.length,
      fields: result.fields.map((field: { name: string; dataTypeID: number }) => ({
        name: field.name,
        dataTypeID: field.dataTypeID,
        tableID: 0,
        columnID: 0,
        dataTypeSize: -1,
        dataTypeModifier: -1,
        format: 0,
      })),
      command: this.extractCommand(sql),
    }
  }

  /**
   * Execute multiple queries in a transaction
   */
  async transaction<T>(
    queries: Array<{ sql: string; params?: unknown[] }>,
    options?: TransactionOptions
  ): Promise<T> {
    await this.ensureReady()

    // Build BEGIN statement with options
    const beginParts = ['BEGIN']

    if (options?.isolationLevel) {
      // Validate isolation level to prevent SQL injection
      const validLevels = ['read uncommitted', 'read committed', 'repeatable read', 'serializable']
      const normalized = options.isolationLevel.toLowerCase()
      if (!validLevels.includes(normalized)) {
        throw new Error(`Invalid isolation level: "${options.isolationLevel}"`)
      }
      beginParts.push(`ISOLATION LEVEL ${normalized.toUpperCase()}`)
    }
    if (options?.readOnly) {
      beginParts.push('READ ONLY')
    }
    if (options?.deferrable) {
      beginParts.push('DEFERRABLE')
    }

    // Start transaction
    await this.pglite.query(beginParts.join(' '))

    try {
      const results: unknown[] = []

      for (const q of queries) {
        const result = await this.pglite.query(q.sql, q.params)
        results.push(result.rows)
      }

      await this.pglite.query('COMMIT')
      return results as T
    } catch (error) {
      await this.pglite.query('ROLLBACK')
      throw error
    }
  }

  /**
   * Close the PGLite connection
   */
  async close(): Promise<void> {
    if (this.pglite.close) {
      await this.pglite.close()
    }
    this.initialized = false
  }

  /**
   * Check if the transport is connected (PGLite is always connected when initialized)
   */
  isConnected(): boolean {
    // PGLite's ready property indicates if it's ready for queries
    // The 'ready' property is true when PGLite has finished initialization
    return this.initialized || this.pglite.ready === true
  }

  /**
   * Get the underlying PGLite instance
   */
  getPGLite(): PGlite {
    return this.pglite
  }

  /**
   * Extract the SQL command type from a query string
   */
  private extractCommand(sql: string): string {
    const trimmed = sql.trim().toUpperCase()
    const firstWord = trimmed.split(/\s+/)[0]
    return firstWord || 'UNKNOWN'
  }
}

/**
 * Create a PGLite transport from a PGLite instance
 *
 * @param pglite - The PGLite instance to use
 * @param options - Optional configuration
 * @returns A Transport implementation using PGLite
 *
 * @example
 * ```typescript
 * import { PGlite } from '@dotdo/pglite'
 * import { createPGLiteTransport } from 'postgres.do/pglite'
 *
 * const pglite = new PGlite()
 * const transport = createPGLiteTransport(pglite)
 * ```
 */
export function createPGLiteTransport(
  pglite: PGlite,
  options?: Omit<PGLiteTransportConfig, 'pglite'>
): PGLiteTransport {
  return new PGLiteTransport({
    pglite,
    ...options,
  })
}

/**
 * Create a postgres.do-compatible SQL function backed by PGLite
 *
 * This provides a convenient way to use PGLite with the postgres.do API
 *
 * @param pgliteOrOptions - PGLite instance or PGLite options
 * @returns A postgres.do SQL tagged template function
 *
 * @example
 * ```typescript
 * import { createPGLiteSql } from 'postgres.do/pglite'
 *
 * // With existing PGLite instance
 * const pglite = new PGlite()
 * const sql = await createPGLiteSql(pglite)
 *
 * // Execute queries using postgres.do API
 * const users = await sql`SELECT * FROM users WHERE id = ${userId}`
 * ```
 */
export async function createPGLiteSql(pglite: PGlite): Promise<PGLiteSql> {
  await pglite.waitReady

  const transport = new PGLiteTransport({ pglite })

  // Create the SQL tagged template function
  const sql = function <T extends Row = Row>(
    strings: TemplateStringsArray,
    ...values: unknown[]
  ): Promise<T[]> {
    // Build parameterized query
    const parts: string[] = []
    for (let i = 0; i < strings.length; i++) {
      const str = strings[i]
      if (str !== undefined) {
        parts.push(str)
      }
      if (i < values.length) {
        parts.push(`$${i + 1}`)
      }
    }
    const query = parts.join('')

    return transport.query<T>(query, values).then((result) => result.rows)
  } as PGLiteSql

  // Add unsafe method for raw queries
  sql.unsafe = async <T extends Row = Row>(
    query: string,
    params?: unknown[]
  ): Promise<T[]> => {
    const result = await transport.query<T>(query, params)
    return result.rows
  }

  // Add transaction support
  sql.begin = async <T>(
    fn: (sql: PGLiteTransactionSql) => Promise<T>,
    options?: TransactionOptions
  ): Promise<T> => {
    await pglite.waitReady

    // Build BEGIN statement with options
    const beginParts = ['BEGIN']
    if (options?.isolationLevel) {
      // Validate isolation level to prevent SQL injection
      const validLevels = ['read uncommitted', 'read committed', 'repeatable read', 'serializable']
      const normalized = options.isolationLevel.toLowerCase()
      if (!validLevels.includes(normalized)) {
        throw new Error(`Invalid isolation level: "${options.isolationLevel}"`)
      }
      beginParts.push(`ISOLATION LEVEL ${normalized.toUpperCase()}`)
    }
    if (options?.readOnly) {
      beginParts.push('READ ONLY')
    }
    if (options?.deferrable) {
      beginParts.push('DEFERRABLE')
    }

    await pglite.query(beginParts.join(' '))

    const txSql = createTransactionSql(pglite)

    try {
      const result = await fn(txSql)
      await pglite.query('COMMIT')
      return result
    } catch (error) {
      await pglite.query('ROLLBACK')
      throw error
    }
  }

  // Add savepoint (throws since it's only valid in transactions)
  sql.savepoint = async <T>(): Promise<T> => {
    throw new Error('savepoint() can only be called within a transaction')
  }

  // Add end method
  sql.end = async (): Promise<void> => {
    await transport.close()
  }

  // Add options for Drizzle compatibility
  sql.options = {
    parsers: {},
    serializers: {},
  }

  // Add reserve method (creates a new client)
  sql.reserve = async (): Promise<PGLiteReservedSql> => {
    const reserved = await createPGLiteSql(pglite)
    ;(reserved as PGLiteReservedSql).release = async () => {
      // No-op for PGLite since connections are not pooled
    }
    return reserved as PGLiteReservedSql
  }

  // Add transport for direct access
  sql.transport = transport

  // Add pglite for direct access
  sql.pglite = pglite

  return sql
}

/**
 * Create a transaction-scoped SQL function
 */
function createTransactionSql(pglite: PGlite): PGLiteTransactionSql {
  let savepointCounter = 0

  const txSql = function <T extends Row = Row>(
    strings: TemplateStringsArray,
    ...values: unknown[]
  ): Promise<T[]> {
    const parts: string[] = []
    for (let i = 0; i < strings.length; i++) {
      const str = strings[i]
      if (str !== undefined) {
        parts.push(str)
      }
      if (i < values.length) {
        parts.push(`$${i + 1}`)
      }
    }
    const query = parts.join('')

    return pglite.query<T>(query, values).then((result: { rows: T[] }) => result.rows)
  } as PGLiteTransactionSql

  txSql.unsafe = async <T extends Row = Row>(
    query: string,
    params?: unknown[]
  ): Promise<T[]> => {
    const result = await pglite.query<T>(query, params)
    return result.rows
  }

  txSql.savepoint = async <T>(
    fn: (sql: PGLiteTransactionSql) => Promise<T>
  ): Promise<T> => {
    const savepointName = `sp_${++savepointCounter}`
    await pglite.query(`SAVEPOINT ${savepointName}`)

    try {
      const result = await fn(txSql)
      await pglite.query(`RELEASE SAVEPOINT ${savepointName}`)
      return result
    } catch (error) {
      await pglite.query(`ROLLBACK TO SAVEPOINT ${savepointName}`)
      throw error
    }
  }

  txSql.options = {
    parsers: {},
    serializers: {},
  }

  return txSql
}

/**
 * PGLite-backed SQL interface
 */
export interface PGLiteSql {
  /**
   * Execute a SQL query using tagged template literal
   */
  <T extends Row = Row>(
    strings: TemplateStringsArray,
    ...values: unknown[]
  ): Promise<T[]>

  /**
   * Execute raw SQL
   */
  unsafe<T extends Row = Row>(query: string, params?: unknown[]): Promise<T[]>

  /**
   * Begin a transaction
   */
  begin<T>(
    fn: (sql: PGLiteTransactionSql) => Promise<T>,
    options?: TransactionOptions
  ): Promise<T>

  /**
   * Create a savepoint (only valid in transactions)
   */
  savepoint<T>(fn: (sql: PGLiteTransactionSql) => Promise<T>): Promise<T>

  /**
   * End the connection
   */
  end(): Promise<void>

  /**
   * Reserve a connection
   */
  reserve(): Promise<PGLiteReservedSql>

  /**
   * Options for Drizzle compatibility
   */
  options: {
    parsers: Record<number, (value: string) => unknown>
    serializers: Record<number, (value: unknown) => string>
  }

  /**
   * The underlying transport
   */
  transport: PGLiteTransport

  /**
   * The underlying PGLite instance
   */
  pglite: PGlite
}

/**
 * Transaction-scoped SQL interface
 */
export interface PGLiteTransactionSql {
  /**
   * Execute a SQL query using tagged template literal
   */
  <T extends Row = Row>(
    strings: TemplateStringsArray,
    ...values: unknown[]
  ): Promise<T[]>

  /**
   * Execute raw SQL
   */
  unsafe<T extends Row = Row>(query: string, params?: unknown[]): Promise<T[]>

  /**
   * Create a savepoint within the transaction
   */
  savepoint<T>(fn: (sql: PGLiteTransactionSql) => Promise<T>): Promise<T>

  /**
   * Options for Drizzle compatibility
   */
  options: {
    parsers: Record<number, (value: string) => unknown>
    serializers: Record<number, (value: unknown) => string>
  }
}

/**
 * Reserved connection SQL interface
 */
export interface PGLiteReservedSql extends PGLiteSql {
  /**
   * Release the reserved connection
   */
  release(): Promise<void>
}