<?php

namespace App\Models;

use CodeIgniter\Model;

class AuditQuickbooksModel extends Model
{
    protected $table = 'bbt_qb_sales_receipts';
    protected $primaryKey = 'id';
    protected $allowedFields = [
        'qb_id',
        'inv_no',
        'createdat',
        'updatedat',
        'update_status',
        // etc...
    ];

    /**
     * Find rows in bbt_qb_sales_receipts that failed to properly update 'update_status'
     * after a deal was reopened, optionally filtering by createdat date range.
     *
     * @param  string|null $fromDate  YYYY-MM-DD
     * @param  string|null $toDate    YYYY-MM-DD
     * @return array
     */
    public function getDuplicateTransactions($fromDate = null, $toDate = null)
    {
        // If either date is missing/empty, return an empty array (avoid huge query)
        if (empty($fromDate) || empty($toDate)) {
            return [];
        }

        $db      = db_connect();
        $builder = $db->table($this->table);

        // 1) Filter by createdat date range
        $builder->where('createdat >=', $fromDate . ' 00:00:00');
        $builder->where('createdat <=', $toDate   . ' 23:59:59');

        // 2) Fetch the filtered rows
        $allRows = $builder
            ->orderBy('inv_no', 'ASC')
            ->get()
            ->getResultArray();

        // 3) Group by "base invoice" (strip trailing -A, -B, etc.)
        $groups = [];
        foreach ($allRows as $row) {
            $invNo = $row['inv_no'] ?? '';

            if (preg_match('/^(.*?)(-[A-Za-z]+)$/', $invNo, $matches)) {
                $base   = $matches[1]; 
                $suffix = $matches[2];
            } else {
                $base   = $invNo;
                $suffix = '';
            }

            $row['_base']   = $base;
            $row['_suffix'] = $suffix;

            $groups[$base][] = $row;
        }

        // 4) Identify the "problem" condition
        $problemRows = [];

        foreach ($groups as $base => $rows) {
            // If there's at least one suffix row => reopened
            $hasSuffixRow = false;
            foreach ($rows as $r) {
                if (!empty($r['_suffix'])) {
                    $hasSuffixRow = true;
                    break;
                }
            }

            // If there's a suffix row, check if the base row is still update_status=0
            if ($hasSuffixRow) {
                $baseRowIsStillClosed = false;
                foreach ($rows as $r) {
                    if ($r['inv_no'] === $base && $r['update_status'] === '0') {
                        $baseRowIsStillClosed = true;
                        break;
                    }
                }

                if ($baseRowIsStillClosed) {
                    // Add all rows for that base
                    foreach ($rows as $r) {
                        $problemRows[] = $r;
                    }
                }
            }
        }

        return $problemRows;
    }
}
