<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use App\Shared\Schema\PartySchemaSupport;
use App\Shared\Schema\SchemaSupport;
use PDO;
use RuntimeException;

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

    public function ensureSchema(): void
    {
        PartySchemaSupport::ensurePartiesSchema($this->pdo);
        if (!SchemaSupport::tableExists($this->pdo, 'purchase_returns')) {
            throw new RuntimeException('مطلوب تشغيل ملف التحديث: sql/migrate_v1_11_purchase_returns.sql');
        }
        if (!SchemaSupport::tableExists($this->pdo, 'supplier_payments')) {
            throw new RuntimeException('مطلوب تشغيل ملف التحديث: sql/migrate_v1_29_supplier_payments.sql');
        }
    }

    /** @return array<string,mixed> */
    public function report(int $partyId, string $asOf): array
    {
        $partyId = max(0, $partyId);
        $asOf = trim($asOf) !== '' ? trim($asOf) : date('Y-m-d');

        $buckets = [
            ['label' => '0-30', 'min' => 0, 'max' => 30],
            ['label' => '31-60', 'min' => 31, 'max' => 60],
            ['label' => '61-90', 'min' => 61, 'max' => 90],
            ['label' => '+90', 'min' => 91, 'max' => 100000],
        ];

        $suppliers = $partyId > 0 ? $this->fetchSupplierById($partyId) : $this->fetchSuppliers();
        $rows = [];
        $totals = array_fill_keys(array_column($buckets, 'label'), 0.0);
        $totals['total'] = 0.0;

        $invoices = $this->fetchInvoices($partyId, $asOf);
        $returnMap = $this->fetchReturnMap($asOf);
        $paymentMap = $this->fetchPaymentMap($asOf);

        $bySupplier = [];
        foreach ($invoices as $inv) {
            $invId = (int) ($inv['id'] ?? 0);
            $supplierId = (int) ($inv['party_id'] ?? 0);
            if ($invId <= 0 || $supplierId <= 0) {
                continue;
            }

            $outstanding = (float) ($inv['total'] ?? 0)
                - (float) ($returnMap[$invId] ?? 0)
                - (float) ($paymentMap[$invId] ?? 0);
            if ($outstanding <= 0.00001) {
                continue;
            }

            $days = (int) floor((strtotime($asOf) - strtotime(substr((string) ($inv['inv_date'] ?? ''), 0, 10))) / 86400);
            if ($days < 0) {
                $days = 0;
            }

            $bucketLabel = '+90';
            foreach ($buckets as $bucket) {
                if ($days >= $bucket['min'] && $days <= $bucket['max']) {
                    $bucketLabel = (string) $bucket['label'];
                    break;
                }
            }

            if (!isset($bySupplier[$supplierId])) {
                $bySupplier[$supplierId] = array_fill_keys(array_column($buckets, 'label'), 0.0);
                $bySupplier[$supplierId]['total'] = 0.0;
            }
            $bySupplier[$supplierId][$bucketLabel] += $outstanding;
            $bySupplier[$supplierId]['total'] += $outstanding;
        }

        foreach ($suppliers as $supplier) {
            $sid = (int) ($supplier['id'] ?? 0);
            $supplierBuckets = $bySupplier[$sid] ?? (array_fill_keys(array_column($buckets, 'label'), 0.0) + ['total' => 0.0]);
            if ((float) ($supplierBuckets['total'] ?? 0) <= 0.00001) {
                continue;
            }
            $rows[] = [
                'supplier' => $supplier,
                'buckets' => $supplierBuckets,
            ];
            foreach (array_column($buckets, 'label') as $label) {
                $totals[$label] += (float) ($supplierBuckets[$label] ?? 0);
            }
            $totals['total'] += (float) ($supplierBuckets['total'] ?? 0);
        }

        $selectedSupplier = ($partyId > 0 && $suppliers) ? $suppliers[0] : null;

        return [
            'as_of' => $asOf,
            'party_id' => $partyId,
            'supplier_id' => $partyId,
            'selected_supplier' => $selectedSupplier,
            'buckets' => $buckets,
            'rows' => $rows,
            'totals' => $totals,
        ];
    }

    /** @return list<array<string,mixed>> */
    private function fetchSuppliers(): array
    {
        $sql = "SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) AS code
                FROM parties
                WHERE is_supplier=1 OR is_customer=0
                ORDER BY name ASC";
        return $this->pdo->query($sql)->fetchAll() ?: [];
    }

    /** @return list<array<string,mixed>> */
    private function fetchSupplierById(int $partyId): array
    {
        $st = $this->pdo->prepare("SELECT id, name, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) AS code FROM parties WHERE id=? LIMIT 1");
        $st->execute([$partyId]);
        $row = $st->fetch();
        return $row ? [$row] : [];
    }

    /** @return list<array<string,mixed>> */
    private function fetchInvoices(int $partyId, string $asOf): array
    {
        $sql = "SELECT id, party_id, inv_date, total
                FROM purchase_invoices
                WHERE status='POSTED' AND inv_date <= ?";
        $params = [$asOf . ' 23:59:59'];
        if ($partyId > 0) {
            $sql .= ' AND party_id=?';
            $params[] = $partyId;
        }
        $sql .= ' ORDER BY inv_date ASC, id ASC';
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        return $st->fetchAll() ?: [];
    }

    /** @return array<int,float> */
    private function fetchReturnMap(string $asOf): array
    {
        $map = [];
        if (!table_exists($this->pdo, 'purchase_returns')) {
            return $map;
        }
        $st = $this->pdo->prepare("SELECT source_invoice_id, COALESCE(SUM(total),0) AS total
                                   FROM purchase_returns
                                   WHERE status='POSTED' AND ret_date <= ?
                                   GROUP BY source_invoice_id");
        $st->execute([$asOf . ' 23:59:59']);
        foreach ($st->fetchAll() ?: [] as $row) {
            $map[(int) ($row['source_invoice_id'] ?? 0)] = (float) ($row['total'] ?? 0);
        }
        return $map;
    }

    /** @return array<int,float> */
    private function fetchPaymentMap(string $asOf): array
    {
        $map = [];
        if (!table_exists($this->pdo, 'supplier_payment_allocations') || !table_exists($this->pdo, 'supplier_payments')) {
            return $map;
        }
        $st = $this->pdo->prepare("SELECT spa.purchase_invoice_id, COALESCE(SUM(spa.amount),0) AS total
                                   FROM supplier_payment_allocations spa
                                   INNER JOIN supplier_payments sp ON sp.id = spa.payment_id
                                   WHERE sp.status='POSTED' AND sp.pay_date <= ?
                                   GROUP BY spa.purchase_invoice_id");
        $st->execute([$asOf]);
        foreach ($st->fetchAll() ?: [] as $row) {
            $map[(int) ($row['purchase_invoice_id'] ?? 0)] = (float) ($row['total'] ?? 0);
        }
        return $map;
    }
}
