<?php
require_once __DIR__ . '/../core/cron_guard.php';
icylab_require_cron_access();
require_once __DIR__ . '/../core/config.php';
require_once __DIR__ . '/../core/database.php';
require_once __DIR__ . '/../services/analytics/AnalyticsEngine.php';

if (PHP_SAPI !== 'cli') header('Content-Type: text/plain; charset=utf-8');

try {
    $pdo = db();
    (new AnalyticsEngine($pdo))->rebuild();

    $summary = $pdo->query("SELECT
        COUNT(*) AS foreign_documents_total,
        SUM(CASE WHEN COALESCE(exchange_rate_value,0)>0 AND net_amount_clp IS NOT NULL THEN 1 ELSE 0 END) AS foreign_documents_fx_ready,
        SUM(CASE WHEN crm_customer_id IS NOT NULL THEN 1 ELSE 0 END) AS foreign_documents_linked
      FROM facto_documents
      WHERE received_issued_flag=1
        AND document_status=1
        AND UPPER(COALESCE(NULLIF(currency_code,''),'CLP')) IN ('USD','EUR')")->fetch(PDO::FETCH_ASSOC);

    $customers = $pdo->query("SELECT
        c.id,
        c.customer_name,
        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 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 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_clp
      FROM crm_customers c
      LEFT JOIN facto_documents fd ON fd.crm_customer_id=c.id
        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)
      WHERE c.deleted_at IS NULL
        AND UPPER(COALESCE(NULLIF(c.country,''),'CHILE')) <> 'CHILE'
      GROUP BY c.id,c.customer_name
      HAVING sales_last_12m_clp > 0
      ORDER BY sales_last_12m_clp DESC")->fetchAll(PDO::FETCH_ASSOC);

    echo "CUSTOMER SALES REBUILD v8.6.4\n";
    echo 'foreign_documents_total: ' . (int)($summary['foreign_documents_total'] ?? 0) . "\n";
    echo 'foreign_documents_fx_ready: ' . (int)($summary['foreign_documents_fx_ready'] ?? 0) . "\n";
    echo 'foreign_documents_linked: ' . (int)($summary['foreign_documents_linked'] ?? 0) . "\n";
    echo 'international_customers_with_sales_12m: ' . count($customers) . "\n";
    foreach ($customers as $row) {
        echo '- ' . $row['customer_name'] . ': ' . number_format((float)$row['sales_last_12m_clp'], 0, ',', '.') . " CLP\n";
    }
    echo "RESULT: PASS\n";
} catch (Throwable $e) {
    if (PHP_SAPI !== 'cli') http_response_code(500);
    echo "RESULT: FAIL\n";
    echo 'error: ' . $e->getMessage() . "\n";
    exit(1);
}
