<?php
namespace App\Services;

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

/** Modul penagihan: worklist, follow-up, dan janji bayar. */
class CollectionService
{
    public const METHODS = [
        'TELEPON' => 'Telepon', 'WHATSAPP' => 'WhatsApp', 'EMAIL' => 'Email',
        'KUNJUNGAN' => 'Kunjungan Langsung', 'SURAT' => 'Surat Resmi',
        'PORTAL' => 'Portal Vendor', 'LAINNYA' => 'Lainnya',
    ];

    public const PRIORITIES = ['RENDAH' => 'Rendah', 'NORMAL' => 'Normal', 'TINGGI' => 'Tinggi', 'KRITIS' => 'Kritis'];

    /** Tujuh kategori worklist penagihan. */
    public static function worklistCategories(): array
    {
        return [
            'terlambat'        => ['label' => 'Invoice Terlambat', 'icon' => 'exclamation-octagon', 'color' => 'danger'],
            'jatuh_tempo_hari_ini' => ['label' => 'Jatuh Tempo Hari Ini', 'icon' => 'calendar-event', 'color' => 'warning'],
            'jatuh_tempo_segera'   => ['label' => 'Jatuh Tempo ≤ 7 Hari', 'icon' => 'calendar-week', 'color' => 'primary'],
            'janji_ingkar'     => ['label' => 'Janji Bayar Ingkar', 'icon' => 'x-circle', 'color' => 'danger'],
            'perlu_followup'   => ['label' => 'Perlu Follow-up', 'icon' => 'telephone-plus', 'color' => 'info'],
            'nilai_besar'      => ['label' => 'Piutang Bernilai Besar', 'icon' => 'cash-coin', 'color' => 'dark'],
            'tanpa_followup'   => ['label' => 'Tanpa Follow-up Lama', 'icon' => 'hourglass-split', 'color' => 'secondary'],
        ];
    }

