// app/api/appapis/ai-embassy-interview-predictor/store/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;

  // Authorization check
  const comingHeaders = request.headers.get("authorization");

  if (comingHeaders !== process.env.APP_API_KEY) {
    return NextResponse.json(
      { success: false, message: 'Unauthorized Access' },
      { status: 401 }
    );
  }

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

    // console.log('📥 Received payload:', body);

    // 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 }
      );
    }

    // Clean and validate age field - ensure it's a valid integer
    let cleanAge = null;
    if (body.age) {
      // Try to parse age as integer
      const parsedAge = parseInt(body.age);
      if (!isNaN(parsedAge) && parsedAge >= 16 && parsedAge <= 100) {
        cleanAge = parsedAge;
      } else {
        console.warn('Invalid age value:', body.age, '- setting to null');
      }
    }

    // Clean other fields
    const cleanWorkExperience = body.workExperience ? parseInt(body.workExperience) : 0;
    const cleanIeltsScore = body.ieltsScore ? parseFloat(body.ieltsScore) : 0;

    // 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(),
      cleanAge,  // Use cleaned age
      body.country || '',
      body.course || '',
      body.degreeLevel || '',
      cleanIeltsScore,
      body.languageProof || '',
      body.previousRefusals ? 1 : 0,
      body.sponsorType || '',
      body.studyGap || '',
      body.universityType || '',
      cleanWorkExperience
    ];

    const [result] = await connection.execute(query, values);
    const leadId = (result as any).insertId;
    // console.log('✅ Database insert successful, ID:', leadId);

    // Prepare CRM payload with properly formatted fields
    const crmPayload = {
      name: body.name,
      email: body.email,
      phoneNumber: body.phoneNumber,
      age: cleanAge,  // Send cleaned integer age
      country: body.country || '',
      course: body.course || '',
      degreeLevel: body.degreeLevel || '',
      ieltsScore: cleanIeltsScore,
      languageProof: body.languageProof || '',
      previousRefusals: body.previousRefusals ? 1 : 0,
      sponsorType: body.sponsorType || '',
      studyGap: body.studyGap || '',
      universityType: body.universityType || '',
      workExperience: cleanWorkExperience,
      lead_type: 'mobile',
      source: 'ai_embassy_predictor',
      lead_id: leadId,
      timestamp: new Date().toISOString()
    };

    // console.log('📤 Cleaned CRM Payload:', JSON.stringify(crmPayload, null, 2));

    // Build CRM URL
    const baseUrl = process.env.CRM_BASE_URL || 'https://crm-universitiespage.com/reactapis/api';
    const crmUrl = 'https://crm-universitiespage.com/reactapis/api/predictors/embassy-interview';
    
    // console.log('📤 CRM URL:', crmUrl);

    // Send to CRM API
    const crmResponse = await fetch(crmUrl, {
      method: "POST",
      headers: {
        'Content-Type': 'application/json',
        'Accept': 'application/json',
        'Authorization': process.env.CRM_API_KEY || '',
      },
      body: JSON.stringify(crmPayload)
    });

    // console.log('📥 CRM Response Status:', crmResponse.status);

    // Get response text
    const responseText = await crmResponse.text();
    // console.log('📥 CRM Raw Response:', responseText);

    let crmData;
    let isSuccess = false;
    let validationErrors = null;

    try {
      if (responseText && responseText.trim()) {
        crmData = JSON.parse(responseText);
        // console.log('📥 CRM Parsed Response:', crmData);
        
        // Check if it's a validation error
        if (crmResponse.status === 422 && crmData?.errors) {
          validationErrors = crmData.errors;
          console.warn('⚠️ CRM validation errors:', validationErrors);
        }
        
        // Check for success based on your CRM's response structure
        isSuccess = crmResponse.ok && (
          crmData?.status_code === 200 || 
          crmData?.success === true ||
          crmData?.status === 'success'
        );
      } else if (crmResponse.ok) {
        isSuccess = true;
      }
    } catch (parseError) {
      console.error('Failed to parse CRM response:', parseError);
    }

    // Always commit transaction to save locally
    await connection.commit();
    await connection.end();

    if (!isSuccess) {
      console.warn('⚠️ CRM sync failed, but data saved locally');
      return NextResponse.json({
        success: true,
        message: 'Lead saved locally, but CRM sync failed',
        leadId: leadId,
        crm_warning: 'Could not sync with CRM. Data saved in local database.',
        crm_status: crmResponse.status,
        crm_response: responseText,
        validation_errors: validationErrors
      }, { status: 201 });
    }

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

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

    console.error('❌ Error storing embassy lead:', error);
    return NextResponse.json(
      { 
        success: false, 
        message: 'Failed to store lead', 
        error: error.message,
        stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
      },
      { status: 500 }
    );
  }
}