import { NextRequest, NextResponse } from 'next/server';
import mysql from 'mysql2/promise';

export async function POST(request: NextRequest) {
  let connection;
  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 database 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_scholarship_predictors table
    const query = `
      INSERT INTO ai_scholarship_predictors (
        name, email, phoneNumber, age, nationality, currentCountry,
        educationLevel, cgpa, fieldOfStudy, languageScore, languageProof,
        researchExperience, publications, awards, preferredCountries,
        degreeLevel, fundingPriority, searchType, matchedCount, analysisScore,
        profileProgress, profileData
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const values = [
      body.name,
      body.email,
      body.phoneNumber,
      body.age || null,
      body.nationality || '',
      body.currentCountry || '',
      body.educationLevel || '',
      parseFloat(body.cgpa?.toString() || '0'),
      body.fieldOfStudy || '',
      parseFloat(body.languageScore?.toString() || '0'),
      body.languageProof || '',
      body.researchExperience || '',
      body.publications || '',
      body.awards || '',
      JSON.stringify(body.preferredCountries || []),
      body.degreeLevel || '',
      body.fundingPriority || '',
      body.searchType || 'scholarship_match',
      body.matchedCount || 0,
      parseFloat(body.analysisScore?.toString() || '0'),
      body.profileProgress || 0,
      JSON.stringify(body.profileData || {})
    ];

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

    // Send to external CRM API
    const crmResponse = await fetch(
      "https://crm-universitiespage.com/reactapis/api/predictors/scholarship",
      {
        method: "POST",
        headers: {
          'Content-Type': 'application/json',
          'Authorization': process.env.CRM_API_KEY || '',
        },
        body: JSON.stringify({
          ...body,
          source: 'ai_scholarship_match_tool',
          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 }
      );
    }


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

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

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

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