<?php
namespace App\Services;

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

/**
 * Aging piutang dengan bucket yang dapat dikonfigurasi (R-08).
 *
 * Perbaikan atas Excel:
 *  - nilai bucket memakai SALDO PIUTANG (bukan nama outlet — lihat DQ-01)
 *  - invoice dibatalkan/diganti/dihapuskan dikeluarkan dari aging
 *  - tanggal pelaporan selalu parameter, bukan TODAY() yang terpaku
 */
class AgingService
{
    /** Konfigurasi aging beserta bucket-nya. */
    public static function config(?int $configId = null): array
    {
        $db = Database::i();
        $configId = $configId ?: Settings::int('aging.konfigurasi_default', 1);
        $cfg = $db->one('SELECT * FROM aging_configurations WHERE id = :id AND deleted_at IS NULL', ['id' => $configId]);
        if (!$cfg) {
            $cfg = $db->one('SELECT * FROM aging_configurations WHERE is_default = 1 AND deleted_at IS NULL LIMIT 1');
        }
        if (!$cfg) {
            $cfg = ['id' => 0, 'code' => 'DEFAULT', 'name' => 'Standar', 'basis' => 'outstanding',
                    'reference' => 'due_date', 'include_paid' => 0];
            $buckets = [
                ['id' => 0, 'label' => '0-45 hari', 'from_days' => -99999, 'to_days' => 45, 'color' => 'success', 'sort_order' => 1],
                ['id' => 0, 'label' => '46-90 hari', 'from_days' => 46, 'to_days' => 90, 'color' => 'info', 'sort_order' => 2],
                ['id' => 0, 'label' => '91-120 hari', 'from_days' => 91, 'to_days' => 120, 'color' => 'warning', 'sort_order' => 3],
                ['id' => 0, 'label' => '> 120 hari', 'from_days' => 121, 'to_days' => null, 'color' => 'danger', 'sort_order' => 4],
            ];
        } else {
            $buckets = $db->all('SELECT * FROM aging_buckets WHERE aging_configuration_id = :id ORDER BY sort_order', ['id' => (int)$cfg['id']]);
        }
        $cfg['buckets'] = $buckets;
        return $cfg;
    }

    public static function allConfigs(): array
    {
        return Database::i()->all('SELECT * FROM aging_configurations WHERE deleted_at IS NULL AND is_active = 1 ORDER BY is_default DESC, name');
    }

    /** Ekspresi SQL untuk nilai aging menurut basis. */
    private static function valueExpr(string $basis, string $a = 'i'): string
    {
        return match ($basis) {
            'net'   => "$a.net_amount",
            'gross' => "$a.gross_amount",
            default => "$a.outstanding_amount",
        };
    }

    /** Ekspresi SQL untuk jumlah hari menurut referensi. */
    private static function daysExpr(string $reference, string $a = 'i'): string
    {
        return match ($reference) {
            'invoice_date' => "DATEDIFF(:asof_days, $a.invoice_date)",
            'sent_date'    => "DATEDIFF(:asof_days, COALESCE($a.sent_date, $a.invoice_date))",
            default        => "DATEDIFF(:asof_days, $a.due_date)",
        };
    }

    /** Ekspresi CASE yang memetakan hari ke label bucket. */
    public static function bucketCaseExpr(array $cfg, string $a = 'i'): string
    {
        $days = self::daysExpr($cfg['reference'], $a);
        $sql = 'CASE';
        foreach ($cfg['buckets'] as $i => $b) {
            $from = (int)$b['from_days'];
            $to   = $b['to_days'] === null ? null : (int)$b['to_days'];
            $cond = $to === null ? "$days >= $from" : "$days >= $from AND $days <= $to";
            $sql .= " WHEN ($cond) THEN " . (int)$i;
        }
        $sql .= ' ELSE -1 END';
        return $sql;
    }

