<?php
namespace App\Services;

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

/**
 * Mesin perhitungan piutang.
 *
 * SEMUA nilai turunan dihitung di sini — tidak pernah diambil dari kolom rumus Excel.
 * Aturan mengacu pada dokumen 02_ARSITEKTUR_DAN_ATURAN.md (R-01 s/d R-07).
 */
class ArCalculator
{
    /** ------------------------------------------------------------------
     *  R-01 : DPP & PPN
     *  ------------------------------------------------------------------ */
    public static function dpp(float $gross): float
    {
        if (!Settings::bool('pajak.nominal_termasuk_ppn', true)) return round($gross, 2);
        $ppn = Settings::float('pajak.tarif_ppn', 11);
        return round($gross / (1 + $ppn / 100), 2);
    }

    public static function vat(float $gross): float
    {
        $dpp = self::dpp($gross);
        if (!Settings::bool('pajak.nominal_termasuk_ppn', true)) {
            return round($dpp * Settings::float('pajak.tarif_ppn', 11) / 100, 2);
        }
        return round($gross - $dpp, 2);
    }

    /** ------------------------------------------------------------------
     *  R-02 : Potongan
     *  ------------------------------------------------------------------ */
    public static function pph23(float $gross, ?float $rate = null): float
    {
        $rate = $rate ?? Settings::float('pajak.tarif_pph23', 2);
        return round(self::dpp($gross) * $rate / 100, 2);
    }

    /** Tarif efektif PPh 23 yang tersirat dari nominal potongan (untuk deteksi anomali). */
    public static function effectivePphRate(float $gross, float $pphAmount): ?float
    {
        $dpp = self::dpp($gross);
        if ($dpp <= 0 || $pphAmount == 0.0) return null;
        return round($pphAmount / $dpp * 100, 4);
    }

    /** Menghitung nominal potongan menurut definisi master deduction_types. */
    public static function deductionAmount(array $deductionType, float $gross, ?float $manualAmount = null, ?float $overrideRate = null): float
    {
        $rate = $overrideRate !== null ? $overrideRate : (float)($deductionType['rate'] ?? 0);
        return match ($deductionType['calc_type'] ?? 'manual') {
            'percent_of_dpp'   => round(self::dpp($gross) * $rate / 100, 2),
            'percent_of_gross' => round($gross * $rate / 100, 2),
            'fixed'            => round($rate, 2),
            default            => round((float)($manualAmount ?? 0), 2),
        };
    }

    /** ------------------------------------------------------------------
     *  R-03 : Nilai bersih
     *  ------------------------------------------------------------------ */
    public static function netAmount(float $gross, float $pph23, float $admin, float $other): float
    {
        return round($gross - $pph23 - $admin - $other, 2);
    }

    /** ------------------------------------------------------------------
     *  R-05 : Jatuh tempo
     *  ------------------------------------------------------------------ */

    /**
     * Menentukan tanggal dasar perhitungan jatuh tempo.
     * @return array{0:?string,1:string} [tanggal, basis yang dipakai]
     */
    public static function resolveBaseDate(array $inv, ?string $basis = null): array
    {
        $basis = $basis ?: Settings::str('ar.dasar_jatuh_tempo', 'sent_date');
        $map = [
            'invoice_date'     => $inv['invoice_date']     ?? null,
            'sent_date'        => $inv['sent_date']        ?? null,
            'received_date'    => $inv['received_date']    ?? null,
            'tax_invoice_date' => $inv['tax_invoice_date'] ?? null,
            'wr_date'          => $inv['wr_date']          ?? null,
            'manual'           => $inv['due_date']         ?? null,
        ];
        if ($basis === 'manual') return [$map['manual'], 'manual'];
        if (!empty($map[$basis])) return [$map[$basis], $basis];

        // Urutan cadangan bila tanggal utama kosong
        $fallback = array_filter(array_map('trim', explode(',', Settings::str('ar.fallback_jatuh_tempo', 'sent_date,invoice_date,wr_date'))));
        foreach ($fallback as $fb) {
            if (!empty($map[$fb])) return [$map[$fb], $fb];
        }
        return [$inv['invoice_date'] ?? null, 'invoice_date'];
    }

