/**
 * Migrate Create Command
 *
 * Create a new migration file.
 *
 * @module cli/commands/migrate-create
 */

import { resolve, join } from 'node:path'
import { existsSync, mkdirSync, writeFileSync, readdirSync } from 'node:fs'
import type { MigrateCLIOptions } from '../types.js'
import { formatSuccess, formatError, formatInfo } from '../formatting.js'

/**
 * Migration file template
 */
function getMigrationTemplate(options: {
  id: string
  name: string
  version: number
  tableName?: string
}): string {
  const { id, name, version, tableName } = options

  // Generate sample SQL based on name
  let upSql = '-- Add your migration SQL here'
  let downSql = '-- Add your rollback SQL here'

  // Try to infer table operations from name
  const nameLower = name.toLowerCase()
  if (nameLower.includes('create') && tableName) {
    upSql = `CREATE TABLE IF NOT EXISTS ${tableName} (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Add your columns here`
    downSql = `DROP TABLE IF EXISTS ${tableName};`
  } else if (nameLower.includes('add') && tableName) {
    upSql = `ALTER TABLE ${tableName}
  ADD COLUMN column_name TEXT;`
    downSql = `ALTER TABLE ${tableName}
  DROP COLUMN column_name;`
  } else if (nameLower.includes('index') && tableName) {
    upSql = `CREATE INDEX IF NOT EXISTS idx_${tableName}_column_name ON ${tableName} (column_name);`
    downSql = `DROP INDEX IF EXISTS idx_${tableName}_column_name;`
  }

  return `/**
 * Migration: ${name}
 * Version: ${version}
 * ID: ${id}
 *
 * @module migrations/${id}
 */

import type { Migration } from '@dotdo/postgres/migrations'

export const migration: Migration = {
  id: '${id}',
  name: '${name}',
  version: ${version},
  up: \`
    ${upSql}
  \`,
  down: \`
    ${downSql}
  \`,
  // Set to false if this migration cannot be safely rolled back
  isReversible: true,
  // Tags for categorization (optional)
  tags: ['schema'],
  // Set to false for operations that can't run in a transaction
  // transactional: true,
}

export default migration
`
}

/**
 * SQL-only migration file template
 */
function getSqlMigrationTemplate(options: {
  id: string
  name: string
  version: number
  tableName?: string
}): { up: string; down: string } {
  const { name, tableName } = options

  let upSql = '-- Add your migration SQL here\n'
  let downSql = '-- Add your rollback SQL here\n'

  const nameLower = name.toLowerCase()
  if (nameLower.includes('create') && tableName) {
    upSql = `-- Create ${tableName} table
CREATE TABLE IF NOT EXISTS ${tableName} (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Add your columns here
`
    downSql = `-- Drop ${tableName} table
DROP TABLE IF EXISTS ${tableName};
`
  } else if (nameLower.includes('add') && tableName) {
    upSql = `-- Add column to ${tableName}
ALTER TABLE ${tableName}
  ADD COLUMN column_name TEXT;
`
    downSql = `-- Remove column from ${tableName}
ALTER TABLE ${tableName}
  DROP COLUMN column_name;
`
  } else if (nameLower.includes('index') && tableName) {
    upSql = `-- Create index on ${tableName}
CREATE INDEX IF NOT EXISTS idx_${tableName}_column_name ON ${tableName} (column_name);
`
    downSql = `-- Drop index on ${tableName}
DROP INDEX IF EXISTS idx_${tableName}_column_name;
`
  }

  return { up: upSql, down: downSql }
}

/**
 * Get the next version number by scanning existing migrations
 */
function getNextVersion(migrationsDir: string): number {
  if (!existsSync(migrationsDir)) {
    return 1
  }

  const files = readdirSync(migrationsDir)
  let maxVersion = 0

  for (const file of files) {
    // Match patterns like:
    // - 0001_create_users.ts
    // - 0001_create_users/up.sql
    // - 20240115_create_users.ts
    const match = file.match(/^(\d+)/)
    if (match && match[1]) {
      const version = parseInt(match[1], 10)
      if (version > maxVersion) {
        maxVersion = version
      }
    }
  }

  return maxVersion + 1
}

