/**
 * Schema Sync CLI Commands
 *
 * Pull and push database schemas.
 *
 * @example
 * ```bash
 * # Pull schema from database to local file
 * npx postgres.do schema:pull --url $DATABASE_URL --output ./schema.ts
 *
 * # Push local schema to database (diff and apply)
 * npx postgres.do schema:push --url $DATABASE_URL --input ./schema.ts
 *
 * # Push with dry run
 * npx postgres.do schema:push --url $DATABASE_URL --input ./schema.ts --dry-run
 * ```
 *
 * @module cli/commands/schema-sync
 */

import { writeFileSync, readFileSync } from 'node:fs'
import { resolve, dirname } from 'node:path'
import { mkdirSync, existsSync } from 'node:fs'

import postgres from '../../index.js'
import type { Sql } from '../../types.js'
import {
  generateSchemaFromDatabase,
  generateSchemaWithTypes,
  introspectDatabase,
  parseTypeScriptInterfaces,
  convertInterfacesToTables,
  type DatabaseSchemaDefinition,
} from '../../drizzle/schema-generator.js'
import {
  diffSchemas,
  generateMigrationSQL,
  formatSchemaDiff,
  type SchemaDiffOptions,
} from '../../drizzle/schema-diff.js'
import type { SchemaSyncCLIOptions } from '../types.js'
import { formatSuccess, formatError, formatWarning, formatInfo, formatProgress } from '../formatting.js'

/**
 * Run schema:pull command
 *
 * Pull schema from a database and generate Drizzle schema file
 */
export async function runSchemaPull(options: SchemaSyncCLIOptions): Promise<void> {
  const url = options.url || process.env['DATABASE_URL']

  if (!url) {
    console.error(formatError('Database URL is required'))
    console.error('Provide --url or set DATABASE_URL environment variable')
    process.exit(1)
  }

  const outputPath = resolve(process.cwd(), options.output || './schema.ts')

  let client: Sql | null = null

  try {
    if (options.verbose) {
      console.log(formatInfo('Connecting to database...'))
    }
    client = postgres(url)

    if (options.verbose) {
      console.log(formatInfo(`Introspecting schema '${options.schema || 'public'}'...`))
    }

    // Build generator options - only include defined values
    const generatorOptions: {
      schema?: string
      includeRelations?: boolean
      includeIndexes?: boolean
      includeTables?: string[]
      excludeTables?: string[]
    } = {}
    if (options.schema !== undefined) generatorOptions.schema = options.schema
    if (options.includeRelations !== undefined) generatorOptions.includeRelations = options.includeRelations
    if (options.includeIndexes !== undefined) generatorOptions.includeIndexes = options.includeIndexes
    if (options.includeTables !== undefined) generatorOptions.includeTables = options.includeTables
    if (options.excludeTables !== undefined) generatorOptions.excludeTables = options.excludeTables

    if (options.generateTypes) {
      const { schema, types } = await generateSchemaWithTypes(client, generatorOptions)

      ensureDirectoryExists(outputPath)
      writeFileSync(outputPath, schema)
      console.log(formatSuccess(`Schema written to: ${outputPath}`))

      const typesPath = outputPath.replace(/\.ts$/, '.types.ts')
      writeFileSync(typesPath, types)
      console.log(formatSuccess(`Types written to: ${typesPath}`))
    } else {
      const schema = await generateSchemaFromDatabase(client, generatorOptions)

      ensureDirectoryExists(outputPath)
      writeFileSync(outputPath, schema)
      console.log(formatSuccess(`Schema written to: ${outputPath}`))
    }

    if (options.json) {
      const dbSchema = await introspectDatabase(client, generatorOptions)
      console.log(JSON.stringify(dbSchema, null, 2))
    }
  } finally {
    if (client) {
      await client.end()
    }
  }
}

/**
 * Run schema:push command
 *
 * Push local schema to database by comparing and applying changes
 */
