/**
 * PGLite Collection Integration
 *
 * Provides TanStack-style collections backed by PGLite local database.
 */

import type {
  BaseRecord,
  PGLiteCollectionOptions,
  Collection,
  SyncState,
  PGLiteInterface,
} from '../types'
import { validateIdentifier } from '@dotdo/postgres-shared/validation'

/**
 * Quote a PostgreSQL identifier (table name, column name, etc.)
 * by escaping any double quotes and wrapping in double quotes.
 *
 * This provides defense-in-depth alongside validateIdentifier().
 * Even after validation, proper quoting ensures safe SQL interpolation.
 *
 * @param identifier - The identifier to quote (must be pre-validated)
 * @returns The properly quoted identifier
 */
function quoteIdentifier(identifier: string): string {
  // Escape any double quotes by doubling them (PostgreSQL standard)
  const escaped = identifier.replace(/"/g, '""')
  return `"${escaped}"`
}

/**
 * PGLiteCollection - A collection backed by PGLite local database
 */
export class PGLiteCollection<T extends BaseRecord> implements Collection<T> {
  readonly id: string
  readonly table: string

  private items: Map<string | number, T> = new Map()
  private subscribers: Set<(items: T[]) => void> = new Set()
  private options: PGLiteCollectionOptions<T>
  private syncState: SyncState = {
    connected: false,
    initialized: false,
    pendingCount: 0,
  }
  private pgliteInstance?: PGLiteInterface

  constructor(options: PGLiteCollectionOptions<T>) {
    // Validate table name to prevent SQL injection
    validateIdentifier(options.table, 'table')

    this.id = options.id
    this.table = options.table
    this.options = options
  }

  /**
   * Initialize the collection - create table if needed and load data
   */
  async initialize(): Promise<void> {
    const pglite = await this.getPGLite()

    // Create table if configured
    if (this.options.autoCreateTable && this.options.tableSchema) {
      await pglite.exec(
        `CREATE TABLE IF NOT EXISTS ${quoteIdentifier(this.table)} (${this.options.tableSchema})`
      )
    }

    // Load existing data
    await this.refresh()

    this.syncState = {
      ...this.syncState,
      connected: true,
      initialized: true,
      lastSyncAt: Date.now(),
    }
  }

  /**
   * Refresh data from PGLite
   */
  async refresh(): Promise<void> {
    const pglite = await this.getPGLite()
    const { rows } = await pglite.query<T>(`SELECT * FROM ${quoteIdentifier(this.table)}`)
    this.items.clear()
    for (const row of rows) {
      this.items.set(row.id, row)
    }
  }

  /**
   * Get all items in the collection
   */
  getAll(): T[] {
    return Array.from(this.items.values())
  }

  /**
   * Get item by ID
   */
  get(id: string | number): T | undefined {
    return this.items.get(id)
  }

  /**
   * Insert a new item
   */
  async insert(data: Omit<T, 'id'> & { id?: T['id'] }): Promise<T> {
    const pglite = await this.getPGLite()

    // Build insert query
    const keys = Object.keys(data).filter(k => k !== 'id')

    // Validate all column names to prevent SQL injection
    for (const key of keys) {
      validateIdentifier(key, 'column')
    }

    const values = keys.map(k => (data as Record<string, unknown>)[k])
    const placeholders = keys.map((_, i) => `$${i + 1}`).join(', ')
    const columns = keys.map(k => quoteIdentifier(k)).join(', ')

    const sql = data.id
      ? `INSERT INTO ${quoteIdentifier(this.table)} ("id", ${columns}) VALUES ($${keys.length + 1}, ${placeholders}) RETURNING *`
      : `INSERT INTO ${quoteIdentifier(this.table)} (${columns}) VALUES (${placeholders}) RETURNING *`

    const params = data.id ? [...values, data.id] : values
    const { rows } = await pglite.query<T>(sql, params)

    const item = rows[0]
    if (!item) {
      throw new Error('Insert failed')
    }

    this.items.set(item.id, item)
    this.notifySubscribers()
    return item
  }

  /**
   * Update an existing item
   */
  async update(id: string | number, data: Partial<T>): Promise<T> {
    if (!this.items.has(id)) {
      throw new Error(`Item with id ${id} not found`)
    }

    const pglite = await this.getPGLite()

    // Build update query
    const keys = Object.keys(data).filter(k => k !== 'id')

    // Validate all column names to prevent SQL injection
    for (const key of keys) {
      validateIdentifier(key, 'column')
    }

    const values = keys.map(k => (data as Record<string, unknown>)[k])
    const setClause = keys.map((k, i) => `${quoteIdentifier(k)} = $${i + 1}`).join(', ')

    const sql = `UPDATE ${quoteIdentifier(this.table)} SET ${setClause} WHERE "id" = $${keys.length + 1} RETURNING *`
    const { rows } = await pglite.query<T>(sql, [...values, id])

    const updated = rows[0]
    if (!updated) {
      throw new Error('Update failed')
    }

    this.items.set(id, updated)
    this.notifySubscribers()
    return updated
  }

  /**
   * Delete an item
   */
  async delete(id: string | number): Promise<void> {
    if (!this.items.has(id)) {
      throw new Error(`Item with id ${id} not found`)
    }

    const pglite = await this.getPGLite()
    await pglite.query(`DELETE FROM ${quoteIdentifier(this.table)} WHERE "id" = $1`, [id])
    this.items.delete(id)
    this.notifySubscribers()
  }

  /**
   * Subscribe to changes
   */
  subscribe(callback: (items: T[]) => void): () => void {
    this.subscribers.add(callback)
    return () => {
      this.subscribers.delete(callback)
    }
  }

  /**
   * Get current sync state
   */
  getSyncState(): SyncState {
    return { ...this.syncState }
  }

  /**
   * Execute a raw SQL query
   */
  async rawQuery<R = unknown>(sql: string, params?: unknown[]): Promise<{ rows: R[] }> {
    const pglite = await this.getPGLite()
    return pglite.query<R>(sql, params)
  }

  private async getPGLite(): Promise<PGLiteInterface> {
    if (this.pgliteInstance) {
      return this.pgliteInstance
    }

    if (typeof this.options.pglite === 'function') {
      this.pgliteInstance = await this.options.pglite()
    } else {
      this.pgliteInstance = this.options.pglite
    }

    return this.pgliteInstance
  }

  private notifySubscribers(): void {
    const items = this.getAll()
    for (const callback of this.subscribers) {
      callback(items)
    }
  }
}

/**
 * Factory function to create a PGLiteCollection
 */
export function createPGLiteCollection<T extends BaseRecord>(
  options: PGLiteCollectionOptions<T>
): PGLiteCollection<T> {
  return new PGLiteCollection(options)
}

/**
 * Options for PGLiteStore
 */
export interface PGLiteStoreOptions {
  pglite: PGLiteInterface | (() => Promise<PGLiteInterface>)
}

/**
 * PGLiteStore - Manages multiple PGLite-backed collections
 */
export class PGLiteStore {
  private pglite: PGLiteInterface | (() => Promise<PGLiteInterface>)
  private collections: Map<string, PGLiteCollection<BaseRecord>> = new Map()
  private pgliteInstance?: PGLiteInterface
  isDisposed: boolean = false

  constructor(options: PGLiteStoreOptions) {
    this.pglite = options.pglite
  }

  /**
   * Register a new collection
   */
  registerCollection<T extends BaseRecord>(
    options: Omit<PGLiteCollectionOptions<T>, 'pglite'>
  ): PGLiteCollection<T> {
    if (this.isDisposed) {
      throw new Error('Cannot register collection: PGLiteStore has been disposed')
    }
    const collection = new PGLiteCollection<T>({
      ...options,
      pglite: this.pglite,
    })
    this.collections.set(options.id, collection as unknown as PGLiteCollection<BaseRecord>)
    return collection
  }

  /**
   * Get a registered collection
   */
  getCollection<T extends BaseRecord>(id: string): PGLiteCollection<T> | undefined {
    return this.collections.get(id) as PGLiteCollection<T> | undefined
  }

  /**
   * Get all collection IDs
   */
  getCollectionIds(): string[] {
    return Array.from(this.collections.keys())
  }

  /**
   * Initialize all collections
   */
  async initializeAll(): Promise<void> {
    await Promise.all(
      Array.from(this.collections.values()).map(c => c.initialize())
    )
  }

  /**
   * Dispose of all resources
   */
  async dispose(): Promise<void> {
    if (typeof this.pglite === 'function') {
      if (this.pgliteInstance) {
        await this.pgliteInstance.close()
      }
    } else {
      await this.pglite.close()
    }
    this.collections.clear()
    this.isDisposed = true
  }
}