<?php

namespace App\Models\Magento;

use CodeIgniter\Model;

class PlvGoodiesModel 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 tous les produits d'une catégorie avec leur stock, statut et
     * nombre de commandes dans lesquelles ils apparaissent.
     */
    public function getProductsByCategory(int $categoryId): array
    {
        $nameInfo   = $this->getProductAttributeInfo('name');
        $statusInfo = $this->getProductAttributeInfo('status');

        $nameAttributeId   = (int) ($nameInfo['attribute_id'] ?? 0);
        $statusAttributeId = (int) ($statusInfo['attribute_id'] ?? 0);

        if ($nameAttributeId === 0 || $statusAttributeId === 0) {
            return [];
        }

        return $this->db->query("
            SELECT
                cpe.entity_id,
                cpe.sku,
                cpe.type_id,
                COALESCE(product_name.value, cpe.sku) AS product_name,
                CASE WHEN status.value = 1 THEN 'Actif' ELSE 'Inactif' END AS status_label,
                status.value = 1 AS is_active,
                COALESCE(si.qty, 0) AS qty,
                COALESCE(res.reserved_qty, 0) AS reserved_qty,
                GREATEST(COALESCE(si.qty, 0) + COALESCE(res.reserved_qty, 0), 0) AS available_qty,
                COALESCE(ord.orders_count, 0) AS orders_count
            FROM catalog_category_product ccp
            INNER JOIN catalog_product_entity cpe ON cpe.entity_id = ccp.product_id
            LEFT JOIN catalog_product_entity_varchar product_name
                ON product_name.entity_id = cpe.entity_id
                AND product_name.attribute_id = ?
                AND product_name.store_id = 0
            LEFT JOIN catalog_product_entity_int status
                ON status.entity_id = cpe.entity_id
                AND status.attribute_id = ?
                AND status.store_id = 0
            LEFT JOIN cataloginventory_stock_item si
                ON si.product_id = cpe.entity_id
            LEFT JOIN (
                SELECT sku, SUM(quantity) AS reserved_qty
                FROM inventory_reservation
                WHERE stock_id = 1
                GROUP BY sku
            ) res
                ON res.sku = cpe.sku
            LEFT JOIN (
                SELECT
                    COALESCE(cpsl.parent_id, soi.product_id) AS product_id,
                    COUNT(DISTINCT soi.order_id) AS orders_count
                FROM sales_order_item soi
                INNER JOIN sales_order so ON so.entity_id = soi.order_id
                LEFT JOIN catalog_product_super_link cpsl ON cpsl.product_id = soi.product_id
                WHERE so.state != 'canceled'
                GROUP BY COALESCE(cpsl.parent_id, soi.product_id)
            ) ord
                ON ord.product_id = cpe.entity_id
            WHERE ccp.category_id = ?
              AND cpe.type_id IN ('simple', 'configurable')
              AND (
                  cpe.type_id = 'configurable'
                  OR NOT EXISTS (
                      SELECT 1
                      FROM catalog_product_super_link cpsl2
                      WHERE cpsl2.product_id = cpe.entity_id
                  )
              )
            ORDER BY product_name ASC
        ", [$nameAttributeId, $statusAttributeId, $categoryId])->getResultArray();
    }
}
