<?php

namespace Modules\Procurement\Services;

use Config\Database;

class ProcurementReportService
{
    protected $db;

    public function __construct()
    {
        $this->db = Database::connect();
    }

    /**
     * @param array<string, mixed> $get
     * @return array{date_from: string, date_to: string, supplier_id: ?int}
     */
    public function resolveFilters(array $get): array
    {
        $dateFrom = trim((string) ($get['date_from'] ?? ''));
        $dateTo   = trim((string) ($get['date_to'] ?? ''));

        if ($dateFrom === '' || ! $this->isValidDate($dateFrom)) {
            $dateFrom = date('Y-01-01');
        }
        if ($dateTo === '' || ! $this->isValidDate($dateTo)) {
            $dateTo = date('Y-m-d');
        }
        if ($dateFrom > $dateTo) {
            [$dateFrom, $dateTo] = [$dateTo, $dateFrom];
        }

        return [
            'date_from'   => $dateFrom,
            'date_to'     => $dateTo,
            'supplier_id' => $this->nullableInt($get['supplier_id'] ?? null),
        ];
    }

    public function summary(array $filters): array
    {
        $from       = $filters['date_from'];
        $to         = $filters['date_to'];
        $supplierId = $filters['supplier_id'];

        $activeSuppliers = $this->countActiveSuppliers($from, $to, $supplierId);
        $prs             = $this->documentSummary('purchase_requests', 'request_date', 'total', $from, $to, null);
        $rfqs            = $this->documentSummary('rfqs', 'rfq_date', 'total', $from, $to, null);
        $quotations      = $this->documentSummary('supplier_quotations', 'quotation_date', 'total', $from, $to, $supplierId);
        $pos             = $this->documentSummary('purchase_orders', 'order_date', 'total', $from, $to, $supplierId);
        $grns            = $this->documentSummary('goods_receipts', 'receipt_date', null, $from, $to, $supplierId);
        $returns         = $this->documentSummary('purchase_returns', 'return_date', 'total', $from, $to, $supplierId);
        $bills           = $this->documentSummary('vendor_bills', 'bill_date', 'total', $from, $to, $supplierId);
        $payments        = $this->documentSummary('supplier_payments', 'payment_date', 'amount', $from, $to, $supplierId);
        $contracts       = $this->documentSummary('supplier_contracts', 'created_at', 'value', $from, $to, $supplierId, true);

        $poStatusMix   = $this->statusMix('purchase_orders', 'order_date', $from, $to, $supplierId);
        $billStatusMix = $this->statusMix('vendor_bills', 'bill_date', $from, $to, $supplierId);

        return [
            'kpis' => [
                $this->kpi('ACTIVE SUPPLIERS', 'fa-truck', number_format($activeSuppliers), 'created in period', 'positive', 'VENDORS'),
                $this->kpi('PURCHASE REQUESTS', 'fa-clipboard-list', number_format($prs['count']), $this->money($prs['total']) . ' value', 'positive', 'REQUESTS'),
                $this->kpi('RFQS', 'fa-file-lines', number_format($rfqs['count']), $this->money($rfqs['total']) . ' value', 'positive', 'RFQS'),
                $this->kpi('QUOTATIONS', 'fa-file-invoice', number_format($quotations['count']), $this->money($quotations['total']) . ' value', 'positive', 'QUOTES'),
                $this->kpi('PURCHASE ORDERS', 'fa-cart-shopping', number_format($pos['count']), $this->money($pos['total']) . ' value', 'positive', 'ORDERS'),
                $this->kpi('GOODS RECEIPTS', 'fa-boxes-stacked', number_format($grns['count']), 'receipts posted', 'positive', 'GRNS'),
                $this->kpi('PURCHASE RETURNS', 'fa-box-open', number_format($returns['count']), $this->money($returns['total']) . ' value', $returns['count'] > 0 ? 'negative' : 'positive', 'RETURNS'),
                $this->kpi('VENDOR BILLS', 'fa-file-invoice-dollar', number_format($bills['count']), $this->money($bills['total']) . ' value', 'positive', 'BILLS'),
                $this->kpi('SUPPLIER PAYMENTS', 'fa-money-bill-wave', number_format($payments['count']), $this->money($payments['total']) . ' paid', 'positive', 'PAYMENTS'),
                $this->kpi('CONTRACTS', 'fa-file-signature', number_format($contracts['count']), $this->money($contracts['total']) . ' value', 'positive', 'CONTRACTS'),
            ],
            'poStatusMix'   => $poStatusMix,
            'billStatusMix' => $billStatusMix,
            'counts'        => [
                'suppliers'           => $activeSuppliers,
                'purchase_requests'   => $prs,
                'rfqs'                => $rfqs,
                'supplier_quotations' => $quotations,
                'purchase_orders'     => $pos,
                'goods_receipts'      => $grns,
                'purchase_returns'    => $returns,
                'vendor_bills'        => $bills,
                'supplier_payments'   => $payments,
                'supplier_contracts'  => $contracts,
            ],
        ];
    }

