<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
App::uses('UtilityController', 'Controller');
App::uses('AppController', 'Controller');
App::uses('CakeTime', 'Utility');
App::uses('ConnectionManager', 'Model');
/**
 * CakePHP ApisController
 * @author aetherss
 */
class CronController extends AppController {
    public function beforeFilter() {
            parent::beforeFilter();
            // Allow users to register and logout.
            $this->Auth->allow();
            $this->layout = false;
            $this->autoRender = false;
            error_reporting(0);
        }
        
    public function index()
    {
        echo "Welcome to PhyloPos";
    }
 
    public function close_open_table()
    {
        $this->autoRender = false;
        $this->layout = false;
        $this->loadModel('Order_table');
        $currentTime = date('Y-m-d H:i:s');

        try {
            $result = $this->Order_table->query("UPDATE order_tables SET status = '1', modified='$currentTime'  WHERE status = 2");
    
            $response = [
                'status' => true,
                'message' => 'done..!',
            ];
        } catch (Exception $e) {
            $response = [
                'status' => false,
                'message' => 'Failed: ' . $e->getMessage(),
            ];
        }
    
        echo json_encode($response);
        exit;
    }
   
    public function sync_to_pos()
    {
        // Get all unprocessed online orders
        $webhooks = $this->Online_order->find('all', [
            'conditions' => ['Online_order.cron_run' => 0],
            'limit' => 10,
            'order' => ['Online_order.id' => 'ASC']
        ]);
    
        foreach ($webhooks as $webhook) {
            $remarks = [];
            $data = [];
    
            $response = json_decode($webhook['Online_order']['response']);
            $txnid = $webhook['Online_order']['reference_id'];
    
            if (empty($response->order->items)) {
                $remarks[] = "Missing order items for txn $txnid";
                continue;
            }
            
            $trxchk=$this->Order_transaction->query("SELECT id FROM `order_transactions` WHERE reference_id='$txnid'");
            if(empty($trxchk)){
            
                $items = $response->order->items;
                $details = $response->order->details;
                $store = $response->store;
                $payments = $response->order->payment ?? [];
        
                $deliverydatetime = date("Y-m-d H:i:s", $details->delivery_datetime / 1000);
                $created = date("Y-m-d H:i:s", $details->created / 1000);
        
                // Initialize totals
                $total_tax_value = 0;
                $total_pack_charge = 0;
                $total_pack_taxes= 0;
                
                // Calculate packaging charges
                if (!empty($details->charges)) {
                    foreach ($details->charges as $char) {
                        $title = strtolower($char->title);
                        if ($title === 'packaging charge' || $title === 'restaurant packaging charges') {
                            $total_pack_charge += $char->value;
                            foreach ($char->taxes as $char_taxes) {
                                $total_pack_taxes += $char_taxes->value;
                            }
                        }
                    }
                }
                
                // Process each item
                $total_amount=0;
                foreach ($items as $item) {
                    $item_tax_value = 0;
                    $item_tax_rate = 0;
        
                    // Sum item taxes
                    if (!empty($item->taxes)) {
                        foreach ($item->taxes as $tax) {
                            $item_tax_value += $tax->value;
                            $item_tax_rate += $tax->rate;
                        }
                    }
                    $total_tax_value += $item_tax_value;
        
                    // Total price per item after discount
                    $ttlprice = ($item->price * $item->quantity) - ($item->discount ?? 0);
                    $total_amount += $ttlprice;
                    // Save order history
                    // $orderHistoryData = [
                    //     'reference_id'         => $txnid,
                    //     'cashier_id'           => 0,
                    //     'category'             => $item->category->id?$item->category->id:0,
                    //     'category_name'        => $item->category->name?$item->category->name:'',
                    //     'product_id'           => $item->merchant_id?$item->merchant_id:0,
                    //     'product_name'         => $item->title?$item->title:'',
                    //     'quantity'             => $item->quantity?$item->quantity:0,
                    //     'price'                => $item->price?$item->price:0,
                    //     'total_tax'            => $item_tax_value?$item_tax_value:0,
                    //     'total_tax_percentage' => $item_tax_rate?$item_tax_rate:0,
                    //     'total_price'          => $ttlprice?$ttlprice:0,
                    //     'discount'             => $item->discount ?? 0,
                    //     'status'               => 2
                    // ];
                    $orderHistoryData = [
                        'reference_id'         => $txnid,
                        'cashier_id'           => 2,
                        'category'             => $item->category->id?$item->category->id:0,
                        'category_name'        => $item->category->name?$item->category->name:'',
                        'product_id'           => $item->merchant_id?$item->merchant_id:0,
                        'product_name'         => $item->title?$item->title:'',
                        'quantity'             => $item->quantity?$item->quantity:0,
                        'price'                => $item->price?$item->price:0,
                        'total_tax'            => 0,
                        'total_tax_percentage' => 0,
                        'total_price'          => $ttlprice?$ttlprice:0,
                        'discount'             => $item->discount ?? 0,
                        'status'               => 2,
                        'created'              => $created
                    ];
                    
                    $this->Order_orderhistory->create();
                    $this->Order_orderhistory->save($orderHistoryData);
                    $lastorder_id = $this->Order_orderhistory->getLastInsertID();
    
                    $discount_percentage= ( $item->discount * 100 ) / $item->price ;
                    // Save taxes per item
                    
                    $ordertaxData = [
                        'orderhistory_id' => $lastorder_id, // Fill after saving order history if needed
                        'cashier_id'      => 2,
                        'product_id'      => $item->merchant_id ?? 0,
                        'product_name'    => $item->title ?? '',
                        'price'           => $item->price ?? 0,
                        'taxable_price'   => $ttlprice ?? 0,
                        'cat_id'          => $item->category->id ?? 0,
                        'cat_name'        => $item->category->name ?? '',
                        'discount_percentage' => round($discount_percentage,2),
                        'quantity'        => $item->quantity ?? 0,
                        'tax_id'          => 0,
                        'tax_name'        => 'Online',
                        'tax_percentage'  => 0,
                        'tax_value'       => 0,
                        'reference_id'    => $txnid ?? 0,
                        'status'          => 2,
                        'created'         => $created
                    ];
                    $this->Order_tax->create();
                    $this->Order_tax->save($ordertaxData);
                }
                    
                $currenttime = date("H:i:s", $details->delivery_datetime / 1000);
                
                $finalamount = round($total_amount);        
                $round_amount = round($total_amount,2)-round($total_amount);
                
                $todayDate = date("Y-m-d");
                $tdaytrx=$this->Order_transaction->query("SELECT COUNT(id) as tkn FROM `order_transactions` WHERE date(created)='$todayDate'");
                $token=$tdaytrx['0']['0']['tkn']+1;
                
                $deliveryother=0;
                $deliveryother=round((($details->total_charges)-$total_pack_charge),2);


                // Save transaction
                $data['Order_transaction'][] = [
                        'reference_id'=>$txnid,
                        'cashier_id'=>2,
                        'price'=>$details->order_subtotal ?? 0,
                        'total_tax'=> 0,
                        'other_taxes'=>$total_pack_taxes ?? 0,
                        'discount'=>$details->discount ?? 0,
                        'discount_type'=>2,
                        'discount_type_value'=>$details->discount ?? 0,
                        'amount'=>round($total_amount ?? 0),
                        'round'=>round($round_amount,2),
                        'delivery_charge'=>$deliveryother ?? 0,
                        'packing_charge' => $total_pack_charge ?? 0,
                        'name' => $response->customer->name ?? '',
                        'phone' => $response->customer->phone ?? '',
                        'address' => $response->customer->address->city ?? '',
                        'createtime'=>$currenttime,
                        'token'=>$token,
                        'payment_mode'=>$response->order->details->channel,
                        'status'           => 2,
                        'created'    => $created
                    ];
                
                if (!empty($data['Order_transaction'])) {
                    $this->Order_transaction->saveAll($data['Order_transaction']);
                }
                
                // Save payments
                if (!empty($payments)) {
                    foreach ($payments as $payment) {
                        $data['Order_payment'][] = [
                            'reference_id' => $txnid,
                            'cashier_id'   =>  2,
                            'type'         => '9', // Map your payment type
                            'type_value'   => $details->channel ?? '',
                            'amount'       => round($total_amount) ?? 0,
                            'remarks'      => $payment->option ?? '' ,
                            'status'       => 2,
                            'created'      => $created
                        ];
                    }
                }
                
                if (!empty($data['Order_payment'])) {
                    $this->Order_payment->saveAll($data['Order_payment']);
                }
                $this->Online_order->updateAll(
                    ['Online_order.cron_run' => 1],
                    ['Online_order.id' => $webhook['Online_order']['id']]
                );
                
                $response1 =array(
                    'status'=> true,
                    'message'=> "Synced order $txnid successfully.",
                );
            }
        }
        return json_encode($response1);
    }

