<?php

namespace Modules\Inventory\Services;

use Config\Database;

class InventoryReportService
{
    protected $db;

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

    /**
     * @param array<string, mixed> $get
     * @return array{date_from: string, date_to: string, warehouse_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,
            'warehouse_id' => $this->nullableInt($get['warehouse_id'] ?? null),
        ];
    }

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

        $productCount      = $this->countActiveProducts();
        $warehouseCount    = $this->countActiveWarehouses();
        $totalStockQty     = $this->sumStockQty($warehouseId);
        $lowStockCount     = $this->countLowStock($warehouseId);
        $adjustmentCount   = $this->countAdjustments($from, $to, $warehouseId);
        $stockCountDocs    = $this->countStockCounts($from, $to, $warehouseId);
        $serialInStock     = $this->countSerialsInStock();
        $batchActive       = $this->countActiveBatches($warehouseId);
        $assetActive       = $this->countActiveAssets();
        $assetPurchaseCost = $this->sumAssetPurchaseCost();

        return [
            'kpis' => [
                $this->kpi('ACTIVE PRODUCTS', 'fa-box', number_format($productCount), 'in catalog', 'positive', 'PRODUCTS'),
                $this->kpi('WAREHOUSES', 'fa-warehouse', number_format($warehouseCount), 'active locations', 'positive', 'WAREHOUSES'),
                $this->kpi('TOTAL STOCK QTY', 'fa-cubes', number_format($totalStockQty, 3), 'units on hand', 'positive', 'STOCK'),
                $this->kpi('LOW STOCK ITEMS', 'fa-triangle-exclamation', number_format($lowStockCount), 'at or below reorder', $lowStockCount > 0 ? 'negative' : 'positive', 'REORDER'),
                $this->kpi('ADJUSTMENTS', 'fa-sliders', number_format($adjustmentCount), 'in period', 'positive', 'ADJUSTMENTS'),
                $this->kpi('STOCK COUNTS', 'fa-clipboard-check', number_format($stockCountDocs), 'in period', 'positive', 'COUNTS'),
                $this->kpi('SERIALS IN STOCK', 'fa-barcode', number_format($serialInStock), 'tracked units', 'positive', 'SERIALS'),
                $this->kpi('ACTIVE BATCHES', 'fa-layer-group', number_format($batchActive), 'lot tracked', 'positive', 'BATCHES'),
                $this->kpi('ACTIVE ASSETS', 'fa-computer', number_format($assetActive), 'tagged assets', 'positive', 'ASSETS'),
                $this->kpi('ASSET VALUE', 'fa-coins', $this->money($assetPurchaseCost), 'purchase cost total', 'positive', 'ASSETS'),
            ],
            'lowStockRows'      => $this->lowStockRows($warehouseId, 10),
            'recentAdjustments' => $this->recentAdjustments($from, $to, $warehouseId, 10),
            'counts'            => [
                'products'            => $productCount,
                'warehouses'          => $warehouseCount,
                'total_stock_qty'     => $totalStockQty,
                'low_stock'           => $lowStockCount,
                'adjustments'         => $adjustmentCount,
                'stock_counts'        => $stockCountDocs,
                'serials_in_stock'    => $serialInStock,
                'batches_active'      => $batchActive,
                'assets_active'       => $assetActive,
                'asset_purchase_cost' => $assetPurchaseCost,
            ],
        ];
    }

    protected function countActiveProducts(): int
    {
        if (! $this->tableExists('products')) {
            return 0;
        }

        return $this->db->table('products')->where('status', 'active')->countAllResults();
    }

    protected function countActiveWarehouses(): int
    {
        if (! $this->tableExists('warehouses')) {
            return 0;
        }

        return $this->db->table('warehouses')->where('status', 'active')->countAllResults();
    }

    protected function sumStockQty(?int $warehouseId): float
    {
        if (! $this->tableExists('stock_balances')) {
            return 0.0;
        }

        $builder = $this->db->table('stock_balances')->select('COALESCE(SUM(quantity), 0) AS total', false);
        if ($warehouseId) {
            $builder->where('warehouse_id', $warehouseId);
        }

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

        return (float) ($row['total'] ?? 0);
    }

    protected function countLowStock(?int $warehouseId): int
    {
        if (! $this->tableExists('stock_balances') || ! $this->tableExists('products')) {
            return 0;
        }

        $builder = $this->db->table('stock_balances')
            ->join('products', 'products.id = stock_balances.product_id')
            ->where('products.reorder_level >', 0)
            ->where('stock_balances.quantity <= products.reorder_level', null, false);

        if ($warehouseId) {
            $builder->where('stock_balances.warehouse_id', $warehouseId);
        }

        return $builder->countAllResults();
    }

    /**
     * @return list<array<string, mixed>>
     */
    protected function lowStockRows(?int $warehouseId, int $limit): array
    {
        if (! $this->tableExists('stock_balances') || ! $this->tableExists('products')) {
            return [];
        }

        $builder = $this->db->table('stock_balances')
            ->select('stock_balances.*, products.name AS product_name, products.sku, products.reorder_level, warehouses.name AS warehouse_name, warehouses.code AS warehouse_code')
            ->join('products', 'products.id = stock_balances.product_id')
            ->join('warehouses', 'warehouses.id = stock_balances.warehouse_id')
            ->where('products.reorder_level >', 0)
            ->where('stock_balances.quantity <= products.reorder_level', null, false)
            ->orderBy('stock_balances.quantity', 'ASC')
            ->limit($limit);

        if ($warehouseId) {
            $builder->where('stock_balances.warehouse_id', $warehouseId);
        }

        return $builder->get()->getResultArray();
    }

