<?php

namespace App\Controllers;

use App\Libraries\TemporaryImageUpload;
use Config\Database;

class AdminController extends BaseController
{
    public function dashboard()
    {
        if (! session()->get('isLoggedIn')) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if (! $this->isUserAccount()) {
            return redirect()->to($this->homePath())->with('error', 'Akses area admin tidak tersedia untuk akun ini.');
        }

        $isAdministrator = $this->isAdministratorRole();

        if ($isAdministrator) {
            (new TemporaryImageUpload())->cleanupExpired(7200);
        }

        return view('admin/dashboard', [
            'summary'           => $this->dashboardSummary($isAdministrator),
            'chartData'         => $this->dashboardCharts($isAdministrator),
            'lowStockItems'     => $this->lowStockItems(),
            'activeServices'    => $this->activeServiceQueue(),
            'topProducts'       => $isAdministrator ? $this->topProductsThisMonth() : [],
            'topServices'       => $isAdministrator ? $this->topServicesThisMonth() : [],
            'userName'          => session()->get('userName'),
            'userEmail'         => session()->get('userEmail'),
            'userRole'          => session()->get('userRole'),
        ]);
    }

    private function dashboardSummary(bool $includeAdminData): array
    {
        $db = Database::connect();
        $today = date('Y-m-d');
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');

        $stock = $db->table('barang')
            ->select('COUNT(*) AS total_barang, COALESCE(SUM(stok), 0) AS total_qty, SUM(CASE WHEN stok <= stok_minimum THEN 1 ELSE 0 END) AS hampir_habis, SUM(CASE WHEN stok <= 0 THEN 1 ELSE 0 END) AS kosong', false)
            ->get()
            ->getRowArray();

        $salesToday = $this->salesSummary($today, $today);
        $serviceToday = $this->serviceSummary($today, $today);
        $serviceStatus = $this->serviceStatusSummary();

        $summary = [
            'total_barang'                  => (int) ($stock['total_barang'] ?? 0),
            'total_qty_barang'              => (int) ($stock['total_qty'] ?? 0),
            'stok_hampir_habis'             => (int) ($stock['hampir_habis'] ?? 0),
            'stok_kosong'                   => (int) ($stock['kosong'] ?? 0),
            'penjualan_hari_ini'            => (int) ($salesToday['total_transaksi'] ?? 0),
            'total_penjualan_hari_ini'      => (float) ($salesToday['total'] ?? 0),
            'laba_penjualan_hari_ini'       => (float) ($salesToday['laba'] ?? 0),
            'service_hari_ini'              => (int) ($serviceToday['total_transaksi'] ?? 0),
            'total_service_hari_ini'        => (float) ($serviceToday['total'] ?? 0),
            'pendapatan_jasa_hari_ini'      => (float) ($serviceToday['subtotal_jasa'] ?? 0),
            'laba_service_hari_ini'         => (float) ($serviceToday['laba'] ?? 0),
            'service_menunggu'              => (int) ($serviceStatus['menunggu'] ?? 0),
            'service_dalam_pengerjaan'      => (int) ($serviceStatus['dalam_pengerjaan'] ?? 0),
            'service_siap_ambil'            => (int) ($serviceStatus['selesai_siap_ambil'] ?? 0),
            'service_selesai'               => (int) ($serviceStatus['selesai_diambil'] ?? 0),
        ];

        if (! $includeAdminData) {
            return $summary;
        }

        $incoming = $db->table('pembelian bm')
            ->select('COUNT(DISTINCT bm.id) AS total_transaksi, COALESCE(SUM(bmd.jumlah), 0) AS total_qty')
            ->join('pembelian_detail bmd', 'bmd.pembelian_id = bm.id', 'left')
            ->where('bm.tanggal_pembelian >=', $monthStart)
            ->where('bm.tanggal_pembelian <=', $monthEnd)
            ->get()
            ->getRowArray();

        $adjustment = $db->table('stok_penyesuaian')
            ->select('COUNT(*) AS total_transaksi, COALESCE(SUM(ABS(selisih)), 0) AS total_qty', false)
            ->where('tanggal_penyesuaian >=', $monthStart)
            ->where('tanggal_penyesuaian <=', $monthEnd)
            ->where('status', 'aktif')
            ->get()
            ->getRowArray();

        $salesMonth = $this->salesSummary($monthStart, $monthEnd);
        $serviceMonth = $this->serviceSummary($monthStart, $monthEnd);

        return array_merge($summary, [
            'total_kategori'                => $db->table('kategori')->countAllResults(),
            'total_suplier'                 => $db->table('suplier')->countAllResults(),
            'penjualan_bulan_ini'           => (int) ($salesMonth['total_transaksi'] ?? 0),
            'total_penjualan_bulan_ini'     => (float) ($salesMonth['total'] ?? 0),
            'pajak_penjualan_bulan_ini'     => (float) ($salesMonth['pajak'] ?? 0),
            'laba_penjualan_bulan_ini'      => (float) ($salesMonth['laba'] ?? 0),
            'service_bulan_ini'             => (int) ($serviceMonth['total_transaksi'] ?? 0),
            'total_service_bulan_ini'       => (float) ($serviceMonth['total'] ?? 0),
            'pendapatan_jasa_bulan_ini'     => (float) ($serviceMonth['subtotal_jasa'] ?? 0),
            'pajak_service_bulan_ini'       => (float) ($serviceMonth['pajak'] ?? 0),
            'laba_service_bulan_ini'        => (float) ($serviceMonth['laba'] ?? 0),
            'total_laba_bulan_ini'          => (float) ($salesMonth['laba'] ?? 0) + (float) ($serviceMonth['laba'] ?? 0),
            'piutang_penjualan'             => $this->receivableSalesTotal(),
            'piutang_service'               => $this->receivableServiceTotal(),
            'hutang_pembelian'              => $this->payablePurchaseTotal(),
            'pembelian_bulan_ini'           => (int) ($incoming['total_transaksi'] ?? 0),
            'qty_pembelian_bulan_ini'       => (int) ($incoming['total_qty'] ?? 0),
            'penyesuaian_bulan_ini'         => (int) ($adjustment['total_transaksi'] ?? 0),
            'qty_penyesuaian_bulan_ini'     => (int) ($adjustment['total_qty'] ?? 0),
            'total_user'                    => $db->table('user')->countAllResults(),
            'total_kasir'                   => $db->table('user')->where('role', 'kasir')->countAllResults(),
        ]);
    }

