<?php

namespace ChurchCRM\Service;

use ChurchCRM\Authentication\AuthenticationManager;
use ChurchCRM\dto\SystemConfig;
use ChurchCRM\dto\SystemURLs;
use ChurchCRM\Utils\MICRUtils;
use ChurchCRM\model\ChurchCRM\Deposit;
use ChurchCRM\model\ChurchCRM\DepositQuery;
use ChurchCRM\model\ChurchCRM\DonationFundQuery;
use ChurchCRM\model\ChurchCRM\FamilyQuery;
use ChurchCRM\model\ChurchCRM\Map\DonationFundTableMap;
use ChurchCRM\model\ChurchCRM\Map\PledgeTableMap;
use ChurchCRM\model\ChurchCRM\Pledge;
use ChurchCRM\model\ChurchCRM\PledgeQuery;
use ChurchCRM\Plugin\Hook\HookManager;
use ChurchCRM\Plugin\Hooks;
use ChurchCRM\Utils\FunctionsUtils;
use ChurchCRM\Utils\InputUtils;
use ChurchCRM\Service\AuthService;
use ChurchCRM\Service\DonationFundService;
use Propel\Runtime\ActiveQuery\Criteria;
use Propel\Runtime\Collection\ObjectCollection;
use Propel\Runtime\Map\TableMap;
use Propel\Runtime\Propel;

class FinancialService
{
    public function deletePayment(string $groupKey): void
    {
        AuthService::requireUserGroupMembership('bFinance');
        $pledge = PledgeQuery::create()->findOneByGroupKey($groupKey);
        if ($pledge === null) {
            return;
        }
        $pledge->delete();
    }

    public function getPayments(?string $depID = null): array
    {
        $query = PledgeQuery::create();
        
        // If a specific deposit ID is provided, filter by that deposit
        if (!empty($depID)) {
            $query->filterByDepositId($depID);
        } else {
            // Filter by user permissions (only when getting all payments)
            if (!empty(AuthenticationManager::getCurrentUser()->getShowSince())) {
                $query->filterByDate(AuthenticationManager::getCurrentUser()->getShowSince(), Criteria::GREATER_EQUAL);
            }
            if (!AuthenticationManager::getCurrentUser()->isShowPayments()) {
                $query->filterByPledgeOrPayment('Payment', Criteria::NOT_EQUAL);
            }
            if (!AuthenticationManager::getCurrentUser()->isShowPledges()) {
                $query->filterByPledgeOrPayment('Pledge', Criteria::NOT_EQUAL);
            }
        }
        
        $query->innerJoinDonationFund()->addAsColumn('PledgeName', DonationFundTableMap::COL_FUN_NAME);
        $data = $query->find();

        $rows = [];
        foreach ($data as $row) {
            $newRow['FormattedFY'] = $row->getFormattedFY();
            $newRow['GroupKey'] = $row->getGroupKey();
            $newRow['Amount'] = $row->getAmount();
            $newRow['Nondeductible'] = $row->getNondeductible();
            $newRow['Schedule'] = $row->getSchedule();
            $newRow['Method'] = $row->getMethod();
            $newRow['Comment'] = InputUtils::sanitizeAndEscapeText($row->getComment() ?? '');
            $newRow['PledgeOrPayment'] = $row->getPledgeOrPayment();
            $newRow['Date'] = $row->getDate('Y-m-d');
            $newRow['DateLastEdited'] = $row->getDateLastEdited('Y-m-d');
            $newRow['EditedBy'] = $row->getPerson() ? $row->getPerson()->getFullName() : '';
            $newRow['Fund'] = $row->getPledgeName();
            $rows[] = $newRow;
        }

        return $rows;
    }

    public function getMemberByScanString(string $tScanString): array
    {
        if (!SystemConfig::getBooleanValue('bUseScannedChecks')) {
            throw new \Exception('Scanned Checks is disabled');
        }

        $micrObj = new MICRUtils(); // Instantiate the MICR class
        $routeAndAccount = $micrObj->findRouteAndAccount($tScanString); // use routing and account number for matching
        if (!$routeAndAccount) {
            throw new \Exception('error in locating family');
        }
        $family = FamilyQuery::create()->filterByScanCheck($routeAndAccount)->findOne();
        $iCheckNo = $micrObj->findCheckNo($tScanString);

        return [
            'ScanString'      => $tScanString,
            'RouteAndAccount' => $routeAndAccount,
            'CheckNumber'     => $iCheckNo,
            'fam_ID'          => $family?->getId(),
            'fam_Name'        => $family?->getName(),
        ];
    }

