/**
 * Schema Diff and Sync Tools
 *
 * Compare database schemas and generate migration SQL from differences.
 * Supports comparing:
 * - Two live databases
 * - Database vs Drizzle schema definition
 * - Two schema definitions
 *
 * @example Compare two databases
 * ```typescript
 * import { compareSchemas, generateMigrationSQL } from 'postgres.do/drizzle'
 *
 * const diff = await compareSchemas(sourceClient, targetClient)
 * const sql = generateMigrationSQL(diff)
 * ```
 *
 * @example Diff local vs production
 * ```typescript
 * const localSchema = await introspectDatabase(localClient)
 * const prodSchema = await introspectDatabase(prodClient)
 * const diff = diffSchemas(localSchema, prodSchema)
 * console.log(formatSchemaDiff(diff))
 * ```
 *
 * @module drizzle/schema-diff
 */

import type { Sql } from '../types.js'
import {
  introspectDatabase,
  type DatabaseSchemaDefinition,
  type SchemaTableDefinition,
  type SchemaColumnDefinition,
  type SchemaIndexDefinition,
  type SchemaEnumDefinition,
  type SchemaGeneratorOptions,
} from './schema-generator.js'

// ============================================================================
// Type Definitions
// ============================================================================

/**
 * Types of changes that can occur in a schema diff
 */
export type ChangeType = 'added' | 'removed' | 'modified'

/**
 * A single column change
 */
export interface ColumnChange {
  /** Type of change */
  changeType: ChangeType
  /** Column name */
  columnName: string
  /** Column in source schema (undefined if added) */
  source?: SchemaColumnDefinition
  /** Column in target schema (undefined if removed) */
  target?: SchemaColumnDefinition
  /** Specific attribute changes (for modified columns) */
  attributeChanges?: ColumnAttributeChange[]
}

/**
 * Specific attribute change within a column
 */
export interface ColumnAttributeChange {
  /** Attribute name */
  attribute: 'type' | 'nullable' | 'default' | 'primaryKey' | 'unique' | 'references' | 'maxLength'
  /** Value in source schema */
  sourceValue: unknown
  /** Value in target schema */
  targetValue: unknown
}

/**
 * A single index change
 */
export interface IndexChange {
  /** Type of change */
  changeType: ChangeType
  /** Index name */
  indexName: string
  /** Index in source schema (undefined if added) */
  source?: SchemaIndexDefinition
  /** Index in target schema (undefined if removed) */
  target?: SchemaIndexDefinition
}

/**
 * A single table change
 */
export interface TableChange {
  /** Type of change */
  changeType: ChangeType
  /** Table name */
  tableName: string
  /** Table in source schema (undefined if added) */
  source?: SchemaTableDefinition
  /** Table in target schema (undefined if removed) */
  target?: SchemaTableDefinition
  /** Column changes (for modified tables) */
  columnChanges?: ColumnChange[]
  /** Index changes (for modified tables) */
  indexChanges?: IndexChange[]
  /** Primary key changes */
  primaryKeyChange?: {
    source?: string[]
    target?: string[]
  }
}

/**
 * A single enum change
 */
export interface EnumChange {
  /** Type of change */
  changeType: ChangeType
  /** Enum name */
  enumName: string
  /** Enum in source schema (undefined if added) */
  source?: SchemaEnumDefinition
  /** Enum in target schema (undefined if removed) */
  target?: SchemaEnumDefinition
  /** Values added */
  addedValues?: string[]
  /** Values removed */
  removedValues?: string[]
}

/**
 * Complete schema diff result
 */
export interface SchemaDiff {
  /** Source schema name */
  sourceSchema: string
  /** Target schema name */
  targetSchema: string
  /** Table changes */
  tableChanges: TableChange[]
  /** Enum changes */
  enumChanges: EnumChange[]
  /** Whether schemas are identical */
  isIdentical: boolean
  /** Summary statistics */
  summary: {
    tablesAdded: number
    tablesRemoved: number
    tablesModified: number
    columnsAdded: number
    columnsRemoved: number
    columnsModified: number
    indexesAdded: number
    indexesRemoved: number
    enumsAdded: number
    enumsRemoved: number
    enumsModified: number
  }
}

/**
 * Options for schema diff
 */
export interface SchemaDiffOptions {
  /** Ignore case differences in names */
  ignoreCase?: boolean
  /** Ignore whitespace in default values */
  ignoreDefaultWhitespace?: boolean
  /** Tables to include (if specified, only these tables are compared) */
  includeTables?: string[]
  /** Tables to exclude from comparison */
  excludeTables?: string[]
  /** Whether to compare indexes */
  compareIndexes?: boolean
  /** Whether to detect column renames (vs add/remove) */
  detectRenames?: boolean
}

/**
 * Options for migration SQL generation
 */
export interface MigrationSQLOptions {
  /** Whether to include comments in generated SQL */
  includeComments?: boolean
  /** Whether to wrap in transaction */
  wrapInTransaction?: boolean
  /** Target database type (for syntax variations) */
  dialect?: 'postgresql' | 'postgres'
  /** Schema name to use */
  schema?: string
  /** Generate IF EXISTS/IF NOT EXISTS clauses */
  safeMode?: boolean
}