    protected function countAdjustments(string $from, string $to, ?int $warehouseId): int
    {
        if (! $this->tableExists('stock_adjustments')) {
            return 0;
        }

        $builder = $this->db->table('stock_adjustments')
            ->where('adjustment_date >=', $from)
            ->where('adjustment_date <=', $to);

        if ($warehouseId) {
            $builder->where('warehouse_id', $warehouseId);
        }

        return $builder->countAllResults();
    }

    /**
     * @return list<array<string, mixed>>
     */
    protected function recentAdjustments(string $from, string $to, ?int $warehouseId, int $limit): array
    {
        if (! $this->tableExists('stock_adjustments')) {
            return [];
        }

        $builder = $this->db->table('stock_adjustments')
            ->select('stock_adjustments.*, products.name AS product_name, products.sku, warehouses.name AS warehouse_name, warehouses.code AS warehouse_code')
            ->join('products', 'products.id = stock_adjustments.product_id')
            ->join('warehouses', 'warehouses.id = stock_adjustments.warehouse_id')
            ->where('stock_adjustments.adjustment_date >=', $from)
            ->where('stock_adjustments.adjustment_date <=', $to)
            ->orderBy('stock_adjustments.id', 'DESC')
            ->limit($limit);

        if ($warehouseId) {
            $builder->where('stock_adjustments.warehouse_id', $warehouseId);
        }

        return $builder->get()->getResultArray();
    }

    protected function countStockCounts(string $from, string $to, ?int $warehouseId): int
    {
        if (! $this->tableExists('stock_counts')) {
            return 0;
        }

        $builder = $this->db->table('stock_counts')
            ->where('count_date >=', $from)
            ->where('count_date <=', $to);

        if ($warehouseId) {
            $builder->where('warehouse_id', $warehouseId);
        }

        return $builder->countAllResults();
    }

    protected function countSerialsInStock(): int
    {
        if (! $this->tableExists('product_serials')) {
            return 0;
        }

        return $this->db->table('product_serials')->where('status', 'in_stock')->countAllResults();
    }

    protected function countActiveBatches(?int $warehouseId): int
    {
        if (! $this->tableExists('product_batches')) {
            return 0;
        }

        $builder = $this->db->table('product_batches')->where('status', 'active');
        if ($warehouseId) {
            $builder->where('warehouse_id', $warehouseId);
        }

        return $builder->countAllResults();
    }

    protected function countActiveAssets(): int
    {
        if (! $this->tableExists('inventory_assets')) {
            return 0;
        }

        return $this->db->table('inventory_assets')->where('status', 'active')->countAllResults();
    }

    protected function sumAssetPurchaseCost(): float
    {
        if (! $this->tableExists('inventory_assets')) {
            return 0.0;
        }

        $row = $this->db->table('inventory_assets')
            ->select('COALESCE(SUM(purchase_cost), 0) AS total', false)
            ->where('status', 'active')
            ->get()
            ->getRowArray();

        return (float) ($row['total'] ?? 0);
    }

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

    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;
    }
}
