<?php

declare(strict_types=1);

namespace App\Modules\Purchases\Application\Services;

use PDO;

final class SupplierOpenPurchaseInvoicesService
{
    public function __construct(private PDO $pdo)
    {
    }

    /** @return array<string,mixed> */
    public function fetch(int $partyId, int $paymentId): array
    {
        if (!table_exists($this->pdo, 'purchase_invoices') || !table_exists($this->pdo, 'supplier_payments')) {
            return ['ok' => false, 'error' => 'missing_tables'];
        }
        if (!table_exists($this->pdo, 'supplier_payment_allocations')) {
            return [
                'ok' => false,
                'error' => 'missing_allocations',
                'hint' => 'Run sql/migrate_v1_30_supplier_payment_allocations.sql',
            ];
        }

        if ($partyId <= 0) {
            return ['ok' => true, 'items' => []];
        }

        $hasReturns = table_exists($this->pdo, 'purchase_returns');
        $joinReturns = $hasReturns
            ? "LEFT JOIN (SELECT source_invoice_id, SUM(total) AS ret_total FROM purchase_returns WHERE status='POSTED' GROUP BY source_invoice_id) pr ON pr.source_invoice_id = pi.id"
            : "LEFT JOIN (SELECT NULL AS source_invoice_id, 0 AS ret_total) pr ON 1=0";

        $sql = "
SELECT
  pi.id,
  pi.inv_no,
  DATE(pi.inv_date) AS inv_date,
  pi.total,
  COALESCE(pr.ret_total, 0) AS ret_total,
  COALESCE(pa.posted_alloc, 0) AS posted_alloc,
  COALESCE(ca.cur_alloc, 0) AS cur_alloc
FROM purchase_invoices pi
$joinReturns
LEFT JOIN (
  SELECT a.purchase_invoice_id, SUM(a.amount) AS posted_alloc
  FROM supplier_payment_allocations a
  JOIN supplier_payments sp ON sp.id = a.payment_id
  WHERE sp.status = 'POSTED'
  GROUP BY a.purchase_invoice_id
) pa ON pa.purchase_invoice_id = pi.id
LEFT JOIN (
  SELECT purchase_invoice_id, SUM(amount) AS cur_alloc
  FROM supplier_payment_allocations
  WHERE payment_id = ?
  GROUP BY purchase_invoice_id
) ca ON ca.purchase_invoice_id = pi.id
WHERE COALESCE(pi.party_id, pi.supplier_id) = ?
  AND pi.status = 'POSTED'
ORDER BY pi.inv_date DESC, pi.id DESC
";

        $st = $this->pdo->prepare($sql);
        $st->execute([$paymentId, $partyId]);
        $rows = $st->fetchAll() ?: [];

        $items = [];
        foreach ($rows as $r) {
            $total = (float) $r['total'];
            $rets = (float) ($r['ret_total'] ?? 0);
            $posted = (float) $r['posted_alloc'];
            $cur = (float) $r['cur_alloc'];
            $available = ($total - $rets) - $posted + $cur;
            if ($available < 0) {
                $available = 0;
            }
            if ($available > 0.0001 || $cur > 0.0001) {
                $items[] = [
                    'id' => (int) $r['id'],
                    'inv_no' => (string) $r['inv_no'],
                    'inv_date' => (string) $r['inv_date'],
                    'total' => round($total, 2),
                    'posted_alloc' => round($posted, 2),
                    'cur_alloc' => round($cur, 2),
                    'available' => round($available, 2),
                ];
            }
        }

        return ['ok' => true, 'items' => $items];
    }
}