/**
 * Generated migration result
 */
export interface GeneratedMigration {
  /** The SQL statements for forward migration */
  up: string[]
  /** The SQL statements for rollback (if possible) */
  down: string[]
  /** Complete up migration as single string */
  upSQL: string
  /** Complete down migration as single string */
  downSQL: string
  /** Whether the migration is fully reversible */
  isReversible: boolean
  /** Warnings about potentially destructive changes */
  warnings: string[]
  /** Description of the migration */
  description: string
}

// ============================================================================
// Schema Comparison
// ============================================================================

/**
 * Compare two database schemas by connecting to both databases
 *
 * @example
 * ```typescript
 * const diff = await compareSchemas(localClient, prodClient, {
 *   compareIndexes: true,
 *   excludeTables: ['_migrations']
 * })
 * ```
 */
export async function compareSchemas(
  sourceClient: Sql,
  targetClient: Sql,
  options: SchemaDiffOptions & SchemaGeneratorOptions = {}
): Promise<SchemaDiff> {
  const sourceSchema = await introspectDatabase(sourceClient, options)
  const targetSchema = await introspectDatabase(targetClient, options)
  return diffSchemas(sourceSchema, targetSchema, options)
}

/**
 * Compare a database against a schema definition
 *
 * @example
 * ```typescript
 * const currentSchema = await introspectDatabase(client)
 * const diff = await compareDatabaseToSchema(client, desiredSchema)
 * ```
 */
export async function compareDatabaseToSchema(
  client: Sql,
  targetSchema: DatabaseSchemaDefinition,
  options: SchemaDiffOptions & SchemaGeneratorOptions = {}
): Promise<SchemaDiff> {
  const sourceSchema = await introspectDatabase(client, options)
  return diffSchemas(sourceSchema, targetSchema, options)
}

/**
 * Compare two schema definitions
 *
 * @example
 * ```typescript
 * const diff = diffSchemas(localSchema, prodSchema)
 * console.log(diff.summary)
 * ```
 */
export function diffSchemas(
  source: DatabaseSchemaDefinition,
  target: DatabaseSchemaDefinition,
  options: SchemaDiffOptions = {}
): SchemaDiff {
  const tableChanges: TableChange[] = []
  const enumChanges: EnumChange[] = []

  // Create maps for easier lookup
  const sourceTables = new Map(
    source.tables
      .filter(t => filterTable(t.name, options))
      .map(t => [normalizeIdentifier(t.name, options), t])
  )
  const targetTables = new Map(
    target.tables
      .filter(t => filterTable(t.name, options))
      .map(t => [normalizeIdentifier(t.name, options), t])
  )

  const sourceEnums = new Map(source.enums.map(e => [normalizeIdentifier(e.name, options), e]))
  const targetEnums = new Map(target.enums.map(e => [normalizeIdentifier(e.name, options), e]))

  // Find added, removed, and modified tables
  for (const [name, sourceTable] of sourceTables) {
    const targetTable = targetTables.get(name)
    if (!targetTable) {
      // Table removed in target (exists in source, not in target)
      tableChanges.push({
        changeType: 'removed',
        tableName: sourceTable.name,
        source: sourceTable,
      })
    } else {
      // Compare tables
      const diff = diffTables(sourceTable, targetTable, options)
      if (diff) {
        tableChanges.push(diff)
      }
    }
  }

  for (const [name, targetTable] of targetTables) {
    if (!sourceTables.has(name)) {
      // Table added in target (exists in target, not in source)
      tableChanges.push({
        changeType: 'added',
        tableName: targetTable.name,
        target: targetTable,
      })
    }
  }

  // Find added, removed, and modified enums
  for (const [name, sourceEnum] of sourceEnums) {
    const targetEnum = targetEnums.get(name)
    if (!targetEnum) {
      enumChanges.push({
        changeType: 'removed',
        enumName: sourceEnum.name,
        source: sourceEnum,
      })
    } else {
      const diff = diffEnums(sourceEnum, targetEnum)
      if (diff) {
        enumChanges.push(diff)
      }
    }
  }

  for (const [name, targetEnum] of targetEnums) {
    if (!sourceEnums.has(name)) {
      enumChanges.push({
        changeType: 'added',
        enumName: targetEnum.name,
        target: targetEnum,
      })
    }
  }

  // Calculate summary
  const summary = {
    tablesAdded: tableChanges.filter(t => t.changeType === 'added').length,
    tablesRemoved: tableChanges.filter(t => t.changeType === 'removed').length,
    tablesModified: tableChanges.filter(t => t.changeType === 'modified').length,
    columnsAdded: tableChanges.reduce((acc, t) =>
      acc + (t.columnChanges?.filter(c => c.changeType === 'added').length ?? 0), 0),
    columnsRemoved: tableChanges.reduce((acc, t) =>
      acc + (t.columnChanges?.filter(c => c.changeType === 'removed').length ?? 0), 0),
    columnsModified: tableChanges.reduce((acc, t) =>
      acc + (t.columnChanges?.filter(c => c.changeType === 'modified').length ?? 0), 0),
    indexesAdded: tableChanges.reduce((acc, t) =>
      acc + (t.indexChanges?.filter(i => i.changeType === 'added').length ?? 0), 0),
    indexesRemoved: tableChanges.reduce((acc, t) =>
      acc + (t.indexChanges?.filter(i => i.changeType === 'removed').length ?? 0), 0),
    enumsAdded: enumChanges.filter(e => e.changeType === 'added').length,
    enumsRemoved: enumChanges.filter(e => e.changeType === 'removed').length,
    enumsModified: enumChanges.filter(e => e.changeType === 'modified').length,
  }

  const isIdentical = tableChanges.length === 0 && enumChanges.length === 0

  return {
    sourceSchema: source.schemaName,
    targetSchema: target.schemaName,
    tableChanges,
    enumChanges,
    isIdentical,
    summary,
  }
}

