<?php

namespace App\Controllers;

class Analytics extends BaseController
{
    // -------------------------------------------------------
    // GET /analytics  — Halaman Dashboard Analitik
    // -------------------------------------------------------
    public function index(): string
    {
        return view('analytics/index');
    }

    // -------------------------------------------------------
    // GET /analytics/data  — AJAX: Data chart & KPI
    // Query param: ?range=7|30|90|365|all
    // Sumber data: tb_penjualan + tb_penjualan_item
    // -------------------------------------------------------
    public function data(): \CodeIgniter\HTTP\ResponseInterface
    {
        if (!$this->request->isAJAX()) {
            return $this->response->setStatusCode(403)->setJSON(['success' => false, 'error' => 'Forbidden']);
        }

        $range = $this->request->getGet('range') ?? '30';
        $db    = \Config\Database::connect();

        // --- WHERE clause berdasarkan range (pakai tanggal_penjualan) ---
        $whereDate = '';
        if ($range !== 'all') {
            $days      = (int) $range;
            $whereDate = "AND DATE(pj.tanggal_penjualan) >= DATE_SUB(CURDATE(), INTERVAL {$days} DAY)";
        }

        // -------------------------------------------------------
        // 1. KPI Cards
        // -------------------------------------------------------
        $kpi = $db->query("
            SELECT
                COUNT(DISTINCT pj.id_penjualan)      AS total_order,
                COALESCE(SUM(pj.total_penjualan), 0) AS total_revenue,
                CASE
                    WHEN COUNT(DISTINCT pj.id_penjualan) > 0
                    THEN COALESCE(SUM(pj.total_penjualan), 0) / COUNT(DISTINCT pj.id_penjualan)
                    ELSE 0
                END AS avg_order_value,
                COALESCE(SUM(i.qty), 0) AS total_qty_terjual
            FROM tb_penjualan pj
            LEFT JOIN tb_penjualan_item i ON i.id_penjualan = pj.id_penjualan
            WHERE 1=1 {$whereDate}
        ")->getRowArray();

        // -------------------------------------------------------
        // 2. Grafik Revenue per Hari
        // -------------------------------------------------------
        $limitDays    = ($range === 'all') ? 365 : min((int)$range, 365);
        $revenueChart = $db->query("
            SELECT
                DATE(pj.tanggal_penjualan)           AS tgl,
                COALESCE(SUM(pj.total_penjualan), 0) AS revenue,
                COUNT(*)                              AS jml_order
            FROM tb_penjualan pj
            WHERE DATE(pj.tanggal_penjualan) >= DATE_SUB(CURDATE(), INTERVAL {$limitDays} DAY)
            GROUP BY DATE(pj.tanggal_penjualan)
            ORDER BY tgl ASC
        ")->getResultArray();

        // -------------------------------------------------------
        // 3. Breakdown Jenis Penjualan (Ecer / Reseller / Maklon)
        // -------------------------------------------------------
        $jenisBreakdown = $db->query("
            SELECT
                pj.jenis_penjualan                   AS jenis,
                COUNT(*)                              AS jml_order,
                COALESCE(SUM(pj.total_penjualan), 0) AS revenue
            FROM tb_penjualan pj
            WHERE 1=1 {$whereDate}
            GROUP BY pj.jenis_penjualan
            ORDER BY revenue DESC
        ")->getResultArray();

        // -------------------------------------------------------
        // 4. Top 5 Produk Terlaris
        // -------------------------------------------------------
        $topProducts = $db->query("
            SELECT
                p.nama_produk,
                COALESCE(SUM(i.qty), 0)      AS total_qty,
                COALESCE(SUM(i.subtotal), 0) AS total_revenue
            FROM tb_penjualan_item i
            JOIN tb_penjualan pj ON pj.id_penjualan = i.id_penjualan
            JOIN tb_produk p     ON p.id_produk     = i.id_produk
            WHERE 1=1 {$whereDate}
            GROUP BY i.id_produk, p.nama_produk
            ORDER BY total_qty DESC
            LIMIT 5
        ")->getResultArray();

        // -------------------------------------------------------
        // 5. Revenue bulan ini vs bulan lalu
        // -------------------------------------------------------
        $revBulanIni = $db->query("
            SELECT COALESCE(SUM(pj.total_penjualan), 0) AS rev
            FROM tb_penjualan pj
            WHERE YEAR(pj.tanggal_penjualan)  = YEAR(CURDATE())
              AND MONTH(pj.tanggal_penjualan) = MONTH(CURDATE())
        ")->getRowArray()['rev'] ?? 0;

        $revBulanLalu = $db->query("
            SELECT COALESCE(SUM(pj.total_penjualan), 0) AS rev
            FROM tb_penjualan pj
            WHERE YEAR(pj.tanggal_penjualan)  = YEAR(CURDATE() - INTERVAL 1 MONTH)
              AND MONTH(pj.tanggal_penjualan) = MONTH(CURDATE() - INTERVAL 1 MONTH)
        ")->getRowArray()['rev'] ?? 0;

        // -------------------------------------------------------
        // 6. Tren Harian 7 Hari Terakhir
        // -------------------------------------------------------
        $daily7 = $db->query("
            SELECT
                DATE(pj.tanggal_penjualan)           AS tgl,
                COALESCE(SUM(pj.total_penjualan), 0) AS revenue,
                COUNT(*)                              AS jml_order
            FROM tb_penjualan pj
            WHERE DATE(pj.tanggal_penjualan) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            GROUP BY DATE(pj.tanggal_penjualan)
            ORDER BY tgl ASC
        ")->getResultArray();

        // -------------------------------------------------------
        // 7. Detail Jenis (untuk tabel bawah)
        // -------------------------------------------------------
        $jenisDetail = $db->query("
            SELECT
                pj.jenis_penjualan                   AS jenis,
                COUNT(*)                              AS jml_transaksi,
                COALESCE(SUM(pj.total_penjualan), 0) AS total_nilai
            FROM tb_penjualan pj
            WHERE 1=1 {$whereDate}
            GROUP BY pj.jenis_penjualan
            ORDER BY total_nilai DESC
        ")->getResultArray();

        return $this->response->setJSON([
            'success'         => true,
            'range'           => $range,
            'kpi'             => $kpi,
            'revenue_chart'   => $revenueChart,
            'jenis_breakdown' => $jenisBreakdown,
            'top_products'    => $topProducts,
            'rev_bulan_ini'   => (float) $revBulanIni,
            'rev_bulan_lalu'  => (float) $revBulanLalu,
            'daily7'          => $daily7,
            'jenis_detail'    => $jenisDetail,
        ]);
    }
}