<?php
namespace App\Services;

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

/** Menghitung KPI dan data chart dashboard. */
class DashboardService
{
    private array $filters;
    private string $asOf;

    public function __construct(array $filters = [], ?string $asOf = null)
    {
        $this->asOf = $asOf ?: Dates::today();
        [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
        $filters['_scope_sql'] = $scopeSql;
        $filters['_scope_params'] = $scopeParams;
        $this->filters = $filters;
    }

    public function filters(): array { return $this->filters; }
    public function asOf(): string   { return $this->asOf; }

    private function where(): array { return ArCalculator::buildFilterSql($this->filters, 'i'); }

    /** 16 KPI utama. */
    public function kpi(): array
    {
        $db = Database::i();
        [$w, $p] = $this->where();
        $p['asof'] = $this->asOf;
        $monthStart = Dates::monthStart($this->asOf);
        $p['ms'] = $monthStart; $p['me'] = $this->asOf;

        $base = $db->one(
            "SELECT
                COUNT(*)                                            AS jumlah_invoice,
                COALESCE(SUM(i.gross_amount),0)                     AS total_bruto,
                COALESCE(SUM(i.net_amount),0)                       AS total_bersih,
                COALESCE(SUM(i.total_deduction),0)                  AS total_potongan,
                COALESCE(SUM(i.pph23_amount),0)                     AS total_pph23,
                COALESCE(SUM(i.paid_amount),0)                      AS total_pembayaran,
                COALESCE(SUM(i.outstanding_amount),0)               AS total_saldo,
                COALESCE(SUM(i.revenue_amount),0)                   AS total_revenue,
                COALESCE(SUM(CASE WHEN i.payment_status_code IN ('TERLAMBAT') THEN i.outstanding_amount ELSE 0 END),0) AS saldo_terlambat,
                COALESCE(SUM(CASE WHEN i.payment_status_code = 'JATUH_TEMPO' THEN i.outstanding_amount ELSE 0 END),0)  AS saldo_jatuh_tempo,
                COALESCE(SUM(CASE WHEN i.payment_status_code = 'BELUM_JATUH_TEMPO' THEN i.outstanding_amount ELSE 0 END),0) AS saldo_belum_jt,
                COALESCE(SUM(CASE WHEN i.payment_status_code = 'PEMBAYARAN_SEBAGIAN' THEN i.outstanding_amount ELSE 0 END),0) AS saldo_sebagian,
                SUM(i.payment_status_code = 'PEMBAYARAN_SEBAGIAN')  AS jumlah_sebagian,
                SUM(i.payment_status_code = 'LUNAS')                AS jumlah_lunas,
                SUM(i.payment_status_code = 'TERLAMBAT')            AS jumlah_terlambat,
                SUM(i.doc_status = 'cancelled')                     AS jumlah_dibatalkan,
                SUM(i.doc_status IN ('replaced','revised'))         AS jumlah_direvisi,
                SUM(i.doc_status = 'written_off')                   AS jumlah_dihapuskan,
                COUNT(DISTINCT i.customer_id)                       AS jumlah_customer,
                AVG(CASE WHEN i.outstanding_amount > 1 AND i.due_date IS NOT NULL
                         THEN DATEDIFF(:asof, i.due_date) END)      AS rata_umur_piutang
             FROM invoices i
             WHERE i.deleted_at IS NULL AND $w",
            $p
        );

        // Nilai bulan berjalan
        $month = $db->one(
            "SELECT COALESCE(SUM(i.revenue_amount),0) AS revenue_bulan,
                    COALESCE(SUM(i.gross_amount),0)   AS invoice_bulan,
                    COUNT(*) AS jumlah_invoice_bulan
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status='active' AND $w
                AND i.invoice_date BETWEEN :ms AND :me",
            $p
        );
        $collected = (float)$db->value(
            "SELECT COALESCE(SUM(pa.amount),0)
               FROM payment_allocations pa
               JOIN payments p ON p.id = pa.payment_id
               JOIN invoices i ON i.id = pa.invoice_id
              WHERE 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 BETWEEN :ms AND :me
                AND i.deleted_at IS NULL AND $w",
            $p
        );

        $acp = AcpService::calculate(Dates::monthStart($this->asOf), $this->asOf, $this->filters);
        $year = (int)substr($this->asOf, 0, 4);
        $target = AcpService::target($year, (int)substr($this->asOf, 5, 2));

        $bersih  = (float)$base['total_bersih'];
        $dibayar = (float)$base['total_pembayaran'];
        $saldo   = (float)$base['total_saldo'];
        $overdue = (float)$base['saldo_terlambat'];

        return [
            'jumlah_invoice'     => (int)$base['jumlah_invoice'],
            'total_bruto'        => (float)$base['total_bruto'],
            'total_bersih'       => $bersih,
            'total_potongan'     => (float)$base['total_potongan'],
            'total_pph23'        => (float)$base['total_pph23'],
            'total_pembayaran'   => $dibayar,
            'total_saldo'        => $saldo,
            'total_revenue'      => (float)$base['total_revenue'],
            'saldo_terlambat'    => $overdue,
            'saldo_jatuh_tempo'  => (float)$base['saldo_jatuh_tempo'],
            'saldo_belum_jt'     => (float)$base['saldo_belum_jt'],
            'saldo_sebagian'     => (float)$base['saldo_sebagian'],
            'jumlah_sebagian'    => (int)$base['jumlah_sebagian'],
            'jumlah_lunas'       => (int)$base['jumlah_lunas'],
            'jumlah_terlambat'   => (int)$base['jumlah_terlambat'],
            'jumlah_dibatalkan'  => (int)$base['jumlah_dibatalkan'],
            'jumlah_direvisi'    => (int)$base['jumlah_direvisi'],
            'jumlah_dihapuskan'  => (int)$base['jumlah_dihapuskan'],
            'jumlah_customer'    => (int)$base['jumlah_customer'],
            'rata_umur_piutang'  => $base['rata_umur_piutang'] === null ? null : round((float)$base['rata_umur_piutang'], 1),
            'acp'                => $acp['acp_hari'],
            'acp_metode'         => $acp['metode_label'],
            'acp_target'         => $target ? (float)$target['target_days'] : null,
            'acp_toleransi'      => $target ? (float)$target['tolerance_days'] : 5.0,
            'revenue_bulan'      => (float)$month['revenue_bulan'],
            'invoice_bulan'      => (float)$month['invoice_bulan'],
            'jumlah_invoice_bulan' => (int)$month['jumlah_invoice_bulan'],
            'penerimaan_bulan'   => round($collected, 2),
            'collection_rate'    => $bersih > 0 ? round($dibayar / $bersih * 100, 2) : null,
            'overdue_rate'       => $saldo > 0 ? round($overdue / $saldo * 100, 2) : null,
            'as_of'              => $this->asOf,
        ];
    }

    /** Komposisi aging untuk chart donat. */
    public function agingComposition(?int $configId = null): array
    {
        return AgingService::composition($this->filters, $this->asOf, $configId);
    }

    /** Tren saldo AR (n bulan terakhir). */
    public function arTrend(int $months = 12): array
    {
        return AcpService::arTrend($months, $this->filters, $this->asOf);
    }

    /** Tren invoice & penerimaan bulanan. */
    public function monthlyTrend(int $months = 12): array
    {
        $db = Database::i();
        [$w, $p] = $this->where();
        $start = (new \DateTime($this->asOf))->modify('-' . ($months - 1) . ' month')->format('Y-m-01');
        $p['start'] = $start; $p['end'] = $this->asOf;

        $inv = $db->all(
            "SELECT DATE_FORMAT(i.invoice_date,'%Y-%m') AS periode,
                    COUNT(*) AS jumlah,
                    COALESCE(SUM(i.gross_amount),0) AS bruto,
                    COALESCE(SUM(i.net_amount),0) AS bersih,
                    COALESCE(SUM(i.revenue_amount),0) AS revenue
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status='active' AND $w
                AND i.invoice_date BETWEEN :start AND :end
              GROUP BY periode ORDER BY periode",
            $p
        );
        $pay = $db->all(
            "SELECT DATE_FORMAT(p.payment_date,'%Y-%m') AS periode,
                    COALESCE(SUM(pa.amount),0) AS nilai, COUNT(DISTINCT p.id) AS jumlah
               FROM payment_allocations pa
               JOIN payments p ON p.id = pa.payment_id
               JOIN invoices i ON i.id = pa.invoice_id
              WHERE 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 BETWEEN :start AND :end
                AND i.deleted_at IS NULL AND $w
              GROUP BY periode ORDER BY periode",
            $p
        );

        $map = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $d = (new \DateTime($this->asOf))->modify("-$i month");
            $k = $d->format('Y-m');
            $map[$k] = ['periode' => $k, 'label' => Dates::monthName((int)$d->format('n'), true) . ' ' . $d->format('y'),
                        'invoice_nilai' => 0.0, 'invoice_jumlah' => 0, 'revenue' => 0.0,
                        'penerimaan' => 0.0, 'penerimaan_jumlah' => 0];
        }
        foreach ($inv as $r) if (isset($map[$r['periode']])) {
            $map[$r['periode']]['invoice_nilai'] = (float)$r['bruto'];
            $map[$r['periode']]['invoice_jumlah'] = (int)$r['jumlah'];
            $map[$r['periode']]['revenue'] = (float)$r['revenue'];
        }
        foreach ($pay as $r) if (isset($map[$r['periode']])) {
            $map[$r['periode']]['penerimaan'] = (float)$r['nilai'];
            $map[$r['periode']]['penerimaan_jumlah'] = (int)$r['jumlah'];
        }
        return array_values($map);
    }