/**
 * Compare two tables and return differences
 */
function diffTables(
  source: SchemaTableDefinition,
  target: SchemaTableDefinition,
  options: SchemaDiffOptions
): TableChange | null {
  const columnChanges: ColumnChange[] = []
  const indexChanges: IndexChange[] = []

  // Create maps for columns
  const sourceColumns = new Map(
    source.columns.map(c => [normalizeIdentifier(c.name, options), c])
  )
  const targetColumns = new Map(
    target.columns.map(c => [normalizeIdentifier(c.name, options), c])
  )

  // Compare columns
  for (const [name, sourceCol] of sourceColumns) {
    const targetCol = targetColumns.get(name)
    if (!targetCol) {
      columnChanges.push({
        changeType: 'removed',
        columnName: sourceCol.name,
        source: sourceCol,
      })
    } else {
      const colDiff = diffColumns(sourceCol, targetCol, options)
      if (colDiff) {
        columnChanges.push(colDiff)
      }
    }
  }

  for (const [name, targetCol] of targetColumns) {
    if (!sourceColumns.has(name)) {
      columnChanges.push({
        changeType: 'added',
        columnName: targetCol.name,
        target: targetCol,
      })
    }
  }

  // Compare indexes if requested
  if (options.compareIndexes !== false) {
    const sourceIndexes = new Map(
      source.indexes.map(i => [normalizeIdentifier(i.name, options), i])
    )
    const targetIndexes = new Map(
      target.indexes.map(i => [normalizeIdentifier(i.name, options), i])
    )

    for (const [name, sourceIdx] of sourceIndexes) {
      const targetIdx = targetIndexes.get(name)
      if (!targetIdx) {
        indexChanges.push({
          changeType: 'removed',
          indexName: sourceIdx.name,
          source: sourceIdx,
        })
      } else if (!indexesEqual(sourceIdx, targetIdx)) {
        indexChanges.push({
          changeType: 'modified',
          indexName: sourceIdx.name,
          source: sourceIdx,
          target: targetIdx,
        })
      }
    }

    for (const [name, targetIdx] of targetIndexes) {
      if (!sourceIndexes.has(name)) {
        indexChanges.push({
          changeType: 'added',
          indexName: targetIdx.name,
          target: targetIdx,
        })
      }
    }
  }

  // Compare primary keys
  let primaryKeyChange: TableChange['primaryKeyChange'] | undefined
  const sourcePK = source.primaryKey ?? source.columns.filter(c => c.isPrimaryKey).map(c => c.name)
  const targetPK = target.primaryKey ?? target.columns.filter(c => c.isPrimaryKey).map(c => c.name)

  if (!arraysEqual(sourcePK, targetPK)) {
    primaryKeyChange = { source: sourcePK, target: targetPK }
  }

  // Return null if no changes
  if (columnChanges.length === 0 && indexChanges.length === 0 && !primaryKeyChange) {
    return null
  }

  const result: TableChange = {
    changeType: 'modified',
    tableName: source.name,
    source,
    target,
    columnChanges,
    indexChanges,
  }
  if (primaryKeyChange) {
    result.primaryKeyChange = primaryKeyChange
  }
  return result
}

/**
 * Compare two columns and return differences
 */
function diffColumns(
  source: SchemaColumnDefinition,
  target: SchemaColumnDefinition,
  options: SchemaDiffOptions
): ColumnChange | null {
  const attributeChanges: ColumnAttributeChange[] = []

  // Compare type
  if (normalizeType(source.pgType) !== normalizeType(target.pgType)) {
    attributeChanges.push({
      attribute: 'type',
      sourceValue: source.pgType,
      targetValue: target.pgType,
    })
  }

  // Compare nullable
  if (source.nullable !== target.nullable) {
    attributeChanges.push({
      attribute: 'nullable',
      sourceValue: source.nullable,
      targetValue: target.nullable,
    })
  }

  // Compare default (with optional whitespace normalization)
  const sourceDefault = normalizeDefault(source.defaultValue, options)
  const targetDefault = normalizeDefault(target.defaultValue, options)
  if (sourceDefault !== targetDefault) {
    attributeChanges.push({
      attribute: 'default',
      sourceValue: source.defaultValue,
      targetValue: target.defaultValue,
    })
  }

  // Compare max length
  if (source.maxLength !== target.maxLength) {
    attributeChanges.push({
      attribute: 'maxLength',
      sourceValue: source.maxLength,
      targetValue: target.maxLength,
    })
  }

  // Compare unique constraint
  if (source.isUnique !== target.isUnique) {
    attributeChanges.push({
      attribute: 'unique',
      sourceValue: source.isUnique,
      targetValue: target.isUnique,
    })
  }

  // Compare foreign key references
  if (!referencesEqual(source.references, target.references)) {
    attributeChanges.push({
      attribute: 'references',
      sourceValue: source.references,
      targetValue: target.references,
    })
  }

  if (attributeChanges.length === 0) {
    return null
  }

  return {
    changeType: 'modified',
    columnName: source.name,
    source,
    target,
    attributeChanges,
  }
}

