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

export async function GET(req) {
  const connection = await pool.getConnection();

  try {
    const [scholarships] = await connection.query<RowDataPacket[]>(`
      SELECT 
        s.id,
        s.title,
        s.slug,
        s.image,
        s.deadline,
        s.description,
        s.amount_min,
        s.amount_max,
        s.cgpa,
        s.percentage_cover,
        s.ielts,
        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
      WHERE s.is_active = 1 
        AND (s.deadline IS NULL OR s.deadline >= CURDATE())
      ORDER BY 
        CASE 
          WHEN s.deadline IS NULL THEN 1
          ELSE 0 
        END,
        s.deadline ASC,
        s.created_at DESC
    `);

    // Function to create short description
    const createShortDescription = (description) => {
      if (!description) return '';
      
      // Remove HTML tags
      const plainText = description.replace(/<[^>]*>/g, '');
      
      // Truncate to 200 characters
      if (plainText.length <= 200) {
        return plainText;
      }
      
      // Truncate to last complete word
      const truncated = plainText.substring(0, 200);
      const lastSpace = truncated.lastIndexOf(' ');
      
      if (lastSpace > 150) {
        return truncated.substring(0, lastSpace) + '...';
      }
      
      return truncated + '...';
    };

    // Fetch qualifications for each scholarship
    const scholarshipsWithQualifications = await Promise.all(
      scholarships.map(async (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`,
          [scholarship.id]
        );
        
        return {
          ...scholarship,
          description: createShortDescription(scholarship.description), // Shorter version
          qualifications: qualifications || []
        };
      })
    );

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

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