<?php

namespace App\Libraries\undeposited_funds;

class PullSalesReceiptsAndJournalEntries
{
    /**
     * Fetch sales receipts from QuickBooks Online and upsert into qb_sales_receipts table.
     *
     * @return string|null Error message or null on success.
     */
    public function fetchSalesReceipts(string $realmId, string $accessToken, string $fromDate, string $toDate): ?string
    {
        $allReceipts   = [];
        $startPosition = 1;
        $maxResults    = 1000;

        try {
            do {
                $query = "
                        SELECT *
                        FROM SalesReceipt
                        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);
                curl_close($ch);

                if ($httpCode === 200) {
                    $decoded      = json_decode($response, true);
                    $pageResults  = $decoded['QueryResponse']['SalesReceipt'] ?? [];
                    $allReceipts  = array_merge($allReceipts, $pageResults);
                    $fetchedCount = count($pageResults);

                    $startPosition += $maxResults;
                } else {
                    return "QuickBooks API Error (HTTP code: {$httpCode}):<br>{$response}";
                }
            } while (isset($fetchedCount) && $fetchedCount === $maxResults);
        } catch (\Throwable $e) {
            return 'Exception while fetching data: ' . $e->getMessage();
        }

        $db      = db_connect();
        $srTable = $db->table('qb_sales_receipts');

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

            $existing = $srTable->where('qb_id', $qbId)->get()->getRowArray();

            $data = [
                'qb_id'                       => $qbId,
                'domain'                      => $receipt['domain']             ?? '',
                'sparse'                      => ! empty($receipt['sparse']) ? $receipt['sparse'] : '',
                'sync_token'                  => $receipt['SyncToken']          ?? '',
                'create_time'                 => $receipt['MetaData']['CreateTime']       ?? '',
                'last_updated_time'           => $receipt['MetaData']['LastUpdatedTime']  ?? '',
                'doc_number'                  => $receipt['DocNumber']          ?? '',
                'txn_date'                    => $receipt['TxnDate']            ?? '',
                'currency_ref_value'          => $receipt['CurrencyRef']['value'] ?? '',
                'currency_ref_name'           => $receipt['CurrencyRef']['name']  ?? '',
                'private_note'                => $receipt['PrivateNote']         ?? '',
                'linked_txn_json'             => json_encode($receipt['LinkedTxn'] ?? []),
                'line_items_json'             => json_encode($receipt['Line'] ?? []),
                'txn_tax_code_ref_value'      => $receipt['TxnTaxDetail']['TxnTaxCodeRef']['value'] ?? '',
                'total_tax'                   => $receipt['TxnTaxDetail']['TotalTax']     ?? '0',
                'tax_line_json'               => json_encode($receipt['TxnTaxDetail']['TaxLine'] ?? []),
                'customer_ref_value'          => $receipt['CustomerRef']['value']     ?? '',
                'customer_ref_name'           => $receipt['CustomerRef']['name']      ?? '',
                'bill_addr_id'                => $receipt['BillAddr']['Id']       ?? '',
                'bill_addr_line1'             => $receipt['BillAddr']['Line1']    ?? '',
                'bill_addr_city'              => $receipt['BillAddr']['City']     ?? '',
                'bill_addr_country'           => $receipt['BillAddr']['Country']  ?? '',
                'bill_addr_country_subdivision_code' => $receipt['BillAddr']['CountrySubDivisionCode'] ?? '',
                'bill_addr_postal_code'       => $receipt['BillAddr']['PostalCode']       ?? '',
                'ship_addr_id'                => $receipt['ShipAddr']['Id']       ?? '',
                'ship_addr_city'              => $receipt['ShipAddr']['City']     ?? '',
                'ship_addr_country'           => $receipt['ShipAddr']['Country']  ?? '',
                'ship_addr_country_subdivision_code' => $receipt['ShipAddr']['CountrySubDivisionCode'] ?? '',
                'ship_addr_postal_code'       => $receipt['ShipAddr']['PostalCode']       ?? '',
                'total_amt'                   => $receipt['TotalAmt']            ?? '0',
                'apply_tax_after_discount'    => $receipt['ApplyTaxAfterDiscount']    ?? '',
                'print_status'                => $receipt['PrintStatus']         ?? '',
                'email_status'                => $receipt['EmailStatus']         ?? '',
                'balance'                     => $receipt['Balance']             ?? '0',
                'payment_ref_num'             => $receipt['PaymentRefNum']       ?? '',
                'deposit_to_account_ref_value'=> $receipt['DepositToAccountRef']['value'] ?? '',
                'deposit_to_account_ref_name' => $receipt['DepositToAccountRef']['name']  ?? '',
                'raw_json'                    => json_encode($receipt),
            ];

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

        return null;
    }

    /**
     * Fetch journal entries from QuickBooks Online and upsert into qb_journal_entries table.
     *
     * @return string|null Error message or null on success.
     */
    public function fetchJournalEntries(string $realmId, string $accessToken, string $fromDate, string $toDate): ?string
    {
        $allJournals  = [];
        $startPosition = 1;
        $maxResults    = 1000;

        try {
            do {
                $query = "
                        SELECT *
                        FROM JournalEntry
                        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);
                curl_close($ch);

                if ($httpCode === 200) {
                    $decoded      = json_decode($response, true);
                    $pageResults  = $decoded['QueryResponse']['JournalEntry'] ?? [];
                    $allJournals  = array_merge($allJournals, $pageResults);
                    $fetchedCount = count($pageResults);

                    $startPosition += $maxResults;
                } else {
                    return "QuickBooks API Error (HTTP code: {$httpCode}):<br>{$response}";
                }
            } while (isset($fetchedCount) && $fetchedCount === $maxResults);
        } catch (\Throwable $e) {
            return 'Exception while fetching Journal Entries: ' . $e->getMessage();
        }

        $db           = db_connect();
        $journalTable = $db->table('qb_journal_entries');

        foreach ($allJournals as $journal) {
            $qbId = $journal['Id'] ?? null;
            if (! $qbId) {
                continue;
            }
            $existing = $journalTable->where('qb_id', $qbId)->get()->getRowArray();

            $data = [
                'qb_id'             => $qbId,
                'adjustment'        => (! empty($journal['Adjustment'])) ? 1 : 0,
                'domain'            => $journal['domain']  ?? '',
                'sparse'            => (! empty($journal['sparse'])) ? 1 : 0,
                'sync_token'        => $journal['SyncToken']  ?? '',
                'create_time'       => $journal['MetaData']['CreateTime']   ?? '',
                'last_updated_time' => $journal['MetaData']['LastUpdatedTime'] ?? '',
                'doc_number'        => $journal['DocNumber']  ?? '',
                'txn_date'          => $journal['TxnDate']  ?? '',
                'currency_ref_value' => $journal['CurrencyRef']['value']   ?? '',
                'currency_ref_name'  => $journal['CurrencyRef']['name']   ?? '',
                'line_items_json'   => json_encode($journal['Line']  ?? []),
                'txn_tax_detail_json' => json_encode($journal['TxnTaxDetail'] ?? []),
                'raw_json'          => json_encode($journal),
            ];

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

        return null;
    }
}
