<?php

namespace App\Models;

use CodeIgniter\Model;

/**
 * Unified model for
 *   • undeposited_funds
 *   • deposits_made
 *   • deposits_made_lines
 *   • undeposited_funds_txn_lines
 */
class UndepositedFundsModel extends Model
{
    /* ---------------------------------------------------------------------
       Core table metadata
       --------------------------------------------------------------------- */
    protected $DBGroup          = 'default';
    protected $table            = 'undeposited_funds';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';

    protected $allowedFields = [
        // basic txn columns
        'qb_txn_id', 'txn_type', 'txn_date', 'customer_name', 'payment_method',
        'amount', 'deposit_to_account', 'memo', 'reference_number',
        'is_deposited', 'deposit_txn_id', 'deposit_date', 'journal_reference',
        'created_at',

        // JournalEntry extras
        'debit_amount', 'credit_amount', 'je_description',
        'je_account_name', 'je_line_name',

        // misc
        'payment_ref_num', 'refund_from_account', 'unapplied_amt',
        'exchange_rate', 'currency_ref_name', 'ar_account_ref_name',
        'line_items_json',
    ];

    /* =====================================================================
       1.  Undeposited‑funds upsert (headers + line‑table)
       ===================================================================== */
    public function upsertUndepositedRecord(array $txn, string $txnType): void
    {
        $qbId = $txn['Id'] ?? null;
        if (! $qbId) { return; }

        /* ---------- common columns ---------- */
        $data = [
            'qb_txn_id'        => $qbId,
            'txn_type'         => $txnType,
            'txn_date'         => $txn['TxnDate']            ?? null,
            'memo'             => $txn['PrivateNote']        ?? '',
            'reference_number' => $txn['DocNumber']
                                   ?? ($txn['TxnReferenceNumber'] ?? ''),
            'customer_name'    => $txn['CustomerRef']['name']     ?? '',
            'payment_method'   => $txn['PaymentMethodRef']['name'] ?? '',
            'amount'           => $txn['TotalAmt']           ?? 0.00,
            'is_deposited'     => 0,
        ];
        if (! empty($txn['ExchangeRate']))        { $data['exchange_rate']     = $txn['ExchangeRate']; }
        if (! empty($txn['CurrencyRef']['name'])) { $data['currency_ref_name'] = $txn['CurrencyRef']['name']; }

        /* ---------- type‑specific filters ---------- */
        switch ($txnType) {
            case 'Payment':       if (! $this->qualifyPayment($txn, $data))       { return; } break;
            case 'SalesReceipt':  if (! $this->qualifySalesReceipt($txn, $data))  { return; } break;
            case 'RefundReceipt': if (! $this->qualifyRefundReceipt($txn, $data)) { return; } break;
            case 'CreditMemo':    if (! $this->qualifyCreditMemo($txn, $data))    { return; } break;
            case 'JournalEntry':  if (! $this->qualifyJournalEntry($txn, $data))  { return; } break;
            default: return; // ignore anything else
        }

        /* ---------- header upsert ---------- */
        $existing = $this->where('qb_txn_id', $qbId)->first();
        if ($existing) {
            $this->update($existing['id'], $data);
            $ufId = (int) $existing['id'];
        } else {
            $this->insert($data);
            $ufId = (int) $this->insertID();
        }

        /* ---------- line‑table sync ---------- */
        $this->syncTxnLines($ufId, $txn['Line'] ?? []);
    }

    /* ---------------------------------------------------------------------
       1a.  Qualification helpers (return false to skip)
       --------------------------------------------------------------------- */
    private function qualifyPayment(array $t, array &$data): bool
    {
        $data['payment_ref_num'] = $t['PaymentRefNum'] ?? '';
        $data['unapplied_amt']   = $t['UnappliedAmt']  ?? 0;
        if (! empty($t['ARAccountRef']['name'])) {
            $data['ar_account_ref_name'] = $t['ARAccountRef']['name'];
        }
        if (! empty($t['Line'])) {
            $data['line_items_json'] = json_encode($t['Line']);
        }

        $depName  = $t['DepositToAccountRef']['name'] ?? '';
        $bankName = $t['BankAccountRef']['name']      ?? '';

        if ($depName === '' && $bankName === '') {
            $data['deposit_to_account'] = 'Undeposited Funds';
        } elseif (stripos($depName,  'Undeposited') !== false) { $data['deposit_to_account'] = $depName;
        } elseif (stripos($bankName, 'Undeposited') !== false) { $data['deposit_to_account'] = $bankName;
        } else { return false; }

        return $data['deposit_to_account'] === 'Undeposited Funds';
    }