    public function setDeposit(string $depositType, string $depositComment, string $depositDate, $iDepositSlipID = null, $depositClosed = false): void
    {
        if ($iDepositSlipID) {
            $deposit = DepositQuery::create()->findOneById($iDepositSlipID);
            $deposit
                ->setDate($depositDate)
                ->setComment(InputUtils::sanitizeText($depositComment))
                ->setEnteredby(AuthenticationManager::getCurrentUser()->getId())
                ->setClosed(intval($depositClosed));
            $deposit->save();
            if ($depositClosed) {
                HookManager::doAction(Hooks::DEPOSIT_CLOSED, $deposit);
            }
            if ($depositClosed && ($depositType === 'CreditCard' || $depositType === 'BankDraft')) {
                // Delete any failed transactions on this deposit slip now that it is closing
                PledgeQuery::create()
                    ->filterByDepId((int) $iDepositSlipID)
                    ->filterByPledgeOrPayment('Payment')
                    ->filterByAutCleared(0)
                    ->delete();
            }
        } else {
            $deposit = new Deposit();
            $deposit
                ->setDate($depositDate)
                ->setComment(InputUtils::sanitizeText($depositComment))
                ->setEnteredby(AuthenticationManager::getCurrentUser()->getId())
                ->setType($depositType);
            $deposit->save();
            $deposit->reload();

            $iDepositSlipID = $deposit->getId();
        }
        $_SESSION['iCurrentDeposit'] = $iDepositSlipID;
    }

    public function getDepositTotal($id, $type = null)
    {
        AuthService::requireUserGroupMembership('bFinance');
        $query = PledgeQuery::create()
            ->filterByDepId((int) $id)
            ->filterByPledgeOrPayment('Payment');
        if ($type) {
            $query->filterByMethod($type);
        }
        $query->addAsColumn('deposit_total', 'SUM(' . PledgeTableMap::COL_PLG_AMOUNT . ')')
            ->select(['deposit_total']);
        $deposit_total = $query->findOne();

        return $deposit_total;
    }
        // ...existing code...

    public function getPaymentViewURI(string $groupKey): string
    {
        return SystemURLs::getRootPath() . '/finance/pledge/' . urlencode($groupKey);
    }

    /**
     * Return all pledge rows for a given GroupKey as a structured array.
     *
     * Includes family info, fund name, per-row amounts, and deposit association.
     *
     * @param string $groupKey
     * @return array{
     *   groupKey: string,
     *   familyId: int,
     *   familyName: string,
     *   date: string,
     *   fyId: int,
     *   method: string,
     *   checkNo: string|null,
     *   depositId: int|null,
     *   pledgeOrPayment: string,
     *   schedule: string|null,
     *   total: float,
     *   funds: list<array{
     *     fundId: int, fundName: string, amount: float,
     *     nonDeductible: float, comment: string
     *   }>
     * }
     * @throws \InvalidArgumentException when the group key does not exist
     */
    public function getPledgesByGroupKey(string $groupKey): array
    {
        AuthService::requireUserGroupMembership('bFinance');

        $pledges = PledgeQuery::create()
            ->filterByGroupKey($groupKey)
            ->joinWithDonationFund()
            ->joinWithFamily()
            ->find();

        if ($pledges->count() === 0) {
            throw new \InvalidArgumentException('Pledge group not found');
        }

        $funds = [];
        $total = 0.0;
        $header = null;

        foreach ($pledges as $pledge) {
            if ($header === null) {
                $family = $pledge->getFamily();
                $header = [
                    'groupKey'        => $pledge->getGroupKey(),
                    'familyId'        => (int) $pledge->getFamId(),
                    'familyName'      => $family ? $family->getFamilyString() : '',
                    'date'            => $pledge->getDate('Y-m-d'),
                    'fyId'            => (int) $pledge->getFyId(),
                    'method'          => $pledge->getMethod() ?? '',
                    'checkNo'         => $pledge->getCheckNo(),
                    'depositId'       => $pledge->getDepId() ? (int) $pledge->getDepId() : null,
                    'pledgeOrPayment' => $pledge->getPledgeOrPayment() ?? '',
                    'schedule'        => $pledge->getSchedule(),
                ];
            }

            $fund = $pledge->getDonationFund();
            $amount = (float) $pledge->getAmount();
            $total += $amount;

            $funds[] = [
                'fundId'       => (int) $pledge->getFundId(),
                'fundName'     => $fund ? $fund->getName() : '',
                'amount'       => $amount,
                'nonDeductible' => (float) $pledge->getNondeductible(),
                'comment'      => $pledge->getComment() ?? '',
            ];
        }

        $header['total'] = $total;
        $header['funds'] = $funds;

        return $header;
    }