    /**
     * Termin pembayaran menurut hierarki 7 tingkat:
     * invoice → outlet → customer → kategori pendapatan (PMSC/By Case) → area → unit bisnis → global.
     */
    public static function resolveTermDays(array $inv): int
    {
        if (!empty($inv['payment_term_days']) && (int)$inv['payment_term_days'] > 0 && !empty($inv['_term_explicit'])) {
            return (int)$inv['payment_term_days'];
        }
        $db = Database::i();

        if (!empty($inv['payment_term_id'])) {
            $d = $db->value('SELECT days FROM payment_terms WHERE id = ? AND deleted_at IS NULL', [(int)$inv['payment_term_id']]);
            if ($d !== null) return (int)$d;
        }
        if (!empty($inv['outlet_id'])) {
            $d = $db->value('SELECT payment_term_days FROM outlets WHERE id = ? AND deleted_at IS NULL', [(int)$inv['outlet_id']]);
            if ($d !== null && (int)$d > 0) return (int)$d;
        }
        if (!empty($inv['customer_id'])) {
            $c = $db->one('SELECT payment_term_days, payment_term_id FROM customers WHERE id = ? AND deleted_at IS NULL', [(int)$inv['customer_id']]);
            if ($c) {
                if ($c['payment_term_days'] !== null && (int)$c['payment_term_days'] > 0) return (int)$c['payment_term_days'];
                if (!empty($c['payment_term_id'])) {
                    $d = $db->value('SELECT days FROM payment_terms WHERE id = ?', [(int)$c['payment_term_id']]);
                    if ($d !== null) return (int)$d;
                }
            }
        }
        if (!empty($inv['revenue_category_id'])) {
            $d = $db->value('SELECT payment_term_days FROM revenue_categories WHERE id = ? AND deleted_at IS NULL', [(int)$inv['revenue_category_id']]);
            if ($d !== null && (int)$d > 0) return (int)$d;
        }
        if (!empty($inv['area_id'])) {
            $d = $db->value('SELECT payment_term_days FROM areas WHERE id = ? AND deleted_at IS NULL', [(int)$inv['area_id']]);
            if ($d !== null && (int)$d > 0) return (int)$d;
        }
        if (!empty($inv['business_unit_id'])) {
            $d = $db->value('SELECT payment_term_days FROM business_units WHERE id = ? AND deleted_at IS NULL', [(int)$inv['business_unit_id']]);
            if ($d !== null && (int)$d > 0) return (int)$d;
        }
        return max(0, Settings::int('ar.termin_default_hari', 30));
    }

    /**
     * Menghitung jatuh tempo.
     * @return array{due_date:?string,basis:string,term_days:int}
     */
    public static function computeDueDate(array $inv, ?string $basis = null, ?int $termDays = null): array
    {
        if (!empty($inv['due_date_manual'])) {
            return ['due_date' => $inv['due_date'] ?? null, 'basis' => 'manual', 'term_days' => (int)($inv['payment_term_days'] ?? 0)];
        }
        [$base, $usedBasis] = self::resolveBaseDate($inv, $basis);
        $term = $termDays !== null ? $termDays : self::resolveTermDays($inv);
        if (!$base) return ['due_date' => null, 'basis' => $usedBasis, 'term_days' => $term];
        return ['due_date' => Dates::addDays($base, $term), 'basis' => $usedBasis, 'term_days' => $term];
    }

    /** ------------------------------------------------------------------
     *  R-06 : Status pembayaran
     *  ------------------------------------------------------------------ */
    public static function paymentStatus(array $inv, ?string $reportDate = null): string
    {
        $reportDate = $reportDate ?: Dates::today();
        $doc = (string)($inv['doc_status'] ?? 'active');

        if ($doc === 'cancelled')   return 'DIBATALKAN';
        if ($doc === 'replaced' || $doc === 'revised') return 'DIREVISI';
        if ($doc === 'written_off') return 'DIHAPUSKAN';

        $tol  = Settings::float('ar.toleransi_lunas', 1);
        $out  = round((float)($inv['outstanding_amount'] ?? 0), 2);
        $paid = round((float)($inv['paid_amount'] ?? 0), 2);
        $due  = $inv['due_date'] ?? null;

        if ($out <= $tol) return 'LUNAS';           // termasuk kelebihan bayar (saldo negatif)
        if ($paid > 0)    return 'PEMBAYARAN_SEBAGIAN';
        if (!$due)        return 'BELUM_JATUH_TEMPO';
        if ($due <  $reportDate) return 'TERLAMBAT';
        if ($due === $reportDate) return 'JATUH_TEMPO';
        return 'BELUM_JATUH_TEMPO';
    }

