<?php
namespace App\Controllers;

use App\Core\Acl;
use App\Core\Controller;
use App\Core\Database;
use App\Core\Dates;
use App\Core\Num;
use App\Core\Request;
use App\Core\Response;
use App\Models\Customer;
use App\Models\Invoice;
use App\Services\AgingService;
use App\Services\ArCalculator;
use App\Services\DashboardService;
use App\Services\ExportService;

/** Daftar piutang, kartu piutang customer, dan perkiraan penerimaan kas. */
class PiutangController extends Controller
{
    protected string $module = 'piutang';

    public static function readFilters(bool $defaultOutstanding = true): array
    {
        $f = [];
        foreach (['area_id', 'business_unit_id', 'customer_id', 'outlet_id', 'salesperson_id',
                  'collection_officer_id', 'job_category_id', 'job_type_id', 'revenue_category_id',
                  'payment_status_code', 'doc_status'] as $k) {
            $v = Request::get($k, '');
            if ($v !== '' && $v !== null) $f[$k] = $v;
        }
        foreach (['invoice_date_from', 'invoice_date_to', 'due_date_from', 'due_date_to',
                  'payment_date_from', 'payment_date_to'] as $k) {
            $v = Request::date($k);
            if ($v) $f[$k] = $v;
        }
        foreach (['cari_teks'] as $k) {
            $v = trim((string)Request::get($k, ''));
            if ($v !== '') $f[$k] = $v;
        }
        foreach (['nilai_min', 'nilai_max'] as $k) {
            $v = Request::get($k, '');
            if ($v !== '' && $v !== null) $f[$k] = Num::parse((string)$v);
        }
        foreach (['aging_min', 'aging_max'] as $k) {
            $v = Request::get($k, '');
            if ($v !== '') $f[$k] = (int)$v;
        }
        if (Request::get('tanpa_faktur_pajak')) $f['tanpa_faktur_pajak'] = 1;
        if (Request::get('ada_dq')) $f['ada_dq'] = 1;
        if (Request::get('hanya_outstanding')) $f['hanya_outstanding'] = 1;
        elseif ($defaultOutstanding && Request::get('semua') === null && !isset($f['payment_status_code']) && !isset($f['doc_status'])) {
            $f['hanya_outstanding'] = 1;
            $f['hanya_aktif'] = 1;
        }
        return $f;
    }

    public function index(): void
    {
        $filters = self::readFilters(true);
        $sort = (string)Request::get('urut', 'due_date');
        $dir  = (string)Request::get('arah', 'asc');
        [$page, $per] = $this->pageParams();

        $m = new Invoice();
        $p = $m->listing($filters, $sort, $dir, $page, $per);
        $tot = $m->listingTotals($filters);

        $this->view('piutang/index', [
            'judul'    => 'Daftar Piutang',
            'subjudul' => 'Invoice yang masih memiliki saldo per ' . Dates::fmtLong(Dates::today()),
            'p' => $p, 'tot' => $tot, 'filters' => $filters, 'sort' => $sort, 'dir' => $dir,
            'opsi' => DashboardService::filterOptions(),
        ]);
    }

