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

interface BankAccount extends RowDataPacket {
  id: number;
  accountname: string;
  accountdetails: string;
  minBalance: number | null;
  minIncome: number | null;
  accounturl: string;
  accountimagename: string;
  bankname: string;
  accountType: string;
  features?: string[];
  offers?: string[];
  bankLogo?: string;
}

interface Feature extends RowDataPacket {
  bankaccountfeatureDetails: string;
}

interface Offer extends RowDataPacket {
  bankaccountofferDetails: string;
}

export async function GET(req: NextRequest) {
  try {
    const { searchParams } = new URL(req.url);
    const banks = searchParams.get('banks')?.split(',').filter(b => b) || [];
    const accounttypes = searchParams.get('accounttypes')?.split(',').filter(a => a) || [];
    const salary = searchParams.get('salary') ? parseInt(searchParams.get('salary')!) : 0;

    const whereClauses: string[] = [];
    const params: (string | number)[] = [];

    if (banks.length > 0) {
      const placeholders = banks.map(() => '?').join(',');
      whereClauses.push(`b.bankname IN (${placeholders})`);
      params.push(...banks);
    }

    if (accounttypes.length > 0) {
      const placeholders = accounttypes.map(() => '?').join(',');
      whereClauses.push(`at.accounttypedesc IN (${placeholders})`);
      params.push(...accounttypes);
    }

    if (salary > 0) {
      whereClauses.push(`(ba.MinimumIncome IS NULL OR ba.MinimumIncome <= ?)`);
      params.push(salary);
    }

    const where = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';

    const sql = `
      SELECT ba.idbankaccount as id, ba.accountname, ba.accountdetails, ba.MinimumBalance as minBalance, ba.MinimumIncome as minIncome,
             ba.accounturl, ba.accountimagename, b.bankname, at.accounttypedesc as accountType
      FROM bankaccounts ba
      JOIN banks b ON ba.idbank = b.idbanks
      JOIN accounttypes at ON ba.idaccounttype = at.idaccounttype
      ${where}
    `;

    const [accounts] = await pool.query<BankAccount[]>(sql, params);

    // Fetch features and offers for each account
    for (const acc of accounts) {
      const [features] = await pool.query<Feature[]>(
        `SELECT bankaccountfeatureDetails FROM bankaccountfeatures WHERE idbankaccount = ?`,
        [acc.id]
      );
      acc.features = features.map((f) => f.bankaccountfeatureDetails);

      const [offers] = await pool.query<Offer[]>(
        `SELECT bankaccountofferDetails FROM bankaccountoffers WHERE idbankaccount = ?`,
        [acc.id]
      );
      acc.offers = offers.map((o) => o.bankaccountofferDetails);
      acc.bankLogo = `/banklogo/${acc.bankname.replace(/\s+/g, '_')}.webp`;
    }

    return NextResponse.json(accounts);
  } catch (error) {
    console.error('Error fetching bank accounts:', error);
    return NextResponse.json({ error: 'Internal Server Error' }, { status: 500 });
  }
}