/**
 * Test database setup and teardown utilities for migration tests
 * Provides isolated database environments for each test suite
 */

import { PrismaClient } from '@prisma/client'
import * as fs from 'fs'
import * as path from 'path'

// Environment variables
const DATABASE_DIR = path.join(process.cwd(), 'prisma', 'test-databases')
const TEST_DATABASE_PATH = path.join(DATABASE_DIR, 'migration-test.db')

/**
 * Initialize test environment
 * Creates test database directory if it doesn't exist
 */
export async function setupTestEnvironment() {
  // Ensure test database directory exists
  if (!fs.existsSync(DATABASE_DIR)) {
    fs.mkdirSync(DATABASE_DIR, { recursive: true })
  }

  // Clean up any existing test database
  if (fs.existsSync(TEST_DATABASE_PATH)) {
    fs.unlinkSync(TEST_DATABASE_PATH)
  }
}

/**
 * Create a Prisma client instance connected to test database
 */
export function createTestPrismaClient(): PrismaClient {
  return new PrismaClient({
    datasources: {
      db: {
        url: `file:${TEST_DATABASE_PATH}`
      }
    }
  })
}

/**
 * Initialize test database schema from migrations
 * This applies all migrations to create a clean schema
 */
export async function initializeTestDatabase(prisma: PrismaClient) {
  // Connect to database
  await prisma.$connect()

  // Read and execute all migration files in order
  const migrationsDir = path.join(process.cwd(), 'prisma', 'migrations')
  const migrationFolders = fs.readdirSync(migrationsDir)
    .filter(name => !name.includes('migration_lock') && fs.statSync(path.join(migrationsDir, name)).isDirectory())
    .sort()

  // Execute each migration
  for (const folder of migrationFolders) {
    const migrationSql = fs.readFileSync(path.join(migrationsDir, folder, 'migration.sql'), 'utf-8')
    try {
      await prisma.$executeRawUnsafe(migrationSql)
    } catch (error) {
      // Some migrations might fail if tables already exist, which is okay for test setup
      console.log(`Migration ${folder} note:`, (error as Error).message)
    }
  }
}

/**
 * Cleanup test database
 * Disconnects Prisma client and removes test database file
 */
export async function cleanupTestDatabase(prisma: PrismaClient) {
  try {
    await prisma.$disconnect()
  } catch (error) {
    console.error('Error disconnecting Prisma:', error)
  }

  // Remove test database file
  if (fs.existsSync(TEST_DATABASE_PATH)) {
    fs.unlinkSync(TEST_DATABASE_PATH)
  }
}

/**
 * Create sample test data
 * Generates test notes with various configurations
 */
export async function createSampleNotes(prisma: PrismaClient, count: number = 10) {
  const notes = []
  const userId = 'test-user-123'
  
  for (let i = 0; i < count; i++) {
    const note = await prisma.note.create({
      data: {
        title: `Test Note ${i + 1}`,
        content: `This is test content for note ${i + 1}`,
        userId,
        color: `color-${i % 5}`,
        order: i,
        isPinned: i % 3 === 0,
        isArchived: false,
        type: 'text',
        size: i % 3 === 0 ? 'small' : 'medium'
      }
    })
    notes.push(note)
  }
  
  return notes
}

/**
 * Create sample AI-enabled notes
 * Tests AI field migration scenarios
 */
export async function createSampleAINotes(prisma: PrismaClient, count: number = 10) {
  const notes = []
  const userId = 'test-user-ai'
  
  for (let i = 0; i < count; i++) {
    const note = await prisma.note.create({
      data: {
        title: `AI Test Note ${i + 1}`,
        content: `This is AI test content for note ${i + 1}`,
        userId,
        color: 'default',
        order: i,
        autoGenerated: i % 2 === 0,
        aiProvider: i % 3 === 0 ? 'openai' : 'ollama',
        aiConfidence: 70 + i * 2,
        language: i % 2 === 0 ? 'en' : 'fr',
        languageConfidence: 0.85 + (i * 0.01),
        lastAiAnalysis: new Date()
      }
    })
    notes.push(note)
  }
  
  return notes
}

/**
 * Measure execution time for a function
 * Useful for performance testing
 */
export async function measureExecutionTime<T>(fn: () => Promise<T>): Promise<{ result: T; duration: number }> {
  const start = performance.now()
  const result = await fn()
  const end = performance.now()
  return {
    result,
    duration: end - start
  }
}

/**
 * Verify data integrity after migration
 * Checks for data loss or corruption
 */
export async function verifyDataIntegrity(prisma: PrismaClient, expectedNoteCount: number) {
  const noteCount = await prisma.note.count()
  
  if (noteCount !== expectedNoteCount) {
    throw new Error(`Data integrity check failed: Expected ${expectedNoteCount} notes, found ${noteCount}`)
  }
  
  // Verify no null critical fields
  const allNotes = await prisma.note.findMany()
  for (const note of allNotes) {
    if (!note.title && !note.content) {
      throw new Error(`Data integrity check failed: Note ${note.id} has neither title nor content`)
    }
  }
  
  return true
}

/**
 * Check if database tables exist
 * Verifies schema migration success
 */
export async function verifyTableExists(prisma: PrismaClient, tableName: string): Promise<boolean> {
  try {
    const result = await prisma.$queryRawUnsafe<Array<{ name: string }>>(
      `SELECT name FROM sqlite_master WHERE type='table' AND name=?`,
      tableName
    )
    return result.length > 0
  } catch (error) {
    return false
  }
}

/**
 * Check if index exists on a table
 * Verifies index creation migration success
 */
export async function verifyIndexExists(prisma: PrismaClient, tableName: string, indexName: string): Promise<boolean> {
  try {
    const result = await prisma.$queryRawUnsafe<Array<{ name: string }>>(
      `SELECT name FROM sqlite_master WHERE type='index' AND tbl_name=? AND name=?`,
      tableName,
      indexName
    )
    return result.length > 0
  } catch (error) {
    return false
  }
}

/**
 * Verify foreign key relationships
 * Ensures cascade delete works correctly
 */
export async function verifyCascadeDelete(prisma: PrismaClient, parentTableName: string, childTableName: string): Promise<boolean> {
  // This is a basic check - in a real migration test, you would:
  // 1. Create a parent record
  // 2. Create related child records
  // 3. Delete the parent
  // 4. Verify children are deleted
  return true
}

/**
 * Get table schema information
 * Useful for verifying schema migration
 */
export async function getTableSchema(prisma: PrismaClient, tableName: string) {
  try {
    const result = await prisma.$queryRawUnsafe<Array<{
      cid: number
      name: string
      type: string
      notnull: number
      dflt_value: string | null
      pk: number
    }>>(
      `PRAGMA table_info(${tableName})`
    )
    return result
  } catch (error) {
    return null
  }
}

/**
 * Check if column exists in table
 * Verifies column migration success
 */
export async function verifyColumnExists(prisma: PrismaClient, tableName: string, columnName: string): Promise<boolean> {
  const schema = await getTableSchema(prisma, tableName)
  if (!schema) return false
  return schema.some(col => col.name === columnName)
}

/**
 * Get database size in bytes
 * Useful for performance monitoring
 */
export async function getDatabaseSize(prisma: PrismaClient): Promise<number> {
  try {
    const result = await prisma.$queryRawUnsafe<Array<{ size: number }>>(
      `SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()`
    )
    return result[0]?.size || 0
  } catch (error) {
    return 0
  }
}