<?php
require_once __DIR__ . '/../../core/config.php';
require_once __DIR__ . '/../../core/session.php';
require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/../../core/permissions.php';

require_login();
require_permission('module.crm');

$stageLabels = array(
    'nuevo' => 'Nuevo',
    'contactado' => 'Contactado',
    'cotizando' => 'Cotizando',
    'negociacion' => 'Negociación',
    'ganada' => 'Ganada',
    'perdida' => 'Perdida',
    'facturada' => 'Facturada',
);

$stmt = db()->query("
    SELECT
        o.id,
        o.title,
        o.stage,
        o.expected_value,
        o.probability,
        o.owner_name,
        o.source,
        o.expected_close_date,
        o.next_action,
        o.next_action_date,
        o.payment_status,
        o.payment_due_date,
        COALESCE(
            fd.document_number,
            (
                SELECT d.document_number
                FROM crm_documents d
                WHERE d.opportunity_id = o.id
                  AND d.document_type IN ('factura', 'nota_credito', 'nota_debito')
                ORDER BY d.document_date DESC, d.id DESC
                LIMIT 1
            )
        ) AS invoice_number,
        o.updated_at,
        COALESCE(NULLIF(c.commercial_name, ''), NULLIF(c.customer_name, ''), c.legal_name, o.customer_name) AS commercial_name,
        COALESCE(NULLIF(c.legal_name, ''), c.customer_name, o.customer_name) AS legal_name,
        COALESCE(NULLIF(c.customer_tax_id, ''), o.customer_tax_id) AS customer_tax_id,
        c.email,
        c.phone,
        c.city,
        c.region
    FROM crm_opportunities o
    LEFT JOIN crm_customers c
      ON c.id = o.customer_id
     AND c.deleted_at IS NULL
    LEFT JOIN facto_documents fd
      ON fd.environment = 'production'
     AND BINARY fd.document_id = BINARY o.facto_document_id
    WHERE o.deleted_at IS NULL
      AND o.archived_at IS NULL
      AND o.stage IN ('nuevo','contactado','cotizando','negociacion','ganada','perdida','facturada')
    ORDER BY FIELD(o.stage,'nuevo','contactado','cotizando','negociacion','ganada','facturada','perdida'),
             o.expected_close_date ASC,
             o.updated_at DESC
");

$safeCell = static function ($value) {
    $value = trim((string)$value);
    if ($value !== '' && preg_match('/^[=+\-@]/', $value)) {
        return "'" . $value;
    }
    return $value;
};

$fileName = 'embudo_comercial_' . date('Y-m-d_His') . '.csv';
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $fileName . '"');
header('Cache-Control: private, no-store, max-age=0');
header('X-Content-Type-Options: nosniff');

$output = fopen('php://output', 'wb');
fwrite($output, "\xEF\xBB\xBF");
fputcsv($output, array(
    'ID',
    'Oportunidad',
    'Etapa',
    'Nombre comercial',
    'Razón social',
    'RUT',
    'Valor esperado',
    'Probabilidad',
    'Valor ponderado',
    'Responsable',
    'Origen',
    'Cierre estimado',
    'Próxima acción',
    'Fecha próxima acción',
    'Estado cobranza',
    'Vencimiento pago',
    'Folio factura',
    'Email',
    'Teléfono',
    'Ciudad',
    'Región',
    'Última actualización',
), ';', '"', '\\');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $value = (float)($row['expected_value'] ?? 0);
    $probability = max(0, min(100, (int)($row['probability'] ?? 0)));
    fputcsv($output, array_map($safeCell, array(
        $row['id'],
        $row['title'],
        $stageLabels[$row['stage']] ?? $row['stage'],
        $row['commercial_name'],
        $row['legal_name'],
        $row['customer_tax_id'],
        number_format($value, 0, ',', ''),
        $probability . '%',
        number_format($value * $probability / 100, 0, ',', ''),
        $row['owner_name'],
        $row['source'],
        $row['expected_close_date'],
        $row['next_action'],
        $row['next_action_date'],
        $row['payment_status'],
        $row['payment_due_date'],
        $row['invoice_number'],
        $row['email'],
        $row['phone'],
        $row['city'],
        $row['region'],
        $row['updated_at'],
    )), ';', '"', '\\');
}

fclose($output);