    public function food_cost_product_update()
    {
        $this->autoRender = false;
        $this->layout = false;
        
        $currentTime = date('Y-m-d H:i:s');
        
        // $costprices = $this->Inventory_linking->query("SELECT il.parent_id AS product_id, ROUND(SUM( CASE WHEN il.component_type = 'SKU' THEN il.quantity * COALESCE(s.price, 0) WHEN il.component_type = 'Semifinished' THEN il.quantity * COALESCE(sf.food_cost, 0) ELSE 0 END ), 2) AS total_component_cost FROM inventory_linking il LEFT JOIN inventory_skus s ON il.component_type = 'SKU' AND il.component_id = s.id LEFT JOIN inventory_semifinished sf ON il.component_type = 'Semifinished' AND il.component_id = sf.id WHERE il.parent_type = 'Product' GROUP BY il.parent_id ORDER BY il.parent_id; ");
        $costprices = $this->Inventory_linking->query("
            SELECT 
                il.parent_id AS product_id,
            
                ROUND(
                    SUM(
                        CASE 
                            WHEN il.component_type = 'SKU' 
                                THEN il.quantity * COALESCE(s.price, 0)
            
                            WHEN il.component_type = 'Semifinished' 
                                THEN il.quantity * COALESCE(sf.food_cost, 0)
            
                            ELSE 0
                        END
                    ),
                2) AS total_component_cost
            
            FROM inventory_linking il
            
            LEFT JOIN inventory_skus s 
                ON il.component_type = 'SKU' 
               AND il.component_id = s.id
               AND s.status = 1
            
            LEFT JOIN inventory_semifinished sf 
                ON il.component_type = 'Semifinished' 
               AND il.component_id = sf.id
               AND sf.status = 1
            
            WHERE il.parent_type = 'Product'
              AND il.status = 1
            
            GROUP BY il.parent_id
            ORDER BY il.parent_id
        ");
        
        foreach($costprices as $costprice){
            $product_id= $costprice['il']['product_id'];
            $total_sku_cost= $costprice[0]['total_component_cost'];
            
            $this->Catalog_product->id = $product_id;
            
            if ($this->Catalog_product->exists()) {
                $saved  = $this->Catalog_product->save([
                    'id' => $product_id,
                    'food_cost' => $total_sku_cost,
                    'modified' => $currentTime
                ]);
                
                $response[] = [
                    'product_id' => $product_id,
                    'food_cost' => $total_sku_cost,
                    'status' => $saved ? 'updated' : 'failed'
                ];
            } else {
                
            }

            
        }
        
        echo json_encode([
            'status' => true,
            'message' => 'Food cost updated for products',
            'data' => $response
        ]);
        exit;
    }
    
    public function food_cost_semifinished_update()
    {
        
        $this->autoRender = false;
        $this->layout = false;
        
        $currentTime = date('Y-m-d H:i:s');
        
        // $costprices = $this->Inventory_linking->query("WITH RECURSIVE bom AS ( SELECT l.parent_id AS semifinished_id, l.component_type, l.component_id, l.component_name, CAST(l.quantity AS DECIMAL(18,6)) AS qty FROM inventory_linking l WHERE l.parent_type = 'Semifinished' UNION ALL SELECT bom.semifinished_id, l.component_type, l.component_id, l.component_name, bom.qty * CAST(l.quantity AS DECIMAL(18,6)) AS qty FROM bom JOIN inventory_linking l ON l.parent_type = 'Semifinished' AND l.parent_id = bom.component_id ) SELECT bom.semifinished_id, ROUND(SUM(bom.qty * COALESCE(s.price, 0)) / NULLIF(sf.yield, 0), 2) AS cost_per_unit FROM bom JOIN inventory_skus s ON bom.component_type = 'SKU' AND bom.component_id = s.id JOIN inventory_semifinished sf ON bom.semifinished_id = sf.id GROUP BY bom.semifinished_id, sf.yield; ");
        $costprices = $this->Inventory_linking->query("
            WITH RECURSIVE bom AS (
            
                -- Base Level
                SELECT 
                    l.parent_id AS semifinished_id,
                    l.component_type,
                    l.component_id,
                    l.component_name,
                    CAST(l.quantity AS DECIMAL(18,6)) AS qty
                FROM inventory_linking l
                WHERE l.parent_type = 'Semifinished'
                  AND l.status = 1
            
                UNION ALL
            
                -- Recursive Level
                SELECT 
                    bom.semifinished_id,
                    l.component_type,
                    l.component_id,
                    l.component_name,
                    bom.qty * CAST(l.quantity AS DECIMAL(18,6)) AS qty
                FROM bom
                JOIN inventory_linking l 
                    ON l.parent_type = 'Semifinished'
                   AND l.parent_id = bom.component_id
                   AND l.status = 1
            )
            
            SELECT 
                bom.semifinished_id,
                ROUND(
                    SUM(bom.qty * COALESCE(s.price, 0)) 
                    / NULLIF(sf.yield, 0),
                    2
                ) AS cost_per_unit
            
            FROM bom
            JOIN inventory_skus s 
                ON bom.component_type = 'SKU' 
               AND bom.component_id = s.id
            
            JOIN inventory_semifinished sf 
                ON bom.semifinished_id = sf.id
            
            GROUP BY bom.semifinished_id, sf.yield
        ");
        foreach($costprices as $costprice){
            $product_id= $costprice['bom']['semifinished_id'];
            $total_sku_cost= $costprice[0]['cost_per_unit'];
            
            $this->Inventory_semifinished->id = $product_id;
            // print_r($costprice);exit;
        
            if ($this->Inventory_semifinished->exists()) {
                
                $saved  = $this->Inventory_semifinished->saveAll([
                    'id' => $product_id,
                    'food_cost' => $total_sku_cost,
                    'modified' => $currentTime
                ]);
                
                $response[] = [
                    'semifinished_id' => $product_id,
                    'food_cost' => $total_sku_cost,
                    'status' => $saved ? 'updated' : 'failed'
                ];
            } else {
                
            }

            
        }
        
        echo json_encode([
            'status' => true,
            'message' => 'Food cost updated for semifinished',
            'data' => $response
        ]);
        exit;
    
    }
   
    public function fire_order()
    {
        $this->autoRender = false;
        $this->layout = false;
        $currentTime = date('Y-m-d H:i:s');

        $fire_items = $this->Order_orderhistory->find('all', [
            'conditions' => ['Order_orderhistory.kds_status' => 0,'Order_orderhistory.fire_at <= ' => $currentTime],
            'order' => ['Order_orderhistory.id' => 'ASC']
        ]);
        
        foreach ($fire_items as $fire_item) {
            
            $txnid = $fire_item['Order_orderhistory']['id'];
            $reference_id = $fire_item['Order_orderhistory']['reference_id'];
            $cashier_id = $fire_item['Order_orderhistory']['cashier_id'];
            
            $store_id='';
            $stores = $this->User->find('first', [
                'conditions' => [
                    'User.id' => $cashier_id,
                    'User.role' => 'cashier',
                    'User.status' => 1
                ],
                'recursive' => 1
            ]);
            $store_id=$stores['User']['location_id'];
            $store_name=$stores['User']['location_name'];
        
            $data['Kds_track'][] = [
                'order_history_id'   => $txnid,
                'order_reference_id' => $reference_id,
                'kds_group_id'       => $fire_item['Order_orderhistory']['group_id'],
                'kds_user_id'        => 0,
                'kds_station_id'     => $fire_item['Order_orderhistory']['kds'],
                'kds_username'       => 'auto-fire',
                'product_id'         => $fire_item['Order_orderhistory']['product_id'],
                'product_name'       => $fire_item['Order_orderhistory']['product_name'],
                'role'               => 'auto-fire',
                'table_no'           => $fire_item['Order_orderhistory']['table_number'],
                'cashier_id'         => $fire_item['Order_orderhistory']['cashier_id'],
                'location_id'        => $store_id,
                'location_name'      => $store_name,
                'status'             => 1
            ];
            
            
            $this->Kds_track->create();
            $this->Kds_track->saveAll($data['Kds_track']);
            $track_id = $this->Kds_track->id; // last inserted id
        
            $this->Order_orderhistory->updateAll(
                [
                    'Order_orderhistory.kds_status'   => 1,
                    'Order_orderhistory.kds_track_id'   => $track_id,
                ],
                ['Order_orderhistory.id' => $txnid]
            );
            $response='done';
        }
        echo json_encode($response);
        
    }
   
    // public function push_data()
    // {
    //     $this->autoRender = false;
    
    //     $tables = [
    //         'cards' => 'Card',
    //         'cardtracking' => 'Cardtracking',
    //         'catalog_category' => 'Catalog_category',
    //         'catalog_category_branches' => 'Catalog_category_branches',
    //         'catalog_modifier' => 'Catalog_modifier',
    //         'catalog_modifier_group' => 'Catalog_modifier_group',
    //         'catalog_package' => 'Catalog_package',
    //         'catalog_product' => 'Catalog_product',
    //         'catalog_product_attribute' => 'Catalog_product_attribute',
    //         'catalog_product_branches' => 'Catalog_product_branches',
    //         'catalog_product_tax' => 'Catalog_product_tax',
    //         'catalog_subcategory' => 'Catalog_subcategory',
    //         'catalog_tax' => 'Catalog_tax',
    //         'catalog_units' => 'Catalog_unit',
    //         'catalog_unit_mapping' => 'Catalog_unit_mapping',
    //         'inventory_semifinished_tax' => 'Inventory_semifinished_tax',
    //         'inventory_semifinished' => 'Inventory_semifinished',
    //         'inventory_purchase_order_items' => 'Inventory_purchase_order_item',
    //         'inventory_purchase_order' => 'Inventory_purchase_order',
    //         'inventory_linking' => 'Inventory_linking',
    //         'inventory_internal_indent_items' => 'Inventory_internal_indent_items',
    //         'inventory_internal_indent' => 'Inventory_internal_indent',
    //         'inventory_indent_items' => 'Inventory_indent_items',
    //         'inventory_indent' => 'Inventory_indent',
    //         'inventory_damage_items' => 'Inventory_damage_item',
    //         'inventory_damage' => 'Inventory_damage',
    //         'inventory_category' => 'Inventory_category',
    //         'coupons' => 'Coupon',
    //         'kds_users_station_mapping' => 'Kds_users_station_mapping',
    //         'kds_users' => 'Kds_user',
    //         'kds_tracks' => 'Kds_track',
    //         'inventory_vendor_sku_served' => 'Inventory_vendor_sku_served',
    //         'inventory_vendor_service_areas' => 'Inventory_vendor_service_area',
    //         'inventory_vendors' => 'Inventory_vendor',
    //         'inventory_units' => 'Inventory_unit',
    //         'inventory_tax' => 'Inventory_tax',
    //         'inventory_subcategory' => 'Inventory_subcategory',
    //         'inventory_sku_tax' => 'Inventory_sku_tax',
    //         'inventory_skus' => 'Inventory_sku',
    //         'order_tables' => 'Order_table',
    //         'order_payments' => 'Order_payment',
    //         'order_orderhistories' => 'Order_orderhistory',
    //         'order_modifier' => 'Order_modifier',
    //         'order_customers' => 'Order_customers',
    //         'online_order' => 'Online_order',
    //         'online_bookings' => 'Online_booking',
    //         'locations' => 'Location',
    //         'order_waiters' => 'Order_waiter',
    //         'order_transactions' => 'Order_transaction',
    //         'order_tax' => 'Order_tax',
    //         'order_table_tracking' => 'Order_table_tracking',
    //         'user_modules' => 'User_module',
    //         'user_locations' => 'User_location',
    //         'users' => 'User',
    //         'stock_transfer_items' => 'Stock_transfer_item',
    //         'stock_transfers' => 'Stock_transfer',
    //         'stock_ledger' => 'Stock_ledger',
    //         'pos_tempauditstock' => 'Temp_audit_stock',
    //         'pos_tempauditstock_items' => 'Temp_audit_stock_items',
    //         'pos_auditstock' => 'Audit_stock',
    //         'pos_auditstock_items' => 'Audit_stock_items',
    //         'pos_printers' => 'Pos_printer',
    //         'order_waiters' => 'Order_waiter',
    //         'order_transactions' => 'Order_transaction',
    //         'order_tax' => 'Order_tax',
    //         'account_imprest' => 'Account_imprest',
    //         'order_split_payments' => 'Order_split_payment',
    //         'order_split_transactions' => 'Order_split_transaction',
    //         'order_split_orderhistories' => 'Order_split_orderhistory',
    //         'pos_offers' => 'Offer',
    //         'user_tracks' => 'User_track',
    //         'order_orderhistories_json' => 'Order_orderhistory_json'
    //     ];
    
    //     $data_to_sync = [];
    
    //     foreach ($tables as $table => $modelname) {
    //         $unsynced = $this->$modelname->find('all', [
    //             'conditions' => ['sync' => 0],
    //             'order' => ['id' => 'ASC']
    //         ]);
    
    //         // convert only if it's an object (ResultSet)
    //         if (is_object($unsynced)) {
    //             $unsynced = $unsynced->toArray();
    //         }
    
    //         if (!empty($unsynced)) {
    //             $data_to_sync[$table] = [];
    
    //             foreach ($unsynced as $entity) {
    //                 // handle entity or array safely
    //                 $data_to_sync[$table][] = is_object($entity)
    //                     ? $entity->toArray()
    //                     : $entity;
    //             }
    //         }
    //     }
    //     if (!empty($data_to_sync)) {
    //         $url = 'https://phylopos.in/TGI/erp/Cron/sync_data_to_live';
    //         $ch = curl_init($url);
    //         curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    //         curl_setopt($ch, CURLOPT_POST, true);
    //         curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query(['sync_data' => json_encode($data_to_sync)]));
    //         $response = curl_exec($ch);
    //         curl_close($ch);
    
    //         $result = json_decode($response, true);
    
    //         if (!empty($result['success'])) {
    //             // Mark as synced in local DB
    //             foreach ($tables as $table => $modelname) {
    //                 if (empty($data_to_sync[$table])) continue;
            
    //                 // ✅ Flatten structure if needed (e.g. ['User' => [...]] → [...])
    //                 $flatRecords = array_map(function ($r) use ($modelname) {
    //                     return isset($r[$modelname]) ? $r[$modelname] : $r;
    //                 }, $data_to_sync[$table]);
            
    //                 // ✅ Extract IDs correctly
    //                 $ids = array_column($flatRecords, 'id');
            
    //                 if (!empty($ids)) {
    //                     // Mark synced = 1
    //                     $this->$modelname->updateAll(['sync' => 1], ['id IN' => $ids]);
    //                 }
    //             }
            
    //             echo json_encode(['success' => true, 'message' => 'All data synced successfully!']);
    //         } else {
    //             echo json_encode([
    //                 'success' => false,
    //                 'message' => 'Failed to sync data: ' . ($result['message'] ?? 'Unknown error')
    //             ]);
    //         }

    //     } else {
    //         echo json_encode([
    //             'success' => true,
    //             'message' => 'No new data to sync'
    //         ]);
    //     }
    
    // }

    // public function sync_data_to_live()
    // {
    //     $this->autoRender = false;
    //     $sync_data = json_decode($_POST['sync_data'], true);
    
    //     if (empty($sync_data)) {
    //         echo json_encode(['success' => false, 'message' => 'No data received.']);
    //         return;
    //     }
    
    //     // Table ↔ Model mapping
    //     // $tables = [
    //     //     'cards' => 'Card',
    //     //     'cardtracking' => 'Cardtracking',
    //     //     'catalog_category' => 'Catalog_category',
    //     //     'catalog_category_branches' => 'Catalog_category_branches',
    //     //     'catalog_modifier' => 'Catalog_modifier',
    //     //     'catalog_modifier_group' => 'Catalog_modifier_group',
    //     //     'catalog_package' => 'Catalog_package',
    //     //     'catalog_product' => 'Catalog_product',
    //     //     'catalog_product_attribute' => 'Catalog_product_attribute',
    //     //     'catalog_product_branches' => 'Catalog_product_branches',
    //     //     'catalog_product_tax' => 'Catalog_product_tax',
    //     //     'catalog_subcategory' => 'Catalog_subcategory',
    //     //     'catalog_tax' => 'Catalog_tax',
    //     //     'catalog_units' => 'Catalog_unit',
    //     //     'catalog_unit_mapping' => 'Catalog_unit_mapping',
    //     //     'inventory_semifinished_tax' => 'Inventory_semifinished_tax',
    //     //     'inventory_semifinished' => 'Inventory_semifinished',
    //     //     'inventory_purchase_order_items' => 'Inventory_purchase_order_item',
    //     //     'inventory_purchase_order' => 'Inventory_purchase_order',
    //     //     'inventory_linking' => 'Inventory_linking',
    //     //     'inventory_internal_indent_items' => 'Inventory_internal_indent_items',
    //     //     'inventory_internal_indent' => 'Inventory_internal_indent',
    //     //     'inventory_indent_items' => 'Inventory_indent_items',
    //     //     'inventory_indent' => 'Inventory_indent',
    //     //     'inventory_damage_items' => 'Inventory_damage_item',
    //     //     'inventory_damage' => 'Inventory_damage',
    //     //     'inventory_category' => 'Inventory_category',
    //     //     'coupons' => 'Coupon',
    //     //     'kds_users_station_mapping' => 'Kds_users_station_mapping',
    //     //     'kds_users' => 'Kds_user',
    //     //     'kds_tracks' => 'Kds_track',
    //     //     'inventory_vendor_sku_served' => 'Inventory_vendor_sku_served',
    //     //     'inventory_vendor_service_areas' => 'Inventory_vendor_service_area',
    //     //     'inventory_vendors' => 'Inventory_vendor',
    //     //     'inventory_units' => 'Inventory_unit',
    //     //     'inventory_tax' => 'Inventory_tax',
    //     //     'inventory_subcategory' => 'Inventory_subcategory',
    //     //     'inventory_sku_tax' => 'Inventory_sku_tax',
    //     //     'inventory_skus' => 'Inventory_sku',
    //     //     'order_tables' => 'Order_table',
    //     //     'order_payments' => 'Order_payment',
    //     //     'order_orderhistories' => 'Order_orderhistory',
    //     //     'order_modifier' => 'Order_modifier',
    //     //     'order_customers' => 'Order_customers',
    //     //     'online_order' => 'Online_order',
    //     //     'online_bookings' => 'Online_booking',
    //     //     'locations' => 'Location',
    //     //     'order_waiters' => 'Order_waiter',
    //     //     'order_transactions' => 'Order_transaction',
    //     //     'order_tax' => 'Order_tax',
    //     //     'order_table_tracking' => 'Order_table_tracking',
    //     //     'user_modules' => 'User_module',
    //     //     'user_locations' => 'User_location',
    //     //     'users' => 'User',
    //     //     'stock_transfer_items' => 'Stock_transfer_item',
    //     //     'stock_transfers' => 'Stock_transfer',
    //     //     'stock_ledger' => 'Stock_ledger',
    //     //     'pos_tempauditstock' => 'Temp_audit_stock',
    //     //     'pos_tempauditstock_items' => 'Temp_audit_stock_items',
    //     //     'pos_auditstock' => 'Audit_stock',
    //     //     'pos_auditstock_items' => 'Audit_stock_items',
    //     //     'pos_printers' => 'Pos_printer',
    //     //     'order_waiters' => 'Order_waiter',
    //     //     'order_transactions' => 'Order_transaction',
    //     //     'order_tax' => 'Order_tax',
    //     //     'account_imprest' => 'Account_imprest',
    //     //     'order_split_payments' => 'Order_split_payment',
    //     //     'order_split_transactions' => 'Order_split_transaction',
    //     //     'order_split_orderhistories' => 'Order_split_orderhistory',
    //     //     'pos_offers' => 'Offer',
    //     //     'order_orderhistories_json' => 'Order_orderhistory_json'
    //     // ];
        
    //     $tables = [
    //         'order_payments' => 'Order_payment',
    //         'order_orderhistories' => 'Order_orderhistory',
    //         'order_modifier' => 'Order_modifier',
    //         'order_customers' => 'Order_customers',
    //         'online_order' => 'Online_order',
    //         'order_waiters' => 'Order_waiter',
    //         'order_transactions' => 'Order_transaction',
    //         'order_tax' => 'Order_tax',
    //         'order_table_tracking' => 'Order_table_tracking',
    //         'order_split_payments' => 'Order_split_payment',
    //         'order_split_transactions' => 'Order_split_transaction',
    //         'order_split_orderhistories' => 'Order_split_orderhistory',
    //         'order_orderhistories_json' => 'Order_orderhistory_json',
    //         'user_tracks' => 'User_track'
    //     ];
    
    //     App::uses('ConnectionManager', 'Model');
    //     $db = ConnectionManager::getDataSource('default');
    
    //     foreach ($sync_data as $table => $records) {
    //         if (empty($records) || !isset($tables[$table])) continue;
    
    //         $modelName = $tables[$table];
    //         $this->loadModel($modelName);
    
    //         $batchValues = [];
    //         $columns = [];
    
    //         foreach ($records as $row) {
    //             if (empty($row) || !is_array($row)) continue;
    
    //             // Flatten model wrapper: ['User' => [...]] → [...]
    //             if (count($row) === 1 && isset($row[$modelName])) {
    //                 $row = $row[$modelName];
    //             }
    
    //             // Set columns once
    //             if (empty($columns)) {
    //                 $columns = array_keys($row);
    //             }
                
    //             $row['sync'] = 1; // force sync = 1
                
    //             // Escape and prepare row values
    //             $escapedValues = array_map(function ($value) use ($db) {
    //                 if ($value === null) return "NULL";
    //                 return "'" . addslashes($value) . "'";
    //             }, array_values($row));
    
    //             $batchValues[] = "(" . implode(",", $escapedValues) . ")";
    //         }
    
    //         // Skip if no valid rows
    //         if (empty($batchValues) || empty($columns)) continue;
    
    //         // Build update part (skip id, created)
    //         $updates = [];
    //         // foreach ($columns as $col) {
    //         //     if (in_array($col, ['id'])) continue;
    //         //     $updates[] = "`$col` = VALUES(`$col`)";
    //         // }
    //         foreach ($columns as $col) {
    //             if ($col === 'id') continue;
    //             $updates[] = "`$col` = VALUES(`$col`)";
    //         }
    
    //         // Build one combined UPSERT query
    //         $sql = "
    //             INSERT INTO `$table` (`" . implode("`,`", $columns) . "`)
    //             VALUES " . implode(", ", $batchValues) . "
    //             ON DUPLICATE KEY UPDATE " . implode(", ", $updates);
    
    //         try {
    //             $db->query($sql);
    //         } catch (Exception $e) {
    //             CakeLog::write('error', "Sync failed for [$table]: " . $e->getMessage());
    //         }
    //     }
    
    //     echo json_encode(['success' => true, 'message' => 'All data synced successfully!']);
    // }

    // public function pull_data($cashierid = null)
    // {
    //     $this->autoRender = false;
        
    //     /* ---------- Fetch cashier locations ---------- */
    //     $conditions = ['status' => 1];
    //     if (!empty($cashierid)) {
    //         $conditions['id'] = (int)$cashierid;
    //         $locationIds = $this->User->find('list', [
    //             'fields' => ['location_id', 'location_id'],
    //             'conditions' => $conditions
    //         ]);
            
    //     }else{
    //         $locationIds = [2]; 
    //     }
    //     $locationIds = array_values($locationIds);
    //     $locationStr = implode(',', $locationIds);

    //     $liveUrl   = 'https://phylopos.in/TGI/erp/Cron';
    //     $batchSize = 200;

    //     /* ---------- Table → Model map ---------- */
    //     $tables = [
    //         'catalog_category' => 'Catalog_category',
    //         'catalog_category_branches' => 'Catalog_category_branches',
    //         'catalog_modifier' => 'Catalog_modifier',
    //         'catalog_modifier_group' => 'Catalog_modifier_group',
    //         'catalog_modifier_group_linkings' => 'Catalog_modifier_group_linkings',
    //         'catalog_package' => 'Catalog_package',
    //         'catalog_product' => 'Catalog_product',
    //         'catalog_product_attribute' => 'Catalog_product_attribute',
    //         'catalog_product_branches' => 'Catalog_product_branches',
    //         'catalog_product_tax' => 'Catalog_product_tax',
    //         'catalog_subcategory' => 'Catalog_subcategory',
    //         'catalog_tax' => 'Catalog_tax',
    //         'catalog_units' => 'Catalog_unit',
    //         'catalog_unit_mapping' => 'Catalog_unit_mapping',
    //         'kds_users_station_mapping' => 'Kds_users_station_mapping',
    //         'kds_users' => 'Kds_user',
    //         'kds_tracks' => 'Kds_track',
    //         'locations' => 'Location',
    //         'user_modules' => 'User_module',
    //         'user_locations' => 'User_location',
    //         'users' => 'User',
    //         'pos_printers' => 'Pos_printer',
    //         'pos_offers' => 'Offer',
    //         'inventory_linking' => 'Inventory_linking'
    //     ];

        
    //     $modelss = $tables[$table];
    //     $this->loadModel($modelss);
    //     $db = ConnectionManager::getDataSource('default');


    //     /* ---------- Loop tables ---------- */
    //     foreach ($tables as $table => $model) {

    //         $page = 1;

    //         while (true) {

    //             $url = $liveUrl . "/get_unsynced_data_batch/$locationStr"
    //                  . "?table=$table&page=$page&limit=$batchSize";

    //             $ch = curl_init($url);
    //             curl_setopt_array($ch, [
    //                 CURLOPT_RETURNTRANSFER => true,
    //                 CURLOPT_TIMEOUT => 20
    //             ]);
    //             $response = curl_exec($ch);
    //             curl_close($ch);

    //             $json = json_decode($response, true);
    //             if (empty($json['data'])) {
    //                 break;
    //             }

    //             $records = $json['data'];
    //             $columns = [];
    //             $values  = [];
    //             $ids     = [];
    //             foreach ($records as $row) {

    //                 if (isset($row[$model])) {
    //                     $row = $row[$model];
    //                 }

    //                 if (empty($columns)) {
    //                     $columns = array_keys($row);
    //                 }

    //                 $ids[] = (int)$row['id'];

    //                 $escaped = array_map(function ($v) {
    //                     return $v === null ? "NULL" : "'" . addslashes($v) . "'";
    //                 }, array_values($row));

    //                 $values[] = "(" . implode(",", $escaped) . ")";
    //             }

    //             if (empty($values)) break;

    //             $updateCols = [];
    //             foreach ($columns as $col) {
    //                 if (!in_array($col, ['id', 'created'])) {
    //                     $updateCols[] = "`$col`=VALUES(`$col`)";
    //                 }
    //             }

    //             $sql = "
    //                 INSERT INTO `$table` (`" . implode("`,`", $columns) . "`)
    //                 VALUES " . implode(",", $values) . "
    //                 ON DUPLICATE KEY UPDATE " . implode(",", $updateCols);

    //             /* ---------- Execute insert safely ---------- */
    //             $successIds = [];
    //             try {
    //                 $stmt = $db->rawQuery($sql);
    //                 $successIds = $ids; // query ran successfully

    //             } catch (Exception $e) {
    //                 CakeLog::write('error', "DB ERROR [$table]: " . $e->getMessage());
    //             }
    //             /* ---------- Notify LIVE only for successful rows ---------- */
    //             if (!empty($successIds)) {
    //                 $ch2 = curl_init($liveUrl . "/mark_data_synced_batch");
                    
    //                 curl_setopt_array($ch2, [
    //                     CURLOPT_RETURNTRANSFER => true,
    //                     CURLOPT_POST           => true,
    //                     CURLOPT_POSTFIELDS     => http_build_query([
    //                         'table'        => $table,
    //                         'ids'          => json_encode($successIds),
    //                         'location_ids' => json_encode($locationIds)
    //                     ]),
    //                     CURLOPT_TIMEOUT        => 15,
    //                     CURLOPT_CONNECTTIMEOUT => 5
    //                 ]);
                    
    //                 $response23 = curl_exec($ch2);
    //             }

    //             if (count($records) < $batchSize) break;
    //             $page++;
    //         }
    //     }
        
    //     $ch3 = curl_init($liveUrl . "/mark_location_synced_batch");
    //     curl_setopt_array($ch3, [
    //         CURLOPT_RETURNTRANSFER => true,
    //         CURLOPT_POST           => true,
    //         CURLOPT_POSTFIELDS     => http_build_query([
    //             'location_ids' => json_encode($locationIds)
    //         ]),
    //         CURLOPT_TIMEOUT        => 15,
    //         CURLOPT_CONNECTTIMEOUT => 5
    //     ]);
        
    //     $response24 = curl_exec($ch3);
        
    //     echo json_encode(['success' => true, 'message' => 'Sync completed successfully']);
    // }
    
    // public function get_unsynced_data_batch($location_ids)
    // {
    //     $this->autoRender = false;

    //     $table = $_GET['table'] ?? '';
    //     $page  = $_GET['page'] ?? 1;
    //     $limit = $_GET['limit'] ?? 200;

    //     if (!$table) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }

    //     $locationIds = array_map('intval', explode(',', $location_ids));
    //     $unpublished = $this->Location->find('list', [
    //         'fields' => ['id', 'id'],
    //         'conditions' => [
    //             'id' => $locationIds,
    //             'is_publish' => 0
    //         ]
    //     ]);
        
    //     $targetLocations = array_values($unpublished);

    //     $tables = [
    //         'catalog_category' => 'Catalog_category',
    //         'catalog_category_branches' => 'Catalog_category_branches',
    //         'catalog_modifier' => 'Catalog_modifier',
    //         'catalog_modifier_group' => 'Catalog_modifier_group',
    //         'catalog_package' => 'Catalog_package',
    //         'catalog_product' => 'Catalog_product',
    //         'catalog_product_attribute' => 'Catalog_product_attribute',
    //         'catalog_product_branches' => 'Catalog_product_branches',
    //         'catalog_product_tax' => 'Catalog_product_tax',
    //         'catalog_subcategory' => 'Catalog_subcategory',
    //         'catalog_tax' => 'Catalog_tax',
    //         'catalog_units' => 'Catalog_unit',
    //         'catalog_unit_mapping' => 'Catalog_unit_mapping',
    //         'kds_users_station_mapping' => 'Kds_users_station_mapping',
    //         'kds_users' => 'Kds_user',
    //         'kds_tracks' => 'Kds_track',
    //         'locations' => 'Location',
    //         'user_modules' => 'User_module',
    //         'user_locations' => 'User_location',
    //         'users' => 'User',
    //         'pos_printers' => 'Pos_printer',
    //         'pos_offers' => 'Offer',
    //         'inventory_linking' => 'Inventory_linking'
    //     ];

    //     if (!isset($tables[$table])) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }

    //     $model = $tables[$table];
    //     $this->loadModel($model);

    //     $conditions = ['local_sync' => 0];

    //     if ($this->$model->hasField('location_id')) {
    //         $conditions['location_id'] = $targetLocations;
    //     }
        
    //     $records = $this->$model->find('all', [
    //         'conditions' => $conditions,
    //         'limit' => $limit,
    //         'offset' => ($page - 1) * $limit,
    //         'order' => ['id' => 'ASC']
    //     ]);

    //     echo json_encode(['success' => true, 'data' => $records]);
    // }
    
    // public function mark_data_synced_batch()
    // {
    //     $this->autoRender = false;

    //     $table       = $_POST['table'] ?? '';
    //     $ids         = json_decode($_POST['ids'] ?? '[]', true);
    //     $locationIds = json_decode($_POST['location_ids'] ?? '[]', true);
    //     if (!$table || empty($ids) || empty($locationIds)) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    //     $tables = [
    //         'catalog_category' => 'Catalog_category',
    //         'catalog_category_branches' => 'Catalog_category_branches',
    //         'catalog_modifier' => 'Catalog_modifier',
    //         'catalog_modifier_group' => 'Catalog_modifier_group',
    //         'catalog_package' => 'Catalog_package',
    //         'catalog_product' => 'Catalog_product',
    //         'catalog_product_attribute' => 'Catalog_product_attribute',
    //         'catalog_product_branches' => 'Catalog_product_branches',
    //         'catalog_product_tax' => 'Catalog_product_tax',
    //         'catalog_subcategory' => 'Catalog_subcategory',
    //         'catalog_tax' => 'Catalog_tax',
    //         'catalog_units' => 'Catalog_unit',
    //         'catalog_unit_mapping' => 'Catalog_unit_mapping',
    //         'kds_users_station_mapping' => 'Kds_users_station_mapping',
    //         'kds_users' => 'Kds_user',
    //         'kds_tracks' => 'Kds_track',
    //         'locations' => 'Location',
    //         'user_modules' => 'User_module',
    //         'user_locations' => 'User_location',
    //         'users' => 'User',
    //         'pos_printers' => 'Pos_printer',
    //         'pos_offers' => 'Offer',
    //         'inventory_linking' => 'Inventory_linking'
    //     ];

    //     if (!isset($tables[$table])) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }

    //     $model = $tables[$table];
    //     $this->loadModel($model);
    //     $this->loadModel('Location');

    //     /* ---------- Check unpublished only for selected locations ---------- */
    //     $unpublished = $this->Location->find('count', [
    //         'conditions' => [
    //             'status' => 1,
    //             'is_publish' => 0
    //         ]
    //     ]);
        
    //     /* ---------- Update local_sync ONLY for successful IDs ---------- */
    //     if ($unpublished == 1) {
    //         $this->$model->updateAll(
    //             ['local_sync' => '1'],
    //             ['id' => $ids]
    //         );
    //     }

        
    //     echo json_encode(['success' => true]);
    // }
    
    // public function mark_location_synced_batch()
    // {
    //     $this->autoRender = false;

    //     $locationIds = json_decode($_POST['location_ids'] ?? '[]', true);
        
    //     /* ---------- Update is_publish ONLY for selected locations ---------- */
    //     $this->Location->updateAll(
    //         ['is_publish' => '1'],
    //         [
    //             'id' => $locationIds,
    //             'is_publish' => 0
    //         ]
    //     );

        
    //     echo json_encode(['success' => true]);
    // }
    
    //Push Data
    // public function push_data()
    // {
    //     $this->autoRender = false;
    
    //     $tables = push_tables;
    
    //     $dataToSync = [];
    
    //     foreach ($tables as $table => $model) {
    //         $rows = $this->$model->find('all', [
    //             'conditions' => ['sync' => 0],
    //             'order' => ['id' => 'ASC'],
    //             'limit' => 100 // ✅ batch protection
    //         ]);
    
    //         if (!empty($rows)) {
    //             foreach ($rows as $r) {
    //                 $dataToSync[$table][] = $r[$model];
    //             }
    //         }
    //     }
    
    //     if (empty($dataToSync)) {
    //         echo json_encode(['success' => true, 'message' => 'Nothing to sync']);
    //         return;
    //     }
    //     $sync_link=sync_link;
    //     // 🔵 Send to cloud
    //     $url = $sync_link . "/sync_data_to_live";
    
    //     $ch = curl_init($url);
    //     curl_setopt_array($ch, [
    //         CURLOPT_RETURNTRANSFER => true,
    //         CURLOPT_POST => true,
    //         CURLOPT_POSTFIELDS => [
    //             'sync_data' => json_encode($dataToSync)
    //         ],
    //         CURLOPT_TIMEOUT => 60
    //     ]);
    
    //     $response = curl_exec($ch);
    //     curl_close($ch);
    
    //     $result = json_decode($response, true);
    
    //     if (empty($result['tables'])) {
    //         echo json_encode(['success' => false, 'message' => 'Invalid cloud response']);
    //         return;
    //     }
    
    //     // 🟢 Mark ONLY successful tables
    //     foreach ($result['tables'] as $table => $ok) {
    //         if (!$ok || empty($dataToSync[$table])) continue;
    
    //         $model = $tables[$table];
    //         $ids = array_column($dataToSync[$table], 'id');
    
    //         if ($ids) {
    //             $this->$model->updateAll(
    //                 ['sync' => 1],
    //                 ['id IN' => $ids]
    //             );
    //         }
    //     }
    
    //     echo json_encode([
    //         'success' => $result['success'],
    //         'message' => 'Sync completed',
    //         'cloud_errors' => $result['errors'] ?? []
    //     ]);
    // }
    
    // public function sync_data_to_live()
    // {
    //     $this->autoRender = false;
    
    //     $syncData = json_decode($_POST['sync_data'] ?? '', true);
    //     if (empty($syncData)) {
    //         echo json_encode(['success' => false, 'message' => 'No data']);
    //         return;
    //     }
    
    //     $tables = push_tables;
    
    //     App::uses('ConnectionManager', 'Model');
    //     $db = ConnectionManager::getDataSource('default');
    
    //     $response = [
    //         'success' => true,
    //         'tables' => [],
    //         'errors' => []
    //     ];
    
    //     foreach ($syncData as $table => $rows) {
    //         if (empty($rows) || empty($tables[$table])) continue;
    
    //         $columns = [];
    //         $values  = [];
    
    //         foreach ($rows as $row) {
    //             if (!is_array($row)) continue;
    
    //             $row['sync'] = 1; // force cloud sync flag
    
    //             if (empty($columns)) {
    //                 $columns = array_keys($row);
    //             }
    
    //             $escaped = array_map([$db, 'value'], array_values($row));
    //             $values[] = '(' . implode(',', $escaped) . ')';
    //         }
    
    //         if (empty($values)) continue;
    
    //         $updates = [];
    //         foreach ($columns as $col) {
    //             if ($col === 'id') continue;
    //             $updates[] = "`$col` = VALUES(`$col`)";
    //         }
    
    //         $sql = "
    //             INSERT INTO `$table` (`" . implode('`,`', $columns) . "`)
    //             VALUES " . implode(',', $values) . "
    //             ON DUPLICATE KEY UPDATE " . implode(',', $updates);
    
    //         try {
    //             $db->begin();
    //             $db->query($sql);
    //             $db->commit();
    
    //             $response['tables'][$table] = true;
    //         } catch (Exception $e) {
    //             $db->rollback();
    
    //             $response['success'] = false;
    //             $response['tables'][$table] = false;
    //             $response['errors'][$table] = $e->getMessage();
    
    //             CakeLog::write('error', "[SYNC][$table] " . $e->getMessage());
    //         }
    //     }
    
    //     echo json_encode($response);
    // }
    
    // //pull data
    // public function pull_data($cashierid = null)
    // {
    //     $this->autoRender = false;
    //     $this->loadModel('Location');
    //     $this->loadModel('User');
    
    //     /* -------------------------------
    //      * 1️⃣ FETCH LOCATIONS
    //      * ------------------------------- */
    //     if (!empty($cashierid)) {
    //         $locationIds = $this->User->find('list', [
    //             'fields' => ['location_id', 'location_id'],
    //             'conditions' => [
    //                 'id' => (int)$cashierid,
    //                 'status' => 1
    //             ]
    //         ]);
    //     } else {
    //         $locationIds = sync_store_id;
    //     }
    
    //     $locationIds = array_values($locationIds);
    //     if (empty($locationIds)) {
    //         echo json_encode(['success' => false, 'message' => 'No locations found']);
    //         return;
    //     }
    
    //     $locationStr = implode(',', $locationIds);
    //     $liveUrl     = sync_link;
    //     $batchSize   = 200;
    
    //     /* -------------------------------
    //      * 3️⃣ TABLE → MODEL MAP
    //      * ------------------------------- */
    //     $tables = pull_tables;
    
    //     $db = ConnectionManager::getDataSource('default');
    
    //     /* -------------------------------
    //      * 4️⃣ LOOP TABLES & BATCHES
    //      * ------------------------------- */
    //     foreach ($tables as $table => $model) {
    
    //         $this->loadModel($model);
    //         $page = 1;
    
    //         while (true) {
    
    //             $url = $liveUrl . "/get_unsynced_data_batch/$locationStr"
    //                  . "?table=$table&page=$page&limit=$batchSize";
    
    //             $ch = curl_init($url);
    //             curl_setopt_array($ch, [
    //                 CURLOPT_RETURNTRANSFER => true,
    //                 CURLOPT_TIMEOUT => 20
    //             ]);
    //             $response = curl_exec($ch);
    //             curl_close($ch);
    //             $json = json_decode($response, true);
    //             if (empty($json['data'])) break;
    
    //             $records = $json['data'];
    //             $columns = [];
    //             $values  = [];
    //             $ids     = [];
    
    //             foreach ($records as $row) {
    //                 $row = $row[$model] ?? $row;
    
    //                 if (empty($columns)) {
    //                     $columns = array_keys($row);
    //                 }
    
    //                 $ids[] = (int)$row['id'];
    
    //                 $escaped = [];
    //                 foreach ($columns as $col) {
    //                     $escaped[] = $db->value($row[$col], 'string');
    //                 }
    
    //                 $values[] = '(' . implode(',', $escaped) . ')';
    //             }
    
    //             if (empty($values)) break;
    
    //             $updates = [];
    //             foreach ($columns as $col) {
    //                 if (!in_array($col, ['id', 'created'])) {
    //                     $updates[] = "`$col` = VALUES(`$col`)";
    //                 }
    //             }
    
    //             $sql = "
    //                 INSERT INTO `$table` (`" . implode('`,`', $columns) . "`)
    //                 VALUES " . implode(',', $values) . "
    //                 ON DUPLICATE KEY UPDATE " . implode(',', $updates);
    
    //             try {
    //                 $db->begin();
    //                 $db->query($sql);
    //                 $db->commit();
    
    //                 /* -------------------------------
    //                  * 5️⃣ NOTIFY LIVE
    //                  * ------------------------------- */
    //                 $this->notify_live_item_sync($liveUrl, $table, $ids, $locationIds);
    
    //             } catch (Exception $e) {
    //                 $db->rollback();
    //                 CakeLog::write('error', "[PULL][$table] " . $e->getMessage());
    //                 break;
    //             }
    
    //             if (count($records) < $batchSize) break;
    //             $page++;
    //         }
    //     }
    
    //     /* -------------------------------
    //      * 6️⃣ MARK LOCATIONS PUBLISHED
    //      * ------------------------------- */
    //     $this->notify_live_location_sync($liveUrl, $locationIds);
    
    //     echo json_encode(['success' => true, 'message' => 'Sync completed']);
    // }
    
    // private function notify_live_item_sync($liveUrl, $table, $ids, $locationIds)
    // {
    //     $ch = curl_init($liveUrl . "/mark_data_synced_batch");
    //     curl_setopt_array($ch, [
    //         CURLOPT_RETURNTRANSFER => true,
    //         CURLOPT_POST => true,
    //         CURLOPT_POSTFIELDS => http_build_query([
    //             'table' => $table,
    //             'ids'   => json_encode($ids),
    //             'location_ids' => json_encode($locationIds)
    //         ]),
    //         CURLOPT_TIMEOUT => 15
    //     ]);
    //     curl_exec($ch);
    //     curl_close($ch);
    // }
    
    // private function notify_live_location_sync($liveUrl, $locationIds)
    // {
    //     $ch = curl_init($liveUrl . "/mark_location_synced_batch");
    //     curl_setopt_array($ch, [
    //         CURLOPT_RETURNTRANSFER => true,
    //         CURLOPT_POST => true,
    //         CURLOPT_POSTFIELDS => http_build_query([
    //             'location_ids' => json_encode($locationIds)
    //         ]),
    //         CURLOPT_TIMEOUT => 15
    //     ]);
    //     curl_exec($ch);
    //     curl_close($ch);
    // }

    // public function get_unsynced_data_batch($location_ids)
    // {
    //     $this->autoRender = false;
    
    //     $table = $_GET['table'] ?? '';
    //     $page  = (int)($_GET['page'] ?? 1);
    //     $limit = (int)($_GET['limit'] ?? 200);
    
    //     if (!$table) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    
    //     $locationIds = array_map('intval', explode(',', $location_ids));
    
    //     $targetLocations = $this->Location->find('list', [
    //         'fields' => ['id', 'id'],
    //         'conditions' => [
    //             'id' => $locationIds,
    //             'is_publish' => 0
    //         ]
    //     ]);
    
    //     if (empty($targetLocations)) {
    //         echo json_encode(['success' => true, 'data' => []]);
    //         return;
    //     }
    
    //     $tables = pull_tables;
    
    //     if (!isset($tables[$table])) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    
    //     $model = $tables[$table];
    //     $this->loadModel($model);
    
    //     $conditions = ['local_sync' => 0];
    
    //     if ($this->$model->hasField('location_id')) {
    //         $conditions['location_id'] = array_values($targetLocations);
    //     }
    
    //     $records = $this->$model->find('all', [
    //         'conditions' => $conditions,
    //         'limit' => $limit,
    //         'offset' => ($page - 1) * $limit,
    //         'order' => ['id' => 'ASC']
    //     ]);
    
    //     echo json_encode(['success' => true, 'data' => $records]);
    // }
    
    // public function mark_data_synced_batch()
    // {
    //     $this->autoRender = false;
    
    //     $table = $_POST['table'] ?? '';
    //     $ids   = json_decode($_POST['ids'] ?? '[]', true);
    //     $locationIds   = json_decode($_POST['location_ids'] ?? '[]', true);
    
    //     if (!$table || empty($ids)) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    
    //     $tables = pull_tables;
    
    //     if (!isset($tables[$table])) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    
    //     $model = $tables[$table];
    //     $this->loadModel($model);
    //     $this->loadModel('Location');
    
    //     /* ⚠️ CRITICAL RULE */
    //     $pending = $this->Location->find('count', [
    //         'conditions' => [
    //             'status' => 1,
    //             'is_publish' => 0
    //         ]
    //     ]);
    
    //     // if ($pending == 1) {
    //     //     $this->$model->updateAll(
    //     //         ['local_sync' => 1],
    //     //         ['id' => $ids]
    //     //     );
    //     // }
    //     if ($this->$model->hasField('location_id')) {
        
    //         // Find locations (from this batch) that are NOW published
    //         $publishedLocations = $this->Location->find('count', [
    //             'conditions' => [
    //                 'id'         => $locationIds,
    //                 'is_publish' => 0,
    //                 'status'     => 1
    //             ]
    //         ]);
        
    //         if ($publishedLocations==1) {
    //             $this->$model->updateAll(
    //                 ['local_sync' => 1],
    //                 [
    //                     'id' => $ids,
    //                     'location_id' => $locationIds
    //                 ]
    //             );
    //         }
        
    //     } else {
    //         if ($pending == 1) {
    //             // Table WITHOUT location_id → safe to mark globally
    //             $this->$model->updateAll(
    //                 ['local_sync' => 1],
    //                 ['id' => $ids]
    //             );
    //         }
    //     }

    
    //     echo json_encode(['success' => true]);
    // }
    
    // public function mark_location_synced_batch()
    // {
    //     $this->autoRender = false;
    
    //     $locationIds = json_decode($_POST['location_ids'] ?? '[]', true);
    //     if (empty($locationIds)) {
    //         echo json_encode(['success' => false]);
    //         return;
    //     }
    
    //     $this->Location->updateAll(
    //         ['is_publish' => 1],
    //         [
    //             'id' => $locationIds,
    //             'is_publish' => 0
    //         ]
    //     );
    
    //     echo json_encode(['success' => true]);
    // }
    
    // =========================================================
    // PUSH DATA v2 — Bill-level grouping with error quarantine
    // Runs on LOCAL, pushes to cloud
    // =========================================================
    public function push_data()
    {
        $this->autoRender = false;
        $this->loadModel('Sync_error');

        $billTables    = bill_tables;
        $nonBillTables = non_bill_tables;
        $billLimit     = sync_bill_limit;
        $recordLimit   = sync_record_limit;
        $maxAttempts   = sync_max_quarantine_attempts;
        $syncLink      = sync_link;

        $summary = [
            'bills_synced' => 0,
            'bills_failed' => 0,
            'non_bill_synced' => 0,
            'errors' => [],
            'started_at' => date('Y-m-d H:i:s')
        ];

        // Capture read time BEFORE fetching data.
        // Used to guard against race conditions where ApisController
        // modifies a record between our read and sync=1 mark.
        $readTime = date('Y-m-d H:i:s');

        // ----- PHASE 1: Bill-level sync -----

        $unionParts = [];
        foreach ($billTables as $table => $model) {
            $unionParts[] = "SELECT DISTINCT reference_id FROM `$table` WHERE sync = 0 AND reference_id IS NOT NULL AND reference_id != ''";
        }

        if (!empty($unionParts)) {
            $unionSql = implode(' UNION ', $unionParts) . " LIMIT $billLimit";

            $firstModel = reset($billTables);
            $this->loadModel($firstModel);
            $refRows = $this->$firstModel->query($unionSql);

            $refIds = [];
            foreach ($refRows as $row) {
                $rid = $row[0]['reference_id'] ?? ($row['reference_id'] ?? null);
                if (!empty($rid)) {
                    $refIds[] = $rid;
                }
            }

            // Exclude quarantined reference_ids (too many failed attempts)
            if (!empty($refIds)) {
                $quarantined = $this->Sync_error->find('list', [
                    'fields' => ['reference_id', 'reference_id'],
                    'conditions' => [
                        'reference_id' => $refIds,
                        'direction' => 'push',
                        'resolved' => 0,
                        'attempts >=' => $maxAttempts
                    ]
                ]);
                $refIds = array_diff($refIds, $quarantined);
            }

            if (!empty($refIds)) {
                // Gather all unsynced data grouped by reference_id
                $billPayload = [];
                foreach ($refIds as $refId) {
                    $billData = [];
                    foreach ($billTables as $table => $model) {
                        $this->loadModel($model);
                        $rows = $this->$model->find('all', [
                            'conditions' => [
                                'reference_id' => $refId,
                                'sync' => 0
                            ]
                        ]);
                        if (!empty($rows)) {
                            foreach ($rows as $r) {
                                $billData[$table][] = $r[$model];
                            }
                        }
                    }
                    if (!empty($billData)) {
                        $billPayload[$refId] = $billData;
                    }
                }

                if (!empty($billPayload)) {
                    $url = $syncLink . "/sync_data_to_live";
                    $ch = curl_init($url);
                    curl_setopt_array($ch, [
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_POST           => true,
                        CURLOPT_HTTPHEADER     => ['Content-Type: application/x-www-form-urlencoded'],
                        CURLOPT_POSTFIELDS     => http_build_query([
                            'sync_data' => json_encode($billPayload),
                            'mode' => 'bill'
                        ]),
                        CURLOPT_TIMEOUT => 120
                    ]);
                    $response = curl_exec($ch);
                    $curlErr  = curl_error($ch);
                    curl_close($ch);

                    if (!empty($curlErr)) {
                        $summary['errors'][] = "cURL error: $curlErr";
                        CakeLog::write('error', "[PUSH] cURL error: $curlErr");
                    } else {
                        $result = json_decode($response, true);

                        if (!empty($result['bills'])) {
                            foreach ($result['bills'] as $refId => $billResult) {
                                if (!empty($billResult['success'])) {
                                    // Mark records as synced — ONLY if not modified since read.
                                    // The modified guard prevents the race condition where
                                    // ApisController updates a record between our read & this mark.
                                    foreach ($billTables as $table => $tblModel) {
                                        if (!empty($billPayload[$refId][$table])) {
                                            $ids = array_column($billPayload[$refId][$table], 'id');
                                            if ($ids) {
                                                $this->loadModel($tblModel);
                                                $idList = implode(',', array_map('intval', $ids));
                                                $this->$tblModel->query(
                                                    "UPDATE `$table` SET sync = 1 "
                                                  . "WHERE id IN ($idList) "
                                                  . "AND modified <= '$readTime'"
                                                );
                                            }
                                        }
                                    }
                                    $summary['bills_synced']++;

                                    // Clear previous sync_errors for this bill
                                    $this->Sync_error->updateAll(
                                        ['Sync_error.resolved' => 1],
                                        [
                                            'Sync_error.reference_id' => $refId,
                                            'Sync_error.direction' => 'push',
                                            'Sync_error.resolved' => 0
                                        ]
                                    );
                                } else {
                                    // Bill failed — quarantine it
                                    $summary['bills_failed']++;
                                    $errMsg = $billResult['error'] ?? 'Unknown cloud error';
                                    $failedTable = $billResult['failed_table'] ?? '';
                                    $failedId    = $billResult['failed_record_id'] ?? null;
                                    $summary['errors'][] = "Bill $refId: $errMsg";

                                    $this->_logSyncError($refId, $failedTable, $failedId, $errMsg, $billPayload[$refId], 'push');
                                }
                            }
                        } else {
                            $summary['errors'][] = 'Invalid cloud response';
                            CakeLog::write('error', '[PUSH] Invalid cloud response: ' . substr($response, 0, 500));
                        }
                    }
                }
            }
        }

        // ----- PHASE 2: Non-bill tables (per-record) -----

        foreach ($nonBillTables as $table => $model) {
            $this->loadModel($model);
            $rows = $this->$model->find('all', [
                'conditions' => ['sync' => 0],
                'order' => ['id' => 'ASC'],
                'limit' => $recordLimit
            ]);

            if (empty($rows)) continue;

            $records = [];
            foreach ($rows as $r) {
                $records[] = $r[$model];
            }

            $url = $syncLink . "/sync_data_to_live";
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_POST           => true,
                CURLOPT_HTTPHEADER     => ['Content-Type: application/x-www-form-urlencoded'],
                CURLOPT_POSTFIELDS     => http_build_query([
                    'sync_data' => json_encode([$table => $records]),
                    'mode' => 'table'
                ]),
                CURLOPT_TIMEOUT => 60
            ]);
            $resp = curl_exec($ch);
            curl_close($ch);

            $res = json_decode($resp, true);
            if (!empty($res['records'][$table])) {
                $successIds = $res['records'][$table]['success'] ?? [];
                $failedIds  = $res['records'][$table]['failed'] ?? [];

                if (!empty($successIds)) {
                    $idList = implode(',', array_map('intval', $successIds));
                    $this->$model->query(
                        "UPDATE `$table` SET sync = 1 "
                      . "WHERE id IN ($idList) AND modified <= '$readTime'"
                    );
                    $summary['non_bill_synced'] += count($successIds);
                }

                foreach ($failedIds as $fail) {
                    $this->_logSyncError(null, $table, $fail['id'] ?? null, $fail['error'] ?? 'Unknown', null, 'push');
                }
            }
        }

        // ----- PHASE 3: Record last sync time -----
        $summary['completed_at'] = date('Y-m-d H:i:s');
        $this->_updateLastSyncTime('push', $summary);

        // ----- PHASE 4: Check for persistent alert conditions -----
        $this->_checkSyncAlerts();

        echo json_encode(['success' => true, 'summary' => $summary]);
    }
    
