<?php

const CHUNK_SIZE = 2000;

// Script qui s'exécute avec une clé sécurisée
if (!isset($_GET['exportkey']) || $_GET['exportkey'] !== 'Pat5hGfA4tMTNqjuDM28DgRj') {
    exit(1);
}

/**
 * @param Db $db
 * @param string $query
 *
 * @return int
 */
function export_query_json($db, $query)
{
    \set_time_limit(0);

    $result = $db->query($query);

    $i = 0;
    while ($row = $db->nextRow($result)) {
        echo \json_encode($row) . "\n";

        ++$i;

        if ($i % CHUNK_SIZE === 0) {
            \set_time_limit(0);
        }
    }

    return $i;
}

// Bootstrap PrestaShop
try {
    require_once __DIR__ . '/config/config.inc.php';

    // Gestion multiboutique (au cas où) : Ce module est global
    if (Shop::isFeatureActive()) {
        Shop::setContext(Shop::CONTEXT_ALL);
    }
} catch (Exception $e) {
    echo $e->getMessage() . "\n" . $e->getTraceAsString();
    exit(1);
}

$codePrivileges = \array_map(
    'strval',
    \explode(',', $_GET['code_privilege'])
);
$whereCodePriv = '"' . \implode('","', $codePrivileges) . '"';

if (empty($codePrivileges)) {
    echo 'No ID provided';
    exit(1);
}

$db = Db::getInstance();

echo "### STORES\n";
$nbStores = \export_query_json(
    $db,
    'SELECT s.*, cli.*'
    . ' FROM ps2_store s'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
);

echo "### CUSTOMERS\n";
$nbCustomers = \export_query_json(
    $db,
    'SELECT c.*, cli.*'
    . ' FROM ps2_customer c'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps2_orders WHERE invoice_date >= "2023-01-01")'
);

echo "### ADDRESSES\n";
$nbAddresses = \export_query_json(
    $db,
    'SELECT c.email, a.*'
    . ' FROM ps2_address a'
    . ' INNER JOIN ps2_customer c ON c.id_customer = a.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND a.id_address IN (SELECT id_address_invoice FROM ps2_orders WHERE invoice_date >= "2023-01-01" UNION ALL SELECT id_address_delivery FROM ps2_orders WHERE invoice_date >= "2023-01-01")'
    . ' ORDER BY a.id_address ASC'
);

echo "### ORDERS\n";
$nbOrders = \export_query_json(
    $db,
    'SELECT c.email, o.*'
    . ' FROM ps2_orders o'
    . ' INNER JOIN ps2_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND o.invoice_date >= "2023-01-01"'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### ORDER_DETAILS\n";
$nbOrderDetails = \export_query_json(
    $db,
    'SELECT c.email, o.reference, od.*'
    . ' FROM ps2_order_detail od'
    . ' INNER JOIN ps2_orders o ON o.id_order = od.id_order'
    . ' INNER JOIN ps2_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND o.invoice_date >= "2023-01-01"'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### ORDER_DETAIL_TAXES\n";
$nbOrderDetailTaxes = \export_query_json(
    $db,
    'SELECT c.email, o.reference, ps.product_supplier_reference, odt.*'
    . ' FROM ps2_order_detail_tax odt'
    . ' INNER JOIN ps2_order_detail od ON od.id_order_detail = odt.id_order_detail'
    . ' LEFT JOIN ps2_product_supplier ps ON ps.id_product = od.product_id AND ps.id_product_attribute = od.product_attribute_id AND ps.id_supplier = 1'
    . ' INNER JOIN ps2_orders o ON o.id_order = od.id_order'
    . ' INNER JOIN ps2_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND o.invoice_date >= "2023-01-01"'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### CARTS\n";
$nbCarts = \export_query_json(
    $db,
    'SELECT o.reference, cart.*'
    . ' FROM ps2_cart cart'
    . ' INNER JOIN ps2_orders o ON o.id_cart = cart.id_cart'
    . ' INNER JOIN ps2_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND o.invoice_date >= "2023-01-01"'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### CART_PRODUCTS\n";
$nbCartProducts = \export_query_json(
    $db,
    'SELECT c.email, o.reference, ps.product_supplier_reference, cp.*'
    . ' FROM ps2_cart_product cp'
    . ' LEFT JOIN ps2_product_supplier ps ON ps.id_product = cp.id_product AND ps.id_product_attribute = cp.id_product_attribute AND ps.id_supplier = 1'
    . ' INNER JOIN ps2_orders o ON o.id_cart = cp.id_cart'
    . ' INNER JOIN ps2_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND o.invoice_date >= "2023-01-01"'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### CART_RULES\n";
$nbCartRules = \export_query_json(
    $db,
    'SELECT c.email, crl.name, cr.*'
    . ' FROM ps2_cart_rule cr'
    . ' INNER JOIN ps2_cart_rule_lang crl ON crl.id_cart_rule = cr.id_cart_rule AND crl.id_lang = 1'
    . ' INNER JOIN ps2_customer c ON c.id_customer = cr.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps2_orders WHERE invoice_date >= "2023-01-01")'
    . ' AND cr.quantity = 1 AND cr.date_to >= NOW()'
    . ' ORDER BY cr.date_add ASC'
);

echo "\n### REWARDS\n";
$nbRewards = \export_query_json(
    $db,
    'SELECT *,'
    . ' IF(points_2024<=450,1,IF(points_2024<=1000,2,IF(points_2024<=2000,3,5))) AS ratio_100_points,'
    . ' points_2024 * IF(points_2024<=450,1,IF(points_2024<=1000,2,IF(points_2024<=2000,3,5))) / 100 AS montant_reduction,'
    . ' points_2024 * IF(points_2024<=450,1,IF(points_2024<=1000,2,IF(points_2024<=2000,3,5))) * 10 AS points_myvetshop'
    . ' FROM ('
    . ' SELECT c.email, r.current_points AS current_points,'
    . ' (SELECT FLOOR(SUM(total_paid_real)) FROM ps2_orders o WHERE o.id_customer = r.id_customer AND o.invoice_date >= "2024-01-01") AS ca_2024,'
    . ' LEAST(current_points, (SELECT FLOOR(SUM(total_paid_real)) FROM ps2_orders o WHERE o.id_customer = r.id_customer AND o.invoice_date >= "2024-01-01")) AS points_2024'
    . ' FROM ps2_loyalty_customer r'
    . ' INNER JOIN ps2_customer c ON c.id_customer = r.id_customer'
    . ' INNER JOIN ps2_store s ON s.id_store = c.id_store'
    . ' INNER JOIN ps2_clinic cli ON cli.id_clinic = s.id_clinic'
    . ' WHERE s.active = 1'
    . ' AND cli.code_privilege IN (' . $whereCodePriv . ')'
    . ' AND r.date_upd >= "2023-01-01 00:00:00"'
    . ' AND c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps2_orders WHERE invoice_date >= "2023-01-01")'
    . ' HAVING ca_2024 IS NOT NULL'
    . ' ) t'
);
$totalRewards = '-';

echo "\n### STATS\n";
echo \json_encode([
    'nbStores' => $nbStores,
    'nbCustomers' => $nbCustomers,
    'nbAddresses' => $nbAddresses,
    'nbOrders' => $nbOrders,
    'nbOrderDetails' => $nbOrderDetails,
    'nbOrderDetailTaxes' => $nbOrderDetailTaxes,
    'nbCarts' => $nbCarts,
    'nbCartProducts' => $nbCartProducts,
    'nbCartRules' => $nbCartRules,
    'nbRewards' => $nbRewards,
    'totalRewards' => $totalRewards,
]);