/**
 * Convert name to snake_case
 */
function toSnakeCase(str: string): string {
  return str
    .replace(/([a-z])([A-Z])/g, '$1_$2')
    .replace(/[^a-zA-Z0-9]+/g, '_')
    .replace(/^_|_$/g, '')
    .toLowerCase()
}

/**
 * Extract table name from migration name
 */
function extractTableName(name: string): string | undefined {
  const snakeName = toSnakeCase(name)

  // Common patterns:
  // - create_users -> users
  // - add_email_to_users -> users
  // - create_user_profiles -> user_profiles
  const patterns = [
    /^create_(.+)$/,
    /^add_\w+_to_(\w+)$/,
    /^remove_\w+_from_(\w+)$/,
    /^alter_(\w+)$/,
    /^update_(\w+)$/,
    /^drop_(\w+)$/,
    /^index_(\w+)$/,
    /^add_index_to_(\w+)$/,
  ]

  for (const pattern of patterns) {
    const match = snakeName.match(pattern)
    if (match && match[1]) {
      return match[1]
    }
  }

  return undefined
}

/**
 * Create migration command
 */
export async function runMigrateCreate(options: MigrateCLIOptions): Promise<void> {
  if (!options.name) {
    console.error(formatError('Migration name is required'))
    console.error(formatInfo('Usage: npx postgres.do migrate:create <name>'))
    console.error(formatInfo('Example: npx postgres.do migrate:create create_users'))
    process.exit(1)
  }

  const migrationsDir = resolve(process.cwd(), options.migrationsDir || './migrations')

  // Ensure migrations directory exists
  if (!existsSync(migrationsDir)) {
    mkdirSync(migrationsDir, { recursive: true })
    console.log(formatInfo(`Created migrations directory: ${migrationsDir}`))
  }

  // Get next version number
  const version = getNextVersion(migrationsDir)
  const paddedVersion = version.toString().padStart(4, '0')

  // Generate migration ID
  const snakeName = toSnakeCase(options.name)
  const id = `${paddedVersion}_${snakeName}`

  // Extract table name if possible
  const tableName = extractTableName(options.name)

  // Determine output format
  const format = options.migrationFormat || 'ts'

  if (format === 'sql') {
    // Create SQL migration with up.sql and down.sql files
    const migrationDir = join(migrationsDir, id)
    mkdirSync(migrationDir, { recursive: true })

    const { up, down } = getSqlMigrationTemplate({
      id,
      name: options.name,
      version,
      ...(tableName != null ? { tableName } : {}),
    })

    const upPath = join(migrationDir, 'up.sql')
    const downPath = join(migrationDir, 'down.sql')

    writeFileSync(upPath, up)
    writeFileSync(downPath, down)

    if (options.json) {
      console.log(JSON.stringify({
        id,
        name: options.name,
        version,
        directory: migrationDir,
        files: ['up.sql', 'down.sql'],
      }, null, 2))
    } else {
      console.log('')
      console.log(formatSuccess(`Created migration: ${id}`))
      console.log(formatInfo(`Directory: ${migrationDir}`))
      console.log(formatInfo('Files:'))
      console.log(`  - ${upPath}`)
      console.log(`  - ${downPath}`)
      console.log('')
      console.log(formatInfo('Edit the SQL files to add your migration logic'))
    }
  } else {
    // Create TypeScript migration file
    const filename = `${id}.ts`
    const filePath = join(migrationsDir, filename)

    // Check if file already exists
    if (existsSync(filePath)) {
      console.error(formatError(`Migration file already exists: ${filePath}`))
      process.exit(1)
    }

    const content = getMigrationTemplate({
      id,
      name: options.name,
      version,
      ...(tableName != null ? { tableName } : {}),
    })

    writeFileSync(filePath, content)

    if (options.json) {
      console.log(JSON.stringify({
        id,
        name: options.name,
        version,
        file: filePath,
      }, null, 2))
    } else {
      console.log('')
      console.log(formatSuccess(`Created migration: ${id}`))
      console.log(formatInfo(`File: ${filePath}`))
      console.log('')
      console.log(formatInfo('Edit the file to add your migration SQL'))
    }
  }
}