<?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);
}

$idCliniques = [
    501673, 501674, 501675, 501691, 501695, 501697, 501707, 501723, 501729, 501730, 501731, 501732, 501737, 501742,
    501745, 501746, 501747, 501754, 501758, 501761, 501769, 501771, 501773, 501780, 501795, 501796, 501806, 501822,
    501847, 501856, 501874, 501876, 501900, 501923, 501944, 501950, 501951, 501959, 501960, 501979, 501988, 501993,
    501994, 502003, 502004, 502009, 502014, 502024, 502045, 502087, 502089, 502131, 502139, 502145, 502233, 502236,
    502240, 502246, 502255, 502260, 502266, 502286, 502293, 502294, 502309, 502319, 502331, 502333, 502334, 502342,
    502345, 502347, 502366, 502375, 502384, 502403, 502410, 502445, 502467, 502482, 502540, 502556, 502582, 502600,
    502629, 502714, 502756, 502789, 502810, 502855, 502873, 502889, 502902, 502917, 502941, 502943, 502967, 502968,
    503009, 503056, 503105, 503211, 503218, 503317, 503356, 503472, 503512,
];

$db = Db::getInstance();

echo "### STORES\n";
$nbStores = export_query_json(
    $db,
    'SELECT s.*, cli.*'
    . ' FROM ps_store s'
    . ' INNER JOIN ps_chezmonveto_interface_store cis ON cis.id_store = s.id_store'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = cis.id_veto_cmv'
    . ' WHERE s.active = 1'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
);

echo "### CUSTOMERS\n";
$nbCustomers = export_query_json(
    $db,
    'SELECT c.*, cli.*'
    . ' FROM ps_customer c'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps_orders WHERE invoice_date >= "2023-01-01")'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
);

echo "### ADDRESSES\n";
$nbAddresses = export_query_json(
    $db,
    'SELECT c.email, a.*'
    . ' FROM ps_address a'
    . ' INNER JOIN ps_customer c ON c.id_customer = a.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE c.deleted = 0'
    . ' AND a.id_address IN (SELECT id_address_invoice FROM ps_orders WHERE invoice_date >= "2023-01-01" UNION ALL SELECT id_address_delivery FROM ps_orders WHERE invoice_date >= "2023-01-01")'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' ORDER BY a.id_address ASC'
);

echo "### ORDERS\n";
$nbOrders = export_query_json(
    $db,
    'SELECT c.email, o.*'
    . ' FROM ps_orders o'
    . ' INNER JOIN ps_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE o.invoice_date >= "2023-01-01"'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### ORDER_DETAILS\n";
$nbOrderDetails = export_query_json(
    $db,
    'SELECT c.email, o.reference, od.*'
    . ' FROM ps_order_detail od'
    . ' INNER JOIN ps_orders o ON o.id_order = od.id_order'
    . ' INNER JOIN ps_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE o.invoice_date >= "2023-01-01"'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' 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 ps_order_detail_tax odt'
    . ' INNER JOIN ps_order_detail od ON od.id_order_detail = odt.id_order_detail'
    . ' LEFT JOIN ps_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 ps_orders o ON o.id_order = od.id_order'
    . ' INNER JOIN ps_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE o.invoice_date >= "2023-01-01"'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### CARTS\n";
$nbCarts = export_query_json(
    $db,
    'SELECT o.reference, c.*'
    . ' FROM ps_cart c'
    . ' INNER JOIN ps_orders o ON o.id_cart = c.id_cart'
    . ' INNER JOIN ps_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE o.invoice_date >= "2023-01-01"'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' 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 ps_cart_product cp'
    . ' LEFT JOIN ps_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 ps_orders o ON o.id_cart = cp.id_cart'
    . ' INNER JOIN ps_customer c ON c.id_customer = o.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE o.invoice_date >= "2023-01-01"'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' ORDER BY o.date_add ASC'
);

echo "\n### CART_RULES\n";
$nbCartRules = export_query_json(
    $db,
    'SELECT c.email, crl.name, cr.*'
    . ' FROM ps_cart_rule cr'
    . ' INNER JOIN ps_cart_rule_lang crl ON crl.id_cart_rule = cr.id_cart_rule AND crl.id_lang = 1'
    . ' INNER JOIN ps_customer c ON c.id_customer = cr.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE cr.quantity = 1 AND cr.date_to >= NOW()'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' ORDER BY cr.date_add ASC'
);

echo "\n### REWARDS\n";
$nbRewards = export_query_json(
    $db,
    'SELECT c.email, SUM(r.credits) AS credits'
    . ' FROM ps_rewards r'
    . ' INNER JOIN ps_customer c ON c.id_customer = r.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE `id_reward_state` = 2'
    . ' AND r.date_add >= "2023-01-01 00:00:00"'
    . ' AND c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps_orders WHERE invoice_date >= "2023-01-01")'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')'
    . ' GROUP BY r.id_customer'
);
$totalRewards = $db->getValue(
    'SELECT SUM(r.credits) AS credits'
    . ' FROM ps_rewards r'
    . ' INNER JOIN ps_customer c ON c.id_customer = r.id_customer'
    . ' INNER JOIN ps_chezmonveto_has_customer chc ON chc.id_customer = c.id_customer'
    . ' INNER JOIN ps_chezmonveto_interface cli ON cli.id_veto_cmv = chc.id_veto_cmv'
    . ' WHERE `id_reward_state` = 2'
    . ' AND r.date_add >= "2023-01-01 00:00:00"'
    . ' AND c.deleted = 0'
    . ' AND c.id_customer IN (SELECT id_customer FROM ps_orders WHERE invoice_date >= "2023-01-01")'
    . ' AND cli.id_veto_alcyon IN (' . implode(',', $idCliniques) . ')',
    false
);

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,
]);
