<?php

namespace App\Models;

use CodeIgniter\Model;

class SpecialKitProcessedModel extends Model
{
    protected $DBGroup    = 'default';
    protected $table      = 'bbt_upsell_tracking';
    protected $primaryKey = 'id';

    /**
     * An array to store log messages that we can output or return later.
     */
    private $logs = [];

    /**
     * Main method to "process special kits."
     *
     * 1) First pass: Find rows in bbt_upsell_tracking with kit_id=184 & special_kit_processed=0.
     *    - For each row:
     *       * Load bbt_subdeals & bbt_subdeal_parts
     *       * Look up bbt_qb_sales_receipts where o_type=0 and update_status=0
     *         to retrieve the correct createdat (if found).
     *         - If not found, SKIP this row (mark it processed so we don’t loop forever).
     *       * Parse JSON to compute retail/cost/gross_profit
     *       * Insert into upsells_processed (with createdat from the bbt_qb_sales_receipts row)
     *       * Mark original row => special_kit_processed=1
     *    - die() if subdeal or part data is missing, unchanged from your original logic.
     *
     * 2) Second pass: For rows in bbt_upsell_tracking where special_kit_processed=0 AND
     *       ( kit_id != 184 OR (kit_id=184 AND gross_profit != 0) ),
     *    - If upsells_processed already has a row with upsell_tracking_id = that row's ID, skip it.
     *    - Otherwise, copy row data (no JSON parsing) but again use bbt_qb_sales_receipts.createdat
     *      if found. If not found, skip the row. Mark original row => special_kit_processed=1 either way.
     *
     * 3) Logs everything, then returns a message.
     */
    public function runSpecialKitProcess()
    {
        $db = db_connect();

        // ========================================
        // PART 1: Process kit_id=184 with JSON logic
        // ========================================
        while (true) {
            // 1) Find the NEXT row in bbt_upsell_tracking => kit_id=184 && special_kit_processed=0
            $row = $db->table('bbt_upsell_tracking')
                      ->where('kit_id', 184)
                      ->where('special_kit_processed', 0)
                      ->get(1) // limit 1
                      ->getRowArray();

            if (! $row) {
                // If no more rows, we're done with this pass.
                $this->logs[] = "No more kit=184 rows to process. Exiting first pass loop.";
                break;
            }

            // For logging:
            $id     = $row['id'];
            $dealId = $row['deal_id'];
            $this->logs[] = "Found bbt_upsell_tracking row => ID={$id}, deal_id={$dealId}, kit_id=184, special_kit_processed=0";

            // 2) Find the bbt_subdeals row for this deal_id
            $subdealRow = $db->table('bbt_subdeals')
                             ->where('deal_id', $dealId)
                             ->get(1)
                             ->getRowArray();
            $this->logs[] = "SQL => SELECT * FROM bbt_subdeals WHERE deal_id={$dealId}";
            $this->logs[] = "Result => " . json_encode($subdealRow);

            if (! $subdealRow) {
                // Pause and show message
                echo "Data doesn't exist in the bbt_subdeals table for deal_id={$dealId}.
(Ship and Continue Processing Button) (Cancel Processing Button)\n\n";
                echo "LOGS:\n" . implode("\n", $this->logs) . "\n\n";
                die();
            }

            $TargetSubDealID = $subdealRow['id'];
            $this->logs[] = "Found bbt_subdeals row => ID={$TargetSubDealID} (subdeal_id).";

            // 3) Find subdeal_parts rows with p_type=1 and special_added_by != null
            $partsRows = $db->table('bbt_subdeal_parts')
                            ->where('subdeal_id', $TargetSubDealID)
                            ->where('p_type', '1')
                            ->where('special_added_by IS NOT NULL')
                            ->get()
                            ->getResultArray();
            $this->logs[] = "SQL => SELECT * FROM bbt_subdeal_parts 
                              WHERE subdeal_id={$TargetSubDealID} 
                                AND p_type='1' 
                                AND special_added_by IS NOT NULL";
            $this->logs[] = "Result => Found " . count($partsRows) . " row(s).";

            // --- Look up the bbt_qb_sales_receipts row for the entire deal
            //     (o_type=0 and update_status=0). If not found, skip this row.
            $salesRow = $db->table('bbt_qb_sales_receipts')
                           ->where('deal_id', $dealId)
                           ->where('o_type', '0')
                           ->where('update_status', '0')
                           ->get(1)
                           ->getRowArray();
            $this->logs[] = "SQL => SELECT * FROM bbt_qb_sales_receipts WHERE deal_id={$dealId} AND o_type='0' AND update_status='0'";
            $this->logs[] = "Result => " . json_encode($salesRow);

            if (! $salesRow) {
                // LOG, mark bbt_upsell_tracking => special_kit_processed=1 to avoid infinite loop, and skip
                $this->logs[] = "No bbt_qb_sales_receipts row found for deal_id={$dealId}, o_type=0, update_status=0. Skipping row ID={$id}.";
                $db->table('bbt_upsell_tracking')
                   ->where('id', $id)
                   ->update(['special_kit_processed' => 1]);
                // Move on to the next row in the loop
                continue;
            }

            // We will use this createdat for ANY upsell rows in this pass:
            $receiptCreatedAt = $salesRow['createdat'];

            // 4) For each part row, parse the BLOB data from bbt_qb_sales_receipts
            foreach ($partsRows as $partRow) {
                $partId         = $partRow['part_id'];
                $specialAddedBy = $partRow['special_added_by'];

                $this->logs[] = "Processing subdeal_part => ID={$partRow['id']}, part_id={$partId}, special_added_by={$specialAddedBy}";

                // 4b) Parse the JSON data
                $jsonBlob    = $salesRow['sales_data'];
                $decodedData = json_decode($jsonBlob, true);

                if (! $decodedData) {
                    $this->logs[] = "Could not parse JSON from bbt_qb_sales_receipts.sales_data for deal_id={$dealId}. Skipping part_id={$partId}.";
                    continue;
                }

                // 4c) Find the matching item by part_id
                $foundItem = null;
                foreach ($decodedData as $item) {
                    if (isset($item['id']) && (string) $item['id'] === (string) $partId) {
                        $foundItem = $item;
                        break;
                    }
                }

                // If no match, pause with a message
                if (! $foundItem) {
                    echo "Part {$partId} does not exist in the JSON Data of deal_id={$dealId}.
(Ship and Continue Processing Button) (Cancel Processing Button)\n\n";
                    echo "LOGS:\n" . implode("\n", $this->logs) . "\n\n";
                    die();
                }

                // 4d) Calculate retail, cost, gross_profit
                $qty         = (float) ($foundItem['qty']    ?? 0);
                $rate        = (float) ($foundItem['rate']   ?? 0);
                $aCost       = (float) ($foundItem['a_cost'] ?? 0);

                $retail      = round($qty * $rate, 2);
                $costValue   = round($qty * $aCost, 2);
                $grossProfit = round($retail - $costValue, 2);

                $this->logs[] = "Part ID={$partId} => qty={$qty}, rate={$rate}, a_cost={$aCost} 
                                 => retail={$retail}, cost={$costValue}, gross_profit={$grossProfit}";

                // 4e) Insert a new row INTO upsells_processed
                $insertData = [
                    'upsell_tracking_id' => $id,  // The original bbt_upsell_tracking row’s ID
                    'deal_id'            => $dealId,
                    'kit_id'             => 184,
                    'retail'             => $retail,
                    'cost'               => $costValue,
                    'gross_profit'       => $grossProfit,
                    'createdat'          => $receiptCreatedAt,
                    'salesman'           => $specialAddedBy,
                ];

                $this->logs[] = "INSERT into upsells_processed => " . json_encode($insertData);
                $db->table('upsells_processed')->insert($insertData);
            }

            // 5) After processing all relevant subdeal_parts, update the original upsell row
            $this->logs[] = "UPDATING original bbt_upsell_tracking row => ID={$id}, SET special_kit_processed=1";
            $db->table('bbt_upsell_tracking')
               ->where('id', $id)
               ->update(['special_kit_processed' => 1]);
        }

        // ========================================
        // PART 2: Copy any remaining rows where:
        //    special_kit_processed=0
        //    AND ( kit_id != 184 OR (kit_id=184 AND gross_profit != 0) )
        // If they do NOT already exist in upsells_processed, copy them.
        // ========================================
        $this->logs[] = "Starting second pass with new condition: " .
                        "special_kit_processed=0 AND (kit_id != 184 OR (kit_id=184 AND gross_profit != 0.00))";

        // Build a query that excludes kit_id=184 rows if gross_profit=0
        $builder = $db->table('bbt_upsell_tracking')
                      ->where('special_kit_processed', 0);

        // (kit_id != 184) OR (kit_id=184 AND gross_profit != 0)
        $builder->groupStart()
                    ->where('kit_id !=', 184)
                    ->orGroupStart()
                        ->where('kit_id', 184)
                        ->where('gross_profit !=', 0)
                    ->groupEnd()
                ->groupEnd();

        $otherRows = $builder->get()->getResultArray();
        $this->logs[] = "Found " . count($otherRows) . " rows for second pass.";

        foreach ($otherRows as $otherRow) {
            $bbtUpsellTrackingID = $otherRow['id'];
            $dealId2            = $otherRow['deal_id'];

            // Check if upsells_processed already has a row where upsell_tracking_id = $bbtUpsellTrackingID
            $exists = $db->table('upsells_processed')
                         ->where('upsell_tracking_id', $bbtUpsellTrackingID)
                         ->countAllResults();

            if ($exists > 0) {
                // Already exists, so no action
                $this->logs[] = "Row ID={$bbtUpsellTrackingID} already in upsells_processed. Skipping.";
                continue;
            }

            // --- Look up bbt_qb_sales_receipts for this row's deal_id,
            //     (o_type=0 and update_status=0). If not found, skip the row.
            $salesRow2 = $db->table('bbt_qb_sales_receipts')
                            ->where('deal_id', $dealId2)
                            ->where('o_type', '0')
                            ->where('update_status', '0')
                            ->get(1)
                            ->getRowArray();
            $this->logs[] = "SQL => SELECT * FROM bbt_qb_sales_receipts WHERE deal_id={$dealId2} AND o_type='0' AND update_status='0'";
            $this->logs[] = "Result => " . json_encode($salesRow2);

            if (! $salesRow2) {
                $this->logs[] = "No bbt_qb_sales_receipts row found for deal_id={$dealId2}, o_type=0, update_status=0. Skipping row ID={$bbtUpsellTrackingID}.";
                // Mark as processed so we don't loop forever
                $db->table('bbt_upsell_tracking')
                   ->where('id', $bbtUpsellTrackingID)
                   ->update(['special_kit_processed' => 1]);
                continue;
            }

            $receiptCreatedAt2 = $salesRow2['createdat'];

            // Copy data directly from bbt_upsell_tracking to upsells_processed, using $receiptCreatedAt2
            $insertData2 = [
                'upsell_tracking_id' => $bbtUpsellTrackingID,
                'deal_id'            => $otherRow['deal_id'],
                'kit_id'             => $otherRow['kit_id'],
                'retail'             => $otherRow['retail'],
                'cost'               => $otherRow['cost'],
                'gross_profit'       => $otherRow['gross_profit'],
                'createdat'          => $receiptCreatedAt2,
                'salesman'           => $otherRow['salesman'],
            ];

            $this->logs[] = "Inserting row ID={$bbtUpsellTrackingID} into upsells_processed => " . json_encode($insertData2);
            $db->table('upsells_processed')->insert($insertData2);

            // Update the original row => special_kit_processed=1
            $db->table('bbt_upsell_tracking')
               ->where('id', $bbtUpsellTrackingID)
               ->update(['special_kit_processed' => 1]);

            $this->logs[] = "Updated bbt_upsell_tracking ID={$bbtUpsellTrackingID} => special_kit_processed=1";
        }

        // ========================================
        // DONE: Output logs + return
        // ========================================
        foreach ($this->logs as $logLine) {
            echo $logLine . "\n";
        }

        return "Kits processed with logging complete!";
    }
}