/**
 * Compare two enums and return differences
 */
function diffEnums(source: SchemaEnumDefinition, target: SchemaEnumDefinition): EnumChange | null {
  const sourceValues = new Set(source.values)
  const targetValues = new Set(target.values)

  const addedValues = target.values.filter(v => !sourceValues.has(v))
  const removedValues = source.values.filter(v => !targetValues.has(v))

  if (addedValues.length === 0 && removedValues.length === 0) {
    return null
  }

  return {
    changeType: 'modified',
    enumName: source.name,
    source,
    target,
    addedValues,
    removedValues,
  }
}

// ============================================================================
// Migration SQL Generation
// ============================================================================

/**
 * Generate migration SQL from a schema diff
 *
 * @example
 * ```typescript
 * const diff = diffSchemas(localSchema, prodSchema)
 * const migration = generateMigrationSQL(diff, {
 *   includeComments: true,
 *   safeMode: true
 * })
 * console.log(migration.upSQL)
 * ```
 */
export function generateMigrationSQL(
  diff: SchemaDiff,
  options: MigrationSQLOptions = {}
): GeneratedMigration {
  const up: string[] = []
  const down: string[] = []
  const warnings: string[] = []
  let isReversible = true

  const schema = options.schema ?? diff.targetSchema
  const schemaPrefix = schema && schema !== 'public' ? `"${schema}".` : ''
  const ifExists = options.safeMode ? 'IF EXISTS ' : ''

  // Process enum changes first (types must exist before tables use them)
  for (const enumChange of diff.enumChanges) {
    if (enumChange.changeType === 'added' && enumChange.target) {
      const values = enumChange.target.values.map(v => `'${v}'`).join(', ')
      up.push(`CREATE TYPE ${schemaPrefix}"${enumChange.enumName}" AS ENUM (${values});`)
      down.push(`DROP TYPE ${ifExists}${schemaPrefix}"${enumChange.enumName}";`)
    } else if (enumChange.changeType === 'removed' && enumChange.source) {
      up.push(`DROP TYPE ${ifExists}${schemaPrefix}"${enumChange.enumName}";`)
      const values = enumChange.source.values.map(v => `'${v}'`).join(', ')
      down.push(`CREATE TYPE ${schemaPrefix}"${enumChange.enumName}" AS ENUM (${values});`)
    } else if (enumChange.changeType === 'modified') {
      // PostgreSQL can only add values to enums, not remove
      if (enumChange.addedValues && enumChange.addedValues.length > 0) {
        for (const value of enumChange.addedValues) {
          up.push(`ALTER TYPE ${schemaPrefix}"${enumChange.enumName}" ADD VALUE '${value}';`)
        }
      }
      if (enumChange.removedValues && enumChange.removedValues.length > 0) {
        warnings.push(`Cannot remove values from enum "${enumChange.enumName}": ${enumChange.removedValues.join(', ')}. This requires recreating the type.`)
        isReversible = false
      }
    }
  }

  // Process table changes
  for (const tableChange of diff.tableChanges) {
    if (tableChange.changeType === 'added' && tableChange.target) {
      const createSQL = generateCreateTableSQL(tableChange.target, schemaPrefix, options)
      up.push(createSQL)
      down.push(`DROP TABLE ${ifExists}${schemaPrefix}"${tableChange.tableName}";`)
    } else if (tableChange.changeType === 'removed' && tableChange.source) {
      up.push(`DROP TABLE ${ifExists}${schemaPrefix}"${tableChange.tableName}";`)
      warnings.push(`Dropping table "${tableChange.tableName}" will lose all data.`)
      const createSQL = generateCreateTableSQL(tableChange.source, schemaPrefix, options)
      down.push(createSQL)
    } else if (tableChange.changeType === 'modified') {
      const tableName = `${schemaPrefix}"${tableChange.tableName}"`

      // Process column changes
      if (tableChange.columnChanges) {
        for (const colChange of tableChange.columnChanges) {
          if (colChange.changeType === 'added' && colChange.target) {
            const colDef = generateColumnDefinition(colChange.target)
            up.push(`ALTER TABLE ${tableName} ADD COLUMN ${colDef};`)
            down.push(`ALTER TABLE ${tableName} DROP COLUMN ${ifExists}"${colChange.columnName}";`)
          } else if (colChange.changeType === 'removed' && colChange.source) {
            up.push(`ALTER TABLE ${tableName} DROP COLUMN ${ifExists}"${colChange.columnName}";`)
            warnings.push(`Dropping column "${tableChange.tableName}.${colChange.columnName}" will lose data.`)
            const colDef = generateColumnDefinition(colChange.source)
            down.push(`ALTER TABLE ${tableName} ADD COLUMN ${colDef};`)
          } else if (colChange.changeType === 'modified' && colChange.attributeChanges) {
            const alterStatements = generateColumnAlterStatements(
              tableName,
              colChange.columnName,
              colChange.attributeChanges,
              colChange.source!,
              colChange.target!,
              warnings
            )
            up.push(...alterStatements.up)
            down.push(...alterStatements.down)
          }
        }
      }

      // Process index changes
      if (tableChange.indexChanges) {
        for (const idxChange of tableChange.indexChanges) {
          if (idxChange.changeType === 'added' && idxChange.target) {
            up.push(generateCreateIndexSQL(idxChange.target, tableChange.tableName, schemaPrefix))
            down.push(`DROP INDEX ${ifExists}${schemaPrefix}"${idxChange.indexName}";`)
          } else if (idxChange.changeType === 'removed' && idxChange.source) {
            up.push(`DROP INDEX ${ifExists}${schemaPrefix}"${idxChange.indexName}";`)
            down.push(generateCreateIndexSQL(idxChange.source, tableChange.tableName, schemaPrefix))
          } else if (idxChange.changeType === 'modified' && idxChange.source && idxChange.target) {
            // Drop and recreate
            up.push(`DROP INDEX ${ifExists}${schemaPrefix}"${idxChange.indexName}";`)
            up.push(generateCreateIndexSQL(idxChange.target, tableChange.tableName, schemaPrefix))
            down.push(`DROP INDEX ${ifExists}${schemaPrefix}"${idxChange.indexName}";`)
            down.push(generateCreateIndexSQL(idxChange.source, tableChange.tableName, schemaPrefix))
          }
        }
      }
    }
  }

  // Build complete SQL strings
  const upSQL = buildMigrationSQL(up, 'up', options)
  const downSQL = buildMigrationSQL(down.reverse(), 'down', options)

  // Generate description
  const description = generateMigrationDescription(diff)

  return {
    up,
    down: down.reverse(),
    upSQL,
    downSQL,
    isReversible,
    warnings,
    description,
  }
}

