<?php

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

class AnalyticsEngine
{
    private $pdo;

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

    public function rebuild()
    {
        $this->rebuildMonthlySales();
        $this->rebuildCustomers();
        $this->log('success', 'Analytics Engine reconstruido correctamente con montos normalizados a CLP.');
    }

    public function rebuildMonthlySales()
    {
        $amount = $this->amountClpSql();
        $customerKey = $this->customerKeySql();
        $sql = "
            SELECT
                YEAR(issue_date) AS year,
                MONTH(issue_date) AS month,
                DATE_FORMAT(issue_date, '%Y-%m') AS period,
                SUM(CASE WHEN document_type_taxbureau IN (33,34,39,110) THEN {$amount} ELSE 0 END) AS gross_sales_net,
                SUM(CASE WHEN document_type_taxbureau IN (61,112) THEN {$amount} ELSE 0 END) AS credit_notes_net,
                SUM(CASE WHEN document_type_taxbureau IN (56,111) THEN {$amount} ELSE 0 END) AS debit_notes_net,
                SUM(CASE
                    WHEN document_type_taxbureau IN (33,34,39,56,110,111) THEN {$amount}
                    WHEN document_type_taxbureau IN (61,112) THEN -{$amount}
                    ELSE 0
                END) AS net_sales,
                COUNT(*) AS document_count,
                SUM(CASE WHEN document_type_taxbureau IN (33,34,39,110) THEN 1 ELSE 0 END) AS invoice_count,
                SUM(CASE WHEN document_type_taxbureau IN (61,112) THEN 1 ELSE 0 END) AS credit_note_count,
                SUM(CASE WHEN document_type_taxbureau IN (56,111) THEN 1 ELSE 0 END) AS debit_note_count,
                COUNT(DISTINCT {$customerKey}) AS customer_count
            FROM facto_documents
            WHERE environment = 'production'
              AND received_issued_flag = 1
              AND document_status = 1
              AND issue_date IS NOT NULL
              AND document_type_taxbureau IN (33,34,39,56,61,110,111,112)
            GROUP BY YEAR(issue_date), MONTH(issue_date), DATE_FORMAT(issue_date, '%Y-%m')
            ORDER BY period ASC
        ";
        $rows = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $this->pdo->beginTransaction();
        try {
            // La tabla es una proyección reconstruible: eliminar antes evita conservar
            // períodos antiguos o datos de sandbox que hayan quedado de versiones previas.
            $this->pdo->exec('DELETE FROM analytics_monthly_sales');
            $stmt = $this->pdo->prepare("
                    INSERT INTO analytics_monthly_sales
                    (year,month,period,gross_sales_net,credit_notes_net,debit_notes_net,net_sales,document_count,invoice_count,credit_note_count,debit_note_count,customer_count,average_ticket)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
                ");
            foreach ($rows as $row) {
                $invoiceCount = (int)$row['invoice_count'];
                $netSales = (float)$row['net_sales'];
                $averageTicket = $invoiceCount > 0 ? round($netSales / $invoiceCount) : 0;
                $stmt->execute(array(
                    (int)$row['year'],(int)$row['month'],$row['period'],(float)$row['gross_sales_net'],(float)$row['credit_notes_net'],
                    (float)$row['debit_notes_net'],$netSales,(int)$row['document_count'],$invoiceCount,(int)$row['credit_note_count'],
                    (int)$row['debit_note_count'],(int)$row['customer_count'],$averageTicket
                ));
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    public function rebuildCustomers()
    {
        $amount = $this->amountClpSql();
        $customerKey = $this->customerKeySql();
        $sql = "
            SELECT
                {$customerKey} AS receiver_tax_id_code,
                MAX(receiver_legal_name) AS receiver_legal_name,
                MIN(issue_date) AS first_purchase,
                MAX(issue_date) AS last_purchase,
                SUM(CASE
                    WHEN document_type_taxbureau IN (33,34,39,56,110,111) THEN {$amount}
                    WHEN document_type_taxbureau IN (61,112) THEN -{$amount}
                    ELSE 0
                END) AS total_net_sales,
                SUM(CASE
                    WHEN issue_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND document_type_taxbureau IN (33,34,39,56,110,111) THEN {$amount}
                    WHEN issue_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND document_type_taxbureau IN (61,112) THEN -{$amount}
                    ELSE 0
                END) AS sales_last_12m,
                COUNT(*) AS document_count,
                SUM(CASE WHEN document_type_taxbureau IN (33,34,39,110) THEN 1 ELSE 0 END) AS invoice_count,
                SUM(CASE WHEN document_type_taxbureau IN (61,112) THEN 1 ELSE 0 END) AS credit_note_count
            FROM facto_documents
            WHERE environment = 'production'
              AND received_issued_flag = 1
              AND document_status = 1
              AND ({$customerKey}) IS NOT NULL
              AND receiver_legal_name IS NOT NULL
              AND document_type_taxbureau IN (33,34,39,56,61,110,111,112)
            GROUP BY {$customerKey}
        ";

        $rows = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $this->pdo->beginTransaction();
        try {
            $this->pdo->exec('DELETE FROM analytics_customers');
            $stmt = $this->pdo->prepare("
                INSERT INTO analytics_customers
                (receiver_tax_id_code,receiver_legal_name,first_purchase,last_purchase,total_net_sales,sales_last_12m,document_count,invoice_count,credit_note_count,average_ticket,customer_status,days_since_last_purchase)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
            ");
            foreach ($rows as $row) {
                $invoiceCount = (int)$row['invoice_count'];
                $totalNetSales = (float)$row['total_net_sales'];
                $averageTicket = $invoiceCount > 0 ? round($totalNetSales / $invoiceCount) : 0;
                $daysSinceLastPurchase = $row['last_purchase'] ? (int)floor((time() - strtotime($row['last_purchase'])) / 86400) : null;
                $stmt->execute(array(
                    $row['receiver_tax_id_code'],$row['receiver_legal_name'],$row['first_purchase'],$row['last_purchase'],$totalNetSales,
                    (float)$row['sales_last_12m'],(int)$row['document_count'],$invoiceCount,(int)$row['credit_note_count'],$averageTicket,
                    $this->classifyCustomer($daysSinceLastPurchase,$row['first_purchase']),$daysSinceLastPurchase
                ));
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    private function amountClpSql()
    {
        return "COALESCE(net_amount_clp, CASE
            WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))='CLP' THEN net_amount
            WHEN COALESCE(exchange_rate_value,0)>0 THEN ROUND(net_amount*exchange_rate_value,2)
            ELSE 0 END)";
    }

    private function customerKeySql()
    {
        return "COALESCE(
            NULLIF(REPLACE(REPLACE(UPPER(TRIM(receiver_tax_id_code)),'.',''),'-',''),''),
            CONCAT('CRM-',NULLIF(crm_customer_id,0)),
            NULLIF(UPPER(TRIM(receiver_legal_name)),'')
        )";
    }

    private function classifyCustomer($daysSinceLastPurchase, $firstPurchase)
    {
        if ($daysSinceLastPurchase === null) return 'perdido';
        $daysSinceFirstPurchase = $firstPurchase ? (int)floor((time() - strtotime($firstPurchase)) / 86400) : null;
        if ($daysSinceFirstPurchase !== null && $daysSinceFirstPurchase <= 30) return 'nuevo';
        if ($daysSinceLastPurchase <= 60) return 'activo';
        if ($daysSinceLastPurchase <= 90) return 'en_riesgo';
        if ($daysSinceLastPurchase <= 180) return 'dormido';
        return 'perdido';
    }

    private function log($status, $message)
    {
        $stmt = $this->pdo->prepare("INSERT INTO analytics_logs (process,status,message) VALUES ('analytics_engine',?,?)");
        $stmt->execute(array($status,$message));
    }
}
