/**
 * CLI Utilities
 *
 * Helper functions for the postgres.do CLI.
 *
 * @module cli/utils
 */

import { join, extname } from 'node:path'
import { existsSync, readFileSync, readdirSync, statSync } from 'node:fs'

/**
 * Migration definition (simplified for loading)
 */
export interface MigrationDefinition {
  id: string
  name: string
  version: number
  up: string
  down?: string | undefined
  isReversible?: boolean | undefined
  tags?: string[] | undefined
  transactional?: boolean | undefined
  timeoutMs?: number | undefined
}

/**
 * Query executor interface
 */
export interface QueryExecutor {
  query<T = unknown>(
    sql: string,
    params?: unknown[]
  ): Promise<{
    rows: T[]
    fields: { name: string; dataTypeID: number }[]
    affectedRows?: number
  }>
  end?: () => Promise<void>
}

/**
 * Load migrations from a directory
 *
 * Supports multiple formats:
 * - TypeScript files (*.ts) exporting a Migration object
 * - JavaScript files (*.js) exporting a Migration object
 * - SQL directories with up.sql/down.sql files
 *
 * @param dir Path to migrations directory
 * @returns Array of migrations sorted by version
 */
export function loadMigrationsFromDirectory(dir: string): MigrationDefinition[] {
  const migrations: MigrationDefinition[] = []
  const entries = readdirSync(dir)

  for (const entry of entries) {
    const entryPath = join(dir, entry)
    const stat = statSync(entryPath)

    // Skip hidden files and non-migration files
    if (entry.startsWith('.') || entry.startsWith('_')) {
      continue
    }

    if (stat.isDirectory()) {
      // SQL-style migration directory
      const upPath = join(entryPath, 'up.sql')
      const downPath = join(entryPath, 'down.sql')

      if (existsSync(upPath)) {
        const migration = loadSqlMigration(entry, entryPath, upPath, downPath)
        if (migration) {
          migrations.push(migration)
        }
      }
    } else if (stat.isFile()) {
      const ext = extname(entry)

      if (ext === '.ts' || ext === '.js' || ext === '.mjs') {
        // TypeScript/JavaScript migration
        const migration = loadTsMigration(entry, entryPath)
        if (migration) {
          migrations.push(migration)
        }
      } else if (ext === '.sql') {
        // Single SQL file migration
        const migration = loadSingleSqlMigration(entry, entryPath)
        if (migration) {
          migrations.push(migration)
        }
      }
    }
  }

  // Sort by version
  return migrations.sort((a, b) => a.version - b.version)
}

/**
 * Load a SQL-style migration from a directory
 */
function loadSqlMigration(
  dirName: string,
  _dirPath: string,
  upPath: string,
  downPath: string
): MigrationDefinition | null {
  // Parse migration info from directory name
  // Expected format: NNNN_name or timestamp_name
  const match = dirName.match(/^(\d+)_(.+)$/)
  if (!match || !match[1] || !match[2]) {
    console.warn(`Skipping invalid migration directory: ${dirName}`)
    return null
  }

  const version = parseInt(match[1], 10)
  const name = match[2].replace(/_/g, ' ')

  const up = readFileSync(upPath, 'utf-8').trim()
  const down = existsSync(downPath)
    ? readFileSync(downPath, 'utf-8').trim()
    : undefined

  return {
    id: dirName,
    name,
    version,
    up,
    down,
    isReversible: !!down,
    tags: ['sql'],
  }
}

/**
 * Load a single SQL file migration
 */
function loadSingleSqlMigration(
  filename: string,
  filePath: string
): MigrationDefinition | null {
  // Parse migration info from filename
  // Expected format: NNNN_name.sql
  const match = filename.match(/^(\d+)_(.+)\.sql$/)
  if (!match || !match[1] || !match[2]) {
    console.warn(`Skipping invalid migration file: ${filename}`)
    return null
  }

  const version = parseInt(match[1], 10)
  const name = match[2].replace(/_/g, ' ')

  const content = readFileSync(filePath, 'utf-8').trim()

  // Try to split into up/down sections
  // Look for -- DOWN or -- ROLLBACK marker
  const downMarkers = ['-- DOWN', '--DOWN', '-- ROLLBACK', '--ROLLBACK']
  let up = content
  let down: string | undefined

  for (const marker of downMarkers) {
    const index = content.toUpperCase().indexOf(marker.toUpperCase())
    if (index !== -1) {
      up = content.substring(0, index).trim()
      down = content.substring(index + marker.length).trim()
      break
    }
  }

  return {
    id: filename.replace(/\.sql$/, ''),
    name,
    version,
    up,
    down,
    isReversible: !!down,
    tags: ['sql'],
  }
}

/**
 * Load a TypeScript/JavaScript migration
 *
 * This function reads the file and parses it to extract migration info.
 * For full runtime loading, use dynamic imports.
 */
