/**
 * Schema Diff CLI Command
 *
 * Compare database schemas and generate migration SQL.
 *
 * @example
 * ```bash
 * # Diff two databases
 * npx postgres.do schema:diff --from $LOCAL_DB_URL --to $PROD_DB_URL
 *
 * # Generate migration from diff
 * npx postgres.do schema:diff --from $LOCAL_DB_URL --to $PROD_DB_URL --generate-migration
 *
 * # Output as JSON
 * npx postgres.do schema:diff --from $LOCAL_DB_URL --to $PROD_DB_URL --json
 * ```
 *
 * @module cli/commands/schema-diff
 */

import { writeFileSync } 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 {
  compareSchemas,
  generateMigrationSQL,
  generateDrizzleMigration,
  formatSchemaDiff,
  formatSchemaDiffJSON,
  type SchemaDiffOptions,
  type MigrationSQLOptions,
} from '../../drizzle/schema-diff.js'
import type { SchemaDiffCLIOptions } from '../types.js'
import { formatSuccess, formatError, formatWarning, formatInfo } from '../formatting.js'

/**
 * Run schema:diff command
 */
export async function runSchemaDiff(options: SchemaDiffCLIOptions): Promise<void> {
  const fromUrl = options.from || process.env['SOURCE_DATABASE_URL'] || process.env['DATABASE_URL']
  const toUrl = options.to || process.env['TARGET_DATABASE_URL']

  if (!fromUrl) {
    console.error(formatError('Source database URL is required'))
    console.error('Provide --from or set SOURCE_DATABASE_URL/DATABASE_URL environment variable')
    process.exit(1)
  }

  if (!toUrl) {
    console.error(formatError('Target database URL is required'))
    console.error('Provide --to or set TARGET_DATABASE_URL environment variable')
    process.exit(1)
  }

  let sourceClient: Sql | null = null
  let targetClient: Sql | null = null

  try {
    if (options.verbose) {
      console.log(formatInfo('Connecting to source database...'))
    }
    sourceClient = postgres(fromUrl)

    if (options.verbose) {
      console.log(formatInfo('Connecting to target database...'))
    }
    targetClient = postgres(toUrl)

    // Build diff options - only include defined values
    const diffOptions: SchemaDiffOptions = {
      compareIndexes: options.includeIndexes !== false,
    }
    if (options.ignoreCase !== undefined) diffOptions.ignoreCase = options.ignoreCase
    if (options.includeTables !== undefined) diffOptions.includeTables = options.includeTables
    if (options.excludeTables !== undefined) diffOptions.excludeTables = options.excludeTables

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

    // Build combined options for compareSchemas
    const compareOptions: SchemaDiffOptions & { schema?: string; includeIndexes?: boolean } = {
      ...diffOptions,
    }
    if (options.schema !== undefined) compareOptions.schema = options.schema
    if (options.includeIndexes !== undefined) compareOptions.includeIndexes = options.includeIndexes

    const diff = await compareSchemas(sourceClient, targetClient, compareOptions)

    // Output results
    if (options.json) {
      console.log(formatSchemaDiffJSON(diff))
    } else {
      console.log(formatSchemaDiff(diff, { color: !options.noColor }))
    }

    // Generate migration if requested
    if (options.generateMigration && !diff.isIdentical) {
      const migrationOptions: MigrationSQLOptions = {
        includeComments: true,
      }
      if (options.safeMode !== undefined) migrationOptions.safeMode = options.safeMode
      if (options.schema !== undefined) migrationOptions.schema = options.schema
      if (options.wrapInTransaction !== undefined) migrationOptions.wrapInTransaction = options.wrapInTransaction

      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}`)
        }
      }

      // Determine output path
      const outputDir = options.migrationsDir || './migrations'
      const timestamp = new Date().toISOString().replace(/[-:T]/g, '').slice(0, 14)
      const migrationName = options.migrationName || 'schema_sync'
      const fileName = `${timestamp}_${migrationName}`

      if (options.migrationFormat === 'sql') {
        // Generate raw SQL files
        const upPath = resolve(process.cwd(), outputDir, `${fileName}.up.sql`)
        const downPath = resolve(process.cwd(), outputDir, `${fileName}.down.sql`)

        ensureDirectoryExists(upPath)
        writeFileSync(upPath, migration.upSQL)
        writeFileSync(downPath, migration.downSQL)

        console.log('')
        console.log(formatSuccess(`Migration created:`))
        console.log(`  Up:   ${upPath}`)
        console.log(`  Down: ${downPath}`)
      } else {
        // Generate Drizzle migration file
        const migrationPath = resolve(process.cwd(), outputDir, `${fileName}.ts`)
        const drizzleMigration = generateDrizzleMigration(diff, migrationName, migrationOptions)

        ensureDirectoryExists(migrationPath)
        writeFileSync(migrationPath, drizzleMigration)

        console.log('')
        console.log(formatSuccess(`Migration created: ${migrationPath}`))
      }

      if (!migration.isReversible) {
        console.log(formatWarning('Note: This migration may not be fully reversible'))
      }
    } else if (options.generateMigration && diff.isIdentical) {
      console.log('')
      console.log(formatInfo('No migration needed - schemas are identical'))
    }

    // Exit with code 1 if schemas differ (useful for CI)
    if (options.exitCode && !diff.isIdentical) {
      process.exit(1)
    }
  } finally {
    if (sourceClient) {
      await sourceClient.end()
    }
    if (targetClient) {
      await targetClient.end()
    }
  }
}

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