    /**
     * Laporan aging teragregasi.
     *
     * @param array  $filters filter standar (lihat ArCalculator::buildFilterSql)
     * @param string $groupBy customer|outlet|area|business_unit|salesperson|collection_officer|
     *                        job_category|job_type|revenue_category|payment_status|
     *                        invoice_month|invoice_year|due_month|none
     * @return array{buckets:array,rows:array,total:array,config:array,as_of:string}
     */
    public static function report(array $filters = [], string $groupBy = 'customer', ?string $asOf = null, ?int $configId = null): array
    {
        $db  = Database::i();
        $cfg = self::config($configId);
        $asOf = $asOf ?: Dates::today();

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

        $valueExpr = self::valueExpr($cfg['basis']);
        $daysExpr  = self::daysExpr($cfg['reference']);

        $extraWhere = " AND i.doc_status = 'active'";
        if ((int)$cfg['include_paid'] !== 1) {
            $extraWhere .= ' AND i.outstanding_amount > ' . (float)Settings::float('ar.toleransi_lunas', 1);
        }

        [$gExpr, $gLabel, $gJoin, $gId] = self::groupExpr($groupBy);

        // Satu kolom SUM per bucket
        $cols = [];
        foreach ($cfg['buckets'] as $i => $b) {
            $from = (int)$b['from_days'];
            $to   = $b['to_days'] === null ? null : (int)$b['to_days'];
            $cond = $to === null ? "($daysExpr) >= $from" : "($daysExpr) >= $from AND ($daysExpr) <= $to";
            $cols[] = "SUM(CASE WHEN $cond THEN $valueExpr ELSE 0 END) AS b$i";
            $cols[] = "SUM(CASE WHEN $cond THEN 1 ELSE 0 END) AS c$i";
        }

        $sql = "SELECT $gExpr AS grup_id, $gLabel AS grup_label,
                       COUNT(*) AS jumlah_invoice,
                       SUM($valueExpr) AS total_nilai,
                       SUM(i.gross_amount) AS total_bruto,
                       SUM(i.net_amount) AS total_bersih,
                       SUM(i.outstanding_amount) AS total_saldo,
                       AVG($daysExpr) AS rata_hari,
                       MAX($daysExpr) AS maks_hari,
                       " . implode(",\n                       ", $cols) . "
                  FROM invoices i
                  $gJoin
                 WHERE i.deleted_at IS NULL AND $where $extraWhere
                 GROUP BY " . ($groupBy === 'none' ? '1' : $gExpr . ($gId ? ", $gLabel" : '')) . "
                 ORDER BY total_nilai DESC";

        $rows = $db->all($sql, $params);

        // Grand total
        $total = ['jumlah_invoice' => 0, 'total_nilai' => 0.0, 'total_bruto' => 0.0,
                  'total_bersih' => 0.0, 'total_saldo' => 0.0];
        foreach ($cfg['buckets'] as $i => $b) { $total["b$i"] = 0.0; $total["c$i"] = 0; }
        foreach ($rows as $r) {
            $total['jumlah_invoice'] += (int)$r['jumlah_invoice'];
            $total['total_nilai']  += (float)$r['total_nilai'];
            $total['total_bruto']  += (float)$r['total_bruto'];
            $total['total_bersih'] += (float)$r['total_bersih'];
            $total['total_saldo']  += (float)$r['total_saldo'];
            foreach ($cfg['buckets'] as $i => $b) {
                $total["b$i"] += (float)$r["b$i"];
                $total["c$i"] += (int)$r["c$i"];
            }
        }

        return ['buckets' => $cfg['buckets'], 'rows' => $rows, 'total' => $total,
                'config' => $cfg, 'as_of' => $asOf, 'group_by' => $groupBy];
    }

    /** Ringkasan komposisi aging (untuk chart dashboard). */
    public static function composition(array $filters = [], ?string $asOf = null, ?int $configId = null): array
    {
        $r = self::report($filters, 'none', $asOf, $configId);
        $out = [];
        foreach ($r['buckets'] as $i => $b) {
            $out[] = [
                'label'  => $b['label'],
                'color'  => $b['color'],
                'nilai'  => (float)($r['total']["b$i"] ?? 0),
                'jumlah' => (int)($r['total']["c$i"] ?? 0),
                'index'  => $i,
                'from_days' => (int)$b['from_days'],
                'to_days'   => $b['to_days'] === null ? null : (int)$b['to_days'],
            ];
        }
        return ['buckets' => $out, 'total' => (float)($r['total']['total_nilai'] ?? 0), 'as_of' => $r['as_of'], 'config' => $r['config']];
    }

