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

export async function POST(req: NextRequest) {

  const connection = await pool.getConnection();

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

    // Map gender to MySQL-compatible value
    const genderMap = {
      male: "Male",
      female: "Female",
      other: "Other"
    };
    
    await connection.beginTransaction();

    // Check if the email already exists to handle duplicates
    const [existingVisa] = await connection.query<RowDataPacket[]>(
      'SELECT * FROM visit_visas WHERE email = ?',
      [body.email]
    );

    if (existingVisa.length > 0) {
      return new Response(
        JSON.stringify({
          success: false,
          error: 'Duplicate entry',
          details: 'A visa application with this email already exists'
        }),
        { status: 400 }
      );
    }

    // Insert new visa data into the database
    const [result] = await connection.query<ResultSetHeader>(
      `INSERT INTO visit_visas (
        name, email, phone, last_education, gender, taxpayer_type, 
        bank_statment, country, state, city, country_name, choosable_status, created_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        body.givenName || '',
        body.email || '',
        body.mobile || '',
        body.education || null,
        genderMap[body.gender?.toLowerCase()] || null,
        body.taxpayer || null,
        body.bankStatement || null,
        body.homeCountry || null,
        body.state || null,
        body.city || null,
        body.applyFor || null,
        'Pending',
        new Date()
      ]
    );

    const [ getcountry ] = await pool.query("SELECT * FROM countries_db WHERE id = ?", [body.homeCountry]);
    const [ getState ] = await pool.query("SELECT * FROM states_db WHERE id = ?", [body.state]);
    const [ getCity ] = await pool.query("SELECT * FROM cities_db WHERE id = ?", [body.city]);

    const payload = {
      name: body.givenName,
      email: body.email,
      phone: body.mobile,
      last_education: body.education,
      gender: genderMap[body.gender?.toLowerCase()],
      taxpayer_type: body.taxpayer,
      bank_statment: body.bankStatement,
      country: getcountry[0].name,
      state: getState[0].name,
      city: getCity[0].name,
      country_name: body.applyFor,
    };

    const response = await fetch("https://crm-universitiespage.com/reactapis/api/website_visit_visa_crm/store", {
      method: "POST",
      body: JSON.stringify(payload),
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `${process.env.CRM_API_KEY}`,
      },
    })

    const data = await response.json();

    if (data?.status_code !== 200) {

      await connection.rollback();

      return NextResponse.json(
        {
          message: "Failed to submit your form. Please try again later.",
          success: false
        },
        {
          status: 500
        }
      )
    }

    await connection.commit();
    
    return new Response(
      JSON.stringify({ success: true, visa: { id: result.insertId, ...body } }),
      { status: 201 }
    );
  } catch (error) {
    console.error('Error creating visit visa:', error);
    await connection.rollback();

    // Handle general errors
    return new Response(
      JSON.stringify({
        success: false,
        error: 'Internal Server Error',
        details: error.message
      }),
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}