<?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.customers');

$stmt = db()->query("
    SELECT
        c.id,
        COALESCE(NULLIF(c.commercial_name, ''), NULLIF(c.customer_name, ''), c.legal_name) AS commercial_name,
        COALESCE(NULLIF(c.legal_name, ''), c.customer_name) AS legal_name,
        c.customer_tax_id,
        c.business_activity,
        c.contact_name,
        c.email,
        c.phone,
        c.address,
        c.district,
        c.city,
        c.region,
        c.country,
        c.website,
        c.payment_terms,
        c.owner_name,
        c.source,
        c.restock_days,
        c.next_restock_date,
        COALESCE(op.open_opportunities, 0) AS open_opportunities,
        sales.last_purchase_date,
        COALESCE(sales.total_net_sales, 0) AS total_net_sales,
        c.updated_at
    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
    ) op ON op.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, fd.net_amount, 0)
                    WHEN fd.document_type_taxbureau IN (61,112)
                        THEN -COALESCE(fd.net_amount_clp, fd.net_amount, 0)
                    ELSE 0
                END
            ) AS total_net_sales
        FROM facto_documents fd
        WHERE fd.environment = 'production'
          AND fd.received_issued_flag = 1
          AND fd.document_status = 1
          AND fd.crm_customer_id IS NOT NULL
        GROUP BY fd.crm_customer_id
    ) sales ON sales.customer_id = c.id
    WHERE c.deleted_at IS NULL
      AND c.counterparty_type IN ('customer', 'both')
    ORDER BY commercial_name ASC, legal_name ASC
");

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

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="clientes_' . date('Y-m-d_His') . '.csv"');
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',
    'Nombre comercial',
    'Razón social',
    'RUT',
    'Giro',
    'Contacto',
    'Email',
    'Teléfono',
    'Dirección',
    'Comuna',
    'Ciudad',
    'Región',
    'País',
    'Sitio web',
    'Condición de pago',
    'Responsable',
    'Origen',
    'Ciclo reposición (días)',
    'Próxima reposición',
    'Oportunidades abiertas',
    'Última compra',
    'Ventas netas acumuladas',
    'Última actualización',
), ';', '"', '\\');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($output, array_map($safeCell, array(
        $row['id'],
        $row['commercial_name'],
        $row['legal_name'],
        $row['customer_tax_id'],
        $row['business_activity'],
        $row['contact_name'],
        $row['email'],
        $row['phone'],
        $row['address'],
        $row['district'],
        $row['city'],
        $row['region'],
        $row['country'],
        $row['website'],
        $row['payment_terms'],
        $row['owner_name'],
        $row['source'],
        $row['restock_days'],
        $row['next_restock_date'],
        $row['open_opportunities'],
        $row['last_purchase_date'],
        number_format((float)$row['total_net_sales'], 0, ',', ''),
        $row['updated_at'],
    )), ';', '"', '\\');
}

fclose($output);

