import { NextResponse } from "next/server";
import pool from "../../../../lib/db/db"

export async function GET(request) {
  let connection;
  
  try {
    connection = await pool.getConnection();

    const { searchParams } = new URL(request.url);
    const page = Number.parseInt(searchParams.get("page") || "1", 10) || 1;
    const limit = Number.parseInt(searchParams.get("limit") || "15", 10) || 15;
    const search = (searchParams.get("search") || "").trim();
    const country = searchParams.get("country") || "";
    const qualification = searchParams.get("qualification") || "";
    const ielts = searchParams.get("ielts") || "";
    const status = searchParams.get("status") || "";
    const amount_min = searchParams.get("amount_min") || "";
    const amount_max = searchParams.get("amount_max") || "";
    const showInMenu = searchParams.get("showInMenu") || ""; // NEW: Menu visibility filter
    const skip = (page - 1) * limit;

    // Build WHERE clause for search and filters
    let whereSql = "";
    const whereParams = [];
    
    const conditions = [];
    
    if (search) {
      conditions.push("(s.title LIKE ? OR c.country LIKE ? OR p.title LIKE ? OR s.slug LIKE ?)");
      whereParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }
    
    if (country) {
      conditions.push("s.country_id = ?");
      whereParams.push(country);
    }
    
    if (qualification) {
      conditions.push("sq.qualification_id = ?");
      whereParams.push(qualification);
    }
    
    if (ielts) {
      conditions.push("s.ielts = ?");
      whereParams.push(ielts);
    }
    
    if (status) {
      if (status === 'active') {
        conditions.push("s.is_active = 1");
      } else if (status === 'inactive') {
        conditions.push("s.is_active = 0");
      }
    }

    // NEW: Menu visibility filter
    if (showInMenu) {
      conditions.push("s.show_in_menu = ?");
      whereParams.push(showInMenu === '1' ? 1 : 0);
    }

    // Amount range filters
    if (amount_min) {
      conditions.push("(s.amount_max >= ? OR s.amount_max IS NULL)");
      whereParams.push(parseFloat(amount_min));
    }
    
    if (amount_max) {
      conditions.push("(s.amount_min <= ? OR s.amount_min IS NULL)");
      whereParams.push(parseFloat(amount_max));
    }
    
    if (conditions.length > 0) {
      whereSql = `WHERE ${conditions.join(" AND ")}`;
    }

    // Count query - updated to handle junction table
    const countQuery = `
      SELECT COUNT(DISTINCT s.id) AS totalCount 
      FROM scholarships s
      LEFT JOIN countries c ON s.country_id = c.id
      LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
      LEFT JOIN posts p ON sq.qualification_id = p.id
      ${whereSql}
    `;
    
    const [countRows] = await connection.query(countQuery, whereParams);
    const totalCount = countRows[0]?.totalCount ?? 0;

    // Data query with joins - including amount range fields and show_in_menu
    const dataQuery = `
      SELECT DISTINCT
        s.id,
        s.title,
        s.slug,
        s.country_id,
        s.image,
        s.deadline,
        s.cgpa,
        s.percentage_cover,
        s.amount_min,
        s.amount_max,
        s.ielts,
        s.is_active,
        s.show_in_menu,
        s.created_at,
        s.updated_at,
        c.country as country_name,
        c.code as country_code,
        c.currency_symbol as currency_symbol
      FROM scholarships s
      LEFT JOIN countries c ON s.country_id = c.id
      LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
      LEFT JOIN posts p ON sq.qualification_id = p.id
      ${whereSql}
      ORDER BY s.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const dataParams = [...whereParams, limit, skip];
    const [scholarships] = await connection.query(dataQuery, dataParams);

    // Fetch qualifications for each scholarship
    const scholarshipsWithQualifications = await Promise.all(
      scholarships.map(async (scholarship) => {
        const [qualifications] = await connection.query(
          `SELECT 
            sq.qualification_id,
            p.title as qualification_name
           FROM scholarship_qualifications sq
           LEFT JOIN posts p ON sq.qualification_id = p.id
           WHERE sq.scholarship_id = ?
           ORDER BY p.title`,
          [scholarship.id]
        );
        
        return {
          ...scholarship,
          qualifications: qualifications || []
        };
      })
    );

    const totalPages = Math.ceil(totalCount / limit);

    return NextResponse.json({
      success: true,
      data: scholarshipsWithQualifications,
      meta: {
        totalItems: totalCount,
        totalPages,
        currentPage: page,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      },
    });
  } catch (error) {
    console.error("GET /scholarships error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch scholarships", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function POST(req) {
  let connection;

  try {
    connection = await pool.getConnection();
    const body = await req.json();
    
    // Required field validation
    if (!body.title || !body.title.trim()) {
      return NextResponse.json(
        { success: false, error: "Scholarship title is required" },
        { status: 400 }
      );
    }

    if (!body.slug || !body.slug.trim()) {
      return NextResponse.json(
        { success: false, error: "Scholarship slug is required" },
        { status: 400 }
      );
    }

    if (!body.deadline) {
      return NextResponse.json(
        { success: false, error: "Scholarship deadline is required" },
        { status: 400 }
      );
    }

    // Validate deadline is not in the past
    const deadlineDate = new Date(body.deadline);
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    
    if (deadlineDate < today) {
      return NextResponse.json(
        { success: false, error: "Deadline cannot be in the past" },
        { status: 400 }
      );
    }

    // Validate slug format
    const slugRegex = /^[a-z0-9]+(?:-[a-z0-9]+)*$/;
    if (!slugRegex.test(body.slug)) {
      return NextResponse.json(
        { success: false, error: "Slug can only contain lowercase letters, numbers, and hyphens" },
        { status: 400 }
      );
    }

    // Check if slug already exists
    const [existingSlug] = await connection.query(
      'SELECT id FROM scholarships WHERE slug = ?',
      [body.slug]
    );

    if (existingSlug.length > 0) {
      return NextResponse.json(
        { success: false, error: "Slug already exists. Please use a different slug." },
        { status: 400 }
      );
    }

    // Validate CGPA if provided
    if (body.cgpa !== undefined && body.cgpa !== null && body.cgpa !== '') {
      const cgpa = parseFloat(body.cgpa);
      if (isNaN(cgpa) || cgpa < 0 || cgpa > 4) {
        return NextResponse.json(
          { success: false, error: "CGPA must be a number between 0 and 4" },
          { status: 400 }
        );
      }
    }

    // Validate percentage cover if provided
    if (body.percentage_cover !== undefined && body.percentage_cover !== null && body.percentage_cover !== '') {
      const percentage = parseFloat(body.percentage_cover);
      if (isNaN(percentage) || percentage < 0 || percentage > 100) {
        return NextResponse.json(
          { success: false, error: "Percentage cover must be between 0 and 100" },
          { status: 400 }
        );
      }
    }

    // Validate amount range if provided
    if (body.amount_min !== undefined && body.amount_min !== null && body.amount_min !== '' &&
        body.amount_max !== undefined && body.amount_max !== null && body.amount_max !== '') {
      const min = parseFloat(body.amount_min);
      const max = parseFloat(body.amount_max);
      
      if (isNaN(min) || min < 0) {
        return NextResponse.json(
          { success: false, error: "Minimum amount must be a positive number" },
          { status: 400 }
        );
      }
      
      if (isNaN(max) || max < 0) {
        return NextResponse.json(
          { success: false, error: "Maximum amount must be a positive number" },
          { status: 400 }
        );
      }
      
      if (min > max) {
        return NextResponse.json(
          { success: false, error: "Minimum amount cannot be greater than maximum amount" },
          { status: 400 }
        );
      }
    }

    // Validate IELTS value if provided
    if (body.ielts !== undefined && body.ielts !== null && body.ielts !== '') {
      if (body.ielts !== 0 && body.ielts !== 1 && body.ielts !== "0" && body.ielts !== "1") {
        return NextResponse.json(
          { success: false, error: "IELTS value must be either 0 (No) or 1 (Yes)" },
          { status: 400 }
        );
      }
    }

    // Validate country_id exists if provided
    if (body.country_id && body.country_id !== '') {
      const [countryExists] = await connection.query(
        'SELECT id FROM countries WHERE id = ?',
        [body.country_id]
      );
      
      if (countryExists.length === 0) {
        return NextResponse.json(
          { success: false, error: "Selected country does not exist" },
          { status: 400 }
        );
      }
    }

    // Validate qualifications if provided
    if (body.qualification_ids && Array.isArray(body.qualification_ids)) {
      if (body.qualification_ids.length === 0) {
        return NextResponse.json(
          { success: false, error: "At least one qualification must be selected" },
          { status: 400 }
        );
      }

      // Check if all qualification IDs exist
      const placeholders = body.qualification_ids.map(() => '?').join(',');
      const [qualificationsExist] = await connection.query(
        `SELECT id FROM posts WHERE id IN (${placeholders})`,
        body.qualification_ids
      );
      
      if (qualificationsExist.length !== body.qualification_ids.length) {
        return NextResponse.json(
          { success: false, error: "One or more selected qualifications do not exist" },
          { status: 400 }
        );
      }
    } else {
      return NextResponse.json(
        { success: false, error: "Qualifications are required" },
        { status: 400 }
      );
    }

    // Validate FAQs if provided
    if (body.faqs && Array.isArray(body.faqs)) {
      if (body.faqs.length === 0) {
        return NextResponse.json(
          { success: false, error: "At least one FAQ is required" },
          { status: 400 }
        );
      }

      const hasInvalidFaqs = body.faqs.some((faq, index) => {
        if (!faq.question || !faq.question.trim()) {
          return true;
        }
        if (!faq.answer || !faq.answer.trim()) {
          return true;
        }
        return false;
      });

      if (hasInvalidFaqs) {
        return NextResponse.json(
          { success: false, error: "All FAQs must have both question and answer" },
          { status: 400 }
        );
      }

      // Check for duplicate questions
      const questions = body.faqs.map(faq => faq.question.toLowerCase().trim());
      const uniqueQuestions = new Set(questions);
      if (uniqueQuestions.size !== questions.length) {
        return NextResponse.json(
          { success: false, error: "FAQ questions must be unique" },
          { status: 400 }
        );
      }
    } else {
      return NextResponse.json(
        { success: false, error: "FAQs are required" },
        { status: 400 }
      );
    }

    // Start transaction
    await connection.beginTransaction();

    try {
      // Insert scholarship with amount range and show_in_menu
      const [result] = await connection.query(
        `INSERT INTO scholarships (
          title, slug, country_id, image, deadline, 
          description, cgpa, percentage_cover, amount_min, amount_max, ielts, 
          is_active, show_in_menu, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          body.title.trim(),
          body.slug.trim(),
          body.country_id || null,
          body.image || null,
          body.deadline,
          body.description || null,
          body.cgpa !== undefined && body.cgpa !== null && body.cgpa !== '' ? parseFloat(body.cgpa) : null,
          body.percentage_cover !== undefined && body.percentage_cover !== null && body.percentage_cover !== '' ? parseFloat(body.percentage_cover) : null,
          body.amount_min !== undefined && body.amount_min !== null && body.amount_min !== '' ? parseFloat(body.amount_min) : null,
          body.amount_max !== undefined && body.amount_max !== null && body.amount_max !== '' ? parseFloat(body.amount_max) : null,
          body.ielts !== undefined && body.ielts !== null && body.ielts !== '' ? parseInt(body.ielts) : null,
          body.is_active ? 1 : 0,
          body.show_in_menu ? 1 : 0, // NEW: Add menu visibility
          new Date(),
          new Date(),
        ]
      );

      const scholarshipId = result.insertId;

      // Insert qualifications (multiple)
      if (body.qualification_ids && Array.isArray(body.qualification_ids)) {
        const qualificationValues = body.qualification_ids.map(qualId => [scholarshipId, parseInt(qualId)]);
        if (qualificationValues.length > 0) {
          await connection.query(
            `INSERT INTO scholarship_qualifications (scholarship_id, qualification_id) VALUES ?`,
            [qualificationValues]
          );
        }
      }

      // Insert FAQs
      if (body.faqs && Array.isArray(body.faqs)) {
        const faqValues = body.faqs.map(faq => [
          scholarshipId,
          faq.question.trim(),
          faq.answer.trim(),
          new Date(),
          new Date()
        ]);
        if (faqValues.length > 0) {
          await connection.query(
            `INSERT INTO scholarship_faqs (scholarship_id, question, answer, created_at, updated_at) VALUES ?`,
            [faqValues]
          );
        }
      }

      // Commit transaction
      await connection.commit();

      // Fetch the created scholarship with joins
      const [rows] = await connection.query(
        `SELECT 
          s.*,
          c.country as country_name,
          c.code as country_code
         FROM scholarships s
         LEFT JOIN countries c ON s.country_id = c.id
         WHERE s.id = ?`,
        [scholarshipId]
      );

      // Fetch qualifications for response
      const [qualRows] = await connection.query(
        `SELECT 
          sq.qualification_id,
          p.title as qualification_name
         FROM scholarship_qualifications sq
         LEFT JOIN posts p ON sq.qualification_id = p.id
         WHERE sq.scholarship_id = ?`,
        [scholarshipId]
      );

      // Fetch FAQs for response
      const [faqRows] = await connection.query(
        `SELECT 
          id, question, answer
         FROM scholarship_faqs
         WHERE scholarship_id = ?
         ORDER BY id`,
        [scholarshipId]
      );

      const scholarshipData = {
        ...rows[0],
        qualifications: qualRows,
        faqs: faqRows
      };

      return NextResponse.json({ 
        success: true, 
        data: scholarshipData,
        message: 'Scholarship created successfully'
      }, { status: 201 });

    } catch (dbError) {
      // Rollback transaction in case of database error
      await connection.rollback();
      throw dbError;
    }

  } catch (error) {
    console.error("Error creating scholarship:", error);
    
    // Handle specific database errors
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { success: false, error: "A scholarship with this slug already exists" },
        { status: 400 }
      );
    }
    
    if (error.code === 'ER_NO_REFERENCED_ROW' || error.code === 'ER_NO_REFERENCED_ROW_2') {
      return NextResponse.json(
        { success: false, error: "Referenced country or qualification does not exist" },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to create scholarship",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function PATCH(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Scholarship ID is required" },
        { status: 400 }
      );
    }

    // Fetch single scholarship with joins including qualifications and FAQs
    const [scholarships] = await connection.query(
      `SELECT 
        s.*,
        c.country as country_name,
        c.code as country_code
       FROM scholarships s
       LEFT JOIN countries c ON s.country_id = c.id
       WHERE s.id = ?`,
      [id]
    );

    if (scholarships.length === 0) {
      return NextResponse.json(
        { success: false, error: "Scholarship not found" },
        { status: 404 }
      );
    }

    // Fetch qualifications
    const [qualifications] = await connection.query(
      `SELECT 
        sq.qualification_id,
        p.title as qualification_name
       FROM scholarship_qualifications sq
       LEFT JOIN posts p ON sq.qualification_id = p.id
       WHERE sq.scholarship_id = ?
       ORDER BY p.title`,
      [id]
    );

    // Fetch FAQs
    const [faqs] = await connection.query(
      `SELECT 
        id, question, answer
       FROM scholarship_faqs
       WHERE scholarship_id = ?
       ORDER BY id`,
      [id]
    );

    const scholarshipData = {
      ...scholarships[0],
      qualifications: qualifications || [],
      faqs: faqs || []
    };

    return NextResponse.json({
      success: true,
      data: scholarshipData
    });
  } catch (error) {
    console.error("PATCH /scholarships error:", error);
    return NextResponse.json(
      { 
        success: false,
        error: "Failed to fetch scholarship", 
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function PUT(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Scholarship ID is required" },
        { status: 400 }
      );
    }

    const body = await req.json();

    console.log('Update scholarship data:', body);
    
    // Validation
    if (!body.title) {
      return NextResponse.json(
        { success: false, error: "Scholarship title is required" },
        { status: 400 }
      );
    }

    if (!body.slug) {
      return NextResponse.json(
        { success: false, error: "Scholarship slug is required" },
        { status: 400 }
      );
    }

    if (!body.deadline) {
      return NextResponse.json(
        { success: false, error: "Scholarship deadline is required" },
        { status: 400 }
      );
    }

    if (!body.qualification_ids || !Array.isArray(body.qualification_ids) || body.qualification_ids.length === 0) {
      return NextResponse.json(
        { success: false, error: "At least one qualification is required" },
        { status: 400 }
      );
    }

    if (!body.faqs || !Array.isArray(body.faqs) || body.faqs.length === 0) {
      return NextResponse.json(
        { success: false, error: "At least one FAQ is required" },
        { status: 400 }
      );
    }

    // Check if slug already exists (excluding current scholarship)
    const [existingSlug] = await connection.query(
      'SELECT id FROM scholarships WHERE slug = ? AND id != ?',
      [body.slug, id]
    );

    if (existingSlug.length > 0) {
      return NextResponse.json(
        { success: false, error: "Slug already exists. Please use a different slug." },
        { status: 400 }
      );
    }

    // Validate CGPA if provided
    if (body.cgpa && (body.cgpa < 0 || body.cgpa > 4)) {
      return NextResponse.json(
        { success: false, error: "CGPA must be between 0 and 4" },
        { status: 400 }
      );
    }

    // Validate percentage_cover if provided
    if (body.percentage_cover && (body.percentage_cover < 0 || body.percentage_cover > 100)) {
      return NextResponse.json(
        { success: false, error: "Percentage cover must be between 0 and 100" },
        { status: 400 }
      );
    }

    // Validate amount range if both are provided
    if (body.amount_min && body.amount_max) {
      const min = parseFloat(body.amount_min);
      const max = parseFloat(body.amount_max);
      if (min > max) {
        return NextResponse.json(
          { success: false, error: "Minimum amount cannot be greater than maximum amount" },
          { status: 400 }
        );
      }
    }

    // Validate IELTS value if provided
    if (body.ielts !== undefined && body.ielts !== null) {
      if (body.ielts !== 0 && body.ielts !== 1) {
        return NextResponse.json(
          { success: false, error: "IELTS value must be either 0 (No) or 1 (Yes)" },
          { status: 400 }
        );
      }
    }

    // Validate FAQs
    const hasInvalidFaqs = body.faqs.some(faq => !faq.question || !faq.answer);
    if (hasInvalidFaqs) {
      return NextResponse.json(
        { success: false, error: "All FAQs must have both question and answer" },
        { status: 400 }
      );
    }

    // Start transaction
    await connection.beginTransaction();

    try {
      // Update scholarship with amount range and show_in_menu
      const [result] = await connection.query(
        `UPDATE scholarships SET 
          title = ?, 
          slug = ?,
          country_id = ?, 
          image = ?, 
          deadline = ?, 
          description = ?,
          cgpa = ?,
          percentage_cover = ?,
          amount_min = ?,
          amount_max = ?,
          ielts = ?,
          is_active = ?,
          show_in_menu = ?,
          updated_at = ?
         WHERE id = ?`,
        [
          body.title,
          body.slug,
          body.country_id || null,
          body.image || null,
          body.deadline,
          body.description || null,
          body.cgpa || null,
          body.percentage_cover || null,
          body.amount_min !== undefined && body.amount_min !== null && body.amount_min !== '' ? parseFloat(body.amount_min) : null,
          body.amount_max !== undefined && body.amount_max !== null && body.amount_max !== '' ? parseFloat(body.amount_max) : null,
          body.ielts !== undefined ? body.ielts : null,
          body.is_active ? 1 : 0,
          body.show_in_menu !== undefined ? (body.show_in_menu ? 1 : 0) : 1, // NEW: Update menu visibility
          new Date(),
          id
        ]
      );

      if (result.affectedRows === 0) {
        await connection.rollback();
        return NextResponse.json(
          { success: false, error: "Scholarship not found" },
          { status: 404 }
        );
      }

      // Update qualifications - delete existing and insert new
      await connection.query(
        `DELETE FROM scholarship_qualifications WHERE scholarship_id = ?`,
        [id]
      );

      const qualificationValues = body.qualification_ids.map(qualId => [id, parseInt(qualId)]);
      if (qualificationValues.length > 0) {
        await connection.query(
          `INSERT INTO scholarship_qualifications (scholarship_id, qualification_id) VALUES ?`,
          [qualificationValues]
        );
      }

      // Update FAQs - delete existing and insert new
      await connection.query(
        `DELETE FROM scholarship_faqs WHERE scholarship_id = ?`,
        [id]
      );

      const faqValues = body.faqs.map(faq => [
        id,
        faq.question,
        faq.answer,
        new Date(),
        new Date()
      ]);
      if (faqValues.length > 0) {
        await connection.query(
          `INSERT INTO scholarship_faqs (scholarship_id, question, answer, created_at, updated_at) VALUES ?`,
          [faqValues]
        );
      }

      // Commit transaction
      await connection.commit();

      // Fetch updated scholarship with joins
      const [rows] = await connection.query(
        `SELECT 
          s.*,
          c.country as country_name
         FROM scholarships s
         LEFT JOIN countries c ON s.country_id = c.id
         WHERE s.id = ?`,
        [id]
      );

      // Fetch updated qualifications
      const [qualRows] = await connection.query(
        `SELECT 
          sq.qualification_id,
          p.title as qualification_name
         FROM scholarship_qualifications sq
         LEFT JOIN posts p ON sq.qualification_id = p.id
         WHERE sq.scholarship_id = ?`,
        [id]
      );

      // Fetch updated FAQs
      const [faqRows] = await connection.query(
        `SELECT 
          id, question, answer
         FROM scholarship_faqs
         WHERE scholarship_id = ?
         ORDER BY id`,
        [id]
      );

      const scholarshipData = {
        ...rows[0],
        qualifications: qualRows,
        faqs: faqRows
      };

      return NextResponse.json({ 
        success: true, 
        data: scholarshipData,
        message: 'Scholarship updated successfully'
      });
    } catch (dbError) {
      await connection.rollback();
      throw dbError;
    }
  } catch (error) {
    console.error("Error updating scholarship:", error);
    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to update scholarship",
        details: error.message 
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}

export async function DELETE(req) {
  let connection;
  
  try {
    connection = await pool.getConnection();
    const { searchParams } = new URL(req.url);
    const id = searchParams.get('id');
    
    if (!id) {
      return NextResponse.json(
        { success: false, error: "Scholarship ID is required" },
        { status: 400 }
      );
    }

    // Start transaction to ensure data consistency
    await connection.beginTransaction();

    try {
      // First, check if the scholarship exists
      const [scholarshipExists] = await connection.query(
        'SELECT id FROM scholarships WHERE id = ?',
        [id]
      );

      if (scholarshipExists.length === 0) {
        await connection.rollback();
        return NextResponse.json(
          { success: false, error: "Scholarship not found" },
          { status: 404 }
        );
      }

      // Delete related FAQs first (due to foreign key constraints)
      await connection.query(
        `DELETE FROM scholarship_faqs WHERE scholarship_id = ?`,
        [id]
      );

      // Delete related qualifications
      await connection.query(
        `DELETE FROM scholarship_qualifications WHERE scholarship_id = ?`,
        [id]
      );

      // Now delete the main scholarship record
      const [result] = await connection.query(
        `DELETE FROM scholarships WHERE id = ?`,
        [id]
      );

      if (result.affectedRows === 0) {
        await connection.rollback();
        return NextResponse.json(
          { success: false, error: "Scholarship not found" },
          { status: 404 }
        );
      }

      // Commit the transaction if all operations succeed
      await connection.commit();

      return NextResponse.json({ 
        success: true, 
        message: "Scholarship and all related data deleted successfully" 
      });

    } catch (dbError) {
      // Rollback transaction in case of any error
      await connection.rollback();
      throw dbError;
    }

  } catch (error) {
    console.error("Error deleting scholarship:", error);
    
    // Handle specific database errors
    if (error.code === 'ER_LOCK_DEADLOCK') {
      return NextResponse.json(
        { success: false, error: "Database lock error. Please try again." },
        { status: 500 }
      );
    }
    
    if (error.code === 'ER_ROW_IS_REFERENCED_2' || error.code === 'ER_NO_REFERENCED_ROW_2') {
      return NextResponse.json(
        { success: false, error: "Cannot delete scholarship due to existing references." },
        { status: 400 }
      );
    }

    return NextResponse.json(
      { 
        success: false, 
        error: "Failed to delete scholarship",
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}