    public function export(): void
    {
        $filters = self::readFilters(true);
        $m = new Invoice();
        $p = $m->listing($filters, (string)Request::get('urut', 'due_date'), (string)Request::get('arah', 'asc'),
                         1, \App\Core\Settings::int('laporan.maks_baris_export', 50000));
        $tot = $m->listingTotals($filters);

        $columns = [
            ['label' => 'No. Invoice',      'key' => 'invoice_no',        'type' => 'text'],
            ['label' => 'No. Faktur Pajak', 'key' => 'tax_invoice_no',    'type' => 'text'],
            ['label' => 'Tgl Invoice',      'key' => 'invoice_date',      'type' => 'date'],
            ['label' => 'Tgl Kirim',        'key' => 'sent_date',         'type' => 'date'],
            ['label' => 'Jatuh Tempo',      'key' => 'due_date',          'type' => 'date'],
            ['label' => 'Customer',         'key' => 'customer_name',     'type' => 'text'],
            ['label' => 'Outlet',           'key' => 'outlet_name',       'type' => 'text'],
            ['label' => 'Area',             'key' => 'area_name',         'type' => 'text'],
            ['label' => 'Unit Bisnis',      'key' => 'business_unit_name','type' => 'text'],
            ['label' => 'Sales',            'key' => 'salesperson_name',  'type' => 'text'],
            ['label' => 'Kategori',         'key' => 'job_category_name', 'type' => 'text'],
            ['label' => 'PMSC/By Case',     'key' => 'revenue_category_name', 'type' => 'text'],
            ['label' => 'Nominal',          'key' => 'gross_amount',      'type' => 'money'],
            ['label' => 'PPh 23',           'key' => 'pph23_amount',      'type' => 'money'],
            ['label' => 'Pot. Adm',         'key' => 'admin_fee_amount',  'type' => 'money'],
            ['label' => 'Pot. Lain',        'key' => 'other_deduction_amount', 'type' => 'money'],
            ['label' => 'Total Tagihan',    'key' => 'net_amount',        'type' => 'money'],
            ['label' => 'Pembayaran',       'key' => 'paid_amount',       'type' => 'money'],
            ['label' => 'Saldo',            'key' => 'outstanding_amount','type' => 'money'],
            ['label' => 'Tgl Bayar Terakhir','key' => 'last_payment_date','type' => 'date'],
            ['label' => 'Status',           'key' => 'payment_status_name','type' => 'text'],
            ['label' => 'Umur (hari)',      'key' => 'aging_days',        'type' => 'int'],
        ];
        ExportService::send((string)Request::get('format', 'xlsx'), [
            'judul' => 'DAFTAR PIUTANG (ACCOUNTS RECEIVABLE)',
            'tanggal_lapor' => Dates::fmt(Dates::today()),
            'filter' => self::filterLabels($filters),
            'nama_berkas' => 'daftar_piutang',
            'sheet' => 'Daftar Piutang',
            'orientasi' => 'landscape',
        ], $columns, $p['data'], [
            'invoice_no' => 'TOTAL (' . nf($tot['jumlah']) . ' invoice)',
            'gross_amount' => $tot['bruto'], 'pph23_amount' => $tot['pph23'],
            'net_amount' => $tot['bersih'], 'paid_amount' => $tot['dibayar'],
            'outstanding_amount' => $tot['saldo'],
        ]);
    }

    /** Label filter yang mudah dibaca (untuk kop laporan). */
    public static function filterLabels(array $f): array
    {
        $db = Database::i();
        $out = [];
        $map = [
            'area_id' => ['Area', 'areas'], 'business_unit_id' => ['Unit Bisnis', 'business_units'],
            'customer_id' => ['Customer', 'customers'], 'outlet_id' => ['Outlet', 'outlets'],
            'salesperson_id' => ['Sales', 'salespersons'], 'collection_officer_id' => ['Petugas Penagihan', 'collection_officers'],
            'job_category_id' => ['Kategori Pekerjaan', 'job_categories'], 'job_type_id' => ['Jenis Pekerjaan', 'job_types'],
            'revenue_category_id' => ['PMSC/By Case', 'revenue_categories'],
        ];
        foreach ($map as $k => [$label, $table]) {
            if (empty($f[$k]) || is_array($f[$k])) continue;
            $n = $db->value("SELECT name FROM `$table` WHERE id = ?", [(int)$f[$k]]);
            if ($n) $out[$label] = $n;
        }
        if (!empty($f['payment_status_code'])) {
            $n = $db->value('SELECT name FROM payment_statuses WHERE code = ?', [$f['payment_status_code']]);
            $out['Status Pembayaran'] = $n ?: $f['payment_status_code'];
        }
        if (!empty($f['doc_status'])) {
            $out['Status Dokumen'] = ['active' => 'Aktif', 'cancelled' => 'Dibatalkan', 'replaced' => 'Diganti',
                                      'revised' => 'Direvisi', 'written_off' => 'Dihapuskan'][$f['doc_status']] ?? $f['doc_status'];
        }
        if (!empty($f['invoice_date_from']) || !empty($f['invoice_date_to'])) {
            $out['Tanggal Invoice'] = Dates::fmt($f['invoice_date_from'] ?? null) . ' s/d ' . Dates::fmt($f['invoice_date_to'] ?? null);
        }
        if (!empty($f['due_date_from']) || !empty($f['due_date_to'])) {
            $out['Jatuh Tempo'] = Dates::fmt($f['due_date_from'] ?? null) . ' s/d ' . Dates::fmt($f['due_date_to'] ?? null);
        }
        if (!empty($f['cari_teks'])) $out['Pencarian'] = $f['cari_teks'];
        if (!empty($f['hanya_outstanding'])) $out['Tampilan'] = 'Hanya invoice bersaldo';
        return $out;
    }

