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

export async function POST(req: NextRequest) {

  const connection = await pool.getConnection();

  try {
    const {name, email, phone_number, start_date, job_id, resume_url} = await req.json();

    await connection.beginTransaction();

    // Validation
    if (!name || !email || !job_id || !resume_url) {
      return NextResponse.json(
        { 
          error: 'Missing required fields',
          details: { 
            name: !name ? 'Name is required' : null,
            email: !email ? 'Email is required' : null,
            job_id: !job_id ? 'Job ID is required' : null,
            resume_url: !resume_url ? 'Resume URL is required' : null
          }
        }, 
        { status: 400 }
      );
    }

    let startDateString = null;
    if (start_date && typeof start_date === 'string') {
      const dateObj = new Date(start_date);
      if (isNaN(dateObj.getTime())) {
        return NextResponse.json({ error: 'Invalid start_date format' }, { status: 400 });
      }
      startDateString = dateObj.toISOString();
    }

    // Prepare query and values
    const query = `
      INSERT INTO job_applies (name, email, phone_number, start_date, resume, job_id)
      VALUES (?, ?, ?, ?, ?, ?)
    `;

    const values = [
      name, 
      email, 
      phone_number || null, 
      startDateString, 
      resume_url, // Store the resume URL
      job_id,
    ];

    const crmValues = {
      name, email, phone_number, start_date, city: "Lahore", resume: resume_url, job_id
    }

    // Execute query
    const [result] = await connection.query<ResultSetHeader>(query, values);

    if (result.affectedRows === 0) {
      return NextResponse.json({ error: 'Failed to submit application' }, { status: 500 });
    }

    const response = await fetch("http://crm-universitiespage.com/reactapis/api/store-job-apply", {
      method: "POST",
      body: JSON.stringify(crmValues),
      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();

    // Send email notification
    await sendFormEmail(FormType.APPLY_ONLINE, {
      email: String(email)
    });

    return NextResponse.json(
      {
        message: "Application submitted successfully",
        data: {
          id: result.insertId,
          name,
          email,
          job_id,
          resume_url
        }
      }, 
      { status: 201 }
    );

  } catch (error: any) {
    await connection.rollback();
    console.error('Job application error:', error);
    
    // Handle duplicate entry error (if unique constraint exists)
    if (error.code === 'ER_DUP_ENTRY') {
      return NextResponse.json(
        { error: 'You have already applied for this job' }, 
        { status: 409 }
      );
    }
    
    // Handle database connection errors
    if (error.code === 'ECONNREFUSED' || error.code === 'PROTOCOL_CONNECTION_LOST') {
      return NextResponse.json(
        { error: 'Database connection error. Please try again later.' }, 
        { status: 503 }
      );
    }

    return NextResponse.json(
      { 
        error: 'Internal Server Error',
        details: process.env.NODE_ENV === 'development' ? error.message : undefined
      }, 
      { status: 500 }
    );
  }
}
