<?php

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

class CustomerTerritoryService
{
    private $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: db();
    }

    public function getCustomers(array $filters = array())
    {
        $where = array('c.deleted_at IS NULL');
        $params = array();

        $q = trim((string)($filters['q'] ?? ''));
        if ($q !== '') {
            $where[] = "(
                c.customer_name LIKE ?
                OR c.commercial_name LIKE ?
                OR c.legal_name LIKE ?
                OR c.customer_tax_id LIKE ?
                OR c.email LIKE ?
                OR c.phone LIKE ?
                OR c.address LIKE ?
                OR c.district LIKE ?
                OR c.city LIKE ?
                OR c.region LIKE ?
                OR c.country LIKE ?
            )";
            for ($i = 0; $i < 11; $i++) {
                $params[] = '%' . $q . '%';
            }
        }

        $segment = trim((string)($filters['customer_segment'] ?? ''));
        if ($segment === 'purchased') {
            $where[] = '(COALESCE(f.total_net_sales, ac.total_net_sales, 0) > 0 OR COALESCE(qs.paid_quotes, 0) > 0)';
        } elseif ($segment === 'invoiced') {
            $where[] = '(COALESCE(f.invoice_count, ac.invoice_count, 0) > 0 OR COALESCE(qs.invoiced_quotes, 0) > 0)';
        } elseif ($segment === 'quoted_only') {
            $where[] = "(
                COALESCE(qs.quote_count, 0) > 0
                AND COALESCE(f.invoice_count, ac.invoice_count, 0) = 0
                AND COALESCE(f.total_net_sales, ac.total_net_sales, 0) <= 0
                AND COALESCE(qs.invoiced_quotes, 0) = 0
                AND COALESCE(qs.paid_quotes, 0) = 0
            )";
        }

        $quoteStatus = trim((string)($filters['quote_status'] ?? ''));
        if ($quoteStatus !== '') {
            $where[] = "EXISTS (
                SELECT 1
                FROM crm_quotes qf
                WHERE qf.customer_id = c.id
                  AND qf.deleted_at IS NULL
                  AND qf.status = ?
            )";
            $params[] = $quoteStatus;
        }

        $sql = "
            SELECT
                c.id,
                c.customer_name,
                c.commercial_name,
                c.legal_name,
                c.customer_tax_id,
                c.email,
                c.phone,
                c.address,
                c.district,
                c.city,
                c.region,
                c.country,
                c.latitude,
                c.longitude,
                c.geocode_status,
                c.geocode_source,
                c.geocoded_at,
                COALESCE(o.open_opportunities, 0) AS open_opportunities,
                COALESCE(o.open_pipeline_value, 0) AS open_pipeline_value,
                COALESCE(o.weighted_pipeline_value, 0) AS weighted_pipeline_value,
                COALESCE(o.hot_opportunities, 0) AS hot_opportunities,
                o.next_action_due,
                COALESCE(f.last_purchase_date, ac.last_purchase) AS last_purchase_date,
                COALESCE(f.total_net_sales, ac.total_net_sales, 0) AS total_net_sales,
                COALESCE(f.sales_last_12m, ac.sales_last_12m, 0) AS sales_last_12m,
                COALESCE(ac.average_ticket, 0) AS average_ticket,
                COALESCE(f.invoice_count, ac.invoice_count, 0) AS invoice_count,
                COALESCE(f.total_documents, ac.document_count, 0) AS total_documents,
                ac.customer_status,
                ac.days_since_last_purchase,
                COALESCE(qs.quote_count, 0) AS quote_count,
                COALESCE(qs.paid_quotes, 0) AS paid_quotes,
                COALESCE(qs.invoiced_quotes, 0) AS invoiced_quotes,
                COALESCE(qs.accepted_quotes, 0) AS accepted_quotes,
                qs.last_quote_at,
                qs.quote_statuses
            FROM crm_customers c
            LEFT JOIN (
                SELECT
                    customer_id,
                    COUNT(*) AS open_opportunities,
                    SUM(COALESCE(expected_value, 0)) AS open_pipeline_value,
                    SUM(COALESCE(expected_value, 0) * (COALESCE(probability, 0) / 100)) AS weighted_pipeline_value,
                    SUM(CASE WHEN temperature = 'caliente' OR priority = 'alta' THEN 1 ELSE 0 END) AS hot_opportunities,
                    MIN(next_action_date) AS next_action_due
                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 analytics_customers ac ON REPLACE(REPLACE(UPPER(ac.receiver_tax_id_code),'.',''),'-','') = REPLACE(REPLACE(UPPER(COALESCE(NULLIF(c.export_tax_id_code,''),c.customer_tax_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,
                    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
                    ) AS sales_last_12m,
                    SUM(CASE WHEN fd.document_type_taxbureau IN (33, 34, 39, 110) THEN 1 ELSE 0 END) AS invoice_count,
                    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
            LEFT JOIN (
                SELECT
                    customer_id,
                    COUNT(*) AS quote_count,
                    SUM(CASE WHEN status = 'Pagada' THEN 1 ELSE 0 END) AS paid_quotes,
                    SUM(CASE WHEN status IN ('Facturada', 'Esperando pago', 'Pagada') OR COALESCE(invoice_number, '') <> '' THEN 1 ELSE 0 END) AS invoiced_quotes,
                    SUM(CASE WHEN status = 'Aceptada' THEN 1 ELSE 0 END) AS accepted_quotes,
                    MAX(created_at) AS last_quote_at,
                    GROUP_CONCAT(DISTINCT status ORDER BY status SEPARATOR ', ') AS quote_statuses
                FROM crm_quotes
                WHERE deleted_at IS NULL
                GROUP BY customer_id
            ) qs ON qs.customer_id = c.id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY
                CASE
                    WHEN COALESCE(f.sales_last_12m, ac.sales_last_12m, 0) > 0 THEN 0
                    WHEN COALESCE(o.weighted_pipeline_value, 0) > 0 THEN 1
                    WHEN COALESCE(qs.quote_count, 0) > 0 THEN 2
                    ELSE 3
                END,
                COALESCE(f.sales_last_12m, ac.sales_last_12m, 0) DESC,
                COALESCE(o.weighted_pipeline_value, 0) DESC,
                last_purchase_date DESC,
                qs.last_quote_at DESC,
                c.customer_name ASC
        ";

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

    public function getFilterOptions()
    {
        $defaultStatuses = array('Borrador', 'Enviada', 'Aceptada', 'Facturada', 'Esperando pago', 'Pagada', 'Rechazada', 'Vencida', 'Anulada');
        $statuses = array();

        $stmt = $this->pdo->query("SELECT DISTINCT status FROM crm_quotes WHERE deleted_at IS NULL AND status IS NOT NULL AND status <> '' ORDER BY status ASC");
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $status) {
            $statuses[(string)$status] = (string)$status;
        }

        foreach ($defaultStatuses as $status) {
            $statuses[$status] = $status;
        }

        return array(
            'segments' => array(
                'purchased' => 'Con compra',
                'invoiced' => 'Con factura',
                'quoted_only' => 'Sólo cotizaron',
            ),
            'quote_statuses' => array_values($statuses),
        );
    }

    public function buildSummary(array $customers)
    {
        $summary = array(
            'total' => count($customers),
            'geocoded' => 0,
            'no_coordinates' => 0,
            'countries' => array(),
            'regions' => array(),
            'segments' => array(
                'Con compra' => 0,
                'Con factura' => 0,
                'Sólo cotizaron' => 0,
                'Sin cotizaciones' => 0,
            ),
            'quote_statuses' => array(),
            'open_opportunities' => 0,
            'open_pipeline_value' => 0.0,
            'weighted_pipeline_value' => 0.0,
            'total_net_sales' => 0.0,
            'sales_last_12m' => 0.0,
            'at_risk_customers' => 0,
        );

        foreach ($customers as $customer) {
            $hasCoordinates = $this->hasCoordinates($customer);
            if ($hasCoordinates) {
                $summary['geocoded']++;
            } else {
                $summary['no_coordinates']++;
            }

            $country = trim((string)($customer['country'] ?? 'Sin país')) ?: 'Sin país';
            $region = trim((string)($customer['region'] ?? 'Sin región')) ?: 'Sin región';
            if (!isset($summary['countries'][$country])) $summary['countries'][$country] = 0;
            if (!isset($summary['regions'][$region])) $summary['regions'][$region] = 0;
            $summary['countries'][$country]++;
            $summary['regions'][$region]++;

            foreach ($this->segmentLabels($customer) as $label) {
                if (!isset($summary['segments'][$label])) $summary['segments'][$label] = 0;
                $summary['segments'][$label]++;
            }

            $statuses = array_filter(array_map('trim', explode(',', (string)($customer['quote_statuses'] ?? ''))));
            foreach ($statuses as $status) {
                if (!isset($summary['quote_statuses'][$status])) $summary['quote_statuses'][$status] = 0;
                $summary['quote_statuses'][$status]++;
            }

            if ((int)($customer['quote_count'] ?? 0) === 0) {
                $summary['segments']['Sin cotizaciones']++;
            }

            $status = $this->customerStatus($customer);
            if (in_array($status, array('en_riesgo', 'dormido', 'perdido'), true)) {
                $summary['at_risk_customers']++;
            }

            $summary['open_opportunities'] += (int)($customer['open_opportunities'] ?? 0);
            $summary['open_pipeline_value'] += (float)($customer['open_pipeline_value'] ?? 0);
            $summary['weighted_pipeline_value'] += (float)($customer['weighted_pipeline_value'] ?? 0);
            $summary['total_net_sales'] += (float)($customer['total_net_sales'] ?? 0);
            $summary['sales_last_12m'] += (float)($customer['sales_last_12m'] ?? 0);
        }

        arsort($summary['countries']);
        arsort($summary['regions']);
        arsort($summary['segments']);
        arsort($summary['quote_statuses']);

        return $summary;
    }

    public function buildMapPoints(array $customers)
    {
        $points = array();
        foreach ($customers as $customer) {
            if (!$this->hasCoordinates($customer)) continue;

            $displayName = trim((string)($customer['commercial_name'] ?? ''));
            if ($displayName === '') $displayName = trim((string)($customer['customer_name'] ?? 'Cliente'));

            $status = $this->customerStatus($customer);
            $segmentLabels = $this->segmentLabels($customer);

            $points[] = array(
                'id' => (int)$customer['id'],
                'name' => $displayName,
                'legal_name' => trim((string)($customer['legal_name'] ?? '')),
                'tax_id' => trim((string)($customer['customer_tax_id'] ?? '')),
                'lat' => (float)$customer['latitude'],
                'lng' => (float)$customer['longitude'],
                'address' => trim((string)($customer['address'] ?? '')),
                'district' => trim((string)($customer['district'] ?? '')),
                'city' => trim((string)($customer['city'] ?? '')),
                'region' => trim((string)($customer['region'] ?? '')),
                'country' => trim((string)($customer['country'] ?? '')),
                'open_opportunities' => (int)($customer['open_opportunities'] ?? 0),
                'open_pipeline_value' => (float)($customer['open_pipeline_value'] ?? 0),
                'weighted_pipeline_value' => (float)($customer['weighted_pipeline_value'] ?? 0),
                'hot_opportunities' => (int)($customer['hot_opportunities'] ?? 0),
                'next_action_due' => trim((string)($customer['next_action_due'] ?? '')),
                'quote_count' => (int)($customer['quote_count'] ?? 0),
                'paid_quotes' => (int)($customer['paid_quotes'] ?? 0),
                'accepted_quotes' => (int)($customer['accepted_quotes'] ?? 0),
                'invoiced_quotes' => (int)($customer['invoiced_quotes'] ?? 0),
                'invoice_count' => (int)($customer['invoice_count'] ?? 0),
                'total_documents' => (int)($customer['total_documents'] ?? 0),
                'total_net_sales' => (float)($customer['total_net_sales'] ?? 0),
                'sales_last_12m' => (float)($customer['sales_last_12m'] ?? 0),
                'average_ticket' => (float)($customer['average_ticket'] ?? 0),
                'last_purchase_date' => trim((string)($customer['last_purchase_date'] ?? '')),
                'days_since_last_purchase' => $customer['days_since_last_purchase'] === null ? null : (int)$customer['days_since_last_purchase'],
                'customer_status' => $status,
                'customer_status_label' => $this->customerStatusLabel($status),
                'segment' => implode(' / ', $segmentLabels),
                'segment_key' => $this->segmentKey($customer),
                'quote_statuses' => trim((string)($customer['quote_statuses'] ?? '')),
                'url' => '/modules/customers/view.php?id=' . (int)$customer['id'],
            );
        }
        return $points;
    }

    private function segmentLabels(array $customer)
    {
        $labels = array();
        $hasPurchase = (float)($customer['total_net_sales'] ?? 0) > 0 || (int)($customer['paid_quotes'] ?? 0) > 0;
        $hasInvoice = (int)($customer['invoice_count'] ?? 0) > 0 || (int)($customer['invoiced_quotes'] ?? 0) > 0;
        $hasQuote = (int)($customer['quote_count'] ?? 0) > 0;

        if ($hasPurchase) $labels[] = 'Con compra';
        if ($hasInvoice) $labels[] = 'Con factura';
        if ($hasQuote && !$hasPurchase && !$hasInvoice) $labels[] = 'Sólo cotizaron';

        return $labels;
    }

    private function segmentKey(array $customer)
    {
        $hasPurchase = (float)($customer['total_net_sales'] ?? 0) > 0 || (int)($customer['paid_quotes'] ?? 0) > 0;
        $hasQuote = (int)($customer['quote_count'] ?? 0) > 0;
        $hasPipeline = (float)($customer['weighted_pipeline_value'] ?? 0) > 0 || (int)($customer['open_opportunities'] ?? 0) > 0;

        if ($hasPurchase) return 'customer';
        if ($hasPipeline) return 'pipeline';
        if ($hasQuote) return 'quoted_only';
        return 'prospect';
    }

    private function customerStatus(array $customer)
    {
        $status = trim((string)($customer['customer_status'] ?? ''));
        if ($status !== '') return $status;

        $sales = (float)($customer['total_net_sales'] ?? 0);
        if ($sales <= 0) {
            return (int)($customer['quote_count'] ?? 0) > 0 ? 'nuevo' : 'sin_compra';
        }

        $days = $customer['days_since_last_purchase'] ?? null;
        if ($days === null || $days === '') return 'activo';
        $days = (int)$days;

        if ($days <= 60) return 'activo';
        if ($days <= 90) return 'en_riesgo';
        if ($days <= 180) return 'dormido';
        return 'perdido';
    }

    private function customerStatusLabel($status)
    {
        $labels = array(
            'nuevo' => 'Nuevo',
            'activo' => 'Activo',
            'en_riesgo' => 'En riesgo',
            'dormido' => 'Dormido',
            'perdido' => 'Perdido',
            'sin_compra' => 'Sin compra',
        );
        return $labels[$status] ?? ucfirst(str_replace('_', ' ', (string)$status));
    }

    private function hasCoordinates(array $customer)
    {
        return $customer['latitude'] !== null
            && $customer['longitude'] !== null
            && is_numeric($customer['latitude'])
            && is_numeric($customer['longitude']);
    }
}