export async function runSchemaPush(options: SchemaSyncCLIOptions): Promise<void> {
  const url = options.url || process.env['DATABASE_URL']

  if (!url) {
    console.error(formatError('Database URL is required'))
    console.error('Provide --url or set DATABASE_URL environment variable')
    process.exit(1)
  }

  if (!options.input) {
    console.error(formatError('Input schema file is required'))
    console.error('Provide --input with path to Drizzle schema file')
    process.exit(1)
  }

  const inputPath = resolve(process.cwd(), options.input)

  if (!existsSync(inputPath)) {
    console.error(formatError(`Input file not found: ${inputPath}`))
    process.exit(1)
  }

  let client: Sql | null = null

  try {
    if (options.verbose) {
      console.log(formatInfo('Connecting to database...'))
    }
    client = postgres(url)

    // Get current database schema
    if (options.verbose) {
      console.log(formatInfo('Introspecting current database schema...'))
    }

    const introspectOptions: { schema?: string; includeIndexes: boolean } = {
      includeIndexes: true,
    }
    if (options.schema !== undefined) introspectOptions.schema = options.schema

    const currentSchema = await introspectDatabase(client, introspectOptions)

    // Parse desired schema from input file
    if (options.verbose) {
      console.log(formatInfo('Parsing local schema file...'))
    }

    const desiredSchema = await parseSchemaFile(inputPath, options.schema)

    // Compare schemas
    if (options.verbose) {
      console.log(formatInfo('Comparing schemas...'))
    }

    const diffOptions: SchemaDiffOptions = {
      compareIndexes: true,
    }
    if (options.excludeTables !== undefined) diffOptions.excludeTables = options.excludeTables
    if (options.includeTables !== undefined) diffOptions.includeTables = options.includeTables

    // Note: we diff from current -> desired to get the changes needed
    const diff = diffSchemas(currentSchema, desiredSchema, diffOptions)

    if (diff.isIdentical) {
      console.log(formatSuccess('Schema is already up to date'))
      return
    }

    // Show diff
    console.log(formatSchemaDiff(diff, { color: !options.noColor }))

    // Generate migration
    const migrationOptions: { includeComments: boolean; safeMode: boolean; schema?: string } = {
      includeComments: true,
      safeMode: true,
    }
    if (options.schema !== undefined) migrationOptions.schema = options.schema

    const migration = generateMigrationSQL(diff, migrationOptions)

    // Show warnings
    if (migration.warnings.length > 0) {
      console.log('')
      console.log(formatWarning('Warnings:'))
      for (const warning of migration.warnings) {
        console.log(`  - ${warning}`)
      }
    }

    // Dry run - just show what would be executed
    if (options.dryRun) {
      console.log('')
      console.log(formatInfo('Dry run - SQL that would be executed:'))
      console.log('')
      console.log(migration.upSQL)
      return
    }

    // Require confirmation for destructive changes
    if (!options.force && (diff.summary.tablesRemoved > 0 || diff.summary.columnsRemoved > 0)) {
      console.log('')
      console.log(formatWarning('This operation will remove tables/columns and may cause data loss.'))
      console.log('Use --force to proceed or --dry-run to preview changes.')
      process.exit(1)
    }

    // Apply changes
    console.log('')
    console.log(formatInfo('Applying schema changes...'))

    for (let i = 0; i < migration.up.length; i++) {
      const statement = migration.up[i]!
      if (options.verbose) {
        console.log(formatProgress(`Executing (${i + 1}/${migration.up.length}): ${statement.slice(0, 60)}...`))
      }

      try {
        await client.unsafe(statement)
      } catch (error) {
        console.error(formatError(`Failed to execute: ${statement}`))
        console.error(error instanceof Error ? error.message : String(error))

        if (!options.force) {
          console.error('')
          console.error('Use --force to continue despite errors')
          process.exit(1)
        }
      }
    }

    console.log('')
    console.log(formatSuccess(`Schema push complete - ${migration.up.length} statements executed`))
  } finally {
    if (client) {
      await client.end()
    }
  }
}

/**
 * Parse a Drizzle schema file and extract table definitions
 *
 * This is a simplified parser that works with common Drizzle schema patterns.
 * For complex schemas, consider using the TypeScript compiler API.
 */
async function parseSchemaFile(filePath: string, schemaName?: string): Promise<DatabaseSchemaDefinition> {
  const source = readFileSync(filePath, 'utf-8')

  // Try to parse as TypeScript interfaces first (for type definitions)
  const interfaces = parseTypeScriptInterfaces(source)
  if (interfaces.length > 0) {
    const convertOptions: { schema?: string } = {}
    if (schemaName !== undefined) convertOptions.schema = schemaName
    const tables = convertInterfacesToTables(interfaces, convertOptions)
    return {
      tables,
      enums: [],
      schemaName: schemaName ?? 'public',
    }
  }

  // Parse Drizzle table definitions
  const tables = parseDrizzleTables(source, schemaName ?? 'public')
  const enums = parseDrizzleEnums(source)

  return {
    tables,
    enums,
    schemaName: schemaName ?? 'public',
  }
}

/**
 * Parse Drizzle pgTable definitions from source code
 */
function parseDrizzleTables(source: string, schema: string): DatabaseSchemaDefinition['tables'] {
  const tables: DatabaseSchemaDefinition['tables'] = []

  // Match pgTable definitions
  // Pattern: export const tableName = pgTable('table_name', { ... })
  const tableRegex = /export\s+const\s+(\w+)\s*=\s*pgTable\s*\(\s*['"]([^'"]+)['"]\s*,\s*\{([^}]+)\}/g

  let match
  while ((match = tableRegex.exec(source)) !== null) {
    const tableName = match[2] ?? match[1]!
    const columnsBody = match[3] ?? ''

    const columns = parseDrizzleColumns(columnsBody)

    tables.push({
      name: tableName,
      schema,
      columns,
      indexes: [],
    })
  }

  return tables
}