    /** ------------------------------------------------------------------
     *  R-07 : Umur piutang & hari penagihan
     *  ------------------------------------------------------------------ */

    /** Umur piutang: tanggal_lapor − jatuh_tempo (positif = terlambat). NULL untuk invoice lunas. */
    public static function agingDays(array $inv, ?string $reportDate = null): ?int
    {
        $reportDate = $reportDate ?: Dates::today();
        $tol = Settings::float('ar.toleransi_lunas', 1);
        if (round((float)($inv['outstanding_amount'] ?? 0), 2) <= $tol) return null;
        if (empty($inv['due_date'])) return null;
        return Dates::diffDays($inv['due_date'], $reportDate);
    }

    /** Lama penagihan riil untuk invoice lunas: tanggal_bayar_terakhir − tanggal_dasar. */
    public static function collectionDays(array $inv): ?int
    {
        if (empty($inv['last_payment_date'])) return null;
        $basis = Settings::str('acp.dasar_hari_penagihan', 'sent_date');
        if ($basis === 'due_date') {
            return empty($inv['due_date']) ? null : Dates::diffDays($inv['due_date'], $inv['last_payment_date']);
        }
        [$base] = self::resolveBaseDate($inv, $basis);
        if (!$base) return null;
        return Dates::diffDays($base, $inv['last_payment_date']);
    }

    /** Hari keterlambatan pembayaran (0 bila tepat waktu). */
    public static function daysLate(array $inv): ?int
    {
        if (empty($inv['last_payment_date']) || empty($inv['due_date'])) return null;
        return Dates::diffDays($inv['due_date'], $inv['last_payment_date']);
    }

    /** ------------------------------------------------------------------
     *  Perhitungan penuh satu invoice + penulisan ke database
     *  ------------------------------------------------------------------ */

