<?php

namespace App\Commands;

use App\Models\PrestashopProductsModel;
use Config\Database;
use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;

class SyncPrestashopProducts extends BaseCommand
{
    protected $group       = 'Cron';
    protected $name        = 'cron:sync-prestashop-products';
    protected $description = 'Synchronise les produits depuis la base Prestashop vers la table locale.';

    public function run(array $params)
    {
        CLI::write('Démarrage de la synchronisation des produits Prestashop...', 'green');

        // Connexion à la base Prestashop
        $prestashopDb = Database::connect('prestashop');

        // Requête pour récupérer les données Prestashop
        $query = "
            SELECT 
                p.id_product AS id_product,
                pa.id_product_attribute AS id_product_attribute,
                p.reference AS product_reference,
                pa.reference AS attribute_reference,
                pl.name AS nom,
                p.wholesale_price AS pa_ht,
                COALESCE(pa.ean13, p.ean13) AS ean,
                (p.price + IFNULL(pa.price, 0)) AS pv_ht,
                pa.wholesale_price AS pa_declinaison_ht,
                pa.price AS impact_prix,
                (p.price + pa.price) AS pv_declinaison_ht,
                m.name AS marque,
                GROUP_CONCAT(DISTINCT al.name SEPARATOR ', ') AS declinaison,
                COALESCE(sa.quantity, 0) AS qte,
                p.active AS actif
            FROM ps_product p
            LEFT JOIN ps_product_attribute pa ON p.id_product = pa.id_product
            LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1
            LEFT JOIN ps_product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute
            LEFT JOIN ps_attribute a ON pac.id_attribute = a.id_attribute
            LEFT JOIN ps_attribute_lang al ON a.id_attribute = al.id_attribute AND al.id_lang = 1
            LEFT JOIN ps_attribute_group_lang agl ON a.id_attribute_group = agl.id_attribute_group AND agl.id_lang = 1
            LEFT JOIN ps_manufacturer m ON p.id_manufacturer = m.id_manufacturer
            LEFT JOIN ps_stock_available sa ON (
                (pa.id_product_attribute = sa.id_product_attribute AND sa.id_product = p.id_product) 
                OR (pa.id_product_attribute IS NULL AND sa.id_product = p.id_product AND sa.id_product_attribute = 0)
            )
            GROUP BY pa.id_product_attribute
            ORDER BY p.id_product DESC, pa.id_product_attribute DESC
        ";

        // Exécuter la requête
        $prestashopData = $prestashopDb->query($query)->getResultArray();

        if (empty($prestashopData)) {
            CLI::write('Aucune donnée récupérée de Prestashop.', 'red');
            return;
        }

        // Connexion à la base locale
        $model = new PrestashopProductsModel();

        // Vider la table avant l'insertion
        $model->truncate();

        // Insérer les nouvelles données
        $model->insertBatch($prestashopData);

        CLI::write('Synchronisation terminée avec succès.', 'green');
    }
}