    // ------------------------------------------------------------------ KARTU PIUTANG
    public function cards(): void
    {
        $cari = trim((string)Request::get('cari', ''));
        [$page, $per] = $this->pageParams();
        $w = ['c.deleted_at IS NULL'];
        $p = [];
        if ($cari !== '') { $w[] = '(c.name LIKE :q OR c.code LIKE :q OR c.npwp LIKE :q)'; $p['q'] = '%' . $cari . '%'; }
        if (Request::get('hanya_bersaldo')) $w[] = 'ag.total_saldo > 0';
        [$scope, $sp] = Acl::invoiceScopeSql('i2');
        $scopeJoin = $scope !== '1=1' ? " AND EXISTS (SELECT 1 FROM invoices i2 WHERE i2.customer_id = c.id AND ($scope))" : '';
        if ($scope !== '1=1') $p = array_merge($p, $sp);
        $where = implode(' AND ', $w) . $scopeJoin;

        $sql = "SELECT c.*, a.name AS area_name, s.name AS salesperson_name, co.name AS collection_officer_name,
                       COALESCE(ag.jumlah_invoice,0) AS jumlah_invoice,
                       COALESCE(ag.total_bersih,0) AS total_bersih,
                       COALESCE(ag.total_bayar,0) AS total_bayar,
                       COALESCE(ag.total_saldo,0) AS total_saldo,
                       COALESCE(ag.total_terlambat,0) AS total_terlambat,
                       ag.rata_hari, ag.maks_umur, ag.pembayaran_terakhir
                  FROM customers c
                  LEFT JOIN areas a ON a.id = c.area_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(*) jumlah_invoice, SUM(net_amount) total_bersih,
                                    SUM(paid_amount) total_bayar, SUM(outstanding_amount) total_saldo,
                                    SUM(CASE WHEN payment_status_code='TERLAMBAT' THEN outstanding_amount ELSE 0 END) total_terlambat,
                                    AVG(CASE WHEN payment_status_code='LUNAS' THEN collection_days END) rata_hari,
                                    MAX(CASE WHEN outstanding_amount>0 THEN aging_days END) maks_umur,
                                    MAX(last_payment_date) pembayaran_terakhir
                               FROM invoices WHERE deleted_at IS NULL AND doc_status='active' GROUP BY customer_id) ag
                    ON ag.customer_id = c.id
                 WHERE $where ORDER BY COALESCE(ag.total_saldo,0) DESC, c.name";
        $m = new Customer();
        $res = $m->paginate($sql, $p, $page, $per,
            "SELECT COUNT(*) FROM customers c LEFT JOIN (SELECT customer_id, SUM(outstanding_amount) total_saldo
               FROM invoices WHERE deleted_at IS NULL AND doc_status='active' GROUP BY customer_id) ag ON ag.customer_id=c.id
              WHERE $where");