    /**
     * Menghitung ulang seluruh nilai turunan sebuah invoice dan menyimpannya.
     * Dijalankan setiap kali invoice / potongan / pembayaran / nota kredit berubah.
     *
     * @param int         $invoiceId
     * @param string|null $reportDate tanggal pelaporan untuk aging (default: hari ini)
     * @param bool        $recomputeDueDate hitung ulang jatuh tempo dari tanggal dasar
     * @return array invoice setelah diperbarui
     */
    public static function recalculate(int $invoiceId, ?string $reportDate = null, bool $recomputeDueDate = false): array
    {
        $db = Database::i();
        $reportDate = $reportDate ?: Dates::today();

        $inv = $db->one('SELECT * FROM invoices WHERE id = :id', ['id' => $invoiceId]);
        if (!$inv) throw new \RuntimeException("Invoice #$invoiceId tidak ditemukan.");

        $gross = (float)$inv['gross_amount'];

        // --- Potongan dari tabel rincian (sumber kebenaran)
        $ded = $db->all(
            'SELECT d.*, t.target_column, t.code
               FROM invoice_deductions d
               JOIN deduction_types t ON t.id = d.deduction_type_id
              WHERE d.invoice_id = :id AND d.deleted_at IS NULL',
            ['id' => $invoiceId]
        );
        $pph = 0.0; $adm = 0.0; $oth = 0.0;
        foreach ($ded as $d) {
            $amt = (float)$d['amount'];
            match ($d['target_column']) {
                'pph23' => $pph += $amt,
                'admin' => $adm += $amt,
                default => $oth += $amt,
            };
        }

        $dpp   = self::dpp($gross);
        $vat   = self::vat($gross);
        $totDed = round($pph + $adm + $oth, 2);
        $net   = round($gross - $totDed, 2);

        // --- Pembayaran (hanya yang sudah posted & disetujui)
        $pay = $db->one(
            'SELECT COALESCE(SUM(pa.amount),0) AS total,
                    MIN(p.payment_date) AS first_date,
                    MAX(p.payment_date) AS last_date,
                    COUNT(*) AS cnt
               FROM payment_allocations pa
               JOIN payments p ON p.id = pa.payment_id
              WHERE pa.invoice_id = :id
                AND pa.deleted_at IS NULL
                AND p.deleted_at IS NULL
                AND p.status = "posted"
                AND p.approval_status IN ("approved","not_required")',
            ['id' => $invoiceId]
        );
        $paid = round((float)($pay['total'] ?? 0), 2);

        // --- Nota kredit / penyesuaian yang disetujui
        $cn = round((float)$db->value(
            'SELECT COALESCE(SUM(amount),0) FROM credit_notes
              WHERE invoice_id = :id AND status = "approved" AND deleted_at IS NULL',
            ['id' => $invoiceId]
        ), 2);

        $doc = (string)$inv['doc_status'];
        $outstanding = round($net - $paid - $cn, 2);
        // Invoice yang dibatalkan / diganti / dihapuskan tidak lagi menjadi piutang aktif
        if (in_array($doc, ['cancelled', 'replaced', 'revised', 'written_off'], true)) {
            $outstanding = 0.0;
        }

        // --- Jatuh tempo
        $dueDate  = $inv['due_date'];
        $dueBasis = $inv['due_date_basis'];
        $termDays = (int)$inv['payment_term_days'];
        if ($recomputeDueDate && (int)$inv['due_date_manual'] !== 1) {
            $r = self::computeDueDate($inv);
            if ($r['due_date']) { $dueDate = $r['due_date']; $dueBasis = $r['basis']; $termDays = $r['term_days']; }
        }

        $work = $inv;
        $work['paid_amount'] = $paid;
        $work['outstanding_amount'] = $outstanding;
        $work['due_date'] = $dueDate;
        $work['last_payment_date'] = $pay['last_date'] ?? null;

        $status = self::paymentStatus($work, $reportDate);
        $aging  = self::agingDays($work, $reportDate);
        $collDays = self::collectionDays($work);
        $late   = self::daysLate($work);

        // --- Revenue
        $revBasis = Settings::str('ar.basis_revenue', 'dpp');
        $isCounted = (int)$inv['is_revenue_counted'] === 1 && $doc === 'active';
        $revenue = 0.0;
        if ($isCounted) {
            $revenue = match ($revBasis) {
                'gross' => $gross,
                'net'   => $net,
                default => $dpp,
            };
        }

        $pphRate = self::effectivePphRate($gross, $pph) ?? 0;

        $update = [
            'dpp_amount'             => $dpp,
            'vat_amount'             => $vat,
            'pph23_amount'           => $pph,
            'pph23_rate'             => $pphRate,
            'admin_fee_amount'       => $adm,
            'other_deduction_amount' => $oth,
            'total_deduction'        => $totDed,
            'net_amount'             => $net,
            'credit_note_amount'     => $cn,
            'paid_amount'            => $paid,
            'outstanding_amount'     => $outstanding,
            'revenue_amount'         => round($revenue, 2),
            'first_payment_date'     => $pay['first_date'] ?? null,
            'last_payment_date'      => $pay['last_date'] ?? null,
            'payment_count'          => (int)($pay['cnt'] ?? 0),
            'payment_status_code'    => $status,
            'due_date'               => $dueDate,
            'due_date_basis'         => $dueBasis,
            'payment_term_days'      => $termDays,
            'aging_days'             => $aging,
            'collection_days'        => $collDays,
            'days_late'              => $late,
            'aging_recalc_date'      => $reportDate,
        ];
        $db->update('invoices', $update, 'id = :pk', ['pk' => $invoiceId]);

        return array_merge($inv, $update);
    }

    /** Menghitung ulang banyak invoice sekaligus (mis. setelah import atau ubah pengaturan). */
    public static function recalculateBatch(?array $invoiceIds = null, ?string $reportDate = null, bool $recomputeDueDate = false, ?callable $progress = null): int
    {
        $db = Database::i();
        $reportDate = $reportDate ?: Dates::today();
        if ($invoiceIds === null) {
            $invoiceIds = array_map('intval', $db->column('SELECT id FROM invoices WHERE deleted_at IS NULL ORDER BY id'));
        }
        $n = 0;
        foreach (array_chunk($invoiceIds, 200) as $chunk) {
            $db->begin();
            try {
                foreach ($chunk as $id) { self::recalculate((int)$id, $reportDate, $recomputeDueDate); $n++; }
                $db->commit();
            } catch (\Throwable $e) { $db->rollback(); throw $e; }
            if ($progress) $progress($n, count($invoiceIds));
        }
        return $n;
    }

