<?php

namespace ChurchCRM\model\ChurchCRM;

use ChurchCRM\model\ChurchCRM\Base\PledgeQuery as BasePledgeQuery;
use ChurchCRM\model\ChurchCRM\Map\DepositTableMap;
use ChurchCRM\model\ChurchCRM\Map\DonationFundTableMap;
use ChurchCRM\model\ChurchCRM\Map\FamilyTableMap;
use Propel\Runtime\ActiveQuery\Criteria;

/**
 * Skeleton subclass for performing query and update operations on the 'pledge_plg' table.
 *
 *
 *
 * You should add additional methods to this class to meet the
 * application requirements.  This class will only be generated as
 * long as it does not already exist in the output directory.
 */
class PledgeQuery extends BasePledgeQuery
{
    /**
     * Filter for Tax Report (Giving Report)
     * Payments with optional date range, funds, families, and classifications
     *
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @param array $fundIds Optional fund IDs to filter
     * @param array $familyIds Optional family IDs to filter
     * @param array $classificationIds Optional classification IDs to filter
     * @return self
     */
    public function filterForTaxReport(
        string $dateStart = '',
        string $dateEnd = '',
        array $fundIds = [],
        array $familyIds = [],
        array $classificationIds = []
    ): self {
        $this->filterByPledgeOrPayment('Payment');

        if (!empty($dateStart)) {
            $this->filterByDate($dateStart, Criteria::GREATER_EQUAL);
        }
        if (!empty($dateEnd)) {
            $this->filterByDate($dateEnd, Criteria::LESS_EQUAL);
        }
        if (!empty($fundIds)) {
            $this->filterByFundId($fundIds, Criteria::IN);
        }
        if (!empty($familyIds)) {
            $this->filterByFamId($familyIds, Criteria::IN);
        }
        // Note: Classification filtering is complex and requires post-processing
        // as it involves a relationship through ListOption. Can be added to service layer if needed.

        return $this->leftJoinWithFamily()
            ->leftJoinWithDonationFund()
            ->leftJoinWithPerson()
            ->orderByFamId()
            ->orderByDate();
    }

    /**
     * Filter for Advanced Deposit Report
     * Payments with sorting, date range, funds, families, methods, and classifications
     *
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @param array $fundIds Optional fund IDs to filter
     * @param array $familyIds Optional family IDs to filter
     * @param array $methods Optional payment methods to filter
     * @param array $classificationIds Optional classification IDs to filter
     * @param string $datetype 'Payment' for pledge date, 'Deposit' for deposit date
     * @param string $sort Sort order: 'deposit', 'fund', or 'family'
     * @return self
     */
    public function filterForAdvancedDeposit(
        string $dateStart = '',
        string $dateEnd = '',
        array $fundIds = [],
        array $familyIds = [],
        array $methods = [],
        array $classificationIds = [],
        string $datetype = 'Payment',
        string $sort = 'deposit'
    ): self {
        $this->filterByPledgeOrPayment('Payment');

        // Apply date filtering based on selected datetype
        // CRITICAL: Use innerJoinWithDeposit (INNER JOIN) when filtering by deposit date
        // This ensures deposits exist and date filtering works correctly (matches 5.22.0 SQL behavior)
        if ($datetype === 'Deposit') {
            // Inner join with deposit and filter by deposit date.
            // Both date conditions must be applied within a single useDepositQuery() block.
            // Calling useDepositQuery() twice generates two separate query contexts with
            // different aliases (e.g. deposit_1, deposit_2), making it impossible for a
            // single pledge row to satisfy both conditions simultaneously — every deposit
            // date filter query would return no results.
            $this->innerJoinWithDeposit();
            if (!empty($dateStart) || !empty($dateEnd)) {
                $depositQuery = $this->useDepositQuery();
                if (!empty($dateStart)) {
                    $depositQuery->filterByDate($dateStart, Criteria::GREATER_EQUAL);
                }
                if (!empty($dateEnd)) {
                    $depositQuery->filterByDate($dateEnd, Criteria::LESS_EQUAL);
                }
                $depositQuery->endUse();
            }
        } else {
            // Filter by payment date
            if (!empty($dateStart)) {
                $this->filterByDate($dateStart, Criteria::GREATER_EQUAL);
            }
            if (!empty($dateEnd)) {
                $this->filterByDate($dateEnd, Criteria::LESS_EQUAL);
            }
            // For payment date, left join is fine since deposit is optional
            $this->leftJoinWithDeposit();
        }
        
        if (!empty($fundIds)) {
            $this->filterByFundId($fundIds, Criteria::IN);
        }
        if (!empty($familyIds)) {
            $this->filterByFamId($familyIds, Criteria::IN);
        }
        // Handle payment methods - use IN clause (supports array)
        if (!empty($methods)) {
            $this->filterByMethod($methods, Criteria::IN);
        }
        // Note: Classification filtering is complex and requires post-processing
        // as it involves a relationship through ListOption. Can be added to service layer if needed.

        // Left joins for optional relationships
        $this->leftJoinWithFamily()
            ->leftJoinWithDonationFund()
            ->leftJoinWithPerson();

        // Add columns from joined tables to avoid needing foreign objects in toArray()
        $this->withColumn(FamilyTableMap::COL_FAM_NAME, 'FamilyName')
            ->withColumn(FamilyTableMap::COL_FAM_ADDRESS1, 'FamilyAddress1')
            ->withColumn(FamilyTableMap::COL_FAM_ADDRESS2, 'FamilyAddress2')
            ->withColumn(FamilyTableMap::COL_FAM_CITY, 'FamilyCity')
            ->withColumn(FamilyTableMap::COL_FAM_STATE, 'FamilyState')
            ->withColumn(FamilyTableMap::COL_FAM_ZIP, 'FamilyZip')
            ->withColumn(FamilyTableMap::COL_FAM_COUNTRY, 'FamilyCountry')
            ->withColumn(DonationFundTableMap::COL_FUN_NAME, 'FundName')
            ->withColumn(DepositTableMap::COL_DEP_DATE, 'DepositDate');

        // Apply sorting
        if ($sort === 'fund') {
            $this->orderByFundId()
                ->orderByFamId();
        } elseif ($sort === 'family') {
            $this->orderByFamId()
                ->orderByFundId();
        } else {
            // default: 'deposit'
            $this->orderByDepId()
                ->orderByFundId()
                ->orderByFamId();
        }

        return $this;
    }

    /**
     * Filter for Zero Givers Report
     * Get pledges by date range and optional filters
     *
     * @param string $dateStart Start date (Y-m-d format)
     * @param string $dateEnd End date (Y-m-d format)
     * @return self
     */
    public function filterForZeroGivers(
        string $dateStart = '',
        string $dateEnd = ''
    ): self {
        $this->filterByPledgeOrPayment('Payment');

        if (!empty($dateStart)) {
            $this->filterByDate($dateStart, Criteria::GREATER_EQUAL);
        }
        if (!empty($dateEnd)) {
            $this->filterByDate($dateEnd, Criteria::LESS_EQUAL);
        }

        return $this->orderByFamId();
    }
}
