<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\MenuModel; 
// Removed: use App\Models\FetchSalesReceiptsModel;

class AuditQuickbooks extends Controller
{
    public function index()
    {
        // 1) Must be logged in
        if (! service('auth')->loggedIn()) {
            return redirect()->to('/login');
        }

        // 2) Must be admin
        $user = service('auth')->user();
        if ((int) $user->is_admin !== 1) {
            return redirect()->to('/')->with('error', 'Access denied: Admins only.');
        }

        // 3) Load menu items for left nav
        $menuModel = new MenuModel();
        $menuItems = $menuModel->orderBy('order', 'ASC')->findAll();

        // 4) Check QuickBooks session
        $session     = session();
        $realmId     = $session->get('realmid');
        $accessToken = $session->get('access_token');
        $isConnected = (! empty($realmId) && ! empty($accessToken));

        // 5) Grab date/form data
        $request    = service('request');
        $fromDate   = $request->getGetPost('from_date');
        $toDate     = $request->getGetPost('to_date');
        $action     = $request->getPost('action');
        $viewSelect = $request->getGetPost('view_select') ?? 'pull';

        // We'll store whether Amazon rows are shown/hidden in a hidden input
        $amazonVisible = ($request->getPost('amazon_visible') === '1');

        // Toggle Amazon display
        if ($action === 'toggle_amazon') {
            $amazonVisible = ! $amazonVisible;
        }

        // Handle view_select redirects
        if ($viewSelect === 'audit') {
            return redirect()->to(
                '/auditliabilityaccounts'
                . '?from_date=' . urlencode($fromDate)
                . '&to_date='   . urlencode($toDate)
                . '&view_select=audit'
            );
        } elseif ($viewSelect === 'duplicates') {
            return redirect()->to(
                '/auditduplicatetransactions'
                . '?from_date=' . urlencode($fromDate)
                . '&to_date='   . urlencode($toDate)
                . '&view_select=duplicates'
            );
        }

        // Prepare data array for the view
        $viewData = [
            'menuItems'      => $menuItems,
            'isConnected'    => $isConnected,
            'error'          => null,
            'from_date'      => $fromDate ?? '',
            'to_date'        => $toDate   ?? '',
            'dbResults'      => [],
            'amazon_visible' => $amazonVisible,
            'journalJson'    => '',
            'view_select'    => $viewSelect,
            'uniqueDebit'    => [],
            'uniqueCredit'   => [],
        ];

        // ---------------------------------------------------------------
        // (A) FETCH SALES RECEIPTS FROM QBO -> upsert into qb_sales_receipts
        // ---------------------------------------------------------------
        if ($action === 'fetch' && $isConnected && ! empty($fromDate) && ! empty($toDate)) {
            $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);
                    $curlError  = curl_error($ch);
                    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; // move to next page
                    } else {
                        $viewData['error'] = "QuickBooks API Error (HTTP code: {$httpCode}):<br>{$response}";
                        break;
                    }
                } while (isset($fetchedCount) && $fetchedCount === $maxResults);

            } catch (\Exception $e) {
                $viewData['error'] = 'Exception while fetching data: ' . $e->getMessage();
            }

            // Manually upsert rows into qb_sales_receipts (NOT bbt_qb_sales_receipts)
            $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);
                }
            }
        }

        // ---------------------------------------------------------------
        // (B) FETCH JOURNAL ENTRIES FROM QBO -> upsert into qb_journal_entries
        // ---------------------------------------------------------------
        if ($action === 'fetch_journal' && $isConnected && ! empty($fromDate) && ! empty($toDate)) {
            $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);
                    $curlError = curl_error($ch);
                    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 {
                        $viewData['error'] = "QuickBooks API Error (HTTP code: {$httpCode}):<br>{$response}";
                        break;
                    }
                } while (isset($fetchedCount) && $fetchedCount === $maxResults);

            } catch (\Exception $e) {
                $viewData['error'] = 'Exception while fetching Journal Entries: ' . $e->getMessage();
            }

            // Upsert into qb_journal_entries
            $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);
                }
            }
        }

        // -----------------------------------------------------------
        // (C) "Populate/Audit JE&SR" (Pass_1_Je + Pass_2_Sr)
        //     *** Left UNCHANGED — references bbt_qb_sales_receipts on purpose. ***
        // -----------------------------------------------------------
        if ($action === 'audit_populate' && $isConnected && ! empty($fromDate) && ! empty($toDate)) {
            $db = db_connect();

            // 1) Find all rows in bbt_qb_sales_receipts within the date range:
            $bbtQbSrRows = $db->table('bbt_qb_sales_receipts')
                ->where('createdat >=', $fromDate . ' 00:00:00')
                ->where('createdat <=', $toDate . ' 23:59:59')
                ->get()
                ->getResultArray();

            // ----------------------------
            // PASS_1_Je (delete-then-insert)
            // ----------------------------
            foreach ($bbtQbSrRows as $row) {
                $targetDealId    = $row['deal_id'];
                $targetInvNo     = $row['inv_no'];
                $targetDealOType = $row['o_type'] ?? '';

                // Match both deal_id AND o_type in bbt_deal_payments
                $dealPayments = $db->table('bbt_deal_payments')
                    ->where('deal_id', $targetDealId)
                    ->where('o_type', $targetDealOType)
                    ->get()
                    ->getResultArray();

                foreach ($dealPayments as $payment) {
                    $jeQbId = $payment['qb_id'] ?? null;
                    if (empty($jeQbId)) {
                        continue;
                    }

                    // Lookup the matching journal entry in qb_journal_entries
                    $jeTable = $db->table('qb_journal_entries');
                    $jeRow   = $jeTable
                        ->where('qb_id', $jeQbId)
                        ->get()
                        ->getRowArray();

                    if (! $jeRow) {
                        continue;
                    }

                    // Update qb_journal_entries.o_type, deal_id, and inv_no
                    $jeTable->where('id', $jeRow['id'])->update([
                        'o_type'  => $targetDealOType,
                        'deal_id' => $targetDealId,
                        'inv_no'  => $targetInvNo,
                    ]);

                    $lines      = json_decode($jeRow['line_items_json'] ?? '[]', true);
                    $auditTable = $db->table('audit_je_to_sr');

                    // 1) Delete all existing PASS_1_Je rows for this deal/payment
                    $auditTable
                        ->where('deal_id', $targetDealId)
                        ->where('type', '0')  // Pass_1_Je
                        ->where('je_id', $jeQbId)
                        ->delete();

                    // 2) Now insert fresh rows
                    foreach ($lines as $line) {
                        if (! isset($line['JournalEntryLineDetail'])) {
                            continue;
                        }

                        $postingType = $line['JournalEntryLineDetail']['PostingType'] ?? '';
                        $acctName    = $line['JournalEntryLineDetail']['AccountRef']['name'] ?? '';
                        $amt         = $line['Amount'] ?? 0;
                        $desc        = $line['Description'] ?? '';

                        if ($postingType !== 'Debit' && $postingType !== 'Credit') {
                            continue;
                        }

                        $dataToInsert = [
                            'deal_id'          => $targetDealId,
                            'type'             => '0', // Pass_1_Je
                            'je_id'            => $jeQbId,
                            'o_type'           => $targetDealOType,
                            'je_discription'   => $desc,
                            'inv_no'           => $targetInvNo,
                            'debit_acct'       => null,
                            'debit_amt'        => null,
                            'credit_acct'      => null,
                            'credit_amt'       => null,
                            'is_debit_liability'  => 0,
                            'is_credit_liability' => 0,
                        ];

                        if ($postingType === 'Debit') {
                            $dataToInsert['debit_acct'] = $acctName;
                            $dataToInsert['debit_amt']  = $amt;

                            if (in_array($acctName, [
                                'Clearing Account',
                                'Non C3 Customer Deposits',
                                'Customer Deposits'
                            ])) {
                                $dataToInsert['is_debit_liability'] = 1;
                            }
                        } else {
                            $dataToInsert['credit_acct'] = $acctName;
                            $dataToInsert['credit_amt']  = $amt;

                            if (in_array($acctName, [
                                'Clearing Account',
                                'Non C3 Customer Deposits',
                                'Customer Deposits'
                            ])) {
                                $dataToInsert['is_credit_liability'] = 1;
                            }
                        }

                        $auditTable->insert($dataToInsert);
                    }
                }
            }

            // ----------------------------
            // PASS_2_Sr
            // ----------------------------
            foreach ($bbtQbSrRows as $row) {
                $targetQBID      = $row['qb_id'];
                $targetDealId    = $row['deal_id'];
                $targetInvNo     = $row['inv_no'];
                $targetDealOType = $row['o_type'] ?? '';

                // Look up the matching qb_sales_receipts row
                $srTable = $db->table('qb_sales_receipts');
                $srRow   = $srTable
                    ->where('qb_id', $targetQBID)
                    ->get()
                    ->getRowArray();

                if (! $srRow) {
                    continue;
                }

                // Update qb_sales_receipts with new fields
                $srTable->where('id', $srRow['id'])->update([
                    'o_type'  => $targetDealOType,
                    'deal_id' => $targetDealId,
                    'inv_no'  => $targetInvNo,
                ]);

                $srTotal = $srRow['total_amt'] ?? 0;
                $srLines = json_decode($srRow['line_items_json'] ?? '[]', true);

                // Map certain item names to numeric codes
                $mapping = [
                    'Customer Deposits'        => '0',
                    'Payment Clearing Account' => '1',
                    'Non C3 Customer Deposits' => '2',
                ];

                $auditTable = $db->table('audit_je_to_sr');

                // 1) Delete existing PASS_2_Sr rows for this SR
                $auditTable
                    ->where('deal_id', $targetDealId)
                    ->where('type', '1')
                    ->where('sr_id', $targetQBID)
                    ->delete();

                // 2) Insert new rows
                foreach ($srLines as $line) {
                    if (! isset($line['SalesItemLineDetail']['ItemRef']['name'])) {
                        continue;
                    }

                    $itemName = $line['SalesItemLineDetail']['ItemRef']['name'];
                    $amount   = $line['Amount'] ?? 0;

                    if (isset($mapping[$itemName])) {
                        $liabilityAct = $mapping[$itemName];

                        $dataToInsert = [
                            'deal_id'       => $targetDealId,
                            'type'          => '1', // Pass_2_Sr
                            'sr_id'         => $targetQBID,
                            'sr_total'      => $srTotal,
                            'liability_act' => $liabilityAct,
                            'liability_amt' => $amount,
                            'inv_no'        => $targetInvNo,
                            'o_type'        => $targetDealOType,
                        ];

                        $auditTable->insert($dataToInsert);
                    }
                }
            }
        }

        // -----------------------------------------------------------
        // (D) "Unique Debit & Credit Accounts"
        // -----------------------------------------------------------
        if ($action === 'unique_accounts') {
            $db = db_connect();

            // Distinct debit accounts (non-null)
            $debitRows = $db->table('audit_je_to_sr')
                ->select('debit_acct')
                ->distinct()
                ->where('debit_acct IS NOT NULL')
                ->get()
                ->getResultArray();
            $uniqueDebit = array_column($debitRows, 'debit_acct');

            // Distinct credit accounts (non-null)
            $creditRows = $db->table('audit_je_to_sr')
                ->select('credit_acct')
                ->distinct()
                ->where('credit_acct IS NOT NULL')
                ->get()
                ->getResultArray();
            $uniqueCredit = array_column($creditRows, 'credit_acct');

            $viewData['uniqueDebit']  = $uniqueDebit;
            $viewData['uniqueCredit'] = $uniqueCredit;
        }

        // -----------------------------------------------------------
        // (E) Show Sales Receipts (from qb_sales_receipts) if we've fetched/toggled
        // -----------------------------------------------------------
        if (
            ($action === 'fetch'
             || $action === 'submit'
             || $action === 'toggle_amazon'
             || $action === 'audit_populate'
             || $action === 'unique_accounts'
            )
            && $isConnected
            && ! empty($fromDate)
            && ! empty($toDate)
        ) {
            $db = db_connect();
            $query = $db->table('qb_sales_receipts')
                        ->where('txn_date >=', $fromDate)
                        ->where('txn_date <=', $toDate);

            // Hide doc_number starting with "INVB-A%" if Amazon is hidden
            if (! $amazonVisible) {
                $query->where('doc_number NOT LIKE', 'INVB-A%');
            }

            $dbResults = $query->orderBy('id', 'DESC')->get()->getResultArray();
            $viewData['dbResults'] = $dbResults;
        }

        // Finally, render the view
        return view('auditquickbooks/index', $viewData);
    }
}