    /**
     * Pembaruan cepat status & aging untuk semua invoice pada tanggal pelaporan tertentu
     * (tanpa menghitung ulang potongan/pembayaran). Dipakai oleh tugas harian.
     */
    public static function refreshAgingOnly(?string $reportDate = null): int
    {
        $reportDate = $reportDate ?: Dates::today();
        $tol = Settings::float('ar.toleransi_lunas', 1);
        $db = Database::i();
        return $db->exec(
            'UPDATE invoices SET
               aging_days = CASE WHEN outstanding_amount > :tol AND due_date IS NOT NULL
                                 THEN DATEDIFF(:d1, due_date) ELSE NULL END,
               payment_status_code = CASE
                    WHEN doc_status = "cancelled"    THEN "DIBATALKAN"
                    WHEN doc_status IN ("replaced","revised") THEN "DIREVISI"
                    WHEN doc_status = "written_off"  THEN "DIHAPUSKAN"
                    WHEN outstanding_amount <= :tol2 THEN "LUNAS"
                    WHEN paid_amount > 0             THEN "PEMBAYARAN_SEBAGIAN"
                    WHEN due_date IS NULL            THEN "BELUM_JATUH_TEMPO"
                    WHEN due_date <  :d2             THEN "TERLAMBAT"
                    WHEN due_date =  :d3             THEN "JATUH_TEMPO"
                    ELSE "BELUM_JATUH_TEMPO" END,
               aging_recalc_date = :d4
             WHERE deleted_at IS NULL',
            ['tol' => $tol, 'tol2' => $tol, 'd1' => $reportDate, 'd2' => $reportDate, 'd3' => $reportDate, 'd4' => $reportDate]
        );
    }

