<?php
defined('BASEPATH') OR exit('No direct script access allowed');

use GuzzleHttp\Client;

class ShopifySync extends CI_Controller {
    
    private $shopifyClient;

    public function __construct() {
        parent::__construct();
        $this->load->database();

        $this->shopifyClient = new Client([
            'base_uri' => SHOP_URL,
            'headers' => [
                'Content-Type' => 'application/json',
                'X-Shopify-Access-Token' => SHOP_TOKEN
            ]
        ]);
    }

    // Fetch all product IDs from the sh_product table
    private function getDatabaseProductIds() {
        $query = $this->db->select('product_id')->from('sh_product')->get();
        return array_column($query->result_array(), 'product_id');
    }

    // Fetch all product IDs from Shopify
    private function getShopifyProductIds() {
        $shopifyProducts = $this->fetchAllShopifyProducts();
        return array_column($shopifyProducts, 'id'); // Extract only the product IDs
    }

    // Fetch all products from Shopify
    private function fetchAllShopifyProducts() {
        $products = [];
        $nextPageInfo = null;

        do {
            $endpoint = '/admin/api/2024-01/products.json?limit=250';
            if ($nextPageInfo) {
                $endpoint .= "&page_info=" . $nextPageInfo;
            }

            $response = $this->shopifyClient->request('GET', $endpoint, [
                'http_errors' => false
            ]);

            if ($response->getStatusCode() !== 200) {
                die('Error: Shopify API request failed. Status Code: ' . $response->getStatusCode());
            }

            $responseData = json_decode($response->getBody(), true);
            if (isset($responseData['products'])) {
                $products = array_merge($products, $responseData['products']);
            }

            // Check for pagination
            $linkHeader = $response->getHeader('Link');
            if (!empty($linkHeader) && preg_match('/<([^>]+)>;\s*rel="next"/', $linkHeader[0], $matches)) {
                parse_str(parse_url($matches[1], PHP_URL_QUERY), $queryParams);
                $nextPageInfo = $queryParams['page_info'] ?? null;
            } else {
                $nextPageInfo = null;
            }
        } while ($nextPageInfo);

        return $products;
    }

