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

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

type BankRow = { idbanks: number };
type FeatureRow = { idcreditcardsbenefits: number; creditcardsfeturesDetails: string };
type OfferRow = { idcreditcardsoffers: number; creditcardsoffersDetails: string };
type RawCard = {
  id: number;
  name: string;
  bankname: string;
  minSalary: number;
  annualFee: number;
  image: string;
};

type Feature = {
  id: number;
  details: string;
};

type Offer = {
  id: number;
  details: 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 bankId: number | null = null;
    if (bank) {
      const [bankRows] = await pool.query<BankRow[]>(
        `SELECT idbanks FROM banks WHERE bankcode = ?`,
        [bank]
      );
      if (bankRows.length === 0) {
        return NextResponse.json({ success: false, error: 'Bank not found' }, { status: 404 });
      }
      bankId = bankRows[0].idbanks;
    }

    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: (string | number)[] = [];
    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(categoryId);
    } 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(type);
    }

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

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

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

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

          const features: Feature[] = featuresRows.map((f) => ({
            id: f.idcreditcardsbenefits,
            details: f.creditcardsfeturesDetails,
          }));

          const offers: Offer[] = offersRows.map((o) => ({
            id: o.idcreditcardsoffers,
            details: o.creditcardsoffersDetails,
          }));

          return {
            ...card,
            features,
            offers,
          };
        } catch (err) {
          console.error(`Error fetching details for card ID ${card.id}:`, err);
          return { ...card, features: [], offers: [] };
        }
      })
    );

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