    /**
     * Klausa SQL tambahan untuk setiap kategori worklist.
     * @return array{0:string,1:array}
     */
    private static function categorySql(string $cat, string $asOf): array
    {
        $hariSegera = Settings::int('penagihan.hari_jatuh_tempo_segera', 7);
        $hariTanpa  = Settings::int('penagihan.hari_tanpa_followup', 14);
        $nilaiBesar = Settings::float('penagihan.nilai_invoice_besar', 10000000);

        return match ($cat) {
            'terlambat' => ["i.outstanding_amount > 0 AND i.due_date < :wl_asof", ['wl_asof' => $asOf]],
            'jatuh_tempo_hari_ini' => ["i.outstanding_amount > 0 AND i.due_date = :wl_asof", ['wl_asof' => $asOf]],
            'jatuh_tempo_segera' => ["i.outstanding_amount > 0 AND i.due_date > :wl_asof AND i.due_date <= DATE_ADD(:wl_asof2, INTERVAL $hariSegera DAY)",
                                     ['wl_asof' => $asOf, 'wl_asof2' => $asOf]],
            'janji_ingkar' => ["i.outstanding_amount > 0 AND EXISTS (
                                  SELECT 1 FROM promise_to_pay ptp WHERE ptp.invoice_id = i.id AND ptp.deleted_at IS NULL
                                    AND (ptp.status = 'GAGAL' OR (ptp.status = 'TERBUKA' AND ptp.promise_date < :wl_asof)))",
                               ['wl_asof' => $asOf]],
            'perlu_followup' => ["i.outstanding_amount > 0 AND EXISTS (
                                    SELECT 1 FROM collection_followups f WHERE f.invoice_id = i.id AND f.deleted_at IS NULL
                                      AND f.next_followup_date IS NOT NULL AND f.next_followup_date <= :wl_asof)",
                                 ['wl_asof' => $asOf]],
            'nilai_besar' => ["i.outstanding_amount >= $nilaiBesar", []],
            'tanpa_followup' => ["i.outstanding_amount > 0 AND i.due_date < :wl_asof AND NOT EXISTS (
                                    SELECT 1 FROM collection_followups f WHERE f.invoice_id = i.id AND f.deleted_at IS NULL
                                      AND f.followup_date >= DATE_SUB(:wl_asof2, INTERVAL $hariTanpa DAY))",
                                 ['wl_asof' => $asOf, 'wl_asof2' => $asOf]],
            default => ['i.outstanding_amount > 0', []],
        };
    }

    /** Jumlah & nilai per kategori worklist. */
    public static function worklistCounts(array $filters = [], ?string $asOf = null): array
    {
        $asOf = $asOf ?: Dates::today();
        $db = Database::i();
        if (!isset($filters['_scope_sql'])) {
            [$s, $sp] = Acl::invoiceScopeSql('i');
            $filters['_scope_sql'] = $s; $filters['_scope_params'] = $sp;
        }
        [$w, $p] = ArCalculator::buildFilterSql($filters, 'i');
        $out = [];
        foreach (self::worklistCategories() as $cat => $meta) {
            [$cw, $cp] = self::categorySql($cat, $asOf);
            $r = $db->one(
                "SELECT 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 $w AND ($cw)",
                array_merge($p, $cp)
            );
            $out[$cat] = array_merge($meta, ['kategori' => $cat, 'jumlah' => (int)$r['jumlah'], 'nilai' => (float)$r['nilai']]);
        }
        return $out;
    }

    /** Daftar invoice pada satu kategori worklist. */
    public static function worklist(string $cat, array $filters = [], ?string $asOf = null, string $sort = 'outstanding_amount', string $dir = 'desc', int $page = 1, int $perPage = 50): array
    {
        $asOf = $asOf ?: Dates::today();
        if (!isset($filters['_scope_sql'])) {
            [$s, $sp] = Acl::invoiceScopeSql('i');
            $filters['_scope_sql'] = $s; $filters['_scope_params'] = $sp;
        }
        [$w, $p] = ArCalculator::buildFilterSql($filters, 'i');
        [$cw, $cp] = self::categorySql($cat, $asOf);
        $p = array_merge($p, $cp);
        $p['asof_days'] = $asOf;

        $sortable = ['outstanding_amount' => 'i.outstanding_amount', 'due_date' => 'i.due_date',
                     'invoice_date' => 'i.invoice_date', 'customer' => 'c.name',
                     'aging' => 'hari_lewat', 'invoice_no' => 'i.invoice_no'];
        $col = $sortable[$sort] ?? 'i.outstanding_amount';
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';

        $sql = "SELECT i.id, i.invoice_no, i.invoice_date, i.due_date, i.net_amount, i.outstanding_amount,
                       i.payment_status_code, i.tax_invoice_no,
                       c.id AS customer_id, c.name AS customer_name, c.phone AS customer_phone,
                       c.contact_person, o.name AS outlet_name, a.name AS area_name,
                       co.name AS officer_name, s.name AS salesperson_name,
                       DATEDIFF(:asof_days, i.due_date) AS hari_lewat,
                       (SELECT f.followup_date FROM collection_followups f
                         WHERE f.invoice_id = i.id AND f.deleted_at IS NULL
                         ORDER BY f.followup_date DESC, f.id DESC LIMIT 1) AS followup_terakhir,
                       (SELECT cs.name FROM collection_followups f
                          LEFT JOIN collection_statuses cs ON cs.id = f.collection_status_id
                         WHERE f.invoice_id = i.id AND f.deleted_at IS NULL
                         ORDER BY f.followup_date DESC, f.id DESC LIMIT 1) AS status_penagihan,
                       (SELECT cs.color FROM collection_followups f
                          LEFT JOIN collection_statuses cs ON cs.id = f.collection_status_id
                         WHERE f.invoice_id = i.id AND f.deleted_at IS NULL
                         ORDER BY f.followup_date DESC, f.id DESC LIMIT 1) AS status_warna,
                       (SELECT ptp.promise_date FROM promise_to_pay ptp
                         WHERE ptp.invoice_id = i.id AND ptp.status='TERBUKA' AND ptp.deleted_at IS NULL
                         ORDER BY ptp.promise_date LIMIT 1) AS janji_bayar,
                       (SELECT ptp.promise_amount FROM promise_to_pay ptp
                         WHERE ptp.invoice_id = i.id AND ptp.status='TERBUKA' AND ptp.deleted_at IS NULL
                         ORDER BY ptp.promise_date LIMIT 1) AS janji_nilai
                  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 collection_officers co ON co.id = i.collection_officer_id
                  LEFT JOIN salespersons s ON s.id = i.salesperson_id
                 WHERE i.deleted_at IS NULL AND i.doc_status='active' AND $w AND ($cw)
                 ORDER BY $col $dir, i.id DESC";
        $countSql = "SELECT COUNT(*) FROM invoices i LEFT JOIN customers c ON c.id = i.customer_id
                      WHERE i.deleted_at IS NULL AND i.doc_status='active' AND $w AND ($cw)";

        $m = new \App\Models\CollectionFollowup();
        return $m->paginate($sql, $p, $page, $perPage, $countSql);
    }

    /** Menyimpan aktivitas follow-up (opsional beserta janji bayar). */
    public static function saveFollowup(array $data, ?array $promise = null): array
    {
        $db = Database::i();
        return $db->transaction(function () use ($db, $data, $promise) {
            $invoiceId = (int)($data['invoice_id'] ?? 0);
            $inv = $invoiceId ? $db->one('SELECT customer_id FROM invoices WHERE id = ?', [$invoiceId]) : null;

            $fid = $db->insert('collection_followups', [
                'invoice_id'   => $invoiceId ?: null,
                'customer_id'  => $data['customer_id'] ?? ($inv['customer_id'] ?? null),
                'collection_officer_id' => $data['collection_officer_id'] ?? null,
                'followup_date'=> $data['followup_date'] ?? Dates::today(),
                'method'       => $data['method'] ?? 'TELEPON',
                'contact_person' => $data['contact_person'] ?? null,
                'contact_phone'  => $data['contact_phone'] ?? null,
                'result'       => $data['result'] ?? null,
                'collection_status_id' => $data['collection_status_id'] ?? null,
                'priority'     => $data['priority'] ?? 'NORMAL',
                'next_followup_date' => $data['next_followup_date'] ?? null,
                'notes'        => $data['notes'] ?? null,
                'created_by'   => Auth::id() ?: null,
            ]);

            $ptpId = null;
            if ($promise && !empty($promise['promise_date']) && (float)($promise['promise_amount'] ?? 0) > 0) {
                $ptpId = $db->insert('promise_to_pay', [
                    'invoice_id'   => $invoiceId ?: null,
                    'customer_id'  => $data['customer_id'] ?? ($inv['customer_id'] ?? null),
                    'followup_id'  => $fid,
                    'promise_date' => $promise['promise_date'],
                    'promise_amount' => round((float)$promise['promise_amount'], 2),
                    'status'       => 'TERBUKA',
                    'notes'        => $promise['notes'] ?? null,
                    'created_by'   => Auth::id() ?: null,
                ]);
            }

            // Menugaskan petugas penagihan ke invoice bila belum ada
            if ($invoiceId && !empty($data['collection_officer_id'])) {
                $db->exec('UPDATE invoices SET collection_officer_id = :co WHERE id = :id AND collection_officer_id IS NULL',
                          ['co' => (int)$data['collection_officer_id'], 'id' => $invoiceId]);
            }

            Audit::log('penagihan', 'create', 'collection_followup', $fid,
                'Follow-up penagihan dicatat' . ($invoiceId ? ' untuk invoice #' . $invoiceId : ''), null, $data);

            return ['sukses' => true, 'followup_id' => $fid, 'promise_id' => $ptpId, 'pesan' => 'Aktivitas penagihan berhasil dicatat.'];
        });
    }

    /**
     * Mengevaluasi janji bayar yang sudah jatuh temponya:
     * DIPENUHI / SEBAGIAN / GAGAL berdasarkan pembayaran yang tercatat.
     */
    public static function evaluatePromises(?string $asOf = null): array
    {
        $asOf = $asOf ?: Dates::today();
        $db = Database::i();
        $rows = $db->all(
            "SELECT * FROM promise_to_pay
              WHERE deleted_at IS NULL AND status = 'TERBUKA' AND promise_date < :d",
            ['d' => $asOf]
        );
        $stat = ['dipenuhi' => 0, 'sebagian' => 0, 'gagal' => 0];
        foreach ($rows as $r) {
            $paid = (float)$db->value(
                "SELECT COALESCE(SUM(pa.amount),0) FROM payment_allocations pa
                   JOIN payments p ON p.id = pa.payment_id
                  WHERE pa.invoice_id = :i 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 BETWEEN :d1 AND :d2",
                ['i' => (int)$r['invoice_id'], 'd1' => $r['created_at'] ? substr((string)$r['created_at'], 0, 10) : $r['promise_date'], 'd2' => $asOf]
            );
            $target = (float)$r['promise_amount'];
            $status = $paid <= 0 ? 'GAGAL' : ($paid + 0.5 >= $target ? 'DIPENUHI' : 'SEBAGIAN');
            $db->update('promise_to_pay', ['status' => $status, 'actual_paid' => round($paid, 2), 'evaluated_at' => Dates::now()],
                        'id = :pk', ['pk' => (int)$r['id']]);
            $stat[strtolower($status)] = ($stat[strtolower($status)] ?? 0) + 1;
        }
        return $stat;
    }

    /** Daftar riwayat follow-up. */
    public static function followupListing(array $f = [], int $page = 1, int $perPage = 50): array
    {
        $db = Database::i();
        $w = ['fu.deleted_at IS NULL'];
        $p = [];
        if (!empty($f['cari'])) {
            $w[] = '(i.invoice_no LIKE :q OR c.name LIKE :q OR fu.contact_person LIKE :q OR fu.result LIKE :q)';
            $p['q'] = '%' . $f['cari'] . '%';
        }
        if (!empty($f['collection_officer_id'])) { $w[] = 'fu.collection_officer_id = :co'; $p['co'] = (int)$f['collection_officer_id']; }
        if (!empty($f['collection_status_id']))  { $w[] = 'fu.collection_status_id = :cs'; $p['cs'] = (int)$f['collection_status_id']; }
        if (!empty($f['method']))                { $w[] = 'fu.method = :m'; $p['m'] = $f['method']; }
        if (!empty($f['priority']))              { $w[] = 'fu.priority = :pr'; $p['pr'] = $f['priority']; }
        if (!empty($f['dari']))                  { $w[] = 'fu.followup_date >= :d1'; $p['d1'] = $f['dari']; }
        if (!empty($f['sampai']))                { $w[] = 'fu.followup_date <= :d2'; $p['d2'] = $f['sampai']; }

        [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
        if ($scopeSql !== '1=1') { $w[] = '(' . $scopeSql . ')'; $p = array_merge($p, $scopeParams); }
        $where = implode(' AND ', $w);

        $sql = 'SELECT fu.*, i.invoice_no, i.outstanding_amount, i.due_date,
                       c.name AS customer_name, cs.name AS status_name, cs.color AS status_color,
                       co.name AS officer_name, u.name AS created_by_name,
                       ptp.promise_date, ptp.promise_amount, ptp.status AS promise_status
                  FROM collection_followups fu
                  LEFT JOIN invoices i ON i.id = fu.invoice_id
                  LEFT JOIN customers c ON c.id = fu.customer_id
                  LEFT JOIN collection_statuses cs ON cs.id = fu.collection_status_id
                  LEFT JOIN collection_officers co ON co.id = fu.collection_officer_id
                  LEFT JOIN users u ON u.id = fu.created_by
                  LEFT JOIN promise_to_pay ptp ON ptp.followup_id = fu.id AND ptp.deleted_at IS NULL
                 WHERE ' . $where . ' ORDER BY fu.followup_date DESC, fu.id DESC';
        $countSql = 'SELECT COUNT(*) FROM collection_followups fu
                       LEFT JOIN invoices i ON i.id = fu.invoice_id
                       LEFT JOIN customers c ON c.id = fu.customer_id
                      WHERE ' . $where;
        $m = new \App\Models\CollectionFollowup();
        return $m->paginate($sql, $p, $page, $perPage, $countSql);
    }

    /** Daftar janji bayar. */
    public static function promiseListing(array $f = [], int $page = 1, int $perPage = 50): array
    {
        $db = Database::i();
        $w = ['ptp.deleted_at IS NULL'];
        $p = [];
        if (!empty($f['status'])) { $w[] = 'ptp.status = :st'; $p['st'] = $f['status']; }
        if (!empty($f['dari']))   { $w[] = 'ptp.promise_date >= :d1'; $p['d1'] = $f['dari']; }
        if (!empty($f['sampai'])) { $w[] = 'ptp.promise_date <= :d2'; $p['d2'] = $f['sampai']; }
        if (!empty($f['cari']))   { $w[] = '(i.invoice_no LIKE :q OR c.name LIKE :q)'; $p['q'] = '%' . $f['cari'] . '%'; }
        [$scopeSql, $scopeParams] = Acl::invoiceScopeSql('i');
        if ($scopeSql !== '1=1') { $w[] = '(' . $scopeSql . ')'; $p = array_merge($p, $scopeParams); }
        $where = implode(' AND ', $w);
        $sql = 'SELECT ptp.*, i.invoice_no, i.outstanding_amount, i.due_date, c.name AS customer_name,
                       u.name AS created_by_name
                  FROM promise_to_pay ptp
                  LEFT JOIN invoices i ON i.id = ptp.invoice_id
                  LEFT JOIN customers c ON c.id = ptp.customer_id
                  LEFT JOIN users u ON u.id = ptp.created_by
                 WHERE ' . $where . ' ORDER BY ptp.promise_date DESC, ptp.id DESC';
        $m = new \App\Models\PromiseToPay();
        return $m->paginate($sql, $p, $page, $perPage,
            'SELECT COUNT(*) FROM promise_to_pay ptp LEFT JOIN invoices i ON i.id = ptp.invoice_id
               LEFT JOIN customers c ON c.id = ptp.customer_id WHERE ' . $where);
    }
}
