<?php

namespace App\Models;

use CodeIgniter\Model;

class BbtApiModel extends Model
{
    protected $DBGroup        = 'default';
    protected $table          = '';
    protected $primaryKey     = 'id';
    protected $returnType     = 'array';
    protected $useSoftDeletes = false;
    protected $allowedFields  = [];

    /**
     * Insert or update data for the bbt_deals table.
     */
    public function upsertDeals(array $items)
    {
        $this->table = 'bbt_deals';
        $this->allowedFields = [
            'id',
            'customer_id',
            'total_price',
            'createdat',
            'createdby',
            'make_id',
            'custom_notes',
            'doc_fee_value',
            'doc_fee_checked',
            'lien_fee_value',
            'lien_fee_checked',
            'discount',
            'title_fee_value',
            'title_fee_checked',
            'updatedat',
            'updatedby',
            'cbo',
            'quote_id',
            'd_type',
            'county',
            'salesman',
            'closedat',
            'canceledat',
            'how_find',
            'charge_due',
            'ca_balance',
            'service_id',
            'service_price',
            'service_title',
            'is_hide_subtotal',
            'tradein_price',
            'tradein_tax',
            'financing_option',
            'is_c3',
            'service_tax',
            'internal_notes',
            'is_service_tax',
            'service_tax_percent',
            'original_closedat',
            'mm_names',
            'balance',
            'invs',
            'vins',
            'journey_id',
            'pickup_date',
            'pickup_time',
            'schedule_id',
            'lead_source',
            'distance',
            'deposit_collected_by',
            'customer_care_by',
            'titlework_completed_by',
            'checkout_completed_by',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                $this->insert($item);
            } else {
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Insert or update data for the bbt_qb_sales_receipts table.
     */
    public function upsertQbSalesReceipts(array $items)
    {
        $this->table = 'bbt_qb_sales_receipts';
        $this->allowedFields = [
            'id',
            'inv_no',
            'pdf',
            'deal_id',
            'o_type',
            'createdat',
            'updatedat',
            'update_status',
            'qb_id',
            'is_sync',
            'sales_data',
            'customer_id',
            'total_price',
            'is_charge',
            'qb_customer_id',
            'qb_journal_id',
            'is_sync_journal',
            'cogs',
            'profit',
            'createdby',
            'is_web',
            'model_price',
            'model_cost',
            'model_cnt',
            'labor_total',
            'hours_billed',
            'parts_total',
            'parts_cost',
            'parts_profit',
            'hours_total',
            'hours_cnt',
            'revenue',
            'tax',
            'unit_tax',
            'parts_tax',
            'county_tax',
            'tire_tax',
            'reg_fee',
            'doc_fee',
            'lien_fee',
            'title_fee',
            'discount',
            'shipping',
            'amazon_fee',
            'labor_cost',
            'fee_total',
            'fee_cost',
            'custom_option_total',
            'custom_option_cost',
            'shop_supplies',
            'unit_duty',
            'unit_shipping',
            'is_update',
            'option_total',
            'parts_cnt',
            'promotion_total',
            'promotion_cost',
            'commission_paid',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                // Insert
                $this->insert($item);
            } else {
                // Update if changed
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Insert or update data for the bbt_users table.
     */
    public function upsertUsers(array $items)
    {
        $this->table = 'bbt_users';
        $this->allowedFields = [
            'id',
            'first_name',
            'last_name',
            'useremail',
            'userpwd',
            'usergroup',
            'createdat',
            'sales_username',
            'sales_pass',
            'sales_client_id',
            'sales_client_secret',
            'sales_token',
            'sales_phone',
            'photo',
            'is_tech',
            'calendar_id',
            'menu_id',
            'dial_id',
            'fa_check',
            'fa_check_date',
            'is_backup',
            'is_force_part',
            'pushappid',
            'is_sales',
            'dial_phone',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                $this->insert($item);
            } else {
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Insert or update data for the bbt_subdeal_parts table.
     */
    public function upsertSubdealParts(array $items)
    {
        $this->table = 'bbt_subdeal_parts';
        $this->allowedFields = [
            'id',
            'subdeal_id',
            'part_id',
            'qty',
            'price',
            'p_type',
            'is_installed',
            'is_stocked',
            'is_transferred',
            'kit_id',
            'kit_added_by',
            'cost',
            'special_added_by',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                $this->insert($item);
            } else {
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Insert or update data for the bbt_upsell_tracking table.
     */
    public function upsertUpsellTracking(array $items)
    {
        $this->table = 'bbt_upsell_tracking';
        $this->allowedFields = [
            'id',
            'deal_id',
            'kit_id',
            'retail',
            'cost',
            'gross_profit',
            'createdat',
            'salesman',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                $this->insert($item);
            } else {
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Insert or update data for the bbt_subdeals table.
     */
    public function upsertSubdeals(array $items)
    {
        $this->table = 'bbt_subdeals';
        $this->allowedFields = [
            'id',
            'deal_id',
            'category_id',
            'total_price',
            'model_id',
            'make_id',
            'reg_fee_option',
            'reg_fee_value',
            'tire_tax_value',
            'county_tax_value',
            'model_tax_value',
            'option_tax_value',
            'vin',
            'model_price',
            'co',
            'title',
            'labor_total',
            'tag_no',
            'kit_not_interested',
            'added_kits',
            'website_price',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                $this->insert($item);
            } else {
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * NEW: Insert or update data for the bbt_parts table.
     */
    public function upsertParts(array $items)
    {
        $this->table = 'bbt_parts';
        $this->allowedFields = [
            'id',
            'part_no',
            'description',
            'category',
            'retail',
            'qty_available',
            'modified',
            's_status',
            'vendor',
            'cost',
            'location',
            'qty_instock',
            'qty_committed',
            'qty_onorder',
            'msrp',
            'last_counted',
            'last_sold',
            'last_stocked',
            'reorder_at',
            'reorder_to',
            'mfg_part',
            'total_cost',
            'sku',
            'sale_price',
            'createdat',
            'vendor_id',
            'location_id',
            'markup',
            'taxable',
            'p_type',
            'ave_cost',
            'p_cost',
            'input_type',
            'qty_in',
            'qty_out',
            'is_show',
            'qb_pid',
            'qb_pname',
            'is_tire',
            'weekly_audit',
            'qty_discount',
            'aisle',
            'section',
            'bay',
        ];

        foreach ($items as $item) {
            $existing = $this->where('id', $item['id'])->first();
            if (!$existing) {
                // Insert
                $this->insert($item);
            } else {
                // Update if changed
                if ($this->hasDataChanged($existing, $item)) {
                    $this->update($item['id'], $item);
                }
            }
        }
    }

    /**
     * Helper function to compare existing data with new data.
     */
    private function hasDataChanged(array $existing, array $incoming): bool
    {
        // Basic check using array_diff_assoc
        $diff1 = array_diff_assoc($existing, $incoming);
        $diff2 = array_diff_assoc($incoming, $existing);
        return !empty($diff1) || !empty($diff2);
    }

    /**
     * Retrieve the max ID from bbt_qb_sales_receipts.
     */
    public function getMaxQbSalesReceiptsId(): int
    {
        $result = $this->db->table('bbt_qb_sales_receipts')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    public function getMaxDealsId(): int
    {
        $result = $this->db->table('bbt_deals')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    public function getMaxSubdealPartsId(): int
    {
        $result = $this->db->table('bbt_subdeal_parts')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    public function getMaxUpsellTrackingId(): int
    {
        $result = $this->db->table('bbt_upsell_tracking')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    public function getMaxSubdealsId(): int
    {
        $result = $this->db->table('bbt_subdeals')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    /**
     * NEW: Retrieve the max ID from bbt_parts.
     */
    public function getMaxPartsId(): int
    {
        $result = $this->db->table('bbt_parts')
            ->selectMax('id')
            ->get()
            ->getRowArray();

        return (int) ($result['id'] ?? 0);
    }

    /**
     * NEW: Delete rows in bbt_qb_sales_receipts within [startId..endId]
     *      that are NOT in the $remoteIds array (deleted at source).
     */
    public function deleteMissingQbSalesReceipts(int $startId, int $endId, array $remoteIds)
    {
        $builder = $this->db->table('bbt_qb_sales_receipts');

        $builder->where('id >=', $startId)
                ->where('id <=', $endId);

        // If the chunk returned some IDs, exclude them from deletion:
        if (!empty($remoteIds)) {
            $builder->whereNotIn('id', $remoteIds);
        }

        // Perform the deletion
        $builder->delete();
    }


    public function getMaxOrdersId(): int
{
    $result = $this->db->table('bbt_orders')
        ->selectMax('id')
        ->get()
        ->getRowArray();

    return (int) ($result['id'] ?? 0);
}

public function upsertOrders(array $items)
{
    $this->table = 'bbt_orders';
    $this->allowedFields = [
        'id',
        'cbo',
        'customer_id',
        'total_price',
        'createdat',
        'createdby',
        'o_type',
        'total_amount',
        'closedat',
        'canceledat',
        'is_web',
        'labor_total',
        'charge_due',
        'ca_balance',
        'account_credit',
        'custom_notes',
        'order_po',
        'is_pickup',
        'is_tax',
        'salesman',
        'original_closedat',
        'stock_status',
        'invs',
        'mm_names',
        'balance',
        'is_online_payment'
    ];

    foreach ($items as $item) {
        $existing = $this->where('id', $item['id'])->first();
        if (!$existing) {
            $this->insert($item);
        } else {
            if ($this->hasDataChanged($existing, $item)) {
                $this->update($item['id'], $item);
            }
        }
    }
}

public function deleteMissingOrders(int $startId, int $endId, array $remoteIds)
{
    $builder = $this->db->table('bbt_orders');
    $builder->where('id >=', $startId)
            ->where('id <=', $endId);

    if (!empty($remoteIds)) {
        $builder->whereNotIn('id', $remoteIds);
    }

    $builder->delete();
}

}