    // Find missing products in Shopify
    private function findMissingProducts() {
        $dbProductIds = $this->getDatabaseProductIds();
        $shopifyProductIds = $this->getShopifyProductIds();

        // Find products in the database that are NOT in Shopify
        return array_diff($dbProductIds, $shopifyProductIds);
    }
 public function addProductToSkuM1()
{
    $params = [];
    $response = $this->getJewelrypr($params);

    // Response ka type check karein
    if (is_string($response)) {
        $data = json_decode($response, true);
    } elseif (is_array($response)) {
        $data = $response;
    } else {
        die("Invalid response format from getJewelrypr()");
    }

    if (!is_array($data)) {
        die("Error decoding JSON response");
    }

    foreach ($data as $product) {
        if (isset($product['sku'])) {
            $sku = $product['sku'];

            // Database query
            $existingVariant = $this->db->select('*')->from('sh_product_variant')->where('sku', $sku)->get()->row();

            if (!$existingVariant) {
                 $this->db->query("UPDATE sh_product_raw SET is_upload = 0 WHERE BINARY sku = '$sku'");
                echo  $sku . "<br>";
            }
        }
    }
}


public function checkMissingProducts() {
    // Get missing products from Shopify
    $missingProductIds = $this->findMissingProducts();

    if (!empty($missingProductIds)) {
        echo "Missing Product IDs (Present in DB but NOT in Shopify): <br>";
        echo "<pre>" . print_r($missingProductIds, true) . "</pre>";

        foreach ($missingProductIds as $productId) {
        //     // Start transaction for deleting related records
        //     $this->db->trans_start();

        //     try {
        //         // Delete related records from various tables
        //         $this->db->where('product_id', $productId)->delete('sh_product');
        //         $this->db->where('product_id', $productId)->delete('sh_product_image');
        //         $this->db->where('product_id', $productId)->delete('sh_product_metafield');

        //         // Get all product variants for this product ID
               $variants = $this->db->select('*')->where('product_id', $productId)->get('sh_product_variant')->result_array();

              if (!empty($variants)) {
                    foreach ($variants as $variant) {
                      $sku = $variant['sku'];

        //                 // // Get all products with this SKU
                        $skuVariants = $this->db->select('*')->where('sku', $sku)->get('sh_product_variant')->result_array();
// print_pre($skuVariants); die;
        //                 // // Check if any product with this SKU exists in Shopify
                foreach ($skuVariants as $skuVariant) {
                         $skuExistsInShopify = $this->checkSkuInShopify($skuVariant['product_id']);

        //                 // // **Check if SKU exists in the external API** and update only if missing in Shopify
                            $skuExistsInExternalApi = $this->checkSkuInExternalApi($skuVariant['sku']);

        //                 // // Only update is_upload = 0 if the SKU is missing in Shopify and found in the external API
                            if (!$skuExistsInShopify && $skuExistsInExternalApi) {
                                  echo "<pre>sku" . print_r($skuVariant['sku'], true) . "</pre>";
        //                 //     $this->db->query("UPDATE sh_product_raw SET is_upload = 0 WHERE BINARY sku = '$sku'");
                            }
                         }
        //                 // Delete variant-specific data
        //                 $this->db->where('variant_id', $variant['variant_id'])->delete('sh_variant_metafield');
                     }
                 }

        //         // Log the deleted product
        //         $record = [
        //             'product_id' => $productId,
        //             'resposne_delete' => json_encode($variants)
        //         ];
        //         $this->db->insert('shopify_missing_product_delete', $record);

        //         // Finally, delete the product variants
        //         $this->db->where('product_id', $productId)->delete('sh_product_variant');

        //         // Commit transaction if all queries are successful
        //         $this->db->trans_complete();

        //         if ($this->db->trans_status() === FALSE) {
        //             throw new Exception("Error deleting data for product ID: {$productId}");
        //         }
        //     } catch (Exception $e) {
        //         // Rollback transaction in case of any failure
        //         $this->db->trans_rollback();
        //         echo "Error processing product ID: {$productId}. " . $e->getMessage() . "<br>";
        //     }
        }
    } else {
        echo "All products are synced between DB and Shopify.";
    }
}

// New method to check if SKU exists in the external API
private function checkSkuInExternalApi($sku) {
    // Make an API call to the external service to check if the SKU exists
    $params = ['sku' => $sku];  // Pass SKU as a parameter to the external API
    $products = $this->getJewelrypr($params);  // Fetch jewelry products from the external API
    
    foreach ($products as $product) {
        // Check if the SKU exists in the API response
        if (isset($product['sku']) && $product['sku'] === $sku) {
            return true;  // SKU exists in the API
        }
    }
    return false;  // SKU not found in the API
}

// Ensure that `getJewelrypr` method is already implemented as provided

private function checkSkuInShopify($product_id) {
    //foreach ($skuVariants as $skuVariant) {
        // Check if any product with this SKU exists in Shopify
        if ($this->isProductInShopify($product_id)) {
            return true; // If any variant exists, return true
        //}
    }
    return false; // No SKU found in Shopify
}

private function isProductInShopify($productId) {
    try {
        $response = $this->shopifyClient->request('GET', "/admin/api/2024-01/products/{$productId}.json", [
            'http_errors' => false
        ]);

        // Check if the response status code is 200 (OK)
        if ($response->getStatusCode() === 200) {
            return true;  // Product found in Shopify
        } else {
            return false; // Product not found or some other error
        }
    } catch (Exception $e) {
        // Log the error and return false if something goes wrong
        echo "Error fetching product status from Shopify: " . $e->getMessage() . "<br>";
        return false;
    }
}


