<?php
/**
 * Copyright since 2022 Inferendo Srl
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Academic Free License 3.0 (AFL-3.0)
 * that is bundled with this package in the file LICENSE.md.
 * It is also available through the world-wide-web at this URL:
 * https://opensource.org/licenses/AFL-3.0
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to hello@visidea.ai so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Visidea to newer
 * versions in the future. If you wish to customize Visidea for your
 * needs please refer to https://visidea.ai/ for more information.
 *
 * @author    Inferendo SRL <hello@visidea.ai>
 * @copyright Since 2022 Inferendo Srl
 * @license   https://opensource.org/licenses/AFL-3.0 Academic Free License 3.0 (AFL-3.0)
 */

class visideaCronModuleFrontController extends ModuleFrontController
{
    public function initContent()
    {
        parent::initContent();

        ini_set('memory_limit', '512M'); // Adjust as needed
        ini_set('max_execution_time', 0); // Unlimited execution time
        set_time_limit(0); // Removes the time limit for script execution
    
        $cronKey = Configuration::get('VISIDEA_PRIVATE_KEY');

        $urlVar = $_GET;
        if (!isset($urlVar['token']) || !$urlVar['token'] || $cronKey != $urlVar['token'])
        {
            echo 'Token error!';
            die();
        }

        $path = __DIR__ . "/../../csv/";
        if (!is_dir($path)) {
            mkdir($path, 0777, true);
        }

        // Determine which export to run based on URL parameter
        $exportType = Tools::getValue('export_type');

        switch ($exportType) {
            case 'items':
                $this->exportItems($path, $urlVar['token']);
                break;
            case 'users': 
                $this->exportUsers($path, $urlVar['token']);
                break;
            case 'interactions':
                $this->exportInteractions($path, $urlVar['token']);
                break;
            default:
                $this->exportItems($path, $urlVar['token']);
                $this->exportUsers($path, $urlVar['token']);
                $this->exportInteractions($path, $urlVar['token']);
                break;
        }

    }

    public function sanitizeString($string) {
        $sanitized = preg_replace('#<[^>]+>#', ' ', $string);
        $sanitized = str_replace('"', '\"', $sanitized);
        return $sanitized;
    }


    private function getAdditionalLanguages($languages, $default_language_id) {
        $additionalLanguages = [];
        foreach ($languages as $language) {
          if ($language['id_lang'] != $default_language_id)
              $additionalLanguages[] = $language;
        }
        return $additionalLanguages;
    }
 
