<?php

namespace App\Models;

use CodeIgniter\Model;

/**
 * A single model to handle the three tables:
 *   - undeposited_funds
 *   - deposits_made
 *   - deposits_made_lines
 */
class UndepositedFundsModel extends Model
{
    protected $DBGroup          = 'default';
    protected $table            = 'undeposited_funds'; // The "main" table
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    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',
        'debit_amount',
        'credit_amount',
        'je_description',
        'je_account_name',
        'je_line_name',
        'deposit_to_account',
        'refund_from_account',
        'is_deposited',
        'deposit_txn_id',
        'deposit_date',
        'memo',
        'line_items_json',
        'reference_number',
        'journal_reference',
        'created_at'
    ];

    /**
     * Fetch all undeposited_funds rows for a given date range.
     */
    public function fetchByDateRange(string $startDate, string $endDate): array
    {
        return $this->where('txn_date >=', $startDate)
                    ->where('txn_date <=', $endDate)
                    ->orderBy('txn_date', 'ASC')
                    ->findAll();
    }

    /**
     * Upsert (insert or update) a record into undeposited_funds table.
     * Example usage in your controller: upsertUndepositedRecord($quickBooksObject, 'Payment')
     */
    public function upsertUndepositedRecord(array $qbObj, string $txnType)
    {
        // Typically you get 'Id' from QuickBooks data, e.g. $qbObj['Id'].
        $qbTxnId = $qbObj['Id'] ?? null;
        if (! $qbTxnId) {
            return;
        }

        // Check if record already exists
        $existing = $this->where('qb_txn_id', $qbTxnId)->first();

        // Convert QB array to your DB fields
        $data = [
            'qb_txn_id'       => $qbTxnId,
            'txn_type'        => $txnType,
            'txn_date'        => $qbObj['TxnDate']         ?? null,
            'customer_name'   => $qbObj['CustomerRef']['name']     ?? null,
            'payment_method'  => $qbObj['PaymentMethodRef']['name'] ?? null,
            'payment_ref_num' => $qbObj['PaymentRefNum']            ?? null,
            'amount'          => $qbObj['TotalAmt']                 ?? 0.00,
            'memo'            => $qbObj['PrivateNote']               ?? null,
            // ... add additional logic if needed ...
        ];

        if ($existing) {
            // Update existing record
            $this->update($existing['id'], $data);
        } else {
            // Insert new
            $this->insert($data);
        }
    }

    /**
     * Upsert deposit header + lines into deposits_made and deposits_made_lines.
     * This is a sample approach to unify all deposit logic here.
     */
    public function upsertDeposit(array $depositData)
    {
        $db = \Config\Database::connect();

        // 1) Upsert into deposits_made
        $qbDepositId = $depositData['Id'] ?? null;
        if (! $qbDepositId) {
            return;
        }

        $headerTable = $db->table('deposits_made');

        // Check existing
        $existingHeader = $headerTable->where('qb_deposit_id', $qbDepositId)->get()->getRowArray();

        $headerPayload = [
            'qb_deposit_id'               => $qbDepositId,
            'deposit_date'                => $depositData['TxnDate']                 ?? null,
            'doc_number'                  => $depositData['DocNumber']               ?? null,
            'deposit_to_account_ref_value'=> $depositData['DepositToAccountRef']['value'] ?? null,
            'deposit_to_account_ref_name' => $depositData['DepositToAccountRef']['name']  ?? null,
            'total_amt'                   => $depositData['TotalAmt']                ?? 0.00,
            'memo'                        => $depositData['PrivateNote']             ?? null,
        ];

        if ($existingHeader) {
            $headerTable->where('id', $existingHeader['id'])->update($headerPayload);
            $headerId = $existingHeader['id'];
        } else {
            $headerTable->insert($headerPayload);
            $headerId = $db->insertID();
        }

        // 2) Upsert deposit lines in deposits_made_lines
        if (! empty($depositData['Line']) && $headerId) {
            $lineTable = $db->table('deposits_made_lines');

            foreach ($depositData['Line'] as $idx => $line) {
                // QuickBooks may or may not have an Id for the line. We'll do a basic approach:
                $linkedTxn = $line['LinkedTxn'][0] ?? [];
                $linkedTxnId   = $linkedTxn['TxnId']   ?? null;
                $linkedTxnType = $linkedTxn['TxnType'] ?? null;

                // Check if there's an existing line with the same deposit header + linked txn
                $existingLine = $lineTable
                                ->where('deposits_made_id', $headerId)
                                ->where('linked_txn_id', $linkedTxnId)
                                ->get()
                                ->getRowArray();

                $linePayload = [
                    'deposits_made_id' => $headerId,
                    'line_num'         => $line['LineNum']       ?? ($idx + 1),
                    'description'      => $line['Description']   ?? '',
                    'amount'           => $line['Amount']        ?? 0.00,
                    'linked_txn_type'  => $linkedTxnType,
                    'linked_txn_id'    => $linkedTxnId,
                ];

                if ($existingLine) {
                    $lineTable->where('id', $existingLine['id'])->update($linePayload);
                } else {
                    $lineTable->insert($linePayload);
                }
            }
        }
    }

    /**
     * Example: fetch all data from all three tables, if you ever want them in one shot.
     */
    public function fetchAllData(): array
    {
        $db = \Config\Database::connect();

        $undepositedFunds = $db->table('undeposited_funds')->get()->getResultArray();
        $depositsMade     = $db->table('deposits_made')->get()->getResultArray();
        $depositLines     = $db->table('deposits_made_lines')->get()->getResultArray();

        return [
            'undeposited_funds' => $undepositedFunds,
            'deposits_made'     => $depositsMade,
            'deposits_made_lines' => $depositLines
        ];
    }
}
