<?php

namespace App\Models\Magento;

use CodeIgniter\Model;

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

    /**
     * Retourne les infos d'un attribut EAV produit (catalog_product).
     */
    private function getProductAttributeInfo(string $attributeCode): ?array
    {
        return $this->db->query("
            SELECT attribute_id, backend_type
            FROM eav_attribute
            WHERE attribute_code = ?
              AND entity_type_id = (
                  SELECT entity_type_id
                  FROM eav_entity_type
                  WHERE entity_type_code = 'catalog_product'
                  LIMIT 1
              )
            LIMIT 1
        ", [$attributeCode])->getRowArray();
    }

    /**
     * Retourne l'utilisation des produits cadeaux issues de lca_promo_limiter_usage,
     * agrégées par produit Magento (configurable parent si le SKU est un enfant).
     */
    public function getPromoProduits(?string $dateDebut = null, ?string $dateFin = null): array
    {
        $dateDebutSql = (!empty($dateDebut)) ? $dateDebut . ' 00:00:00' : null;
        $dateFinSql   = (!empty($dateFin))   ? $dateFin . ' 23:59:59'   : null;

        $nameInfo  = $this->getProductAttributeInfo('name');
        $brandInfo = $this->getProductAttributeInfo('nc_marque');

        $nameAttributeId  = (int) ($nameInfo['attribute_id'] ?? 0);
        $brandAttributeId = (int) ($brandInfo['attribute_id'] ?? 0);

        $params = [];
        $dateWhere = '';

        if ($dateDebutSql !== null && $dateFinSql !== null) {
            $dateWhere = ' WHERE plu.created_at >= ? AND plu.created_at <= ? ';
            $params[] = $dateDebutSql;
            $params[] = $dateFinSql;
        }

        $sql = "
            WITH usage_with_product AS (
                SELECT
                    plu.entity_id AS usage_id,
                    plu.customer_id,
                    plu.customer_email,
                    plu.rule_id,
                    plu.sku,
                    plu.order_id,
                    plu.qty,
                    plu.created_at,
                    plu.type,
                    cpe.entity_id AS raw_product_id,
                    cpe.type_id AS raw_type_id,
                    COALESCE(cpsl.parent_id, cpe.entity_id) AS product_id
                FROM lca_promo_limiter_usage plu
                INNER JOIN catalog_product_entity cpe ON cpe.sku = plu.sku
                LEFT JOIN catalog_product_super_link cpsl ON cpsl.product_id = cpe.entity_id
                {$dateWhere}
            )
            SELECT
                final_product.entity_id,
                final_product.sku,
                final_product.type_id,
                CASE
                    WHEN final_product.type_id = 'configurable' THEN 'Configurable'
                    ELSE 'Simple'
                END AS type_label,
                COALESCE(product_name.value, final_product.sku) AS product_name,
                COALESCE(brand_value.value, '') AS brand,
                COUNT(DISTINCT uwp.order_id) AS orders_count,
                COALESCE(SUM(uwp.qty), 0) AS total_qty,
                COUNT(DISTINCT uwp.rule_id) AS rules_count,
                COUNT(DISTINCT uwp.usage_id) AS usages_count,
                MIN(uwp.created_at) AS first_used_at,
                MAX(uwp.created_at) AS last_used_at,
                GROUP_CONCAT(DISTINCT uwp.rule_id ORDER BY sr.name SEPARATOR ',') AS rule_ids,
                GROUP_CONCAT(DISTINCT sr.name ORDER BY sr.name SEPARATOR '||') AS rule_names,
                GROUP_CONCAT(DISTINCT CASE WHEN sr.is_active = 1 THEN 'Actif' ELSE 'Inactif' END ORDER BY sr.name SEPARATOR '||') AS rule_statuses,
                GROUP_CONCAT(DISTINCT sr.from_date ORDER BY sr.name SEPARATOR '||') AS rule_from_dates,
                GROUP_CONCAT(DISTINCT sr.to_date ORDER BY sr.name SEPARATOR '||') AS rule_to_dates
            FROM usage_with_product uwp
            INNER JOIN catalog_product_entity final_product
                ON final_product.entity_id = uwp.product_id
            INNER JOIN salesrule sr ON sr.rule_id = uwp.rule_id
            INNER JOIN amasty_ampromo_rule ap ON ap.salesrule_id = sr.rule_id AND ap.sku IS NOT NULL AND TRIM(ap.sku) != ''
            LEFT JOIN (
                SELECT
                    entity_id,
                    COALESCE(
                        MAX(CASE WHEN store_id = 1 THEN value END),
                        MAX(CASE WHEN store_id = 0 THEN value END)
                    ) AS value
                FROM catalog_product_entity_varchar
                WHERE attribute_id = ?
                  AND store_id IN (0, 1)
                GROUP BY entity_id
            ) product_name
                ON product_name.entity_id = final_product.entity_id
            LEFT JOIN catalog_product_entity_int brand
                ON brand.entity_id = final_product.entity_id
                AND brand.attribute_id = ?
                AND brand.store_id = 0
            LEFT JOIN eav_attribute_option_value brand_value
                ON brand_value.option_id = brand.value
                AND brand_value.store_id = 0
            GROUP BY
                final_product.entity_id,
                final_product.sku,
                final_product.type_id,
                product_name.value,
                brand_value.value
            ORDER BY total_qty DESC, product_name ASC
        ";

        $queryParams = array_merge($params, [$nameAttributeId, $brandAttributeId]);

        $rows = $this->db->query($sql, $queryParams)->getResultArray();

        return array_map(function ($row) {
            return [
                'entity_id'      => (int) $row['entity_id'],
                'sku'            => $row['sku'],
                'type_id'        => $row['type_id'],
                'type_label'     => $row['type_label'],
                'product_name'   => $row['product_name'],
                'brand'          => $row['brand'],
                'orders_count'   => (int) $row['orders_count'],
                'total_qty'      => (float) $row['total_qty'],
                'rules_count'    => (int) $row['rules_count'],
                'usages_count'   => (int) $row['usages_count'],
                'first_used_at'  => $row['first_used_at'],
                'last_used_at'   => $row['last_used_at'],
                'rules'          => $this->parseRules(
                    $row['rule_ids'] ?? null,
                    $row['rule_names'] ?? null,
                    $row['rule_statuses'] ?? null,
                    $row['rule_from_dates'] ?? null,
                    $row['rule_to_dates'] ?? null
                ),
            ];
        }, $rows);
    }

    /**
     * Parse les GROUP_CONCAT des règles salesrule en tableau structuré.
     */
    private function parseRules(?string $ruleIds, ?string $ruleNames, ?string $ruleStatuses, ?string $ruleFromDates, ?string $ruleToDates): array
    {
        if (empty($ruleIds)) {
            return [];
        }

        $ids       = explode(',', $ruleIds);
        $names     = !empty($ruleNames)     ? explode('||', $ruleNames)     : [];
        $statuses  = !empty($ruleStatuses) ? explode('||', $ruleStatuses)  : [];
        $fromDates = !empty($ruleFromDates) ? explode('||', $ruleFromDates) : [];
        $toDates   = !empty($ruleToDates)   ? explode('||', $ruleToDates)   : [];

        $rules = [];
        $today = date('Y-m-d');

        foreach ($ids as $index => $id) {
            $fromDate = $fromDates[$index] ?? null;
            $toDate   = $toDates[$index] ?? null;
            $status   = $statuses[$index] ?? '';

            $isExpired = (!empty($toDate) && $toDate !== '0000-00-00' && $toDate < $today);
            $isActive  = ($status === 'Actif' && !$isExpired);

            $rules[] = [
                'rule_id'   => (int) $id,
                'name'      => $names[$index] ?? '',
                'status'    => $isActive ? 'Actif' : 'Inactif',
                'from_date' => (!empty($fromDate) && $fromDate !== '0000-00-00') ? $fromDate : null,
                'to_date'   => (!empty($toDate) && $toDate !== '0000-00-00') ? $toDate : null,
            ];
        }

        return $rules;
    }

    /**
     * Retourne les clients ayant reçu le produit cadeau (parent ou ses enfants configurables),
     * avec leur nombre de commandes et la quantité totale.
     */
    public function getClientsByProductId(int $productId, ?string $dateDebut = null, ?string $dateFin = null): array
    {
        $dateDebutSql = (!empty($dateDebut)) ? $dateDebut . ' 00:00:00' : null;
        $dateFinSql   = (!empty($dateFin))   ? $dateFin . ' 23:59:59'   : null;

        $params = [$productId, $productId];
        $dateWhere = '';

        if ($dateDebutSql !== null && $dateFinSql !== null) {
            $dateWhere = ' AND plu.created_at >= ? AND plu.created_at <= ? ';
            $params[] = $dateDebutSql;
            $params[] = $dateFinSql;
        }

        $rows = $this->db->query("
            SELECT
                plu.customer_id,
                plu.customer_email,
                COALESCE(MAX(so.customer_firstname), '') AS firstname,
                COALESCE(MAX(so.customer_lastname), '') AS lastname,
                COUNT(DISTINCT plu.order_id) AS nb_commandes,
                COALESCE(SUM(plu.qty), 0) AS total_qty,
                MIN(plu.created_at) AS first_used_at,
                MAX(plu.created_at) AS last_used_at
            FROM lca_promo_limiter_usage plu
            INNER JOIN catalog_product_entity cpe ON cpe.sku = plu.sku
            INNER JOIN salesrule sr ON sr.rule_id = plu.rule_id
            INNER JOIN amasty_ampromo_rule ap ON ap.salesrule_id = sr.rule_id AND ap.sku IS NOT NULL AND TRIM(ap.sku) != ''
            LEFT JOIN sales_order so ON so.increment_id = plu.order_id
            WHERE (
                cpe.entity_id = ?
                OR cpe.entity_id IN (
                    SELECT product_id
                    FROM catalog_product_super_link
                    WHERE parent_id = ?
                )
            )
            {$dateWhere}
            GROUP BY plu.customer_id, plu.customer_email
            ORDER BY total_qty DESC, plu.customer_email ASC
        ", $params)->getResultArray();

        return array_map(static function ($row) {
            return [
                'customer_id'   => (int) ($row['customer_id'] ?? 0),
                'customer_email' => $row['customer_email'] ?? '',
                'firstname'     => $row['firstname'] ?? '',
                'lastname'      => $row['lastname'] ?? '',
                'customer_name'   => trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? '')),
                'nb_commandes'  => (int) $row['nb_commandes'],
                'total_qty'     => (float) $row['total_qty'],
                'first_used_at' => $row['first_used_at'],
                'last_used_at'  => $row['last_used_at'],
            ];
        }, $rows);
    }

    /**
     * Règles panier (salesrule) qui n'ont aucun produit cadeau dans lca_promo_limiter_usage
     * sur la période demandée (ou jamais si pas de dates).
     */
    public function getUnusedRules(?string $dateDebut = null, ?string $dateFin = null): array
    {
        $dateDebutSql = (!empty($dateDebut)) ? $dateDebut . ' 00:00:00' : null;
        $dateFinSql   = (!empty($dateFin))   ? $dateFin . ' 23:59:59'   : null;

        $params = [];
        $dateWhere = '';

        if ($dateDebutSql !== null && $dateFinSql !== null) {
            $dateWhere = ' AND plu.created_at >= ? AND plu.created_at <= ? ';
            $params[] = $dateDebutSql;
            $params[] = $dateFinSql;
        }

        $rows = $this->db->query("
            SELECT
                sr.rule_id,
                sr.name,
                sr.is_active,
                sr.from_date,
                sr.to_date
            FROM salesrule sr
            INNER JOIN amasty_ampromo_rule ap ON ap.salesrule_id = sr.rule_id AND ap.sku IS NOT NULL AND TRIM(ap.sku) != ''
            WHERE NOT EXISTS (
                SELECT 1
                FROM lca_promo_limiter_usage plu
                WHERE plu.rule_id = sr.rule_id
                {$dateWhere}
            )
            ORDER BY sr.rule_id DESC
        ", $params)->getResultArray();

        $today = date('Y-m-d');

        return array_map(static function ($row) use ($today) {
            $isActive  = (int) ($row['is_active'] ?? 0) === 1;
            $toDate    = (!empty($row['to_date']) && $row['to_date'] !== '0000-00-00') ? $row['to_date'] : null;
            $isExpired = (!empty($toDate) && $toDate < $today);

            return [
                'rule_id'   => (int) $row['rule_id'],
                'name'      => $row['name'] ?? '',
                'status'    => ($isActive && !$isExpired) ? 'Actif' : 'Inactif',
                'from_date' => (!empty($row['from_date']) && $row['from_date'] !== '0000-00-00') ? $row['from_date'] : null,
                'to_date'   => $toDate,
            ];
        }, $rows);
    }

    /**
     * KPIs globaux sur les produits cadeaux : nb produits distincts, nb commandes, qté totale.
     */
    public function getPromoProduitsSummary(?string $dateDebut = null, ?string $dateFin = null): array
    {
        $dateDebutSql = (!empty($dateDebut)) ? $dateDebut . ' 00:00:00' : null;
        $dateFinSql   = (!empty($dateFin))   ? $dateFin . ' 23:59:59'   : null;

        $params = [];
        $dateWhere = '';

        if ($dateDebutSql !== null && $dateFinSql !== null) {
            $dateWhere = ' WHERE plu.created_at >= ? AND plu.created_at <= ? ';
            $params[] = $dateDebutSql;
            $params[] = $dateFinSql;
        }

        $row = $this->db->query("
            SELECT
                COUNT(DISTINCT COALESCE(cpsl.parent_id, cpe.entity_id)) AS nb_produits,
                COUNT(DISTINCT plu.order_id) AS nb_commandes,
                COALESCE(SUM(plu.qty), 0) AS total_qty
            FROM lca_promo_limiter_usage plu
            INNER JOIN catalog_product_entity cpe ON cpe.sku = plu.sku
            LEFT JOIN catalog_product_super_link cpsl ON cpsl.product_id = cpe.entity_id
            INNER JOIN salesrule sr ON sr.rule_id = plu.rule_id
            INNER JOIN amasty_ampromo_rule ap ON ap.salesrule_id = sr.rule_id AND ap.sku IS NOT NULL AND TRIM(ap.sku) != ''
            {$dateWhere}
        ", $params)->getRowArray();

        return [
            'nb_produits'   => (int) ($row['nb_produits'] ?? 0),
            'nb_commandes'  => (int) ($row['nb_commandes'] ?? 0),
            'total_qty'     => (float) ($row['total_qty'] ?? 0),
        ];
    }
}