    /**
     * Delete ALL pledge rows sharing a GroupKey (multi-fund aware).
     *
     * Unlike deletePayment() which only removes the first match, this method
     * deletes every row with the given GroupKey.
     *
     * @param string $groupKey
     * @throws \InvalidArgumentException when the group key does not exist
     */
    public function deletePledgeGroup(string $groupKey): void
    {
        AuthService::requireUserGroupMembership('bFinance');

        $count = PledgeQuery::create()
            ->filterByGroupKey($groupKey)
            ->delete();

        if ($count === 0) {
            throw new \InvalidArgumentException('Pledge group not found');
        }
    }

    public function getViewURI(string $Id): string
    {
        return SystemURLs::getRootPath() . '/DepositSlipEditor.php?DepositSlipID=' . $Id;
    }

    private function validateDate(object $payment): void
    {
        // Validate Date
        if (isset($payment->Date) && strlen($payment->Date) > 0) {
            [$iYear, $iMonth, $iDay] = sscanf($payment->Date, '%04d-%02d-%02d');
            if (!checkdate($iMonth, $iDay, $iYear)) {
                throw new \Exception('Invalid Date');
            }
        }
    }

    private function validateFund(object $payment): void
    {
        //Validate that the fund selection is valid:
        //If a single fund is selected, that fund must exist, and not equal the default "Select a Fund" selection.
        //If a split is selected, at least one fund must be non-zero, the total must add up to the total of all funds, and all funds in the split must be valid funds.
        $FundSplit = $payment->FundSplit;
        if (count($FundSplit) >= 1 && $FundSplit[0]->FundID !== 'None') { // split
            $nonZeroFundAmountEntered = 0;
            foreach ($FundSplit as $fund) {
                //$fun_active = $fundActive[$fun_id];
                if ($fund->Amount > 0) {
                    $nonZeroFundAmountEntered++;
                }
                if (SystemConfig::getBooleanValue('bEnableNonDeductible') && isset($fund->NonDeductible)) {
                    //Validate the NonDeductible Amount
                    if ($fund->NonDeductible > $fund->Amount) { //Validate the NonDeductible Amount
                        throw new \Exception(gettext("NonDeductible amount can't be greater than total amount."));
                    }
                }
            } // end foreach
            if (!$nonZeroFundAmountEntered) {
                throw new \Exception(gettext('At least one fund must have a non-zero amount.'));
            }
        } else {
            throw new \Exception('Must select a valid fund');
        }
    }

    public function locateFamilyCheck(string $checkNumber, string $fam_ID, ?string $excludeGroupKey = null)
    {
        AuthService::requireUserGroupMembership('bFinance');

        $query = PledgeQuery::create()
            ->filterByCheckNo($checkNumber)
            ->filterByFamId((int) $fam_ID);

        if ($excludeGroupKey !== null) {
            $query->filterByGroupKey($excludeGroupKey, Criteria::NOT_EQUAL);
        }

        return $query->count();
    }

    /**
     * @param ?string $excludeGroupKey when editing an existing pledge/payment, exclude its own
     *                                  rows from the duplicate-check-number lookup
     */
    public function validateChecks(object $payment, ?string $excludeGroupKey = null): void
    {
        AuthService::requireUserGroupMembership('bFinance');
        //validate that the payment options are valid
        //If the payment method is a check, then the check number must be present, and it must not already have been used for this family
        //if the payment method is cash, there must not be a check number
        if (!empty($payment->type) && $payment->type === 'Payment' && !empty($payment->iMethod) && $payment->iMethod === 'CHECK' && !isset($payment->iCheckNo)) {
            throw new \Exception(gettext('Must specify non-zero check number'));
        }
        // detect check inconsistencies
        if (!empty($payment->type) && $payment->type === 'Payment' && isset($payment->iCheckNo)) {
            if (!empty($payment->iMethod) && $payment->iMethod === 'CASH') {
                throw new \Exception(gettext("Check number not valid for 'CASH' payment"));
            } elseif (!empty($payment->iMethod) && $payment->iMethod === 'CHECK' && !empty($payment->FamilyID) && $this->locateFamilyCheck($payment->iCheckNo, $payment->FamilyID, $excludeGroupKey)) {
                //build routine to make sure this check number hasn't been used by this family yet (look at group key)
                throw new \Exception("Check number '" . $payment->iCheckNo . "' for selected family already exists.");
            }
        }
    }

    public function processCurrencyDenominations(object $payment, string $groupKey): void
    {
        if (empty($payment->cashDenominations)) {
            return;
        }
        global $cnInfoCentral;
        $currencyDenoms = json_decode($payment->cashDenominations, null, 512, JSON_THROW_ON_ERROR);
        foreach ($currencyDenoms as $cdom) {
            if (empty($payment->DepositID) || empty($cdom->currencyID) || empty($cdom->Count)) {
                continue;
            }
            $escapedGroupKey = mysqli_real_escape_string($cnInfoCentral, $groupKey);
            $sSQL = "INSERT INTO pledge_denominations_pdem (pdem_plg_GroupKey, plg_depID, pdem_denominationID, pdem_denominationQuantity)
      VALUES ('" . $escapedGroupKey . "','" . (int) $payment->DepositID . "','" . (int) $cdom->currencyID . "','" . (int) $cdom->Count . "')";
            FunctionsUtils::runQuery($sSQL);
        }
    }

