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

type Category = {
  id: number;
  title: string;
};

type Card = {
  id: number;
  name: string;
  bankname: string;
  minSalary: number;
  annualFee: number;
  image: string;
  features: string[];
  offers: string[];
};

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

    let categories: Category[] = [];

    //1. Determine which categories to use
    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[0] as Category];
    } 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 {
      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[];
    }

    // 2. Get bank ID if bank is specified
    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;
    }

    // 3. For each category, fetch cards
    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[] = [];
        const conditions: string[] = [];

        if (categoryId && !type) {
          conditions.push(`
            EXISTS (
              SELECT 1 FROM creditcardstypes ctt
              INNER JOIN cardtypes ct ON ct.idcardtype = ctt.idcardtype
              WHERE ct.useascategory = 1 AND ct.idcardtype = ? AND ctt.idcreditcards = cc.idcreditcards
            )
          `);
          cardParams.push(category.id);
        } else if (type) {
          conditions.push(`
            EXISTS (
              SELECT 1 FROM creditcardstypes ctt
              INNER JOIN cardtypes ct ON ct.idcardtype = ctt.idcardtype
              WHERE ct.useascategory = 0 AND ct.idcardtype = ? AND ctt.idcreditcards = cc.idcreditcards
            )
          `);
          cardParams.push(category.id);
        }

        if (bankId !== null) {
          conditions.push(`cc.idbanks = ?`);
          cardParams.push(bankId);
        }

        if (conditions.length > 0) {
          cardQuery += ' WHERE ' + conditions.join(' AND ');
        }

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

        // 4. For each card, get features and offers
        const cardsWithDetails = await Promise.all(
          (cards as Card[]).map(async (card) => {
            try {
              const [featuresRows] = await pool.query(
                `SELECT idcreditcardsbenefits,creditcardsfeturesDetails FROM creditcardsfetures WHERE idcreditcards = ?`,
                [card.id]
              );
              const [offersRows] = await pool.query(
                `SELECT idcreditcardsoffers,creditcardsoffersDetails FROM creditcardsoffers WHERE idcreditcards = ?`,
                [card.id]
              );
return {
  ...card,
  features: (featuresRows as any[]).map((f) => ({
    id: f.idcreditcardsbenefits,
    details: f.creditcardsfeturesDetails,
  })),
  offers: (offersRows as any[]).map((o) => ({
    id: o.idcreditcardsoffers,
    details: o.creditcardsoffersDetails,
  })),
};
              // return {
              //   ...card,
              //   features: (featuresRows as any[]).map((f) =>({id:f.idcreditcardsbenefits,details:f.creditcardsfeturesDetails}) ),
              //   offers: (offersRows as any[]).map((o) =>({id:o.idcreditcardsoffers,details:o.creditcardsoffersDetails}) ),
              // };
            } catch (err) {
              console.error(`Error fetching features/offers 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 }
    );
  }
}