/**
 * Generate CREATE TABLE SQL
 */
function generateCreateTableSQL(
  table: SchemaTableDefinition,
  schemaPrefix: string,
  _options: MigrationSQLOptions
): string {
  const lines: string[] = []
  const tableName = `${schemaPrefix}"${table.name}"`

  lines.push(`CREATE TABLE ${tableName} (`)

  const columnDefs = table.columns.map(col => `  ${generateColumnDefinition(col)}`)

  // Add primary key constraint if composite
  if (table.primaryKey && table.primaryKey.length > 1) {
    columnDefs.push(`  PRIMARY KEY (${table.primaryKey.map(c => `"${c}"`).join(', ')})`)
  }

  lines.push(columnDefs.join(',\n'))
  lines.push(');')

  return lines.join('\n')
}

/**
 * Generate column definition for CREATE TABLE or ADD COLUMN
 */
function generateColumnDefinition(col: SchemaColumnDefinition): string {
  let def = `"${col.name}" ${col.pgType}`

  if (col.maxLength) {
    def = `"${col.name}" ${col.pgType}(${col.maxLength})`
  }

  if (col.isArray) {
    def += '[]'
  }

  if (col.isPrimaryKey && !col.references) {
    def += ' PRIMARY KEY'
  }

  if (!col.nullable && !col.isPrimaryKey) {
    def += ' NOT NULL'
  }

  if (col.isUnique && !col.isPrimaryKey) {
    def += ' UNIQUE'
  }

  if (col.hasDefault && col.defaultValue) {
    def += ` DEFAULT ${col.defaultValue}`
  }

  if (col.references) {
    def += ` REFERENCES "${col.references.table}"("${col.references.column}")`
    if (col.references.onDelete && col.references.onDelete !== 'NO ACTION') {
      def += ` ON DELETE ${col.references.onDelete}`
    }
    if (col.references.onUpdate && col.references.onUpdate !== 'NO ACTION') {
      def += ` ON UPDATE ${col.references.onUpdate}`
    }
  }

  return def
}

/**
 * Generate ALTER statements for column modifications
 */
