<?php

require_once '../../core/config.php';
require_once '../../core/session.php';
require_once '../../core/permissions.php';
require_once '../../core/database.php';
require_once __DIR__ . '/RestockForecastService.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 archived_at IS NULL
                  AND stage IN ('nuevo', 'contactado', 'cotizando', 'negociacion')
                GROUP BY customer_id
            ) o ON o.customer_id = c.id
    
            LEFT JOIN (
                SELECT
                    fd.crm_customer_id AS customer_id,
                    MAX(fd.issue_date) AS last_purchase_date,
                    SUM(
                        CASE
                            WHEN fd.document_type_taxbureau IN (33,34,39,56,110,111) THEN COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                            WHEN fd.document_type_taxbureau IN (61,112) THEN -COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                            ELSE 0
                        END
                    ) AS total_net_sales,
                    COUNT(*) AS total_documents
                FROM facto_documents fd
                LEFT JOIN billing_currencies cur
                  ON cur.facto_currency_id = CAST(fd.currency_id AS UNSIGNED)
                WHERE fd.received_issued_flag = 1
                  AND fd.document_status = 1
                  AND fd.crm_customer_id IS NOT NULL
                  AND fd.document_type_taxbureau IN (33, 34, 39, 56, 61, 110, 111, 112)
                GROUP BY fd.crm_customer_id
            ) f ON f.customer_id = c.id

            WHERE c.deleted_at IS NULL
              AND c.counterparty_type IN ('customer', 'both')

            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(CASE WHEN o.archived_at IS NULL AND o.stage IN ('nuevo','contactado','cotizando','negociacion') THEN 1 END) AS open_opportunities,
                COALESCE(SUM(CASE WHEN o.archived_at IS NULL AND o.stage IN ('nuevo','contactado','cotizando','negociacion') THEN o.expected_value ELSE 0 END), 0) AS embudo_value,
                COALESCE(SUM(CASE WHEN o.archived_at IS NULL AND o.stage IN ('nuevo','contactado','cotizando','negociacion') THEN o.expected_value * o.probability / 100 ELSE 0 END), 0) AS weighted_embudo,
                COUNT(CASE WHEN o.archived_at IS NULL AND o.stage = 'ganada' THEN 1 END) AS won_count,
                COUNT(CASE WHEN o.archived_at IS NULL AND o.stage = 'perdida' THEN 1 END) AS lost_count
            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
        ";

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

    public function getEmbudo()
    {
        $stmt = $this->pdo->query("
            SELECT
                o.*,
                c.customer_tax_id AS customer_tax_id,
                COALESCE(NULLIF(c.commercial_name, ''), NULLIF(c.customer_name, ''), c.legal_name) AS customer_display_name,
                c.commercial_name AS customer_commercial_name,
                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 o.archived_at IS NULL
              AND o.stage IN ('nuevo','contactado','cotizando','negociacion','ganada','perdida','facturada')
              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
                o.*,
                COALESCE(NULLIF(c.commercial_name, ''), NULLIF(c.customer_name, ''), c.legal_name, o.customer_name) AS customer_display_name,
                c.commercial_name AS customer_commercial_name,
                c.legal_name AS customer_legal_name,
                COALESCE(NULLIF(c.customer_tax_id, ''), o.customer_tax_id) AS customer_current_tax_id,
                c.email AS customer_email,
                c.phone AS customer_phone
            FROM crm_opportunities o
            LEFT JOIN crm_customers c ON c.id = o.customer_id AND c.deleted_at IS NULL
            WHERE o.id = ?
              AND o.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
                d.*,
                fd.id AS linked_facto_local_id,
                fd.environment AS linked_facto_environment,
                fd.pdf_file_path AS linked_pdf_file_path,
                fd.currency_code AS linked_currency_code,
                fd.exchange_rate_value AS linked_exchange_rate_value,
                fd.exchange_rate_date AS linked_exchange_rate_date,
                fd.exchange_rate_source AS linked_exchange_rate_source,
                fd.exchange_rate_status AS linked_exchange_rate_status,
                fd.total_amount AS linked_foreign_total_amount,
                fd.total_amount_clp AS linked_total_amount_clp
            FROM crm_documents d
            LEFT JOIN facto_documents fd
                   ON BINARY fd.document_id = BINARY d.facto_document_id
                  AND BINARY fd.environment = BINARY 'production'
            WHERE d.opportunity_id = ?
            ORDER BY d.document_date DESC, d.id DESC
        ");

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

        return $stmt->fetchAll();
    }

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

        $normalized = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', (string)$customerTaxId));
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM analytics_customers
            WHERE REPLACE(REPLACE(UPPER(receiver_tax_id_code),'.',''),'-','') = ?
            LIMIT 1
        ");

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

        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 archived_at IS NULL
              AND stage IN ('nuevo','contactado','cotizando','negociacion')
            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 archived_at IS NULL
              AND stage IN ('nuevo','contactado','cotizando','negociacion')
              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(array $customer, $limit = 20)
    {
        $customerId = (int)($customer['id'] ?? 0);
        if ($customerId <= 0) {
            return array();
        }

        $limit = max(1, min(200, (int)$limit));
        $params = array();
        $matchSql = $this->customerFactoDocumentMatchSql($customer, $params, 'fd');

        $sql = "
            SELECT fd.*,
                   COALESCE(NULLIF(fd.currency_code, ''), cur.currency_code, 'CLP') AS resolved_currency_code
            FROM facto_documents fd
            LEFT JOIN billing_currencies cur
              ON cur.facto_currency_id = CAST(fd.currency_id AS UNSIGNED)
            WHERE (" . $matchSql . ")
              AND fd.received_issued_flag = 1
              AND fd.document_status = 1
              AND fd.document_type_taxbureau IN (33,34,39,56,61,110,111,112)
            ORDER BY fd.issue_date DESC, fd.id DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getCustomerFactoSummary(array $customer)
    {
        $customerId = (int)($customer['id'] ?? 0);
        if ($customerId <= 0) {
            return array(
                'net_sales' => 0,
                'sales_last_12m' => 0,
                'documents_count' => 0,
                'last_document_date' => null,
                'credit_notes' => 0,
                'export_documents' => 0,
                'unconverted_export_documents' => 0,
            );
        }

        $params = array();
        $matchSql = $this->customerFactoDocumentMatchSql($customer, $params, 'fd');
        $stmt = $this->pdo->prepare("
            SELECT
                COALESCE(SUM(
                    CASE
                        WHEN fd.document_type_taxbureau IN (33,34,39,56,110,111) THEN COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                        WHEN fd.document_type_taxbureau IN (61,112) THEN -COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                        ELSE 0
                    END
                ), 0) AS net_sales,
                COALESCE(SUM(
                    CASE
                        WHEN fd.issue_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                         AND fd.document_type_taxbureau IN (33,34,39,56,110,111)
                        THEN COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                        WHEN fd.issue_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                         AND fd.document_type_taxbureau IN (61,112)
                        THEN -COALESCE(fd.net_amount_clp, CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),cur.currency_code,'CLP'))='CLP' THEN fd.net_amount WHEN COALESCE(fd.exchange_rate_value,0)>0 THEN ROUND(fd.net_amount*fd.exchange_rate_value,2) ELSE 0 END)
                        ELSE 0
                    END
                ), 0) AS sales_last_12m,
                COUNT(*) AS documents_count,
                MAX(fd.issue_date) AS last_document_date,
                SUM(CASE WHEN fd.document_type_taxbureau IN (61,112) THEN 1 ELSE 0 END) AS credit_notes,
                SUM(CASE WHEN fd.document_type_taxbureau IN (110,111,112) THEN 1 ELSE 0 END) AS export_documents,
                SUM(CASE
                    WHEN fd.document_type_taxbureau IN (110,111,112)
                     AND COALESCE(NULLIF(fd.currency_code, ''), cur.currency_code, 'CLP') <> 'CLP'
                     AND (COALESCE(fd.exchange_rate_value, 0) <= 0 OR fd.net_amount_clp IS NULL)
                    THEN 1 ELSE 0 END
                ) AS unconverted_export_documents
            FROM facto_documents fd
            LEFT JOIN billing_currencies cur
              ON cur.facto_currency_id = CAST(fd.currency_id AS UNSIGNED)
            WHERE (" . $matchSql . ")
              AND fd.received_issued_flag = 1
              AND fd.document_status = 1
              AND fd.document_type_taxbureau IN (33,34,39,56,61,110,111,112)
        ");
        $stmt->execute($params);
        return $stmt->fetch();
    }

    private function customerFactoDocumentMatchSql(array $customer, array &$params, $alias = 'fd')
    {
        $prefix = $alias !== '' ? $alias . '.' : '';
        $conditions = array();

        $customerId = (int)($customer['id'] ?? 0);
        if ($customerId > 0) {
            $conditions[] = $prefix . 'crm_customer_id = ?';
            $params[] = $customerId;
        }

        $factoCustomerId = trim((string)($customer['facto_customer_supplier_id'] ?? ''));
        if ($factoCustomerId !== '') {
            $conditions[] = 'BINARY ' . $prefix . 'customer_supplier_id = BINARY ?';
            $params[] = $factoCustomerId;
        }

        foreach (array('export_tax_id_code', 'customer_tax_id') as $field) {
            $taxId = $this->normalizeFactoTaxId((string)($customer[$field] ?? ''));
            if ($taxId === '') continue;
            $conditions[] = "REPLACE(REPLACE(REPLACE(UPPER(" . $prefix . "receiver_tax_id_code), '.', ''), '-', ''), ' ', '') = ?";
            $params[] = $taxId;
        }

        return $conditions ? implode(' OR ', $conditions) : '1 = 0';
    }

    private function normalizeFactoTaxId($value)
    {
        return strtoupper(preg_replace('/[^0-9A-Z]/i', '', trim((string)$value)) ?: '');
    }

    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 projectCustomerRestockDate($customerId, $customerTaxId = '')
    {
        $service = new RestockForecastService($this->pdo);
        return $service->projectCustomerRestockDate((int)$customerId, (string)$customerTaxId);
    }

    public function updateCustomerRestockFrequency($customerId, $customerTaxId)
    {
        $service = new RestockForecastService($this->pdo);
        return $service->recalculateCustomerCycle((int)$customerId, (string)$customerTaxId);
    }

}