    // =========================================================
    // SYNC DATA TO LIVE v2 — Per-bill transactions, per-record UPSERT
    // Runs on CLOUD, receives data from local push_data()
    // =========================================================
    public function sync_data_to_live()
    {
        $this->autoRender = false;

        $syncData = json_decode($_POST['sync_data'] ?? '', true);
        $mode     = $_POST['mode'] ?? 'bill';

        if (empty($syncData)) {
            echo json_encode(['success' => false, 'message' => 'No data']);
            return;
        }

        $allTables = push_tables;

        $db = ConnectionManager::getDataSource('default');

        $response = ['success' => true];

        if ($mode === 'bill') {
            // ----- BILL MODE: data grouped by reference_id -----
            $response['bills'] = [];

            foreach ($syncData as $refId => $billData) {
                if (empty($billData) || !is_array($billData)) continue;

                $db->begin();
                $billSuccess = true;
                $failedTable = '';
                $failedRecordId = null;
                $errorMsg = '';

                try {
                    foreach ($billData as $table => $rows) {
                        if (empty($rows) || !is_array($rows) || empty($allTables[$table])) continue;

                        foreach ($rows as $row) {
                            if (!is_array($row)) continue;

                            $row['sync'] = 1;

                            $columns = array_keys($row);
                            $escaped = array_map([$db, 'value'], array_values($row));

                            $updates = [];
                            foreach ($columns as $col) {
                                if ($col === 'id') continue;
                                $updates[] = "`$col` = VALUES(`$col`)";
                            }

                            $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                                 . "VALUES (" . implode(',', $escaped) . ") "
                                 . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

                            $db->query($sql);
                        }
                    }

                    $db->commit();

                    $response['bills'][$refId] = ['success' => true];

                } catch (Exception $e) {
                    $db->rollback();
                    $billSuccess = false;
                    $errorMsg = $e->getMessage();

                    $failInfo = $this->_identifyFailedRecord($db, $billData, $allTables);
                    $failedTable    = $failInfo['table'] ?? '';
                    $failedRecordId = $failInfo['record_id'] ?? null;
                    if (!empty($failInfo['error'])) {
                        $errorMsg = $failInfo['error'];
                    }

                    CakeLog::write('error', "[SYNC_BILL][$refId][$failedTable] $errorMsg");

                    $response['success'] = false;
                    $response['bills'][$refId] = [
                        'success'          => false,
                        'error'            => $errorMsg,
                        'failed_table'     => $failedTable,
                        'failed_record_id' => $failedRecordId
                    ];
                }
            }

        } elseif ($mode === 'table') {
            // ----- TABLE MODE: non-bill tables, per-record processing -----
            $response['records'] = [];

            foreach ($syncData as $table => $rows) {
                if (empty($rows) || !is_array($rows) || empty($allTables[$table])) continue;

                $successIds = [];
                $failedIds  = [];

                foreach ($rows as $row) {
                    if (!is_array($row)) continue;

                    $row['sync'] = 1;
                    $recordId = $row['id'] ?? null;

                    $columns = array_keys($row);
                    $escaped = array_map([$db, 'value'], array_values($row));

                    $updates = [];
                    foreach ($columns as $col) {
                        if ($col === 'id') continue;
                        $updates[] = "`$col` = VALUES(`$col`)";
                    }

                    $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                         . "VALUES (" . implode(',', $escaped) . ") "
                         . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

                    try {
                        $db->query($sql);
                        $successIds[] = $recordId;
                    } catch (Exception $e) {
                        CakeLog::write('error', "[SYNC_REC][$table][id=$recordId] " . $e->getMessage());
                        $failedIds[] = ['id' => $recordId, 'error' => $e->getMessage()];
                    }
                }

                $response['records'][$table] = [
                    'success' => $successIds,
                    'failed'  => $failedIds
                ];
            }

        } else {
            // ----- LEGACY fallback -----
            $response['tables'] = [];
            $response['errors'] = [];

            foreach ($syncData as $table => $rows) {
                if (empty($rows) || !is_array($rows) || empty($allTables[$table])) continue;

                $columns = [];
                $values  = [];

                foreach ($rows as $row) {
                    if (!is_array($row)) continue;
                    $row['sync'] = 1;
                    if (empty($columns)) {
                        $columns = array_keys($row);
                    }
                    $escaped = array_map([$db, 'value'], array_values($row));
                    $values[] = '(' . implode(',', $escaped) . ')';
                }

                if (empty($values)) continue;

                $updates = [];
                foreach ($columns as $col) {
                    if ($col === 'id') continue;
                    $updates[] = "`$col` = VALUES(`$col`)";
                }

                $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                     . "VALUES " . implode(',', $values) . " "
                     . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

                try {
                    $db->begin();
                    $db->query($sql);
                    $db->commit();
                    $response['tables'][$table] = true;
                } catch (Exception $e) {
                    $db->rollback();
                    $response['success'] = false;
                    $response['tables'][$table] = false;
                    $response['errors'][$table] = $e->getMessage();
                    CakeLog::write('error', "[SYNC_LEGACY][$table] " . $e->getMessage());
                }
            }
        }

        echo json_encode($response);
    }