function generateColumnAlterStatements(
  tableName: string,
  columnName: string,
  changes: ColumnAttributeChange[],
  source: SchemaColumnDefinition,
  target: SchemaColumnDefinition,
  warnings: string[]
): { up: string[]; down: string[] } {
  const up: string[] = []
  const down: string[] = []
  const colRef = `"${columnName}"`

  for (const change of changes) {
    switch (change.attribute) {
      case 'type':
        up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} TYPE ${target.pgType} USING ${colRef}::${target.pgType};`)
        down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} TYPE ${source.pgType} USING ${colRef}::${source.pgType};`)
        warnings.push(`Type change on "${columnName}" from ${source.pgType} to ${target.pgType} may cause data loss.`)
        break

      case 'nullable':
        if (target.nullable) {
          up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} DROP NOT NULL;`)
          down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} SET NOT NULL;`)
        } else {
          up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} SET NOT NULL;`)
          down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} DROP NOT NULL;`)
          warnings.push(`Setting NOT NULL on "${columnName}" may fail if column contains NULL values.`)
        }
        break

      case 'default':
        if (target.defaultValue) {
          up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} SET DEFAULT ${target.defaultValue};`)
        } else {
          up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} DROP DEFAULT;`)
        }
        if (source.defaultValue) {
          down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} SET DEFAULT ${source.defaultValue};`)
        } else {
          down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} DROP DEFAULT;`)
        }
        break

      case 'maxLength':
        up.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} TYPE ${target.pgType}(${target.maxLength});`)
        down.push(`ALTER TABLE ${tableName} ALTER COLUMN ${colRef} TYPE ${source.pgType}${source.maxLength ? `(${source.maxLength})` : ''};`)
        if (target.maxLength && source.maxLength && target.maxLength < source.maxLength) {
          warnings.push(`Reducing max length on "${columnName}" may truncate data.`)
        }
        break

      case 'unique':
        if (target.isUnique) {
          up.push(`ALTER TABLE ${tableName} ADD CONSTRAINT "${tableName.replace(/"/g, '')}_${columnName}_unique" UNIQUE (${colRef});`)
          down.push(`ALTER TABLE ${tableName} DROP CONSTRAINT IF EXISTS "${tableName.replace(/"/g, '')}_${columnName}_unique";`)
        } else {
          up.push(`ALTER TABLE ${tableName} DROP CONSTRAINT IF EXISTS "${tableName.replace(/"/g, '')}_${columnName}_unique";`)
          down.push(`ALTER TABLE ${tableName} ADD CONSTRAINT "${tableName.replace(/"/g, '')}_${columnName}_unique" UNIQUE (${colRef});`)
        }
        break
    }
  }

  return { up, down }
}

/**
 * Generate CREATE INDEX SQL
 */
function generateCreateIndexSQL(
  index: SchemaIndexDefinition,
  tableName: string,
  schemaPrefix: string
): string {
  const unique = index.isUnique ? 'UNIQUE ' : ''
  const using = index.type && index.type !== 'btree' ? `USING ${index.type} ` : ''
  const columns = index.columns.map(c => `"${c}"`).join(', ')
  const where = index.where ? ` WHERE ${index.where}` : ''

  return `CREATE ${unique}INDEX "${index.name}" ON ${schemaPrefix}"${tableName}" ${using}(${columns})${where};`
}

/**
 * Build complete migration SQL with optional comments and transaction
 */
function buildMigrationSQL(
  statements: string[],
  direction: 'up' | 'down',
  options: MigrationSQLOptions
): string {
  const lines: string[] = []

  if (options.includeComments) {
    lines.push(`-- Migration ${direction}`)
    lines.push(`-- Generated at ${new Date().toISOString()}`)
    lines.push('')
  }

  if (options.wrapInTransaction) {
    lines.push('BEGIN;')
    lines.push('')
  }

  lines.push(...statements.map(s => s + (s.endsWith(';') ? '' : ';')))

  if (options.wrapInTransaction) {
    lines.push('')
    lines.push('COMMIT;')
  }

  return lines.join('\n')
}

/**
 * Generate a human-readable migration description
 */
function generateMigrationDescription(diff: SchemaDiff): string {
  const parts: string[] = []

  if (diff.summary.tablesAdded > 0) {
    const tables = diff.tableChanges
      .filter(t => t.changeType === 'added')
      .map(t => t.tableName)
    parts.push(`Add table${tables.length > 1 ? 's' : ''}: ${tables.join(', ')}`)
  }

  if (diff.summary.tablesRemoved > 0) {
    const tables = diff.tableChanges
      .filter(t => t.changeType === 'removed')
      .map(t => t.tableName)
    parts.push(`Remove table${tables.length > 1 ? 's' : ''}: ${tables.join(', ')}`)
  }

  if (diff.summary.columnsAdded > 0) {
    parts.push(`Add ${diff.summary.columnsAdded} column${diff.summary.columnsAdded > 1 ? 's' : ''}`)
  }

  if (diff.summary.columnsRemoved > 0) {
    parts.push(`Remove ${diff.summary.columnsRemoved} column${diff.summary.columnsRemoved > 1 ? 's' : ''}`)
  }

  if (diff.summary.columnsModified > 0) {
    parts.push(`Modify ${diff.summary.columnsModified} column${diff.summary.columnsModified > 1 ? 's' : ''}`)
  }

  if (diff.summary.indexesAdded > 0) {
    parts.push(`Add ${diff.summary.indexesAdded} index${diff.summary.indexesAdded > 1 ? 'es' : ''}`)
  }

  if (diff.summary.indexesRemoved > 0) {
    parts.push(`Remove ${diff.summary.indexesRemoved} index${diff.summary.indexesRemoved > 1 ? 'es' : ''}`)
  }

  if (parts.length === 0) {
    return 'No changes'
  }

  return parts.join('; ')
}