    private function qualifySalesReceipt(array $t, array &$data): bool
    {
        if (! empty($t['Line'])) { $data['line_items_json'] = json_encode($t['Line']); }
        $depName = $t['DepositToAccountRef']['name'] ?? '';
        if (stripos($depName, 'Undeposited') === false) { return false; }
        $data['deposit_to_account'] = $depName;
        return true;
    }

    private function qualifyRefundReceipt(array $t, array &$data): bool
    {
        $refundAcct = $t['RefundFromAccountRef']['name'] ?? '';
        $depName    = $t['DepositToAccountRef']['name']  ?? '';
        if (stripos($refundAcct, 'Undeposited') !== false) {
            $data['refund_from_account'] = $refundAcct;
            $data['deposit_to_account']  = $refundAcct;
        } elseif (stripos($depName, 'Undeposited') !== false) {
            $data['refund_from_account'] = $depName;
            $data['deposit_to_account']  = $depName;
        } else { return false; }

        if (! empty($t['Line'])) { $data['line_items_json'] = json_encode($t['Line']); }
        return true;
    }

    private function qualifyCreditMemo(array $t, array &$data): bool
    {
        $depName = $t['DepositToAccountRef']['name'] ?? '';
        if (stripos($depName, 'Undeposited') === false) { return false; }
        $data['deposit_to_account'] = $depName;
        if (! empty($t['Line'])) { $data['line_items_json'] = json_encode($t['Line']); }
        return true;
    }

    private function qualifyJournalEntry(array $t, array &$data): bool
    {
        $data['journal_reference'] = $t['DocNumber'] ?? '';
        $debit = $credit = 0.0; $desc = $acct = $name = '';

        foreach ($t['Line'] ?? [] as $ln) {
            $det = $ln['JournalEntryLineDetail'] ?? null;
            if (! $det) { continue; }
            $acctNm = $det['AccountRef']['name'] ?? '';
            if (stripos($acctNm, 'Undeposited Funds') === false) { continue; }

            $posting = $det['PostingType'] ?? '';
            $amt     = (float) ($ln['Amount'] ?? 0);
            if ($posting === 'Debit')  { $debit  += $amt; }
            if ($posting === 'Credit') { $credit += $amt; }

            $desc = $desc ?: ($ln['Description'] ?? '');
            $acct = $acct ?: $acctNm;
            $name = $name ?: ($det['Entity']['EntityRef']['name']
                              ?? ($det['Entity']['Name'] ?? ''));
        }

        if ($debit == 0 && $credit == 0) { return false; }

        $data['debit_amount']    = $debit;
        $data['credit_amount']   = $credit;
        $data['je_description']  = $desc;
        $data['je_account_name'] = $acct;
        $data['je_line_name']    = $name;
        return true;
    }

    /* ---------------------------------------------------------------------
       1b.  Line‑table maintenance (undeposited_funds_txn_lines)
       --------------------------------------------------------------------- */
    private function syncTxnLines(int $ufId, array $lines): void
    {
        $builder = $this->db->table('undeposited_funds_txn_lines');
        $builder->where('uf_id', $ufId)->delete();               // wipe old

        foreach ($lines as $ln) {
            $lineId = $this->extractLineId($ln);
            if ($lineId === null) { continue; }

            $builder->insert([
                'uf_id'       => $ufId,
                'txn_line_id' => (string) $lineId,
                'line_json'   => json_encode($ln),
            ]);
        }
    }

