import { typedQuery } from '@/backend/utils/typedQuery';

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

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

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

type OfferRow = {
  idcreditcardsoffers: number;
  creditcardsoffersDetails: string;
};

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

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

export async function getCardById(id: number): Promise<Card | null> {
  try {
    const [cardRows] = await typedQuery<CardRow>(
      `
            SELECT DISTINCT
            cc.idcreditcards AS id,
            cc.creditcardsname AS name,
            cc.MinimumSalary AS minSalary,
            cc.AnnualFee AS annualFee,
            CONCAT('/cardimages/', cc.creditcardsimagename) AS image,
            b.bankname AS bankname  -- ← add this
            ,cc.ccurl
          FROM creditcards cc
          JOIN banks b ON cc.idbanks = b.idbanks  -- Adjust if your schema differs
          WHERE b.lactive=1 AND cc.idcreditcards = ?
          LIMIT 1
    `,
      [id]
    );

    if (cardRows.length === 0) return null;

    const card = cardRows[0];

    const [featuresRows] = await typedQuery<FeatureRow>(
      `
      SELECT 
        idcreditcardsbenefits, 
        creditcardshortfeature AS creditcardsfeturesDetails 
      FROM creditcardsfetures 
      WHERE idcreditcards = ?
    `,
      [card.id]
    );

    const [offersRows] = await typedQuery<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,
    }));
// console.log("Features:", features);
// console.log("Offers:",offers);
    return {
      ...card,
      features,
      offers,
    };

  } catch (error) {
    console.error('getCardById error:', error);
    return null;
  }
}