    /** Distribusi AR per dimensi. */
    public function byDimension(string $dimension, int $limit = 10, bool $onlyOverdue = false): array
    {
        $db = Database::i();
        [$gExpr, $gLabel, $gJoin] = AgingService::groupExpr($dimension);
        [$w, $p] = $this->where();
        $extra = $onlyOverdue ? " AND i.payment_status_code = 'TERLAMBAT'" : '';
        return $db->all(
            "SELECT $gExpr AS id, $gLabel AS label,
                    COUNT(*) AS jumlah,
                    COALESCE(SUM(i.outstanding_amount),0) AS saldo,
                    COALESCE(SUM(i.gross_amount),0) AS bruto,
                    COALESCE(SUM(CASE WHEN i.payment_status_code='TERLAMBAT' THEN i.outstanding_amount ELSE 0 END),0) AS terlambat
               FROM invoices i $gJoin
              WHERE i.deleted_at IS NULL AND i.doc_status='active' AND i.outstanding_amount > 0 AND $w $extra
              GROUP BY $gExpr, $gLabel
              HAVING saldo > 0
              ORDER BY saldo DESC
              LIMIT " . (int)$limit,
            $p
        );
    }

    /** Perbandingan invoice lunas vs belum lunas. */
    public function paidVsUnpaid(): array
    {
        $db = Database::i();
        [$w, $p] = $this->where();
        $r = $db->one(
            "SELECT SUM(i.payment_status_code='LUNAS') AS lunas,
                    SUM(i.payment_status_code<>'LUNAS' AND i.doc_status='active') AS belum,
                    COALESCE(SUM(CASE WHEN i.payment_status_code='LUNAS' THEN i.net_amount ELSE 0 END),0) AS nilai_lunas,
                    COALESCE(SUM(CASE WHEN i.payment_status_code<>'LUNAS' AND i.doc_status='active' THEN i.outstanding_amount ELSE 0 END),0) AS nilai_belum
               FROM invoices i WHERE i.deleted_at IS NULL AND $w",
            $p
        );
        return (array)$r;
    }