    public function insertPledgeorPayment(object $payment, ?string $presetGroupKey = null)
    {
        AuthService::requireUserGroupMembership('bFinance');
        // Only set PledgeOrPayment when the record is first created.
        // Loop through all funds and create a pledge row for every non-zero amount.
        //
        // FundSplit may arrive as a JSON string (legacy callers) or as an already-
        // decoded array (when called via submitPledgeOrPayment after normalisation).
        $FundSplit = is_string($payment->FundSplit)
            ? json_decode($payment->FundSplit, false, 512, JSON_THROW_ON_ERROR)
            : $payment->FundSplit;

        // $presetGroupKey reuses the caller's GroupKey (updatePledgeOrPayment) instead of
        // generating a new one — the loop below only auto-generates when this is null.
        $sGroupKey = $presetGroupKey;

        foreach ($FundSplit as $Fund) {
            if ($Fund->Amount > 0) {  // Only insert a row if this fund has a non-zero amount.
                if ($sGroupKey === null) {  // GroupKey is shared across all fund rows for one payment.
                    $iAutID = $payment->iAutID ?? null;
                    if ($payment->iMethod === 'CHECK') {
                        $sGroupKey = FunctionsUtils::genGroupKey($payment->iCheckNo, $payment->FamilyID, $Fund->FundID, $payment->Date);
                    } elseif ($payment->iMethod === 'BANKDRAFT') {
                        $sGroupKey = FunctionsUtils::genGroupKey($iAutID ?? 'draft', $payment->FamilyID, $Fund->FundID, $payment->Date);
                    } elseif ($payment->iMethod === 'CREDITCARD') {
                        $sGroupKey = FunctionsUtils::genGroupKey($iAutID ?? 'credit', $payment->FamilyID, $Fund->FundID, $payment->Date);
                    } else {
                        $sGroupKey = FunctionsUtils::genGroupKey('cash', $payment->FamilyID, $Fund->FundID, $payment->Date);
                    }
                }

                $pledge = new Pledge();
                $pledge
                    ->setFamId($payment->FamilyID)
                    ->setFyId($payment->FYID)
                    ->setDate($payment->Date)
                    ->setAmount($Fund->Amount)
                    ->setMethod($payment->iMethod)
                    ->setComment($Fund->Comment ?? '')
                    ->setDateLastEdited(date('YmdHis'))
                    ->setEditedBy(AuthenticationManager::getCurrentUser()->getId())
                    ->setPledgeOrPayment($payment->type)
                    ->setFundId($Fund->FundID)
                    ->setDepId($payment->DepositID)
                    ->setGroupKey($sGroupKey);
                if (!empty($payment->schedule)) {
                    $pledge->setSchedule($payment->schedule);
                }
                if (!empty($payment->iCheckNo)) {
                    $pledge->setCheckNo($payment->iCheckNo);
                }
                if (!empty($payment->tScanString)) {
                    $pledge->setScanString($payment->tScanString);
                }
                if (!empty($payment->iAutID)) {
                    $pledge->setAutId($payment->iAutID);
                }
                // Always set NonDeductible — the column is NOT NULL with no default.
                // Using empty() here would skip 0, leaving the property null and
                // causing a MySQL constraint error on INSERT.
                $pledge->setNondeductible((float) ($Fund->NonDeductible ?? 0));
                $pledge->save();
                HookManager::doAction(Hooks::DONATION_RECEIVED, $pledge);
                // Do NOT return here — continue to save all fund rows before returning.
            }
        }

        return $sGroupKey;
    }

    /**
     * Normalise $payment->FundSplit to an array of stdClass objects.
     *
     * The legacy API and the new MVC editor both send FundSplit as a
     * JSON-encoded string.  validateFund() needs an array (it calls count()
     * and arrow-accesses ->FundID / ->Amount), while insertPledgeorPayment()
     * historically json_decoded the string itself.  Normalising once here
     * lets both methods share a single, already-decoded representation and
     * avoids a PHP 8 TypeError from count()-on-string.
     *
     * Accepts: JSON string  →  decoded to array of stdClass
     *          array        →  used as-is (future callers passing native arrays)
     *          stdClass     →  wrapped in a single-element array
     */
    private function normalizeFundSplit(object $payment): void
    {
        $raw = $payment->FundSplit ?? [];
        if (is_string($raw)) {
            $raw = json_decode($raw, false, 512, JSON_THROW_ON_ERROR);
        }
        if ($raw instanceof \stdClass) {
            $raw = [$raw];
        }
        $payment->FundSplit = is_array($raw) ? array_values($raw) : [];
    }

