<?php
namespace App\Services;

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

/**
 * Average Collection Period (ACP) — dua metode (R-09 & R-10).
 *
 * Metode 1 "Standar Finansial" : AR rata-rata / penjualan kredit bersih × jumlah hari
 * Metode 2 "Operasional"       : rata-rata hari aktual penagihan invoice yang lunas
 */
class AcpService
{
    public const METODE = [
        'finansial'   => 'Metode 1 — Standar Finansial (AR Rata-rata / Penjualan Kredit × Hari)',
        'operasional' => 'Metode 2 — Operasional Penagihan (Rata-rata Hari Penagihan Aktual)',
    ];

    /** ------------------------------------------------------------------
     *  METODE 1 — Standar Finansial
     *  ------------------------------------------------------------------ */
    public static function financial(string $from, string $to, array $filters = []): array
    {
        $db = Database::i();
        $days = Dates::daysInRange($from, $to);
        $openingDate = Dates::addDays($from, -1);

        $openingAr = ArCalculator::arBalanceAt($openingDate, $filters);
        $closingAr = ArCalculator::arBalanceAt($to, $filters);
        $averageAr = round(($openingAr + $closingAr) / 2, 2);

        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');
        $params['from'] = $from; $params['to'] = $to;

        $sales = $db->one(
            "SELECT COALESCE(SUM(i.net_amount),0) AS penjualan_bersih,
                    COALESCE(SUM(i.gross_amount),0) AS penjualan_bruto,
                    COALESCE(SUM(i.dpp_amount),0) AS dpp,
                    COALESCE(SUM(i.revenue_amount),0) AS revenue,
                    COUNT(*) AS jumlah_invoice
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status = 'active'
                AND i.is_revenue_counted = 1
                AND i.invoice_date BETWEEN :from AND :to
                AND $where",
            $params
        );
        $netCreditSales = (float)$sales['penjualan_bersih'];

        $acp = null;
        if ($netCreditSales > 0 && $days > 0) {
            $acp = round($averageAr / $netCreditSales * $days, 2);
        }

        $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 :from AND :to
                AND i.deleted_at IS NULL AND $where",
            $params
        );