    // -----------------------------------------------------------
    public function deleteProductsFromShopifyAndDatabase() {
    // List of missing product IDs that need to be deleted from both Shopify and the database
    $missingProductIds = [
        '8449070203070'
    ];

    foreach ($missingProductIds as $productId) {
        // Delete from the database
        $this->db->where('product_id', $productId)->delete('sh_product');
        $this->db->where('product_id', $productId)->delete('sh_product_image');
        $this->db->where('product_id', $productId)->delete('sh_product_metafield');

        // Find and delete product variants and their metafields from the database
        $result = $this->db->select('*')->where('product_id', $productId)->get('sh_product_variant')->result_array();
        if (!empty($result)) {
            foreach ($result as $value) {
                // Delete variant metafields from the database
                $this->db->where('variant_id', $value['variant_id'])->delete('sh_variant_metafield');
            }
        }

        
        // Delete product variants from the database
        $this->db->where('product_id', $productId)->delete('sh_product_variant');

        // Delete from Shopify
        $this->deleteProductFromShopify($productId);
    }
}

    // -----------------------------------------------------------
public function checkMissingProductsInDB() {
    // Fetch product IDs from the database and Shopify
    $dbProductIds = $this->getDatabaseProductIds();
    $shopifyProductIds = $this->getShopifyProductIds();

    // Find products in Shopify that are NOT in the database
    $missingProductIdsInDB = array_diff($shopifyProductIds, $dbProductIds);

    // Check if there are any missing products
    if (!empty($missingProductIdsInDB)) {
        echo "Missing Product IDs (Present in Shopify but NOT in DB): <br>";
        echo "<pre>" . print_r($missingProductIdsInDB, true) . "</pre>";

        // Delete missing products from Shopify
        foreach ($missingProductIdsInDB as $productId) {
            $this->deleteProductFromShopify($productId);
        }
    } else {
        echo "All products from Shopify are present in the database.";
    }
}

// Function to delete product from Shopify
private function deleteProductFromShopify($productId) {
    try {
        // Make the DELETE request to Shopify to delete the product
        $response = $this->shopifyClient->request('DELETE', "/admin/api/2024-01/products/{$productId}.json");

        // Check for successful deletion
        if ($response->getStatusCode() == 200) {
            echo "Product with ID {$productId} has been successfully deleted from Shopify.<br>";
        } else {
            echo "Error deleting product with ID {$productId}. Status Code: " . $response->getStatusCode() . "<br>";
        }
    } catch (Exception $e) {
        echo "Error deleting product with ID {$productId}: " . $e->getMessage() . "<br>";
    }
}
 //-------------------------------archived code------------------------- 
// Fetch archived product IDs from the sh_product table
public function getArchivedDatabaseProductIds() {
    $query = $this->db->select('product_id')
                      ->from('sh_product')
                      ->like('status', 'archived')
                      ->get();
    
    // Print the number of rows
   // print_r($query->num_rows()); 

    // Return an array of product IDs
    return array_column($query->result_array(), 'product_id');
}
// Delete archived products in Shopify and then delete them from the database
private function deleteArchivedProductsFromShopify($productIds) {
    foreach ($productIds as $productId) {
        try {
            // Step 1: Delete the product from Shopify
            $response = $this->shopifyClient->request('DELETE', "/admin/api/2024-01/products/{$productId}.json", [
                'http_errors' => false
            ]);
            
            if ($response->getStatusCode() === 200) {
                echo "Product ID {$productId} successfully deleted from Shopify.<br>";

                // Step 2: Now delete the product from the database
                $this->deleteProductFromDatabase($productId);
            } else {
                echo "Failed to delete Product ID {$productId} from Shopify. Status Code: " . $response->getStatusCode() . "<br>";
            }
        } catch (Exception $e) {
            echo "Error occurred while deleting Product ID {$productId} from Shopify: " . $e->getMessage() . "<br>";
        }
    }
}

// Delete product and related data from the database
private function deleteProductFromDatabase($productId) {
    // Delete from the sh_product table
    $this->db->where('product_id', $productId)->delete('sh_product');
    $this->db->where('product_id', $productId)->delete('sh_product_image');
    $this->db->where('product_id', $productId)->delete('sh_product_metafield');

    // Delete related product variants
    $result = $this->db->select('*')->where('product_id', $productId)->get('sh_product_variant')->result_array();
    if (!empty($result)) {
        foreach ($result as $key => $value) {
            // Delete related variant metafields
            $this->db->where('variant_id', $value['variant_id'])->delete('sh_variant_metafield');
        }
    }

    // Finally, delete the product variants
    $this->db->where('product_id', $productId)->delete('sh_product_variant');
}

public function deleteArchivedProducts() {
    // Step 1: Get archived product IDs from the database
    $archivedProductIds = $this->getArchivedDatabaseProductIds();
    
    // Step 2: Check if there are archived products in the database
    if (!empty($archivedProductIds)) {
        // Step 3: Delete these archived products from Shopify
        $this->deleteArchivedProductsFromShopify($archivedProductIds);
    } else {
        echo "No archived products found in the database.";
    }
}
//-------------------------------archived end code-------------------------

//-------------------------------active code-------------------------
// Fetch active products from the database
private function getActiveDatabaseProductIds() {
    $query = $this->db->select('product_id')
                      ->from('sh_product')
                      ->like('status', 'active')
                      ->get();
    
    // Return an array of product IDs
    return array_column($query->result_array(), 'product_id');
}
// Fetch product status from Shopify by product ID
private function getShopifyProductStatus($productId) {
    try {
        $response = $this->shopifyClient->request('GET', "/admin/api/2024-01/products/{$productId}.json", [
            'http_errors' => false
        ]);

        if ($response->getStatusCode() === 200) {
            $responseData = json_decode($response->getBody(), true);
            return $responseData['product']['status'] ?? null; // Returns 'archived' or 'active'
        } else {
            return null; // If Shopify API call fails, return null
        }
    } catch (Exception $e) {
        echo "Error fetching product status from Shopify: " . $e->getMessage() . "<br>";
        return null;
    }
}
// Update the product status to active on Shopify
private function updateShopifyProductStatus($productId) {
    try {
        $response = $this->shopifyClient->request('PUT', "/admin/api/2024-01/products/{$productId}.json", [
            'json' => [
                'product' => [
                    'id' => $productId,
                    'status' => 'active'
                ]
            ],
            'http_errors' => false
        ]);

        if ($response->getStatusCode() === 200) {
            echo "Product ID {$productId} status successfully updated to 'active' on Shopify.<br>";
        } else {
            echo "Failed to update Product ID {$productId} status on Shopify. Status Code: " . $response->getStatusCode() . "<br>";
        }
    } catch (Exception $e) {
        echo "Error updating product status on Shopify: " . $e->getMessage() . "<br>";
    }
}
// Check and update product statuses from DB to Shopify
public function updateActiveProductsInShopify() {
    // Step 1: Get active product IDs from the database
    $activeProductIds = $this->getActiveDatabaseProductIds();
    
    // Step 2: Check each product in Shopify and update if necessary
    foreach ($activeProductIds as $productId) {
        // Step 3: Fetch product status from Shopify
        $shopifyStatus = $this->getShopifyProductStatus($productId);

        // Step 4: If product is archived on Shopify, update it to active
        if ($shopifyStatus === 'archived') {
            $this->updateShopifyProductStatus($productId);
        } else {
            echo "Product ID {$productId} is already active on Shopify.<br>";
        }
    }
}
//-----------------------end-------------------------------------

