<?php
namespace App\Models;

use App\Core\Database;
use App\Core\Model;

/** Model tabel `customers`. */
class Customer extends Model
{
    protected string $table = 'customers';

    public function findByName(string $name): ?array
    {
        return $this->db()->one(
            'SELECT * FROM customers WHERE name = :n AND deleted_at IS NULL LIMIT 1',
            ['n' => $name]
        );
    }

    /** Membuat kode customer unik dari nama. */
    public function generateCode(string $name): string
    {
        $slug = strtoupper(preg_replace('/[^A-Z0-9]/', '', str_replace(' ', '', substr(self::normalizeName($name), 0, 12))) ?? '');
        if ($slug === '') $slug = 'CUST';
        $base = 'C-' . substr($slug, 0, 10);
        $code = $base;
        $i = 1;
        while ($this->db()->value('SELECT COUNT(*) FROM customers WHERE code = ?', [$code]) > 0) {
            $code = $base . '-' . (++$i);
            if ($i > 9999) { $code = 'C-' . strtoupper(bin2hex(random_bytes(4))); break; }
        }
        return $code;
    }

    /** Normalisasi nama: trim, kolaps spasi ganda, huruf besar (mengatasi DQ-17). */
    public static function normalizeName(string $n): string
    {
        $n = preg_replace('/\s+/u', ' ', trim($n)) ?? $n;
        return mb_strtoupper($n, 'UTF-8');
    }

    /** Ringkasan piutang satu customer. */
    public function arSummary(int $customerId, ?string $asOf = null): array
    {
        $asOf = $asOf ?: \App\Core\Dates::today();
        return (array)$this->db()->one(
            "SELECT COUNT(*) AS jumlah_invoice,
                    COALESCE(SUM(gross_amount),0)   AS total_bruto,
                    COALESCE(SUM(net_amount),0)     AS total_bersih,
                    COALESCE(SUM(paid_amount),0)    AS total_bayar,
                    COALESCE(SUM(outstanding_amount),0) AS total_saldo,
                    COALESCE(SUM(CASE WHEN payment_status_code='TERLAMBAT' THEN outstanding_amount ELSE 0 END),0) AS total_terlambat,
                    COALESCE(SUM(CASE WHEN payment_status_code='BELUM_JATUH_TEMPO' THEN outstanding_amount ELSE 0 END),0) AS total_belum_jt,
                    MAX(last_payment_date) AS pembayaran_terakhir,
                    AVG(CASE WHEN payment_status_code='LUNAS' THEN collection_days END) AS rata_hari_penagihan
               FROM invoices
              WHERE customer_id = :c AND deleted_at IS NULL AND doc_status='active'",
            ['c' => $customerId]
        );
    }

    /** Skor risiko customer (R-14). */
    public function riskScore(int $customerId): array
    {
        $s = $this->arSummary($customerId);
        $cust = $this->find($customerId) ?? [];
        $db = $this->db();

        $totalOut = (float)($s['total_saldo'] ?? 0);
        $overdue  = (float)($s['total_terlambat'] ?? 0);
        $rasioOverdue = $totalOut > 0 ? min(1, $overdue / $totalOut) : 0;

        $term = (float)($cust['payment_term_days'] ?? 0) ?: 30;
        $rataHari = (float)($s['rata_hari_penagihan'] ?? 0);
        $rasioHari = $rataHari > 0 ? min(1.5, $rataHari / $term) / 1.5 : 0;

        $ptp = $db->one(
            'SELECT COUNT(*) AS total, SUM(CASE WHEN status="GAGAL" THEN 1 ELSE 0 END) AS gagal
               FROM promise_to_pay WHERE customer_id = :c AND deleted_at IS NULL',
            ['c' => $customerId]
        );
        $rasioPtp = (int)($ptp['total'] ?? 0) > 0 ? (int)$ptp['gagal'] / (int)$ptp['total'] : 0;

        $limit = (float)($cust['credit_limit'] ?? 0);
        $rasioLimit = $limit > 0 ? min(1, $totalOut / $limit) : 0;

        $score = round($rasioOverdue * 40 + $rasioHari * 30 + $rasioPtp * 20 + $rasioLimit * 10, 2);
        $kategori = $score < 30 ? 'RENDAH' : ($score < 60 ? 'SEDANG' : ($score < 80 ? 'TINGGI' : 'SANGAT_TINGGI'));
        return ['skor' => $score, 'kategori' => $kategori, 'rasio_overdue' => $rasioOverdue,
                'rasio_hari' => $rasioHari, 'rasio_janji_gagal' => $rasioPtp, 'rasio_limit' => $rasioLimit];
    }
}
