<?php

namespace App\Models;

use CodeIgniter\Model;

class PullPaymentTestModel extends Model
{
    protected $table      = 'undeposited_funds';
    protected $primaryKey = 'id';
    protected $allowedFields = [
        'qb_txn_id',
        'txn_type',
        'txn_date',
        'customer_name',
        'payment_method',
        'payment_ref_num',
        'amount',
        'exchange_rate',
        'currency_ref_name',
        'ar_account_ref_name',
        'unapplied_amt',
        'deposit_to_account',
        'refund_from_account',
        'line_items_json',
        'reference_number',
        // etc. (include all columns that exist in your DB)
    ];

    /**
     * Fetch all Payment objects from QBO within a given date range,
     * then upsert them into `undeposited_funds`.
     */
    public function fetchPayments($fromDate, $toDate, $realmId, $accessToken)
    {
        $allPayments   = [];
        $startPosition = 1;
        $maxResults    = 1000;
        $errorMsg      = null;

        // We'll do a paging loop, similar to your existing code
        do {
            // QBO query for Payment objects
            $query = "
                SELECT *
                FROM Payment
                WHERE TxnDate >= '{$fromDate}'
                  AND TxnDate <= '{$toDate}'
                ORDERBY Id
                STARTPOSITION {$startPosition}
                MAXRESULTS {$maxResults}
            ";
            $encodedQuery = urlencode($query);

            $url = "https://quickbooks.api.intuit.com/v3/company/{$realmId}/query?query={$encodedQuery}";

            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_HTTPHEADER, [
                "Authorization: Bearer {$accessToken}",
                "Accept: application/json",
                "Content-Type: application/text",
            ]);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

            $response  = curl_exec($ch);
            $httpCode  = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $curlError = curl_error($ch);
            curl_close($ch);

            if ($httpCode === 200) {
                $decoded      = json_decode($response, true);
                $pageResults  = $decoded['QueryResponse']['Payment'] ?? [];
                $allPayments  = array_merge($allPayments, $pageResults);
                $fetchedCount = count($pageResults);
                $startPosition += $maxResults;
            } else {
                $errorMsg = "QuickBooks API Error (HTTP code: {$httpCode}):<br>{$response}";
                break;
            }
        } while (isset($fetchedCount) && $fetchedCount === $maxResults);

        // Upsert each Payment into undeposited_funds
        $db    = db_connect();
        $table = $db->table($this->table);

        foreach ($allPayments as $payment) {
            $qbId = $payment['Id'] ?? null;
            if (! $qbId) {
                continue;
            }

            // Check if record exists
            $existing = $table
                ->where('qb_txn_id', $qbId)
                ->where('txn_type', 'Payment')
                ->get()
                ->getRowArray();

            // Gather data to upsert
            // If 'DepositToAccountRef' is missing, default to "Undeposited Funds"
            $data = [
                'qb_txn_id'           => $qbId,
                'txn_type'            => 'Payment',
                'txn_date'            => $payment['TxnDate'] ?? null,
                'customer_name'       => $payment['CustomerRef']['name']          ?? null,
                'payment_method'      => $payment['PaymentMethodRef']['name']     ?? null,
                'payment_ref_num'     => $payment['PaymentRefNum']                ?? null,
                'amount'              => $payment['TotalAmt']                      ?? '0.00',
                'exchange_rate'       => $payment['ExchangeRate']                  ?? null,
                'currency_ref_name'   => $payment['CurrencyRef']['name']           ?? null,
                'ar_account_ref_name' => $payment['ARAccountRef']['name']          ?? null,
                'unapplied_amt'       => $payment['UnappliedAmt']                  ?? null,
                'deposit_to_account'  => isset($payment['DepositToAccountRef']['name'])
                    ? $payment['DepositToAccountRef']['name']
                    : 'Undeposited Funds',
                'refund_from_account' => $payment['RefundFromAccountRef']['name']  ?? null,
                'reference_number'    => $payment['DocNumber']                     ?? null,
                'line_items_json'     => json_encode($payment['Line']             ?? []),
            ];

            if ($existing) {
                $table->where('id', $existing['id'])->update($data);
            } else {
                $table->insert($data);
            }
        }

        return [
            'error'   => $errorMsg,
            'records' => $allPayments, // for display if needed
        ];
    }
}
