<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\MenuModel;

class AuditLiabilityAccounts 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
        $menuModel = new MenuModel();
        $menuItems = $menuModel->orderBy('order', 'ASC')->findAll();

        // 4) Grab from_date/to_date from either GET or POST
        $fromDate = $this->request->getGet('from_date') ?? $this->request->getPost('from_date') ?? '';
        $toDate   = $this->request->getGet('to_date')   ?? $this->request->getPost('to_date')   ?? '';

        // 5) "View" dropdown selection
        $viewSelect = $this->request->getGet('view_select') ?? $this->request->getPost('view_select') ?? 'audit';
        if ($viewSelect === 'pull') {
            // If user switches back, go to AuditQuickbooks
            return redirect()->to(
                '/auditquickbooks'
                . '?from_date=' . urlencode($fromDate)
                . '&to_date=' . urlencode($toDate)
                . '&view_select=pull'
            );
        }

        // ---------------------------------------------------------------------
        // Existing or placeholder "results" to display in your older example table:
        $results = [];  // placeholder for demonstration

        // ---------------------------------------------------------------------
        // NEW LOGIC: Build the data for the AuditPageTable

        // Edge-case: if the user hasn't chosen a date yet, we might skip or default
        $startDateTime = $fromDate ? $fromDate . ' 00:00:00' : null;
        $endDateTime   = $toDate   ? $toDate   . ' 23:59:59' : null;

        $db = db_connect();

        // 1) Get target rows from bbt_qb_sales_receipts (createdat in range + update_status=0)
        $qbTable = $db->table('bbt_qb_sales_receipts');
        $qbTable->select('*');
        $qbTable->where('update_status', '0');

        if ($startDateTime && $endDateTime) {
            $qbTable->where('createdat >=', $startDateTime);
            $qbTable->where('createdat <=', $endDateTime);
        }

        $targetQbSalesReceiptsRows = $qbTable->get()->getResultArray();

        // 2) Loop over each relevant row from bbt_qb_sales_receipts
        $auditPageResults = [];

        foreach ($targetQbSalesReceiptsRows as $row) {
            $dealId = $row['deal_id'];
            $oType  = $row['o_type'];
            $invNo  = $row['inv_no'];
            $qbId   = $row['id']; // The unique row ID in bbt_qb_sales_receipts
            $notes  = $row['audit_notes'] ?? ''; // The new field

            // Skip row if invoice number starts with 'INVB-A'
            if (substr($invNo, 0, 6) === 'INVB-A') {
                continue;
            }

            // ------------------------------------------
            // Compute JE Liability Amount
            //   Step 1: sum of debit_amt (where is_debit_liability=1), multiplied by 1
            $auditTable = $db->table('audit_je_to_sr');

            $debitRows = $auditTable
                ->select('debit_amt, je_id')
                ->where('deal_id', $dealId)
                ->where('o_type', $oType)
                ->where('is_debit_liability', 1)
                ->get()->getResultArray();

            $sumOfDebit = 0;
            $journalEntriesDebitArray = [];
            foreach ($debitRows as $dr) {
                $debitAmt = (float) $dr['debit_amt'];
                $sumOfDebit += $debitAmt;
                if (!empty($dr['je_id'])) {
                    $journalEntriesDebitArray[] = [
                        'je_id'  => $dr['je_id'],
                        'amount' => $debitAmt
                    ];
                }
            }
            $xyz = $sumOfDebit * 1;

            //   Step 2: sum of credit_amt (where is_credit_liability=1)
            $auditTable = $db->table('audit_je_to_sr');
            $creditRows = $auditTable
                ->select('credit_amt, je_id')
                ->where('deal_id', $dealId)
                ->where('o_type', $oType)
                ->where('is_credit_liability', 1)
                ->get()->getResultArray();

            $sumOfCredit = 0;
            $journalEntriesCreditArray = [];
            foreach ($creditRows as $cr) {
                $creditAmt = (float) $cr['credit_amt'];
                $sumOfCredit += $creditAmt;
                if (!empty($cr['je_id'])) {
                    $journalEntriesCreditArray[] = [
                        'je_id'  => $cr['je_id'],
                        'amount' => $creditAmt
                    ];
                }
            }

            //   Subtract (sumOfDebit * 1) from sumOfCredit => (sumOfCredit) - (xyz)
            $jeLiabilityAmount = $sumOfCredit - $xyz;

            // ------------------------------------------
            // Compute SR Liability Amount
            $auditTable = $db->table('audit_je_to_sr');
            $srRows = $auditTable
                ->select('liability_amt, sr_id')
                ->where('deal_id', $dealId)
                ->where('o_type', $oType)
                ->where('type', '1')
                ->get()->getResultArray();

            $srLiabilityAmount = 0;
            $salesReceiptIdsArray = [];
            foreach ($srRows as $sr) {
                $srLiabilityAmount += (float) $sr['liability_amt'];
                if (!empty($sr['sr_id'])) {
                    $salesReceiptIdsArray[] = $sr['sr_id'];
                }
            }

            // ------------------------------------------
            // Difference = SR Liability + JE Liability
            $difference = $srLiabilityAmount + $jeLiabilityAmount;

            // ------------------------------------------
            // Build final row for the new table
            $auditPageResults[] = [
                'qb_id'                     => $qbId,
                'deal_id'                   => $dealId,
                'o_type'                    => $oType,
                'inv_no'                    => $invNo,
                'je_liability'             => $jeLiabilityAmount,
                'sr_liability'             => $srLiabilityAmount,
                'difference'               => $difference,
                'audit_notes'              => $notes, // pass the notes to the table
                'journalEntriesDebitArray'  => $journalEntriesDebitArray,
                'journalEntriesCreditArray' => $journalEntriesCreditArray,
                'salesReceiptsArray'        => $salesReceiptIdsArray
            ];
        }

        // ---------------------------------------------------------------------
        // Pass data to the view
        $data = [
            'menuItems'        => $menuItems,
            'from_date'        => $fromDate,
            'to_date'          => $toDate,
            'view_select'      => $viewSelect,
            'error'            => null,
            'results'          => $results, // existing table’s data
            'auditPageResults' => $auditPageResults, // data for new “AuditPageTable”
        ];

        return view('auditquickbooks/auditliabilityaccounts', $data);
    }

    /**
     * AJAX endpoint to update the audit_notes field in bbt_qb_sales_receipts.
     */
    public function updateAuditNotes()
    {
        if (! $this->request->isAJAX()) {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Invalid request type'
            ]);
        }

        // Must be logged in
        if (! service('auth')->loggedIn()) {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Unauthorized'
            ]);
        }

        // Must be admin
        $user = service('auth')->user();
        if ((int) $user->is_admin !== 1) {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Access denied: Admins only.'
            ]);
        }

        $qbId = $this->request->getPost('qb_id');
        $newNotes = $this->request->getPost('audit_notes');

        if (! $qbId) {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Missing row ID.'
            ]);
        }

        $db = db_connect();
        $builder = $db->table('bbt_qb_sales_receipts');
        $builder->where('id', $qbId);
        $success = $builder->update(['audit_notes' => $newNotes]);

        if ($success) {
            return $this->response->setJSON(['status' => 'success']);
        } else {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Failed to update record.'
            ]);
        }
    }
}