/**
 * Parse Drizzle column definitions
 */
function parseDrizzleColumns(body: string): DatabaseSchemaDefinition['tables'][0]['columns'] {
  const columns: DatabaseSchemaDefinition['tables'][0]['columns'] = []

  // Match column definitions
  // Pattern: columnName: type('column_name').modifiers()
  const columnRegex = /(\w+):\s*(\w+)\s*\(\s*['"]([^'"]+)['"]/g

  let match
  while ((match = columnRegex.exec(body)) !== null) {
    const columnDbName = match[3] ?? match[1]!
    const drizzleType = match[2]!

    // Get the full column definition to parse modifiers
    const startIndex = match.index + match[0].length
    const restOfBody = body.slice(startIndex)

    // Find the end of this column definition (next comma at same level or end)
    let depth = 1
    let endIndex = 0
    for (let i = 0; i < restOfBody.length; i++) {
      const char = restOfBody[i]
      if (char === '(' || char === '{' || char === '[') depth++
      else if (char === ')' || char === '}' || char === ']') depth--
      else if (char === ',' && depth === 0) {
        endIndex = i
        break
      }
      if (depth === 0) {
        endIndex = i
        break
      }
    }

    const columnDef = restOfBody.slice(0, endIndex)

    // Parse modifiers
    const isPrimaryKey = columnDef.includes('.primaryKey()')
    const isNotNull = columnDef.includes('.notNull()')
    const isUnique = columnDef.includes('.unique()')
    const hasDefault = columnDef.includes('.default(') || columnDef.includes('.defaultNow()')

    // Get default value
    let defaultValue: string | undefined
    if (columnDef.includes('.defaultNow()')) {
      defaultValue = 'now()'
    } else if (columnDef.includes('.$defaultFn(')) {
      // Custom default function - likely UUID
      if (columnDef.includes('randomUUID')) {
        defaultValue = 'gen_random_uuid()'
      }
    } else {
      const defaultMatch = columnDef.match(/\.default\s*\(\s*([^)]+)\s*\)/)
      if (defaultMatch) {
        defaultValue = defaultMatch[1]
      }
    }

    // Map Drizzle type to PostgreSQL type
    const pgType = drizzleTypeToPgType(drizzleType)

    const column: DatabaseSchemaDefinition['tables'][0]['columns'][0] = {
      name: columnDbName,
      pgType,
      nullable: !isNotNull && !isPrimaryKey,
      hasDefault,
      isPrimaryKey,
      isUnique,
      isArray: false,
    }
    if (defaultValue !== undefined) {
      column.defaultValue = defaultValue
    }
    columns.push(column)
  }

  return columns
}

/**
 * Parse Drizzle pgEnum definitions
 */
function parseDrizzleEnums(source: string): DatabaseSchemaDefinition['enums'] {
  const enums: DatabaseSchemaDefinition['enums'] = []

  // Match pgEnum definitions
  // Pattern: export const enumName = pgEnum('enum_name', ['value1', 'value2'])
  const enumRegex = /export\s+const\s+(\w+)\s*=\s*pgEnum\s*\(\s*['"]([^'"]+)['"]\s*,\s*\[([^\]]+)\]/g

  let match
  while ((match = enumRegex.exec(source)) !== null) {
    const enumName = match[2]!
    const valuesStr = match[3] ?? ''

    // Parse values
    const values = valuesStr
      .split(',')
      .map(v => v.trim().replace(/^['"]|['"]$/g, ''))
      .filter(Boolean)

    enums.push({ name: enumName, values })
  }

  return enums
}

/**
 * Map Drizzle column type to PostgreSQL type
 */
function drizzleTypeToPgType(drizzleType: string): string {
  const typeMap: Record<string, string> = {
    text: 'text',
    varchar: 'character varying',
    char: 'character',
    integer: 'integer',
    smallint: 'smallint',
    bigint: 'bigint',
    serial: 'serial',
    smallserial: 'smallserial',
    bigserial: 'bigserial',
    real: 'real',
    doublePrecision: 'double precision',
    numeric: 'numeric',
    decimal: 'numeric',
    boolean: 'boolean',
    date: 'date',
    time: 'time',
    timestamp: 'timestamp',
    interval: 'interval',
    uuid: 'uuid',
    json: 'json',
    jsonb: 'jsonb',
    bytea: 'bytea',
    inet: 'inet',
    cidr: 'cidr',
    macaddr: 'macaddr',
    point: 'point',
  }

  return typeMap[drizzleType] ?? drizzleType
}

/**
 * Ensure directory exists for a file path
 */
function ensureDirectoryExists(filePath: string): void {
  const dir = dirname(filePath)
  if (!existsSync(dir)) {
    mkdirSync(dir, { recursive: true })
  }
}