// ============================================================================
// Formatting and Display
// ============================================================================

/**
 * Format schema diff for CLI display
 *
 * @example
 * ```typescript
 * const diff = diffSchemas(localSchema, prodSchema)
 * console.log(formatSchemaDiff(diff))
 * // Output:
 * // + Table: audit_logs (not in target)
 * // ~ Table: users
 * //   + Column: last_login_at
 * //   ~ Column: email: varchar(100) -> varchar(255)
 * // - Table: temp_data (not in source)
 * ```
 */
export function formatSchemaDiff(diff: SchemaDiff, options: { color?: boolean } = {}): string {
  const lines: string[] = []
  const { color = true } = options

  const green = color ? '\x1b[32m' : ''
  const red = color ? '\x1b[31m' : ''
  const yellow = color ? '\x1b[33m' : ''
  const reset = color ? '\x1b[0m' : ''

  if (diff.isIdentical) {
    return `${green}Schemas are identical${reset}`
  }

  // Header
  lines.push(`Schema diff: ${diff.sourceSchema} -> ${diff.targetSchema}`)
  lines.push('')

  // Enum changes
  for (const enumChange of diff.enumChanges) {
    if (enumChange.changeType === 'added') {
      lines.push(`${green}+ Enum: ${enumChange.enumName}${reset}`)
    } else if (enumChange.changeType === 'removed') {
      lines.push(`${red}- Enum: ${enumChange.enumName}${reset}`)
    } else if (enumChange.changeType === 'modified') {
      lines.push(`${yellow}~ Enum: ${enumChange.enumName}${reset}`)
      if (enumChange.addedValues?.length) {
        lines.push(`    ${green}+ Values: ${enumChange.addedValues.join(', ')}${reset}`)
      }
      if (enumChange.removedValues?.length) {
        lines.push(`    ${red}- Values: ${enumChange.removedValues.join(', ')}${reset}`)
      }
    }
  }

  // Table changes
  for (const tableChange of diff.tableChanges) {
    if (tableChange.changeType === 'added') {
      lines.push(`${green}+ Table: ${tableChange.tableName}${reset}`)
      if (tableChange.target) {
        for (const col of tableChange.target.columns) {
          lines.push(`    ${green}+ ${col.name}: ${col.pgType}${col.nullable ? '' : ' NOT NULL'}${reset}`)
        }
      }
    } else if (tableChange.changeType === 'removed') {
      lines.push(`${red}- Table: ${tableChange.tableName}${reset}`)
    } else if (tableChange.changeType === 'modified') {
      lines.push(`${yellow}~ Table: ${tableChange.tableName}${reset}`)

      // Column changes
      if (tableChange.columnChanges) {
        for (const colChange of tableChange.columnChanges) {
          if (colChange.changeType === 'added') {
            const col = colChange.target!
            lines.push(`    ${green}+ Column: ${col.name}: ${col.pgType}${col.nullable ? '' : ' NOT NULL'}${reset}`)
          } else if (colChange.changeType === 'removed') {
            lines.push(`    ${red}- Column: ${colChange.columnName}${reset}`)
          } else if (colChange.changeType === 'modified' && colChange.attributeChanges) {
            for (const attr of colChange.attributeChanges) {
              lines.push(`    ${yellow}~ Column: ${colChange.columnName}.${attr.attribute}: ${formatValue(attr.sourceValue)} -> ${formatValue(attr.targetValue)}${reset}`)
            }
          }
        }
      }

      // Index changes
      if (tableChange.indexChanges) {
        for (const idxChange of tableChange.indexChanges) {
          if (idxChange.changeType === 'added') {
            lines.push(`    ${green}+ Index: ${idxChange.indexName}${reset}`)
          } else if (idxChange.changeType === 'removed') {
            lines.push(`    ${red}- Index: ${idxChange.indexName}${reset}`)
          } else if (idxChange.changeType === 'modified') {
            lines.push(`    ${yellow}~ Index: ${idxChange.indexName}${reset}`)
          }
        }
      }
    }
  }

  // Summary
  lines.push('')
  lines.push('Summary:')
  lines.push(`  Tables:  ${green}+${diff.summary.tablesAdded}${reset} ${red}-${diff.summary.tablesRemoved}${reset} ${yellow}~${diff.summary.tablesModified}${reset}`)
  lines.push(`  Columns: ${green}+${diff.summary.columnsAdded}${reset} ${red}-${diff.summary.columnsRemoved}${reset} ${yellow}~${diff.summary.columnsModified}${reset}`)
  lines.push(`  Indexes: ${green}+${diff.summary.indexesAdded}${reset} ${red}-${diff.summary.indexesRemoved}${reset}`)
  if (diff.enumChanges.length > 0) {
    lines.push(`  Enums:   ${green}+${diff.summary.enumsAdded}${reset} ${red}-${diff.summary.enumsRemoved}${reset} ${yellow}~${diff.summary.enumsModified}${reset}`)
  }

  return lines.join('\n')
}

