import { typedQuery } from '@/backend/utils/typedQuery';
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;
  ccurl: string;
  features: Feature[];
  offers: Offer[];
};

type RawCard = {
  id: number;
  name: string;
  bankname: string;
  minSalary: number;
  annualFee: number;
  image: string;
  ccurl: string;
};

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

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

type BankRow = {
  idbanks: number;
};

type FeatureRow = {
  idcreditcardsbenefits: number;
  creditcardsfeturesDetails: string;
};

type OfferRow = {
  idcreditcardsoffers: number;
  creditcardsoffersDetails: 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 typedQuery<BankRow>(
        `SELECT idbanks FROM banks  lactive=1 AND 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
        ,cc.ccurl
      FROM creditcards cc
      INNER JOIN banks bs ON cc.idbanks = bs.idbanks and bs.lactive=1
    `;

    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 typedQuery<RawCard>(cardQuery, cardParams);

    const cardsWithDetails: Card[] = await Promise.all(
      cards.map(async (card) => {
        try {
          const [featuresRows] = await typedQuery<FeatureRow>(
            `SELECT idcreditcardsbenefits, creditcardshortfeature creditcardsfeturesDetails FROM creditcardsfetures WHERE idcreditcards = ?`,
            [card.id]
          );

          const [offersRows] = await typedQuery<OfferRow>(
            `SELECT idcreditcardsoffers, creditcardsoffersDetails FROM creditcardsoffers WHERE idcreditcards = ?`,
            [card.id]
          );

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

          const offers = 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 }
    );
  }
}