<?php

require_once '../../core/config.php';
require_once '../../core/session.php';
require_once '../../core/permissions.php';
require_once '../../core/database.php';

require_login();
class CrmService
{
    private $pdo;

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

    public function getCustomers()
    {
        $stmt = $this->pdo->query("
            SELECT
                c.*,
    
                COALESCE(o.open_opportunities, 0) AS open_opportunities,
    
                f.last_purchase_date,
                COALESCE(f.total_net_sales, 0) AS total_net_sales,
                COALESCE(f.total_documents, 0) AS total_documents
    
            FROM crm_customers c

            LEFT JOIN (
                SELECT
                    customer_id,
                    COUNT(*) AS open_opportunities
                FROM crm_opportunities
                WHERE deleted_at IS NULL
                  AND stage NOT IN ('ganada', 'perdida', 'facturada')
                GROUP BY customer_id
            ) o ON o.customer_id = c.id
    
            LEFT JOIN (
                SELECT
                    receiver_tax_id_code,
                    MAX(issue_date) AS last_purchase_date,
                    SUM(
                        CASE
                            WHEN document_type_taxbureau IN (33, 34, 39, 56) THEN net_amount
                            WHEN document_type_taxbureau = 61 THEN -net_amount
                            ELSE 0
                        END
                    ) AS total_net_sales,
                    COUNT(*) AS total_documents
                FROM facto_documents
                WHERE received_issued_flag = 1
                  AND document_status = 1
                  AND receiver_tax_id_code IS NOT NULL
                  AND receiver_tax_id_code <> ''
                  AND document_type_taxbureau IN (33, 34, 39, 56, 61)
                GROUP BY receiver_tax_id_code
            ) f ON f.receiver_tax_id_code = c.customer_tax_id

            WHERE c.deleted_at IS NULL

            ORDER BY
                CASE
                    WHEN f.last_purchase_date IS NULL THEN 1
                    ELSE 0
                END,
                f.last_purchase_date DESC,
                c.customer_name ASC
        ");
    
        return $stmt->fetchAll();
    }

    public function getCustomer($id)
    {
        return $this->getCustomerById($id);
    }

    public function getCustomerById($id, $includeDeleted = false)
    {
        $sql = "
            SELECT *
            FROM crm_customers
            WHERE id = ?
        ";

        if (!$includeDeleted) {
            $sql .= " AND deleted_at IS NULL";
        }

        $sql .= " LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array((int) $id));

        return $stmt->fetch();
    }

    public function getDashboardKpis()
    {
        $sql = "
            SELECT
                COUNT(*) AS open_opportunities,
                COALESCE(SUM(expected_value), 0) AS embudo_value,
                COALESCE(SUM(expected_value * probability / 100), 0) AS weighted_embudo,
                COUNT(CASE WHEN stage = 'ganada' THEN 1 ELSE NULL END) AS won_count,
                COUNT(CASE WHEN stage = 'perdida' THEN 1 ELSE NULL END) AS lost_count
            FROM crm_opportunities o
            JOIN crm_customers c ON c.id = o.customer_id
            WHERE o.stage NOT IN ('facturada')
              AND o.deleted_at IS NULL
              AND c.deleted_at IS NULL
        ";

        return $this->pdo->query($sql)->fetch();
    }

    public function getEmbudo()
    {
        $stmt = $this->pdo->query("
            SELECT
                o.*,
                c.customer_tax_id AS customer_tax_id,
                c.legal_name AS customer_legal_name,
                c.email AS customer_email,
                c.phone AS customer_phone,
                c.city AS customer_city,
                c.district AS customer_district,
                c.region AS customer_region,
                c.country AS customer_country
            FROM crm_opportunities o
            JOIN crm_customers c ON c.id = o.customer_id
            WHERE o.deleted_at IS NULL
              AND c.deleted_at IS NULL
            ORDER BY o.expected_close_date ASC, o.updated_at DESC
        ");

        $rows = $stmt->fetchAll();

        $embudo = array(
            'nuevo' => array(),
            'contactado' => array(),
            'cotizando' => array(),
            'negociacion' => array(),
            'ganada' => array(),
            'perdida' => array(),
            'facturada' => array()
        );

        foreach ($rows as $row) {
            if (isset($embudo[$row['stage']])) {
                $embudo[$row['stage']][] = $row;
            }
        }

        return $embudo;
    }

    public function getOpportunity($id)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_opportunities
            WHERE id = ?
              AND deleted_at IS NULL
            LIMIT 1
        ");

        $stmt->execute(array((int) $id));

        return $stmt->fetch();
    }

    public function getActivities($opportunityId)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_activities
            WHERE opportunity_id = ?
            ORDER BY activity_date DESC, id DESC
        ");

        $stmt->execute(array((int) $opportunityId));

        return $stmt->fetchAll();
    }

    public function getDocuments($opportunityId)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_documents
            WHERE opportunity_id = ?
            ORDER BY document_date DESC, id DESC
        ");

        $stmt->execute(array((int) $opportunityId));

