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: [],
      updated: 0,
      created: 0,
      failedRows: []
    };

    // 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 programName = row['Program Name'] || '';
        const universityName = row['university name'] || '';
        const courseCategory = row['Course Category'] || '';
        const studyLevel = row['Study Level'] || '';
        const tuitionFees = parseFloat(row['Tuition Fees']) || null;
        const applicationFees = parseFloat(row['Application Fees']) || null;
        const applicationStartDate = parseDate(row['Application Start Date']);
        const applicationEndDate = parseDate(row['Application End Date']);
        const entryRequirements = row['Entry Requirements'] || '';
        const scholarshipAvailable = row['Scholarship Available'] || '';
        const cityName = row['City Name'] || '';
        const countryName = row['Country Name'] || '';
        const cgpa = parseCGPA(row['CGPA']);
        const currency = row['Currency'] || '';
        const currencySymbol = row['Currency Symbol'] || '';
        const openIntakes = row['Open Intakes'] || '';
        const remarks = row['Remarks'] || '';
        const scholarship = row['Scholarship'] || '';
        const websiteUrl = row['Website URL'] || '';
        const englishTest = row['English Test'] || '';
        const englishRequirementsDetails = row['English Requirements Details'] || '';
        const duration = row['Duration'] || ''; // New duration field

        // Validate required fields
        if (!programName || !universityName || !courseCategory || !studyLevel) {
          const missingFields = [];
          if (!programName) missingFields.push('Program Name');
          if (!universityName) missingFields.push('university name');
          if (!courseCategory) missingFields.push('Course Category');
          if (!studyLevel) missingFields.push('Study Level');
          
          failureReason = `Missing required fields: ${missingFields.join(', ')}`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: programName,
            reason: failureReason
          });
          continue;
        }

        // Get university_id from university_details table
        const [universityResult] = await connection.query(
          'SELECT id FROM university_details WHERE name = ?',
          [universityName.trim()]
        );

        if (universityResult.length === 0) {
          failureReason = `University not found: ${universityName}`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: programName,
            reason: failureReason
          });
          continue;
        }
        const university_id = universityResult[0].id;

        // Get subject_id from subjects table
        const [subjectResult] = await connection.query(
          'SELECT id FROM subjects WHERE name = ?',
          [courseCategory.trim()]
        );

        if (subjectResult.length === 0) {
          failureReason = `Subject/Category not found: ${courseCategory}`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: programName,
            reason: failureReason
          });
          continue;
        }
        const subject_id = subjectResult[0].id;

        // Get qualification_id from posts table
        const [qualificationResult] = await connection.query(
          'SELECT id FROM posts WHERE title = ?',
          [studyLevel.trim()]
        );

        if (qualificationResult.length === 0) {
          failureReason = `Qualification/Study Level not found: ${studyLevel}`;
          results.failed++;
          results.errors.push(`Row ${i + 2}: ${failureReason}`);
          results.failedRows.push({
            rowNumber: i + 2,
            courseName: programName,
            reason: failureReason
          });
          continue;
        }
        const qualification_id = qualificationResult[0].id;

        // Convert scholarship available to 1/0
        const scholarshipValue = convertScholarshipToBoolean(scholarshipAvailable);

        // Check if course already exists based on unique identifiers
        // Using more specific matching to prevent duplicates
        const [existingCourse] = await connection.query(
          `SELECT id FROM courses 
           WHERE name = ? 
           AND university_id = ? 
           AND subject_id = ? 
           AND qualification = ?`,
          [
            programName.trim(), 
            university_id, 
            subject_id, 
            qualification_id
          ]
        );

        if (existingCourse.length > 0) {
          // Update existing course - include all unique identifiers in update
          await connection.query(`
            UPDATE courses SET
              yearly_fee = ?,
              application_fee = ?,
              starting_date = ?,
              deadline = ?,
              entry_requirments = ?,
              scholarship = ?,
              city_name = ?,
              country_name = ?,
              cgpa = ?,
              currency = ?,
              currency_symbol = ?,
              open_intakes = ?,
              remarks = ?,
              scholarship_price = ?,
              website_url = ?,
              english_test = ?,
              english_requirements_details = ?,
              duration = ?,
              updated_at = NOW()
            WHERE id = ?
          `, [
            tuitionFees,
            applicationFees,
            applicationStartDate,
            applicationEndDate,
            entryRequirements,
            scholarshipValue, // This is now 0 or 1
            cityName,
            countryName,
            cgpa,
            currency,
            currencySymbol,
            openIntakes,
            remarks,
            scholarship,
            websiteUrl,
            englishTest,
            englishRequirementsDetails,
            duration, // New duration field
            existingCourse[0].id
          ]);

          results.updated++;
          results.successful++;
          console.log(`✅ Updated existing course: ${programName}`);
        } else {
          // Insert new course
          await connection.query(`
            INSERT INTO courses (
              name, university_id, subject_id, qualification, yearly_fee, 
              application_fee, starting_date, deadline, entry_requirments, 
              scholarship, city_name, country_name, cgpa, currency, 
              currency_symbol, open_intakes, remarks, scholarship_price, 
              website_url, english_test, english_requirements_details,
              duration, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
          `, [
            programName.trim(), 
            university_id, 
            subject_id, 
            qualification_id, // Using qualification_id instead of qualification text
            tuitionFees,
            applicationFees,
            applicationStartDate,
            applicationEndDate,
            entryRequirements,
            scholarshipValue, // This is now 0 or 1
            cityName,
            countryName,
            cgpa,
            currency,
            currencySymbol,
            openIntakes,
            remarks,
            scholarship,
            websiteUrl,
            englishTest,
            englishRequirementsDetails,
            duration // New duration field
          ]);

          results.created++;
          results.successful++;
          console.log(`✅ Created new course: ${programName}`);
        }

      } catch (error) {
        failureReason = error.message;
        results.failed++;
        results.errors.push(`Row ${i + 2}: ${failureReason}`);
        results.failedRows.push({
          rowNumber: i + 2,
          courseName: row['Program Name'] || 'Unknown',
          reason: failureReason
        });
        console.error(`❌ Error in row ${i + 2}:`, error);
      }
    }

    return NextResponse.json({
      success: true,
      results,
      message: `Import completed: ${results.successful} successful (${results.created} created, ${results.updated} updated), ${results.failed} failed`,
      failedRows: results.failedRows
    });

  } catch (error) {
    console.error('Import error:', error);
    return NextResponse.json(
      { success: false, error: 'Failed to import Excel file: ' + error.message },
      { 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') {
    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;
  }
  
  if (dateValue instanceof Date) {
    return dateValue;
  }
  
  return null;
}

// Helper function to convert scholarship text to boolean (0/1)
function convertScholarshipToBoolean(scholarshipText) {
  if (!scholarshipText) return 0;
  
  const text = scholarshipText.toString().toLowerCase().trim();
  
  // Check for yes/true values
  if (
    text === 'yes' || 
    text === 'y' || 
    text === 'true' || 
    text === '1' || 
    text === 'available' ||
    text === 'scholarship available'
  ) {
    return 1;
  }
  
  // Check for no/false values
  if (
    text === 'no' || 
    text === 'n' || 
    text === 'false' || 
    text === '0' || 
    text === 'not available' ||
    text === 'none'
  ) {
    return 0;
  }
  
  // Default to 0 if unclear
  return 0;
}