    protected function countActiveSuppliers(string $from, string $to, ?int $supplierId): int
    {
        $builder = $this->db->table('suppliers')
            ->where('status', 'active')
            ->where('created_at >=', $from . ' 00:00:00')
            ->where('created_at <=', $to . ' 23:59:59');

        if ($supplierId) {
            $builder->where('id', $supplierId);
        }

        return $builder->countAllResults();
    }

    /**
     * @return array{count: int, total: float}
     */
    protected function documentSummary(
        string $table,
        string $dateCol,
        ?string $amountCol,
        string $from,
        string $to,
        ?int $supplierId,
        bool $dateTime = false
    ): array {
        if (! $this->tableExists($table)) {
            return ['count' => 0, 'total' => 0.0];
        }

        $select = $amountCol
            ? "COUNT(*) AS cnt, COALESCE(SUM({$amountCol}), 0) AS total"
            : 'COUNT(*) AS cnt, 0 AS total';

        $builder = $this->db->table($table)->select($select, false);

        if ($dateTime) {
            $builder->where("{$dateCol} >=", $from . ' 00:00:00')
                ->where("{$dateCol} <=", $to . ' 23:59:59');
        } else {
            $builder->where("{$dateCol} >=", $from)
                ->where("{$dateCol} <=", $to);
        }

        if ($supplierId && $this->tableHasSupplier($table)) {
            $builder->where('supplier_id', $supplierId);
        }

        $row = $builder->get()->getRowArray();

        return [
            'count' => (int) ($row['cnt'] ?? 0),
            'total' => (float) ($row['total'] ?? 0),
        ];
    }

    /**
     * @return array{labels: list<string>, values: list<int>, rows: list<array{status: string, total: int|string}>}
     */
    protected function statusMix(string $table, string $dateCol, string $from, string $to, ?int $supplierId): array
    {
        if (! $this->tableExists($table)) {
            return ['labels' => ['No data'], 'values' => [0], 'rows' => []];
        }

        $builder = $this->db->table($table)
            ->select('status, COUNT(*) AS total')
            ->where("{$dateCol} >=", $from)
            ->where("{$dateCol} <=", $to)
            ->groupBy('status')
            ->orderBy('total', 'DESC');

        if ($supplierId && $this->tableHasSupplier($table)) {
            $builder->where('supplier_id', $supplierId);
        }

        $rows = $builder->get()->getResultArray();
        if ($rows === []) {
            return ['labels' => ['No data'], 'values' => [0], 'rows' => []];
        }

        return [
            'labels' => array_map(static fn ($r) => ucwords(str_replace('_', ' ', $r['status'])), $rows),
            'values' => array_map(static fn ($r) => (int) $r['total'], $rows),
            'rows'   => $rows,
        ];
    }

    protected function tableExists(string $table): bool
    {
        return $this->db->tableExists($table);
    }

    protected function tableHasSupplier(string $table): bool
    {
        return in_array($table, [
            'supplier_quotations', 'purchase_orders', 'goods_receipts',
            'purchase_returns', 'vendor_bills', 'supplier_payments', 'supplier_contracts',
        ], true);
    }

    protected function kpi(string $label, string $icon, string $value, string $badge, string $badgeType, string $dept): array
    {
        return compact('label', 'icon', 'value', 'badge', 'badgeType', 'dept');
    }

    protected function money(float $amount): string
    {
        return number_format($amount, 2);
    }

    protected function nullableInt(mixed $value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }

        return (int) $value ?: null;
    }

    protected function isValidDate(string $date): bool
    {
        $dt = \DateTime::createFromFormat('Y-m-d', $date);

        return $dt && $dt->format('Y-m-d') === $date;
    }
}