    private function dashboardCharts(bool $includeAdminData): array
    {
        $charts = [
            'stockByCategory'  => $this->stockByCategoryChart(),
            'serviceStatus'    => $this->serviceStatusChart(),
        ];

        if ($includeAdminData) {
            $charts['salesProfit'] = $this->salesProfitChart();
            $charts['annualSalesProfit'] = $this->annualSalesProfitChart();
            $charts['topProducts'] = $this->topProductsChart();
            $charts['topServices'] = $this->topServicesChart();
        }

        return $charts;
    }

    private function salesSummary(string $dateFrom, string $dateTo): array
    {
        $db = Database::connect();

        $sales = $db->table('penjualan')
            ->select('COUNT(*) AS total_transaksi, COALESCE(SUM(total_penjualan), 0) AS total, COALESCE(SUM(pajak_nominal), 0) AS pajak', false)
            ->where('tanggal_penjualan >=', $dateFrom)
            ->where('tanggal_penjualan <=', $dateTo)
            ->get()
            ->getRowArray();

        $profit = $db->table('penjualan p')
            ->select('COALESCE(SUM(pd.subtotal - pd.total_modal), 0) AS laba', false)
            ->join('penjualan_detail pd', 'pd.penjualan_id = p.id', 'left')
            ->where('p.tanggal_penjualan >=', $dateFrom)
            ->where('p.tanggal_penjualan <=', $dateTo)
            ->get()
            ->getRowArray();

        return [
            'total_transaksi' => (int) ($sales['total_transaksi'] ?? 0),
            'total'           => (float) ($sales['total'] ?? 0),
            'pajak'           => (float) ($sales['pajak'] ?? 0),
            'laba'            => (float) ($profit['laba'] ?? 0),
        ];
    }

