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

type Loan = {
  id: number;
  name: string;
  bankname: string;
  minIncome: number;
  annualFee: string | null;
  image: string;
  loanurl: string;
  reducingInterestRate: number | null;
  flatInterestRate: number | null;
  loantypecode: string | null;
  loantypedesc: string | null;
  features: Feature[];
  offers: Offer[];
};

type BankRow = { idbanks: number };
type FeatureRow = { idloanfeature: number; loanfeatureDetails: string; loanshortfeature: string };
type OfferRow = { idloanoffer: number; loanofferDetails: string };
type RawLoan = {
  id: number;
  name: string;
  bankname: string;
  minIncome: number;
  annualFee: string | null;
  reducingInterestRate: number | null;
  flatInterestRate: number | null;
  image: string;
  loanurl: string;
  loantypecode: string | null;
  loantypedesc: string | null;
};

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 loanType = searchParams.get('loantype');
    const minIncomeParam = searchParams.get('minIncome');
    const idsParam = searchParams.get('ids');

    let bankId: number | null = null;
    if (bank) {
      const [bankRows] = await typedQuery<BankRow>(
        `SELECT idbanks FROM banks WHERE bankcode = ? OR bankname = ?`,
        [bank, bank]
      );
      if (!bankRows || bankRows.length === 0) {
        return NextResponse.json({ success: false, error: 'Bank not found' }, { status: 404 });
      }
      bankId = bankRows[0].idbanks;
    }

    let loanQuery = `
      SELECT DISTINCT
        ld.idloan              AS id,
        ld.loanname            AS name,
        b.bankname             AS bankname,
        ld.MinimumIncome       AS minIncome,
        ld.AnnualFee           AS annualFee,
        ld.FlatInterestRate    AS flatInterestRate,
        ld.ReducingInterestRate AS reducingInterestRate,
        ld.loanurl             AS loanurl,
        CONCAT('/banklogo/', b.banklogo) AS image,
        lt.loantypecode        AS loantypecode,
        lt.loantypedesc        AS loantypedesc
      FROM loansdetails ld
      INNER JOIN banks b ON ld.idbank = b.idbanks
      LEFT JOIN loantypes lt ON ld.idloantype = lt.idloantype
    `;

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

    if (bankId !== null) {
      conditions.push('ld.idbank = ?');
      params.push(bankId);
    }

    if (loanType) {
      conditions.push('(lt.loantypecode = ? OR lt.loantypedesc = ?)');
      params.push(loanType, loanType);
    }

    if (minIncomeParam) {
      const minIncome = Number(minIncomeParam);
      if (!Number.isNaN(minIncome)) {
        conditions.push('ld.MinimumIncome >= ?');
        params.push(minIncome);
      }
    }

    if (idsParam) {
      const ids = idsParam
        .split(',')
        .map((id) => Number(id.trim()))
        .filter((n) => Number.isInteger(n) && n > 0);
      if (ids.length > 0) {
        const placeholders = ids.map(() => '?').join(',');
        conditions.push(`ld.idloan IN (${placeholders})`);
        params.push(...ids);
      }
    }

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

    loanQuery += ' ORDER BY ld.loanname';

    const [loans] = await typedQuery<RawLoan>(loanQuery, params);

    const loansWithDetails: Loan[] = await Promise.all(
      loans.map(async (loan) => {
        try {
          const [featuresRows] = await typedQuery<FeatureRow>(
            `SELECT idloanfeature, loanfeatureDetails, loanshortfeature FROM loanfeatures WHERE idloan = ? ORDER BY idloanfeature`,
            [loan.id]
          );
          const [offersRows] = await typedQuery<OfferRow>(
            `SELECT idloanoffer, loanofferDetails FROM loanoffers WHERE idloan = ? ORDER BY idloanoffer`,
            [loan.id]
          );

          const features: Feature[] = featuresRows.map((f) => ({
            id: f.idloanfeature,
            details: f.loanfeatureDetails || f.loanshortfeature,
          }));

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

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

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