<?php

namespace App\Models\Magento;

use CodeIgniter\Model;

class MarqueModel extends Model
{
    protected $DBGroup = 'magento';

    public function getAnalyseMarques(int $annee = null): array
    {
        if ($annee === null) {
            $annee = date('Y');
        }

        return $this->db->query("
            SELECT 
                eaov.value as marque,
                COUNT(DISTINCT soi.order_id) as nb_commandes,
                SUM(soi.qty_ordered) as qty_total,
                SUM(soi.base_row_total) as ca_total
            FROM sales_order_item soi
            JOIN catalog_product_entity_int cpei 
                ON cpei.entity_id = soi.product_id 
                AND cpei.attribute_id = (
                    SELECT attribute_id 
                    FROM eav_attribute 
                    WHERE attribute_code = 'nc_marque' 
                    LIMIT 1
                )
            JOIN eav_attribute_option_value eaov 
                ON eaov.option_id = cpei.value 
                AND eaov.store_id = 0
            JOIN sales_order so 
                ON so.entity_id = soi.order_id
            WHERE YEAR(so.created_at) = {$annee}
            GROUP BY eaov.value
            ORDER BY ca_total DESC
        ")->getResultArray();
    }

    public function getTopProduitsParMarque(string $marque, int $annee = null, int $limit = 10): array
    {
        if ($annee === null) {
            $annee = date('Y');
        }

        return $this->db->query("
            SELECT 
                soi.name as product_name,
                soi.sku,
                SUM(soi.qty_ordered) as qty_total,
                SUM(soi.base_row_total) as ca_total
            FROM sales_order_item soi
            JOIN catalog_product_entity_int cpei 
                ON cpei.entity_id = soi.product_id 
                AND cpei.attribute_id = (
                    SELECT attribute_id 
                    FROM eav_attribute 
                    WHERE attribute_code = 'nc_marque' 
                    LIMIT 1
                )
            JOIN eav_attribute_option_value eaov 
                ON eaov.option_id = cpei.value 
                AND eaov.store_id = 0
            JOIN sales_order so 
                ON so.entity_id = soi.order_id
            WHERE eaov.value = ?
                AND YEAR(so.created_at) = {$annee}
            GROUP BY soi.product_id, soi.name, soi.sku
            ORDER BY ca_total DESC
            LIMIT {$limit}
        ", [$marque])->getResultArray();
    }

    public function getEvolutionMarque(string $marque, int $annee = null): array
    {
        if ($annee === null) {
            $annee = date('Y');
        }

        return $this->db->query("
            SELECT 
                DATE_FORMAT(so.created_at, '%Y-%m') as mois,
                SUM(soi.qty_ordered) as qty_total,
                SUM(soi.base_row_total) as ca_total
            FROM sales_order_item soi
            JOIN catalog_product_entity_int cpei 
                ON cpei.entity_id = soi.product_id 
                AND cpei.attribute_id = (
                    SELECT attribute_id 
                    FROM eav_attribute 
                    WHERE attribute_code = 'nc_marque' 
                    LIMIT 1
                )
            JOIN eav_attribute_option_value eaov 
                ON eaov.option_id = cpei.value 
                AND eaov.store_id = 0
            JOIN sales_order so 
                ON so.entity_id = soi.order_id
            WHERE eaov.value = ?
                AND YEAR(so.created_at) = {$annee}
            GROUP BY DATE_FORMAT(so.created_at, '%Y-%m')
            ORDER BY mois ASC
        ", [$marque])->getResultArray();
    }
}