    public function submitPledgeOrPayment(object $payment): string
    {
        AuthService::requireUserGroupMembership('bFinance');
        $this->normalizeFundSplit($payment);
        $this->validateFund($payment);
        $this->validateChecks($payment);
        $this->validateDate($payment);
        $groupKey = $this->insertPledgeorPayment($payment);

        return $this->getPledgeorPayment($groupKey);
    }

    /**
     * Update an existing pledge/payment group in place.
     *
     * GroupKey is a plain string column, not the table's primary key (plg_plgID is),
     * so replaying insertPledgeorPayment() against an existing GroupKey would create
     * duplicate rows rather than updating them. Instead, replace the group's rows
     * atomically: delete the existing rows for $groupKey, then re-insert the submitted
     * fund rows under that same GroupKey.
     *
     * @throws \InvalidArgumentException when the group key does not exist
     */
    public function updatePledgeOrPayment(object $payment, string $groupKey): string
    {
        AuthService::requireUserGroupMembership('bFinance');
        $this->normalizeFundSplit($payment);
        $this->validateFund($payment);
        $this->validateChecks($payment, $groupKey);
        $this->validateDate($payment);

        $con = Propel::getWriteConnection(PledgeTableMap::DATABASE_NAME);
        $con->beginTransaction();
        try {
            // Existence check is inside the transaction so the check, denomination
            // cleanup, and pledge delete are all atomic — avoids a TOCTOU race.
            $existingCount = PledgeQuery::create()->filterByGroupKey($groupKey)->count($con);
            if ($existingCount === 0) {
                // No explicit rollBack() here — the catch (\Throwable) block below
                // handles rollback for every exception thrown inside this try{},
                // including \InvalidArgumentException.  Calling rollBack() here first
                // and then rethrowing would close the transaction before catch fires,
                // causing a second rollBack() on an inactive transaction (PDOException).
                throw new \InvalidArgumentException('Pledge group not found');
            }
            // Remove orphaned denomination rows. pledge_denominations_pdem has no FK
            // constraint and no Propel model, so we use a parameterized statement on
            // the same connection to keep the deletion within this transaction.
            $stmt = $con->prepare(
                'DELETE FROM pledge_denominations_pdem WHERE pdem_plg_GroupKey = :groupKey'
            );
            $stmt->execute([':groupKey' => $groupKey]);
            PledgeQuery::create()->filterByGroupKey($groupKey)->delete($con);
            $this->insertPledgeorPayment($payment, $groupKey);
            $con->commit();
        } catch (\Throwable $e) {
            $con->rollBack();
            throw $e;
        }

        return $this->getPledgeorPayment($groupKey);
    }

    public function getPledgeorPayment(string $GroupKey): string
    {
        AuthService::requireUserGroupMembership('bFinance');
        $total = 0;
        $pledges = PledgeQuery::create()->filterByGroupKey($GroupKey)->find();
        $payment = new \stdClass();
        $payment->funds = [];
        foreach ($pledges as $row) {
            $family = FamilyQuery::create()->findOneById($row->getFamId());
            $payment->Family = $family?->getFamilyString() ?? '';
            $payment->Date = $row->getDate('Y-m-d');
            $payment->FYID = $row->getFyId();
            $payment->iMethod = $row->getMethod();
            $fund = [];
            $fund['FundID'] = $row->getFundId();
            $fund['Amount'] = $row->getAmount();
            $fund['NonDeductible'] = $row->getNondeductible();
            $fund['Comment'] = $row->getComment();
            $payment->funds[] = $fund;
            $total += $row->getAmount();
            $onePlgID = $row->getId();
            $oneFundID = $row->getFundId();
            $iOriginalSelectedFund = $oneFundID; // remember the original fund in case we switch to splitting
            $fund2PlgIds[$oneFundID] = $onePlgID;
        }
        $payment->GroupKey = $GroupKey;
        $payment->total = $total;

        return json_encode($payment, JSON_THROW_ON_ERROR);
    }

    public function getDepositPDF($depID): void
    {
    }

    public function getCurrencyTypeOnDeposit(string $currencyID, string $depositID)
    {
        // Get the list of Currency denominations
        $sSQL = 'SELECT SUM(pdem_denominationQuantity) FROM pledge_denominations_pdem
                 WHERE plg_depID = ' . (int) $depositID . '
                 AND pdem_denominationID = ' . (int) $currencyID;
        $rscurrencyDenomination = FunctionsUtils::runQuery($sSQL);

        return mysqli_fetch_array($rscurrencyDenomination)[0];
    }