/**
 * Format schema diff as JSON
 */
export function formatSchemaDiffJSON(diff: SchemaDiff): string {
  return JSON.stringify(diff, null, 2)
}

// ============================================================================
// Drizzle Schema Support
// ============================================================================

/**
 * Generate Drizzle schema migration file from diff
 *
 * @example
 * ```typescript
 * const diff = diffSchemas(localSchema, prodSchema)
 * const drizzleMigration = generateDrizzleMigration(diff, 'sync_to_production')
 * fs.writeFileSync('./migrations/0001_sync_to_production.ts', drizzleMigration)
 * ```
 */
export function generateDrizzleMigration(
  diff: SchemaDiff,
  name: string,
  options: MigrationSQLOptions = {}
): string {
  const migration = generateMigrationSQL(diff, { ...options, includeComments: true })

  const lines: string[] = []

  lines.push(`/**`)
  lines.push(` * Migration: ${name}`)
  lines.push(` * ${migration.description}`)
  lines.push(` * Generated at: ${new Date().toISOString()}`)
  if (migration.warnings.length > 0) {
    lines.push(` *`)
    lines.push(` * Warnings:`)
    for (const warning of migration.warnings) {
      lines.push(` * - ${warning}`)
    }
  }
  lines.push(` */`)
  lines.push(``)
  lines.push(`import { sql } from 'drizzle-orm'`)
  lines.push(`import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js'`)
  lines.push(``)
  lines.push(`export async function up(db: PostgresJsDatabase) {`)
  for (const stmt of migration.up) {
    lines.push(`  await db.execute(sql\`${escapeTemplateString(stmt)}\`)`)
  }
  lines.push(`}`)
  lines.push(``)
  lines.push(`export async function down(db: PostgresJsDatabase) {`)
  for (const stmt of migration.down) {
    lines.push(`  await db.execute(sql\`${escapeTemplateString(stmt)}\`)`)
  }
  lines.push(`}`)
  lines.push(``)

  return lines.join('\n')
}

// ============================================================================
// Helper Functions
// ============================================================================

/**
 * Filter table by include/exclude options
 */
function filterTable(name: string, options: SchemaDiffOptions): boolean {
  if (options.includeTables && options.includeTables.length > 0) {
    return options.includeTables.includes(name)
  }
  if (options.excludeTables && options.excludeTables.includes(name)) {
    return false
  }
  return true
}

/**
 * Normalize identifier for comparison
 */
function normalizeIdentifier(name: string, options: SchemaDiffOptions): string {
  return options.ignoreCase ? name.toLowerCase() : name
}

/**
 * Normalize PostgreSQL type for comparison
 */
function normalizeType(type: string): string {
  // Handle common type aliases
  const normalized = type.toLowerCase()
    .replace(/^int$/, 'integer')
    .replace(/^int4$/, 'integer')
    .replace(/^int8$/, 'bigint')
    .replace(/^float4$/, 'real')
    .replace(/^float8$/, 'double precision')
    .replace(/^bool$/, 'boolean')
    .replace(/^timestamptz$/, 'timestamp with time zone')
    .replace(/^timetz$/, 'time with time zone')

  return normalized
}

/**
 * Normalize default value for comparison
 */
function normalizeDefault(value: string | undefined, options: SchemaDiffOptions): string | undefined {
  if (!value) return undefined
  if (options.ignoreDefaultWhitespace) {
    return value.replace(/\s+/g, ' ').trim()
  }
  return value
}

/**
 * Check if two indexes are equal
 */
function indexesEqual(a: SchemaIndexDefinition, b: SchemaIndexDefinition): boolean {
  return (
    a.isUnique === b.isUnique &&
    a.type === b.type &&
    arraysEqual(a.columns, b.columns) &&
    a.where === b.where
  )
}

/**
 * Check if two foreign key references are equal
 */
function referencesEqual(
  a: SchemaColumnDefinition['references'],
  b: SchemaColumnDefinition['references']
): boolean {
  if (!a && !b) return true
  if (!a || !b) return false
  return (
    a.table === b.table &&
    a.column === b.column &&
    (a.onDelete ?? 'NO ACTION') === (b.onDelete ?? 'NO ACTION') &&
    (a.onUpdate ?? 'NO ACTION') === (b.onUpdate ?? 'NO ACTION')
  )
}

/**
 * Check if two arrays are equal
 */
function arraysEqual<T>(a: T[], b: T[]): boolean {
  if (a.length !== b.length) return false
  return a.every((v, i) => v === b[i])
}

/**
 * Format a value for display
 */
function formatValue(value: unknown): string {
  if (value === undefined) return 'undefined'
  if (value === null) return 'null'
  if (typeof value === 'object') return JSON.stringify(value)
  return String(value)
}

/**
 * Escape backticks in SQL for template strings
 */
function escapeTemplateString(sql: string): string {
  return sql.replace(/`/g, '\\`').replace(/\$/g, '\\$')
}