<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use PDO;
use Throwable;

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

    /**
     * @return array<string,mixed>
     */
    public function dashboard(
        int $daysAhead,
        int $agingDays,
        float $cashMin,
        float $bankMin,
        int $draftDays,
        int $stagnantDays
    ): array {
        $daysAhead = in_array($daysAhead, [3, 7, 14, 30], true) ? $daysAhead : 7;
        $agingDays = in_array($agingDays, [15, 30, 45, 60, 90], true) ? $agingDays : 30;
        $draftDays = in_array($draftDays, [3, 7, 14, 30], true) ? $draftDays : 7;
        $stagnantDays = in_array($stagnantDays, [30, 60, 90, 180], true) ? $stagnantDays : 60;
        $cashMin = max(0.0, $cashMin);
        $bankMin = max(0.0, $bankMin);

        $today = date('Y-m-d');
        $future = date('Y-m-d', strtotime('+' . $daysAhead . ' days'));
        $cutoff = date('Y-m-d', strtotime('-' . $agingDays . ' days'));
        $draftCutoff = date('Y-m-d H:i:s', strtotime('-' . $draftDays . ' days'));
        $stagnantCutoff = date('Y-m-d H:i:s', strtotime('-' . $stagnantDays . ' days'));

        $checkReturned = $this->safeRows(fn(): array => $this->fetchCheckReturned());
        $checkDueToday = $this->safeRows(fn(): array => $this->fetchChecksDueOn($today));
        $checkDueSoon = $this->safeRows(fn(): array => $this->fetchChecksDueBetween($today, $future));
        $checkOverdue = $this->safeRows(fn(): array => $this->fetchChecksOverdueBefore($today));
        $inventoryRows = $this->safeRows(fn(): array => $this->fetchInventoryAlerts());
        [$customerOverdue, $supplierDue] = $this->safePartyRows(fn(): array => $this->fetchPartyAlerts($cutoff));
        $stagnantRows = $this->safeRows(fn(): array => $this->fetchStagnantItems($stagnantCutoff));
        $cashboxRows = $this->safeRows(fn(): array => $this->fetchCashboxAlerts($cashMin));
        $bankRows = $this->safeRows(fn(): array => $this->fetchBankAlerts($bankMin));
        $draftSales = $this->safeRows(fn(): array => $this->fetchDraftSales($draftCutoff));
        $draftPurchases = $this->safeRows(fn(): array => $this->fetchDraftPurchases($draftCutoff));
        $draftSalesReturns = $this->safeRows(fn(): array => $this->fetchDraftSalesReturns($draftCutoff));
        $draftPurchaseReturns = $this->safeRows(fn(): array => $this->fetchDraftPurchaseReturns($draftCutoff));

        return [
            'daysAhead' => $daysAhead,
            'agingDays' => $agingDays,
            'cashMin' => $cashMin,
            'bankMin' => $bankMin,
            'draftDays' => $draftDays,
            'stagnantDays' => $stagnantDays,
            'counts' => alerts_dashboard_counts($this->pdo, $daysAhead, $agingDays, $cashMin, $bankMin, $draftDays, $stagnantDays),
            'checkReturned' => $checkReturned,
            'checkDueToday' => $checkDueToday,
            'checkDueSoon' => $checkDueSoon,
            'checkOverdue' => $checkOverdue,
            'inventoryRows' => $inventoryRows,
            'stagnantRows' => $stagnantRows,
            'customerOverdue' => $customerOverdue,
            'supplierDue' => $supplierDue,
            'cashboxRows' => $cashboxRows,
            'bankRows' => $bankRows,
            'draftSales' => $draftSales,
            'draftPurchases' => $draftPurchases,
            'draftSalesReturns' => $draftSalesReturns,
            'draftPurchaseReturns' => $draftPurchaseReturns,
            'today' => $today,
            'future' => $future,
            'cutoff' => $cutoff,
            'draftCutoff' => $draftCutoff,
            'stagnantCutoff' => $stagnantCutoff,
        ];
    }

    /** @return array<int,array<string,mixed>> */
    private function safeRows(callable $loader): array
    {
        try {
            $rows = $loader();
            return is_array($rows) ? $rows : [];
        } catch (Throwable $e) {
            error_log('Alerts dashboard row loader failed: ' . $e->getMessage());
            return [];
        }
    }

    /** @return array{0:array<int,array<string,mixed>>,1:array<int,array<string,mixed>>} */
    private function safePartyRows(callable $loader): array
    {
        try {
            $rows = $loader();
            if (is_array($rows) && array_key_exists(0, $rows) && array_key_exists(1, $rows)) {
                return [is_array($rows[0]) ? $rows[0] : [], is_array($rows[1]) ? $rows[1] : []];
            }
        } catch (Throwable $e) {
            error_log('Alerts dashboard party loader failed: ' . $e->getMessage());
        }
        return [[], []];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchCheckReturned(): array
    {
        return $this->pdo->query(
            "SELECT c.*, p.name drawer_name FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE c.current_status='returned' ORDER BY c.updated_at DESC, c.id DESC LIMIT 15"
        )->fetchAll() ?: [];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchChecksDueOn(string $date): array
    {
        $st = $this->pdo->prepare(
            "SELECT c.*, p.name drawer_name FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE c.current_status IN ('received','endorsed') AND c.due_date=? ORDER BY c.due_date, c.id DESC LIMIT 15"
        );
        $st->execute([$date]);
        return $st->fetchAll() ?: [];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchChecksDueBetween(string $from, string $to): array
    {
        $st = $this->pdo->prepare(
            "SELECT c.*, p.name drawer_name FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE c.current_status IN ('received','endorsed') AND c.due_date IS NOT NULL AND c.due_date BETWEEN ? AND ? ORDER BY c.due_date, c.id DESC LIMIT 20"
        );
        $st->execute([$from, $to]);
        return $st->fetchAll() ?: [];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchChecksOverdueBefore(string $date): array
    {
        $st = $this->pdo->prepare(
            "SELECT c.*, p.name drawer_name FROM checks c LEFT JOIN parties p ON p.id=c.drawer_party_id WHERE c.current_status IN ('received','endorsed') AND c.due_date IS NOT NULL AND c.due_date < ? ORDER BY c.due_date ASC, c.id DESC LIMIT 20"
        );
        $st->execute([$date]);
        return $st->fetchAll() ?: [];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchInventoryAlerts(): array
    {
        return $this->pdo->query(
            "SELECT i.id, i.name, COALESCE(i.barcode, '') barcode, COALESCE(i.low_stock_level, i.reorder_point, 0) min_level, COALESCE(SUM(sb.qty),0) qty
             FROM items i
             LEFT JOIN stock_balances sb ON sb.item_id=i.id
             WHERE COALESCE(i.is_active,1)=1
             GROUP BY i.id, i.name, i.barcode, i.low_stock_level, i.reorder_point
             HAVING qty <= 0 OR (min_level > 0 AND qty <= min_level)
             ORDER BY (qty<=0) DESC, qty ASC, i.name ASC LIMIT 30"
        )->fetchAll() ?: [];
    }

    /** @return array{0:array<int,array<string,mixed>>,1:array<int,array<string,mixed>>} */
    private function fetchPartyAlerts(string $cutoff): array
    {
        $partyRows = $this->pdo->query(
            "SELECT p.id, p.name, COALESCE(p.is_customer,0) is_customer, COALESCE(p.is_supplier,0) is_supplier, COALESCE(SUM(pl.debit-pl.credit),0) balance, MAX(pl.doc_date) last_doc_date
             FROM parties p
             LEFT JOIN party_ledger pl ON pl.party_id=p.id
             GROUP BY p.id, p.name, p.is_customer, p.is_supplier
             ORDER BY p.name ASC"
        )->fetchAll() ?: [];

        $customerOverdue = [];
        $supplierDue = [];
        foreach ($partyRows as $row) {
            $balance = (float) ($row['balance'] ?? 0);
            $lastDocDate = (string) ($row['last_doc_date'] ?? '');
            $isOld = $lastDocDate === '' || $lastDocDate <= $cutoff;
            if ((int) ($row['is_customer'] ?? 0) === 1 && $balance > 0 && $isOld) {
                $customerOverdue[] = $row;
            }
            if ((int) ($row['is_supplier'] ?? 0) === 1 && $balance < 0 && $isOld) {
                $supplierDue[] = $row;
            }
        }

        return [array_slice($customerOverdue, 0, 20), array_slice($supplierDue, 0, 20)];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchStagnantItems(string $cutoff): array
    {
        try {
            $st = $this->pdo->prepare(
                "SELECT i.id, i.name, COALESCE(i.barcode,'') barcode, COALESCE(SUM(sb.qty),0) qty, MAX(sm.created_at) last_move_at
                 FROM items i
                 LEFT JOIN stock_balances sb ON sb.item_id=i.id
                 LEFT JOIN stock_moves sm ON sm.item_id=i.id
                 WHERE COALESCE(i.is_active,1)=1
                 GROUP BY i.id, i.name, i.barcode
                 HAVING qty > 0 AND (last_move_at IS NULL OR last_move_at <= ?)
                 ORDER BY COALESCE(last_move_at,'1970-01-01') ASC, qty DESC, i.name ASC
                 LIMIT 20"
            );
            $st->execute([$cutoff]);
            return $st->fetchAll() ?: [];
        } catch (Throwable) {
            return [];
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchCashboxAlerts(float $cashMin): array
    {
        try {
            $rows = $this->pdo->query("SELECT id, name, opening_balance FROM cashboxes WHERE COALESCE(is_active,1)=1 ORDER BY name ASC")->fetchAll() ?: [];
            $alerts = [];
            foreach ($rows as $row) {
                $balance = cashbox_balance($this->pdo, (int) $row['id']);
                if ($balance < 0 || $balance <= $cashMin) {
                    $row['current_balance'] = $balance;
                    $alerts[] = $row;
                }
            }
            usort($alerts, static fn(array $a, array $b): int => (float) $a['current_balance'] <=> (float) $b['current_balance']);
            return array_slice($alerts, 0, 15);
        } catch (Throwable) {
            return [];
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchBankAlerts(float $bankMin): array
    {
        try {
            $rows = $this->pdo->query("SELECT id, name, bank_name, opening_balance, currency_code FROM bank_accounts WHERE COALESCE(is_active,1)=1 ORDER BY name ASC")->fetchAll() ?: [];
            $alerts = [];
            foreach ($rows as $row) {
                $balance = bank_account_balance($this->pdo, (int) $row['id']);
                if ($balance < 0 || $balance <= $bankMin) {
                    $row['current_balance'] = $balance;
                    $alerts[] = $row;
                }
            }
            usort($alerts, static fn(array $a, array $b): int => (float) $a['current_balance'] <=> (float) $b['current_balance']);
            return array_slice($alerts, 0, 15);
        } catch (Throwable) {
            return [];
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchDraftSales(string $cutoff): array
    {
        return $this->fetchDraftRows(
            "SELECT id, invoice_no doc_no, invoice_date doc_date, created_at, grand_total total, notes FROM sales_invoices WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= ? ORDER BY created_at ASC, id ASC LIMIT 10",
            $cutoff
        );
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchDraftPurchases(string $cutoff): array
    {
        return $this->fetchDraftRows(
            "SELECT id, inv_no doc_no, inv_date doc_date, created_at, total total, notes FROM purchase_invoices WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= ? ORDER BY created_at ASC, id ASC LIMIT 10",
            $cutoff
        );
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchDraftSalesReturns(string $cutoff): array
    {
        return $this->fetchDraftRows(
            "SELECT id, return_no doc_no, return_date doc_date, created_at, total_amount total, notes FROM sales_returns WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= ? ORDER BY created_at ASC, id ASC LIMIT 10",
            $cutoff
        );
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchDraftPurchaseReturns(string $cutoff): array
    {
        return $this->fetchDraftRows(
            "SELECT id, ret_no doc_no, ret_date doc_date, created_at, total total, notes FROM purchase_returns WHERE UPPER(COALESCE(status,''))='DRAFT' AND COALESCE(created_at,'1970-01-01') <= ? ORDER BY created_at ASC, id ASC LIMIT 10",
            $cutoff
        );
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchDraftRows(string $sql, string $cutoff): array
    {
        try {
            $st = $this->pdo->prepare($sql);
            $st->execute([$cutoff]);
            return $st->fetchAll() ?: [];
        } catch (Throwable) {
            return [];
        }
    }
}
