// app/api/store-visa-predictor-lead/route.ts
import { NextRequest, NextResponse } from 'next/server';
import mysql from 'mysql2/promise';

export async function POST(request: NextRequest) {
  let connection: mysql.Connection | null = null;

  try {
    const body = await request.json();

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

    // Create DB connection
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      port: parseInt(process.env.DB_PORT || '3306')
    });

    // Start transaction
    await connection.beginTransaction();

    // Insert into ai_visa_predictors table
    const query = `
      INSERT INTO ai_visa_predictors (
        name, email, phoneNumber, age, visaType, purposeOfTravel,
        country, destinationCountry, residenceCountry, residenceCity,
        educationLevel, educationGapYears, mediumOfInstruction,
        employmentStatus, jobExperienceYears, languageTest,
        languageScore, financialSource, financialProofAmount,
        familyTiesInHomeCountry, previousRefusals,
        created_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
    `;

    const values = [
      body.name.trim(),
      body.email.trim(),
      body.phoneNumber.trim(),
      body.age ? parseInt(body.age) : null,
      body.visaType || '',
      body.purposeOfTravel || '',
      body.country || '',
      body.destinationCountry || '',
      body.residenceCountry || '',
      body.residenceCity || '',
      body.educationLevel || '',
      body.educationGapYears ? parseInt(body.educationGapYears) : 0,
      body.mediumOfInstruction || '',
      body.employmentStatus || '',
      body.jobExperienceYears ? parseInt(body.jobExperienceYears) : 0,
      body.languageTest || '',
      body.languageScore ? parseFloat(body.languageScore) : null,
      body.financialSource || '',
      body.financialProofAmount ? parseFloat(body.financialProofAmount) : 0,
      body.familyTiesInHomeCountry || '',
      body.previousRefusals ? parseInt(body.previousRefusals) : 0
    ];

    const [result] = await connection.execute(query, values);

    // Hit CRM API like complaints
    const crmResponse = await fetch("https://crm-universitiespage.com/reactapis/api/predictors/visa-eligibility", {
      method: "POST",
      headers: {
        'Content-Type': 'application/json',
        'Authorization': process.env.CRM_API_KEY || '',
      },
      body: JSON.stringify({
        ...body,
        lead_id: (result as any).insertId,
        source: 'ai_visa_predictor',
        timestamp: new Date().toISOString()
      })
    });

    const crmData = await crmResponse.json();

    // Rollback if CRM fails
    if (crmData?.status_code !== 200) {
      await connection.rollback();
      return NextResponse.json(
        {
          success: false,
          message: 'Failed to send lead to CRM. Transaction rolled back.',
          crmResponse: crmData
        },
        { status: 500 }
      );
    }

    // Commit transaction
    await connection.commit();
    await connection.end();

    return NextResponse.json({
      success: true,
      message: 'Lead saved and sent to CRM successfully',
      leadId: (result as any).insertId
    }, { status: 201 });

  } catch (error: any) {
    if (connection) await connection.rollback();
    if (connection) await connection.end();

    console.error('Error storing visa lead:', error);
    return NextResponse.json(
      { success: false, message: 'Failed to store lead', error: error.message },
      { status: 500 }
    );
  }
}