    private function serviceSummary(string $dateFrom, string $dateTo): array
    {
        $db = Database::connect();

        $service = $db->table('service')
            ->select('COUNT(*) AS total_transaksi, COALESCE(SUM(total_service), 0) AS total, COALESCE(SUM(subtotal_barang), 0) AS subtotal_barang, COALESCE(SUM(subtotal_jasa), 0) AS subtotal_jasa, COALESCE(SUM(pajak_nominal), 0) AS pajak', false)
            ->where('tanggal_service >=', $dateFrom)
            ->where('tanggal_service <=', $dateTo)
            ->get()
            ->getRowArray();

        $partProfit = $db->table('service s')
            ->select('COALESCE(SUM(sb.subtotal - sb.total_modal), 0) AS laba_part', false)
            ->join('service_barang sb', 'sb.service_id = s.id', 'left')
            ->where('s.tanggal_service >=', $dateFrom)
            ->where('s.tanggal_service <=', $dateTo)
            ->get()
            ->getRowArray();

        return [
            'total_transaksi' => (int) ($service['total_transaksi'] ?? 0),
            'total'           => (float) ($service['total'] ?? 0),
            'subtotal_barang' => (float) ($service['subtotal_barang'] ?? 0),
            'subtotal_jasa'   => (float) ($service['subtotal_jasa'] ?? 0),
            'pajak'           => (float) ($service['pajak'] ?? 0),
            'laba'            => (float) ($service['subtotal_jasa'] ?? 0) + (float) ($partProfit['laba_part'] ?? 0),
        ];
    }

    private function serviceStatusSummary(): array
    {
        $rows = Database::connect()
            ->table('service')
            ->select('status_service, COUNT(*) AS total', false)
            ->whereIn('status_service', ['menunggu', 'dalam_pengerjaan', 'selesai_siap_ambil', 'selesai_diambil'])
            ->groupBy('status_service')
            ->get()
            ->getResultArray();

        $summary = [
            'menunggu' => 0,
            'dalam_pengerjaan' => 0,
            'selesai_siap_ambil' => 0,
            'selesai_diambil' => 0,
        ];

        foreach ($rows as $row) {
            $summary[(string) $row['status_service']] = (int) $row['total'];
        }

        return $summary;
    }

    private function receivableSalesTotal(): float
    {
        $row = Database::connect()->query(
            'SELECT COALESCE(SUM(GREATEST(p.total_penjualan - COALESCE(pay.total_dibayar, 0), 0)), 0) AS total
             FROM penjualan p
             LEFT JOIN (
                 SELECT penjualan_id, SUM(jumlah) AS total_dibayar
                 FROM penjualan_pembayaran
                 GROUP BY penjualan_id
             ) pay ON pay.penjualan_id = p.id
             WHERE p.status_pembayaran = "piutang"'
        )->getRowArray();

        return (float) ($row['total'] ?? 0);
    }

    private function receivableServiceTotal(): float
    {
        $row = Database::connect()->query(
            'SELECT COALESCE(SUM(GREATEST(s.total_service - COALESCE(pay.total_dibayar, 0), 0)), 0) AS total
             FROM service s
             LEFT JOIN (
                 SELECT service_id, SUM(jumlah) AS total_dibayar
                 FROM service_pembayaran
                 GROUP BY service_id
             ) pay ON pay.service_id = s.id
             WHERE s.status_pembayaran = "piutang"'
        )->getRowArray();

        return (float) ($row['total'] ?? 0);
    }

    private function payablePurchaseTotal(): float
    {
        $row = Database::connect()->query(
            'SELECT COALESCE(SUM(GREATEST(p.total_pembelian - COALESCE(pay.total_dibayar, 0), 0)), 0) AS total
             FROM pembelian p
             LEFT JOIN (
                 SELECT pembelian_id, SUM(jumlah) AS total_dibayar
                 FROM pembelian_pembayaran
                 GROUP BY pembelian_id
             ) pay ON pay.pembelian_id = p.id
             WHERE p.status_pembayaran = "hutang"'
        )->getRowArray();

        return (float) ($row['total'] ?? 0);
    }