    /**
     * @return \stdClass[]
     */
    public function getCurrency(): array
    {
        $currencies = [];
        // Get the list of Currency denominations
        $sSQL = 'SELECT * FROM currency_denominations_cdem';
        $rscurrencyDenomination = FunctionsUtils::runQuery($sSQL);
        mysqli_data_seek($rscurrencyDenomination, 0);
        while ($row = mysqli_fetch_array($rscurrencyDenomination)) {
            $currency = new \stdClass();
            $currency->id = $row['cdem_denominationID'];
            $currency->Name = $row['cdem_denominationName'];
            $currency->Value = $row['cdem_denominationValue'];
            $currency->cClass = $row['cdem_denominationClass'];
            $currencies[] = $currency;
        } // end while

        return $currencies;
    }

    /**
     * Format a fiscal year ID into a human-readable string.
     *
     * @param int $fyId Fiscal year ID
     * @return string Formatted fiscal year (e.g., "2024" or "2023/24")
     */
    public static function formatFiscalYear(int $fyId): string
    {
        if (SystemConfig::getIntValue('iFYMonth') === 1) {
            return (string) (1996 + $fyId);
        } else {
            return (1995 + $fyId) . '/' . mb_substr(1996 + $fyId, 2, 2);
        }
    }

    /**
     * Get Advanced Deposit Report data using ORM
     * 
     * Security: Checks finance permissions before returning data
     *
     * @param string $sort Sort order: 'deposit', 'fund', or 'family'
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @param int|null $depositId Optional deposit ID filter
     * @param array $fundIds Optional fund IDs filter
     * @param array $familyIds Optional family IDs filter
     * @param array $methods Optional payment methods filter
     * @param array $classificationIds Optional classification IDs filter
     * @return array
     */
    public function getAdvancedDepositReportData(
        string $sort = 'deposit',
        string $dateStart = '',
        string $dateEnd = '',
        ?int $depositId = null,
        array $fundIds = [],
        array $familyIds = [],
        array $methods = [],
        array $classificationIds = [],
        string $datetype = 'Payment'
    ): array {
        AuthService::requireUserGroupMembership('bFinance');

        $query = PledgeQuery::create()->filterForAdvancedDeposit(
            $dateStart,
            $dateEnd,
            $fundIds,
            $familyIds,
            $methods,
            $classificationIds,
            $datetype,
            $sort
        );

        // Add deposit ID filter if specified
        if ($depositId > 0) {
            $query->filterByDepId($depositId);
        }

        // Get results and convert to array WITHOUT foreign objects
        // Using addAsColumn() in the query provides Family and Fund names directly
        $collection = $query->find();
        $results = [];
        foreach ($collection as $pledge) {
            $results[] = $pledge->toArray(TableMap::TYPE_PHPNAME, true, [], false);
        }
        return $results;
    }

    /**
     * Get Tax Report (Giving Report) data using ORM
     * 
     * Security: Checks finance permissions before returning data
     *
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @param int|null $depositId Optional deposit ID filter
     * @param int|null $minimumAmount Optional minimum amount filter
     * @param array $fundIds Optional fund IDs filter
     * @param array $familyIds Optional family IDs filter
     * @param array $classificationIds Optional classification IDs filter
     * @return array
     */
    public function getTaxReportData(
        string $dateStart = '',
        string $dateEnd = '',
        ?int $depositId = null,
        ?int $minimumAmount = null,
        array $fundIds = [],
        array $familyIds = [],
        array $classificationIds = []
    ): array {
        AuthService::requireUserGroupMembership('bFinance');

        $query = PledgeQuery::create()->filterForTaxReport(
            $dateStart,
            $dateEnd,
            $fundIds,
            $familyIds,
            $classificationIds
        );

        // Add optional filters
        if ($depositId > 0) {
            $query->filterByDepId($depositId);
        }
        if ($minimumAmount > 0) {
            $query->filterByAmount($minimumAmount, Criteria::GREATER_EQUAL);
        }

        // Get results - DO NOT include foreign objects recursively (causes memory exhaustion)
        // Instead, manually fetch only the Family and DonationFund data we need
        $collection = $query->find();
        $results = [];
        foreach ($collection as $pledge) {
            // Get basic pledge data without foreign objects
            $pledgeData = $pledge->toArray(TableMap::TYPE_PHPNAME, true, [], false);
            
            // Manually add only the Family fields we need for Tax Reports
            $family = $pledge->getFamily();
            if ($family !== null) {
                $pledgeData['Family'] = [
                    'Id' => $family->getId(),
                    'Name' => $family->getName(),
                    'Address1' => $family->getAddress1(),
                    'Address2' => $family->getAddress2(),
                    'City' => $family->getCity(),
                    'State' => $family->getState(),
                    'Zip' => $family->getZip(),
                    'Country' => $family->getCountry(),
                    'Envelope' => $family->getEnvelope(),
                ];
            } else {
                $pledgeData['Family'] = null;
            }
            
            // Manually add only the DonationFund name we need
            $fund = $pledge->getDonationFund();
            if ($fund !== null) {
                $pledgeData['DonationFund'] = [
                    'Id' => $fund->getId(),
                    'Name' => $fund->getName(),
                ];
            } else {
                $pledgeData['DonationFund'] = null;
            }
            
            $results[] = $pledgeData;
        }
        return $results;
    }