    /** Daftar invoice pada satu bucket (drill-down). */
    public static function invoicesInBucket(int $bucketIndex, array $filters = [], ?string $asOf = null, ?int $configId = null, int $limit = 500): array
    {
        $cfg = self::config($configId);
        $asOf = $asOf ?: Dates::today();
        if (!isset($cfg['buckets'][$bucketIndex])) return [];
        $b = $cfg['buckets'][$bucketIndex];
        $from = (int)$b['from_days'];
        $to   = $b['to_days'] === null ? null : (int)$b['to_days'];

        [$where, $params] = ArCalculator::buildFilterSql($filters, 'i');
        $params['asof_days'] = $asOf;
        $daysExpr = self::daysExpr($cfg['reference']);
        $cond = $to === null ? "($daysExpr) >= $from" : "($daysExpr) >= $from AND ($daysExpr) <= $to";
        $extra = " AND i.doc_status='active'";
        if ((int)$cfg['include_paid'] !== 1) $extra .= ' AND i.outstanding_amount > ' . (float)Settings::float('ar.toleransi_lunas', 1);

        return Database::i()->all(
            "SELECT i.*, c.name AS customer_name, o.name AS outlet_name, a.name AS area_name,
                    s.name AS salesperson_name, ($daysExpr) AS hari_aging
               FROM invoices i
               LEFT JOIN customers c ON c.id = i.customer_id
               LEFT JOIN outlets   o ON o.id = i.outlet_id
               LEFT JOIN areas     a ON a.id = i.area_id
               LEFT JOIN salespersons s ON s.id = i.salesperson_id
              WHERE i.deleted_at IS NULL AND $where $extra AND ($cond)
              ORDER BY i.outstanding_amount DESC
              LIMIT " . (int)$limit,
            $params
        );
    }

    /**
     * Definisi ekspresi grouping.
     * @return array{0:string,1:string,2:string,3:bool}
     */
    public static function groupExpr(string $groupBy): array
    {
        return match ($groupBy) {
            'customer'   => ['i.customer_id', "COALESCE(c.name,'(Tanpa Customer)')", 'LEFT JOIN customers c ON c.id = i.customer_id', true],
            'outlet'     => ['i.outlet_id', "COALESCE(o.name,'(Tanpa Outlet)')", 'LEFT JOIN outlets o ON o.id = i.outlet_id', true],
            'area'       => ['i.area_id', "COALESCE(a.name,'(Tanpa Area)')", 'LEFT JOIN areas a ON a.id = i.area_id', true],
            'business_unit' => ['i.business_unit_id', "COALESCE(bu.name,'(Tanpa Unit Bisnis)')", 'LEFT JOIN business_units bu ON bu.id = i.business_unit_id', true],
            'salesperson' => ['i.salesperson_id', "COALESCE(s.name,'(Tanpa Sales)')", 'LEFT JOIN salespersons s ON s.id = i.salesperson_id', true],
            'collection_officer' => ['i.collection_officer_id', "COALESCE(co.name,'(Belum Ditugaskan)')", 'LEFT JOIN collection_officers co ON co.id = i.collection_officer_id', true],
            'job_category' => ['i.job_category_id', "COALESCE(jc.name,'(Tanpa Kategori)')", 'LEFT JOIN job_categories jc ON jc.id = i.job_category_id', true],
            'job_type'   => ['i.job_type_id', "COALESCE(jt.name,'(Tanpa Jenis Pekerjaan)')", 'LEFT JOIN job_types jt ON jt.id = i.job_type_id', true],
            'revenue_category' => ['i.revenue_category_id', "COALESCE(rc.name,'(Tanpa Kategori)')", 'LEFT JOIN revenue_categories rc ON rc.id = i.revenue_category_id', true],
            'payment_status' => ['i.payment_status_code', "COALESCE(ps.name, i.payment_status_code)", 'LEFT JOIN payment_statuses ps ON ps.code = i.payment_status_code', true],
            'invoice_month' => ["DATE_FORMAT(i.invoice_date,'%Y-%m')", "DATE_FORMAT(i.invoice_date,'%m-%Y')", '', false],
            'invoice_year'  => ["YEAR(i.invoice_date)", "YEAR(i.invoice_date)", '', false],
            'due_month'     => ["DATE_FORMAT(i.due_date,'%Y-%m')", "DATE_FORMAT(i.due_date,'%m-%Y')", '', false],
            'send_channel'  => ["COALESCE(i.send_channel,'(Tidak Diisi)')", "COALESCE(i.send_channel,'(Tidak Diisi)')", '', false],
            default         => ["'TOTAL'", "'SELURUH PIUTANG'", '', false],
        };
    }

    public static function groupOptions(): array
    {
        return [
            'customer' => 'Customer', 'outlet' => 'Outlet', 'area' => 'Area',
            'business_unit' => 'Unit Bisnis', 'salesperson' => 'Sales',
            'collection_officer' => 'Petugas Penagihan', 'job_category' => 'Kategori Pekerjaan',
            'job_type' => 'Jenis Pekerjaan', 'revenue_category' => 'PMSC / By Case',
            'payment_status' => 'Status Pembayaran', 'invoice_month' => 'Bulan Invoice',
            'invoice_year' => 'Tahun Invoice', 'due_month' => 'Bulan Jatuh Tempo',
            'send_channel' => 'Kanal Pengiriman', 'none' => 'Tanpa Pengelompokan',
        ];
    }
}
