<?php

require_once __DIR__ . '/../../core/database.php';

/**
 * Dashboard comercial
 *
 * Mantiene compatibilidad entre la nomenclatura anterior (pipeline) y la nueva
 * nomenclatura visible (embudo), para evitar que una actualizacion parcial del
 * ZIP deje inaccesible el dashboard.
 */
class CommercialDashboardService
{
    private $pdo;
    private $customerSoftDeleteAvailable = null;

    public function __construct()
    {
        $this->pdo = db();
    }

    private function hasCustomerSoftDelete()
    {
        if ($this->customerSoftDeleteAvailable !== null) {
            return $this->customerSoftDeleteAvailable;
        }

        try {
            $stmt = $this->pdo->query("SHOW COLUMNS FROM crm_customers LIKE 'deleted_at'");
            $this->customerSoftDeleteAvailable = (bool)$stmt->fetch();
        } catch (Throwable $e) {
            $this->customerSoftDeleteAvailable = false;
        }

        return $this->customerSoftDeleteAvailable;
    }

    private function customerJoinSql()
    {
        if (!$this->hasCustomerSoftDelete()) {
            return '';
        }

        return "\n            LEFT JOIN crm_customers c ON c.id = o.customer_id\n        ";
    }

    private function activeCustomerWhereSql()
    {
        if (!$this->hasCustomerSoftDelete()) {
            return '';
        }

        // LEFT JOIN conserva oportunidades historicas sin cliente o con customer_id antiguo,
        // pero excluye clientes desactivados cuando el vinculo existe.
        return "\n              AND (c.id IS NULL OR c.deleted_at IS NULL)\n        ";
    }

    public function getKpis()
    {
        $sql = "
            SELECT
                COUNT(*) AS open_opportunities,
                COALESCE(SUM(o.expected_value), 0) AS embudo_value,
                COALESCE(SUM(o.expected_value), 0) AS pipeline_value,
                COALESCE(SUM(o.expected_value * o.probability / 100), 0) AS weighted_embudo,
                COALESCE(SUM(o.expected_value * o.probability / 100), 0) AS weighted_pipeline,

                COALESCE(SUM(
                    CASE
                        WHEN o.payment_status = 'por_cobrar' THEN o.expected_value
                        ELSE 0
                    END
                ), 0) AS receivables_amount,

                COALESCE(SUM(
                    CASE
                        WHEN o.payment_status = 'atrasado' THEN o.expected_value
                        ELSE 0
                    END
                ), 0) AS overdue_amount,

                COUNT(
                    CASE
                        WHEN o.next_action_date < CURDATE()
                             AND o.stage NOT IN ('ganada','perdida','facturada')
                        THEN 1
                        ELSE NULL
                    END
                ) AS overdue_actions

            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
              AND o.stage NOT IN ('ganada','perdida','facturada')
        ";

        $stmt = $this->pdo->query($sql);
        $row = $stmt->fetch();

        return $row ?: array(
            'open_opportunities' => 0,
            'embudo_value' => 0,
            'pipeline_value' => 0,
            'weighted_embudo' => 0,
            'weighted_pipeline' => 0,
            'receivables_amount' => 0,
            'overdue_amount' => 0,
            'overdue_actions' => 0,
        );
    }

    public function getTodayActions()
    {
        $sql = "
            SELECT o.*
            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
              AND o.stage NOT IN ('ganada','perdida','facturada')
              AND o.next_action IS NOT NULL
              AND o.next_action <> ''
              AND o.next_action_date <= CURDATE()
            ORDER BY o.next_action_date ASC, o.expected_value DESC
            LIMIT 20
        ";

        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }

    public function getUpcomingActions()
    {
        $sql = "
            SELECT o.*
            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
              AND o.stage NOT IN ('ganada','perdida','facturada')
              AND o.next_action IS NOT NULL
              AND o.next_action <> ''
              AND o.next_action_date > CURDATE()
              AND o.next_action_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
            ORDER BY o.next_action_date ASC, o.expected_value DESC
            LIMIT 20
        ";

        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }

    public function getEmbudoByStage()
    {
        $sql = "
            SELECT
                o.stage,
                COUNT(*) AS opportunities,
                COALESCE(SUM(o.expected_value), 0) AS total_value,
                COALESCE(SUM(o.expected_value * o.probability / 100), 0) AS weighted_value
            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
            GROUP BY o.stage
            ORDER BY FIELD(o.stage, 'nuevo','contactado','cotizando','negociacion','ganada','perdida','facturada')
        ";

        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }

    public function getEmbudoBySource()
    {
        $sql = "
            SELECT
                COALESCE(NULLIF(o.source, ''), 'Sin origen') AS source,
                COUNT(*) AS opportunities,
                COALESCE(SUM(o.expected_value), 0) AS total_value,
                COALESCE(SUM(o.expected_value * o.probability / 100), 0) AS weighted_value
            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
            GROUP BY COALESCE(NULLIF(o.source, ''), 'Sin origen')
            ORDER BY total_value DESC
            LIMIT 10
        ";

        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }

    public function getPipelineByStage()
    {
        return $this->getEmbudoByStage();
    }

    public function getPipelineBySource()
    {
        return $this->getEmbudoBySource();
    }

    public function getReceivables()
    {
        $sql = "
            SELECT o.*
            FROM crm_opportunities o
            " . $this->customerJoinSql() . "
            WHERE o.deleted_at IS NULL
              " . $this->activeCustomerWhereSql() . "
              AND o.payment_status IN ('por_cobrar','atrasado')
            ORDER BY
                CASE o.payment_status
                    WHEN 'atrasado' THEN 1
                    WHEN 'por_cobrar' THEN 2
                    ELSE 3
                END,
                o.payment_due_date ASC,
                o.expected_value DESC
            LIMIT 20
        ";

        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }
}