    private function exportItems($path, $token)
    {
        $default_language_id = (int)Configuration::get('PS_LANG_DEFAULT');
        $languages = $this->getAdditionalLanguages(Language::getLanguages(true, $this->context->shop->id), $default_language_id);

        $filename = 'items_' . $token . '.csv';
        $temp_filename = 'items_' . $token . '.tmp';
        $hash_filename = 'items_' . $token . '.hash';
        $state_file = $path . "items_export_state.json";

        // Load state
        $state = file_exists($state_file) ? json_decode(file_get_contents($state_file), true) : [];
        $lastProcessedId = isset($state['items']) ? (int)$state['items'] : 0;

        if (file_exists($path . $temp_filename)) {
            unlink($path . $temp_filename);
        }

        $output = fopen($path . $temp_filename, "a");

        // Only write headers if starting from scratch
        if ($lastProcessedId == 0) {
            $header = "item_id;code;ean;name;brand_id;brand_name;stock;description;page_ids;page_names;price;market_price;discount;url;images;has_variants;";
            foreach ($languages as $language) {
                $header .= 'name_'.$language['language_code'].';';
                $header .= 'description_'.$language['language_code'].';';
                $header .= 'page_names_'.$language['language_code'].';';
                $header .= 'url_'.$language['language_code'].';';
            }
            $header .= "\n";
            fputs($output, $header);
        }

        // Process products in chunks
        $batchSize = 500;
        $hasMoreData = true;

        while ($hasMoreData) {
            $sql = "
                SELECT
                    p.id_product AS item_id,
                    p.reference AS code,
                    CONCAT_WS('|', NULLIF(p.ean13, ''), NULLIF(p.isbn, ''), NULLIF(p.upc, '')) AS ean,
                    pl.name AS name,
                    p.id_manufacturer AS brand_id,
                    m.name AS brand_name,
                    ps.quantity AS stock,
                    pl.description,
                    GROUP_CONCAT(cp.id_category SEPARATOR '|') AS page_ids,
                    GROUP_CONCAT(cl.name SEPARATOR '|') AS page_names
                FROM
                    " . _DB_PREFIX_ . "product p
                LEFT JOIN
                    " . _DB_PREFIX_ . "product_lang pl ON pl.id_product = p.id_product AND pl.id_lang = $default_language_id
                LEFT JOIN
                    " . _DB_PREFIX_ . "manufacturer m ON m.id_manufacturer = p.id_manufacturer
                LEFT JOIN
                    " . _DB_PREFIX_ . "stock_available ps ON ps.id_product = p.id_product AND ps.id_product_attribute = 0
                LEFT JOIN
                    " . _DB_PREFIX_ . "category_product cp ON cp.id_product = p.id_product
                LEFT JOIN
                    " . _DB_PREFIX_ . "category_lang cl ON cl.id_category = cp.id_category AND cl.id_lang = $default_language_id
                WHERE
                    p.id_product > $lastProcessedId
                    AND p.active = 1
                    AND p.visibility != 'none'
                GROUP BY
                    p.id_product
                ORDER BY
                    p.id_product ASC
                LIMIT $batchSize
            ";

            $products = Db::getInstance()->executeS($sql);

            if (!$products || count($products) === 0) {
                $hasMoreData = false;
                break;
            }

            foreach ($products as $product) {
                $prod = new Product($product['item_id']);
                $product['price'] = round($prod->getPriceWithoutReduct(),2);
                $product['market_price'] = round($prod->getPrice(),2);
                if ($product['price'] > 0)
                    $product['discount'] = abs(($product['price'] - $product['market_price']) / $product['price'] * 100);
                else
                    $product['discount'] = 0;
    
                $link = new Link();
                $product['url'] = $link->getProductLink($prod,null,null,null,$default_language_id);
        
                // Fetch images for the product
                $productImages = $prod->getImages($default_language_id);
                $imagePathArr = array();
                foreach ($productImages AS $key => $val)
                {
                    $id_image = $val['id_image'];
                    $imagePath = "http://".$link->getImageLink($prod->link_rewrite[Context::getContext()
                        ->language->id], $id_image, 'home_default');
                    $imagePathArr[] = $imagePath;
                }
                $product['images'] = implode('|', $imagePathArr);
    
                // Check if product has variants (combinations)
                $combinations = $prod->getAttributeCombinations($default_language_id);
                $product['has_variants'] = (!empty($combinations)) ? 'true' : 'false';

                foreach ($languages as $language) {
                    $lang_id = $language['id_lang'];
                    $lang_data = Db::getInstance()->getRow("
                        SELECT
                            pl.name AS name,
                            pl.description AS description,
                            GROUP_CONCAT(cl.name SEPARATOR '|') AS page_names
                        FROM " . _DB_PREFIX_ . "product_lang pl
                        LEFT JOIN " . _DB_PREFIX_ . "category_product cp ON cp.id_product = pl.id_product
                        LEFT JOIN " . _DB_PREFIX_ . "category_lang cl ON cl.id_category = cp.id_category AND cl.id_lang = $lang_id
                        WHERE pl.id_product = " . (int)$product['item_id'] . "
                        GROUP BY pl.id_product
                    ");

                    $product['name_'.$language['language_code']] = $this->sanitizeString($lang_data['name'] ?? '');
                    $product['description_'.$language['language_code']] = $this->sanitizeString($lang_data['description'] ?? '');
                    $product['page_names_'.$language['language_code']] = $this->sanitizeString($lang_data['page_names'] ?? '');
                    $product['url_'.$language['language_code']] = (new Link())->getProductLink(
                        $product['item_id'], null, null, null, $lang_id
                    );
                }

                fputcsv($output, $product, ";", "\"", "\\");

                // Update last processed ID
                $lastProcessedId = $product['item_id'];
    
                // Save the last processed ID
                $state['items'] = $lastProcessedId;
                file_put_contents($state_file, json_encode($state));
            }
        }

        fclose($output);

        // Finalize file
        if (file_exists($path . $filename)) {
            unlink($path . $filename);
        }
        rename($path . $temp_filename, $path . $filename);

        // Generate hash
        file_put_contents($path . $hash_filename, hash_file('sha256', $path . $filename));

        if (file_exists($state_file)) {
            unlink($state_file);
        }
    
        echo 'File successfully exported!';
        die();
    }
    
    private function exportUsers($path, $token)
    {
        $filename = 'users_' . $token . '.csv';
        $temp_filename = 'users_' . $token . '.tmp';
        $hash_filename = 'users_' . $token . '.hash';
        $state_file = $path . "users_export_state.json";

        // Load state
        $state = file_exists($state_file) ? json_decode(file_get_contents($state_file), true) : [];
        $lastProcessedId = isset($state['users']) ? (int)$state['users'] : 0;

        if (file_exists($path . $temp_filename)) {
            unlink($path . $temp_filename);
        }

        // Open file for writing
        $output = fopen($path . $temp_filename, "a");

        // Only write headers if starting from scratch
        if ($lastProcessedId == 0) {
            fputs($output, "user_id;email;name;surname;address;city;zip;state;country;birthday;newsletter;createdDate\n");
        }

        // Fetch customers in chunks
        $batchSize = 1000;
        $hasMoreData = true;

        while ($hasMoreData) {
            $sql = "
                SELECT
                    c.id_customer AS user_id,
                    c.email,
                    c.firstname AS name,
                    c.lastname AS surname,
                    a.address1 AS address,
                    a.city,
                    a.postcode AS zip,
                    s.name AS state,
                    LOWER(ct.iso_code) AS country,
                    c.birthday,
                    IF(c.newsletter = 1, 'true', 'false') AS newsletter,
                    c.date_add AS createdDate
                FROM
                    " . _DB_PREFIX_ . "customer c
                LEFT JOIN
                    " . _DB_PREFIX_ . "address a ON a.id_address = (
                        SELECT id_address FROM " . _DB_PREFIX_ . "address
                        WHERE id_customer = c.id_customer LIMIT 1
                    )
                LEFT JOIN
                    " . _DB_PREFIX_ . "state s ON s.id_state = a.id_state
                LEFT JOIN
                    " . _DB_PREFIX_ . "country ct ON ct.id_country = a.id_country
                WHERE c.id_customer > $lastProcessedId
                ORDER BY c.id_customer ASC
                LIMIT $batchSize
            ";

            $customers = Db::getInstance()->executeS($sql);
            if (!$customers || count($customers) === 0) {
                $hasMoreData = false;
                break;
            }

            foreach ($customers as $customer) {

                // Format the date
                $customer['createdDate'] = (new DateTime($customer['createdDate']))->format(DateTime::ISO8601);

                // Write each customer to CSV
                fputcsv($output, $customer, ";", "\"", "\\");

                // Update last processed ID
                $lastProcessedId = $customer['user_id'];

                // Save the last processed ID
                $state['users'] = $lastProcessedId;
                file_put_contents($state_file, json_encode($state));
            }
        }

        fclose($output);

        // Finalize the file
        if (file_exists($path . $filename)) {
            unlink($path . $filename);
        }
        rename($path . $temp_filename, $path . $filename);

        // Generate hash file
        file_put_contents($path . $hash_filename, hash_file('sha256', $path . $filename));

        if (file_exists($state_file)) {
            unlink($state_file);
        }
    
        echo 'File successfully exported!';
        die();
    }

    private function exportInteractions($path, $token)
    {
        $filename = 'interactions_' . $token . '.csv';
        $temp_filename = 'interactions_' . $token . '.tmp';
        $hash_filename = 'interactions_' . $token . '.hash';
        $state_file = $path . "interactions_export_state.json";

        // Load state
        $state = file_exists($state_file) ? json_decode(file_get_contents($state_file), true) : [];
        $lastProcessedId = isset($state['interactions']) ? (int)$state['interactions'] : 0;

        if (file_exists($path . $temp_filename)) {
            unlink($path . $temp_filename);
        }

        $output = fopen($path . $temp_filename, "a");
        // Only write headers if starting from scratch
        if ($lastProcessedId == 0) {
            fputs($output, "user_id;item_id;action;price;quantity;timestamp\n");
        }

        $batchSize = 1000; // Process 1000 rows at a time
        $hasMoreData = true;

        while ($hasMoreData) {
            // Optimized SQL query to fetch data in chunks
            $sql = 'SELECT cp.*, 
                           IF(o.date_add IS NOT NULL, o.date_add, c.date_add) AS interaction_date, 
                           c.id_customer, 
                           c.id_guest, 
                           p.price, 
                           o.id_order
                    FROM `' . _DB_PREFIX_ . 'cart_product` cp
                    INNER JOIN `' . _DB_PREFIX_ . 'cart` c ON cp.id_cart = c.id_cart
                    INNER JOIN `' . _DB_PREFIX_ . 'product` p ON cp.id_product = p.id_product
                    LEFT JOIN `' . _DB_PREFIX_ . 'orders` o ON c.id_cart = o.id_cart
                    WHERE cp.id_cart > ' . $lastProcessedId . '
                    ORDER BY cp.id_cart ASC
                    LIMIT ' . $batchSize;
            $result = Db::getInstance()->executeS($sql);
            if (!$result || count($result) === 0) {
                $hasMoreData = false;
                break;
            }

            foreach ($result as $product) {
                $cartProduct = array();
                $cartProduct['user_id'] = $product['id_customer'] ?: $product['id_guest'];
                $cartProduct['item_id'] = $product['id_product'];
                $cartProduct['action'] = $product['id_order'] ? 'purchase' : 'cart';
                $cartProduct['price'] = $product['price'];
                $cartProduct['quantity'] = $product['quantity'];
                $timestamp = new DateTime($product['date_upd']);
                $cartProduct['timestamp'] = (new DateTime($product['interaction_date']))->format(DateTime::ISO8601);
    
                if ($cartProduct['user_id'] > 0 && $cartProduct['item_id'] > 0) {
                    fputcsv($output, [
                        $cartProduct['user_id'],
                        $cartProduct['item_id'],
                        $cartProduct['action'],
                        $cartProduct['price'],
                        $cartProduct['quantity'],
                        $cartProduct['timestamp']
                    ], ";", "\"", "\\");
                }

                // Update last processed ID
                $lastProcessedId = $product['id_cart'];

                // Save the last processed ID
                $state['interactions'] = $lastProcessedId;
                file_put_contents($state_file, json_encode($state));   
            }
        }

        fclose($output);
    
        if (file_exists($path . $filename)) {
            unlink($path . $filename);
        }
        rename($path . $temp_filename, $path . $filename);
    
        file_put_contents($path . $hash_filename, hash_file('sha256', $path . $filename));

        if (file_exists($state_file)) {
            unlink($state_file);
        }
    
        echo 'File successfully exported!';
        die();
    }

}
