<?php
namespace App\Models;

use App\Core\Model;

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

    public function findFull(int $id): ?array
    {
        return $this->db()->one(
            'SELECT p.*, c.name AS customer_name, b.name AS bank_name,
                    u.name AS created_by_name, ua.name AS approved_by_name
               FROM payments p
               LEFT JOIN customers c ON c.id = p.customer_id
               LEFT JOIN banks b ON b.id = p.bank_id
               LEFT JOIN users u ON u.id = p.created_by
               LEFT JOIN users ua ON ua.id = p.approved_by
              WHERE p.id = :id',
            ['id' => $id]
        );
    }

    public function allocations(int $paymentId): array
    {
        return $this->db()->all(
            'SELECT pa.*, i.invoice_no, i.invoice_date, i.due_date, i.net_amount,
                    i.outstanding_amount, i.payment_status_code, c.name AS customer_name
               FROM payment_allocations pa
               JOIN invoices i ON i.id = pa.invoice_id
               LEFT JOIN customers c ON c.id = i.customer_id
              WHERE pa.payment_id = :id AND pa.deleted_at IS NULL
              ORDER BY i.invoice_date, i.id',
            ['id' => $paymentId]
        );
    }

    public function listing(array $f = [], string $sort = 'payment_date', string $dir = 'desc', int $page = 1, int $perPage = 50): array
    {
        $w = ['p.deleted_at IS NULL'];
        $prm = [];
        if (!empty($f['cari'])) {
            $w[] = '(p.payment_no LIKE :q OR p.reference_no LIKE :q OR c.name LIKE :q
                     OR EXISTS (SELECT 1 FROM payment_allocations pa2 JOIN invoices i2 ON i2.id=pa2.invoice_id
                                 WHERE pa2.payment_id=p.id AND i2.invoice_no LIKE :q))';
            $prm['q'] = '%' . $f['cari'] . '%';
        }
        if (!empty($f['customer_id'])) { $w[] = 'p.customer_id = :c'; $prm['c'] = (int)$f['customer_id']; }
        if (!empty($f['bank_id']))     { $w[] = 'p.bank_id = :b'; $prm['b'] = (int)$f['bank_id']; }
        if (!empty($f['method']))      { $w[] = 'p.method = :m'; $prm['m'] = $f['method']; }
        if (!empty($f['payment_type'])){ $w[] = 'p.payment_type = :pt'; $prm['pt'] = $f['payment_type']; }
        if (!empty($f['status']))      { $w[] = 'p.status = :st'; $prm['st'] = $f['status']; }
        if (!empty($f['approval_status'])) { $w[] = 'p.approval_status = :ap'; $prm['ap'] = $f['approval_status']; }
        if (!empty($f['dari']))        { $w[] = 'p.payment_date >= :d1'; $prm['d1'] = $f['dari']; }
        if (!empty($f['sampai']))      { $w[] = 'p.payment_date <= :d2'; $prm['d2'] = $f['sampai']; }

        $sortable = ['payment_no' => 'p.payment_no', 'payment_date' => 'p.payment_date',
                     'customer' => 'c.name', 'amount' => 'p.amount', 'method' => 'p.method',
                     'status' => 'p.status', 'created_at' => 'p.created_at'];
        $col = $sortable[$sort] ?? 'p.payment_date';
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';
        $where = implode(' AND ', $w);

        $sql = 'SELECT p.*, c.name AS customer_name, b.name AS bank_name, u.name AS created_by_name,
                       (SELECT COUNT(*) FROM payment_allocations pa WHERE pa.payment_id = p.id AND pa.deleted_at IS NULL) AS jumlah_alokasi,
                       (SELECT GROUP_CONCAT(i3.invoice_no ORDER BY i3.invoice_no SEPARATOR ", ")
                          FROM payment_allocations pa3 JOIN invoices i3 ON i3.id = pa3.invoice_id
                         WHERE pa3.payment_id = p.id AND pa3.deleted_at IS NULL) AS daftar_invoice
                  FROM payments p
                  LEFT JOIN customers c ON c.id = p.customer_id
                  LEFT JOIN banks b ON b.id = p.bank_id
                  LEFT JOIN users u ON u.id = p.created_by
                 WHERE ' . $where . " ORDER BY $col $dir, p.id DESC";
        $countSql = 'SELECT COUNT(*) FROM payments p LEFT JOIN customers c ON c.id = p.customer_id WHERE ' . $where;
        return $this->paginate($sql, $prm, $page, $perPage, $countSql);
    }

    public function listingTotals(array $f = []): array
    {
        $w = ['p.deleted_at IS NULL'];
        $prm = [];
        if (!empty($f['dari']))   { $w[] = 'p.payment_date >= :d1'; $prm['d1'] = $f['dari']; }
        if (!empty($f['sampai'])) { $w[] = 'p.payment_date <= :d2'; $prm['d2'] = $f['sampai']; }
        if (!empty($f['customer_id'])) { $w[] = 'p.customer_id = :c'; $prm['c'] = (int)$f['customer_id']; }
        if (!empty($f['status']))      { $w[] = 'p.status = :st'; $prm['st'] = $f['status']; }
        return (array)$this->db()->one(
            'SELECT COUNT(*) AS jumlah, COALESCE(SUM(p.amount),0) AS total,
                    COALESCE(SUM(p.allocated_amount),0) AS teralokasi,
                    SUM(CASE WHEN p.status="posted" THEN 1 ELSE 0 END) AS posted,
                    SUM(CASE WHEN p.status="reversed" THEN 1 ELSE 0 END) AS dibalik,
                    SUM(CASE WHEN p.approval_status="pending" THEN 1 ELSE 0 END) AS menunggu
               FROM payments p WHERE ' . implode(' AND ', $w),
            $prm
        );
    }

    public function nextNo(?string $date = null): string
    {
        $prefix = \App\Core\Settings::str('ar.prefix_pembayaran', 'BYR');
        $ym = date('ym', strtotime($date ?: 'now'));
        $like = "$prefix/$ym/%";
        $last = $this->db()->value('SELECT payment_no FROM payments WHERE payment_no LIKE :p ORDER BY payment_no DESC LIMIT 1', ['p' => $like]);
        $n = 1;
        if ($last && preg_match('#/(\d+)$#', (string)$last, $m)) $n = (int)$m[1] + 1;
        return sprintf('%s/%s/%05d', $prefix, $ym, $n);
    }
}
