// app/api/store-embassy-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 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 database
    const query = `
      INSERT INTO ai_embassy_interview_predictors (
        name, email, phoneNumber, age, country, course,
        degreeLevel, ieltsScore, languageProof,
        previousRefusals, sponsorType, studyGap,
        universityType, workExperience, created_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
    `;

    const values = [
      body.name.trim(),
      body.email.trim(),
      body.phoneNumber.trim(),
      body.age ? parseInt(body.age) : null,
      body.country || '',
      body.course || '',
      body.degreeLevel || '',
      body.ieltsScore ? parseFloat(body.ieltsScore) : 0,
      body.languageProof || '',
      body.previousRefusals ? 1 : 0,
      body.sponsorType || '',
      body.studyGap || '',
      body.universityType || '',
      parseInt(body.workExperience) || 0
    ];

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

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

    const crmData = await crmResponse.json();

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

    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 embassy lead:', error);
    return NextResponse.json(
      { success: false, message: 'Failed to store lead', error: error.message },
      { status: 500 }
    );
  }
}
