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

export async function GET(request: NextRequest, { params }) {
  const { id: slug } = params;
  
  try {
    // Fetch the university details
    const [universityRows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM university_details WHERE slug = ? AND display = true AND is_active = true`,
      [slug]
    );
    
    if (universityRows.length === 0) {
      return Response.json(
        { error: 'University not found' }, 
        { status: 404 }
      );
    }

    const university = universityRows[0];

    // Fetch country information if university has a country
    let countryData = null;
    if (university.country) {
      try {
        const [countryRows] = await pool.query<RowDataPacket[]>(
          `SELECT consultation_fee, consultation_fee_discount, currency 
           FROM countries 
           WHERE country = ?`,
          [university.country]
        );

        if (countryRows.length > 0) {
          countryData = countryRows[0];
        }
      } catch (countryError) {
        console.error(`Error fetching country data for ${university.country}:`, countryError);
        countryData = null;
      }
    }

    // Fetch FAQs from university_faqs table
    const [faqRows] = await pool.query<RowDataPacket[]>(
      `SELECT id, question, answer FROM university_faqs WHERE university_id = ? ORDER BY id`,
      [university.id]
    );

    // Combine university data with country data, FAQs, and parse JSON fields
    const responseData = {
      ...university,
      overview: university.overview || '',
      academic_programs: university.academic_programs || '',
      scholarships_financial_support: university.scholarships_financial_support || '',
      campus_life_student_experience: university.campus_life_student_experience || '',
      why_choose: university.why_choose || '',
      short_description: university.short_description || '',
      keywords: university.keywords ? JSON.parse(university.keywords) : [],
      featured_images: university.featured_images ? JSON.parse(university.featured_images) : [],
      faqs: faqRows, // Add FAQs from separate table
      phone_no: university.phone_no ? university.phone_no.toString() : null,
      country_info: countryData ? {
        consultation_fee: countryData.consultation_fee,
        consultation_fee_discount: countryData.consultation_fee_discount,
        currency: countryData.currency
      } : null
    };

    return Response.json(responseData, { status: 200 });

  } catch (error) {
    console.error('Error fetching university:', error);
    return Response.json(
      { error: 'Internal server error' }, 
      { status: 500 }
    );
  }
}

export async function PUT(request, { params }) {
  const token = request.cookies.get("university-token")?.value || null;
  const putConnection = await pool.getConnection();

  if (!token) {
    return NextResponse.json(
      {
        message: "Please Login to continue"
      },
      {
        status: 400
      }
    );
  }

  const decoded = await verifyAuthentication(token);

  if (decoded instanceof NextResponse) {
    return NextResponse.json(
      {
        message: "You are not authorized to do this action"
      },
      {
        status: 400
      }
    );
  }

  const { id } = await params;
  
  if (!id || isNaN(parseInt(id))) {
    return Response.json({ error: 'Invalid university ID' }, { status: 400 });
  }
  
  const data = await request.json();

  try {
    // Helper functions for data processing
    const safeParseJson = (value) => {
      if (!value) return null;
      if (typeof value === 'object') return value;
      if (typeof value === 'string') {
        try {
          return JSON.parse(value);
        } catch (e) {
          return value;
        }
      }
      return value;
    };

    const convertArrayToString = (value) => {
      if (Array.isArray(value)) {
        return JSON.stringify(value);
      }
      return value;
    };

    await putConnection.beginTransaction();

    // Process data fields
    const phoneNo = data.phone_no || null;
    
    const totalStudents = data.total_students ? 
      parseInt(data.total_students) : null;
    
    const internationalStudent = data.international_student ? 
      parseInt(data.international_student) : null;
    
    // Handle scholarship value - it comes as '1' or '0' from frontend
    const scholarshipValue = data.scholarship === '1' ? 1 : 0;

    // Handle popular value - it comes as '1' or '0' from frontend
    const popularValue = data.popular === '1' ? 1 : 0;

    // Handle country_popular value - it comes as '1' or '0' from frontend
    const countryPopularValue = data.country_popular === '1' ? 1 : 0;

    // Process CGPA value
    const cgpaValue = data.cgpa ? parseFloat(data.cgpa) : null;

    // Process application fee
    const applicationFeeValue = data.application_fee ? parseFloat(data.application_fee) : null;

    // Process acceptance rate - NEW FIELD
    const acceptanceRateValue = data.acceptance_rate && data.acceptance_rate.trim() !== '' 
      ? data.acceptance_rate.trim() 
      : null;

    // Process meta fields - NEW
    const metaTitleValue = data.meta_title && data.meta_title.trim() !== ''
      ? data.meta_title.trim()
      : null;

    const metaDescriptionValue = data.meta_description && data.meta_description.trim() !== ''
      ? data.meta_description.trim()
      : null;

    // Process FAQs - NEW
    const faqsValue = data.faqs ? convertArrayToString(safeParseJson(data.faqs)) : null;

    // Process English test requirements
    const englishTestRequirements = data.english_test_requirements ? 
      convertArrayToString(safeParseJson(data.english_test_requirements)) : null;

    // Process keywords
    const keywordsValue = data.keywords ? convertArrayToString(safeParseJson(data.keywords)) : null;

    // Process featured images
    const featuredImagesValue = data.featured_images ? convertArrayToString(safeParseJson(data.featured_images)) : null;

    const rankingValue = data.ranking && data.ranking.trim() !== '' ? parseFloat(data.ranking) : null;

    // Prepare final data for MySQL - UPDATED to include acceptance_rate, meta fields, and faqs
    const finalData = {
      name: data.name,
      university_type: data.university_type,
      founded_in: data.founded_in,
      country: data.country,
      city: data.city,
      address: data.address,
      postcode: data.postcode,
      phone_no: phoneNo,
      total_students: totalStudents,
      international_student: internationalStudent,
      scholarship: scholarshipValue,
      cgpa: cgpaValue,
      overview: data.overview || '',
      academic_programs: data.academic_programs || '',
      scholarships_financial_support: data.scholarships_financial_support || '',
      campus_life_student_experience: data.campus_life_student_experience || '',
      why_choose: data.why_choose || '',
      short_description: data.short_description || '',
      keywords: keywordsValue,
      languages: data.languages,
      accommodation: data.accommodation,
      intake: data.intake,
      ranking: rankingValue,
      alternate_email: data.alternate_email,
      website: data.website,
      popular: popularValue,
      country_popular: countryPopularValue,
      average_course_fee: data.average_course_fee || null,
      application_fee: applicationFeeValue,
      // NEW FIELDS
      acceptance_rate: acceptanceRateValue,
      meta_title: metaTitleValue,
      meta_description: metaDescriptionValue,
      faqs: faqsValue,
      review_detail: convertArrayToString(safeParseJson(data.review_detail)),
      english_test_requirements: englishTestRequirements,
      logo: data.logo_url || null,
      feature_image: data.feature_image_url || null,
      featured_images: featuredImagesValue,
    };

    // STEP 1: First update MySQL
    console.log("🔄 Step 1: Updating university in MySQL...");
    
    // Build the SQL UPDATE query - UPDATED to include acceptance_rate, meta fields, and faqs
    const query = `
      UPDATE university_details 
      SET
        name = ?, 
        university_type = ?,
        founded_in = ?, 
        country = ?, 
        city = ?, 
        address = ?, 
        postcode = ?, 
        phone_no = ?, 
        total_students = ?, 
        international_student = ?, 
        scholarship = ?, 
        cgpa = ?, 
        overview = ?,
        academic_programs = ?,
        scholarships_financial_support = ?,
        campus_life_student_experience = ?,
        why_choose = ?,
        short_description = ?,
        keywords = ?,
        languages = ?, 
        accommodation = ?, 
        intake = ?, 
        ranking = ?, 
        alternate_email = ?, 
        website = ?, 
        popular = ?, 
        country_popular = ?,
        average_course_fee = ?,
        application_fee = ?,
        acceptance_rate = ?,
        meta_title = ?,
        meta_description = ?,
        review_detail = ?, 
        english_test_requirements = ?, 
        logo = ?, 
        feature_image = ?,
        featured_images = ?
      WHERE id = ?`;

    // Execute the query with the final data - UPDATED to include new fields
    const [updatedUniversity] = await putConnection.query<ResultSetHeader>(query, [
      finalData.name,
      finalData.university_type,
      finalData.founded_in,
      finalData.country,
      finalData.city,
      finalData.address,
      finalData.postcode,
      finalData.phone_no,
      finalData.total_students,
      finalData.international_student,
      finalData.scholarship,
      finalData.cgpa,
      finalData.overview,
      finalData.academic_programs,
      finalData.scholarships_financial_support,
      finalData.campus_life_student_experience,
      finalData.why_choose,
      finalData.short_description,
      finalData.keywords,
      finalData.languages,
      finalData.accommodation,
      finalData.intake,
      finalData.ranking,
      finalData.alternate_email,
      finalData.website,
      finalData.popular,
      finalData.country_popular,
      finalData.average_course_fee,
      finalData.application_fee,
      finalData.acceptance_rate, // NEW: Acceptance rate field
      finalData.meta_title, // NEW: Meta title
      finalData.meta_description, // NEW: Meta description
      finalData.review_detail,
      finalData.english_test_requirements,
      finalData.logo,
      finalData.feature_image,
      finalData.featured_images,
      id, // University ID for WHERE clause
    ]);

    if(updatedUniversity.affectedRows > 0)
    {
      const [rows] = await putConnection.query<RowDataPacket[]>(`SELECT * FROM university_details WHERE id = ?`, [id]);

      if(rows.length > 0){
        const updatedUniversityData = rows[0];

        // Update FAQs in university_faqs table
        const parsedFaqs = safeParseJson(data.faqs);
        if (Array.isArray(parsedFaqs)) {
          // First, delete existing FAQs
          await putConnection.query(
            "DELETE FROM university_faqs WHERE university_id = ?",
            [id]
          );

          // Then, insert new FAQs
          for (const faq of parsedFaqs) {
            if (faq.question && faq.answer) {
              await putConnection.query(
                "INSERT INTO university_faqs (university_id, question, answer) VALUES (?, ?, ?)",
                [id, faq.question.trim(), faq.answer.trim()]
              );
            }
          }
        }

        // STEP 3: Only commit and log activity if both updates are successful
        console.log("🔄 Step 3: Logging activity and committing transaction...");
        
        await putConnection.query(
          "INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", 
          [decoded.id, `University Name ${updatedUniversityData.name} Updated by user ${decoded.name} and email is ${decoded.email}`]
        );

        await putConnection.commit();

        console.log("✅ University successfully updated in MySQL");

        // Prepare response data
        const responseData = {
          success: true,
          data: {
            ...updatedUniversityData,
            overview: finalData.overview,
            academic_programs: finalData.academic_programs,
            scholarships_financial_support: finalData.scholarships_financial_support,
            campus_life_student_experience: finalData.campus_life_student_experience,
            why_choose: finalData.why_choose,
            short_description: finalData.short_description,
            keywords: safeParseJson(data.keywords),
            featured_images: safeParseJson(data.featured_images),
            // NEW FIELDS
            acceptance_rate: finalData.acceptance_rate,
            meta_title: finalData.meta_title,
            meta_description: finalData.meta_description,
            faqs: parsedFaqs || [],
          }
        };

        return Response.json(responseData, { status: 200 });

      } else {
        await putConnection.rollback();
        return Response.json({ error: 'Something went wrong.' }, { status: 500 });
      }
    }
    else
    {
      await putConnection.rollback();
      return NextResponse.json(
        { error: 'Error Updating University' },
        { status: 500 }
      );
    }
  } catch (error) {
    console.error('Error updating university:', error);
    if (putConnection) {
      await putConnection.rollback();
      console.log("Transaction rolled back due to error:", error);
    }
    return Response.json({ 
      error: 'Failed to update university', 
      details: error.message 
    }, { status: 500 });
  } finally {
      // Always release connection back to pool
      if (putConnection) {
          putConnection.release();
      }
  }
}

type PageProps = {
  params: Promise<{
    id: string;
  }>;
};

export async function DELETE(request: NextRequest, { params }: PageProps) {
  const token = request.cookies.get("university-token")?.value || null;

  const deleteConnection = await pool.getConnection();

  if (!token) {
    return NextResponse.json(
      {
        message: "Please Login to continue"
      },
      {
        status: 400
      }
    );
  }

  const decoded = await verifyAuthentication(token);

  if (decoded instanceof NextResponse) {
    return NextResponse.json(
      {
        message: "You are not authorized to do this action"
      },
      {
        status: 400
      }
    );
  }

  await deleteConnection.beginTransaction();

  const getIds = process.env.ALLOWED_DELETE_IDS;
  const allowedDeleteIds = getIds.split(',').map(id => id.trim());

  function canUserDelete(currentUserId) {
      return allowedDeleteIds.includes(currentUserId.toString());
  }

  if (!canUserDelete(decoded.id)) {
    return NextResponse.json(
      {
        success: false,
        message: "You are not authorized to do this action"
      },
      {
        status: 401
      }
    );
  }

  const {id} = await params // Access query parameter

  if (!id || isNaN(parseInt(id))) {
    return NextResponse.json(
      { error: 'Invalid university ID' },
      { status: 400 }
    );
  }

  try {

    const [checkID] = await deleteConnection.query<RowDataPacket[]>("SELECT * FROM university_details WHERE id = ?", [parseInt(id)]);

    if(checkID.length === 0)
    {
      return NextResponse.json({ error: 'University not found' }, { status: 404 });
    }

    // First delete FAQs
    await deleteConnection.query("DELETE FROM university_faqs WHERE university_id = ?", [parseInt(id)]);

    // Then delete the university
    const query = `DELETE FROM university_details WHERE id = ?`;

    // Execute the query
    const [result] = await deleteConnection.execute<RowDataPacket[]>(query, [parseInt(id)]);

    if (result.length === 0) {
      return NextResponse.json({ error: 'University not found' }, { status: 404 });
    }

    await deleteConnection.query("INSERT INTO activity_logs (user_id, action) VALUES (?, ?)", [decoded.id, `University Name ${checkID[0].name} deleted by user ${decoded.name} and email is ${decoded.email}`]);

    await deleteConnection.commit();

    // Return a 204 No Content response if successfully deleted
    return new Response(null, { status: 204 });
  } catch (error) {
    console.error('Error deleting university:', error);

    return Response.json({ error: 'Internal server error' }, { status: 500 });
  }
}

export async function PATCH(request: NextRequest, { params }) {
  const token = request.cookies.get("university-token")?.value || null;

  if (!token) {
    return NextResponse.json(
      {
        message: "Please Login to continue"
      },
      {
        status: 400
      }
    );
  }

  const decoded = await verifyAuthentication(token);

  if (decoded instanceof NextResponse) {
    return NextResponse.json(
      {
        message: "You are not authorized to do this action"
      },
      {
        status: 400
      }
    );
  }

  const { id } = await params;
  const data = await request.json();

  try {
    // Process popular and country_popular field values
    const popularValue = data.popular === 'true' || data.popular === true;
    const countryPopularValue = data.country_popular === 'true' || data.country_popular === true;

    // Build the SQL UPDATE query dynamically based on provided fields
    let query = 'UPDATE university_details SET ';
    const queryParams = [];
    
    if (data.popular !== undefined) {
      query += 'popular = ?';
      queryParams.push(popularValue);
    }
    
    if (data.country_popular !== undefined) {
      if (data.popular !== undefined) {
        query += ', ';
      }
      query += 'country_popular = ?';
      queryParams.push(countryPopularValue);
    }
    
    query += ' WHERE id = ?';
    queryParams.push(parseInt(id));

    // Execute the query with the processed data
    const [result] = await pool.execute<ResultSetHeader>(query, queryParams);

    // Now, run a SELECT query to get the updated data
    const [updatedUniversity] = await pool.query<RowDataPacket[]>("SELECT * FROM university_details WHERE id = ?", [
      parseInt(id), // University ID to fetch the updated data
    ]);

    return Response.json(updatedUniversity[0], { status: 200 });
  } catch (error) {
    console.error('Error updating university (PATCH):', error);
    return Response.json({ error: 'Internal server error' }, { status: 500 });
  }
}