    private function salesProfitChart(): array
    {
        $db = Database::connect();
        $days = [];
        $labels = [];
        $today = date_create(date('Y-m-d'));
        $date = date_create(date('Y-m-01'));

        while ($date <= $today) {
            $period = $date->format('Y-m-d');
            $days[$period] = [
                'penjualan' => 0,
                'service'   => 0,
                'laba'      => 0,
            ];
            $labels[$period] = $date->format('d/m');
            $date->modify('+1 day');
        }

        $dateStart = array_key_first($days);
        $dateEnd = array_key_last($days);

        $salesRows = $db->table('penjualan')
            ->select('tanggal_penjualan AS tanggal, COALESCE(SUM(total_penjualan), 0) AS total', false)
            ->where('tanggal_penjualan >=', $dateStart)
            ->where('tanggal_penjualan <=', $dateEnd)
            ->groupBy('tanggal_penjualan')
            ->get()
            ->getResultArray();

        foreach ($salesRows as $row) {
            if (isset($days[$row['tanggal']])) {
                $days[$row['tanggal']]['penjualan'] = (float) $row['total'];
            }
        }

        $serviceRows = $db->table('service')
            ->select('tanggal_service AS tanggal, COALESCE(SUM(total_service), 0) AS total', false)
            ->where('tanggal_service >=', $dateStart)
            ->where('tanggal_service <=', $dateEnd)
            ->groupBy('tanggal_service')
            ->get()
            ->getResultArray();

        foreach ($serviceRows as $row) {
            if (isset($days[$row['tanggal']])) {
                $days[$row['tanggal']]['service'] = (float) $row['total'];
            }
        }

        $salesProfitRows = $db->table('penjualan p')
            ->select('p.tanggal_penjualan AS tanggal, COALESCE(SUM(pd.subtotal - pd.total_modal), 0) AS laba', false)
            ->join('penjualan_detail pd', 'pd.penjualan_id = p.id', 'left')
            ->where('p.tanggal_penjualan >=', $dateStart)
            ->where('p.tanggal_penjualan <=', $dateEnd)
            ->groupBy('p.tanggal_penjualan')
            ->get()
            ->getResultArray();

        foreach ($salesProfitRows as $row) {
            if (isset($days[$row['tanggal']])) {
                $days[$row['tanggal']]['laba'] += (float) $row['laba'];
            }
        }

        $serviceProfitRows = $db->table('service s')
            ->select('s.tanggal_service AS tanggal, COALESCE(MAX(s.subtotal_jasa), 0) + COALESCE(SUM(sb.subtotal - sb.total_modal), 0) AS laba', false)
            ->join('service_barang sb', 'sb.service_id = s.id', 'left')
            ->where('s.tanggal_service >=', $dateStart)
            ->where('s.tanggal_service <=', $dateEnd)
            ->groupBy('s.id, s.tanggal_service')
            ->get()
            ->getResultArray();

        foreach ($serviceProfitRows as $row) {
            if (isset($days[$row['tanggal']])) {
                $days[$row['tanggal']]['laba'] += (float) $row['laba'];
            }
        }

        return [
            'labels'    => array_values($labels),
            'penjualan' => array_column($days, 'penjualan'),
            'service'   => array_column($days, 'service'),
            'laba'      => array_column($days, 'laba'),
        ];
    }

