<?php

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

class FactoDashboardService
{
    private $pdo;

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

    public function getKpis()
    {
        $period = date('Y-m');
        $monthlyTarget = 16000000;

        $stmt = $this->pdo->prepare("
            SELECT
                net_sales,
                invoice_count,
                customer_count,
                average_ticket
            FROM analytics_monthly_sales
            WHERE period = ?
            LIMIT 1
        ");

        $stmt->execute(array($period));
        $row = $stmt->fetch();

        $salesMonth = $row ? (float) $row['net_sales'] : 0;
        $invoiceCount = $row ? (int) $row['invoice_count'] : 0;
        $customerCount = $row ? (int) $row['customer_count'] : 0;
        $averageTicket = $row ? (float) $row['average_ticket'] : 0;

        $progress = $monthlyTarget > 0 ? round(($salesMonth / $monthlyTarget) * 100) : 0;

        return array(
            'sales_month' => $salesMonth,
            'monthly_target' => $monthlyTarget,
            'progress' => $progress,
            'documents_month' => $invoiceCount,
            'customers_month' => $customerCount,
            'average_ticket' => $averageTicket
        );
    }

    public function getTopCustomers($limit)
    {
        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 5;
        }

        $sql = "
            SELECT
                receiver_legal_name,
                receiver_tax_id_code,
                total_net_sales AS total_sales,
                invoice_count AS document_count,
                last_purchase,
                customer_status
            FROM analytics_customers
            WHERE total_net_sales > 0
            ORDER BY total_net_sales DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function getLatestDocuments($limit)
    {
        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 8;
        }

        $sql = "
            SELECT
                issue_date,
                document_number,
                receiver_legal_name,
                document_type_taxbureau,
                net_amount,
                total_amount,

                CASE
                    WHEN document_type_taxbureau IN (33,34,39) THEN 'Factura/Boleta'
                    WHEN document_type_taxbureau = 56 THEN 'Nota de débito'
                    WHEN document_type_taxbureau = 61 THEN 'Nota de crédito'
                    WHEN document_type_taxbureau = 52 THEN 'Guía'
                    ELSE 'Otro'
                END AS document_label,

                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_effect

            FROM facto_documents

            WHERE received_issued_flag = 1
              AND document_status = 1
              AND document_type_taxbureau IN (33,34,39,52,56,61)

            ORDER BY issue_date DESC, document_id DESC

            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function getMonthlySales($limit)
    {
        $limit = (int) $limit;

        if ($limit <= 0) {
            $limit = 12;
        }

        $sql = "
            SELECT
                period,
                net_sales,
                gross_sales_net,
                credit_notes_net,
                debit_notes_net,
                invoice_count,
                customer_count,
                average_ticket
            FROM analytics_monthly_sales
            ORDER BY period DESC
            LIMIT " . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();

        return array_reverse($stmt->fetchAll());
    }
    public function getRecentSales($days)
    {
        $days = (int) $days;
    
        if (!in_array($days, array(30, 60, 90))) {
            $days = 30;
        }
    
        $sql = "
            SELECT
                issue_date,
                document_number,
                receiver_legal_name,
                document_type_taxbureau,
                net_amount,
    
                CASE
                    WHEN document_type_taxbureau IN (33,34,39) THEN 'Factura/Boleta'
                    WHEN document_type_taxbureau = 56 THEN 'Nota de debito'
                    WHEN document_type_taxbureau = 61 THEN 'Nota de credito'
                    ELSE 'Otro'
                END AS document_label,
    
                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_effect
    
            FROM facto_documents
    
            WHERE received_issued_flag = 1
              AND document_status = 1
              AND document_type_taxbureau IN (33,34,39,56,61)
              AND issue_date >= DATE_SUB(CURDATE(), INTERVAL " . $days . " DAY)
    
            ORDER BY issue_date DESC, document_id DESC
        ";
    
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
    
        return $stmt->fetchAll();
    }
    public function getRollingKpis($days)
    {
        $days = (int) $days;
    
        if (!in_array($days, array(30, 60, 90))) {
            $days = 30;
        }
    
        $sql = "
            SELECT
                COALESCE(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
                ), 0) AS sales,
    
                COUNT(CASE
                    WHEN document_type_taxbureau IN (33,34,39,56) THEN 1
                    ELSE NULL
                END) AS documents,
    
                COUNT(DISTINCT CASE
                    WHEN document_type_taxbureau IN (33,34,39,56,61) THEN receiver_tax_id_code
                    ELSE NULL
                END) AS customers
    
            FROM facto_documents
    
            WHERE received_issued_flag = 1
              AND document_status = 1
              AND net_amount > 0
              AND document_type_taxbureau IN (33,34,39,56,61)
              AND issue_date >= DATE_SUB(CURDATE(), INTERVAL " . $days . " DAY)
        ";
    
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
    
        $row = $stmt->fetch();
    
        $sales = isset($row['sales']) ? (float) $row['sales'] : 0;
        $documents = isset($row['documents']) ? (int) $row['documents'] : 0;
        $customers = isset($row['customers']) ? (int) $row['customers'] : 0;
        $averageTicket = $documents > 0 ? round($sales / $documents) : 0;
    
        return array(
            'sales' => $sales,
            'documents' => $documents,
            'customers' => $customers,
            'average_ticket' => $averageTicket
        );
    }
    public function getSalesQuotesChart($fromDate, $toDate, $group)
    {
        if (!in_array($group, array('day', 'week', 'month'))) {
            $group = 'day';
        }
    
        if ($group === 'week') {
            $selectPeriod = "YEARWEEK(issue_date, 1) AS period_key, MIN(issue_date) AS label_date";
            $groupBy = "YEARWEEK(issue_date, 1)";
        } elseif ($group === 'month') {
            $selectPeriod = "DATE_FORMAT(issue_date, '%Y-%m') AS period_key, MIN(issue_date) AS label_date";
            $groupBy = "DATE_FORMAT(issue_date, '%Y-%m')";
        } else {
            $selectPeriod = "issue_date AS period_key, issue_date AS label_date";
            $groupBy = "issue_date";
        }
    
        $salesSql = "
            SELECT
                " . $selectPeriod . ",
                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 sales
            FROM facto_documents
            WHERE received_issued_flag = 1
              AND document_status = 1
              AND document_type_taxbureau IN (33,34,39,56,61)
              AND issue_date BETWEEN ? AND ?
            GROUP BY " . $groupBy . "
            ORDER BY label_date ASC
        ";
    
        $stmt = $this->pdo->prepare($salesSql);
        $stmt->execute(array($fromDate, $toDate));
        $salesRows = $stmt->fetchAll();
    
        $quoteSql = "
            SELECT
                " . $selectPeriod . ",
                COUNT(*) AS quotes
            FROM facto_quotes
            WHERE issue_date BETWEEN ? AND ?
            GROUP BY " . $groupBy . "
            ORDER BY label_date ASC
        ";
    
        $stmt = $this->pdo->prepare($quoteSql);
        $stmt->execute(array($fromDate, $toDate));
        $quoteRows = $stmt->fetchAll();
    
        $data = array();
    
        foreach ($salesRows as $row) {
            $key = $row['period_key'];
    
            if (!isset($data[$key])) {
                $data[$key] = array(
                    'label_date' => $row['label_date'],
                    'sales' => 0,
                    'quotes' => 0
                );
            }
    
            $data[$key]['sales'] = (float) $row['sales'];
        }
    
        foreach ($quoteRows as $row) {
            $key = $row['period_key'];
    
            if (!isset($data[$key])) {
                $data[$key] = array(
                    'label_date' => $row['label_date'],
                    'sales' => 0,
                    'quotes' => 0
                );
            }
    
            $data[$key]['quotes'] = (int) $row['quotes'];
        }
    
        uasort($data, function ($a, $b) {
            return strtotime($a['label_date']) - strtotime($b['label_date']);
        });
    
        $labels = array();
        $sales = array();
        $quotes = array();
    
        foreach ($data as $row) {
            if ($group === 'month') {
                $labels[] = date('m/Y', strtotime($row['label_date']));
            } elseif ($group === 'week') {
                $labels[] = 'Sem ' . date('W', strtotime($row['label_date']));
            } else {
                $labels[] = date('d/m', strtotime($row['label_date']));
            }
    
            $sales[] = $row['sales'];
            $quotes[] = $row['quotes'];
        }
    
        return array(
            'labels' => $labels,
            'sales' => $sales,
            'quotes' => $quotes,
            'group' => $group
        );
    }
}