// app/api/creditcards/cards/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { pool } from '@/backend/utils/db';

export async function GET(req: NextRequest) {
  try {
    // 1. Get all card categories (useascategory=1)
    const [categories] = await pool.query(`
      SELECT cd.idcardtype AS id, cardtypedesc AS title 
      FROM cardtypes cd
      WHERE useascategory = 1 AND  exists (select 1 from creditcardstypes ct where ct.idcardtype=cd.idcardtype)`);

    // 2. For each category, get its cards and their features
    const result = await Promise.all(
      (categories as any[]).map(async (category) => {
        const [cards] = await pool.query(
          `
          SELECT distinct
            cc.idcreditcards AS id,
            cc.creditcardsname AS name,
            cc.MinimumSalary AS minSalary,
            cc.AnnualFee AS annualFee,
            CONCAT('/cardimages/',cc.creditcardsimagename) AS image
          FROM creditcards cc
          INNER JOIN creditcardstypes ct ON ct.idcreditcards = cc.idcreditcards
                    WHERE ct.idcardtype = ?`,
          [category.id]
        );

        // // Get features (points) for each card
        // const cardsWithPoints = await Promise.all(
        //   (cards as any[]).map(async (card) => {
        //     const [points] = await pool.query(
        //       `
        //       SELECT creditcardsfeturesDetails AS point
        //       FROM creditcardsfetures
        //       WHERE idcreditcards = ?
        //       `,
        //       [card.id]
        //     );

        //     return {
        //       ...card,
        //       points: (points as any[]).map((p) => p.point),
        //     };
        //   })
        // );
          const cardsWithPoints = await Promise.all(
            (cards as any[]).map(async (card) => {
              try {
                const [points] = await pool.query(
                  `
                  SELECT creditcardsfeturesDetails AS point
                  FROM creditcardsfetures
                  WHERE idcreditcards = ?
                  `,
                  [card.id]
                );

                return {
                  ...card,
                  points: (points as any[] || []).map((p) => p.point),
                };
              } catch (err) {
                console.error(`Error fetching points for card ID ${card.id}:`, err);
                return {
                  ...card,
                  points: [], // Return empty array if error or no data
                };
              }
            })
          );
        return {
          title: category.title,
          cards: cardsWithPoints,
        };
      })
    );

    return NextResponse.json(result);
  } catch (error: any) {
    console.error('API error:', error);
    return NextResponse.json({ success: false, error: error.message }, { status: 500 });
  }
}