    private function annualSalesProfitChart(): array
    {
        $db = Database::connect();
        $year = date('Y');
        $months = [];
        $labels = [];

        for ($month = 1; $month <= 12; $month++) {
            $period = sprintf('%s-%02d', $year, $month);
            $months[$period] = [
                'penjualan' => 0,
                'service'   => 0,
                'laba'      => 0,
            ];
            $labels[$period] = date('M', strtotime($period . '-01'));
        }

        $dateStart = $year . '-01-01';
        $dateEnd = $year . '-12-31';

        $salesRows = $db->table('penjualan')
            ->select("DATE_FORMAT(tanggal_penjualan, '%Y-%m') AS periode", false)
            ->select('COALESCE(SUM(total_penjualan), 0) AS total', false)
            ->where('tanggal_penjualan >=', $dateStart)
            ->where('tanggal_penjualan <=', $dateEnd)
            ->groupBy('periode')
            ->get()
            ->getResultArray();

        foreach ($salesRows as $row) {
            if (isset($months[$row['periode']])) {
                $months[$row['periode']]['penjualan'] = (float) $row['total'];
            }
        }

        $serviceRows = $db->table('service')
            ->select("DATE_FORMAT(tanggal_service, '%Y-%m') AS periode", false)
            ->select('COALESCE(SUM(total_service), 0) AS total', false)
            ->where('tanggal_service >=', $dateStart)
            ->where('tanggal_service <=', $dateEnd)
            ->groupBy('periode')
            ->get()
            ->getResultArray();

        foreach ($serviceRows as $row) {
            if (isset($months[$row['periode']])) {
                $months[$row['periode']]['service'] = (float) $row['total'];
            }
        }

        $salesProfitRows = $db->table('penjualan p')
            ->select("DATE_FORMAT(p.tanggal_penjualan, '%Y-%m') AS periode", false)
            ->select('COALESCE(SUM(pd.subtotal - pd.total_modal), 0) AS laba', false)
            ->join('penjualan_detail pd', 'pd.penjualan_id = p.id', 'left')
            ->where('p.tanggal_penjualan >=', $dateStart)
            ->where('p.tanggal_penjualan <=', $dateEnd)
            ->groupBy('periode')
            ->get()
            ->getResultArray();

        foreach ($salesProfitRows as $row) {
            if (isset($months[$row['periode']])) {
                $months[$row['periode']]['laba'] += (float) $row['laba'];
            }
        }

        $serviceProfitRows = $db->table('service s')
            ->select("DATE_FORMAT(s.tanggal_service, '%Y-%m') AS periode", false)
            ->select('COALESCE(MAX(s.subtotal_jasa), 0) + COALESCE(SUM(sb.subtotal - sb.total_modal), 0) AS laba', false)
            ->join('service_barang sb', 'sb.service_id = s.id', 'left')
            ->where('s.tanggal_service >=', $dateStart)
            ->where('s.tanggal_service <=', $dateEnd)
            ->groupBy('s.id, periode')
            ->get()
            ->getResultArray();

        foreach ($serviceProfitRows as $row) {
            if (isset($months[$row['periode']])) {
                $months[$row['periode']]['laba'] += (float) $row['laba'];
            }
        }

        return [
            'labels'    => array_values($labels),
            'penjualan' => array_column($months, 'penjualan'),
            'service'   => array_column($months, 'service'),
            'laba'      => array_column($months, 'laba'),
        ];
    }

    private function serviceStatusChart(): array
    {
        $statusLabels = [
            'menunggu' => 'Menunggu',
            'dalam_pengerjaan' => 'Dikerjakan',
            'selesai_siap_ambil' => 'Siap Ambil',
        ];
        $summary = $this->serviceStatusSummary();

        return [
            'labels' => array_values($statusLabels),
            'values' => array_map(static fn (string $status): int => $summary[$status] ?? 0, array_keys($statusLabels)),
        ];
    }

    private function stockByCategoryChart(): array
    {
        $rows = Database::connect()
            ->table('barang b')
            ->select('COALESCE(k.nama_kategori, "Tanpa Kategori") AS nama_kategori', false)
            ->select('COALESCE(SUM(b.stok), 0) AS total_qty', false)
            ->join('kategori k', 'k.id = b.kategori_id', 'left')
            ->groupBy('k.id, k.nama_kategori')
            ->orderBy('total_qty', 'DESC')
            ->limit(8)
            ->get()
            ->getResultArray();

        return [
            'labels' => array_map(static fn (array $row): string => (string) $row['nama_kategori'], $rows),
            'values' => array_map(static fn (array $row): int => (int) $row['total_qty'], $rows),
        ];
    }

