<?php

namespace App\Controllers;

use App\Models\BarangModel;
use App\Models\KategoriModel;
use App\Models\PelangganModel;
use App\Models\SuplierModel;
use App\Models\TeknisiModel;
use App\Libraries\SimpleXlsxWriter;
use Config\Database;
use Dompdf\Dompdf;
use Dompdf\Options;

class LaporanController extends BaseController
{
    private BarangModel $barangModel;
    private KategoriModel $kategoriModel;
    private PelangganModel $pelangganModel;
    private SuplierModel $suplierModel;
    private TeknisiModel $teknisiModel;

    public function __construct()
    {
        $this->barangModel = new BarangModel();
        $this->kategoriModel = new KategoriModel();
        $this->pelangganModel = new PelangganModel();
        $this->suplierModel = new SuplierModel();
        $this->teknisiModel = new TeknisiModel();
    }

    public function stok()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/dashboard', 'Akses ditolak. Laporan hanya dapat diakses oleh administrator.')) {
            return $redirect;
        }

        [$barang, $filters] = $this->stockReportData();

        return view('admin/laporan/stok', [
            'barang'     => $barang,
            'kategori'   => $this->kategoriOptions(),
            'filters'    => $filters,
            'summary'    => $this->stockSummary($barang),
            'userName'   => session()->get('userName'),
            'userEmail'  => session()->get('userEmail'),
            'userRole'   => session()->get('userRole'),
        ]);
    }

    public function stokPdf()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/stok')) {
            return $redirect;
        }

        [$barang, $filters] = $this->stockReportData();
        $kategori = $this->kategoriOptions();
        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml(view('admin/laporan/stok_pdf', [
            'barang'   => $barang,
            'kategori' => $kategori,
            'filters'  => $filters,
            'summary'  => $this->stockSummary($barang),
        ]));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setHeader('Content-Type', 'application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="laporan-stok-barang.pdf"')
            ->setBody($dompdf->output());
    }

    public function stokExcel()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/stok')) {
            return $redirect;
        }

        [$barang, $filters] = $this->stockReportData();
        $summary = $this->stockSummary($barang);
        $content = (new SimpleXlsxWriter())->write(
            'Laporan Stok',
            $this->stockExcelRows($barang, $filters, $summary),
            [16, 34, 22, 22, 14, 14, 14, 16, 16, 18],
            [1 => 2, 8 => 1]
        );

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="laporan-stok-barang.xlsx"')
            ->setBody($content);
    }

    public function pembelian()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/dashboard', 'Akses ditolak. Laporan hanya dapat diakses oleh administrator.')) {
            return $redirect;
        }

        [$rows, $filters] = $this->incomingReportData();

        return view('admin/laporan/pembelian', [
            'rows'      => $rows,
            'suplier'   => $this->suplierOptions(),
            'filters'   => $filters,
            'summary'   => $this->incomingSummary($rows),
            'userName'  => session()->get('userName'),
            'userEmail' => session()->get('userEmail'),
            'userRole'  => session()->get('userRole'),
        ]);
    }

    public function pembelianPdf()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/pembelian')) {
            return $redirect;
        }

        [$rows, $filters] = $this->incomingReportData();
        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml(view('admin/laporan/pembelian_pdf', [
            'rows'    => $rows,
            'suplier' => $this->suplierOptions(),
            'filters' => $filters,
            'summary' => $this->incomingSummary($rows),
        ]));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setHeader('Content-Type', 'application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="laporan-pembelian.pdf"')
            ->setBody($dompdf->output());
    }

    public function pembelianExcel()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/pembelian')) {
            return $redirect;
        }

        [$rows, $filters] = $this->incomingReportData();
        $summary = $this->incomingSummary($rows);
        $content = (new SimpleXlsxWriter())->write(
            'Laporan Pembelian',
            $this->incomingExcelRows($rows, $filters, $summary),
            [16, 20, 24, 24, 18, 34, 12, 16, 16, 16, 16, 18, 18],
            [1 => 2, 8 => 1]
        );

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="laporan-pembelian.xlsx"')
            ->setBody($content);
    }

    public function penjualan()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/dashboard', 'Akses ditolak. Laporan hanya dapat diakses oleh administrator.')) {
            return $redirect;
        }

        [$rows, $filters] = $this->outgoingReportData();

        return view('admin/laporan/penjualan', [
            'rows'      => $rows,
            'pelanggan' => $this->pelangganOptions(),
            'filters'   => $filters,
            'summary'   => $this->outgoingSummary($rows),
            'userName'  => session()->get('userName'),
            'userEmail' => session()->get('userEmail'),
            'userRole'  => session()->get('userRole'),
        ]);
    }

    public function penjualanPdf()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/penjualan')) {
            return $redirect;
        }

        [$rows, $filters] = $this->outgoingReportData();
        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml(view('admin/laporan/penjualan_pdf', [
            'rows'      => $rows,
            'pelanggan' => $this->pelangganOptions(),
            'filters'   => $filters,
            'summary'   => $this->outgoingSummary($rows),
        ]));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setHeader('Content-Type', 'application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="laporan-penjualan.pdf"')
            ->setBody($dompdf->output());
    }

    public function penjualanExcel()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/penjualan')) {
            return $redirect;
        }

        [$rows, $filters] = $this->outgoingReportData();
        $summary = $this->outgoingSummary($rows);
        $content = (new SimpleXlsxWriter())->write(
            'Laporan Penjualan',
            $this->outgoingExcelRows($rows, $filters, $summary),
            [16, 14, 20, 22, 24, 18, 34, 12, 16, 16, 16, 16, 16, 18, 18],
            [1 => 2, 8 => 1]
        );

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="laporan-penjualan.xlsx"')
            ->setBody($content);
    }

    public function service()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/dashboard', 'Akses ditolak. Laporan hanya dapat diakses oleh administrator.')) {
            return $redirect;
        }

        [$rows, $filters] = $this->serviceReportData();

        return view('admin/laporan/service', [
            'rows'      => $rows,
            'pelanggan' => $this->pelangganOptions(),
            'teknisi'   => $this->teknisiOptions(),
            'filters'   => $filters,
            'summary'   => $this->serviceSummary($rows),
            'userName'  => session()->get('userName'),
            'userEmail' => session()->get('userEmail'),
            'userRole'  => session()->get('userRole'),
        ]);
    }

    public function servicePdf()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/service')) {
            return $redirect;
        }

        [$rows, $filters] = $this->serviceReportData();
        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml(view('admin/laporan/service_pdf', [
            'rows'      => $rows,
            'pelanggan' => $this->pelangganOptions(),
            'teknisi'   => $this->teknisiOptions(),
            'filters'   => $filters,
            'summary'   => $this->serviceSummary($rows),
        ]));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setHeader('Content-Type', 'application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="laporan-service.pdf"')
            ->setBody($dompdf->output());
    }

    public function serviceExcel()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/service')) {
            return $redirect;
        }

        [$rows, $filters] = $this->serviceReportData();
        $summary = $this->serviceSummary($rows);
        $content = (new SimpleXlsxWriter())->write(
            'Laporan Service',
            $this->serviceExcelRows($rows, $filters, $summary),
            [16, 20, 22, 20, 22, 20, 22, 18, 18, 16, 16, 16, 16, 16, 16, 18, 18],
            [1 => 2, 8 => 1]
        );

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="laporan-service.xlsx"')
            ->setBody($content);
    }

    public function penyesuaianStok()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/dashboard', 'Akses ditolak. Laporan hanya dapat diakses oleh administrator.')) {
            return $redirect;
        }

        [$rows, $filters] = $this->adjustmentReportData();

        return view('admin/laporan/penyesuaian_stok', [
            'rows'      => $rows,
            'filters'   => $filters,
            'summary'   => $this->adjustmentSummary($rows),
            'userName'  => session()->get('userName'),
            'userEmail' => session()->get('userEmail'),
            'userRole'  => session()->get('userRole'),
        ]);
    }

    public function penyesuaianStokPdf()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/penyesuaian-stok')) {
            return $redirect;
        }

        [$rows, $filters] = $this->adjustmentReportData();
        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', false);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml(view('admin/laporan/penyesuaian_stok_pdf', [
            'rows'    => $rows,
            'filters' => $filters,
            'summary' => $this->adjustmentSummary($rows),
        ]));
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setHeader('Content-Type', 'application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="laporan-penyesuaian-stok.pdf"')
            ->setBody($dompdf->output());
    }

    public function penyesuaianStokExcel()
    {
        if (! $this->isLoggedIn()) {
            return redirect()->to('/login')->with('error', 'Silakan login terlebih dahulu.');
        }

        if ($redirect = $this->requireAdministrator('/admin/laporan/penyesuaian-stok')) {
            return $redirect;
        }

        [$rows, $filters] = $this->adjustmentReportData();
        $summary = $this->adjustmentSummary($rows);
        $content = (new SimpleXlsxWriter())->write(
            'Penyesuaian Stok',
            $this->adjustmentExcelRows($rows, $filters, $summary),
            [16, 22, 18, 34, 14, 14, 14, 16, 26, 16, 18, 18, 22],
            [1 => 2, 8 => 1]
        );

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="laporan-penyesuaian-stok.xlsx"')
            ->setBody($content);
    }

    private function stockReportData(): array
    {
        $filters = [
            'kategori_id' => (string) $this->request->getGet('kategori_id'),
            'status'      => (string) $this->request->getGet('status'),
        ];

        $builder = $this->barangModel
            ->select('barang.*, kategori.nama_kategori, kategori.icon_kategori')
            ->join('kategori', 'kategori.id = barang.kategori_id', 'left');

        if ($filters['kategori_id'] !== '') {
            $builder->where('barang.kategori_id', (int) $filters['kategori_id']);
        }

        if ($filters['status'] === 'hampir-habis') {
            $builder->where('barang.stok <= barang.stok_minimum');
        } elseif ($filters['status'] === 'kosong') {
            $builder->where('barang.stok', 0);
        } elseif ($filters['status'] === 'aman') {
            $builder->where('barang.stok > barang.stok_minimum');
        }

        return [
            $builder
                ->orderBy('kategori.nama_kategori', 'ASC')
                ->orderBy('barang.nama_barang', 'ASC')
                ->findAll(),
            $filters,
        ];
    }

    private function stockSummary(array $barang): array
    {
        $totalStok = 0;
        $hampirHabis = 0;
        $kosong = 0;

        foreach ($barang as $item) {
            $stok = (int) ($item['stok'] ?? 0);
            $stokMinimum = (int) ($item['stok_minimum'] ?? 0);
            $totalStok += $stok;

            if ($stok <= 0) {
                $kosong++;
            }

            if ($stok <= $stokMinimum) {
                $hampirHabis++;
            }
        }

        return [
            'total_barang'  => count($barang),
            'total_stok'    => $totalStok,
            'hampir_habis'  => $hampirHabis,
            'kosong'        => $kosong,
        ];
    }

    private function incomingReportData(): array
    {
        $filters = [
            'tanggal_mulai'   => (string) $this->request->getGet('tanggal_mulai'),
            'tanggal_selesai' => (string) $this->request->getGet('tanggal_selesai'),
            'suplier_id'      => (string) $this->request->getGet('suplier_id'),
        ];

        $builder = Database::connect()
            ->table('pembelian_detail bmd')
            ->select('bm.id AS transaksi_id, bm.no_transaksi, bm.tanggal_pembelian, bm.nomor_dokumen, bm.pajak_nominal, bm.total_pembelian, bm.catatan')
            ->select('s.nama AS nama_suplier, s.perusahaan AS perusahaan_suplier')
            ->select('user.name AS nama_user')
            ->select('bmd.kode_barang, bmd.nama_barang, bmd.satuan, bmd.jumlah, bmd.harga_beli, bmd.subtotal')
            ->join('pembelian bm', 'bm.id = bmd.pembelian_id', 'inner')
            ->join('suplier s', 's.id = bm.suplier_id', 'left')
            ->join('user', 'user.id = bm.user_id', 'left');

        if ($filters['tanggal_mulai'] !== '') {
            $builder->where('bm.tanggal_pembelian >=', $filters['tanggal_mulai']);
        }

        if ($filters['tanggal_selesai'] !== '') {
            $builder->where('bm.tanggal_pembelian <=', $filters['tanggal_selesai']);
        }

        if ($filters['suplier_id'] !== '') {
            $builder->where('bm.suplier_id', (int) $filters['suplier_id']);
        }

        return [
            $builder
                ->orderBy('bm.tanggal_pembelian', 'DESC')
                ->orderBy('bm.id', 'DESC')
                ->orderBy('bmd.id', 'ASC')
                ->get()
                ->getResultArray(),
            $filters,
        ];
    }

    private function incomingSummary(array $rows): array
    {
        $transactions = [];
        $totalJumlah = 0;
        $subtotal = 0;

        foreach ($rows as $row) {
            $transactionId = (int) $row['transaksi_id'];
            $transactions[$transactionId] = [
                'pajak' => (float) ($row['pajak_nominal'] ?? 0),
                'total' => (float) ($row['total_pembelian'] ?? 0),
            ];
            $totalJumlah += (int) $row['jumlah'];
            $subtotal += (float) ($row['subtotal'] ?? 0);
        }

        $totalPajak = 0;
        $totalHarga = 0;

        foreach ($transactions as $transaction) {
            $totalPajak += $transaction['pajak'];
            $totalHarga += $transaction['total'];
        }

        if ($totalHarga <= 0) {
            $totalHarga = $subtotal;
        }

        return [
            'total_transaksi' => count($transactions),
            'total_item'      => count($rows),
            'total_jumlah'    => $totalJumlah,
            'total_pajak'     => $totalPajak,
            'total_harga'     => $totalHarga,
        ];
    }

    private function outgoingReportData(): array
    {
        $filters = [
            'tanggal_mulai'   => (string) $this->request->getGet('tanggal_mulai'),
            'tanggal_selesai' => (string) $this->request->getGet('tanggal_selesai'),
            'pelanggan_id'    => (string) $this->request->getGet('pelanggan_id'),
        ];

        $db = Database::connect();
        $salesBuilder = $db
            ->table('penjualan_detail pjd')
            ->select('pj.id AS transaksi_id, pjd.id AS detail_id, "penjualan" AS jenis_transaksi, "Penjualan" AS sumber_transaksi', false)
            ->select('pj.no_transaksi, pj.tanggal_penjualan, pj.pajak_nominal, pj.catatan')
            ->select('pelanggan.nama AS nama_pelanggan, pelanggan.no_hp AS no_hp_pelanggan')
            ->select('user.name AS nama_user')
            ->select('pjd.kode_barang, pjd.nama_barang, pjd.satuan, pjd.jumlah, pjd.harga_jual, pjd.subtotal')
            ->select('COALESCE(pjd.harga_beli, 0) AS harga_beli', false)
            ->select('COALESCE(pjd.total_modal, pjd.jumlah * COALESCE(pjd.harga_beli, 0)) AS total_modal', false)
            ->select('(pjd.subtotal - COALESCE(pjd.total_modal, pjd.jumlah * COALESCE(pjd.harga_beli, 0))) AS laba', false)
            ->join('penjualan pj', 'pj.id = pjd.penjualan_id', 'inner')
            ->join('pelanggan', 'pelanggan.id = pj.pelanggan_id', 'left')
            ->join('user', 'user.id = pj.user_id', 'left');

        if ($filters['tanggal_mulai'] !== '') {
            $salesBuilder->where('pj.tanggal_penjualan >=', $filters['tanggal_mulai']);
        }

        if ($filters['tanggal_selesai'] !== '') {
            $salesBuilder->where('pj.tanggal_penjualan <=', $filters['tanggal_selesai']);
        }

        if ($filters['pelanggan_id'] !== '') {
            $salesBuilder->where('pj.pelanggan_id', (int) $filters['pelanggan_id']);
        }

        $serviceTaxExpression = 'CASE WHEN (s.subtotal_barang + s.subtotal_jasa) > 0 THEN ROUND(s.pajak_nominal * (s.subtotal_barang / (s.subtotal_barang + s.subtotal_jasa))) ELSE 0 END';
        $serviceBuilder = $db
            ->table('service_barang sb')
            ->select('s.id AS transaksi_id, sb.id AS detail_id, "service" AS jenis_transaksi, "Service" AS sumber_transaksi', false)
            ->select('s.no_transaksi, s.tanggal_service AS tanggal_penjualan, ' . $serviceTaxExpression . ' AS pajak_nominal, s.keterangan AS catatan', false)
            ->select('pelanggan.nama AS nama_pelanggan, pelanggan.no_hp AS no_hp_pelanggan')
            ->select('user.name AS nama_user')
            ->select('sb.kode_barang, sb.nama_barang, sb.satuan, sb.jumlah, sb.harga_jual, sb.subtotal')
            ->select('COALESCE(sb.harga_beli, 0) AS harga_beli', false)
            ->select('COALESCE(sb.total_modal, sb.jumlah * COALESCE(sb.harga_beli, 0)) AS total_modal', false)
            ->select('(sb.subtotal - COALESCE(sb.total_modal, sb.jumlah * COALESCE(sb.harga_beli, 0))) AS laba', false)
            ->join('service s', 's.id = sb.service_id', 'inner')
            ->join('pelanggan', 'pelanggan.id = s.pelanggan_id', 'left')
            ->join('user', 'user.id = s.user_id', 'left');

        if ($filters['tanggal_mulai'] !== '') {
            $serviceBuilder->where('s.tanggal_service >=', $filters['tanggal_mulai']);
        }

        if ($filters['tanggal_selesai'] !== '') {
            $serviceBuilder->where('s.tanggal_service <=', $filters['tanggal_selesai']);
        }

        if ($filters['pelanggan_id'] !== '') {
            $serviceBuilder->where('s.pelanggan_id', (int) $filters['pelanggan_id']);
        }

        $rows = array_merge(
            $salesBuilder->get()->getResultArray(),
            $serviceBuilder->get()->getResultArray()
        );

        usort($rows, static function (array $left, array $right): int {
            $dateCompare = strcmp((string) ($right['tanggal_penjualan'] ?? ''), (string) ($left['tanggal_penjualan'] ?? ''));

            if ($dateCompare !== 0) {
                return $dateCompare;
            }

            $transactionCompare = (int) ($right['transaksi_id'] ?? 0) <=> (int) ($left['transaksi_id'] ?? 0);

            if ($transactionCompare !== 0) {
                return $transactionCompare;
            }

            $typeCompare = strcmp((string) ($left['jenis_transaksi'] ?? ''), (string) ($right['jenis_transaksi'] ?? ''));

            if ($typeCompare !== 0) {
                return $typeCompare;
            }

            return (int) ($left['detail_id'] ?? 0) <=> (int) ($right['detail_id'] ?? 0);
        });

        return [$rows, $filters];
    }

    private function outgoingSummary(array $rows): array
    {
        $transactions = [];
        $totalJumlah = 0;
        $totalPenjualan = 0;
        $totalModal = 0;
        $totalLaba = 0;
        $totalPajak = 0;

        foreach ($rows as $row) {
            $transaksiId = ($row['jenis_transaksi'] ?? 'penjualan') . '-' . (int) $row['transaksi_id'];

            if (! isset($transactions[$transaksiId])) {
                $transactions[$transaksiId] = true;
                $totalPajak += (float) ($row['pajak_nominal'] ?? 0);
            }

            $totalJumlah += (int) $row['jumlah'];
            $totalPenjualan += (float) ($row['subtotal'] ?? 0);
            $totalModal += (float) ($row['total_modal'] ?? 0);
            $totalLaba += (float) ($row['laba'] ?? 0);
        }

        return [
            'total_transaksi' => count($transactions),
            'total_item'      => count($rows),
            'total_jumlah'    => $totalJumlah,
            'total_penjualan' => $totalPenjualan,
            'total_pajak'     => $totalPajak,
            'total_modal'     => $totalModal,
            'total_laba'      => $totalLaba,
        ];
    }

    private function outgoingExcelRows(array $rows, array $filters, array $summary): array
    {
        $excelRows = [
            ['Laporan Penjualan'],
            ['Periode', $this->dateLabel($filters['tanggal_mulai'] ?? '', 'Awal') . ' s/d ' . $this->dateLabel($filters['tanggal_selesai'] ?? '', 'Akhir')],
            ['Total Transaksi', (int) $summary['total_transaksi'], 'Jumlah Terjual', (int) $summary['total_jumlah']],
            ['Nilai Penjualan', $this->formattedExcelNumber((float) $summary['total_penjualan']), 'Total Pajak', $this->formattedExcelNumber((float) $summary['total_pajak'])],
            ['Total Modal', $this->formattedExcelNumber((float) $summary['total_modal']), 'Total Laba', $this->formattedExcelNumber((float) $summary['total_laba'])],
            [],
            [],
            ['Tanggal', 'Sumber', 'No. Transaksi', 'Pelanggan', 'No. HP', 'Kode Barang', 'Nama Barang', 'Jumlah', 'Satuan', 'Harga Jual', 'Modal Satuan', 'Subtotal', 'Pajak Transaksi', 'Laba', 'User'],
        ];

        $shownTaxTransactions = [];

        foreach ($rows as $row) {
            $transaksiId = ($row['jenis_transaksi'] ?? 'penjualan') . '-' . (int) ($row['transaksi_id'] ?? 0);
            $showTax = ! isset($shownTaxTransactions[$transaksiId]);
            $shownTaxTransactions[$transaksiId] = true;

            $excelRows[] = [
                $this->dateLabel($row['tanggal_penjualan'] ?? ''),
                $row['sumber_transaksi'] ?? 'Penjualan',
                $row['no_transaksi'] ?? '',
                $row['nama_pelanggan'] ?? '',
                $row['no_hp_pelanggan'] ?? '',
                $row['kode_barang'] ?? '',
                $row['nama_barang'] ?? '',
                (int) ($row['jumlah'] ?? 0),
                $row['satuan'] ?? '',
                $this->formattedExcelNumber((float) ($row['harga_jual'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['harga_beli'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['subtotal'] ?? 0)),
                $showTax ? $this->formattedExcelNumber((float) ($row['pajak_nominal'] ?? 0)) : '',
                $this->formattedExcelNumber((float) ($row['laba'] ?? 0)),
                $row['nama_user'] ?? '',
            ];
        }

        if ($rows === []) {
            $excelRows[] = ['Tidak ada data penjualan.'];
        }

        return $excelRows;
    }

    private function stockExcelRows(array $barang, array $filters, array $summary): array
    {
        $statusLabels = [
            '' => 'Semua status',
            'aman' => 'Aman',
            'hampir-habis' => 'Hampir habis',
            'kosong' => 'Kosong',
        ];

        $excelRows = [
            ['Laporan Stok Barang'],
            ['Kategori', $this->categoryFilterLabel($filters['kategori_id'] ?? '')],
            ['Status Stok', $statusLabels[$filters['status'] ?? ''] ?? 'Semua status'],
            ['Total Barang', (int) $summary['total_barang'], 'Total Stok', (int) $summary['total_stok']],
            ['Hampir Habis', (int) $summary['hampir_habis'], 'Stok Kosong', (int) $summary['kosong']],
            [],
            [],
            ['Kode Barang', 'Nama Barang', 'Kategori', 'Lokasi', 'Satuan', 'Stok Awal', 'Stok Minimum', 'Stok Saat Ini', 'Harga Beli', 'Harga Jual', 'Status'],
        ];

        foreach ($barang as $item) {
            $stok = (int) ($item['stok'] ?? 0);
            $stokMinimum = (int) ($item['stok_minimum'] ?? 0);
            $statusLabel = $stok <= 0 ? 'Kosong' : ($stok <= $stokMinimum ? 'Hampir habis' : 'Aman');

            $excelRows[] = [
                $item['kode_barang'] ?? '',
                $item['nama_barang'] ?? '',
                $item['nama_kategori'] ?? '',
                $item['lokasi_barang'] ?? '',
                $item['satuan'] ?? '',
                (int) ($item['stok_awal'] ?? 0),
                $stokMinimum,
                $stok,
                $this->formattedExcelNumber((float) ($item['harga_beli'] ?? 0)),
                $this->formattedExcelNumber((float) ($item['harga_jual'] ?? 0)),
                $statusLabel,
            ];
        }

        if ($barang === []) {
            $excelRows[] = ['Tidak ada data stok barang.'];
        }

        return $excelRows;
    }

    private function incomingExcelRows(array $rows, array $filters, array $summary): array
    {
        $excelRows = [
            ['Laporan Pembelian'],
            ['Periode', $this->dateLabel($filters['tanggal_mulai'] ?? '', 'Awal') . ' s/d ' . $this->dateLabel($filters['tanggal_selesai'] ?? '', 'Akhir')],
            ['Supplier', $this->supplierFilterLabel($filters['suplier_id'] ?? '')],
            ['Total Transaksi', (int) $summary['total_transaksi'], 'Total Item Barang', (int) $summary['total_item'], 'Total Jumlah', (int) $summary['total_jumlah']],
            ['Total Pajak', $this->formattedExcelNumber((float) $summary['total_pajak']), 'Total Nilai Pembelian', $this->formattedExcelNumber((float) $summary['total_harga'])],
            [],
            [],
            ['Tanggal', 'No. Transaksi', 'Supplier', 'Perusahaan', 'No. Dokumen', 'Kode Barang', 'Nama Barang', 'Jumlah', 'Satuan', 'Harga Beli', 'Subtotal', 'Pajak Transaksi', 'User'],
        ];

        $shownTaxTransactions = [];

        foreach ($rows as $row) {
            $transaksiId = (int) ($row['transaksi_id'] ?? 0);
            $showTax = ! isset($shownTaxTransactions[$transaksiId]);
            $shownTaxTransactions[$transaksiId] = true;

            $excelRows[] = [
                $this->dateLabel($row['tanggal_pembelian'] ?? ''),
                $row['no_transaksi'] ?? '',
                $row['nama_suplier'] ?? '',
                $row['perusahaan_suplier'] ?? '',
                $row['nomor_dokumen'] ?? '',
                $row['kode_barang'] ?? '',
                $row['nama_barang'] ?? '',
                (int) ($row['jumlah'] ?? 0),
                $row['satuan'] ?? '',
                $this->formattedExcelNumber((float) ($row['harga_beli'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['subtotal'] ?? 0)),
                $showTax ? $this->formattedExcelNumber((float) ($row['pajak_nominal'] ?? 0)) : '',
                $row['nama_user'] ?? '',
            ];
        }

        if ($rows === []) {
            $excelRows[] = ['Tidak ada data pembelian.'];
        }

        return $excelRows;
    }

    private function adjustmentExcelRows(array $rows, array $filters, array $summary): array
    {
        $jenisLabels = [
            '' => 'Semua jenis',
            'penambahan' => 'Penambahan',
            'pengurangan' => 'Pengurangan',
            'sesuai' => 'Sesuai',
        ];
        $statusLabels = [
            '' => 'Semua status',
            'aktif' => 'Aktif',
            'dibatalkan' => 'Dibatalkan',
        ];

        $excelRows = [
            ['Laporan Penyesuaian Stok'],
            ['Periode', $this->dateLabel($filters['tanggal_mulai'] ?? '', 'Awal') . ' s/d ' . $this->dateLabel($filters['tanggal_selesai'] ?? '', 'Akhir')],
            ['Jenis', $jenisLabels[$filters['jenis'] ?? ''] ?? 'Semua jenis', 'Status', $statusLabels[$filters['status'] ?? ''] ?? 'Semua status'],
            ['Pencarian', $filters['keyword'] ?? ''],
            ['Total Penyesuaian', (int) $summary['total_penyesuaian'], 'Total Penambahan', (int) $summary['total_penambahan'], 'Total Pengurangan', (int) $summary['total_pengurangan'], 'Dibatalkan', (int) $summary['dibatalkan']],
            [],
            [],
            ['Tanggal', 'No. Penyesuaian', 'Kode Barang', 'Nama Barang', 'Stok Sistem', 'Stok Fisik', 'Selisih', 'Jenis', 'Alasan', 'Status', 'User', 'Pembatal', 'Catatan Pembatalan'],
        ];

        foreach ($rows as $row) {
            $excelRows[] = [
                $this->dateLabel($row['tanggal_penyesuaian'] ?? ''),
                $row['no_penyesuaian'] ?? '',
                $row['kode_barang'] ?? '',
                $row['nama_barang'] ?? '',
                (int) ($row['stok_sistem'] ?? 0),
                (int) ($row['stok_fisik'] ?? 0),
                (int) ($row['selisih'] ?? 0),
                ucfirst((string) ($row['jenis'] ?? '')),
                $row['alasan'] ?? '',
                ucfirst((string) ($row['status'] ?? '')),
                $row['nama_user'] ?? '',
                $row['nama_pembatal'] ?? '',
                $row['catatan_pembatalan'] ?? '',
            ];
        }

        if ($rows === []) {
            $excelRows[] = ['Tidak ada data penyesuaian stok.'];
        }

        return $excelRows;
    }

    private function serviceExcelRows(array $rows, array $filters, array $summary): array
    {
        $statusLabels = $this->serviceStatusLabels();
        $paymentLabels = [
            '' => 'Semua status',
            'lunas' => 'Lunas',
            'piutang' => 'Piutang',
        ];

        $excelRows = [
            ['Laporan Service'],
            ['Periode', $this->dateLabel($filters['tanggal_mulai'] ?? '', 'Awal') . ' s/d ' . $this->dateLabel($filters['tanggal_selesai'] ?? '', 'Akhir')],
            ['Pelanggan', $this->customerFilterLabel($filters['pelanggan_id'] ?? ''), 'Teknisi', $this->technicianFilterLabel($filters['teknisi_id'] ?? '')],
            ['Status Service', $statusLabels[$filters['status_service'] ?? ''] ?? 'Semua status', 'Status Pembayaran', $paymentLabels[$filters['status_pembayaran'] ?? ''] ?? 'Semua status'],
            ['Total Transaksi', (int) $summary['total_transaksi'], 'Total Part', (int) $summary['total_jumlah_part']],
            ['Pendapatan Jasa', $this->formattedExcelNumber((float) $summary['total_jasa']), 'Penjualan Part', $this->formattedExcelNumber((float) $summary['total_part']), 'Total Pajak', $this->formattedExcelNumber((float) $summary['total_pajak'])],
            ['Total Service', $this->formattedExcelNumber((float) $summary['total_service']), 'Total Modal', $this->formattedExcelNumber((float) $summary['total_modal']), 'Total Laba', $this->formattedExcelNumber((float) $summary['total_laba'])],
            ['Tanggal', 'No. Service', 'Pelanggan', 'No. HP', 'Teknisi', 'Merk/Tipe', 'Nomor Objek', 'Jumlah Part', 'Item Part', 'Item Jasa', 'Subtotal Part', 'Subtotal Jasa', 'Pajak', 'Total Service', 'Modal Part', 'Laba', 'Status Service', 'Status Pembayaran', 'User', 'Daftar Jasa'],
        ];

        foreach ($rows as $row) {
            $excelRows[] = [
                $this->dateLabel($row['tanggal_service'] ?? ''),
                $row['no_transaksi'] ?? '',
                $row['nama_pelanggan'] ?? '',
                $row['no_hp_pelanggan'] ?? '',
                $row['nama_teknisi'] ?? '',
                $row['merk_tipe'] ?? '',
                $row['nomor_objek'] ?? '',
                (int) ($row['total_jumlah_part'] ?? 0),
                (int) ($row['total_item_part'] ?? 0),
                (int) ($row['total_item_jasa'] ?? 0),
                $this->formattedExcelNumber((float) ($row['subtotal_barang'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['subtotal_jasa'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['pajak_nominal'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['total_service'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['total_modal_part'] ?? 0)),
                $this->formattedExcelNumber((float) ($row['laba'] ?? 0)),
                $statusLabels[$row['status_service'] ?? ''] ?? ($row['status_service'] ?? ''),
                $paymentLabels[$row['status_pembayaran'] ?? ''] ?? ($row['status_pembayaran'] ?? ''),
                $row['nama_user'] ?? '',
                $row['daftar_jasa'] ?? '',
            ];
        }

        if ($rows === []) {
            $excelRows[] = ['Tidak ada data service.'];
        }

        return $excelRows;
    }

    private function serviceReportData(): array
    {
        $filters = [
            'tanggal_mulai'     => (string) $this->request->getGet('tanggal_mulai'),
            'tanggal_selesai'   => (string) $this->request->getGet('tanggal_selesai'),
            'pelanggan_id'      => (string) $this->request->getGet('pelanggan_id'),
            'teknisi_id'        => (string) $this->request->getGet('teknisi_id'),
            'status_service'    => (string) $this->request->getGet('status_service'),
            'status_pembayaran' => (string) $this->request->getGet('status_pembayaran'),
        ];

        $validServiceStatuses = array_keys($this->serviceStatusLabels());
        $validPaymentStatuses = ['lunas', 'piutang'];

        if (! in_array($filters['status_service'], $validServiceStatuses, true)) {
            $filters['status_service'] = '';
        }

        if (! in_array($filters['status_pembayaran'], $validPaymentStatuses, true)) {
            $filters['status_pembayaran'] = '';
        }

        $builder = Database::connect()
            ->table('service s')
            ->select('s.id AS transaksi_id, s.no_transaksi, s.tanggal_service, s.nomor_objek, s.merk_tipe, s.status_service, s.status_pembayaran')
            ->select('s.subtotal_barang, s.subtotal_jasa, s.pajak_nominal, s.total_service')
            ->select('pelanggan.nama AS nama_pelanggan, pelanggan.no_hp AS no_hp_pelanggan')
            ->select('teknisi.nama AS nama_teknisi')
            ->select('user.name AS nama_user')
            ->select('(SELECT COALESCE(SUM(jumlah), 0) FROM service_barang WHERE service_barang.service_id = s.id) AS total_jumlah_part', false)
            ->select('(SELECT COALESCE(SUM(total_modal), 0) FROM service_barang WHERE service_barang.service_id = s.id) AS total_modal_part', false)
            ->select('(SELECT COUNT(*) FROM service_barang WHERE service_barang.service_id = s.id) AS total_item_part', false)
            ->select('(SELECT COUNT(*) FROM service_jasa WHERE service_jasa.service_id = s.id) AS total_item_jasa', false)
            ->select('(SELECT GROUP_CONCAT(nama_jasa ORDER BY id SEPARATOR ", ") FROM service_jasa WHERE service_jasa.service_id = s.id) AS daftar_jasa', false)
            ->select('(s.subtotal_barang + s.subtotal_jasa - (SELECT COALESCE(SUM(total_modal), 0) FROM service_barang WHERE service_barang.service_id = s.id)) AS laba', false)
            ->join('pelanggan', 'pelanggan.id = s.pelanggan_id', 'left')
            ->join('teknisi', 'teknisi.id = s.teknisi_id', 'left')
            ->join('user', 'user.id = s.user_id', 'left');

        if ($filters['tanggal_mulai'] !== '') {
            $builder->where('s.tanggal_service >=', $filters['tanggal_mulai']);
        }

        if ($filters['tanggal_selesai'] !== '') {
            $builder->where('s.tanggal_service <=', $filters['tanggal_selesai']);
        }

        if ($filters['pelanggan_id'] !== '') {
            $builder->where('s.pelanggan_id', (int) $filters['pelanggan_id']);
        }

        if ($filters['teknisi_id'] !== '') {
            $builder->where('s.teknisi_id', (int) $filters['teknisi_id']);
        }

        if ($filters['status_service'] !== '') {
            $builder->where('s.status_service', $filters['status_service']);
        }

        if ($filters['status_pembayaran'] !== '') {
            $builder->where('s.status_pembayaran', $filters['status_pembayaran']);
        }

        return [
            $builder
                ->orderBy('s.tanggal_service', 'DESC')
                ->orderBy('s.id', 'DESC')
                ->get()
                ->getResultArray(),
            $filters,
        ];
    }

    private function serviceSummary(array $rows): array
    {
        $totalPart = 0;
        $totalJasa = 0;
        $totalPajak = 0;
        $totalService = 0;
        $totalModal = 0;
        $totalLaba = 0;
        $totalJumlahPart = 0;

        foreach ($rows as $row) {
            $totalPart += (float) ($row['subtotal_barang'] ?? 0);
            $totalJasa += (float) ($row['subtotal_jasa'] ?? 0);
            $totalPajak += (float) ($row['pajak_nominal'] ?? 0);
            $totalService += (float) ($row['total_service'] ?? 0);
            $totalModal += (float) ($row['total_modal_part'] ?? 0);
            $totalLaba += (float) ($row['laba'] ?? 0);
            $totalJumlahPart += (int) ($row['total_jumlah_part'] ?? 0);
        }

        return [
            'total_transaksi'   => count($rows),
            'total_jumlah_part' => $totalJumlahPart,
            'total_part'        => $totalPart,
            'total_jasa'        => $totalJasa,
            'total_pajak'       => $totalPajak,
            'total_service'     => $totalService,
            'total_modal'       => $totalModal,
            'total_laba'        => $totalLaba,
        ];
    }

    private function serviceStatusLabels(): array
    {
        return [
            'menunggu'           => 'Menunggu',
            'dalam_pengerjaan'   => 'Dalam Pengerjaan',
            'selesai_siap_ambil' => 'Selesai dan Siap Ambil',
            'selesai_diambil'    => 'Selesai Diambil',
        ];
    }

    private function categoryFilterLabel(string $kategoriId): string
    {
        if ($kategoriId === '') {
            return 'Semua kategori';
        }

        $kategori = $this->kategoriModel->find((int) $kategoriId);

        return $kategori['nama_kategori'] ?? 'Semua kategori';
    }

    private function supplierFilterLabel(string $suplierId): string
    {
        if ($suplierId === '') {
            return 'Semua supplier';
        }

        $suplier = $this->suplierModel->find((int) $suplierId);

        if (! $suplier) {
            return 'Semua supplier';
        }

        return trim(($suplier['nama'] ?? '') . ' - ' . ($suplier['perusahaan'] ?? ''), ' -');
    }

    private function customerFilterLabel(string $pelangganId): string
    {
        if ($pelangganId === '') {
            return 'Semua pelanggan';
        }

        $pelanggan = $this->pelangganModel->find((int) $pelangganId);

        return $pelanggan['nama'] ?? 'Semua pelanggan';
    }

    private function technicianFilterLabel(string $teknisiId): string
    {
        if ($teknisiId === '') {
            return 'Semua teknisi';
        }

        $teknisi = $this->teknisiModel->find((int) $teknisiId);

        return $teknisi['nama'] ?? 'Semua teknisi';
    }

    private function formattedExcelNumber(float $value): array
    {
        return [
            'value' => $value,
            'style' => 3,
        ];
    }

    private function dateLabel(string $date, string $fallback = ''): string
    {
        return $date !== '' ? date('d/m/Y', strtotime($date)) : $fallback;
    }

    private function adjustmentReportData(): array
    {
        $filters = [
            'tanggal_mulai'   => (string) $this->request->getGet('tanggal_mulai'),
            'tanggal_selesai' => (string) $this->request->getGet('tanggal_selesai'),
            'jenis'           => (string) $this->request->getGet('jenis'),
            'status'          => (string) $this->request->getGet('status'),
            'keyword'         => trim((string) $this->request->getGet('keyword')),
        ];

        $builder = Database::connect()
            ->table('stok_penyesuaian sp')
            ->select('sp.*')
            ->select('user.name AS nama_user')
            ->select('pembatal.name AS nama_pembatal')
            ->join('user', 'user.id = sp.user_id', 'left')
            ->join('user pembatal', 'pembatal.id = sp.dibatalkan_oleh', 'left');

        if ($filters['tanggal_mulai'] !== '') {
            $builder->where('sp.tanggal_penyesuaian >=', $filters['tanggal_mulai']);
        }

        if ($filters['tanggal_selesai'] !== '') {
            $builder->where('sp.tanggal_penyesuaian <=', $filters['tanggal_selesai']);
        }

        if (in_array($filters['jenis'], ['penambahan', 'pengurangan', 'sesuai'], true)) {
            $builder->where('sp.jenis', $filters['jenis']);
        }

        if (in_array($filters['status'], ['aktif', 'dibatalkan'], true)) {
            $builder->where('sp.status', $filters['status']);
        }

        if ($filters['keyword'] !== '') {
            $builder
                ->groupStart()
                ->like('sp.no_penyesuaian', $filters['keyword'])
                ->orLike('sp.kode_barang', $filters['keyword'])
                ->orLike('sp.nama_barang', $filters['keyword'])
                ->orLike('sp.alasan', $filters['keyword'])
                ->groupEnd();
        }

        return [
            $builder
                ->orderBy('sp.tanggal_penyesuaian', 'DESC')
                ->orderBy('sp.id', 'DESC')
                ->get()
                ->getResultArray(),
            $filters,
        ];
    }

    private function adjustmentSummary(array $rows): array
    {
        $totalPenambahan = 0;
        $totalPengurangan = 0;
        $dibatalkan = 0;

        foreach ($rows as $row) {
            $selisih = (int) ($row['selisih'] ?? 0);

            if ($selisih > 0) {
                $totalPenambahan += $selisih;
            } elseif ($selisih < 0) {
                $totalPengurangan += abs($selisih);
            }

            if (($row['status'] ?? '') === 'dibatalkan') {
                $dibatalkan++;
            }
        }

        return [
            'total_penyesuaian' => count($rows),
            'total_penambahan'  => $totalPenambahan,
            'total_pengurangan' => $totalPengurangan,
            'dibatalkan'        => $dibatalkan,
        ];
    }

    private function kategoriOptions(): array
    {
        return $this->kategoriModel
            ->orderBy('nama_kategori', 'ASC')
            ->findAll();
    }

    private function suplierOptions(): array
    {
        return $this->suplierModel
            ->orderBy('nama', 'ASC')
            ->findAll();
    }

    private function pelangganOptions(): array
    {
        return $this->pelangganModel
            ->orderBy('nama', 'ASC')
            ->findAll();
    }

    private function teknisiOptions(): array
    {
        return $this->teknisiModel
            ->orderBy('nama', 'ASC')
            ->findAll();
    }

    private function isLoggedIn(): bool
    {
        return (bool) session()->get('isLoggedIn') && $this->isUserAccount();
    }
}
