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

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

export async function GET(req: NextRequest, {params}: PageProps) {
  const connection = await pool.getConnection();

  try {
    const {slug} = await params;
    const url = new URL(req.url);
    const includeFaqs = url.searchParams.get('include_faqs') === 'true';

    // Get main scholarship with basic info
    const [singleScholarship] = await connection.query<RowDataPacket[]>(
      `
        SELECT DISTINCT
          s.id,
          s.slug,
          s.title,
          s.image,
          s.amount_min,
          s.amount_max,
          s.cgpa,
          s.percentage_cover,
          s.ielts,
          s.deadline,
          s.description,
          s.created_at,
          c.country AS country_name,
          c.currency_symbol as currency_symbol
        FROM scholarships s
        LEFT JOIN countries c ON s.country_id = c.id
        LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
        WHERE s.slug = ? AND s.is_active = 1
        LIMIT 1
      `, [slug]
    );

    if (singleScholarship.length === 0) {
      return NextResponse.json(
        {
          success: false,
          message: "No Scholarship Found",
          data: []
        },
        { status: 404 }
      );
    }

    const scholarshipId = singleScholarship[0].id;

    // Fetch qualifications for the main scholarship
    const [qualifications] = await connection.query<RowDataPacket[]>(
      `SELECT 
        sq.qualification_id,
        p.title as qualification_name
       FROM scholarship_qualifications sq
       LEFT JOIN posts p ON sq.qualification_id = p.id
       WHERE sq.scholarship_id = ?
       ORDER BY p.title`,
      [scholarshipId]
    );

    // Fetch FAQs if requested
    let faqs = [];
    if (includeFaqs) {
      const [faqsData] = await connection.query<RowDataPacket[]>(
        `SELECT 
          question,
          answer,
          created_at as display_order
         FROM scholarship_faqs
         WHERE scholarship_id = ?
         ORDER BY created_at ASC`,
        [scholarshipId]
      );
      faqs = faqsData || [];
    }

    // Get related scholarships
    const [relatedScholarships] = await connection.query<RowDataPacket[]>(
      `
        SELECT DISTINCT
          s.id,
          s.slug,
          s.title,
          s.image,
          s.amount_min,
          s.amount_max,
          s.cgpa,
          s.country_id,
          c.country AS country_name,
          c.currency_symbol as currency_symbol
        FROM scholarships s
        LEFT JOIN countries c ON s.country_id = c.id
        LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
        WHERE s.slug != ? AND s.is_active = 1 
        ORDER BY s.created_at DESC 
        LIMIT 4
      `, [slug]
    );

    // Fetch qualifications for related scholarships
    const relatedScholarshipsWithQualifications = await Promise.all(
      relatedScholarships.map(async (scholarship) => {
        const [relatedQualifications] = await connection.query<RowDataPacket[]>(
          `SELECT 
            sq.qualification_id,
            p.title as qualification_name
           FROM scholarship_qualifications sq
           LEFT JOIN posts p ON sq.qualification_id = p.id
           WHERE sq.scholarship_id = ?
           ORDER BY p.title`,
          [scholarship.id]
        );
        
        return {
          ...scholarship,
          qualifications: relatedQualifications || []
        };
      })
    );

    const responseData = {
      scholarship: {
        ...singleScholarship[0],
        qualifications: qualifications || []
      },
      relatedScholarships: relatedScholarshipsWithQualifications,
      faqs: faqs
    };

    return NextResponse.json(
      {
        success: true,
        data: responseData,
      },
      { status: 200 }
    );

  } catch (err: any) {
    console.log('Scholarship Detail API Error:', err);
    return NextResponse.json(
      {
        success: false,
        message: err.message,
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}