<?php
namespace App\Controllers;

use App\Core\Acl;
use App\Core\Controller;
use App\Core\Dates;
use App\Core\Request;
use App\Core\Response;
use App\Core\Settings;
use App\Services\AcpService;
use App\Services\AgingService;
use App\Services\ArCalculator;
use App\Services\DashboardService;
use App\Services\ExportService;
use App\Services\ReportBuilder;

/**
 * 24 laporan siap pakai.
 * Setiap laporan didefinisikan sebagai konfigurasi ReportBuilder sehingga
 * pengguna dapat membukanya di Pembuat Laporan untuk dimodifikasi.
 */
class ReportController extends Controller
{
    protected string $module = 'laporan';

    /** Katalog laporan bawaan. */
    public static function catalog(): array
    {
        $today = Dates::today();
        $monthStart = Dates::monthStart($today);
        $yearStart = substr($today, 0, 4) . '-01-01';

        return [
            'ar-aging' => [
                'nama' => 'Laporan AR Aging', 'kelompok' => 'Piutang', 'icon' => 'hourglass-split',
                'deskripsi' => 'Umur piutang per bucket dengan pengelompokan bebas.',
                'khusus' => 'aging',
            ],
            'acp' => [
                'nama' => 'Laporan ACP', 'kelompok' => 'Kinerja', 'icon' => 'speedometer2',
                'deskripsi' => 'Average Collection Period dengan dua metode perhitungan.',
                'khusus' => 'acp',
            ],
            'outstanding' => [
                'nama' => 'Laporan Invoice Outstanding', 'kelompok' => 'Piutang', 'icon' => 'cash-stack',
                'deskripsi' => 'Seluruh invoice yang masih memiliki saldo piutang.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'invoice_date', 'due_date', 'customer', 'outlet', 'area', 'net_amount', 'paid_amount', 'outstanding_amount', 'aging_days', 'payment_status'],
                'filters' => [['field' => 'outstanding_amount', 'operator' => 'gt', 'value1' => '0'],
                              ['field' => 'doc_status', 'operator' => 'eq', 'value1' => 'active']],
                'sort' => ['outstanding_amount' => 'desc'],
            ],
            'overdue' => [
                'nama' => 'Laporan Invoice Terlambat', 'kelompok' => 'Piutang', 'icon' => 'exclamation-octagon',
                'deskripsi' => 'Invoice yang sudah melewati tanggal jatuh tempo.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'invoice_date', 'due_date', 'customer', 'area', 'collection_officer', 'outstanding_amount', 'aging_days'],
                'filters' => [['field' => 'payment_status', 'operator' => 'eq', 'value1' => 'Terlambat']],
                'sort' => ['aging_days' => 'desc'],
            ],
            'due-soon' => [
                'nama' => 'Laporan Jatuh Tempo Segera', 'kelompok' => 'Piutang', 'icon' => 'calendar-week',
                'deskripsi' => 'Invoice yang akan jatuh tempo dalam waktu dekat.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'due_date', 'customer', 'contact' => 'outlet', 'outstanding_amount', 'aging_days', 'collection_officer'],
                'filters' => [['field' => 'outstanding_amount', 'operator' => 'gt', 'value1' => '0'],
                              ['field' => 'due_date', 'operator' => 'between', 'value1' => $today, 'value2' => Dates::addDays($today, 14)]],
                'sort' => ['due_date' => 'asc'],
            ],
            'forecast' => [
                'nama' => 'Perkiraan Penerimaan Kas', 'kelompok' => 'Kinerja', 'icon' => 'cash-coin',
                'deskripsi' => 'Proyeksi kas masuk berdasarkan janji bayar dan jatuh tempo.',
                'khusus' => 'forecast',
            ],
            'payment-history' => [
                'nama' => 'Riwayat Pembayaran', 'kelompok' => 'Pembayaran', 'icon' => 'wallet2',
                'deskripsi' => 'Seluruh transaksi pembayaran beserta alokasinya.',
                'source' => 'payments',
                'columns' => ['payment_no', 'payment_date', 'customer', 'invoice_no', 'alokasi', 'method', 'bank', 'reference_no', 'hari_penagihan', 'hari_terlambat'],
                'sort' => ['payment_date' => 'desc'],
            ],
            'partial-payment' => [
                'nama' => 'Laporan Pembayaran Sebagian', 'kelompok' => 'Pembayaran', 'icon' => 'pie-chart',
                'deskripsi' => 'Invoice yang telah dibayar sebagian namun belum lunas.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'invoice_date', 'due_date', 'customer', 'net_amount', 'paid_amount', 'outstanding_amount', 'last_payment_date', 'payment_count'],
                'filters' => [['field' => 'payment_status', 'operator' => 'eq', 'value1' => 'Pembayaran Sebagian']],
                'sort' => ['outstanding_amount' => 'desc'],
            ],
            'customer-statement' => [
                'nama' => 'Rekening Koran Customer', 'kelompok' => 'Customer', 'icon' => 'person-lines-fill',
                'deskripsi' => 'Ringkasan piutang lengkap per customer.',
                'source' => 'customers',
                'columns' => ['code', 'name', 'npwp', 'area', 'salesperson', 'jumlah_invoice', 'total_bersih', 'total_bayar', 'total_saldo', 'total_terlambat', 'rata_hari_penagihan'],
                'filters' => [['field' => 'total_saldo', 'operator' => 'gt', 'value1' => '0']],
                'sort' => ['total_saldo' => 'desc'],
            ],
            'sales-collection' => [
                'nama' => 'Laporan Penagihan per Sales', 'kelompok' => 'Kinerja', 'icon' => 'person-badge',
                'deskripsi' => 'Kinerja piutang dan penagihan per sales.',
                'source' => 'invoices', 'group_by' => ['salesperson'],
                'columns' => [['field' => 'salesperson'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'gross_amount', 'aggregate' => 'sum'], ['field' => 'paid_amount', 'aggregate' => 'sum'],
                              ['field' => 'outstanding_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'collection_days', 'aggregate' => 'avg']],
            ],
            'area-collection' => [
                'nama' => 'Laporan Penagihan per Area', 'kelompok' => 'Kinerja', 'icon' => 'geo-alt',
                'deskripsi' => 'Kinerja piutang dan penagihan per area & unit bisnis.',
                'source' => 'invoices', 'group_by' => ['area', 'business_unit'],
                'columns' => [['field' => 'area'], ['field' => 'business_unit'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'gross_amount', 'aggregate' => 'sum'], ['field' => 'paid_amount', 'aggregate' => 'sum'],
                              ['field' => 'outstanding_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'collection_days', 'aggregate' => 'avg']],
            ],
            'customer-performance' => [
                'nama' => 'Kinerja Pembayaran Customer', 'kelompok' => 'Customer', 'icon' => 'graph-up',
                'deskripsi' => 'Rata-rata hari penagihan dan ketepatan bayar per customer.',
                'source' => 'invoices', 'group_by' => ['customer'],
                'columns' => [['field' => 'customer'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'net_amount', 'aggregate' => 'sum'], ['field' => 'collection_days', 'aggregate' => 'avg', 'sort_dir' => 'desc'],
                              ['field' => 'days_late', 'aggregate' => 'avg'], ['field' => 'outstanding_amount', 'aggregate' => 'sum']],
            ],
            'job-revenue' => [
                'nama' => 'Laporan Pendapatan per Kategori Pekerjaan', 'kelompok' => 'Pendapatan', 'icon' => 'tools',
                'deskripsi' => 'Nilai invoice dan revenue per kategori & jenis pekerjaan.',
                'source' => 'invoices', 'group_by' => ['job_category'],
                'columns' => [['field' => 'job_category'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'gross_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'dpp_amount', 'aggregate' => 'sum'], ['field' => 'revenue_amount', 'aggregate' => 'sum'],
                              ['field' => 'outstanding_amount', 'aggregate' => 'sum']],
            ],
            'pmsc-bycase' => [
                'nama' => 'Laporan PMSC vs By Case', 'kelompok' => 'Pendapatan', 'icon' => 'diagram-3',
                'deskripsi' => 'Perbandingan pendapatan dan piutang antar kategori pendapatan.',
                'source' => 'invoices', 'group_by' => ['revenue_category'],
                'columns' => [['field' => 'revenue_category'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'gross_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'revenue_amount', 'aggregate' => 'sum'],
                              ['field' => 'paid_amount', 'aggregate' => 'sum'], ['field' => 'outstanding_amount', 'aggregate' => 'sum'],
                              ['field' => 'collection_days', 'aggregate' => 'avg']],
                'chart' => 'doughnut',
            ],
            'pph23' => [
                'nama' => 'Laporan Potongan PPh 23', 'kelompok' => 'Pajak', 'icon' => 'receipt-cutoff',
                'deskripsi' => 'Rincian potongan PPh 23 untuk rekonsiliasi bukti potong.',
                'source' => 'deductions',
                'columns' => ['invoice_no', 'tax_invoice_no', 'invoice_date', 'customer', 'customer_npwp', 'base_amount', 'rate', 'amount'],
                'filters' => [['field' => 'deduction_code', 'operator' => 'eq', 'value1' => 'PPH23']],
                'sort' => ['invoice_date' => 'desc'],
            ],
            'other-deduction' => [
                'nama' => 'Laporan Potongan Lain-lain', 'kelompok' => 'Pajak', 'icon' => 'scissors',
                'deskripsi' => 'Potongan administrasi dan potongan lain di luar PPh 23.',
                'source' => 'deductions',
                'columns' => ['invoice_no', 'invoice_date', 'customer', 'deduction_type', 'base_amount', 'rate', 'amount', 'notes'],
                'filters' => [['field' => 'deduction_code', 'operator' => 'not_in', 'value1' => 'PPH23']],
                'sort' => ['amount' => 'desc'],
            ],
            'invoice-revision' => [
                'nama' => 'Laporan Revisi Invoice (RF)', 'kelompok' => 'Revisi', 'icon' => 'arrow-repeat',
                'deskripsi' => 'Seluruh revisi, penggantian, dan pembatalan invoice.',
                'source' => 'revisions',
                'columns' => ['rf_no', 'rf_date', 'old_invoice_no', 'new_invoice_no', 'revision_type', 'action', 'status', 'original_amount', 'revised_amount', 'selisih', 'customer', 'reason'],
                'sort' => ['rf_date' => 'desc'],
            ],
            'cancelled-invoice' => [
                'nama' => 'Laporan Invoice Dibatalkan', 'kelompok' => 'Revisi', 'icon' => 'x-octagon',
                'deskripsi' => 'Invoice yang dibatalkan dan dikeluarkan dari piutang aktif.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'invoice_date', 'customer', 'gross_amount', 'net_amount', 'doc_status', 'notes'],
                'filters' => [['field' => 'doc_status', 'operator' => 'in', 'value1' => 'cancelled,replaced,revised']],
                'sort' => ['invoice_date' => 'desc'],
            ],
            'bad-debt' => [
                'nama' => 'Laporan Penghapusan Piutang (Bad Debt)', 'kelompok' => 'Revisi', 'icon' => 'trash3',
                'deskripsi' => 'Piutang yang telah dihapusbukukan.',
                'source' => 'invoices',
                'columns' => ['invoice_no', 'invoice_date', 'due_date', 'customer', 'net_amount', 'paid_amount', 'credit_note_amount', 'doc_status'],
                'filters' => [['field' => 'doc_status', 'operator' => 'eq', 'value1' => 'written_off']],
                'sort' => ['net_amount' => 'desc'],
            ],
            'monthly-revenue' => [
                'nama' => 'Laporan Pendapatan Bulanan', 'kelompok' => 'Pendapatan', 'icon' => 'calendar3',
                'deskripsi' => 'Nilai invoice dan revenue diakui per bulan.',
                'source' => 'invoices', 'group_by' => ['invoice_month'],
                'columns' => [['field' => 'invoice_month', 'sort_dir' => 'asc'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'gross_amount', 'aggregate' => 'sum'], ['field' => 'dpp_amount', 'aggregate' => 'sum'],
                              ['field' => 'vat_amount', 'aggregate' => 'sum'], ['field' => 'total_deduction', 'aggregate' => 'sum'],
                              ['field' => 'net_amount', 'aggregate' => 'sum'], ['field' => 'revenue_amount', 'aggregate' => 'sum']],
                'chart' => 'bar',
            ],
            'monthly-collection' => [
                'nama' => 'Laporan Penerimaan Bulanan', 'kelompok' => 'Pembayaran', 'icon' => 'bar-chart-line',
                'deskripsi' => 'Nilai pembayaran diterima per bulan beserta rata-rata hari penagihan.',
                'source' => 'payments', 'group_by' => ['payment_month'],
                'columns' => [['field' => 'payment_month', 'sort_dir' => 'asc'], ['field' => 'jumlah_pembayaran', 'aggregate' => 'dcount'],
                              ['field' => 'alokasi', 'aggregate' => 'sum'], ['field' => 'hari_penagihan', 'aggregate' => 'avg'],
                              ['field' => 'hari_terlambat', 'aggregate' => 'avg']],
                'chart' => 'bar',
            ],
            'top-outstanding' => [
                'nama' => 'Customer Piutang Terbesar', 'kelompok' => 'Customer', 'icon' => 'trophy',
                'deskripsi' => 'Peringkat customer berdasarkan saldo piutang.',
                'source' => 'invoices', 'group_by' => ['customer'],
                'columns' => [['field' => 'customer'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'net_amount', 'aggregate' => 'sum'],
                              ['field' => 'outstanding_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'aging_days', 'aggregate' => 'max']],
                'filters' => [['field' => 'outstanding_amount', 'operator' => 'gt', 'value1' => '0']],
                'chart' => 'horizontalBar', 'limit' => 30,
            ],
            'top-overdue' => [
                'nama' => 'Customer Tunggakan Terbesar', 'kelompok' => 'Customer', 'icon' => 'exclamation-triangle',
                'deskripsi' => 'Peringkat customer berdasarkan piutang terlambat.',
                'source' => 'invoices', 'group_by' => ['customer'],
                'columns' => [['field' => 'customer'], ['field' => 'jumlah_invoice', 'aggregate' => 'count'],
                              ['field' => 'outstanding_amount', 'aggregate' => 'sum', 'sort_dir' => 'desc'],
                              ['field' => 'aging_days', 'aggregate' => 'max'], ['field' => 'aging_days', 'aggregate' => 'avg']],
                'filters' => [['field' => 'payment_status', 'operator' => 'eq', 'value1' => 'Terlambat']],
                'chart' => 'horizontalBar', 'limit' => 30,
            ],
            'collection-activity' => [
                'nama' => 'Laporan Aktivitas Penagihan', 'kelompok' => 'Penagihan', 'icon' => 'telephone-outbound',
                'deskripsi' => 'Riwayat follow-up penagihan dan janji bayar.',
                'source' => 'collection',
                'columns' => ['followup_date', 'officer', 'customer', 'invoice_no', 'method', 'status', 'result', 'promise_date', 'promise_amount', 'promise_status', 'outstanding_amount'],
                'sort' => ['followup_date' => 'desc'],
            ],
            'data-quality' => [
                'nama' => 'Laporan Kualitas Data Invoice', 'kelompok' => 'Kualitas Data', 'icon' => 'clipboard-check',
                'deskripsi' => 'Invoice yang memiliki tanda masalah kualitas data.',
                'khusus' => 'kualitas_data',
            ],
        ];
    }

    public function index(): void
    {
        $cat = self::catalog();
        $groups = [];
        foreach ($cat as $code => $r) $groups[$r['kelompok']][$code] = $r;
        $this->view('laporan/index', [
            'judul' => 'Laporan',
            'subjudul' => count($cat) . ' laporan siap pakai — dapat dibuka dan dimodifikasi di Pembuat Laporan',
            'groups' => $groups,
        ]);
    }

    /** Mengubah entri katalog menjadi definisi ReportBuilder. */
    private function toDefinition(string $code, array $r): array
    {
        $columns = [];
        foreach ($r['columns'] ?? [] as $c) {
            if (is_string($c)) {
                $columns[] = ['field' => $c, 'aggregate' => 'none',
                              'sort_dir' => $r['sort'][$c] ?? '', 'sort_priority' => isset($r['sort'][$c]) ? 1 : 0];
            } else {
                $columns[] = array_merge(['aggregate' => 'none', 'sort_dir' => '', 'sort_priority' => 0], $c);
            }
        }
        return [
            'name' => $r['nama'], 'description' => $r['deskripsi'],
            'data_source' => $r['source'] ?? 'invoices',
            'chart_type' => $r['chart'] ?? 'none',
            'group_by' => $r['group_by'] ?? [],
            'columns' => $columns,
            'filters' => $r['filters'] ?? [],
            'pivot' => null,
            'options' => ['limit' => $r['limit'] ?? 1000, 'as_of' => Dates::today()],
            'visibility' => 'public',
        ];
    }

    public function show(array $params): void
    {
        $code = (string)($params['kode'] ?? '');
        $cat = self::catalog();
        if (!isset($cat[$code])) Response::abort(404, 'Laporan tidak ditemukan.');
        $r = $cat[$code];

        // Laporan dengan halaman khusus
        if (!empty($r['khusus'])) {
            $q = $_GET ? '?' . http_build_query($_GET) : '';
            $this->redirect(match ($r['khusus']) {
                'aging' => '/aging' . $q,
                'acp' => '/acp' . $q,
                'forecast' => '/forecast' . $q,
                'kualitas_data' => '/kualitas-data' . $q,
                default => '/laporan',
            });
        }

        $def = $this->toDefinition($code, $r);
        // Filter tambahan dari query string
        $extra = $this->extraFilters($def['data_source']);
        $def['filters'] = array_merge($def['filters'], $extra);
        [$page, $per] = $this->pageParams();
        $def['options']['limit'] = max(50, min(5000, $per * 10));

        $res = ReportBuilder::execute($def, $def['options']['limit']);

        $this->view('laporan/tampil', [
            'judul' => $r['nama'],
            'subjudul' => $r['deskripsi'],
            'kode' => $code, 'meta' => $r, 'def' => $def, 'res' => $res,
            'opsi' => DashboardService::filterOptions(),
            'filters' => PiutangController::readFilters(false),
        ]);
    }

    /** Filter dari query string yang dipetakan ke field ReportBuilder. */
    private function extraFilters(string $source): array
    {
        $map = [
            'area_id' => ['area', 'areas'], 'business_unit_id' => ['business_unit', 'business_units'],
            'salesperson_id' => ['salesperson', 'salespersons'], 'customer_id' => ['customer', 'customers'],
            'job_category_id' => ['job_category', 'job_categories'],
            'revenue_category_id' => ['revenue_category', 'revenue_categories'],
            'collection_officer_id' => ['collection_officer', 'collection_officers'],
        ];
        $fields = ReportBuilder::fields($source);
        $out = [];
        foreach ($map as $qk => [$field, $table]) {
            $v = Request::int($qk);
            if ($v <= 0 || !isset($fields[$field])) continue;
            $name = \App\Core\Database::i()->value("SELECT name FROM `$table` WHERE id = ?", [$v]);
            if ($name) $out[] = ['field' => $field, 'operator' => 'eq', 'value1' => $name];
        }
        $dari = Request::date('dari') ?: Request::date('invoice_date_from');
        $sampai = Request::date('sampai') ?: Request::date('invoice_date_to');
        $dateField = isset($fields['invoice_date']) ? 'invoice_date' : (isset($fields['payment_date']) ? 'payment_date'
                   : (isset($fields['followup_date']) ? 'followup_date' : (isset($fields['rf_date']) ? 'rf_date' : null)));
        if ($dateField && ($dari || $sampai)) {
            $out[] = ['field' => $dateField, 'operator' => 'between',
                      'value1' => $dari ?: '2000-01-01', 'value2' => $sampai ?: Dates::today()];
        }
        return $out;
    }

    public function export(array $params): void
    {
        $code = (string)($params['kode'] ?? '');
        $cat = self::catalog();
        if (!isset($cat[$code])) Response::abort(404, 'Laporan tidak ditemukan.');
        $r = $cat[$code];
        if (!empty($r['khusus'])) {
            $q = $_GET ? '?' . http_build_query($_GET) : '';
            $this->redirect(match ($r['khusus']) {
                'aging' => '/aging/ekspor' . $q, 'acp' => '/acp/ekspor' . $q,
                'forecast' => '/forecast/ekspor' . $q, 'kualitas_data' => '/kualitas-data/ekspor' . $q,
                default => '/laporan',
            });
        }
        $def = $this->toDefinition($code, $r);
        $def['filters'] = array_merge($def['filters'], $this->extraFilters($def['data_source']));
        $res = ReportBuilder::execute($def, Settings::int('laporan.maks_baris_export', 50000));

        $columns = [];
        foreach ($res['columns'] as $c) $columns[] = ['label' => $c['label'], 'key' => $c['alias'], 'type' => $c['type']];

        ExportService::send((string)Request::get('format', 'xlsx'), [
            'judul' => mb_strtoupper($r['nama']),
            'subjudul' => $r['deskripsi'],
            'tanggal_lapor' => Dates::fmt(Dates::today()),
            'filter' => PiutangController::filterLabels(PiutangController::readFilters(false)),
            'nama_berkas' => $code, 'sheet' => mb_substr($r['nama'], 0, 31),
            'orientasi' => count($columns) > 6 ? 'landscape' : 'portrait',
        ], $columns, $res['rows'], $res['total']);
    }
}
