<?php

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

class AnalyticsEngine
{
    private $pdo;

    public function __construct()
    {
        $this->pdo = db();
    }

    public function rebuild()
    {
        $this->rebuildMonthlySales();
        $this->rebuildCustomers();

        $this->log('success', 'Analytics Engine reconstruido correctamente.');
    }

    public function rebuildMonthlySales()
    {
        $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) THEN net_amount
                    ELSE 0
                END) AS gross_sales_net,

                SUM(CASE
                    WHEN document_type_taxbureau = 61 THEN net_amount
                    ELSE 0
                END) AS credit_notes_net,

                SUM(CASE
                    WHEN document_type_taxbureau = 56 THEN net_amount
                    ELSE 0
                END) AS debit_notes_net,

                SUM(CASE
                    WHEN document_type_taxbureau IN (33,34,39,56) THEN net_amount
                    WHEN document_type_taxbureau = 61 THEN -net_amount
                    ELSE 0
                END) AS net_sales,

                COUNT(*) AS document_count,

                SUM(CASE
                    WHEN document_type_taxbureau IN (33,34,39) THEN 1
                    ELSE 0
                END) AS invoice_count,

                SUM(CASE
                    WHEN document_type_taxbureau = 61 THEN 1
                    ELSE 0
                END) AS credit_note_count,

                SUM(CASE
                    WHEN document_type_taxbureau = 56 THEN 1
                    ELSE 0
                END) AS debit_note_count,

                COUNT(DISTINCT receiver_tax_id_code) AS customer_count

            FROM facto_documents
            WHERE received_issued_flag = 1
              AND document_status = 1
              AND issue_date IS NOT NULL
              AND document_type_taxbureau IN (33,34,39,56,61)
            GROUP BY YEAR(issue_date), MONTH(issue_date), DATE_FORMAT(issue_date, '%Y-%m')
            ORDER BY period ASC
        ";

        $rows = $this->pdo->query($sql)->fetchAll();

        foreach ($rows as $row) {
            $invoiceCount = (int) $row['invoice_count'];
            $netSales = (float) $row['net_sales'];
            $averageTicket = $invoiceCount > 0 ? round($netSales / $invoiceCount) : 0;

            $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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    gross_sales_net = VALUES(gross_sales_net),
                    credit_notes_net = VALUES(credit_notes_net),
                    debit_notes_net = VALUES(debit_notes_net),
                    net_sales = VALUES(net_sales),
                    document_count = VALUES(document_count),
                    invoice_count = VALUES(invoice_count),
                    credit_note_count = VALUES(credit_note_count),
                    debit_note_count = VALUES(debit_note_count),
                    customer_count = VALUES(customer_count),
                    average_ticket = VALUES(average_ticket),
                    updated_at = CURRENT_TIMESTAMP
            ");

            $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'],
                (float) $row['net_sales'],
                (int) $row['document_count'],
                (int) $row['invoice_count'],
                (int) $row['credit_note_count'],
                (int) $row['debit_note_count'],
                (int) $row['customer_count'],
                $averageTicket
            ));
        }
    }

    public function rebuildCustomers()
    {
        $sql = "
            SELECT
                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) THEN net_amount
                    WHEN document_type_taxbureau = 61 THEN -net_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) THEN net_amount
                    WHEN issue_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                     AND document_type_taxbureau = 61 THEN -net_amount
                    ELSE 0
                END) AS sales_last_12m,

                COUNT(*) AS document_count,

                SUM(CASE
                    WHEN document_type_taxbureau IN (33,34,39) THEN 1
                    ELSE 0
                END) AS invoice_count,

                SUM(CASE
                    WHEN document_type_taxbureau = 61 THEN 1
                    ELSE 0
                END) AS credit_note_count

            FROM facto_documents
            WHERE received_issued_flag = 1
              AND document_status = 1
              AND receiver_tax_id_code IS NOT NULL
              AND receiver_legal_name IS NOT NULL
              AND document_type_taxbureau IN (33,34,39,56,61)
            GROUP BY receiver_tax_id_code
        ";

        $rows = $this->pdo->query($sql)->fetchAll();

        foreach ($rows as $row) {
            $invoiceCount = (int) $row['invoice_count'];
            $totalNetSales = (float) $row['total_net_sales'];
            $averageTicket = $invoiceCount > 0 ? round($totalNetSales / $invoiceCount) : 0;

            $daysSinceLastPurchase = null;
            if ($row['last_purchase']) {
                $daysSinceLastPurchase = floor((time() - strtotime($row['last_purchase'])) / 86400);
            }

            $status = $this->classifyCustomer($daysSinceLastPurchase, $row['first_purchase']);

            $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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    receiver_legal_name = VALUES(receiver_legal_name),
                    first_purchase = VALUES(first_purchase),
                    last_purchase = VALUES(last_purchase),
                    total_net_sales = VALUES(total_net_sales),
                    sales_last_12m = VALUES(sales_last_12m),
                    document_count = VALUES(document_count),
                    invoice_count = VALUES(invoice_count),
                    credit_note_count = VALUES(credit_note_count),
                    average_ticket = VALUES(average_ticket),
                    customer_status = VALUES(customer_status),
                    days_since_last_purchase = VALUES(days_since_last_purchase),
                    updated_at = CURRENT_TIMESTAMP
            ");

            $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,
                $status,
                $daysSinceLastPurchase
            ));
        }
    }

    private function classifyCustomer($daysSinceLastPurchase, $firstPurchase)
    {
        if ($daysSinceLastPurchase === null) {
            return 'perdido';
        }

        $daysSinceFirstPurchase = $firstPurchase
            ? 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));
    }
}