app/api/test-db/route.ts

import { NextResponse } from "next/server"
import { PrismaClient } from '@prisma/client'
import { Logger } from "@/lib/logger"

// Create a new Prisma client instance
const prisma = new PrismaClient()

export async function GET() {
  try {
    Logger.info("Starting comprehensive database test...")
    
    // 1. Test Database Connection
    Logger.info("Testing database connection...")
    await prisma.$connect()
    Logger.info("Database connection successful")

    // 2. Test Schema
    Logger.info("Testing database schema...")
    const tables = await prisma.$queryRaw`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public'
    `
    Logger.info("Available tables:", tables)

    // 3. Test Resume Table Structure
    Logger.info("Testing Resume table structure...")
    const resumeColumns = await prisma.$queryRaw`
      SELECT column_name, data_type, column_default
      FROM information_schema.columns
      WHERE table_name = 'Resume'
      ORDER BY ordinal_position
    `
    Logger.info("Resume table columns:", resumeColumns)

    // 4. Test Basic CRUD Operations
    Logger.info("Testing CRUD operations...")
    
    // Create
    const testId = `test-${Date.now()}`
    const testResume = await prisma.resume.create({
      data: {
        id: testId,
        originalName: "test-file.pdf",
        filePath: "/test/path/test-file.pdf",
        status: "Test",
        skills: ["test"],
        matchScore: 0,
        experienceMatch: 0,
        educationMatch: 0
      }
    })
    Logger.info("Created test resume:", testId)

    // Read
    const foundResume = await prisma.resume.findUnique({
      where: { id: testId }
    })
    Logger.info("Found resume:", foundResume)

    // Update
    const updatedResume = await prisma.resume.update({
      where: { id: testId },
      data: { status: "Updated" }
    })
    Logger.info("Updated resume:", updatedResume)

    // Delete
    await prisma.resume.delete({
      where: { id: testId }
    })
    Logger.info("Deleted test resume:", testId)

    // 5. Test Complex Queries
    Logger.info("Testing complex queries...")
    const allResumes = await prisma.resume.findMany({
      where: {
        status: "Test"
      },
      orderBy: {
        uploadedAt: 'desc'
      }
    })
    Logger.info(`Found ${allResumes.length} test resumes`)

    return NextResponse.json({
      success: true,
      message: "All database tests passed successfully",
      testResults: {
        connection: "Successful",
        schema: tables,
        resumeTableStructure: resumeColumns,
        crudOperations: {
          create: "Successful",
          read: "Successful",
          update: "Successful",
          delete: "Successful"
        },
        complexQueries: "Successful"
      }
    })
  } catch (error) {
    Logger.error("Error during database tests:", error)
    
    let errorDetails: string | object = "Unknown error"
    if (error instanceof Error) {
      errorDetails = {
        name: error.name,
        message: error.message,
        stack: error.stack
      }
    }
    
    return NextResponse.json({
      success: false,
      error: errorDetails
    }, { status: 500 })
  } finally {
    await prisma.$disconnect()
  }
}