    /** Pick the best available identifier for a QBO line */
    private function extractLineId(array $ln): ?string
    {
        if (isset($ln['Id'])        && $ln['Id']        !== '') { return (string) $ln['Id']; }
        if (isset($ln['TxnLineId']) && $ln['TxnLineId'] !== '') { return (string) $ln['TxnLineId']; }
        if (isset($ln['LineNum'])   && $ln['LineNum']   !== '') { return (string) $ln['LineNum']; }
        return null;
    }

    /* =====================================================================
       2.  Deposit‑header + line upsert (deposits_made / deposits_made_lines)
       ===================================================================== */
    public function upsertDeposit(array $depositData): void
    {
        $depId = $depositData['Id'] ?? null;
        if (! $depId) { return; }

        $db = db_connect();

        /* ----- header ----- */
        $existing = $db->table('deposits_made')
                       ->where('qb_deposit_id', $depId)
                       ->get()->getRowArray();

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

        if ($existing) {
            $db->table('deposits_made')
               ->where('id', $existing['id'])
               ->update($headerRow);
            $headerId = (int) $existing['id'];
        } else {
            $db->table('deposits_made')->insert($headerRow);
            $headerId = (int) $db->insertID();
        }

        /* ----- lines ----- */
        $db->table('deposits_made_lines')
           ->where('deposits_made_id', $headerId)
           ->delete();

        foreach ($depositData['Line'] ?? [] as $line) {
            $lineNum    = $line['Id']          ?? '';
            $lineDesc   = $line['Description'] ?? '';
            $lineAmount = $line['Amount']      ?? 0.00;
            $linkedTxns = $line['LinkedTxn']   ?? [];

            if (empty($linkedTxns)) {
                $this->insertLineRow(
                    $db, $headerId, $lineNum, $lineDesc,
                    $lineAmount, '', ''
                );
            } else {
                foreach ($linkedTxns as $lt) {
                    $this->insertLineRow(
                        $db, $headerId, $lineNum, $lineDesc, $lineAmount,
                        ($lt['TxnType'] ?? ''), ($lt['TxnId'] ?? '')
                    );
                }
            }
        }
    }

    private function insertLineRow($db, int $headerId,
                                   string $lineNum, string $description, float $amount,
                                   string $txnType, string $txnId): void
    {
        $db->table('deposits_made_lines')->insert([
            'deposits_made_id' => $headerId,
            'line_num'         => $lineNum,
            'description'      => $description,
            'amount'           => $amount,
            'linked_txn_type'  => $txnType,
            'linked_txn_id'    => $txnId,
        ]);
    }

    /* =====================================================================
       3.  Convenience look‑ups for filters
       ===================================================================== */
    public function getDistinctCustomers(): array
    {
        $rows = $this->db->table($this->table)
                   ->distinct()
                   ->select('customer_name, je_line_name')
                   ->groupStart()
                       ->where('customer_name !=', '')
                       ->orWhere('je_line_name !=', '')
                   ->groupEnd()
                   ->get()->getResultArray();

        $names = [];
        foreach ($rows as $r) {
            if (! empty($r['customer_name'])) { $names[] = $r['customer_name']; }
            if (! empty($r['je_line_name']))  { $names[] = $r['je_line_name']; }
        }
        $names = array_unique($names);
        sort($names, SORT_STRING);

        return $names;
    }

    public function getDistinctTxnTypes(): array
    {
        $rows = $this->db->table($this->table)
                   ->distinct()
                   ->select('txn_type')
                   ->where('txn_type !=', '')
                   ->orderBy('txn_type', 'ASC')
                   ->get()->getResultArray();

        return array_column($rows, 'txn_type');
    }

    /* =====================================================================
       4.  Utility fetch (all three tables) – optional helper
       ===================================================================== */
    public function fetchAllData(): array
    {
        $db = db_connect();

        return [
            'undeposited_funds'     => $db->table('undeposited_funds')->get()->getResultArray(),
            'deposits_made'         => $db->table('deposits_made')->get()->getResultArray(),
            'deposits_made_lines'   => $db->table('deposits_made_lines')->get()->getResultArray(),
        ];
    }
}