        return $stmt->fetchAll();
    }

    public function getCustomerAnalytics($customerTaxId)
    {
        if (!$customerTaxId) {
            return null;
        }

        $stmt = $this->pdo->prepare("
            SELECT *
            FROM analytics_customers
            WHERE receiver_tax_id_code = ?
            LIMIT 1
        ");

        $stmt->execute(array($customerTaxId));

        return $stmt->fetch();
    }

    public function getCustomerOpportunities($customerId)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_opportunities
            WHERE customer_id = ?
              AND deleted_at IS NULL
            ORDER BY updated_at DESC
        ");

        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }

    public function getCustomerOpenOpportunities($customerId)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_opportunities
            WHERE customer_id = ?
              AND deleted_at IS NULL
              AND stage NOT IN ('ganada','perdida','facturada')
            ORDER BY expected_close_date ASC, updated_at DESC
        ");

        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }


    public function getCustomerQuotes($customerId, $limit = 20)
    {
        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 20;
        }

        $sql = "
            SELECT
                q.*,
                o.title AS opportunity_title,
                o.stage AS opportunity_stage
            FROM crm_quotes q
            LEFT JOIN crm_opportunities o ON o.id = q.opportunity_id
            WHERE q.customer_id = ?
              AND (q.deleted_at IS NULL OR q.deleted_at = '')
            ORDER BY q.created_at DESC, q.id DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }

    public function getCustomerDocuments($customerId)
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM crm_documents
            WHERE customer_id = ?
            ORDER BY document_date DESC, id DESC
        ");

        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }

    public function getCustomerActivities($customerId, $limit = 40)
    {
        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 40;
        }

        $sql = "
            SELECT *
            FROM crm_activities
            WHERE customer_id = ?
            ORDER BY activity_date DESC, id DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }

    public function getCustomerPendingActions($customerId)
    {
        $stmt = $this->pdo->prepare("
            SELECT
                id,
                title,
                stage,
                next_action,
                next_action_date,
                owner_name,
                expected_value,
                probability
            FROM crm_opportunities
            WHERE customer_id = ?
              AND deleted_at IS NULL
              AND stage NOT IN ('ganada','perdida','facturada')
              AND next_action IS NOT NULL
              AND next_action <> ''
            ORDER BY
                CASE WHEN next_action_date IS NULL THEN 1 ELSE 0 END,
                next_action_date ASC,
                updated_at DESC
        ");

        $stmt->execute(array((int) $customerId));

        return $stmt->fetchAll();
    }

    public function getCustomerInvoicesFromFacto($customerTaxId, $limit = 20)
    {
        if (!$customerTaxId) {
            return array();
        }

        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 20;
        }

        $sql = "
            SELECT *
            FROM facto_documents
            WHERE receiver_tax_id_code = ?
              AND received_issued_flag = 1
              AND document_status = 1
            ORDER BY issue_date DESC, document_id DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($customerTaxId));

        return $stmt->fetchAll();
    }

    public function getCustomerFactoSummary($customerTaxId)
    {
        if (!$customerTaxId) {
            return array(
                'net_sales' => 0,
                'documents_count' => 0,
                'last_document_date' => null,
                'credit_notes' => 0
            );
        }

        $stmt = $this->pdo->prepare("
            SELECT
                COALESCE(SUM(
                    CASE
                        WHEN document_type_taxbureau IN (33,34,39,56) THEN net_amount
                        WHEN document_type_taxbureau = 61 THEN -net_amount
                        ELSE 0
                    END
                ), 0) AS net_sales,

                COUNT(*) AS documents_count,

                MAX(issue_date) AS last_document_date,

                COUNT(CASE
                    WHEN document_type_taxbureau = 61 THEN 1
                    ELSE NULL
                END) AS credit_notes

            FROM facto_documents
            WHERE receiver_tax_id_code = ?
              AND received_issued_flag = 1
              AND document_status = 1
              AND document_type_taxbureau IN (33,34,39,56,61)
        ");

        $stmt->execute(array($customerTaxId));

        return $stmt->fetch();
    }

    public function logActivity($opportunityId, $customerId, $customerName, $type, $title, $description, $ownerName)
    {
        $stmt = $this->pdo->prepare("
            INSERT INTO crm_activities
            (
                opportunity_id,
                customer_id,
                customer_name,
                activity_type,
                title,
                description,
                owner_name
            )
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt->execute(array(
            $opportunityId,
            $customerId,
            $customerName,
            $type,
            $title,
            $description,
            $ownerName
        ));
    }
    
    public function getAssignableUsers()
    {
        $stmt = $this->pdo->query("
            SELECT
                id,
                name,
                email,
                role
            FROM users
            WHERE status = 'active'
              AND role IN ('admin', 'administracion', 'comercial')
            ORDER BY
                FIELD(role, 'admin', 'administracion', 'comercial'),
                name ASC
        ");
    
        return $stmt->fetchAll();
    }

    public function updateCustomerRestockFrequency($customerId, $customerTaxId) {
        if (empty($customerTaxId)) return false;
    
        $invoices = $this->getCustomerInvoicesFromFacto($customerTaxId, 20);
    
        if (!is_array($invoices) || count($invoices) < 2) {
            return false; 
        }
    
        usort($invoices, function($a, $b) {
            return strtotime($a['issue_date']) <=> strtotime($b['issue_date']);
        });
    
        $totalDays = 0;
        $intervals = 0;
    
        for ($i = 1; $i < count($invoices); $i++) {
            $date1 = new DateTime($invoices[$i - 1]['issue_date']);
            $date2 = new DateTime($invoices[$i]['issue_date']);
            $diff = $date1->diff($date2)->days;
    
            if ($diff > 2) { 
                $totalDays += $diff;
                $intervals++;
            }
        }
    
        if ($intervals === 0) return false;

        $restockDays = (int)round($totalDays / $intervals);
    
        $lastInvoiceDate = new DateTime(end($invoices)['issue_date']);
        $lastInvoiceDate->modify("+$restockDays days");
        $nextRestockDate = $lastInvoiceDate->format('Y-m-d');
    
        $stmt = $this->pdo->prepare("UPDATE crm_customers SET restock_days = ?, next_restock_date = ? WHERE id = ?");
        $stmt->execute([$restockDays, $nextRestockDate, $customerId]);
    
        return [
            'restock_days' => $restockDays,
            'next_restock_date' => $nextRestockDate
        ];
    } 
}