    /**
     * Get Zero Givers Report data using ORM
     * 
     * Security: Checks finance permissions before returning data
     * Returns families with members (classification 1) who didn't give in the date range
     *
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @return array
     */
    public function getZeroGiversReportData(string $dateStart = '', string $dateEnd = ''): array
    {
        AuthService::requireUserGroupMembership('bFinance');

        // Get all families with at least one member (classification ID 1)
        $familyQuery = FamilyQuery::create()
            ->usePersonQuery()
                ->filterByClsId(1)
            ->endUse();

        // Get family IDs that made payments in the date range
        $paidFamilyIds = PledgeQuery::create()
            ->filterForZeroGivers($dateStart, $dateEnd)
            ->select(['FamId'])
            ->distinct()
            ->find()
            ->toArray();

        // Flatten the array of arrays to just IDs
        $paidFamilyIds = array_map(function($row) {
            return is_array($row) ? $row[0] : $row;
        }, $paidFamilyIds);

        // Exclude families that made payments
        if (!empty($paidFamilyIds)) {
            $familyQuery->filterById($paidFamilyIds, Criteria::NOT_IN);
        }

        return $familyQuery
            ->orderById()
            ->find()
            ->toArray();
    }

    // =========================================================================
    // Dashboard Methods
    // =========================================================================

    /**
     * Calculate fiscal year date range based on system configuration.
     *
     * @return array{startDate: string, endDate: string, label: string, month: int}
     */
    public function getFiscalYearDates(): array
    {
        $iFYMonth = SystemConfig::getIntValue('iFYMonth');
        $currentYear = (int) date('Y');
        $currentMonth = (int) date('n');

        if ($iFYMonth === 1) {
            // Calendar year fiscal year
            $fyStartDate = $currentYear . '-01-01';
            $fyEndDate = $currentYear . '-12-31';
            $fyLabel = (string) $currentYear;
        } else {
            // Non-calendar fiscal year
            if ($currentMonth >= $iFYMonth) {
                $fyStartYear = $currentYear;
                $fyEndYear = $currentYear + 1;
            } else {
                $fyStartYear = $currentYear - 1;
                $fyEndYear = $currentYear;
            }
            $fyStartDate = $fyStartYear . '-' . str_pad($iFYMonth, 2, '0', STR_PAD_LEFT) . '-01';
            // Calculate end date (last day of month before fiscal year month)
            $endMonth = $iFYMonth - 1;
            if ($endMonth === 0) {
                $endMonth = 12;
            }
            $fyEndDate = $fyEndYear . '-' . str_pad($endMonth, 2, '0', STR_PAD_LEFT) . '-' . date('t', strtotime($fyEndYear . '-' . $endMonth . '-01'));
            $fyLabel = $fyStartYear . '/' . substr((string) $fyEndYear, 2, 2);
        }

        return [
            'startDate' => $fyStartDate,
            'endDate' => $fyEndDate,
            'label' => $fyLabel,
            'month' => $iFYMonth,
        ];
    }

    /**
     * Get deposit statistics (total, open, closed counts).
     *
     * @return array{total: int, open: int, closed: int}
     */
    public function getDepositStatistics(): array
    {
        return [
            'total' => DepositQuery::create()->count(),
            'open' => DepositQuery::create()->filterByClosed(false)->count(),
            'closed' => DepositQuery::create()->filterByClosed(true)->count(),
        ];
    }

    /**
     * Get recent deposits within the current fiscal year.
     *
     * @param int $limit Maximum number of deposits to return
     * @param string|null $fyStartDate Optional fiscal year start date filter
     * @return ObjectCollection Collection of Deposit objects
     */
    public function getRecentDeposits(int $limit = 5, ?string $fyStartDate = null): ObjectCollection
    {
        $query = DepositQuery::create()
            ->orderByDate(Criteria::DESC)
            ->limit($limit);

        // Filter to only show deposits from current fiscal year
        if ($fyStartDate !== null) {
            $query->filterByDate($fyStartDate, Criteria::GREATER_EQUAL);
        }

        return $query->find();
    }

