import { NextRequest, NextResponse } from 'next/server';
import { pool } from '@/backend/utils/db';
import { features } from 'process';

type Category = {
  id: number;
  title: string;
};
type feature ={
  
  idcreditcardsbenefits :number;
  creditcardsfeturesDetails : string ;
  idcreditcards :number
}
type offer ={
  idcreditcardsoffers :number;
  creditcardsoffersDetails : string ;
  idcreditcards :number
}
type Card = {
  id: number;
  name: string;
  bankname: string;
  minSalary: number;
  annualFee: number;
  image: string;
  features: feature[];
  offers: offer[];
};

export async function GET(req: NextRequest) {
  try {
    const { searchParams } = new URL(req.url);
    const bank = searchParams.get('bank');           // optional
    const type = searchParams.get('type');           // optional
    const categoryId = searchParams.get('category'); // optional

    let categories: Category[] = [];

    // If specific categoryId is provided
    if (categoryId && !type) {
      const [rows] = await pool.query(
        `SELECT cd.idcardtype AS id, cardtypedesc AS title 
         FROM cardtypes cd 
         WHERE useascategory = 1 AND cd.idcardtype = ?`,
        [categoryId]
      );
      if ((rows as Category[]).length === 0) {
        return NextResponse.json({ success: false, error: 'Category not found' }, { status: 404 });
      }
      categories = [(rows as Category[])[0]];
    }

    // If "type" is provided (useascategory = 0)
    else if (type) {
      const [rows] = await pool.query(
        `SELECT cd.idcardtype AS id, cardtypedesc AS title 
         FROM cardtypes cd 
         WHERE useascategory = 0`
      );
      categories = rows as Category[];
    }

    // Else, get all categories with useascategory = 1
    else {
      const [rows] = 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
         )`
      );
      categories = rows as Category[];
    }

    // If bank is passed, fetch bank ID
    let bankId: number | null = null;
    if (bank) {
      const [bankRows] = await pool.query(`SELECT idbanks FROM banks WHERE bankcode = ?`, [bank]);
      if ((bankRows as any[]).length === 0) {
        return NextResponse.json({ success: false, error: 'Bank not found' }, { status: 404 });
      }
      bankId = (bankRows as any[])[0].idbanks;
    }

    const result = await Promise.all(
      categories.map(async (category) => {
                        let cardQuery = `
                    SELECT DISTINCT
                        cc.idcreditcards AS id,
                        cc.creditcardsname AS name,
                        bs.bankname as bankname,
                        cc.MinimumSalary AS minSalary,
                        cc.AnnualFee AS annualFee,
                        CONCAT('/cardimages/', cc.creditcardsimagename) AS image
                    FROM creditcards cc
                    inner join banks bs on cc.idbanks=bs.idbanks
                   `;

                    const cardParams: any[] = [];

                    if (categoryId) {
                  cardQuery += ` WHERE exists (select 1 from cardtypes ct 
                    inner join creditcardstypes ctt on ctt.idcardtype=ct.idcardtype
                    where cc.idcreditcards=ctt.idcreditcards and ct.useascategory=1 and  ct.idcardtype = ? ) `;
                    cardParams.push(category.id); // only one category will be processed
                    } else if (type) {
                    cardQuery += ` WHERE exists (select 1 from cardtypes ct 
                    inner join creditcardstypes ctt on ctt.idcardtype=ct.idcardtype
                    where cc.idcreditcards=ctt.idcreditcards and ct.useascategory=0 and  ct.idcardtype = ? ) `;
                    cardParams.push(category.id); // will loop over all types
                    } else if (bankId !== null) {
                    cardQuery += ` WHERE cc.idbanks = ?`;
                    cardParams.push(bankId);
                    }


        const [cards] = await pool.query(cardQuery, cardParams);

        const cardsWithDetails = await Promise.all(
          (cards as Card[]).map(async (card) => {
            try {
              const [features] = await pool.query(
                `SELECT idcreditcardsbenefits,creditcardsfeturesDetails,idcreditcards  FROM creditcardsfetures WHERE idcreditcards = ?`,
                [card.id]
              );
              const [offers] = await pool.query(
                `SELECT idcreditcardsoffers,creditcardsoffersDetails,idcreditcards  FROM creditcardsoffers WHERE idcreditcards = ?`,
                [card.id]
              );

              return {
                ...card,
                features: (features as any[]).map((f) => f.point),
                offers: (offers as any[]).map((o) => o.point),
              };
            } catch (err) {
              console.error(`Error fetching details for card ID ${card.id}:`, err);
              return { ...card, features: [], offers: [] };
            }
          })
        );

        return {
          title: category.title,
          cards: cardsWithDetails,
        };
      })
    );

    return NextResponse.json(result);
  } catch (error: unknown) {
    console.error('API error:', error);
    return NextResponse.json(
      { success: false, error: error instanceof Error ? error.message : 'Unknown error' },
      { status: 500 }
    );
  }
}