    /**
     * After a bill-level transaction fails, pinpoint exactly which record caused it.
     */
    private function _identifyFailedRecord($db, $billData, $allTables)
    {
        foreach ($billData as $table => $rows) {
            if (empty($rows) || empty($allTables[$table])) continue;

            foreach ($rows as $row) {
                if (!is_array($row)) continue;
                $row['sync'] = 1;

                $columns = array_keys($row);
                $escaped = array_map([$db, 'value'], array_values($row));

                $updates = [];
                foreach ($columns as $col) {
                    if ($col === 'id') continue;
                    $updates[] = "`$col` = VALUES(`$col`)";
                }

                $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                     . "VALUES (" . implode(',', $escaped) . ") "
                     . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

                try {
                    $db->begin();
                    $db->query($sql);
                    $db->rollback();
                } catch (Exception $e) {
                    $db->rollback();
                    return [
                        'table'     => $table,
                        'record_id' => $row['id'] ?? null,
                        'error'     => $e->getMessage()
                    ];
                }
            }
        }

        return ['table' => '', 'record_id' => null, 'error' => 'Could not isolate failing record'];
    }
    
    // =========================================================
    // PULL DATA v2 — Per-record UPSERT with error isolation
    // Runs on LOCAL, pulls data from cloud
    // =========================================================
    public function pull_data($cashierid = null)
    {
        $this->autoRender = false;
        $this->loadModel('Location');
        $this->loadModel('User');
        $this->loadModel('Sync_error');

        $maxAttempts = sync_max_quarantine_attempts;

        if (!empty($cashierid)) {
            $locationIds = $this->User->find('list', [
                'fields' => ['location_id', 'location_id'],
                'conditions' => [
                    'id' => (int)$cashierid,
                    'status' => 1
                ]
            ]);
        } else {
            $locationIds = sync_store_id;
        }

        $locationIds = array_values($locationIds);
        if (empty($locationIds)) {
            echo json_encode(['success' => false, 'message' => 'No locations found']);
            return;
        }

        $locationStr = implode(',', $locationIds);
        $liveUrl     = sync_link;
        $batchSize   = 200;

        $tables = pull_tables;

        $db = ConnectionManager::getDataSource('default');

        $summary = ['tables_processed' => 0, 'records_synced' => 0, 'records_failed' => 0];

        foreach ($tables as $table => $model) {

            $this->loadModel($model);
            $page = 1;

            while (true) {

                $url = $liveUrl . "/get_unsynced_data_batch/$locationStr"
                     . "?table=$table&page=$page&limit=$batchSize";

                $ch = curl_init($url);
                curl_setopt_array($ch, [
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_TIMEOUT => 30
                ]);
                $response = curl_exec($ch);
                $curlErr  = curl_error($ch);
                curl_close($ch);

                if (!empty($curlErr)) {
                    CakeLog::write('error', "[PULL][$table] cURL: $curlErr");
                    break;
                }

                $json = json_decode($response, true);

                if (empty($json['data'])) break;

                $records   = $json['data'];
                $successIds = [];
                $failedIds  = [];

                foreach ($records as $row) {
                    $row = $row[$model] ?? $row;
                    if (!is_array($row) || empty($row)) continue;

                    $recordId = $row['id'] ?? null;

                    $columns = array_keys($row);
                    $escaped = [];
                    foreach ($columns as $col) {
                        $escaped[] = $db->value($row[$col], 'string');
                    }

                    $updates = [];
                    foreach ($columns as $col) {
                        if (!in_array($col, ['id', 'created'])) {
                            $updates[] = "`$col` = VALUES(`$col`)";
                        }
                    }

                    $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                         . "VALUES (" . implode(',', $escaped) . ") "
                         . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

                    try {
                        $db->query($sql);
                        $successIds[] = (int)$recordId;
                        $summary['records_synced']++;
                    } catch (Exception $e) {
                        CakeLog::write('error', "[PULL][$table][id=$recordId] " . $e->getMessage());
                        $failedIds[] = (int)$recordId;
                        $summary['records_failed']++;

                        $this->_logSyncError(null, $table, $recordId, $e->getMessage(), $row, 'pull');
                    }
                }

                if (!empty($successIds)) {
                    $this->notify_live_item_sync($liveUrl, $table, $successIds, $locationIds);
                }

                if (count($records) < $batchSize) break;
                $page++;
            }

            $summary['tables_processed']++;
        }

        $this->notify_live_location_sync($liveUrl, $locationIds);

        $summary['completed_at'] = date('Y-m-d H:i:s');
        $this->_updateLastSyncTime('pull', $summary);

        echo json_encode(['success' => true, 'message' => 'Pull completed', 'summary' => $summary]);
    }
    
