import { NextResponse } from 'next/server';
import pool from '../../../../../lib/db/db';
import * as XLSX from 'xlsx';

export async function POST(request) {
  const connection = await pool.getConnection();

  try {
    const formData = await request.formData();
    const file = formData.get('file');

    if (!file) {
      return NextResponse.json(
        { success: false, error: 'No file provided' },
        { status: 400 }
      );
    }

    // Read the Excel file
    const buffer = await file.arrayBuffer();
    const workbook = XLSX.read(buffer, { type: 'buffer' });
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const data = XLSX.utils.sheet_to_json(worksheet);

    if (data.length === 0) {
      return NextResponse.json(
        { success: false, error: 'Excel file is empty' },
        { status: 400 }
      );
    }

    const results = {
      total: data.length,
      successful: 0,
      failed: 0,
      errors: [],
      failedRows: [] // New array to store detailed failed row information
    };

    // Process each row
    for (let i = 0; i < data.length; i++) {
      const row = data[i];
      let failureReason = '';
      
      try {
        // Normalize column names and handle missing values
        const our_product_course_name = row['COURSE NAME'] || row['COURSE NAME '] || '';
        const our_product_course_fee = parseFloat(row['APPLICATION FEE']) || null;
        const city_id = row['City'] || row['City '] || null;
        const country_id = row['Country'] || null;
        const university_id = row['University'] || null;
        const our_product_cgpa = parseCGPA(row['CGPA']);
        const our_product_currency = row['CURRENCY'] || '';
        const our_product_intake_month = row['INTAKE MONTH'] || '';
        const our_product_moi = row['ENGLISH REQUIREMENTS'] || '';
        const our_product_remarks = row['REMARKS'] || '';
        const our_product_other_detail = row['OTHER DETAIL'] || '';
        const our_product_english_test = row['ENGLISH TEST'] || '';
        const our_product_admission_test = row['ADMISSION TEST'] || '';
        const our_product_admission_dead_line_start_date = parseDate(row['ADMISSION DEAD LINE START DATE']);
        const our_product_admission_dead_line_end_date = parseDate(row['ADMISSION DEAD LINE END DATE']);
        const our_product_scholarship_status = row['SCHOLARSHIP STATUS'] || '';
        const our_product_scholarship = row['SCHOLARSHIP'] || '';
        const course_category_id = row['CATEGORY'] || null;
        const interested_program_id = row['INTERESTED PROGRAM'] || null;

        // Validate required fields for duplicate check
        if (!our_product_course_name || !city_id || !country_id || !university_id || !course_category_id) {
          const missingFields = [];
          if (!our_product_course_name) missingFields.push('COURSE NAME');
          if (!city_id) missingFields.push('City');
          if (!country_id) missingFields.push('Country');
          if (!university_id) missingFields.push('University');
          if (!course_category_id) missingFields.push('CATEGORY');
          
          failureReason = `Missing required fields: ${missingFields.join(', ')}`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: our_product_course_name,
            reason: failureReason,
            data: {
              city: city_id,
              country: country_id,
              university: university_id,
              category: course_category_id
            }
          });
          continue;
        }

        // Check for duplicate entry based on composite key
        const [existingProduct] = await connection.query(
          `SELECT our_product_course_name FROM tbl_our_products 
           WHERE our_product_course_name = ? 
           AND city_id = ? 
           AND country_id = ? 
           AND university_id = ? 
           AND course_category_id = ?`,
          [our_product_course_name, city_id, country_id, university_id, course_category_id]
        );

        if (existingProduct.length > 0) {
          failureReason = `Duplicate entry found for course "${our_product_course_name}" with same city, country, university, and category`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: our_product_course_name,
            reason: failureReason,
            data: {
              city: city_id,
              country: country_id,
              university: university_id,
              category: course_category_id
            }
          });
          continue;
        }

        // Insert product
        await connection.query(`
          INSERT INTO tbl_our_products (
            our_product_course_name, our_product_course_fee, city_id, country_id, 
            university_id, our_product_cgpa, our_product_currency, our_product_intake_month, 
            our_product_moi, our_product_remarks, our_product_other_detail, our_product_english_test, 
            our_product_admission_test, our_product_admission_dead_line_start_date, 
            our_product_admission_dead_line_end_date, our_product_scholarship_status, 
            our_product_scholarship, course_category_id, interested_program_id
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
          our_product_course_name, our_product_course_fee, city_id, country_id, 
          university_id, our_product_cgpa, our_product_currency, our_product_intake_month, 
          our_product_moi, our_product_remarks, our_product_other_detail, our_product_english_test, 
          our_product_admission_test, our_product_admission_dead_line_start_date, 
          our_product_admission_dead_line_end_date, our_product_scholarship_status, 
          our_product_scholarship, course_category_id, interested_program_id
        ]);

        results.successful++;

      } catch (error) {
        failureReason = error.message;
        results.failed++;
        results.errors.push(`Row ${i + 2}: ${failureReason}`);
        results.failedRows.push({
          rowNumber: i + 2,
          courseName: row['COURSE NAME'] || row['COURSE NAME '] || 'Unknown',
          reason: failureReason,
          data: {
            city: row['City'] || row['City '] || null,
            country: row['Country'] || null,
            university: row['University'] || null,
            category: row['CATEGORY'] || null
          }
        });
      }
    }

    return NextResponse.json({
      success: true,
      results,
      message: `Import completed: ${results.successful} successful, ${results.failed} failed`,
      failedRows: results.failedRows // Include detailed failed rows in response
    });

  } catch (error) {
    return NextResponse.json(
      { success: false, error: 'Failed to import Excel file' },
      { status: 500 }
    );
  } finally {
    if (connection) connection.release();
  }
}

// Helper function to parse CGPA values
function parseCGPA(cgpaValue) {
  if (!cgpaValue) return null;
  
  if (typeof cgpaValue === 'number') {
    return cgpaValue;
  }
  
  if (typeof cgpaValue === 'string') {
    // Remove percentage sign and convert to number
    const cleaned = cgpaValue.replace('%', '').trim();
    const parsed = parseFloat(cleaned);
    return isNaN(parsed) ? null : parsed;
  }
  
  return null;
}

// Helper function to parse date values
function parseDate(dateValue) {
  if (!dateValue) return null;
  
  if (typeof dateValue === 'number') {
    // Excel serial date number
    return new Date((dateValue - 25569) * 86400 * 1000);
  }
  
  if (typeof dateValue === 'string') {
    // Try to parse various date formats
    const parsed = new Date(dateValue);
    return isNaN(parsed.getTime()) ? null : parsed;
  }
  
  return null;
}