<?php

namespace App\Models;

use CodeIgniter\Model;

class AuditPaymentsModel extends Model
{
    protected $table      = 'bbt_deal_payments';
    protected $primaryKey = 'id';
    protected $returnType = 'array';
    // If your 'id' is truly not auto-increment, set $useAutoIncrement = false;
    // protected $useAutoIncrement = false;

    /**
     * Get a distinct list of p_type values (for the Payment Type dropdown).
     */
    public function getDistinctPaymentTypes(): array
    {
        $builder = $this->db->table($this->table);

        // Use distinct() + select('p_type') instead of selectDistinct('p_type')
        $builder->distinct();
        $builder->select('p_type');

        $query = $builder->get();
        $rows  = $query->getResultArray();

        $types = [];
        foreach ($rows as $row) {
            $types[] = $row['p_type'];
        }
        sort($types);
        return $types;
    }

    /**
     * Get the main data for Audit Payments with the requested filters and pagination.
     *
     * @param array $params
     * @return array
     */
    public function getAuditPayments(array $params): array
    {
        // Extract parameters
        $fromDate    = $params['from_date']    ?? null;
        $toDate      = $params['to_date']      ?? null;
        $pType       = $params['p_type']       ?? 'ALL';
        $limit       = $params['limit']        ?? 'ALL';
        $currentPage = (int)($params['current_page'] ?? 1);

        // Build the base query
        $builder = $this->db->table($this->table);
        $builder->select("
            bbt_deal_payments.id,
            bbt_deal_payments.notes,
            bbt_deal_payments.qb_id,
            bbt_deal_payments.deal_id,
            bbt_deal_payments.amount,
            bbt_deal_payments.createdat,
            bbt_deal_payments.d_type,
            bbt_deal_payments.o_type,
            bbt_qb_sales_receipts.inv_no AS cob_number
        ");
        $builder->join(
            'bbt_qb_sales_receipts',
            'bbt_deal_payments.deal_id = bbt_qb_sales_receipts.deal_id
             AND bbt_deal_payments.o_type = bbt_qb_sales_receipts.o_type',
            'left'
        );

        // Date-range filter
        if (! empty($fromDate)) {
            $builder->where('bbt_deal_payments.createdat >=', $fromDate);
        }
        if (! empty($toDate)) {
            // Add " 23:59:59" to ensure inclusive
            $builder->where('bbt_deal_payments.createdat <=', $toDate . ' 23:59:59');
        }

        // Payment type filter
        if ($pType !== 'ALL') {
            $builder->where('bbt_deal_payments.p_type', $pType);
        }

        // Count total rows (for pagination)
        $countBuilder = clone $builder;
        $totalRows = $countBuilder->countAllResults(false);

        // Determine how to handle the limit
        if ($limit !== 'ALL') {
            $limit = (int) $limit;
            $offset = ($currentPage - 1) * $limit;
            $builder->limit($limit, $offset);
        }

        // Order by createdat descending, or as you prefer
        $builder->orderBy('bbt_deal_payments.createdat', 'DESC');

        // Execute the query for final results
        $query   = $builder->get();
        $results = $query->getResultArray();

        // Compute total pages
        $totalPages = 1;
        if ($limit !== 'ALL') {
            $totalPages = (int) ceil($totalRows / $limit);
            if ($totalPages < 1) {
                $totalPages = 1;
            }
        }

        // Return in a structure similar to your other pages
        return [
            'results'     => $results,
            'totalRows'   => $totalRows,
            'totalPages'  => $totalPages,
            'currentPage' => $currentPage
        ];
    }
}