    private function notify_live_item_sync($liveUrl, $table, $ids, $locationIds)
    {
        $ch = curl_init($liveUrl . "/mark_data_synced_batch");
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => http_build_query([
                'table' => $table,
                'ids'   => json_encode($ids),
                'location_ids' => json_encode($locationIds)
            ]),
            CURLOPT_TIMEOUT => 15
        ]);
        curl_exec($ch);
        curl_close($ch);
    }
    
    private function notify_live_location_sync($liveUrl, $locationIds)
    {
        $ch = curl_init($liveUrl . "/mark_location_synced_batch");
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => http_build_query([
                'location_ids' => json_encode($locationIds)
            ]),
            CURLOPT_TIMEOUT => 15
        ]);
        curl_exec($ch);
        curl_close($ch);
    }

    public function get_unsynced_data_batch($location_ids)
    {
        $this->autoRender = false;
    
        $table = $_GET['table'] ?? '';
        $page  = (int)($_GET['page'] ?? 1);
        $limit = (int)($_GET['limit'] ?? 200);
    
        if (!$table) {
            echo json_encode(['success' => false]);
            return;
        }
    
        $locationIds = array_map('intval', explode(',', $location_ids));
    
        $this->loadModel('Location');
        $targetLocations = $this->Location->find('list', [
            'fields' => ['id', 'id'],
            'conditions' => [
                'id' => $locationIds,
                'is_publish' => 0
            ]
        ]);
    
        if (empty($targetLocations)) {
            echo json_encode(['success' => true, 'data' => []]);
            return;
        }
    
        $tables = pull_tables;
    
        if (!isset($tables[$table])) {
            echo json_encode(['success' => false]);
            return;
        }
    
        $model = $tables[$table];
        $this->loadModel($model);
    
        $conditions = ['local_sync' => 0];
    
        if ($this->$model->hasField('location_id')) {
            $conditions['location_id'] = array_values($targetLocations);
        }
    
        $records = $this->$model->find('all', [
            'conditions' => $conditions,
            'limit' => $limit,
            'offset' => ($page - 1) * $limit,
            'order' => ['id' => 'ASC']
        ]);
    
        echo json_encode(['success' => true, 'data' => $records]);
    }
    
    public function mark_data_synced_batch()
    {
        $this->autoRender = false;
    
        $table = $_POST['table'] ?? '';
        $ids   = json_decode($_POST['ids'] ?? '[]', true);
        $locationIds = json_decode($_POST['location_ids'] ?? '[]', true);
    
        if (!$table || empty($ids)) {
            echo json_encode(['success' => false]);
            return;
        }
    
        $tables = pull_tables;
    
        if (!isset($tables[$table])) {
            echo json_encode(['success' => false]);
            return;
        }
    
        $model = $tables[$table];
        $this->loadModel($model);
        $this->loadModel('Location');
    
        $pending = $this->Location->find('count', [
            'conditions' => [
                'status' => 1,
                'is_publish' => 0
            ]
        ]);

        if ($this->$model->hasField('location_id')) {
            $publishedLocations = $this->Location->find('count', [
                'conditions' => [
                    'id'         => $locationIds,
                    'is_publish' => 0,
                    'status'     => 1
                ]
            ]);
        
            if ($publishedLocations == 1) {
                $this->$model->updateAll(
                    ['local_sync' => 1],
                    [
                        'id' => $ids,
                        'location_id' => $locationIds
                    ]
                );
            }
        } else {
            if ($pending == 1) {
                $this->$model->updateAll(
                    ['local_sync' => 1],
                    ['id' => $ids]
                );
            }
        }
    
        echo json_encode(['success' => true]);
    }
    
    public function mark_location_synced_batch()
    {
        $this->autoRender = false;
    
        $locationIds = json_decode($_POST['location_ids'] ?? '[]', true);
        if (empty($locationIds)) {
            echo json_encode(['success' => false]);
            return;
        }
    
        $this->loadModel('Location');
        $this->Location->updateAll(
            ['is_publish' => 1],
            [
                'id' => $locationIds,
                'is_publish' => 0
            ]
        );
    
        echo json_encode(['success' => true]);
    }

    // =========================================================
    // RETRY QUARANTINED — Re-attempt failed syncs periodically
    // =========================================================
    public function retry_quarantined()
    {
        $this->autoRender = false;
        $this->loadModel('Sync_error');

        $maxRetry   = 10;
        $billTables = bill_tables;
        $syncLink   = sync_link;

        $summary = ['retried' => 0, 'resolved' => 0, 'still_failing' => 0];

        // ----- Retry PUSH errors (bill-level) -----
        $failedBills = $this->Sync_error->find('all', [
            'conditions' => [
                'Sync_error.direction' => 'push',
                'Sync_error.resolved' => 0,
                'Sync_error.attempts <' => $maxRetry,
                'Sync_error.reference_id IS NOT NULL'
            ],
            'order' => ['Sync_error.attempts' => 'ASC'],
            'limit' => 20
        ]);

        foreach ($failedBills as $err) {
            $refId = $err['Sync_error']['reference_id'];
            $summary['retried']++;

            // Re-read current data for this bill from DB
            $billData = [];
            foreach ($billTables as $table => $model) {
                $this->loadModel($model);
                $rows = $this->$model->find('all', [
                    'conditions' => [
                        'reference_id' => $refId,
                        'sync' => 0
                    ]
                ]);
                if (!empty($rows)) {
                    foreach ($rows as $r) {
                        $billData[$table][] = $r[$model];
                    }
                }
            }

            if (empty($billData)) {
                // All records already synced — mark as resolved
                $this->Sync_error->updateAll(
                    ['Sync_error.resolved' => 1],
                    ['Sync_error.reference_id' => $refId, 'Sync_error.direction' => 'push', 'Sync_error.resolved' => 0]
                );
                $summary['resolved']++;
                continue;
            }

            $payload = [$refId => $billData];
            $url = $syncLink . "/sync_data_to_live";
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_POST           => true,
                CURLOPT_HTTPHEADER     => ['Content-Type: application/x-www-form-urlencoded'],
                CURLOPT_POSTFIELDS     => http_build_query(['sync_data' => json_encode($payload), 'mode' => 'bill']),
                CURLOPT_TIMEOUT        => 60
            ]);
            $resp = curl_exec($ch);
            curl_close($ch);

            $result = json_decode($resp, true);

            if (!empty($result['bills'][$refId]['success'])) {
                $retryReadTime = date('Y-m-d H:i:s');
                foreach ($billTables as $table => $tblModel) {
                    if (!empty($billData[$table])) {
                        $ids = array_column($billData[$table], 'id');
                        if ($ids) {
                            $this->loadModel($tblModel);
                            $idList = implode(',', array_map('intval', $ids));
                            $this->$tblModel->query(
                                "UPDATE `$table` SET sync = 1 "
                              . "WHERE id IN ($idList) AND modified <= '$retryReadTime'"
                            );
                        }
                    }
                }
                $this->Sync_error->updateAll(
                    ['Sync_error.resolved' => 1],
                    ['Sync_error.reference_id' => $refId, 'Sync_error.direction' => 'push', 'Sync_error.resolved' => 0]
                );
                $summary['resolved']++;
            } else {
                $newError = $result['bills'][$refId]['error'] ?? 'Retry failed';
                $this->Sync_error->updateAll(
                    [
                        'Sync_error.attempts' => 'Sync_error.attempts + 1',
                        'Sync_error.error_message' => "'" . addslashes($newError) . "'"
                    ],
                    ['Sync_error.reference_id' => $refId, 'Sync_error.direction' => 'push', 'Sync_error.resolved' => 0]
                );
                $summary['still_failing']++;
            }
        }

        // ----- Retry PULL errors (per-record) -----
        $failedPulls = $this->Sync_error->find('all', [
            'conditions' => [
                'Sync_error.direction' => 'pull',
                'Sync_error.resolved' => 0,
                'Sync_error.attempts <' => $maxRetry,
                'Sync_error.record_data IS NOT NULL'
            ],
            'order' => ['Sync_error.attempts' => 'ASC'],
            'limit' => 50
        ]);

        $db = ConnectionManager::getDataSource('default');

        foreach ($failedPulls as $err) {
            $table    = $err['Sync_error']['table_name'];
            $recordId = $err['Sync_error']['record_id'];
            $rowData  = json_decode($err['Sync_error']['record_data'], true);

            if (empty($rowData) || empty($table)) {
                $this->Sync_error->id = $err['Sync_error']['id'];
                $this->Sync_error->save(['resolved' => 1]);
                continue;
            }

            $summary['retried']++;

            $columns = array_keys($rowData);
            $escaped = [];
            foreach ($columns as $col) {
                $escaped[] = $db->value($rowData[$col], 'string');
            }

            $updates = [];
            foreach ($columns as $col) {
                if (!in_array($col, ['id', 'created'])) {
                    $updates[] = "`$col` = VALUES(`$col`)";
                }
            }

            $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) "
                 . "VALUES (" . implode(',', $escaped) . ") "
                 . "ON DUPLICATE KEY UPDATE " . implode(',', $updates);

            try {
                $db->query($sql);
                $this->Sync_error->id = $err['Sync_error']['id'];
                $this->Sync_error->save(['resolved' => 1]);
                $summary['resolved']++;
            } catch (Exception $e) {
                $this->Sync_error->id = $err['Sync_error']['id'];
                $this->Sync_error->save([
                    'attempts' => $err['Sync_error']['attempts'] + 1,
                    'error_message' => $e->getMessage()
                ]);
                $summary['still_failing']++;
            }
        }

        echo json_encode(['success' => true, 'summary' => $summary]);
    }

    // =========================================================
    // SYNC STATUS — Dashboard/API view of sync health
    // =========================================================
    public function sync_status()
    {
        $this->autoRender = false;
        $this->loadModel('Sync_error');

        $billTables    = bill_tables;
        $nonBillTables = non_bill_tables;

        $unsyncedCounts = [];
        foreach (array_merge($billTables, $nonBillTables) as $table => $model) {
            $this->loadModel($model);
            $count = $this->$model->find('count', ['conditions' => ['sync' => 0]]);
            if ($count > 0) {
                $unsyncedCounts[$table] = $count;
            }
        }

        $quarantined = $this->Sync_error->find('count', [
            'conditions' => ['resolved' => 0, 'attempts >=' => sync_max_quarantine_attempts]
        ]);

        $pendingRetry = $this->Sync_error->find('count', [
            'conditions' => ['resolved' => 0, 'attempts <' => sync_max_quarantine_attempts]
        ]);

        $recentErrors = $this->Sync_error->find('all', [
            'conditions' => ['resolved' => 0],
            'order' => ['modified' => 'DESC'],
            'limit' => 10
        ]);

        // Last sync times
        $lastSync = $this->_getLastSyncTimes();

        // Alerts
        $alerts = $this->_getActiveAlerts();

        echo json_encode([
            'success' => true,
            'unsynced_records' => $unsyncedCounts,
            'total_unsynced' => array_sum($unsyncedCounts),
            'quarantined_bills' => $quarantined,
            'pending_retry' => $pendingRetry,
            'recent_errors' => $recentErrors,
            'last_sync' => $lastSync,
            'alerts' => $alerts
        ]);
    }

    // =========================================================
    // LAST SYNC TIME — API endpoint for Dashboard/DSR views
    // =========================================================
    public function get_last_sync_time()
    {
        $this->autoRender = false;
        echo json_encode($this->_getLastSyncTimes());
    }

    // =========================================================
    // PRIVATE HELPERS
    // =========================================================

    /**
     * Log a sync error to the sync_errors table (upsert for bills).
     */
    private function _logSyncError($refId, $table, $recordId, $errMsg, $data, $direction)
    {
        $this->loadModel('Sync_error');

        if (!empty($refId)) {
            $existing = $this->Sync_error->find('first', [
                'conditions' => [
                    'Sync_error.reference_id' => $refId,
                    'Sync_error.direction' => $direction,
                    'Sync_error.resolved' => 0
                ]
            ]);
            if ($existing) {
                $this->Sync_error->id = $existing['Sync_error']['id'];
                $this->Sync_error->save([
                    'attempts' => $existing['Sync_error']['attempts'] + 1,
                    'error_message' => $errMsg,
                    'table_name' => $table,
                    'record_id' => $recordId
                ]);
                return;
            }
        }

        $this->Sync_error->create();
        $this->Sync_error->save([
            'reference_id' => $refId,
            'table_name' => $table,
            'record_id' => $recordId,
            'error_message' => $errMsg,
            'record_data' => is_array($data) ? json_encode($data) : $data,
            'direction' => $direction,
            'attempts' => 1,
            'resolved' => 0
        ]);
    }

    /**
     * Store last sync time in a file (lightweight, no extra DB table needed).
     */
    private function _updateLastSyncTime($direction, $summary)
    {
        $filePath = TMP . 'sync_status.json';
        $existing = [];
        if (file_exists($filePath)) {
            $existing = json_decode(file_get_contents($filePath), true) ?: [];
        }

        $existing[$direction] = [
            'time' => date('Y-m-d H:i:s'),
            'bills_synced' => $summary['bills_synced'] ?? 0,
            'bills_failed' => $summary['bills_failed'] ?? 0,
            'non_bill_synced' => $summary['non_bill_synced'] ?? 0,
            'records_synced' => $summary['records_synced'] ?? 0,
            'records_failed' => $summary['records_failed'] ?? 0,
        ];

        file_put_contents($filePath, json_encode($existing, JSON_PRETTY_PRINT));
    }

    /**
     * Read last sync times from the status file.
     */
    private function _getLastSyncTimes()
    {
        $filePath = TMP . 'sync_status.json';
        if (!file_exists($filePath)) {
            return ['push' => null, 'pull' => null];
        }
        return json_decode(file_get_contents($filePath), true) ?: ['push' => null, 'pull' => null];
    }

    /**
     * Check for alert conditions and log them.
     */
    private function _checkSyncAlerts()
    {
        $this->loadModel('Sync_error');

        $alertThreshold = 5;
        $alerts = [];

        // Check for repeatedly failing bills
        $criticalErrors = $this->Sync_error->find('all', [
            'conditions' => [
                'resolved' => 0,
                'attempts >=' => $alertThreshold,
                'direction' => 'push'
            ],
            'order' => ['attempts' => 'DESC'],
            'limit' => 20
        ]);

        foreach ($criticalErrors as $err) {
            $refId = $err['Sync_error']['reference_id'];
            $attempts = $err['Sync_error']['attempts'];
            $lastErr = $err['Sync_error']['error_message'];

            $alert = "[SYNC ALERT] Bill $refId has failed $attempts times. Last error: $lastErr";
            CakeLog::write('error', $alert);
            $alerts[] = $alert;
        }

        // Check total unsynced backlog
        $billTables = bill_tables;
        $totalUnsynced = 0;
        foreach ($billTables as $table => $model) {
            $this->loadModel($model);
            $totalUnsynced += $this->$model->find('count', ['conditions' => ['sync' => 0]]);
        }

        if ($totalUnsynced > 500) {
            $alert = "[SYNC ALERT] High unsynced backlog: $totalUnsynced records across bill tables";
            CakeLog::write('error', $alert);
            $alerts[] = $alert;
        }

        // Write alerts to file for dashboard consumption
        $filePath = TMP . 'sync_alerts.json';
        $existing = [];
        if (file_exists($filePath)) {
            $existing = json_decode(file_get_contents($filePath), true) ?: [];
        }

        $existing[] = [
            'time' => date('Y-m-d H:i:s'),
            'alerts' => $alerts,
            'total_unsynced' => $totalUnsynced,
            'critical_bills' => count($criticalErrors)
        ];

        // Keep only last 100 alert entries
        if (count($existing) > 100) {
            $existing = array_slice($existing, -100);
        }

        file_put_contents($filePath, json_encode($existing, JSON_PRETTY_PRINT));
    }

    /**
     * Get active alerts for the dashboard.
     */
    private function _getActiveAlerts()
    {
        $filePath = TMP . 'sync_alerts.json';
        if (!file_exists($filePath)) {
            return [];
        }
        $all = json_decode(file_get_contents($filePath), true) ?: [];
        return array_slice($all, -5);
    }


}