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

export async function POST(req: NextRequest) {

  const connection = await pool.getConnection();

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

    const { type, apply_id, item_name="", user_id, payment_status } = body;
    

    // Validate required fields
    if (!type || !apply_id || !item_name || !user_id) {
      return NextResponse.json(
        { success: false, message: 'Missing required fields' },
        { status: 400 }
      );
    }

    const [userDetails] = await connection.query<RowDataPacket[]>("SELECT * FROM users WHERE id = ?", [user_id]);

    if (userDetails.length === 0) {
      return NextResponse.json(
        { success: false, message: 'User not found' },
        { status: 404 }
      );
    }

    let new_item_name = "";

    await connection.beginTransaction();

    if(type === 'course')
    {
      const [courseDetails] = await connection.query<RowDataPacket[]>(`
        SELECT 
        c.name,
        u.name as university_name
        FROM courses c 
        LEFT JOIN university_details u ON c.university_id = u.id
        WHERE c.id = ? AND c.name = ?`, [apply_id, item_name]
      );

      if(courseDetails.length === 0)
      {
        return NextResponse.json(
          { success: false, message: 'Sorry you are trying to enter wrong details.' },
          { status: 404 }
        );
      }

      new_item_name = courseDetails[0].name + " at " + courseDetails[0].university_name;
    }
    else if(type === 'university')
    {
      const [universityDetails] = await connection.query<RowDataPacket[]>(`
        SELECT
        name
        FROM university_details
        WHERE id = ? AND name = ?`, [apply_id, item_name]
      );

      if(universityDetails.length === 0)
      {
        return NextResponse.json(
          { success: false, message: 'Sorry you are trying to enter wrong details.' },
          { status: 404 }
        );
      }

      new_item_name = universityDetails[0].name;
    }

    // Check if user already applied for this item
    const [existingApplication] = await connection.query<RowDataPacket[]>(
      "SELECT * FROM search_apply_now WHERE user_id = ? AND type = ? AND apply_id = ?",
      [user_id, type, apply_id]
    );

    if (existingApplication.length > 0) {
      return NextResponse.json(
        { 
          success: false, 
          message: 'You have already applied for this ' + type 
        },
        { status: 400 }
      );
    }

    // Insert new application
    const [result] = await connection.query<ResultSetHeader>(
      `INSERT INTO search_apply_now 
       (user_id, type, apply_id, item_name, payment_status, created_at, updated_at) 
       VALUES (?, ?, ?, ?, ?, NOW(), NOW())`,
      [user_id, type, apply_id, new_item_name, payment_status]
    );

    if (result.affectedRows === 0) {
      throw new Error('Failed to create application');
    }
    

    const payload = {
      name: userDetails[0].first_name + " " + userDetails[0].last_name,
      email: userDetails[0].email,
      phone: userDetails[0].phone,
      apply_id: apply_id,
      type: type,
      item_name: new_item_name,
      payment_status: payment_status
    }

    // Send to CRM API
    let crmResponse;
    try {
      crmResponse = await fetch(
        "http://crm-universitiespage.com/reactapis/api/website_apply_now_crm/store",
        {
          method: "POST",
          body: JSON.stringify(payload),
          headers: {
            'Content-Type': 'application/json',
            'Authorization': `${process.env.CRM_API_KEY}`,
          },
        }
      );

      if (!crmResponse.ok) {
        throw new Error(`CRM API responded with status: ${crmResponse.status}`);
      }

      const crmData = await crmResponse.json();

      if (crmData?.status_code !== 200) {
        throw new Error('CRM API returned non-success status');
      }

    } catch (crmError) {
      await connection.rollback();
      console.error('CRM API Error:', crmError);
      
      return NextResponse.json(
        {
          success: false,
          message: "Failed to submit your form to CRM. Please try again later."
        },
        { status: 502 } // Bad Gateway
      );
    }

    await connection.commit();


    return NextResponse.json({
      success: true,
      message: 'Application submitted successfully',
      applicationId: result.insertId
    });

  } catch (error) {
    await connection.rollback();
    console.error('Apply Now Error:', error);
    return NextResponse.json(
      { 
        success: false, 
        message: 'Internal server error' 
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}