function loadTsMigration(
  filename: string,
  filePath: string
): MigrationDefinition | null {
  // Parse migration info from filename
  // Expected format: NNNN_name.ts
  const match = filename.match(/^(\d+)_(.+)\.(ts|js|mjs)$/)
  if (!match || !match[1] || !match[2]) {
    console.warn(`Skipping invalid migration file: ${filename}`)
    return null
  }

  const version = parseInt(match[1], 10)
  const namePart = match[2]
  const baseName = namePart.replace(/_/g, ' ')

  // Read and parse the file
  const content = readFileSync(filePath, 'utf-8')

  // Try to extract migration object
  // This is a simple parser - for complex cases, use dynamic import
  const migration = parseTypescriptMigration(content, {
    id: filename.replace(/\.(ts|js|mjs)$/, ''),
    name: baseName,
    version,
  })

  return migration
}

/**
 * Parse a TypeScript migration file to extract migration info
 *
 * This is a simple parser that extracts template literal SQL.
 * It handles common patterns but may not work for all cases.
 */
function parseTypescriptMigration(
  content: string,
  defaults: { id: string; name: string; version: number }
): MigrationDefinition | null {
  // Extract id
  const idMatch = content.match(/id:\s*['"`]([^'"`]+)['"`]/)
  const id = idMatch?.[1] || defaults.id

  // Extract name
  const nameMatch = content.match(/name:\s*['"`]([^'"`]+)['"`]/)
  const name = nameMatch?.[1] || defaults.name

  // Extract version
  const versionMatch = content.match(/version:\s*(\d+)/)
  const version = versionMatch?.[1] ? parseInt(versionMatch[1], 10) : defaults.version

  // Extract up SQL (template literal)
  const upMatch = content.match(/up:\s*`([\s\S]*?)`/)
  const up = upMatch?.[1]?.trim()

  if (!up) {
    // Try extracting from string literal
    const upStringMatch = content.match(/up:\s*['"]([^'"]+)['"]/)
    if (!upStringMatch?.[1]) {
      console.warn(`Could not extract up SQL from: ${defaults.id}`)
      return null
    }
  }

  // Extract down SQL (template literal)
  const downMatch = content.match(/down:\s*`([\s\S]*?)`/)
  const down = downMatch?.[1]?.trim()

  // Extract isReversible
  const reversibleMatch = content.match(/isReversible:\s*(true|false)/)
  const isReversible = reversibleMatch?.[1] === 'true' || (reversibleMatch?.[1] !== 'false' && !!down)

  // Extract tags
  const tagsMatch = content.match(/tags:\s*\[([^\]]*)\]/)
  let tags: string[] | undefined
  if (tagsMatch?.[1]) {
    tags = tagsMatch[1]
      .split(',')
      .map((t) => t.trim().replace(/['"`]/g, ''))
      .filter((t) => t.length > 0)
  }

  // Extract transactional
  const transactionalMatch = content.match(/transactional:\s*(true|false)/)
  const transactional = transactionalMatch?.[1] !== 'false'

  return {
    id,
    name,
    version,
    up: up || '',
    down,
    isReversible,
    tags,
    transactional,
  }
}

/**
 * Create a query executor from a database URL
 */
export async function createQueryExecutor(url: string): Promise<QueryExecutor> {
  // Import postgres.do client dynamically
  const postgres = (await import('../index.js')).default

  const sql = postgres(url)

  // Wrap in QueryExecutor interface
  const executor: QueryExecutor = {
    async query<T = unknown>(sqlStr: string, params?: unknown[]) {
      // Use tagged template for proper escaping
      if (params && params.length > 0) {
        // Substitute parameters ($1, $2, etc)
        let parameterizedSql = sqlStr
        params.forEach((param, i) => {
          const placeholder = `$${i + 1}`
          // This is a simplified approach - real implementation would use proper parameterization
          parameterizedSql = parameterizedSql.replace(
            placeholder,
            typeof param === 'string' ? `'${param.replace(/'/g, "''")}'` : String(param)
          )
        })
        const result = await sql.unsafe(parameterizedSql)
        return {
          rows: result as T[],
          fields: [] as { name: string; dataTypeID: number }[],
        }
      }

      const result = await sql.unsafe(sqlStr)
      return {
        rows: result as T[],
        fields: [] as { name: string; dataTypeID: number }[],
      }
    },
    async end() {
      await sql.end()
    },
  }
  return executor
}

/**
 * Parse a migration version string or number
 */
export function parseVersion(value: string | number): number {
  if (typeof value === 'number') {
    return value
  }

  // Handle formats like "0001" or "v1" or just "1"
  const cleaned = value.replace(/^v/i, '')
  const version = parseInt(cleaned, 10)

  if (isNaN(version)) {
    throw new Error(`Invalid version: ${value}`)
  }

  return version
}

/**
 * Validate a migration definition
 */
export function validateMigration(migration: MigrationDefinition): string[] {
  const errors: string[] = []

  if (!migration.id || typeof migration.id !== 'string') {
    errors.push('Migration ID must be a non-empty string')
  }

  if (!migration.name || typeof migration.name !== 'string') {
    errors.push('Migration name must be a non-empty string')
  }

  if (typeof migration.version !== 'number' || migration.version < 1) {
    errors.push('Migration version must be a positive integer')
  }

  if (!migration.up || typeof migration.up !== 'string') {
    errors.push('Migration up SQL must be a non-empty string')
  }

  return errors
}