    /** Tren ACP 12 bulan. */
    public function acpTrend(?int $year = null, ?string $method = null): array
    {
        $year = $year ?: (int)substr($this->asOf, 0, 4);
        return AcpService::monthlyTrend($year, $this->filters, $method);
    }

    /** Perkiraan penerimaan kas per minggu (R-13). */
    public function collectionForecast(int $weeks = 8): array
    {
        $db = Database::i();
        [$w, $p] = $this->where();
        $p['asof'] = $this->asOf;
        $bobotPtp = Settings::int('penagihan.bobot_janji_bayar', 90) / 100;
        $bobotJt  = Settings::int('penagihan.bobot_jatuh_tempo', 80) / 100;
        $bobotEst = Settings::int('penagihan.bobot_perkiraan', 50) / 100;

        $rows = $db->all(
            "SELECT i.id, i.outstanding_amount, i.due_date,
                    (SELECT MIN(ptp.promise_date) FROM promise_to_pay ptp
                      WHERE ptp.invoice_id = i.id AND ptp.status='TERBUKA' AND ptp.deleted_at IS NULL
                        AND ptp.promise_date >= :asof) AS janji
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status='active' AND i.outstanding_amount > 0 AND $w",
            $p
        );

        $buckets = [];
        $start = (new \DateTime($this->asOf))->modify('monday this week');
        for ($i = 0; $i < $weeks; $i++) {
            $ws = (clone $start)->modify("+$i week");
            $we = (clone $ws)->modify('+6 days');
            $buckets[$ws->format('Y-m-d')] = [
                'mulai' => $ws->format('Y-m-d'), 'selesai' => $we->format('Y-m-d'),
                'label' => 'Mgg ' . $ws->format('d/m'), 'nilai' => 0.0, 'tertimbang' => 0.0, 'jumlah' => 0,
            ];
        }
        $lewat = ['label' => 'Sudah Lewat', 'nilai' => 0.0, 'tertimbang' => 0.0, 'jumlah' => 0];
        $jauh  = ['label' => '> ' . $weeks . ' minggu', 'nilai' => 0.0, 'tertimbang' => 0.0, 'jumlah' => 0];

        foreach ($rows as $r) {
            $amt = (float)$r['outstanding_amount'];
            if ($r['janji'])                          { $tgl = $r['janji'];      $bobot = $bobotPtp; }
            elseif ($r['due_date'] >= $this->asOf)    { $tgl = $r['due_date'];   $bobot = $bobotJt; }
            else                                      { $tgl = $r['due_date'];   $bobot = $bobotEst; }

            if ($tgl < $start->format('Y-m-d')) { $lewat['nilai'] += $amt; $lewat['tertimbang'] += $amt * $bobot; $lewat['jumlah']++; continue; }
            $placed = false;
            foreach ($buckets as $k => &$b) {
                if ($tgl >= $b['mulai'] && $tgl <= $b['selesai']) {
                    $b['nilai'] += $amt; $b['tertimbang'] += $amt * $bobot; $b['jumlah']++;
                    $placed = true; break;
                }
            }
            unset($b);
            if (!$placed) { $jauh['nilai'] += $amt; $jauh['tertimbang'] += $amt * $bobot; $jauh['jumlah']++; }
        }
        return ['lewat' => $lewat, 'minggu' => array_values($buckets), 'jauh' => $jauh];
    }

    /** Ringkasan worklist penagihan untuk kartu dashboard. */
    public function collectionSummary(): array
    {
        return CollectionService::worklistCounts($this->filters, $this->asOf);
    }

    /** Opsi filter untuk dropdown dashboard. */
    public static function filterOptions(): array
    {
        $db = Database::i();
        return [
            'area' => $db->all('SELECT id, name FROM areas WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'business_unit' => $db->all('SELECT id, name FROM business_units WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'salesperson' => $db->all('SELECT id, name FROM salespersons WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'collection_officer' => $db->all('SELECT id, name FROM collection_officers WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'job_category' => $db->all('SELECT id, name FROM job_categories WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'revenue_category' => $db->all('SELECT id, name FROM revenue_categories WHERE deleted_at IS NULL AND is_active=1 ORDER BY name'),
            'payment_status' => $db->all('SELECT code AS id, name FROM payment_statuses ORDER BY sort_order'),
            'aging_config' => $db->all('SELECT id, name FROM aging_configurations WHERE deleted_at IS NULL AND is_active=1 ORDER BY is_default DESC, name'),
        ];
    }
}