    /**
     * Saldo AR pada tanggal tertentu — direkonstruksi dari riwayat transaksi.
     * Inilah yang tidak mungkin dilakukan di Excel dan menjadi dasar ACP Metode 1.
     */
    public static function arBalanceAt(string $date, array $filters = []): float
    {
        $db = Database::i();
        [$where, $params] = self::buildFilterSql($filters, 'i');
        $params['d1'] = $date; $params['d2'] = $date; $params['d3'] = $date;
        $sql = "SELECT COALESCE(SUM(
                    i.net_amount
                    - COALESCE((SELECT SUM(pa.amount) FROM payment_allocations pa
                                  JOIN payments p ON p.id = pa.payment_id
                                 WHERE pa.invoice_id = i.id AND pa.deleted_at IS NULL
                                   AND p.deleted_at IS NULL AND p.status = 'posted'
                                   AND p.approval_status IN ('approved','not_required')
                                   AND p.payment_date <= :d2), 0)
                    - COALESCE((SELECT SUM(cn.amount) FROM credit_notes cn
                                 WHERE cn.invoice_id = i.id AND cn.status = 'approved'
                                   AND cn.deleted_at IS NULL AND cn.cn_date <= :d3), 0)
                ), 0) AS saldo
                FROM invoices i
               WHERE i.deleted_at IS NULL
                 AND i.doc_status = 'active'
                 AND i.invoice_date <= :d1
                 AND $where";
        return round((float)$db->value($sql, $params), 2);
    }

    /** Jumlah invoice yang masih outstanding pada tanggal tertentu. */
    public static function arCountAt(string $date, array $filters = []): int
    {
        $db = Database::i();
        [$where, $params] = self::buildFilterSql($filters, 'i');
        $params['d1'] = $date; $params['d2'] = $date; $params['d3'] = $date;
        $sql = "SELECT COUNT(*) FROM invoices i
                 WHERE i.deleted_at IS NULL AND i.doc_status='active' AND i.invoice_date <= :d1 AND $where
                   AND (i.net_amount
                        - COALESCE((SELECT SUM(pa.amount) FROM payment_allocations pa
                                      JOIN payments p ON p.id=pa.payment_id
                                     WHERE pa.invoice_id=i.id AND pa.deleted_at IS NULL AND p.deleted_at IS NULL
                                       AND p.status='posted' AND p.approval_status IN ('approved','not_required')
                                       AND p.payment_date <= :d2),0)
                        - COALESCE((SELECT SUM(cn.amount) FROM credit_notes cn
                                     WHERE cn.invoice_id=i.id AND cn.status='approved'
                                       AND cn.deleted_at IS NULL AND cn.cn_date <= :d3),0)) > 0";
        return (int)$db->value($sql, $params);
    }

    /**
     * Membangun klausa WHERE dari filter standar sistem.
     * Semua nama kolom di-whitelist sehingga aman dari SQL injection.
     *
     * @return array{0:string,1:array}
     */
    public static function buildFilterSql(array $f, string $alias = 'i'): array
    {
        $a = preg_replace('/[^a-zA-Z0-9_]/', '', $alias) ?: 'i';
        $w = ['1=1'];
        $p = [];
        $simple = [
            'area_id'             => 'area_id',
            'business_unit_id'    => 'business_unit_id',
            'customer_id'         => 'customer_id',
            'outlet_id'           => 'outlet_id',
            'salesperson_id'      => 'salesperson_id',
            'collection_officer_id' => 'collection_officer_id',
            'job_category_id'     => 'job_category_id',
            'job_type_id'         => 'job_type_id',
            'revenue_category_id' => 'revenue_category_id',
            'payment_status_code' => 'payment_status_code',
            'doc_status'          => 'doc_status',
        ];
        foreach ($simple as $key => $col) {
            if (!isset($f[$key]) || $f[$key] === '' || $f[$key] === null) continue;
            $val = $f[$key];
            if (is_array($val)) {
                $val = array_values(array_filter($val, fn($x) => $x !== '' && $x !== null));
                if (!$val) continue;
                $ph = [];
                foreach ($val as $i => $v) { $k = "f_{$key}_{$i}"; $ph[] = ":$k"; $p[$k] = $v; }
                $w[] = "$a.$col IN (" . implode(',', $ph) . ')';
            } else {
                $w[] = "$a.$col = :f_$key";
                $p["f_$key"] = $val;
            }
        }
        if (!empty($f['invoice_date_from'])) { $w[] = "$a.invoice_date >= :f_idf"; $p['f_idf'] = $f['invoice_date_from']; }
        if (!empty($f['invoice_date_to']))   { $w[] = "$a.invoice_date <= :f_idt"; $p['f_idt'] = $f['invoice_date_to']; }
        if (!empty($f['due_date_from']))     { $w[] = "$a.due_date >= :f_ddf";     $p['f_ddf'] = $f['due_date_from']; }
        if (!empty($f['due_date_to']))       { $w[] = "$a.due_date <= :f_ddt";     $p['f_ddt'] = $f['due_date_to']; }
        if (!empty($f['payment_date_from'])) { $w[] = "$a.last_payment_date >= :f_pdf"; $p['f_pdf'] = $f['payment_date_from']; }
        if (!empty($f['payment_date_to']))   { $w[] = "$a.last_payment_date <= :f_pdt"; $p['f_pdt'] = $f['payment_date_to']; }
        if (!empty($f['hanya_outstanding']))  { $w[] = "$a.outstanding_amount > 0"; }
        if (!empty($f['hanya_lunas']))        { $w[] = "$a.payment_status_code = 'LUNAS'"; }
        if (!empty($f['hanya_terlambat']))    { $w[] = "$a.payment_status_code = 'TERLAMBAT'"; }
        if (!empty($f['hanya_aktif']))        { $w[] = "$a.doc_status = 'active'"; }
        if (!empty($f['revenue_counted']))    { $w[] = "$a.is_revenue_counted = 1"; }
        if (!empty($f['nilai_min']))          { $w[] = "$a.gross_amount >= :f_nmin"; $p['f_nmin'] = (float)$f['nilai_min']; }
        if (!empty($f['nilai_max']))          { $w[] = "$a.gross_amount <= :f_nmax"; $p['f_nmax'] = (float)$f['nilai_max']; }
        if (!empty($f['cari'])) {
            $w[] = "($a.invoice_no LIKE :f_cari OR $a.tax_invoice_no LIKE :f_cari OR $a.so_no LIKE :f_cari)";
            $p['f_cari'] = '%' . $f['cari'] . '%';
        }
        // Klausa pembatas cakupan data (dari Acl) sudah berupa SQL aman
        if (!empty($f['_scope_sql'])) {
            $w[] = '(' . $f['_scope_sql'] . ')';
            foreach ($f['_scope_params'] ?? [] as $k => $v) $p[$k] = $v;
        }
        return [implode(' AND ', $w), $p];
    }
}
