<?php
namespace App\Services;

use App\Core\Acl;
use App\Core\Auth;
use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Settings;

/**
 * Pembuat laporan fleksibel.
 *
 * Pengguna memilih sumber data, kolom, filter, pengelompokan, pengurutan,
 * agregasi, subtotal, pivot, dan jenis chart. Seluruh nama kolom, operator,
 * dan fungsi agregasi berasal dari daftar putih (whitelist) di kelas ini —
 * tidak ada bagian input pengguna yang pernah disisipkan langsung ke SQL,
 * sehingga aman dari SQL injection.
 */
class ReportBuilder
{
    // ==================================================================
    // DEFINISI SUMBER DATA
    // ==================================================================

    public static function dataSources(): array
    {
        return [
            'invoices'   => ['label' => 'Invoice & Piutang',        'icon' => 'receipt',            'deskripsi' => 'Seluruh invoice beserta nilai, potongan, pembayaran, saldo, dan umur piutang.'],
            'payments'   => ['label' => 'Transaksi Pembayaran',     'icon' => 'wallet2',            'deskripsi' => 'Rincian setiap pembayaran dan alokasinya ke invoice.'],
            'aging'      => ['label' => 'Aging Piutang',            'icon' => 'hourglass-split',    'deskripsi' => 'Invoice outstanding beserta bucket umur piutang.'],
            'collection' => ['label' => 'Aktivitas Penagihan',      'icon' => 'telephone-outbound', 'deskripsi' => 'Riwayat follow-up penagihan dan janji bayar.'],
            'revisions'  => ['label' => 'Revisi Invoice (RF)',      'icon' => 'arrow-repeat',       'deskripsi' => 'Register revisi, penggantian, pembatalan, dan hapus buku invoice.'],
            'deductions' => ['label' => 'Potongan Invoice',         'icon' => 'scissors',           'deskripsi' => 'Rincian PPh 23, potongan administrasi, dan potongan lainnya.'],
            'customers'  => ['label' => 'Customer & Piutang',       'icon' => 'people',             'deskripsi' => 'Data customer beserta ringkasan piutangnya.'],
        ];
    }