    // Fetch all setcodes from the database (sh_product_raw table)
    private function getSetcodesFromDatabase() {
        // Fetch all setcodes from the `sh_product_raw` table
        $query = $this->db->select('setcode')
                          ->from('sh_product_raw')
                          ->get();
        
        return array_column($query->result_array(), 'setcode'); // Return only setcodes as an array
    }

    // Fetch jewelry product records from the external API
   private function getJewelrypr($params = []) { 
    $record = [];
    $headers = [];
    
    try {
        // Making the GET request to the Shopify API or other external service
        $response = $this->shopifyClient->request('GET', 'https://shop.simplexdiam.com/admin/auto_shopify_api', [
            'http_errors' => false,
            'headers' => $headers,
            'query' => $params
        ]);

        // Check if the request was successful
        if ($response->getStatusCode() > 300) {
            return $record; // If the response is not successful, return empty record
        }

        // Decode the response body to a PHP array
        $body = $response->getBody();
        $parsedBody = json_decode($body, true);

        // Filter the records for only those with "status" = "active"
        $activeProducts = array_filter($parsedBody['records'], function($product) {
            return isset($product['status']) && $product['status'] === 'active';
        });

        // Reindex the filtered products array to maintain the keys
        $record = array_values($activeProducts);

        // Log the API request and response for debugging or record-keeping
        $dataLog = [
            'request' => json_encode($params),
            'response' => json_encode($parsedBody),
            'method' => 'GET',
            'endpoint' => "https://shop.simplexdiam.com/admin/auto_shopify_api",
            'added_at' => date('Y-m-d h:i'),
        ];
        $this->db->insert('external_api_log', $dataLog);

    } catch (\Exception $e) {
        // Handle any exceptions if the API call fails
        echo "Error: " . $e->getMessage();
    }

    return $record;
}
    // Find products from the API that are missing in the database (i.e., missing setcodes)
    public function findMissingProducts_row() {
        // Step 1: Get all setcodes from the database
        $dbSetcodes = $this->getSetcodesFromDatabase();
        
        // Step 2: Fetch jewelry data from the external API
        // Pass an empty array or customize the params as needed
        $params = [];
        $apiRecords = $this->getJewelrypr($params);

        // Step 3: Find products that are in the API but not in the database
        $missingProducts = [];
        foreach ($apiRecords as $apiRecord) {
            if (!in_array($apiRecord['setcode'], $dbSetcodes)) {
                // If the setcode is not found in the database, it's missing
                $missingProducts[] = $apiRecord;

            }
        }

        // Step 4: Log or display missing products
        if (!empty($missingProducts)) {
            foreach ($missingProducts as $missingProduct) {
                echo "Missing Product (Setcode: {$missingProduct['setcode']})<br>";
                // Optionally, you can log missing products to the database in other ways or send notifications
            }
        } else {
            echo "No missing products found in the API.<br>";
        }
    }
    
public function checkMissingJewelryProducts() {
    // Step 1: Fetch all records from the `sh_product_raw` table (get sku list)
    $dbProducts = $this->db->select('sku')->get('sh_product_raw')->result_array();
    $dbSkuList = array_column($dbProducts, 'sku'); // Extract SKU list from database records

    // Debug: Print the number of records in the database
    echo "Number of products in the database: " . count($dbSkuList) . "<br>";

    // Step 2: Get the active jewelry products from the external API
    $apiProducts = $this->getJewelrypr(); // Call to getJewelrypr API method

    // Extract the SKU list from the API response
    $apiSkuList = array_column($apiProducts, 'sku');

    // Debug: Print the number of records in the API
    echo "Number of products in the API: " . count($apiSkuList) . "<br>";

    // Step 3: Find the products that exist in the database but not in the API
    $missingSkuList = array_diff($dbSkuList, $apiSkuList);

    // Debug: Check the missing products
    echo "Missing products (in DB but not in API):<br>";
    echo "<pre>" . print_r($missingSkuList, true) . "</pre>";

    // Step 4: Count how many records are missing
    $missingCount = count($missingSkuList);

    // Step 5: Display the count of missing products
    echo "Total missing products: " . $missingCount . "<br>";

    // Step 6: If there are missing products, delete them from the database (currently commented out)
    if ($missingCount > 0) {
        foreach ($missingSkuList as $sku) {
            // Commenting out the deletion code:
          //  $this->db->where('sku', $sku)->delete('sh_product_raw');
            echo "Product with SKU: {$sku} would be deleted.<br>";
        }
    } else {
        echo "All products are present in the database and API.<br>";
    }
}
public function updateMissingJewelryProducts() {
    // Step 1: Update the `is_upload` column in `sh_product_raw` where SKU does not exist in `sh_product_variant`
    $this->db->query("
        UPDATE sh_product_raw sr
        SET sr.is_upload = 0
        WHERE sr.sku NOT IN (SELECT sv.sku FROM sh_product_variant sv)
    ");

    // Step 2: Check how many rows were affected (updated)
    $affectedRows = $this->db->affected_rows();

    // Step 3: Output the result of the update
    echo "Number of rows updated: " . $affectedRows . "<br>";

    // Step 4: Provide a message if no records were updated
    if ($affectedRows > 0) {
        echo "Missing products' is_upload field has been set to 0.<br>";
    } else {
        echo "No products were missing in the `sh_product_variant` table.<br>";
    }
}
public function checkMissingProductTitlesInDB() {
    // Step 1: Fetch product titles from the database
    $dbProductTitles = $this->getDatabaseProductTitles();
    
    // Step 2: Fetch all products from Shopify
    $shopifyProducts = $this->fetchAllShopifyProducts();
    
    // Extract product titles from Shopify products
    $shopifyProductTitles = array_column($shopifyProducts, 'title');
    
    // Step 3: Find product titles in Shopify that are NOT in the database
    $missingProductTitlesInDB = array_diff($shopifyProductTitles, $dbProductTitles);

    // Step 4: Check if there are any missing product titles
    if (!empty($missingProductTitlesInDB)) {
        echo "Missing Product Titles (Present in Shopify but NOT in DB): <br>";
        echo "<pre>" . print_r($missingProductTitlesInDB, true) . "</pre>";
    } else {
        echo "All products from Shopify are present in the database.";
    }
}

private function getDatabaseProductTitles() {
    // Fetch titles from the database (sh_product table)
    $query = $this->db->select('title')->get('sh_product_raw');
    $result = $query->result_array();
    return array_column($result, 'title');  // Extract titles into an array
}

private function getShopifyProductIdByTitle($title) {
    // This method fetches product ID from Shopify by title (if necessary)
    $products = $this->fetchAllShopifyProducts(); // Fetch all products from Shopify
    
    // Search for the product by title
    foreach ($products as $product) {
        if ($product['title'] == $title) {
            return $product['id'];  // Return the product ID
        }
    }

    return null;  // Return null if product ID is not found
}
public function checkMissingProductTitlesInShopify() {
    // Step 1: Fetch product titles from the external source (getJewelrypr)
    $externalProducts = $this->getJewelrypr(); // Fetch products from the external source
    $externalProductTitles = array_column($externalProducts, 'title'); // Extract titles

    // Step 2: Fetch all products from Shopify
    $shopifyProducts = $this->fetchAllShopifyProducts(); // Fetch all products from Shopify
    $shopifyProductTitles = array_column($shopifyProducts, 'title'); // Extract titles

    // Step 3: Find titles from external source that are NOT in Shopify
    $missingProductTitlesInShopify = array_diff($externalProductTitles, $shopifyProductTitles);

    // Step 4: Check if there are any missing product titles
    if (!empty($missingProductTitlesInShopify)) {
        echo "Missing Product Titles (Present in the external source but NOT in Shopify): <br>";
        echo "<pre>" . print_r($missingProductTitlesInShopify, true) . "</pre>";
    } else {
        echo "All products from the external source are present in Shopify.";
    }
}

public function checkMissingProductsInVariant() {
    // SQL query using NOT EXISTS to find missing product_ids in sh_product_variant
    $query = $this->db->query("
        SELECT * 
        FROM sh_product_variant sv 
        WHERE NOT EXISTS (
            SELECT 1 
            FROM sh_product sp 
            WHERE sp.product_id = sv.product_id
        )
    ");

    // Check if there are any results
    if ($query->num_rows() > 0) {
        // Fetch the missing product_ids
        $missingProducts = $query->result_array();
        echo "Missing Products:<br>";
        echo "<pre>" . print_r($missingProducts, true) . "</pre>";
    } else {
        echo "All product_ids in sh_product_variant have matching entries in sh_product.<br>";
    }
}
public function deleteMissingProductsInVariant() {
    // Step 1: Fetch the missing products from sh_product_variant where sku is missing in sh_product_raw
    $query = $this->db->query("
        SELECT sv.product_id, sv.variant_id, sv.sku
        FROM sh_product_variant sv
        LEFT JOIN sh_product_raw sr ON sr.sku = sv.sku
        WHERE sr.sku IS NULL
    ");

    // Step 2: Check if there are any missing products
    if ($query->num_rows() > 0) {
        // Step 3: Loop through the missing products
        $missingProducts = $query->result_array();

        foreach ($missingProducts as $product) {
            $productId = $product['product_id'];
            $variantId = $product['variant_id'];
            $sku = $product['sku'];

            // Step 4: Delete related records from sh_product, sh_product_image, sh_product_metafield
            $this->db->where('product_id', $productId)->delete('sh_product');
            $this->db->where('product_id', $productId)->delete('sh_product_image');
            $this->db->where('product_id', $productId)->delete('sh_product_metafield');

            // Step 5: Delete product variants from sh_product_variant
            $this->db->where('product_id', $productId)->delete('sh_product_variant');

            // Step 6: Check if variant exists and delete related variant metafields
            if (!empty($variantId)) {
                $this->db->where('variant_id', $variantId)->delete('sh_variant_metafield');
            }

            // Step 7: Delete the product from sh_product_raw based on sku
            $this->db->where('sku', $sku)->delete('sh_product_raw');

            // Step 8: Output deletion message for the SKU and product_id
            echo "Deleted product with SKU: " . $sku . " and product_id: " . $productId . "<br>";
        }

        echo "Missing products deleted successfully.";
    } else {
        echo "No missing products found.";
    }
}
// public function updateSkuFromApi() {
//     $products['records'] = $this->getJewelrypr();

//     if (empty($products['records'])) {
//         return "No active products found in API.";
//     }

//     foreach ($products['records'] as $product) {
//         if (!isset($product['variants']) || empty($product['variants'])) {
//             continue; // Skip if no variants
//         }

//         foreach ($product['variants'] as $variant) {
//             $metal_v = $variant['metal_v']; // `sim_sku` in DB
//             $sku_api = $product['sku']; // New `sku` from API

//             // Update ALL rows with matching sim_sku
//               $this->db->where('sim_sku', $metal_v) // Removed BINARY
//                      ->update('sh_product_variant', ['sku' => $sku_api]);

//             echo "Updated SKU for sim_sku: $metal_v -> New SKU: $sku_api\n";
//         }
//     }
// }

public function activateAllProducts() {
    // Fetch all product IDs from database
    $products = $this->db->select('product_id')->get('sh_product')->result_array();

    if(empty($products)) {
        echo "No products found to update.";
        return;
    }

    foreach ($products as $product) {
        $product_id = $product['product_id'];

        // Create JSON body to update status to active
        $jsonBody = json_encode([
            'product' => [
                'id' => $product_id,
                'status' => 'active',  
                'published' => true     
                ]
        ]);

        // Call updateProduct function
        $response = $this->updateProduct($jsonBody, $product_id);

        if (!empty($response)) {
            echo " Product ID $product_id updated to active in Shopify.<br>";
        } else {
            echo "Failed to update Product ID $product_id.<br>";
        }
    }
}
private function updateProduct($jsonBody, $product_id) {
		if(empty($jsonBody)) {
			return [];
		}
		$this->delay(1);

		$client = new Client([
		    'base_uri' => SHOP_URL
		]);

		$headers = [
			'Content-Type' => 'application/json',
		  	'X-Shopify-Access-Token' => SHOP_TOKEN
		];
		  
		$response = $client->request('PUT', "products/$product_id.json", [
			'http_errors' => false,
			'headers' => $headers,
		    'body' => $jsonBody
		]);
	
		$body = $response->getBody();
		$parsedBody = json_decode($body);

		if($response->getStatusCode() > 300) {
			$dataError = [
				'request' => $jsonBody,
				'response' => json_encode($parsedBody),
				'method' => 'PUT',
				'endpoint' => "products/$product_id.json",
				'added_at' => date('Y-m-d h:i'),
			];
			$this->db->insert('error_log', $dataError);

		    return [];
		}

		return $parsedBody;
	}
		private function delay($sec) {
		sleep($sec);
	}
	
// 	----------------------------------------------------

public function updateSkuFromApi() {
    $products['records'] = $this->getJewelrypr();

    if (empty($products['records'])) {
        return "No active products found in API.";
    }

    $apiSimSkuList = []; // Store all `sim_sku` from API

    foreach ($products['records'] as $product) {
        if (!isset($product['variants']) || empty($product['variants'])) {
            continue; // Skip if no variants
        }

        foreach ($product['variants'] as $variant) {
            $metal_v = $variant['metal_v']; // `sim_sku` in DB
            $sku_api = $product['sku']; // New `sku` from API

            // Store `sim_sku` for later comparison
            $apiSimSkuList[] = $metal_v;

            // Update ALL rows with matching `sim_sku`
            $this->db->where('sim_sku', $metal_v)
                     ->update('sh_product_variant', ['sku' => $sku_api]);

            echo "Updated SKU for sim_sku: $metal_v -> New SKU: $sku_api\n";
        }
    }

    // **Find missing sim_sku values in the DB that are NOT in the API**
    $dbSimSkuRecords = $this->db->select('sim_sku, product_id')
                                ->get('sh_product_variant')
                                ->result_array();
    foreach ($dbSimSkuRecords as $record) {
        $dbSimSku = $record['sim_sku'];
        $productId = $record['product_id'];
        if (!in_array($dbSimSku, $apiSimSkuList)) {
            // **Delete from Shopify**
            $deletedFromShopify = $this->deleteProductFromShopify($productId);

            if ($deletedFromShopify) {
                // **Delete from Database**
                $this->db->where('product_id', $productId)->delete('sh_product');
                $this->db->where('product_id', $productId)->delete('sh_product_image');
                $this->db->where('product_id', $productId)->delete('sh_product_metafield');

                // Delete variant metadata
                $skuVariants = $this->db->select('variant_id')
                                        ->where('sim_sku', $dbSimSku)
                                        ->get('sh_product_variant')
                                        ->result_array();

                foreach ($skuVariants as $variant) {
                    $this->db->where('variant_id', $variant['variant_id'])->delete('sh_variant_metafield');
                }

                // **Log deleted product**
                $record = [
                    'product_id' => $productId,
                    'resposne_delete' => json_encode($skuVariants)
                ];
                $this->db->insert('shopify_missing_product_delete', $record);

                // **Delete the product variants**
                $this->db->where('product_id', $productId)->delete('sh_product_variant');

                echo "Deleted missing product: Product ID $productId (sim_sku: $dbSimSku)\n";
            }
        }
    }
}


}