    /**
     * Get Year-to-Date payment total for a fiscal year.
     *
     * @param string $fyStartDate Fiscal year start date
     * @param string $fyEndDate Fiscal year end date
     * @return float|null
     */
    public function getYtdPaymentTotal(string $fyStartDate, string $fyEndDate): ?float
    {
        return PledgeQuery::create()
            ->filterByPledgeOrPayment('Payment')
            ->filterByDate(['min' => $fyStartDate, 'max' => $fyEndDate])
            ->addAsColumn('TotalAmount', 'SUM(' . PledgeTableMap::COL_PLG_AMOUNT . ')')
            ->select(['TotalAmount'])
            ->findOne();
    }

    /**
     * Get Year-to-Date pledge total for a fiscal year.
     *
     * @param string $fyStartDate Fiscal year start date
     * @param string $fyEndDate Fiscal year end date
     * @return float|null
     */
    public function getYtdPledgeTotal(string $fyStartDate, string $fyEndDate): ?float
    {
        return PledgeQuery::create()
            ->filterByPledgeOrPayment('Pledge')
            ->filterByDate(['min' => $fyStartDate, 'max' => $fyEndDate])
            ->addAsColumn('TotalAmount', 'SUM(' . PledgeTableMap::COL_PLG_AMOUNT . ')')
            ->select(['TotalAmount'])
            ->findOne();
    }

    /**
     * Get Year-to-Date payment count for a fiscal year.
     *
     * @param string $fyStartDate Fiscal year start date
     * @param string $fyEndDate Fiscal year end date
     * @return int
     */
    public function getYtdPaymentCount(string $fyStartDate, string $fyEndDate): int
    {
        return PledgeQuery::create()
            ->filterByPledgeOrPayment('Payment')
            ->filterByDate(['min' => $fyStartDate, 'max' => $fyEndDate])
            ->count();
    }

    /**
     * Get count of unique donor families for a fiscal year.
     *
     * @param string $fyStartDate Fiscal year start date
     * @param string $fyEndDate Fiscal year end date
     * @return int|null
     */
    public function getYtdDonorFamilyCount(string $fyStartDate, string $fyEndDate): ?int
    {
        return PledgeQuery::create()
            ->filterByPledgeOrPayment('Payment')
            ->filterByDate(['min' => $fyStartDate, 'max' => $fyEndDate])
            ->addAsColumn('FamilyCount', 'COUNT(DISTINCT ' . PledgeTableMap::COL_PLG_FAMID . ')')
            ->select(['FamilyCount'])
            ->findOne();
    }

    /**
     * Get current deposit from session.
     *
     * @return Deposit|null
     */
    public function getCurrentDeposit(): ?Deposit
    {
        $currentDepositId = $_SESSION['iCurrentDeposit'] ?? null;
        if ($currentDepositId) {
            return DepositQuery::create()->findOneById((int) $currentDepositId);
        }
        return null;
    }

    /**
     * Get current deposit ID from session.
     *
     * @return int|null
     */
    public function getCurrentDepositId(): ?int
    {
        return $_SESSION['iCurrentDeposit'] ?? null;
    }

    /**
     * Get all dashboard data in a single call.
     * 
     * This method consolidates all the dashboard queries into a single 
     * service call to simplify the view layer.
     *
     * @return array Dashboard data including fiscal year info, statistics, and deposits
     */
    public function getDashboardData(): array
    {
        $fiscalYear = $this->getFiscalYearDates();
        $depositStats = $this->getDepositStatistics();
        $currentDeposit = $this->getCurrentDeposit();
        $donationFundService = new DonationFundService();
        $activeFunds = $donationFundService->getAll();

        return [
            'fiscalYear' => $fiscalYear,
            'depositStats' => $depositStats,
            'recentDeposits' => $this->getRecentDeposits(5, $fiscalYear['startDate']),
            'activeFunds' => $activeFunds,
            'activeFundCount' => $activeFunds->count(),
            'totalFundCount' => $donationFundService->getCount(),
            'ytdPaymentTotal' => $this->getYtdPaymentTotal($fiscalYear['startDate'], $fiscalYear['endDate']),
            'ytdPledgeTotal' => $this->getYtdPledgeTotal($fiscalYear['startDate'], $fiscalYear['endDate']),
            'ytdPaymentCount' => $this->getYtdPaymentCount($fiscalYear['startDate'], $fiscalYear['endDate']),
            'ytdDonorFamilies' => $this->getYtdDonorFamilyCount($fiscalYear['startDate'], $fiscalYear['endDate']),
            'currentDeposit' => $currentDeposit,
            'currentDepositId' => $this->getCurrentDepositId(),
        ];
    }
}
