import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../../lib/db/db';
import { ResultSetHeader, RowDataPacket } from 'mysql2';

function parseFloatOrNull(value: any): number | null {
  if (value === null || value === undefined || value === '') return null;
  const parsed = parseFloat(value);
  return isNaN(parsed) ? null : parsed;
}

function hasValue(value: any): boolean {
  return value !== null && value !== undefined && value !== '' && value !== 0.0;
}

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

  try {
    const body = await request.json();
    
    const {
      name,
      email,
      phone,
      country = "",
      qualification = "",
      budget = "",
      gpa = 0.0,
      englishTestType = "",
      englishTestScore = 0.0,
      major = ""
    } = body;

    // Validate required fields
    if (!name || !email || !phone) {
      return NextResponse.json(
        { success: false, message: 'Name, email, and phone are required' },
        { status: 400 }
      );
    }

    // Check if at least one other field is provided
    const otherFields = [country, qualification, budget, gpa, englishTestType, englishTestScore, major];
    const hasOtherField = otherFields.some(field => hasValue(field));
    
    if (!hasOtherField) {
      return NextResponse.json(
        { 
          success: false, 
          message: 'At least one additional field is required (country, qualification, budget, gpa, englishTestType, englishTestScore, or major)' 
        },
        { status: 400 }
      );
    }

    await connection.beginTransaction();

    let subject_name = "";
    let qualification_name = "";

    // Get subject name if major is provided
    if (hasValue(major)) {
      try {
        const [getSubject] = await connection.query<RowDataPacket[]>(
          "SELECT id, name FROM subjects WHERE id = ?", 
          [major]
        );
        
        if (getSubject.length === 0) {
          await connection.rollback();
          return NextResponse.json(
            { success: false, message: 'Preferred Field of Study not found' },
            { status: 400 }
          );
        }
        subject_name = getSubject[0].name;
      } catch (error) {
        await connection.rollback();
        throw error;
      }
    }

    // Get qualification name if qualification is provided
    if (hasValue(qualification)) {
      try {
        const [getQualification] = await connection.query<RowDataPacket[]>(
          "SELECT id, title FROM posts WHERE id = ?", 
          [qualification]
        );
        
        if (getQualification.length === 0) {
          await connection.rollback();
          return NextResponse.json(
            { success: false, message: 'Preferred Level not found' },
            { status: 400 }
          );
        }
        qualification_name = getQualification[0].title;
      } catch (error) {
        await connection.rollback();
        throw error;
      }
    }

    // Insert into database
    const insertQuery = `
      INSERT INTO eligibility_calculator_submissions (
        name, email, phone, country, qualification, budget, 
        gpa, english_test_type, english_test_score, major
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    
    const insertValues = [
      name,
      email,
      phone,
      hasValue(country) ? country : null,
      hasValue(qualification_name) ? qualification_name : null,
      parseFloatOrNull(budget),
      parseFloatOrNull(gpa),
      hasValue(englishTestType) ? englishTestType : null,
      parseFloatOrNull(englishTestScore),
      hasValue(subject_name) ? subject_name : null
    ];
    
    const [result] = await connection.query<ResultSetHeader>(insertQuery, insertValues);
    const submissionId = result.insertId;

    // Prepare payload for CRM
    const newPayload = {
      name,
      email,
      phone,
      country: hasValue(country) ? country : null,
      qualification: hasValue(qualification_name) ? qualification_name : null,
      budget: parseFloatOrNull(budget),
      gpa: parseFloatOrNull(gpa),
      englishTestType: hasValue(englishTestType) ? englishTestType : null,
      englishTestScore: parseFloatOrNull(englishTestScore),
      major: hasValue(subject_name) ? subject_name : null
    };

    // Send to CRM API
    try {
      const crmResponse = await fetch(
        "https://crm-universitiespage.com/reactapis/api/eligibility_calculated_data_crm/store",
        {
          method: "POST",
          body: JSON.stringify(newPayload),
          headers: {
            'Content-Type': 'application/json',
            'Authorization': `${process.env.CRM_API_KEY || ''}`,
          },
        }
      );

      if (!crmResponse.ok) {
        throw new Error(`CRM API responded with status: ${crmResponse.status}`);
      }

      const crmData = await crmResponse.json();

      if (crmData?.status_code !== 200) {
        throw new Error('CRM API returned non-success status');
      }
    } catch (crmError) {
      await connection.rollback();
      console.error('CRM API Error:', crmError);
      
      return NextResponse.json(
        {
          success: false,
          message: "Failed to submit your form to CRM. Please try again later."
        },
        { status: 502 }
      );
    }

    await connection.commit();

    return NextResponse.json({
      success: true,
      message: 'Eligibility search stored successfully',
      data: { 
        submissionId,
        submittedAt: new Date().toISOString()
      }
    });

  } catch (error) {
    if (connection) {
      await connection.rollback();
    }
    
    console.error('Error storing eligibility search:', error);
    return NextResponse.json(
      { 
        success: false, 
        message: 'Failed to store eligibility search',
        error: error instanceof Error ? error.message : 'Unknown error'
      },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release(); // CRITICAL: Release the connection
    }
  }
}