    private function lowStockItems(): array
    {
        return Database::connect()
            ->table('barang')
            ->select('id, kode_barang, nama_barang, satuan, stok, stok_minimum, lokasi_barang')
            ->where('stok <= stok_minimum')
            ->orderBy('stok', 'ASC')
            ->orderBy('nama_barang', 'ASC')
            ->limit(5)
            ->get()
            ->getResultArray();
    }

    private function activeServiceQueue(): array
    {
        return Database::connect()
            ->table('service s')
            ->select('s.id, s.no_transaksi, s.tanggal_service, s.merk_tipe, s.nomor_objek, s.status_service, pl.nama AS nama_pelanggan, t.nama AS nama_teknisi')
            ->join('pelanggan pl', 'pl.id = s.pelanggan_id', 'left')
            ->join('teknisi t', 't.id = s.teknisi_id', 'left')
            ->whereIn('s.status_service', ['menunggu', 'dalam_pengerjaan', 'selesai_siap_ambil'])
            ->orderBy("FIELD(s.status_service, 'selesai_siap_ambil', 'dalam_pengerjaan', 'menunggu')", '', false)
            ->orderBy('s.tanggal_service', 'ASC')
            ->orderBy('s.id', 'ASC')
            ->limit(6)
            ->get()
            ->getResultArray();
    }

    private function topProductsThisMonth(): array
    {
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');

        return Database::connect()->query(
            'SELECT kode_barang, nama_barang, satuan, SUM(jumlah) AS total_qty, SUM(subtotal) AS total_omzet, SUM(subtotal - total_modal) AS total_laba
             FROM (
                 SELECT pd.kode_barang, pd.nama_barang, pd.satuan, pd.jumlah, pd.subtotal, pd.total_modal
                 FROM penjualan_detail pd
                 JOIN penjualan p ON p.id = pd.penjualan_id
                 WHERE p.tanggal_penjualan >= ? AND p.tanggal_penjualan <= ?
                 UNION ALL
                 SELECT sb.kode_barang, sb.nama_barang, sb.satuan, sb.jumlah, sb.subtotal, sb.total_modal
                 FROM service_barang sb
                 JOIN service s ON s.id = sb.service_id
                 WHERE s.tanggal_service >= ? AND s.tanggal_service <= ?
             ) item
             GROUP BY kode_barang, nama_barang, satuan
             ORDER BY total_qty DESC, total_omzet DESC
             LIMIT 5',
            [$monthStart, $monthEnd, $monthStart, $monthEnd]
        )->getResultArray();
    }

    private function topProductsChart(): array
    {
        $rows = $this->topProductsThisMonth();

        return [
            'labels' => array_map(static fn (array $row): string => (string) $row['nama_barang'], $rows),
            'qty'    => array_map(static fn (array $row): float => (float) $row['total_qty'], $rows),
            'omzet'  => array_map(static fn (array $row): float => (float) $row['total_omzet'], $rows),
            'laba'   => array_map(static fn (array $row): float => (float) $row['total_laba'], $rows),
        ];
    }

    private function topServicesThisMonth(): array
    {
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');

        return Database::connect()->query(
            'SELECT sj.nama_jasa, COUNT(*) AS total_transaksi, SUM(sj.biaya) AS total_omzet
             FROM service_jasa sj
             JOIN service s ON s.id = sj.service_id
             WHERE s.tanggal_service >= ? AND s.tanggal_service <= ?
             GROUP BY sj.nama_jasa
             ORDER BY total_transaksi DESC, total_omzet DESC
             LIMIT 5',
            [$monthStart, $monthEnd]
        )->getResultArray();
    }

    private function topServicesChart(): array
    {
        $rows = $this->topServicesThisMonth();

        return [
            'labels' => array_map(static fn (array $row): string => (string) $row['nama_jasa'], $rows),
            'qty'    => array_map(static fn (array $row): int => (int) $row['total_transaksi'], $rows),
            'omzet'  => array_map(static fn (array $row): float => (float) $row['total_omzet'], $rows),
        ];
    }

}