    /**
     * Definisi field per sumber data.
     * type: text|date|money|int|dec|pct  ; agg: apakah dapat diagregasi
     */
    public static function fields(string $source): array
    {
        $inv = [
            'invoice_no'        => ['label' => 'No. Invoice',        'expr' => 'i.invoice_no',        'type' => 'text', 'group' => 'Invoice'],
            'tax_invoice_no'    => ['label' => 'No. Faktur Pajak',   'expr' => 'i.tax_invoice_no',    'type' => 'text', 'group' => 'Invoice'],
            'so_no'             => ['label' => 'No. SO',             'expr' => 'i.so_no',             'type' => 'text', 'group' => 'Invoice'],
            'invoice_date'      => ['label' => 'Tanggal Invoice',    'expr' => 'i.invoice_date',      'type' => 'date', 'group' => 'Tanggal'],
            'invoice_month'     => ['label' => 'Bulan Invoice',      'expr' => "DATE_FORMAT(i.invoice_date,'%Y-%m')", 'type' => 'text', 'group' => 'Tanggal'],
            'invoice_year'      => ['label' => 'Tahun Invoice',      'expr' => 'YEAR(i.invoice_date)','type' => 'text', 'group' => 'Tanggal'],
            'wr_date'           => ['label' => 'Tanggal WR',         'expr' => 'i.wr_date',           'type' => 'date', 'group' => 'Tanggal'],
            'sent_date'         => ['label' => 'Tanggal Kirim',      'expr' => 'i.sent_date',         'type' => 'date', 'group' => 'Tanggal'],
            'due_date'          => ['label' => 'Jatuh Tempo',        'expr' => 'i.due_date',          'type' => 'date', 'group' => 'Tanggal'],
            'due_month'         => ['label' => 'Bulan Jatuh Tempo',  'expr' => "DATE_FORMAT(i.due_date,'%Y-%m')", 'type' => 'text', 'group' => 'Tanggal'],
            'last_payment_date' => ['label' => 'Tanggal Bayar Terakhir', 'expr' => 'i.last_payment_date', 'type' => 'date', 'group' => 'Tanggal'],
            'payment_term_days' => ['label' => 'Termin (hari)',      'expr' => 'i.payment_term_days', 'type' => 'int',  'group' => 'Invoice', 'agg' => true],
            'customer'          => ['label' => 'Customer',           'expr' => 'c.name',              'type' => 'text', 'group' => 'Dimensi'],
            'customer_code'     => ['label' => 'Kode Customer',      'expr' => 'c.code',              'type' => 'text', 'group' => 'Dimensi'],
            'customer_npwp'     => ['label' => 'NPWP Customer',      'expr' => 'c.npwp',              'type' => 'text', 'group' => 'Dimensi'],
            'outlet'            => ['label' => 'Outlet',             'expr' => 'o.name',              'type' => 'text', 'group' => 'Dimensi'],
            'area'              => ['label' => 'Area',               'expr' => 'a.name',              'type' => 'text', 'group' => 'Dimensi'],
            'business_unit'     => ['label' => 'Unit Bisnis',        'expr' => 'bu.name',             'type' => 'text', 'group' => 'Dimensi'],
            'salesperson'       => ['label' => 'Sales',              'expr' => 's.name',              'type' => 'text', 'group' => 'Dimensi'],
            'collection_officer'=> ['label' => 'Petugas Penagihan',  'expr' => 'co.name',             'type' => 'text', 'group' => 'Dimensi'],
            'job_category'      => ['label' => 'Kategori Pekerjaan', 'expr' => 'jc.name',             'type' => 'text', 'group' => 'Dimensi'],
            'job_type'          => ['label' => 'Jenis Pekerjaan',    'expr' => 'jt.name',             'type' => 'text', 'group' => 'Dimensi'],
            'revenue_category'  => ['label' => 'PMSC / By Case',     'expr' => 'rc.name',             'type' => 'text', 'group' => 'Dimensi'],
            'send_channel'      => ['label' => 'Kanal Pengiriman',   'expr' => 'i.send_channel',      'type' => 'text', 'group' => 'Dimensi'],
            'send_pic'          => ['label' => 'PIC Pengiriman',     'expr' => 'i.send_pic',          'type' => 'text', 'group' => 'Dimensi'],
            'payment_status'    => ['label' => 'Status Pembayaran',  'expr' => 'ps.name',             'type' => 'text', 'group' => 'Status'],
            'doc_status'        => ['label' => 'Status Dokumen',     'expr' => 'i.doc_status',        'type' => 'text', 'group' => 'Status'],
            'gross_amount'      => ['label' => 'Nominal Bruto',      'expr' => 'i.gross_amount',      'type' => 'money','group' => 'Nilai', 'agg' => true],
            'dpp_amount'        => ['label' => 'DPP',                'expr' => 'i.dpp_amount',        'type' => 'money','group' => 'Nilai', 'agg' => true],
            'vat_amount'        => ['label' => 'PPN',                'expr' => 'i.vat_amount',        'type' => 'money','group' => 'Nilai', 'agg' => true],
            'pph23_amount'      => ['label' => 'Potongan PPh 23',    'expr' => 'i.pph23_amount',      'type' => 'money','group' => 'Nilai', 'agg' => true],
            'pph23_rate'        => ['label' => 'Tarif PPh 23 (%)',   'expr' => 'i.pph23_rate',        'type' => 'dec',  'group' => 'Nilai', 'agg' => true],
            'admin_fee_amount'  => ['label' => 'Potongan Admin',     'expr' => 'i.admin_fee_amount',  'type' => 'money','group' => 'Nilai', 'agg' => true],
            'other_deduction_amount' => ['label' => 'Potongan Lain', 'expr' => 'i.other_deduction_amount', 'type' => 'money', 'group' => 'Nilai', 'agg' => true],
            'total_deduction'   => ['label' => 'Total Potongan',     'expr' => 'i.total_deduction',   'type' => 'money','group' => 'Nilai', 'agg' => true],
            'net_amount'        => ['label' => 'Total Tagihan',      'expr' => 'i.net_amount',        'type' => 'money','group' => 'Nilai', 'agg' => true],
            'credit_note_amount'=> ['label' => 'Nota Kredit',        'expr' => 'i.credit_note_amount','type' => 'money','group' => 'Nilai', 'agg' => true],
            'paid_amount'       => ['label' => 'Pembayaran',         'expr' => 'i.paid_amount',       'type' => 'money','group' => 'Nilai', 'agg' => true],
            'outstanding_amount'=> ['label' => 'Saldo Piutang',      'expr' => 'i.outstanding_amount','type' => 'money','group' => 'Nilai', 'agg' => true],
            'revenue_amount'    => ['label' => 'Revenue Diakui',     'expr' => 'i.revenue_amount',    'type' => 'money','group' => 'Nilai', 'agg' => true],
            'aging_days'        => ['label' => 'Umur Piutang (hari)','expr' => 'i.aging_days',        'type' => 'int',  'group' => 'Kinerja', 'agg' => true],
            'collection_days'   => ['label' => 'Hari Penagihan',     'expr' => 'i.collection_days',   'type' => 'int',  'group' => 'Kinerja', 'agg' => true],
            'days_late'         => ['label' => 'Hari Terlambat',     'expr' => 'i.days_late',         'type' => 'int',  'group' => 'Kinerja', 'agg' => true],
            'payment_count'     => ['label' => 'Jumlah Pembayaran',  'expr' => 'i.payment_count',     'type' => 'int',  'group' => 'Kinerja', 'agg' => true],
            'notes'             => ['label' => 'Catatan',            'expr' => 'i.notes',             'type' => 'text', 'group' => 'Lain'],
            'dq_flags'          => ['label' => 'Tanda Kualitas Data','expr' => 'i.dq_flags',          'type' => 'text', 'group' => 'Lain'],
            'source_sheet'      => ['label' => 'Sheet Sumber',       'expr' => 'i.source_sheet',      'type' => 'text', 'group' => 'Lain'],
            'jumlah_invoice'    => ['label' => 'Jumlah Invoice',     'expr' => 'i.id',                'type' => 'int',  'group' => 'Kinerja', 'agg' => true, 'count_only' => true],
        ];

        switch ($source) {
            case 'invoices':
            case 'aging':
                $f = $inv;
                if ($source === 'aging') {
                    $f['bucket'] = ['label' => 'Bucket Aging', 'expr' => '__BUCKET__', 'type' => 'text', 'group' => 'Kinerja'];
                }
                return $f;

            case 'payments':
                return [
                    'payment_no'   => ['label' => 'No. Pembayaran',   'expr' => 'p.payment_no',   'type' => 'text', 'group' => 'Pembayaran'],
                    'payment_date' => ['label' => 'Tanggal Bayar',    'expr' => 'p.payment_date', 'type' => 'date', 'group' => 'Pembayaran'],
                    'payment_month'=> ['label' => 'Bulan Bayar',      'expr' => "DATE_FORMAT(p.payment_date,'%Y-%m')", 'type' => 'text', 'group' => 'Pembayaran'],
                    'method'       => ['label' => 'Metode',           'expr' => 'p.method',       'type' => 'text', 'group' => 'Pembayaran'],
                    'payment_type' => ['label' => 'Jenis Pembayaran', 'expr' => 'p.payment_type', 'type' => 'text', 'group' => 'Pembayaran'],
                    'reference_no' => ['label' => 'No. Referensi',    'expr' => 'p.reference_no', 'type' => 'text', 'group' => 'Pembayaran'],
                    'bank'         => ['label' => 'Bank',             'expr' => 'b.name',         'type' => 'text', 'group' => 'Pembayaran'],
                    'status'       => ['label' => 'Status',           'expr' => 'p.status',       'type' => 'text', 'group' => 'Pembayaran'],
                    'approval_status' => ['label' => 'Status Persetujuan', 'expr' => 'p.approval_status', 'type' => 'text', 'group' => 'Pembayaran'],
                    'alokasi'      => ['label' => 'Nilai Alokasi',    'expr' => 'pa.amount',      'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'invoice_no'   => ['label' => 'No. Invoice',      'expr' => 'i.invoice_no',   'type' => 'text', 'group' => 'Invoice'],
                    'invoice_date' => ['label' => 'Tanggal Invoice',  'expr' => 'i.invoice_date', 'type' => 'date', 'group' => 'Invoice'],
                    'due_date'     => ['label' => 'Jatuh Tempo',      'expr' => 'i.due_date',     'type' => 'date', 'group' => 'Invoice'],
                    'net_amount'   => ['label' => 'Total Tagihan',    'expr' => 'i.net_amount',   'type' => 'money','group' => 'Invoice', 'agg' => true],
                    'outstanding_amount' => ['label' => 'Saldo Piutang', 'expr' => 'i.outstanding_amount', 'type' => 'money', 'group' => 'Invoice', 'agg' => true],
                    'hari_penagihan' => ['label' => 'Hari Penagihan', 'expr' => 'DATEDIFF(p.payment_date, COALESCE(i.sent_date, i.invoice_date))', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true],
                    'hari_terlambat' => ['label' => 'Hari Terlambat', 'expr' => 'GREATEST(0, DATEDIFF(p.payment_date, i.due_date))', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true],
                    'customer'     => ['label' => 'Customer',         'expr' => 'c.name',         'type' => 'text', 'group' => 'Dimensi'],
                    'area'         => ['label' => 'Area',             'expr' => 'a.name',         'type' => 'text', 'group' => 'Dimensi'],
                    'business_unit'=> ['label' => 'Unit Bisnis',      'expr' => 'bu.name',        'type' => 'text', 'group' => 'Dimensi'],
                    'salesperson'  => ['label' => 'Sales',            'expr' => 's.name',         'type' => 'text', 'group' => 'Dimensi'],
                    'notes'        => ['label' => 'Catatan',          'expr' => 'p.notes',        'type' => 'text', 'group' => 'Lain'],
                    'jumlah_pembayaran' => ['label' => 'Jumlah Transaksi', 'expr' => 'p.id', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true, 'count_only' => true],
                ];

            case 'collection':
                return [
                    'followup_date' => ['label' => 'Tanggal Follow-up', 'expr' => 'fu.followup_date', 'type' => 'date', 'group' => 'Penagihan'],
                    'followup_month'=> ['label' => 'Bulan Follow-up',   'expr' => "DATE_FORMAT(fu.followup_date,'%Y-%m')", 'type' => 'text', 'group' => 'Penagihan'],
                    'method'        => ['label' => 'Metode',            'expr' => 'fu.method',        'type' => 'text', 'group' => 'Penagihan'],
                    'status'        => ['label' => 'Status Penagihan',  'expr' => 'cs.name',          'type' => 'text', 'group' => 'Penagihan'],
                    'priority'      => ['label' => 'Prioritas',         'expr' => 'fu.priority',      'type' => 'text', 'group' => 'Penagihan'],
                    'officer'       => ['label' => 'Petugas Penagihan', 'expr' => 'cofc.name',        'type' => 'text', 'group' => 'Penagihan'],
                    'contact_person'=> ['label' => 'Narahubung',        'expr' => 'fu.contact_person','type' => 'text', 'group' => 'Penagihan'],
                    'contact_phone' => ['label' => 'Telepon',           'expr' => 'fu.contact_phone', 'type' => 'text', 'group' => 'Penagihan'],
                    'result'        => ['label' => 'Hasil',             'expr' => 'fu.result',        'type' => 'text', 'group' => 'Penagihan'],
                    'next_followup_date' => ['label' => 'Follow-up Berikutnya', 'expr' => 'fu.next_followup_date', 'type' => 'date', 'group' => 'Penagihan'],
                    'invoice_no'    => ['label' => 'No. Invoice',       'expr' => 'i.invoice_no',     'type' => 'text', 'group' => 'Invoice'],
                    'due_date'      => ['label' => 'Jatuh Tempo',       'expr' => 'i.due_date',       'type' => 'date', 'group' => 'Invoice'],
                    'outstanding_amount' => ['label' => 'Saldo Piutang','expr' => 'i.outstanding_amount', 'type' => 'money', 'group' => 'Invoice', 'agg' => true],
                    'customer'      => ['label' => 'Customer',          'expr' => 'c.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'area'          => ['label' => 'Area',              'expr' => 'a.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'promise_date'  => ['label' => 'Tanggal Janji Bayar','expr' => 'ptp.promise_date','type' => 'date', 'group' => 'Janji Bayar'],
                    'promise_amount'=> ['label' => 'Nilai Janji Bayar', 'expr' => 'ptp.promise_amount','type' => 'money','group' => 'Janji Bayar', 'agg' => true],
                    'promise_status'=> ['label' => 'Status Janji Bayar','expr' => 'ptp.status',       'type' => 'text', 'group' => 'Janji Bayar'],
                    'jumlah_followup' => ['label' => 'Jumlah Follow-up','expr' => 'fu.id', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true, 'count_only' => true],
                ];

            case 'revisions':
                return [
                    'rf_no'        => ['label' => 'Nomor RF',           'expr' => 'r.rf_no',          'type' => 'text', 'group' => 'RF'],
                    'rf_date'      => ['label' => 'Tanggal RF',         'expr' => 'r.rf_date',        'type' => 'date', 'group' => 'RF'],
                    'rf_month'     => ['label' => 'Bulan RF',           'expr' => "DATE_FORMAT(r.rf_date,'%Y-%m')", 'type' => 'text', 'group' => 'RF'],
                    'old_invoice_no' => ['label' => 'Invoice Lama',     'expr' => 'r.old_invoice_no', 'type' => 'text', 'group' => 'RF'],
                    'new_invoice_no' => ['label' => 'Invoice Baru',     'expr' => 'r.new_invoice_no', 'type' => 'text', 'group' => 'RF'],
                    'revision_type'=> ['label' => 'Jenis Revisi',       'expr' => 'rt.name',          'type' => 'text', 'group' => 'RF'],
                    'action'       => ['label' => 'Tindakan Invoice Lama','expr' => 'r.old_invoice_action','type' => 'text','group' => 'RF'],
                    'status'       => ['label' => 'Status Revisi',      'expr' => 'r.status',         'type' => 'text', 'group' => 'RF'],
                    'original_amount' => ['label' => 'Nominal Lama',    'expr' => 'r.original_amount','type' => 'money','group' => 'Nilai', 'agg' => true],
                    'revised_amount'  => ['label' => 'Nominal Baru',    'expr' => 'r.revised_amount', 'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'selisih'      => ['label' => 'Selisih Nominal',    'expr' => '(r.revised_amount - r.original_amount)', 'type' => 'money', 'group' => 'Nilai', 'agg' => true],
                    'reason'       => ['label' => 'Alasan Revisi',      'expr' => 'r.reason',         'type' => 'text', 'group' => 'RF'],
                    'customer'     => ['label' => 'Customer',           'expr' => 'c.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'area'         => ['label' => 'Area',               'expr' => 'a.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'jumlah_revisi'=> ['label' => 'Jumlah Revisi',      'expr' => 'r.id', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true, 'count_only' => true],
                ];

            case 'deductions':
                return [
                    'invoice_no'   => ['label' => 'No. Invoice',        'expr' => 'i.invoice_no',     'type' => 'text', 'group' => 'Invoice'],
                    'invoice_date' => ['label' => 'Tanggal Invoice',    'expr' => 'i.invoice_date',   'type' => 'date', 'group' => 'Invoice'],
                    'invoice_month'=> ['label' => 'Bulan Invoice',      'expr' => "DATE_FORMAT(i.invoice_date,'%Y-%m')", 'type' => 'text', 'group' => 'Invoice'],
                    'tax_invoice_no' => ['label' => 'No. Faktur Pajak', 'expr' => 'i.tax_invoice_no', 'type' => 'text', 'group' => 'Invoice'],
                    'customer'     => ['label' => 'Customer',           'expr' => 'c.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'customer_npwp'=> ['label' => 'NPWP Customer',      'expr' => 'c.npwp',           'type' => 'text', 'group' => 'Dimensi'],
                    'area'         => ['label' => 'Area',               'expr' => 'a.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'deduction_type' => ['label' => 'Jenis Potongan',   'expr' => 'dt.name',          'type' => 'text', 'group' => 'Potongan'],
                    'deduction_code' => ['label' => 'Kode Potongan',    'expr' => 'dt.code',          'type' => 'text', 'group' => 'Potongan'],
                    'base_amount'  => ['label' => 'Dasar Pengenaan',    'expr' => 'd.base_amount',    'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'rate'         => ['label' => 'Tarif (%)',          'expr' => 'd.rate',           'type' => 'dec',  'group' => 'Nilai', 'agg' => true],
                    'amount'       => ['label' => 'Nilai Potongan',     'expr' => 'd.amount',         'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'gross_amount' => ['label' => 'Nominal Invoice',    'expr' => 'i.gross_amount',   'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'notes'        => ['label' => 'Catatan',            'expr' => 'd.notes',          'type' => 'text', 'group' => 'Lain'],
                    'jumlah_potongan' => ['label' => 'Jumlah Baris',    'expr' => 'd.id', 'type' => 'int', 'group' => 'Kinerja', 'agg' => true, 'count_only' => true],
                ];

            case 'customers':
                return [
                    'code'         => ['label' => 'Kode Customer',      'expr' => 'c.code',           'type' => 'text', 'group' => 'Customer'],
                    'name'         => ['label' => 'Nama Customer',      'expr' => 'c.name',           'type' => 'text', 'group' => 'Customer'],
                    'npwp'         => ['label' => 'NPWP',               'expr' => 'c.npwp',           'type' => 'text', 'group' => 'Customer'],
                    'contact_person' => ['label' => 'Narahubung',       'expr' => 'c.contact_person', 'type' => 'text', 'group' => 'Customer'],
                    'phone'        => ['label' => 'Telepon',            'expr' => 'c.phone',          'type' => 'text', 'group' => 'Customer'],
                    'email'        => ['label' => 'Email',              'expr' => 'c.email',          'type' => 'text', 'group' => 'Customer'],
                    'status'       => ['label' => 'Status Customer',    'expr' => 'c.status',         'type' => 'text', 'group' => 'Customer'],
                    'risk_category'=> ['label' => 'Kategori Risiko',    'expr' => 'c.risk_category',  'type' => 'text', 'group' => 'Customer'],
                    'credit_limit' => ['label' => 'Limit Kredit',       'expr' => 'c.credit_limit',   'type' => 'money','group' => 'Nilai', 'agg' => true],
                    'payment_term_days' => ['label' => 'Termin (hari)', 'expr' => 'c.payment_term_days', 'type' => 'int', 'group' => 'Customer', 'agg' => true],
                    'area'         => ['label' => 'Area',               'expr' => 'a.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'business_unit'=> ['label' => 'Unit Bisnis',        'expr' => 'bu.name',          'type' => 'text', 'group' => 'Dimensi'],
                    'salesperson'  => ['label' => 'Sales',              'expr' => 's.name',           'type' => 'text', 'group' => 'Dimensi'],
                    'collection_officer' => ['label' => 'Petugas Penagihan', 'expr' => 'co.name',     'type' => 'text', 'group' => 'Dimensi'],
                    'jumlah_invoice'   => ['label' => 'Jumlah Invoice', 'expr' => 'ag.jumlah_invoice','type' => 'int',  'group' => 'Piutang', 'agg' => true],
                    'total_bruto'      => ['label' => 'Total Nominal',  'expr' => 'ag.total_bruto',   'type' => 'money','group' => 'Piutang', 'agg' => true],
                    'total_bersih'     => ['label' => 'Total Tagihan',  'expr' => 'ag.total_bersih',  'type' => 'money','group' => 'Piutang', 'agg' => true],
                    'total_bayar'      => ['label' => 'Total Pembayaran','expr' => 'ag.total_bayar',  'type' => 'money','group' => 'Piutang', 'agg' => true],
                    'total_saldo'      => ['label' => 'Saldo Piutang',  'expr' => 'ag.total_saldo',   'type' => 'money','group' => 'Piutang', 'agg' => true],
                    'total_terlambat'  => ['label' => 'Saldo Terlambat','expr' => 'ag.total_terlambat','type' => 'money','group' => 'Piutang', 'agg' => true],
                    'rata_hari_penagihan' => ['label' => 'Rata-rata Hari Penagihan', 'expr' => 'ag.rata_hari', 'type' => 'dec', 'group' => 'Kinerja', 'agg' => true],
                    'maks_umur'        => ['label' => 'Umur Piutang Tertua', 'expr' => 'ag.maks_umur','type' => 'int',  'group' => 'Kinerja', 'agg' => true],
                    'pemakaian_limit'  => ['label' => 'Pemakaian Limit (%)', 'expr' => 'CASE WHEN c.credit_limit > 0 THEN ag.total_saldo / c.credit_limit * 100 END', 'type' => 'dec', 'group' => 'Kinerja', 'agg' => true],
                ];
        }
        return [];
    }

    /** Klausa FROM/JOIN per sumber data. */
    private static function fromClause(string $source): string
    {
        return match ($source) {
            'payments' => "FROM payment_allocations pa
                JOIN payments p  ON p.id = pa.payment_id
                JOIN invoices i  ON i.id = pa.invoice_id
                LEFT JOIN banks b ON b.id = p.bank_id
                LEFT JOIN customers c ON c.id = i.customer_id
                LEFT JOIN areas a ON a.id = i.area_id
                LEFT JOIN business_units bu ON bu.id = i.business_unit_id
                LEFT JOIN salespersons s ON s.id = i.salesperson_id",
            'collection' => "FROM collection_followups fu
                LEFT JOIN invoices i ON i.id = fu.invoice_id
                LEFT JOIN customers c ON c.id = fu.customer_id
                LEFT JOIN areas a ON a.id = i.area_id
                LEFT JOIN collection_statuses cs ON cs.id = fu.collection_status_id
                LEFT JOIN collection_officers cofc ON cofc.id = fu.collection_officer_id
                LEFT JOIN promise_to_pay ptp ON ptp.followup_id = fu.id AND ptp.deleted_at IS NULL",
            'revisions' => "FROM invoice_revisions r
                LEFT JOIN revision_types rt ON rt.id = r.revision_type_id
                LEFT JOIN invoices i ON i.id = r.old_invoice_id
                LEFT JOIN customers c ON c.id = i.customer_id
                LEFT JOIN areas a ON a.id = i.area_id",
            'deductions' => "FROM invoice_deductions d
                JOIN invoices i ON i.id = d.invoice_id
                JOIN deduction_types dt ON dt.id = d.deduction_type_id
                LEFT JOIN customers c ON c.id = i.customer_id
                LEFT JOIN areas a ON a.id = i.area_id",
            'customers' => "FROM customers c
                LEFT JOIN areas a ON a.id = c.area_id
                LEFT JOIN business_units bu ON bu.id = c.business_unit_id
                LEFT JOIN salespersons s ON s.id = c.salesperson_id
                LEFT JOIN collection_officers co ON co.id = c.collection_officer_id
                LEFT JOIN (
                    SELECT customer_id,
                           COUNT(*) AS jumlah_invoice,
                           SUM(gross_amount) AS total_bruto,
                           SUM(net_amount) AS total_bersih,
                           SUM(paid_amount) AS total_bayar,
                           SUM(outstanding_amount) AS total_saldo,
                           SUM(CASE WHEN payment_status_code='TERLAMBAT' THEN outstanding_amount ELSE 0 END) AS total_terlambat,
                           AVG(CASE WHEN payment_status_code='LUNAS' THEN collection_days END) AS rata_hari,
                           MAX(CASE WHEN outstanding_amount > 0 THEN aging_days END) AS maks_umur
                      FROM invoices WHERE deleted_at IS NULL AND doc_status='active'
                     GROUP BY customer_id
                ) ag ON ag.customer_id = c.id",
            default => \App\Models\Invoice::SELECT_JOIN,
        };
    }

    /** Klausa WHERE dasar (soft delete + pembatasan cakupan data). */
    private static function baseWhere(string $source): array
    {
        $w = [];
        $p = [];
        switch ($source) {
            case 'payments':    $w[] = 'pa.deleted_at IS NULL AND p.deleted_at IS NULL AND i.deleted_at IS NULL'; break;
            case 'collection':  $w[] = 'fu.deleted_at IS NULL'; break;
            case 'revisions':   $w[] = 'r.deleted_at IS NULL'; break;
            case 'deductions':  $w[] = 'd.deleted_at IS NULL AND i.deleted_at IS NULL'; break;
            case 'customers':   $w[] = 'c.deleted_at IS NULL'; break;
            default:            $w[] = 'i.deleted_at IS NULL';
        }
        if ($source !== 'customers') {
            [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
            if ($scopeSql !== '1=1') { $w[] = '(' . $scopeSql . ')'; $p = $scopeParams; }
        }
        return [implode(' AND ', $w), $p];
    }

    // ==================================================================
    // OPERATOR & AGREGASI
    // ==================================================================

    public static function operators(): array
    {
        return [
            'eq'          => ['label' => 'Sama dengan',        'args' => 1],
            'neq'         => ['label' => 'Tidak sama dengan',  'args' => 1],
            'contains'    => ['label' => 'Mengandung',         'args' => 1],
            'not_contains'=> ['label' => 'Tidak mengandung',   'args' => 1],
            'starts'      => ['label' => 'Diawali dengan',     'args' => 1],
            'ends'        => ['label' => 'Diakhiri dengan',    'args' => 1],
            'gt'          => ['label' => 'Lebih besar dari',   'args' => 1],
            'gte'         => ['label' => 'Lebih besar atau sama dengan', 'args' => 1],
            'lt'          => ['label' => 'Lebih kecil dari',   'args' => 1],
            'lte'         => ['label' => 'Lebih kecil atau sama dengan', 'args' => 1],
            'between'     => ['label' => 'Di antara',          'args' => 2],
            'empty'       => ['label' => 'Kosong',             'args' => 0],
            'not_empty'   => ['label' => 'Tidak kosong',       'args' => 0],
            'in'          => ['label' => 'Salah satu dari (pisahkan dengan koma)', 'args' => 1],
            'not_in'      => ['label' => 'Bukan salah satu dari', 'args' => 1],
        ];
    }

    public static function aggregates(): array
    {
        return [
            'none'   => 'Tanpa Agregasi (detail)',
            'sum'    => 'Jumlah (Sum)',
            'count'  => 'Cacah (Count)',
            'dcount' => 'Cacah Unik (Distinct Count)',
            'avg'    => 'Rata-rata (Average)',
            'min'    => 'Nilai Terkecil (Min)',
            'max'    => 'Nilai Terbesar (Max)',
            'pct'    => 'Persentase terhadap Total',
            'wavg'   => 'Rata-rata Tertimbang (bobot nilai tagihan)',
        ];
    }

    public static function chartTypes(): array
    {
        return ['none' => 'Tanpa Chart', 'bar' => 'Batang Vertikal', 'horizontalBar' => 'Batang Horizontal',
                'line' => 'Garis', 'area' => 'Area', 'pie' => 'Lingkaran', 'doughnut' => 'Donat',
                'stackedBar' => 'Batang Bertumpuk'];
    }

    // ==================================================================
    // EKSEKUSI
    // ==================================================================

    /**
     * Menjalankan definisi laporan.
     *
     * @param array $def [
     *   'data_source' => string,
     *   'columns'     => [['field'=>string,'aggregate'=>string,'label'=>?string,'sort_dir'=>'asc|desc','sort_priority'=>int], ...],
     *   'filters'     => [['field'=>string,'operator'=>string,'value1'=>mixed,'value2'=>mixed,'conjunction'=>'AND|OR'], ...],
     *   'group_by'    => [string, ...],
     *   'pivot'       => ['column'=>string,'value'=>string,'aggregate'=>string]|null,
     *   'options'     => ['limit'=>int,'subtotal'=>bool,'grand_total'=>bool,'as_of'=>string,'aging_config_id'=>int],
     * ]
     * @return array{columns:array,rows:array,total:array,subtotals:array,sql:string,params:array,pivot_columns:array,jumlah_baris:int}
     */
    public static function execute(array $def, int $maxRows = 0): array
    {
        $db = Database::i();
        $source = $def['data_source'] ?? 'invoices';
        if (!isset(self::dataSources()[$source])) $source = 'invoices';
        $fields = self::fields($source);
        $opts = $def['options'] ?? [];
        $asOf = $opts['as_of'] ?? Dates::today();
        $maxRows = $maxRows ?: (int)($opts['limit'] ?? Settings::int('laporan.maks_baris_export', 50000));

        // --- Ekspresi bucket aging (khusus sumber "aging")
        $bucketExpr = null; $bucketLabels = [];
        if ($source === 'aging') {
            $cfg = AgingService::config($opts['aging_config_id'] ?? null);
            $case = 'CASE';
            foreach ($cfg['buckets'] as $b) {
                $daysExpr = match ($cfg['reference']) {
                    'invoice_date' => "DATEDIFF(:rb_asof, i.invoice_date)",
                    'sent_date'    => "DATEDIFF(:rb_asof, COALESCE(i.sent_date, i.invoice_date))",
                    default        => "DATEDIFF(:rb_asof, i.due_date)",
                };
                $from = (int)$b['from_days'];
                $to = $b['to_days'] === null ? null : (int)$b['to_days'];
                $cond = $to === null ? "$daysExpr >= $from" : "$daysExpr BETWEEN $from AND $to";
                $case .= " WHEN ($cond) THEN " . $db->pdo()->quote($b['label']);
                $bucketLabels[] = $b['label'];
            }
            $case .= " ELSE 'Lainnya' END";
            $bucketExpr = $case;
        }

        $resolveExpr = function (string $fieldKey) use ($fields, $bucketExpr): ?string {
            if (!isset($fields[$fieldKey])) return null;
            $e = $fields[$fieldKey]['expr'];
            return $e === '__BUCKET__' ? ($bucketExpr ?? "'-'") : $e;
        };

        // --- Kolom
        $columns = [];
        foreach ($def['columns'] ?? [] as $c) {
            $key = (string)($c['field'] ?? '');
            if (!isset($fields[$key])) continue;
            $agg = (string)($c['aggregate'] ?? 'none');
            if (!isset(self::aggregates()[$agg])) $agg = 'none';
            $columns[] = [
                'field' => $key,
                'aggregate' => $agg,
                'label' => trim((string)($c['label'] ?? '')) !== ''
                           ? (string)$c['label']
                           : ($fields[$key]['label'] . ($agg !== 'none' ? ' (' . self::aggLabelShort($agg) . ')' : '')),
                'type' => $agg === 'count' || $agg === 'dcount' ? 'int' : ($agg === 'pct' ? 'pct' : $fields[$key]['type']),
                'sort_dir' => in_array(strtolower((string)($c['sort_dir'] ?? '')), ['asc', 'desc'], true) ? strtolower((string)$c['sort_dir']) : '',
                'sort_priority' => (int)($c['sort_priority'] ?? 0),
                'format' => $c['format'] ?? null,
            ];
        }
        if (!$columns) {
            foreach (array_slice(array_keys($fields), 0, 8) as $k) {
                $columns[] = ['field' => $k, 'aggregate' => 'none', 'label' => $fields[$k]['label'],
                              'type' => $fields[$k]['type'], 'sort_dir' => '', 'sort_priority' => 0, 'format' => null];
            }
        }

        // --- Grouping
        $groupBy = array_values(array_filter((array)($def['group_by'] ?? []), fn($g) => isset($fields[$g])));
        $isAggregated = $groupBy || array_filter($columns, fn($c) => $c['aggregate'] !== 'none');

        // --- SELECT
        $params = [];
        if ($bucketExpr !== null) $params['rb_asof'] = $asOf;
        $select = [];
        $groupCols = [];
        foreach ($groupBy as $gi => $g) {
            $alias = 'g' . $gi;
            $select[] = $resolveExpr($g) . ' AS `' . $alias . '`';
            $groupCols[$g] = $alias;
        }
        foreach ($columns as $ci => $c) {
            $alias = 'c' . $ci;
            $expr = $resolveExpr($c['field']);
            if ($expr === null) continue;
            if ($c['aggregate'] === 'none') {
                // Dalam mode agregat, kolom non-agregat yang bukan grouping diambil nilai pertamanya
                $sql = ($isAggregated && !isset($groupCols[$c['field']])) ? "MIN($expr)" : $expr;
            } else {
                $sql = self::aggSql($c['aggregate'], $expr, $source);
            }
            $select[] = $sql . ' AS `' . $alias . '`';
            $columns[$ci]['alias'] = $alias;
        }

        // --- Pivot
        $pivotCols = [];
        $pivot = $def['pivot'] ?? null;
        if ($pivot && !empty($pivot['column']) && isset($fields[$pivot['column']]) && !empty($pivot['value']) && isset($fields[$pivot['value']])) {
            $pcExpr = $resolveExpr($pivot['column']);
            $pvExpr = $resolveExpr($pivot['value']);
            $pagg = isset(self::aggregates()[$pivot['aggregate'] ?? 'sum']) ? ($pivot['aggregate'] ?? 'sum') : 'sum';

            [$bw, $bp] = self::baseWhere($source);
            [$fw, $fp] = self::buildFilterWhere($def['filters'] ?? [], $fields, $resolveExpr);
            $distSql = 'SELECT DISTINCT ' . $pcExpr . ' AS v ' . self::fromClause($source)
                     . ' WHERE ' . $bw . ($fw ? " AND ($fw)" : '') . ' ORDER BY v LIMIT 40';
            $vals = $db->column($distSql, array_merge($params, $bp, $fp));
            foreach ($vals as $vi => $v) {
                if ($v === null || $v === '') continue;
                $alias = 'p' . $vi;
                $ph = 'pv' . $vi;
                $select[] = self::aggSql($pagg, "CASE WHEN $pcExpr = :$ph THEN $pvExpr END", $source) . ' AS `' . $alias . '`';
                $params[$ph] = $v;
                $pivotCols[] = ['alias' => $alias, 'label' => (string)$v,
                                'type' => $pagg === 'count' || $pagg === 'dcount' ? 'int' : $fields[$pivot['value']]['type']];
            }
        }

        // --- WHERE
        [$baseWhere, $baseParams] = self::baseWhere($source);
        $params = array_merge($params, $baseParams);
        [$filterWhere, $filterParams] = self::buildFilterWhere($def['filters'] ?? [], $fields, $resolveExpr);
        $params = array_merge($params, $filterParams);
        $where = $baseWhere . ($filterWhere ? " AND ($filterWhere)" : '');
        if ($source === 'aging') {
            $where .= " AND i.doc_status = 'active' AND i.outstanding_amount > " . (float)Settings::float('ar.toleransi_lunas', 1);
        }

        // --- GROUP BY
        $groupSql = '';
        if ($groupBy) {
            $groupSql = ' GROUP BY ' . implode(', ', array_map(fn($g) => $resolveExpr($g), $groupBy));
        } elseif (array_filter($columns, fn($c) => $c['aggregate'] !== 'none') && !$groupBy) {
            $hasNonAgg = array_filter($columns, fn($c) => $c['aggregate'] === 'none');
            if (!$hasNonAgg) $groupSql = '';   // agregat tunggal (satu baris)
        }

        // --- ORDER BY
        $orderParts = [];
        $sorted = $columns;
        usort($sorted, fn($a, $b) => ($a['sort_priority'] ?: 99) <=> ($b['sort_priority'] ?: 99));
        foreach ($sorted as $c) {
            if ($c['sort_dir'] === '') continue;
            $orderParts[] = '`' . $c['alias'] . '` ' . strtoupper($c['sort_dir']);
        }
        if (!$orderParts && $groupBy) $orderParts[] = '`g0` ASC';
        $orderSql = $orderParts ? ' ORDER BY ' . implode(', ', $orderParts) : '';

        $sql = 'SELECT ' . implode(",\n       ", $select) . "\n" . self::fromClause($source)
             . "\n WHERE " . $where . $groupSql . $orderSql;

        $countSql = 'SELECT COUNT(*) FROM (' . $sql . ') _c';
        $jumlahBaris = (int)$db->value($countSql, $params);

        $rows = $db->all($sql . ' LIMIT ' . (int)$maxRows, $params);

        // --- Grand total
        $total = [];
        foreach ($columns as $c) {
            if (!in_array($c['aggregate'], ['sum', 'count', 'dcount'], true) && !in_array($c['type'], ['money', 'int', 'dec'], true)) continue;
            if ($c['aggregate'] === 'avg' || $c['aggregate'] === 'wavg' || $c['aggregate'] === 'min' || $c['aggregate'] === 'max') continue;
            $s = 0.0;
            foreach ($rows as $r) $s += (float)($r[$c['alias']] ?? 0);
            $total[$c['alias']] = $s;
        }
        foreach ($pivotCols as $pc) {
            $s = 0.0;
            foreach ($rows as $r) $s += (float)($r[$pc['alias']] ?? 0);
            $total[$pc['alias']] = $s;
        }

        // --- Subtotal per kelompok pertama
        $subtotals = [];
        if (!empty($opts['subtotal']) && $groupBy) {
            $keyAlias = 'g0';
            $acc = [];
            foreach ($rows as $r) {
                $k = (string)($r[$keyAlias] ?? '');
                if (!isset($acc[$k])) $acc[$k] = ['label' => $k, 'jumlah_baris' => 0, 'nilai' => []];
                $acc[$k]['jumlah_baris']++;
                foreach ($columns as $c) {
                    if (!in_array($c['type'], ['money', 'int', 'dec'], true)) continue;
                    $acc[$k]['nilai'][$c['alias']] = ($acc[$k]['nilai'][$c['alias']] ?? 0) + (float)($r[$c['alias']] ?? 0);
                }
                foreach ($pivotCols as $pc) {
                    $acc[$k]['nilai'][$pc['alias']] = ($acc[$k]['nilai'][$pc['alias']] ?? 0) + (float)($r[$pc['alias']] ?? 0);
                }
            }
            $subtotals = $acc;
        }

        return [
            'columns' => $columns, 'group_by' => $groupBy, 'group_aliases' => $groupCols,
            'pivot_columns' => $pivotCols, 'rows' => $rows, 'total' => $total, 'subtotals' => $subtotals,
            'jumlah_baris' => $jumlahBaris, 'terpotong' => $jumlahBaris > count($rows),
            'sql' => $sql, 'params' => $params, 'fields' => $fields, 'source' => $source, 'as_of' => $asOf,
        ];
    }

    private static function aggSql(string $agg, string $expr, string $source): string
    {
        $weight = match ($source) {
            'payments' => 'pa.amount',
            'customers' => 'ag.total_bersih',
            default => 'i.net_amount',
        };
        return match ($agg) {
            'sum'    => "SUM($expr)",
            'count'  => "COUNT($expr)",
            'dcount' => "COUNT(DISTINCT $expr)",
            'avg'    => "AVG($expr)",
            'min'    => "MIN($expr)",
            'max'    => "MAX($expr)",
            'wavg'   => "SUM(($expr) * COALESCE($weight,0)) / NULLIF(SUM(COALESCE($weight,0)),0)",
            'pct'    => "SUM($expr)",   // persentase dihitung saat render terhadap grand total
            default  => $expr,
        };
    }

    public static function aggLabelShort(string $agg): string
    {
        return ['sum' => 'Total', 'count' => 'Cacah', 'dcount' => 'Unik', 'avg' => 'Rata-rata',
                'min' => 'Min', 'max' => 'Maks', 'wavg' => 'Rata Tertimbang', 'pct' => '%'][$agg] ?? $agg;
    }

    /** Membangun klausa WHERE dari daftar filter (whitelist operator & field). */
    private static function buildFilterWhere(array $filters, array $fields, callable $resolveExpr): array
    {
        $ops = self::operators();
        $parts = [];
        $params = [];
        $i = 0;
        foreach ($filters as $f) {
            $key = (string)($f['field'] ?? '');
            $op  = (string)($f['operator'] ?? 'eq');
            if (!isset($fields[$key]) || !isset($ops[$op])) continue;
            $expr = $resolveExpr($key);
            if ($expr === null) continue;
            $v1 = $f['value1'] ?? null;
            $v2 = $f['value2'] ?? null;
            $type = $fields[$key]['type'];
            $conj = strtoupper((string)($f['conjunction'] ?? 'AND')) === 'OR' ? 'OR' : 'AND';
            $i++;
            $a = 'f' . $i . 'a';
            $b = 'f' . $i . 'b';

            $cast = function ($v) use ($type) {
                if ($v === null || $v === '') return null;
                return match ($type) {
                    'money', 'dec' => Num::parse((string)$v),
                    'int'          => (int)Num::parse((string)$v),
                    'date'         => Dates::parse((string)$v),
                    default        => (string)$v,
                };
            };

            switch ($op) {
                case 'empty':
                    $sql = "($expr IS NULL OR $expr = '')"; break;
                case 'not_empty':
                    $sql = "($expr IS NOT NULL AND $expr <> '')"; break;
                case 'contains':
                    $sql = "$expr LIKE :$a"; $params[$a] = '%' . $v1 . '%'; break;
                case 'not_contains':
                    $sql = "($expr IS NULL OR $expr NOT LIKE :$a)"; $params[$a] = '%' . $v1 . '%'; break;
                case 'starts':
                    $sql = "$expr LIKE :$a"; $params[$a] = $v1 . '%'; break;
                case 'ends':
                    $sql = "$expr LIKE :$a"; $params[$a] = '%' . $v1; break;
                case 'between':
                    $x = $cast($v1); $y = $cast($v2);
                    if ($x === null || $y === null) { $i--; continue 2; }
                    $sql = "$expr BETWEEN :$a AND :$b"; $params[$a] = $x; $params[$b] = $y; break;
                case 'in':
                case 'not_in':
                    $vals = is_array($v1) ? $v1 : array_map('trim', explode(',', (string)$v1));
                    $vals = array_values(array_filter($vals, fn($x) => $x !== '' && $x !== null));
                    if (!$vals) { $i--; continue 2; }
                    $ph = [];
                    foreach ($vals as $vi => $v) { $k = $a . '_' . $vi; $ph[] = ':' . $k; $params[$k] = $cast($v); }
                    $sql = "$expr " . ($op === 'in' ? 'IN' : 'NOT IN') . ' (' . implode(',', $ph) . ')';
                    break;
                case 'neq':
                    $x = $cast($v1);
                    if ($x === null) { $i--; continue 2; }
                    $sql = "($expr <> :$a OR $expr IS NULL)"; $params[$a] = $x; break;
                default:
                    $mapOp = ['eq' => '=', 'gt' => '>', 'gte' => '>=', 'lt' => '<', 'lte' => '<='][$op] ?? '=';
                    $x = $cast($v1);
                    if ($x === null) { $i--; continue 2; }
                    $sql = "$expr $mapOp :$a"; $params[$a] = $x; break;
            }
            $parts[] = ['conj' => $conj, 'sql' => $sql];
        }
        if (!$parts) return ['', []];
        $out = '';
        foreach ($parts as $idx => $p) {
            $out .= ($idx === 0 ? '' : ' ' . $p['conj'] . ' ') . $p['sql'];
        }
        return [$out, $params];
    }

    // ==================================================================
    // TEMPLATE
    // ==================================================================

    /** Memuat template beserta kolom & filternya menjadi definisi laporan. */
    public static function loadTemplate(int $templateId): ?array
    {
        $db = Database::i();
        $t = $db->one('SELECT * FROM report_templates WHERE id = :id AND deleted_at IS NULL', ['id' => $templateId]);
        if (!$t) return null;
        $cols = $db->all('SELECT * FROM report_template_columns WHERE report_template_id = :id ORDER BY sort_order', ['id' => $templateId]);
        $filters = $db->all('SELECT * FROM report_template_filters WHERE report_template_id = :id ORDER BY sort_order', ['id' => $templateId]);
        return [
            'id' => (int)$t['id'], 'code' => $t['code'], 'name' => $t['name'], 'description' => $t['description'],
            'data_source' => $t['data_source'], 'chart_type' => $t['chart_type'],
            'chart_config' => arr($t['chart_config_json']),
            'group_by' => arr($t['group_by_json']),
            'pivot' => arr($t['pivot_json']) ?: null,
            'options' => arr($t['options_json']),
            'visibility' => $t['visibility'], 'owner_id' => $t['owner_id'], 'is_system' => (int)$t['is_system'],
            'columns' => array_map(fn($c) => [
                'field' => $c['field_key'], 'label' => $c['label'], 'aggregate' => $c['aggregate'],
                'sort_dir' => $c['sort_dir'], 'sort_priority' => (int)$c['sort_priority'],
                'format' => $c['format'], 'width' => $c['width'],
            ], $cols),
            'filters' => array_map(fn($f) => [
                'field' => $f['field_key'], 'operator' => $f['operator'], 'value1' => $f['value1'],
                'value2' => $f['value2'], 'conjunction' => $f['conjunction'], 'is_prompt' => (int)$f['is_prompt'],
            ], $filters),
        ];
    }

    /** Menyimpan definisi laporan sebagai template. */
    public static function saveTemplate(array $def, ?int $templateId = null): int
    {
        $db = Database::i();
        return $db->transaction(function () use ($db, $def, $templateId) {
            $data = [
                'name' => mb_substr((string)($def['name'] ?? 'Laporan Tanpa Nama'), 0, 180),
                'description' => mb_substr((string)($def['description'] ?? ''), 0, 500) ?: null,
                'data_source' => $def['data_source'] ?? 'invoices',
                'chart_type' => $def['chart_type'] ?? 'none',
                'chart_config_json' => json_encode($def['chart_config'] ?? [], JSON_UNESCAPED_UNICODE),
                'group_by_json' => json_encode(array_values((array)($def['group_by'] ?? [])), JSON_UNESCAPED_UNICODE),
                'pivot_json' => json_encode($def['pivot'] ?? null, JSON_UNESCAPED_UNICODE),
                'options_json' => json_encode($def['options'] ?? [], JSON_UNESCAPED_UNICODE),
                'visibility' => in_array($def['visibility'] ?? 'private', ['private', 'shared', 'public'], true) ? $def['visibility'] : 'private',
            ];
            if ($templateId) {
                $db->update('report_templates', $data, 'id = :pk', ['pk' => $templateId]);
                $db->exec('DELETE FROM report_template_columns WHERE report_template_id = ?', [$templateId]);
                $db->exec('DELETE FROM report_template_filters WHERE report_template_id = ?', [$templateId]);
            } else {
                $data['owner_id'] = Auth::id() ?: null;
                $data['code'] = $def['code'] ?? null;
                $templateId = $db->insert('report_templates', $data);
            }
            foreach (array_values((array)($def['columns'] ?? [])) as $i => $c) {
                if (empty($c['field'])) continue;
                $db->insert('report_template_columns', [
                    'report_template_id' => $templateId,
                    'field_key' => $c['field'],
                    'label' => $c['label'] ?? null,
                    'aggregate' => $c['aggregate'] ?? 'none',
                    'sort_order' => $i,
                    'sort_dir' => in_array(strtolower((string)($c['sort_dir'] ?? '')), ['asc', 'desc'], true) ? strtolower((string)$c['sort_dir']) : '',
                    'sort_priority' => (int)($c['sort_priority'] ?? 0),
                    'width' => $c['width'] ?? null,
                    'format' => $c['format'] ?? null,
                ]);
            }
            foreach (array_values((array)($def['filters'] ?? [])) as $i => $f) {
                if (empty($f['field'])) continue;
                $db->insert('report_template_filters', [
                    'report_template_id' => $templateId,
                    'field_key' => $f['field'],
                    'operator' => $f['operator'] ?? 'eq',
                    'value1' => is_array($f['value1'] ?? null) ? implode(',', $f['value1']) : ($f['value1'] ?? null),
                    'value2' => $f['value2'] ?? null,
                    'conjunction' => strtoupper((string)($f['conjunction'] ?? 'AND')) === 'OR' ? 'OR' : 'AND',
                    'sort_order' => $i,
                    'is_prompt' => !empty($f['is_prompt']) ? 1 : 0,
                ]);
            }
            return $templateId;
        });
    }

    /** Daftar template yang dapat diakses pengguna aktif. */
    public static function accessibleTemplates(?string $source = null): array
    {
        $db = Database::i();
        $uid = Auth::id();
        $w = 'rt.deleted_at IS NULL AND (rt.is_system = 1 OR rt.visibility IN ("public","shared") OR rt.owner_id = :u
              OR EXISTS (SELECT 1 FROM report_template_shares s WHERE s.report_template_id = rt.id AND s.user_id = :u2))';
        $p = ['u' => $uid, 'u2' => $uid];
        if ($source) { $w .= ' AND rt.data_source = :src'; $p['src'] = $source; }
        return $db->all(
            'SELECT rt.*, u.name AS owner_name,
                    (SELECT COUNT(*) FROM report_template_columns WHERE report_template_id = rt.id) AS jumlah_kolom
               FROM report_templates rt LEFT JOIN users u ON u.id = rt.owner_id
              WHERE ' . $w . ' ORDER BY rt.is_system DESC, rt.name',
            $p
        );
    }

    public static function duplicateTemplate(int $templateId, ?string $newName = null): int
    {
        $def = self::loadTemplate($templateId);
        if (!$def) throw new \RuntimeException('Template tidak ditemukan.');
        $def['name'] = $newName ?: ($def['name'] . ' (Salinan)');
        $def['code'] = null;
        $def['visibility'] = 'private';
        return self::saveTemplate($def, null);
    }

    /** Merender nilai sel sesuai tipe kolom. */
    public static function formatCell($value, string $type, ?float $grandTotal = null, string $aggregate = 'none'): string
    {
        if ($aggregate === 'pct' && $grandTotal !== null && $grandTotal != 0.0) {
            return Num::percent((float)$value / $grandTotal * 100, 2);
        }
        if ($value === null || $value === '') return '-';
        return match ($type) {
            'money' => Num::rupiah($value),
            'int'   => Num::fmt($value, 0),
            'dec'   => Num::fmt($value, 2),
            'pct'   => Num::percent($value, 2),
            'date'  => Dates::fmt(substr((string)$value, 0, 10)),
            default => (string)$value,
        };
    }
}
