<?php

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

class FactoDashboardService
{
    private $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $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(PDO::FETCH_ASSOC);

        $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 = max(1, (int)$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;
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getLatestDocuments($limit)
    {
        $limit = max(1, (int)$limit ?: 8);
        $netClp = $this->amountClpSql('d', 'net_amount', 'net_amount_clp');
        $totalClp = $this->amountClpSql('d', 'total_amount', 'total_amount_clp');

        $sql = "SELECT
                    d.issue_date,
                    d.document_number,
                    d.receiver_legal_name,
                    d.document_type_taxbureau,
                    d.currency_code,
                    d.exchange_rate_value,
                    d.exchange_rate_date,
                    d.exchange_rate_source,
                    d.exchange_rate_status,
                    d.net_amount AS net_amount_original,
                    d.total_amount AS total_amount_original,
                    " . $netClp . " AS net_amount,
                    " . $totalClp . " AS total_amount,
                    CASE
                        WHEN d.document_type_taxbureau IN (33,34,39) THEN 'Factura/Boleta'
                        WHEN d.document_type_taxbureau = 56 THEN 'Nota de débito'
                        WHEN d.document_type_taxbureau = 61 THEN 'Nota de crédito'
                        WHEN d.document_type_taxbureau = 52 THEN 'Guía'
                        WHEN d.document_type_taxbureau = 110 THEN 'Factura de exportación'
                        WHEN d.document_type_taxbureau = 111 THEN 'Nota de débito de exportación'
                        WHEN d.document_type_taxbureau = 112 THEN 'Nota de crédito de exportación'
                        ELSE 'Otro'
                    END AS document_label,
                    CASE
                        WHEN d.document_type_taxbureau IN (33,34,39,56,110,111) THEN " . $netClp . "
                        WHEN d.document_type_taxbureau IN (61,112) THEN -(" . $netClp . ")
                        ELSE 0
                    END AS net_sales_effect
                FROM facto_documents d
                WHERE d.received_issued_flag = 1
                  AND d.document_status = 1
                  AND d.document_type_taxbureau IN (33,34,39,52,56,61,110,111,112)
                ORDER BY d.issue_date DESC, d.document_id DESC
                LIMIT " . $limit;
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getMonthlySales($limit)
    {
        $limit = max(1, (int)$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;
        return array_reverse($this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC));
    }

    public function getRecentSales($days)
    {
        $days = in_array((int)$days, array(30,60,90), true) ? (int)$days : 30;
        $netClp = $this->amountClpSql('d', 'net_amount', 'net_amount_clp');
        $sql = "SELECT
                    d.issue_date,
                    d.document_number,
                    d.receiver_legal_name,
                    d.document_type_taxbureau,
                    d.currency_code,
                    d.exchange_rate_value,
                    d.exchange_rate_date,
                    d.exchange_rate_source,
                    d.net_amount AS net_amount_original,
                    " . $netClp . " AS net_amount,
                    CASE
                        WHEN d.document_type_taxbureau IN (33,34,39) THEN 'Factura/Boleta'
                        WHEN d.document_type_taxbureau = 56 THEN 'Nota de débito'
                        WHEN d.document_type_taxbureau = 61 THEN 'Nota de crédito'
                        WHEN d.document_type_taxbureau = 110 THEN 'Factura de exportación'
                        WHEN d.document_type_taxbureau = 111 THEN 'Nota de débito de exportación'
                        WHEN d.document_type_taxbureau = 112 THEN 'Nota de crédito de exportación'
                        ELSE 'Otro'
                    END AS document_label,
                    CASE
                        WHEN d.document_type_taxbureau IN (33,34,39,56,110,111) THEN " . $netClp . "
                        WHEN d.document_type_taxbureau IN (61,112) THEN -(" . $netClp . ")
                        ELSE 0
                    END AS net_sales_effect
                FROM facto_documents d
                WHERE d.received_issued_flag = 1
                  AND d.document_status = 1
                  AND d.document_type_taxbureau IN (33,34,39,56,61,110,111,112)
                  AND d.issue_date >= DATE_SUB(CURDATE(), INTERVAL " . $days . " DAY)
                ORDER BY d.issue_date DESC, d.document_id DESC";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getRollingKpis($days)
    {
        $days = in_array((int)$days, array(30,60,90), true) ? (int)$days : 30;
        $netClp = $this->amountClpSql('d', 'net_amount', 'net_amount_clp');
        $customerKey = "COALESCE(NULLIF(d.crm_customer_id,0), NULLIF(REPLACE(REPLACE(UPPER(TRIM(d.receiver_tax_id_code)),'.',''),'-',''),''), NULLIF(UPPER(TRIM(d.receiver_legal_name)),''))";
        $sql = "SELECT
                    COALESCE(SUM(CASE
                        WHEN d.document_type_taxbureau IN (33,34,39,56,110,111) THEN " . $netClp . "
                        WHEN d.document_type_taxbureau IN (61,112) THEN -(" . $netClp . ")
                        ELSE 0 END),0) AS sales,
                    COUNT(CASE WHEN d.document_type_taxbureau IN (33,34,39,56,110,111) THEN 1 END) AS documents,
                    COUNT(DISTINCT CASE WHEN d.document_type_taxbureau IN (33,34,39,56,61,110,111,112) THEN " . $customerKey . " END) AS customers
                FROM facto_documents d
                WHERE d.received_issued_flag = 1
                  AND d.document_status = 1
                  AND d.net_amount > 0
                  AND d.document_type_taxbureau IN (33,34,39,56,61,110,111,112)
                  AND d.issue_date >= DATE_SUB(CURDATE(), INTERVAL " . $days . " DAY)";
        $row = $this->pdo->query($sql)->fetch(PDO::FETCH_ASSOC) ?: array();
        $sales = (float)($row['sales'] ?? 0);
        $documents = (int)($row['documents'] ?? 0);
        $customers = (int)($row['customers'] ?? 0);
        return array(
            'sales' => $sales,
            'documents' => $documents,
            'customers' => $customers,
            'average_ticket' => $documents > 0 ? round($sales / $documents) : 0,
        );
    }

    public function getSalesQuotesChart($fromDate, $toDate, $group)
    {
        if (!in_array($group, array('day','week','month'), true)) $group = 'day';
        if ($group === 'week') {
            $selectPeriod = "YEARWEEK(d.issue_date,1) AS period_key, MIN(d.issue_date) AS label_date";
            $groupBy = "YEARWEEK(d.issue_date,1)";
            $quoteSelectPeriod = "YEARWEEK(issue_date,1) AS period_key, MIN(issue_date) AS label_date";
            $quoteGroupBy = "YEARWEEK(issue_date,1)";
        } elseif ($group === 'month') {
            $selectPeriod = "DATE_FORMAT(d.issue_date,'%Y-%m') AS period_key, MIN(d.issue_date) AS label_date";
            $groupBy = "DATE_FORMAT(d.issue_date,'%Y-%m')";
            $quoteSelectPeriod = "DATE_FORMAT(issue_date,'%Y-%m') AS period_key, MIN(issue_date) AS label_date";
            $quoteGroupBy = "DATE_FORMAT(issue_date,'%Y-%m')";
        } else {
            $selectPeriod = "d.issue_date AS period_key, d.issue_date AS label_date";
            $groupBy = "d.issue_date";
            $quoteSelectPeriod = "issue_date AS period_key, issue_date AS label_date";
            $quoteGroupBy = "issue_date";
        }
        $netClp = $this->amountClpSql('d', 'net_amount', 'net_amount_clp');
        $salesSql = "SELECT " . $selectPeriod . ",
                    SUM(CASE
                        WHEN d.document_type_taxbureau IN (33,34,39,56,110,111) THEN " . $netClp . "
                        WHEN d.document_type_taxbureau IN (61,112) THEN -(" . $netClp . ")
                        ELSE 0 END) AS sales
                FROM facto_documents d
                WHERE d.received_issued_flag=1 AND d.document_status=1
                  AND d.document_type_taxbureau IN (33,34,39,56,61,110,111,112)
                  AND d.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(PDO::FETCH_ASSOC);

        $quoteSql = "SELECT " . $quoteSelectPeriod . ", COUNT(*) AS quotes
                     FROM facto_quotes WHERE issue_date BETWEEN ? AND ?
                     GROUP BY " . $quoteGroupBy . " ORDER BY label_date ASC";
        $stmt = $this->pdo->prepare($quoteSql);
        $stmt->execute(array($fromDate,$toDate));
        $quoteRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $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 = $sales = $quotes = array();
        foreach ($data as $row) {
            $labels[] = $group === 'month' ? date('m/Y',strtotime($row['label_date'])) : ($group === 'week' ? 'Sem ' . date('W',strtotime($row['label_date'])) : date('d/m',strtotime($row['label_date'])));
            $sales[] = $row['sales'];
            $quotes[] = $row['quotes'];
        }
        return array('labels'=>$labels,'sales'=>$sales,'quotes'=>$quotes,'group'=>$group);
    }

    private function amountClpSql($alias, $amountColumn, $clpColumn)
    {
        $prefix = $alias ? $alias . '.' : '';
        return "COALESCE(" . $prefix . $clpColumn . ", CASE
            WHEN UPPER(COALESCE(NULLIF(" . $prefix . "currency_code,''),'CLP'))='CLP' THEN " . $prefix . $amountColumn . "
            WHEN COALESCE(" . $prefix . "exchange_rate_value,0)>0 THEN ROUND(" . $prefix . $amountColumn . "*" . $prefix . "exchange_rate_value,2)
            ELSE 0 END)";
    }
}