        $this->view('piutang/kartu_index', [
            'judul' => 'Kartu Piutang Customer',
            'subjudul' => 'Ringkasan piutang per customer',
            'p' => $res, 'cari' => $cari,
        ]);
    }

    public function card(array $params): void
    {
        $id = (int)($params['id'] ?? 0);
        $m = new Customer();
        $cust = $m->find($id);
        if (!$cust) Response::abort(404, 'Customer tidak ditemukan.');

        $asOf = Request::date('tanggal_lapor') ?: Dates::today();
        $db = Database::i();
        [$scope, $sp] = Acl::invoiceScopeSql('i');
        $params2 = array_merge(['c' => $id], $sp);
        $invoices = $db->all(
            'SELECT ' . Invoice::SELECT_COLS . Invoice::SELECT_JOIN
            . " WHERE i.deleted_at IS NULL AND i.customer_id = :c AND ($scope) ORDER BY i.invoice_date, i.id",
            $params2
        );
        $payments = $db->all(
            'SELECT p.payment_no, p.payment_date, p.method, p.reference_no, p.payment_type,
                    pa.amount, i.invoice_no, b.name AS bank_name
               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
              WHERE i.customer_id = :c AND pa.deleted_at IS NULL AND p.deleted_at IS NULL
              ORDER BY p.payment_date DESC, p.id DESC LIMIT 200',
            ['c' => $id]
        );

        $this->view('piutang/kartu_detail', [
            'judul' => 'Kartu Piutang — ' . $cust['name'],
            'subjudul' => 'Kode ' . $cust['code'] . ' · per ' . Dates::fmtLong($asOf),
            'cust' => $cust, 'summary' => $m->arSummary($id, $asOf), 'risk' => $m->riskScore($id),
            'invoices' => $invoices, 'payments' => $payments, 'asOf' => $asOf,
            'aging' => AgingService::report(['customer_id' => $id], 'none', $asOf),
            'followups' => $db->all(
                'SELECT f.*, cs.name AS status_name, cs.color AS status_color, i.invoice_no
                   FROM collection_followups f
                   LEFT JOIN collection_statuses cs ON cs.id = f.collection_status_id
                   LEFT JOIN invoices i ON i.id = f.invoice_id
                  WHERE f.customer_id = :c AND f.deleted_at IS NULL
                  ORDER BY f.followup_date DESC LIMIT 30', ['c' => $id]),
        ]);
    }

    public function cardExport(array $params): void
    {
        $id = (int)($params['id'] ?? 0);
        $m = new Customer();
        $cust = $m->find($id);
        if (!$cust) Response::abort(404, 'Customer tidak ditemukan.');
        $db = Database::i();
        $rows = $db->all(
            'SELECT ' . Invoice::SELECT_COLS . Invoice::SELECT_JOIN
            . ' WHERE i.deleted_at IS NULL AND i.customer_id = :c ORDER BY i.invoice_date, i.id', ['c' => $id]
        );
        $s = $m->arSummary($id);
        ExportService::send((string)Request::get('format', 'xlsx'), [
            'judul' => 'KARTU PIUTANG CUSTOMER',
            'subjudul' => $cust['name'] . ' (' . $cust['code'] . ')',
            'tanggal_lapor' => Dates::fmt(Dates::today()),
            'nama_berkas' => 'kartu_piutang_' . $cust['code'],
            'sheet' => 'Kartu Piutang',
            'orientasi' => 'landscape',
        ], [
            ['label' => 'No. Invoice',   'key' => 'invoice_no',   'type' => 'text'],
            ['label' => 'Tgl Invoice',   'key' => 'invoice_date', 'type' => 'date'],
            ['label' => 'Jatuh Tempo',   'key' => 'due_date',     'type' => 'date'],
            ['label' => 'Outlet',        'key' => 'outlet_name',  'type' => 'text'],
            ['label' => 'Nominal',       'key' => 'gross_amount', 'type' => 'money'],
            ['label' => 'Total Tagihan', 'key' => 'net_amount',   'type' => 'money'],
            ['label' => 'Pembayaran',    'key' => 'paid_amount',  'type' => 'money'],
            ['label' => 'Saldo',         'key' => 'outstanding_amount', 'type' => 'money'],
            ['label' => 'Tgl Bayar',     'key' => 'last_payment_date',  'type' => 'date'],
            ['label' => 'Status',        'key' => 'payment_status_name','type' => 'text'],
            ['label' => 'Umur (hari)',   'key' => 'aging_days',   'type' => 'int'],
        ], $rows, [
            'invoice_no' => 'TOTAL', 'gross_amount' => $s['total_bruto'], 'net_amount' => $s['total_bersih'],
            'paid_amount' => $s['total_bayar'], 'outstanding_amount' => $s['total_saldo'],
        ]);
    }

    // ------------------------------------------------------------------ FORECAST
    public function forecast(): void
    {
        $asOf = Request::date('tanggal_lapor') ?: Dates::today();
        $weeks = max(4, min(26, Request::int('minggu', 12)));
        $filters = self::readFilters(false);
        $svc = new DashboardService($filters, $asOf);
        $fc = $svc->collectionForecast($weeks);

        $db = Database::i();
        [$w, $p] = ArCalculator::buildFilterSql($filters, 'i');
        $p['asof'] = $asOf;
        $byMonth = $db->all(
            "SELECT DATE_FORMAT(i.due_date,'%Y-%m') AS periode, COUNT(*) AS jumlah,
                    COALESCE(SUM(i.outstanding_amount),0) AS nilai
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status='active' AND i.outstanding_amount > 0 AND $w
              GROUP BY periode ORDER BY periode",
            $p
        );

        $this->view('piutang/forecast', [
            'judul' => 'Perkiraan Penerimaan Kas',
            'subjudul' => 'Proyeksi berdasarkan janji bayar, jatuh tempo, dan pola pembayaran historis',
            'fc' => $fc, 'byMonth' => $byMonth, 'asOf' => $asOf, 'weeks' => $weeks,
            'filters' => $filters, 'opsi' => DashboardService::filterOptions(),
        ]);
    }

    public function forecastExport(): void
    {
        $asOf = Request::date('tanggal_lapor') ?: Dates::today();
        $weeks = max(4, min(26, Request::int('minggu', 12)));
        $svc = new DashboardService(self::readFilters(false), $asOf);
        $fc = $svc->collectionForecast($weeks);
        $rows = [];
        $rows[] = ['periode' => 'Sudah Lewat Jatuh Tempo', 'mulai' => null, 'selesai' => null,
                   'jumlah' => $fc['lewat']['jumlah'], 'nilai' => $fc['lewat']['nilai'], 'tertimbang' => $fc['lewat']['tertimbang']];
        foreach ($fc['minggu'] as $m) {
            $rows[] = ['periode' => $m['label'], 'mulai' => $m['mulai'], 'selesai' => $m['selesai'],
                       'jumlah' => $m['jumlah'], 'nilai' => $m['nilai'], 'tertimbang' => $m['tertimbang']];
        }
        $rows[] = ['periode' => $fc['jauh']['label'], 'mulai' => null, 'selesai' => null,
                   'jumlah' => $fc['jauh']['jumlah'], 'nilai' => $fc['jauh']['nilai'], 'tertimbang' => $fc['jauh']['tertimbang']];

        ExportService::send((string)Request::get('format', 'xlsx'), [
            'judul' => 'PERKIRAAN PENERIMAAN KAS',
            'tanggal_lapor' => Dates::fmt($asOf),
            'nama_berkas' => 'perkiraan_penerimaan_kas',
            'sheet' => 'Forecast Kas',
        ], [
            ['label' => 'Periode',   'key' => 'periode',    'type' => 'text', 'width' => 26],
            ['label' => 'Mulai',     'key' => 'mulai',      'type' => 'date'],
            ['label' => 'Selesai',   'key' => 'selesai',    'type' => 'date'],
            ['label' => 'Jumlah Invoice', 'key' => 'jumlah','type' => 'int'],
            ['label' => 'Nilai Jatuh Tempo', 'key' => 'nilai', 'type' => 'money'],
            ['label' => 'Perkiraan Tertimbang', 'key' => 'tertimbang', 'type' => 'money'],
        ], $rows, [
            'periode' => 'TOTAL',
            'jumlah' => array_sum(array_column($rows, 'jumlah')),
            'nilai' => array_sum(array_column($rows, 'nilai')),
            'tertimbang' => array_sum(array_column($rows, 'tertimbang')),
        ]);
    }
}