        return [
            'metode'              => 'finansial',
            'metode_label'        => self::METODE['finansial'],
            'periode_dari'        => $from,
            'periode_sampai'      => $to,
            'jumlah_hari'         => $days,
            'ar_awal'             => $openingAr,
            'ar_akhir'            => $closingAr,
            'ar_rata_rata'        => $averageAr,
            'penjualan_kredit_bersih' => $netCreditSales,
            'penjualan_bruto'     => (float)$sales['penjualan_bruto'],
            'dpp'                 => (float)$sales['dpp'],
            'jumlah_invoice'      => (int)$sales['jumlah_invoice'],
            'penerimaan_kas'      => round($collected, 2),
            'acp_hari'            => $acp,
            'perputaran_piutang'  => $averageAr > 0 ? round($netCreditSales / $averageAr, 2) : null,
            'catatan'             => $netCreditSales <= 0
                ? 'ACP tidak dapat dihitung karena penjualan kredit bersih pada periode ini nol.' : null,
        ];
    }

    /** ------------------------------------------------------------------
     *  METODE 2 — Operasional Penagihan
     *  ------------------------------------------------------------------ */
    public static function operational(string $from, string $to, array $filters = []): array
    {
        $db = Database::i();
        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');
        $params['from'] = $from; $params['to'] = $to;

        $basis = Settings::str('acp.dasar_hari_penagihan', 'sent_date');
        $baseCol = match ($basis) {
            'invoice_date' => 'i.invoice_date',
            'due_date'     => 'i.due_date',
            default        => 'COALESCE(i.sent_date, i.invoice_date)',
        };

        $rows = $db->all(
            "SELECT i.id, i.invoice_no, i.net_amount, i.due_date, i.last_payment_date,
                    DATEDIFF(i.last_payment_date, $baseCol) AS hari,
                    DATEDIFF(i.last_payment_date, i.due_date) AS hari_telat
               FROM invoices i
              WHERE i.deleted_at IS NULL AND i.doc_status='active'
                AND i.payment_status_code = 'LUNAS'
                AND i.last_payment_date IS NOT NULL
                AND i.last_payment_date BETWEEN :from AND :to
                AND $where",
            $params
        );

        $n = count($rows);
        if ($n === 0) {
            return [
                'metode' => 'operasional', 'metode_label' => self::METODE['operasional'],
                'periode_dari' => $from, 'periode_sampai' => $to,
                'jumlah_invoice' => 0, 'nilai_tertagih' => 0.0,
                'acp_hari' => null, 'rata_sederhana' => null, 'rata_tertimbang' => null,
                'median' => null, 'minimum' => null, 'maksimum' => null,
                'persen_tepat_waktu' => null, 'persen_terlambat' => null,
                'rata_keterlambatan' => null, 'jumlah_tepat_waktu' => 0, 'jumlah_terlambat' => 0,
                'dasar_hari' => $basis,
                'catatan' => 'Tidak ada invoice lunas pada periode pembayaran ini.',
            ];
        }

        $hari = []; $wSum = 0.0; $wVal = 0.0; $onTime = 0; $lateSum = 0.0; $totalVal = 0.0;
        foreach ($rows as $r) {
            $h = (int)$r['hari'];
            $v = (float)$r['net_amount'];
            $hari[] = $h;
            $wSum += $h * $v;
            $wVal += $v;
            $totalVal += $v;
            $ht = $r['hari_telat'] === null ? null : (int)$r['hari_telat'];
            if ($ht !== null && $ht <= 0) $onTime++;
            if ($ht !== null && $ht > 0) $lateSum += $ht;
        }
        $late = $n - $onTime;

        return [
            'metode'             => 'operasional',
            'metode_label'       => self::METODE['operasional'],
            'periode_dari'       => $from,
            'periode_sampai'     => $to,
            'jumlah_invoice'     => $n,
            'nilai_tertagih'     => round($totalVal, 2),
            'acp_hari'           => round(array_sum($hari) / $n, 2),
            'rata_sederhana'     => round(array_sum($hari) / $n, 2),
            'rata_tertimbang'    => $wVal > 0 ? round($wSum / $wVal, 2) : null,
            'median'             => Num::median($hari),
            'minimum'            => min($hari),
            'maksimum'           => max($hari),
            'jumlah_tepat_waktu' => $onTime,
            'jumlah_terlambat'   => $late,
            'persen_tepat_waktu' => round($onTime / $n * 100, 2),
            'persen_terlambat'   => round($late / $n * 100, 2),
            'rata_keterlambatan' => round($lateSum / $n, 2),
            'dasar_hari'         => $basis,
            'catatan'            => null,
        ];
    }

    /** Menghitung dengan metode terpilih (atau default pengaturan). */
    public static function calculate(string $from, string $to, array $filters = [], ?string $method = null): array
    {
        $method = $method ?: Settings::str('acp.metode_default', 'operasional');
        return $method === 'finansial'
            ? self::financial($from, $to, $filters)
            : self::operational($from, $to, $filters);
    }

    /** ------------------------------------------------------------------
     *  Target & indikator
     *  ------------------------------------------------------------------ */

    /** Target ACP dengan pencarian dari scope paling spesifik ke global. */
    public static function target(int $year, ?int $month = null, array $scopes = []): ?array
    {
        $db = Database::i();
        $order = ['customer', 'salesperson', 'job_category', 'area', 'business_unit'];
        foreach ($order as $sc) {
            if (empty($scopes[$sc])) continue;
            $id = (int)$scopes[$sc];
            if ($month) {
                $t = $db->one('SELECT * FROM acp_targets WHERE scope=:s AND scope_id=:i AND period_year=:y AND period_month=:m AND deleted_at IS NULL',
                              ['s' => $sc, 'i' => $id, 'y' => $year, 'm' => $month]);
                if ($t) return $t;
            }
            $t = $db->one('SELECT * FROM acp_targets WHERE scope=:s AND scope_id=:i AND period_year=:y AND period_month IS NULL AND deleted_at IS NULL',
                          ['s' => $sc, 'i' => $id, 'y' => $year]);
            if ($t) return $t;
        }
        if ($month) {
            $t = $db->one('SELECT * FROM acp_targets WHERE scope="global" AND period_year=:y AND period_month=:m AND deleted_at IS NULL',
                          ['y' => $year, 'm' => $month]);
            if ($t) return $t;
        }
        $t = $db->one('SELECT * FROM acp_targets WHERE scope="global" AND period_year=:y AND period_month IS NULL AND deleted_at IS NULL', ['y' => $year]);
        if ($t) return $t;

        $def = Settings::float('acp.target_default_hari', 45);
        return $def > 0 ? ['scope' => 'global', 'scope_id' => null, 'period_year' => $year, 'period_month' => $month,
                           'target_days' => $def, 'tolerance_days' => Settings::float('acp.toleransi_default_hari', 5),
                           'notes' => 'Nilai default dari Pengaturan'] : null;
    }

    /** ------------------------------------------------------------------
     *  Tren & perbandingan
     *  ------------------------------------------------------------------ */

    /** Tren ACP bulanan untuk satu tahun. */
    public static function monthlyTrend(int $year, array $filters = [], ?string $method = null): array
    {
        $method = $method ?: Settings::str('acp.metode_default', 'operasional');
        $out = [];
        $today = Dates::today();
        for ($m = 1; $m <= 12; $m++) {
            $from = sprintf('%04d-%02d-01', $year, $m);
            if ($from > $today) break;
            $to = Dates::monthEnd($from);
            if ($to > $today) $to = $today;
            $r = self::calculate($from, $to, $filters, $method);
            $t = self::target($year, $m, self::scopesFromFilters($filters));
            $out[] = [
                'bulan'   => $m,
                'label'   => Dates::monthName($m, true),
                'periode' => $from . '|' . $to,
                'acp'     => $r['acp_hari'],
                'jumlah_invoice' => $r['jumlah_invoice'] ?? 0,
                'nilai'   => $r['nilai_tertagih'] ?? ($r['penerimaan_kas'] ?? 0),
                'target'  => $t ? (float)$t['target_days'] : null,
                'toleransi' => $t ? (float)$t['tolerance_days'] : null,
            ];
        }
        return $out;
    }

    /** Perbandingan periode: bulan/kuartal/tahun ini vs sebelumnya. */
    public static function comparisons(string $refDate, array $filters = [], ?string $method = null): array
    {
        $method = $method ?: Settings::str('acp.metode_default', 'operasional');
        $d = new \DateTime($refDate);
        $y = (int)$d->format('Y'); $m = (int)$d->format('n');

        $pairs = [];

        [$f1, $t1] = Dates::range('bulan_ini', $refDate);
        if ($t1 > $refDate) $t1 = $refDate;
        [$f2, $t2] = Dates::range('bulan_lalu', $refDate);
        $pairs['bulan'] = ['label' => 'Bulan Ini vs Bulan Lalu',
                           'sekarang' => ['label' => Dates::monthName($m) . " $y", 'from' => $f1, 'to' => $t1],
                           'sebelum'  => ['label' => Dates::monthName((int)(new \DateTime($f2))->format('n')) . ' ' . (new \DateTime($f2))->format('Y'), 'from' => $f2, 'to' => $t2]];

        [$f3, $t3] = Dates::range('kuartal_ini', $refDate);
        if ($t3 > $refDate) $t3 = $refDate;
        [$f4, $t4] = Dates::range('kuartal_lalu', $refDate);
        $q = intdiv($m - 1, 3) + 1;
        $pairs['kuartal'] = ['label' => 'Kuartal Ini vs Kuartal Lalu',
                             'sekarang' => ['label' => "Q$q $y", 'from' => $f3, 'to' => $t3],
                             'sebelum'  => ['label' => 'Q' . ($q > 1 ? $q - 1 : 4) . ' ' . ($q > 1 ? $y : $y - 1), 'from' => $f4, 'to' => $t4]];

        [$f5, $t5] = Dates::range('ytd', $refDate);
        $pairs['tahun'] = ['label' => 'Tahun Ini (YTD) vs Tahun Lalu (Periode Sama)',
                           'sekarang' => ['label' => "YTD $y", 'from' => $f5, 'to' => $t5],
                           'sebelum'  => ['label' => 'YTD ' . ($y - 1), 'from' => sprintf('%04d-01-01', $y - 1),
                                          'to' => (new \DateTime($refDate))->modify('-1 year')->format('Y-m-d')]];

        $out = [];
        foreach ($pairs as $key => $p) {
            $a = self::calculate($p['sekarang']['from'], $p['sekarang']['to'], $filters, $method);
            $b = self::calculate($p['sebelum']['from'], $p['sebelum']['to'], $filters, $method);
            $selisih = ($a['acp_hari'] !== null && $b['acp_hari'] !== null) ? round($a['acp_hari'] - $b['acp_hari'], 2) : null;
            $out[$key] = [
                'label' => $p['label'],
                'sekarang_label' => $p['sekarang']['label'], 'sebelum_label' => $p['sebelum']['label'],
                'sekarang' => $a, 'sebelum' => $b, 'selisih' => $selisih,
                'arah' => $selisih === null ? null : ($selisih < 0 ? 'membaik' : ($selisih > 0 ? 'memburuk' : 'stabil')),
                'persen' => ($b['acp_hari'] ?? 0) > 0 && $selisih !== null ? round($selisih / $b['acp_hari'] * 100, 1) : null,
            ];
        }
        return $out;
    }

    /**
     * ACP per dimensi (customer / sales / area / kategori) — untuk tabel peringkat.
     * Menggunakan Metode 2 dalam satu query agregat agar cepat.
     */
    public static function byDimension(string $dimension, string $from, string $to, array $filters = [], int $limit = 50): array
    {
        $db = Database::i();
        [$gExpr, $gLabel, $gJoin] = AgingService::groupExpr($dimension);
        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');
        $params['from'] = $from; $params['to'] = $to;

        $basis = Settings::str('acp.dasar_hari_penagihan', 'sent_date');
        $baseCol = match ($basis) {
            'invoice_date' => 'i.invoice_date',
            'due_date'     => 'i.due_date',
            default        => 'COALESCE(i.sent_date, i.invoice_date)',
        };

        $rows = $db->all(
            "SELECT $gExpr AS grup_id, $gLabel AS grup_label,
                    COUNT(*) AS jumlah_invoice,
                    SUM(i.net_amount) AS nilai_tertagih,
                    AVG(DATEDIFF(i.last_payment_date, $baseCol)) AS rata_hari,
                    SUM(DATEDIFF(i.last_payment_date, $baseCol) * i.net_amount) / NULLIF(SUM(i.net_amount),0) AS rata_tertimbang,
                    MIN(DATEDIFF(i.last_payment_date, $baseCol)) AS min_hari,
                    MAX(DATEDIFF(i.last_payment_date, $baseCol)) AS maks_hari,
                    SUM(CASE WHEN DATEDIFF(i.last_payment_date, i.due_date) <= 0 THEN 1 ELSE 0 END) AS tepat_waktu,
                    AVG(GREATEST(0, DATEDIFF(i.last_payment_date, i.due_date))) AS rata_telat
               FROM invoices i
               $gJoin
              WHERE i.deleted_at IS NULL AND i.doc_status='active'
                AND i.payment_status_code='LUNAS' AND i.last_payment_date IS NOT NULL
                AND i.last_payment_date BETWEEN :from AND :to
                AND $where
              GROUP BY $gExpr, $gLabel
              HAVING COUNT(*) > 0
              ORDER BY rata_hari DESC
              LIMIT " . (int)$limit,
            $params
        );

        $year = (int)substr($to, 0, 4);
        foreach ($rows as &$r) {
            $r['rata_hari'] = $r['rata_hari'] === null ? null : round((float)$r['rata_hari'], 2);
            $r['rata_tertimbang'] = $r['rata_tertimbang'] === null ? null : round((float)$r['rata_tertimbang'], 2);
            $r['rata_telat'] = $r['rata_telat'] === null ? null : round((float)$r['rata_telat'], 2);
            $r['persen_tepat_waktu'] = (int)$r['jumlah_invoice'] > 0
                ? round((int)$r['tepat_waktu'] / (int)$r['jumlah_invoice'] * 100, 1) : null;
            $scopeKey = match ($dimension) {
                'customer' => 'customer', 'salesperson' => 'salesperson',
                'area' => 'area', 'business_unit' => 'business_unit',
                'job_category' => 'job_category', default => null,
            };
            $t = $scopeKey ? self::target($year, null, [$scopeKey => (int)$r['grup_id']]) : self::target($year);
            $r['target'] = $t ? (float)$t['target_days'] : null;
            $r['toleransi'] = $t ? (float)$t['tolerance_days'] : 5.0;
            $r['indikator'] = acpIndicator($r['rata_hari'], $r['target'], $r['toleransi']);
        }
        return $rows;
    }

    private static function scopesFromFilters(array $f): array
    {
        return array_filter([
            'customer' => $f['customer_id'] ?? null,
            'salesperson' => $f['salesperson_id'] ?? null,
            'area' => $f['area_id'] ?? null,
            'business_unit' => $f['business_unit_id'] ?? null,
            'job_category' => $f['job_category_id'] ?? null,
        ], fn($v) => $v !== null && $v !== '' && !is_array($v));
    }

    /** Tren saldo AR bulanan (untuk chart). */
    public static function arTrend(int $months, array $filters = [], ?string $endDate = null): array
    {
        $endDate = $endDate ?: Dates::today();
        $out = [];
        $d = new \DateTime($endDate);
        for ($i = $months - 1; $i >= 0; $i--) {
            $x = (clone $d)->modify("-$i month");
            $eom = $x->format('Y-m-t');
            if ($eom > $endDate) $eom = $endDate;
            $out[] = [
                'periode' => $x->format('Y-m'),
                'label'   => Dates::monthName((int)$x->format('n'), true) . ' ' . $x->format('y'),
                'tanggal' => $eom,
                'saldo'   => ArCalculator::arBalanceAt($eom, $filters),
            ];
        }
        return $out;
    }
}
