<?php

namespace App\Models\Magento;

use CodeIgniter\Model;

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

    /**
     * Retourne l'ID d'attribut EAV customer.
     */
    private function getCustomerAttributeId(string $attributeCode): ?int
    {
        $row = $this->db->query("
            SELECT attribute_id
            FROM eav_attribute
            WHERE attribute_code = ?
              AND entity_type_id = (
                  SELECT entity_type_id
                  FROM eav_entity_type
                  WHERE entity_type_code = 'customer'
                  LIMIT 1
              )
            LIMIT 1
        ", [$attributeCode])->getRowArray();

        return $row ? (int) $row['attribute_id'] : null;
    }

    /**
     * Retourne la liste de tous les dealers avec les infos admin.
     */
    public function getDealers(): array
    {
        return $this->db->query("
            SELECT
                d.entity_id AS dealer_id,
                d.user_id,
                d.description,
                d.emails,
                d.customer_group_ids,
                u.username AS admin_username,
                u.email AS admin_email,
                u.firstname AS admin_firstname,
                u.lastname AS admin_lastname,
                CONCAT(u.firstname, ' ', u.lastname) AS admin_fullname
            FROM amasty_perm_dealer d
            INNER JOIN admin_user u ON u.user_id = d.user_id
            ORDER BY admin_fullname ASC
        ")->getResultArray();
    }

    /**
     * Retourne un dealer par son ID.
     */
    public function getDealerById(int $dealerId): ?array
    {
        $row = $this->db->query("
            SELECT
                d.entity_id AS dealer_id,
                d.user_id,
                d.description,
                d.emails,
                d.customer_group_ids,
                u.username AS admin_username,
                u.email AS admin_email,
                u.firstname AS admin_firstname,
                u.lastname AS admin_lastname,
                CONCAT(u.firstname, ' ', u.lastname) AS admin_fullname
            FROM amasty_perm_dealer d
            INNER JOIN admin_user u ON u.user_id = d.user_id
            WHERE d.entity_id = ?
            LIMIT 1
        ", [$dealerId])->getRowArray();

        return $row ?: null;
    }

    /**
     * Retourne tous les clients affectés à un dealer.
     */
    public function getCustomersByDealer(int $dealerId): array
    {
        $firstnameId = $this->getCustomerAttributeId('firstname');
        $lastnameId = $this->getCustomerAttributeId('lastname');

        if ($firstnameId === null || $lastnameId === null) {
            return [];
        }

        return $this->db->query("
            SELECT
                c.entity_id AS customer_id,
                c.email AS customer_email,
                COALESCE(fn.value, '') AS firstname,
                COALESCE(ln.value, '') AS lastname,
                CONCAT(COALESCE(fn.value, ''), ' ', COALESCE(ln.value, '')) AS customer_fullname
            FROM amasty_perm_dealer_customer pc
            INNER JOIN customer_entity c ON c.entity_id = pc.customer_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) fn ON fn.entity_id = c.entity_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) ln ON ln.entity_id = c.entity_id
            WHERE pc.dealer_id = ?
            ORDER BY lastname ASC, firstname ASC
        ", [$firstnameId, $lastnameId, $dealerId])->getResultArray();
    }

    /**
     * Retourne tous les dealers avec leurs clients (mapping customer_id => dealer_id).
     */
    public function getAllCustomerDealerAssignments(): array
    {
        return $this->db->query("
            SELECT customer_id, dealer_id
            FROM amasty_perm_dealer_customer
        ")->getResultArray();
    }

    /**
     * Retourne les IDs client d'un dealer.
     */
    public function getCustomerIdsByDealer(int $dealerId): array
    {
        $rows = $this->db->query("
            SELECT customer_id
            FROM amasty_perm_dealer_customer
            WHERE dealer_id = ?
        ", [$dealerId])->getResultArray();

        return array_map('intval', array_column($rows, 'customer_id'));
    }

    /**
     * Retourne les commandes d'un dealer en se basant sur les clients affectés.
     */
    public function getOrdersByDealer(int $dealerId, ?string $dateDebut = null, ?string $dateFin = null): array
    {
        $params = [$dealerId];
        $dateWhere = '';

        if (!empty($dateDebut) && !empty($dateFin)) {
            $dateWhere = ' AND so.created_at BETWEEN ? AND ? ';
            $params[] = $dateDebut . ' 00:00:00';
            $params[] = $dateFin . ' 23:59:59';
        }

        return $this->db->query("
            SELECT
                so.entity_id AS order_id,
                so.increment_id,
                so.customer_id,
                so.customer_email,
                so.customer_firstname,
                so.customer_lastname,
                so.base_subtotal,
                so.status,
                so.state,
                so.created_at
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            WHERE pc.dealer_id = ?
              AND so.state != 'canceled'
              {$dateWhere}
            ORDER BY so.created_at DESC
        ", $params)->getResultArray();
    }

    /**
     * KPIs d'un dealer sur une période.
     */
    public function getDealerKPIs(int $dealerId, ?string $dateDebut = null, ?string $dateFin = null): array
    {
        $params = [$dealerId];
        $dateWhere = '';

        if (!empty($dateDebut) && !empty($dateFin)) {
            $dateWhere = ' AND so.created_at BETWEEN ? AND ? ';
            $params[] = $dateDebut . ' 00:00:00';
            $params[] = $dateFin . ' 23:59:59';
        }

        $row = $this->db->query("
            SELECT
                COUNT(DISTINCT so.entity_id) AS nb_commandes,
                COUNT(DISTINCT so.customer_id) AS nb_clients,
                SUM(so.base_subtotal) AS ca_total
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            WHERE pc.dealer_id = ?
              AND so.state != 'canceled'
              {$dateWhere}
        ", $params)->getRowArray();

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

    /**
     * Comparaison YTD année en cours vs N-1 pour un dealer.
     */
    public function getDealerYearComparison(int $dealerId): array
    {
        $currentYear = date('Y');
        $previousYear = (int) $currentYear - 1;
        $today = date('Y-m-d');
        $todayN1 = $previousYear . date('-m-d');

        $startN = $currentYear . '-01-01 00:00:00';
        $endN = $today . ' 23:59:59';
        $startN1 = $previousYear . '-01-01 00:00:00';
        $endN1 = $todayN1 . ' 23:59:59';

        $row = $this->db->query("
            SELECT
                SUM(CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.base_subtotal ELSE 0 END) AS ca_n,
                SUM(CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.base_subtotal ELSE 0 END) AS ca_n1,
                COUNT(DISTINCT CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.entity_id END) AS commandes_n,
                COUNT(DISTINCT CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.entity_id END) AS commandes_n1
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            WHERE pc.dealer_id = ?
              AND so.state != 'canceled'
        ", [$startN, $endN, $startN1, $endN1, $startN, $endN, $startN1, $endN1, $dealerId])->getRowArray();

        $caN = (float) ($row['ca_n'] ?? 0);
        $caN1 = (float) ($row['ca_n1'] ?? 0);
        $commandesN = (int) ($row['commandes_n'] ?? 0);
        $commandesN1 = (int) ($row['commandes_n1'] ?? 0);

        $evolutionCa = $caN1 > 0 ? round((($caN - $caN1) / $caN1) * 100, 2) : 0;
        $evolutionCommandes = $commandesN1 > 0 ? round((($commandesN - $commandesN1) / $commandesN1) * 100, 2) : 0;

        return [
            'annee_n'       => $currentYear,
            'annee_n1'      => $previousYear,
            'date_debut_n'  => $currentYear . '-01-01',
            'date_fin_n'    => $today,
            'date_debut_n1' => $previousYear . '-01-01',
            'date_fin_n1'   => $todayN1,
            'ca_n'          => $caN,
            'ca_n1'         => $caN1,
            'commandes_n'   => $commandesN,
            'commandes_n1'  => $commandesN1,
            'evolution_ca'  => $evolutionCa,
            'evolution_commandes' => $evolutionCommandes,
        ];
    }

    /**
     * Courbe mensuelle sur les N dernières années pour un dealer.
     */
    public function getDealerMonthlyCurve(int $dealerId, int $years = 2): array
    {
        $startDate = date('Y-m-d', strtotime("-{$years} years +1 day"));
        $rows = $this->db->query("
            SELECT
                DATE_FORMAT(so.created_at, '%Y-%m') AS month,
                COUNT(DISTINCT so.entity_id) AS nb_commandes,
                SUM(so.base_subtotal) AS ca_total
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            WHERE pc.dealer_id = ?
              AND so.state != 'canceled'
              AND so.created_at >= ?
            GROUP BY DATE_FORMAT(so.created_at, '%Y-%m')
            ORDER BY month ASC
        ", [$dealerId, $startDate])->getResultArray();

        return array_map(static function ($row) {
            return [
                'month'        => $row['month'],
                'nb_commandes' => (int) $row['nb_commandes'],
                'ca_total'     => (float) $row['ca_total'],
            ];
        }, $rows);
    }

    /**
     * Retourne les clients d'un dealer avec leur CA total, société et nombre de commandes.
     */
    public function getCustomersByDealerWithCa(int $dealerId): array
    {
        $firstnameId = $this->getCustomerAttributeId('firstname');
        $lastnameId = $this->getCustomerAttributeId('lastname');
        $societeId = $this->getCustomerAttributeId('ca_societe');

        if ($firstnameId === null || $lastnameId === null) {
            return [];
        }

        $currentYear = date('Y');
        $previousYear = (int) $currentYear - 1;
        $today = date('Y-m-d');
        $todayN1 = $previousYear . date('-m-d');
        $startN = $currentYear . '-01-01 00:00:00';
        $endN = $today . ' 23:59:59';
        $startN1 = $previousYear . '-01-01 00:00:00';
        $endN1 = $todayN1 . ' 23:59:59';

        $params = [$startN, $endN, $startN1, $endN1, $firstnameId, $lastnameId, $dealerId];
        $societeJoin = '';
        $societeSelect = "'' AS company";

        if ($societeId !== null) {
            $societeSelect = "COALESCE(MAX(societe.value), '') AS company";
            $societeJoin = 'LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) societe ON societe.entity_id = c.entity_id';
            $params = [$startN, $endN, $startN1, $endN1, $firstnameId, $lastnameId, $societeId, $dealerId];
        }

        $rows = $this->db->query("
            SELECT
                c.entity_id AS customer_id,
                c.email AS customer_email,
                COALESCE(MAX(fn.value), MAX(so.customer_firstname), '') AS firstname,
                COALESCE(MAX(ln.value), MAX(so.customer_lastname), '') AS lastname,
                CONCAT(COALESCE(MAX(fn.value), MAX(so.customer_firstname), ''), ' ', COALESCE(MAX(ln.value), MAX(so.customer_lastname), '')) AS customer_fullname,
                {$societeSelect},
                COUNT(DISTINCT so.entity_id) AS nb_commandes,
                COALESCE(SUM(so.base_subtotal), 0) AS ca_total,
                SUM(CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.base_subtotal ELSE 0 END) AS ca_n,
                SUM(CASE WHEN so.created_at >= ? AND so.created_at <= ? THEN so.base_subtotal ELSE 0 END) AS ca_n1
            FROM amasty_perm_dealer_customer pc
            INNER JOIN customer_entity c ON c.entity_id = pc.customer_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) fn ON fn.entity_id = c.entity_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) ln ON ln.entity_id = c.entity_id
            {$societeJoin}
            LEFT JOIN sales_order so ON so.customer_id = c.entity_id AND so.state != 'canceled'
            WHERE pc.dealer_id = ?
            GROUP BY c.entity_id, c.email
            ORDER BY ca_total DESC
        ", $params)->getResultArray();

        return array_map(static function ($row) {
            $caTotal = (float) $row['ca_total'];
            $caN = (float) $row['ca_n'];
            $caN1 = (float) $row['ca_n1'];
            $row['ca_total'] = $caTotal;
            $row['ca_n'] = $caN;
            $row['ca_n1'] = $caN1;
            $row['evolution_ca_n1'] = $caN1 > 0 ? round((($caN - $caN1) / $caN1) * 100, 2) : 0;
            return $row;
        }, $rows);
    }

    /**
     * Top clients d'un dealer sur une période.
     */
    public function getTopCustomersByDealer(int $dealerId, ?string $dateDebut = null, ?string $dateFin = null, int $limit = 20): array
    {
        $params = [$dealerId];
        $dateWhere = '';

        if (!empty($dateDebut) && !empty($dateFin)) {
            $dateWhere = ' AND so.created_at BETWEEN ? AND ? ';
            $params[] = $dateDebut . ' 00:00:00';
            $params[] = $dateFin . ' 23:59:59';
        }
        $params[] = $limit;

        return $this->db->query("
            SELECT
                so.customer_id,
                so.customer_email,
                so.customer_firstname,
                so.customer_lastname,
                COUNT(DISTINCT so.entity_id) AS nb_commandes,
                SUM(so.base_subtotal) AS ca_total
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            WHERE pc.dealer_id = ?
              AND so.state != 'canceled'
              {$dateWhere}
            GROUP BY so.customer_id, so.customer_email, so.customer_firstname, so.customer_lastname
            ORDER BY ca_total DESC
            LIMIT ?
        ", $params)->getResultArray();
    }

    /**
     * Résumé global : tous les dealers avec leurs clients et commandes.
     */
    public function getDealersSummary(): array
    {
        return $this->db->query("
            SELECT
                d.entity_id AS dealer_id,
                d.user_id,
                CONCAT(u.firstname, ' ', u.lastname) AS dealer_name,
                u.email AS dealer_email,
                COUNT(DISTINCT pc.customer_id) AS nb_clients,
                COUNT(DISTINCT so.entity_id) AS nb_commandes,
                COALESCE(SUM(so.base_subtotal), 0) AS ca_total
            FROM amasty_perm_dealer d
            INNER JOIN admin_user u ON u.user_id = d.user_id
            LEFT JOIN amasty_perm_dealer_customer pc ON pc.dealer_id = d.entity_id
            LEFT JOIN sales_order so
                ON so.customer_id = pc.customer_id
                AND so.state != 'canceled'
            GROUP BY d.entity_id, d.user_id, u.firstname, u.lastname, u.email
            ORDER BY dealer_name ASC
        ")->getResultArray();
    }

    /**
     * Liste de tous les clients avec leur dealer assigné.
     */
    public function getAllCustomersWithDealer(): array
    {
        $firstnameId = $this->getCustomerAttributeId('firstname');
        $lastnameId = $this->getCustomerAttributeId('lastname');

        if ($firstnameId === null || $lastnameId === null) {
            return [];
        }

        return $this->db->query("
            SELECT
                c.entity_id AS customer_id,
                c.email AS customer_email,
                COALESCE(fn.value, '') AS firstname,
                COALESCE(ln.value, '') AS lastname,
                CONCAT(COALESCE(fn.value, ''), ' ', COALESCE(ln.value, '')) AS customer_fullname,
                d.entity_id AS dealer_id,
                d.user_id,
                CONCAT(u.firstname, ' ', u.lastname) AS dealer_name,
                u.email AS dealer_email
            FROM amasty_perm_dealer_customer pc
            INNER JOIN customer_entity c ON c.entity_id = pc.customer_id
            INNER JOIN amasty_perm_dealer d ON d.entity_id = pc.dealer_id
            INNER JOIN admin_user u ON u.user_id = d.user_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) fn ON fn.entity_id = c.entity_id
            LEFT JOIN (
                SELECT entity_id, MAX(value) AS value
                FROM customer_entity_varchar
                WHERE attribute_id = ?
                GROUP BY entity_id
            ) ln ON ln.entity_id = c.entity_id
            ORDER BY dealer_name ASC, lastname ASC, firstname ASC
        ", [$firstnameId, $lastnameId])->getResultArray();
    }

    /**
     * Liste de toutes les commandes rattachées à un dealer via les clients.
     */
    public function getAllOrdersWithDealer(): array
    {
        return $this->db->query("
            SELECT
                so.entity_id AS order_id,
                so.increment_id,
                so.customer_id,
                so.customer_email,
                so.customer_firstname,
                so.customer_lastname,
                so.base_subtotal,
                so.status,
                so.state,
                so.created_at,
                d.entity_id AS dealer_id,
                d.user_id,
                CONCAT(u.firstname, ' ', u.lastname) AS dealer_name,
                u.email AS dealer_email
            FROM sales_order so
            INNER JOIN amasty_perm_dealer_customer pc ON pc.customer_id = so.customer_id
            INNER JOIN amasty_perm_dealer d ON d.entity_id = pc.dealer_id
            INNER JOIN admin_user u ON u.user_id = d.user_id
            WHERE so.state != 'canceled'
            ORDER BY so.created_at DESC
        ")->getResultArray();
    }
}
