<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use PDO;

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

    /**
     * @return array{rows:array<int,array<string,mixed>>,warehouses:array<int,array<string,mixed>>,warehouse_id:int,warehouse_name:string,total_cost_value:float}
     */
    public function stockReport(int $warehouseId, int $limit = 2000, int $onlyNonzero = 0): array
    {
        $warehouseId = $warehouseId > 0 ? $warehouseId : 1;
        $onlyNonzero = $onlyNonzero === 1 ? 1 : 0;
        $warehouses = $this->fetchWarehouses();
        $warehouseName = $this->resolveWarehouseName($warehouses, $warehouseId);

        $sql = "SELECT i.id, i.name, i.barcode, i.cost, i.price, COALESCE(sb.qty,0) qty
             FROM items i
             LEFT JOIN stock_balances sb ON sb.item_id=i.id AND sb.warehouse_id=:wid";
        if ($onlyNonzero === 1) {
            $sql .= " WHERE COALESCE(sb.qty,0) <> 0";
        }
        $sql .= " ORDER BY i.name LIMIT {$limit}";
        $st = $this->pdo->prepare($sql);
        $st->execute(['wid' => $warehouseId]);
        $rows = $st->fetchAll() ?: [];

        $totalCostValue = 0.0;
        foreach ($rows as $row) {
            $totalCostValue += (float) ($row['qty'] ?? 0) * (float) ($row['cost'] ?? 0);
        }

        return [
            'rows' => $rows,
            'warehouses' => $warehouses,
            'warehouse_id' => $warehouseId,
            'warehouse_name' => $warehouseName,
            'total_cost_value' => $totalCostValue,
            'only_nonzero' => $onlyNonzero,
        ];
    }



    /**
     * @return array{rows:array<int,array<string,mixed>>,warehouses:array<int,array<string,mixed>>,warehouse_id:int,warehouse_name:string,party_id:int,supplier:?array<string,mixed>,supplier_name:string,supplier_query:string,only_nonzero:int,total_cost_value:float}
     */
    public function supplierStock(int $warehouseId, int $partyId, int $onlyNonzero = 1, int $limit = 5000): array
    {
        $warehouseId = max(0, $warehouseId);
        $partyId = max(0, $partyId);
        $onlyNonzero = $onlyNonzero === 1 ? 1 : 0;

        $warehouses = $this->fetchWarehouses();
        $warehouseName = $warehouseId > 0 ? $this->resolveWarehouseName($warehouses, $warehouseId) : 'كل المخازن';
        $supplier = $this->loadParty($partyId);
        $rows = [];

        $supplierColumn = table_has_column($this->pdo, 'items', 'default_supplier_id') ? 'default_supplier_id' : null;

        if ($partyId > 0 && $supplierColumn !== null) {
            // Canonical indexed relation: items.default_supplier_id -> parties.id (supplier party).
            $supplierFilter = "i.{$supplierColumn}=:sid";

            if ($warehouseId > 0) {
                $sql = "SELECT i.id, i.name, i.barcode, i.cost, i.price, COALESCE(sb.qty,0) qty
                        FROM items i
                        LEFT JOIN stock_balances sb ON sb.item_id=i.id AND sb.warehouse_id=:wid
                        WHERE {$supplierFilter}"
                    . ($onlyNonzero === 1 ? " AND COALESCE(sb.qty,0) <> 0" : '') . "
                        ORDER BY i.name
                        LIMIT {$limit}";
                $st = $this->pdo->prepare($sql);
                $st->execute(['wid' => $warehouseId, 'sid' => $partyId]);
                $rows = $st->fetchAll() ?: [];
            } else {
                $sql = "SELECT i.id, i.name, i.barcode, i.cost, i.price, COALESCE(SUM(sb.qty),0) qty
                        FROM items i
                        LEFT JOIN stock_balances sb ON sb.item_id=i.id
                        WHERE {$supplierFilter}
                        GROUP BY i.id"
                    . ($onlyNonzero === 1 ? " HAVING COALESCE(SUM(sb.qty),0) <> 0" : '') . "
                        ORDER BY i.name
                        LIMIT {$limit}";
                $st = $this->pdo->prepare($sql);
                $st->execute(['sid' => $partyId]);
                $rows = $st->fetchAll() ?: [];
            }
        }

        $totalCostValue = 0.0;
        foreach ($rows as $row) {
            $totalCostValue += (float) ($row['qty'] ?? 0) * (float) ($row['cost'] ?? 0);
        }

        return [
            'rows' => $rows,
            'warehouses' => $warehouses,
            'warehouse_id' => $warehouseId,
            'warehouse_name' => $warehouseName,
            'party_id' => $partyId,
            'supplier' => $supplier,
            'supplier_name' => (string) ($supplier['name'] ?? ''),
            'supplier_query' => $supplier ? (string) \supplier_label($supplier) : '',
            'only_nonzero' => $onlyNonzero,
            'total_cost_value' => $totalCostValue,
        ];
    }

    /**
     * @return array{warehouse_id:int,item_id:int,from:string,to:string,opening:?float,moves:array<int,array<string,mixed>>,tot_in:float,tot_out:float,meta:array<string,string>,warehouses:array<int,array<string,mixed>>}
     */
    public function itemMovement(int $warehouseId, int $itemId, string $from, string $to): array
    {
        $warehouses = get_warehouses($this->pdo);
        $warehouseId = $warehouseId > 0 ? $warehouseId : (int) main_warehouse_id($this->pdo);
        $from = trim($from) !== '' ? trim($from) : date('Y-m-01');
        $to = trim($to) !== '' ? trim($to) : date('Y-m-d');

        $opening = null;
        $moves = [];
        $totIn = 0.0;
        $totOut = 0.0;
        $meta = [
            'warehouse_name' => '',
            'item_name' => '',
            'item_barcode' => '',
        ];

        if ($itemId > 0) {
            $meta = $this->loadItemMovementMeta($warehouseId, $itemId);
            $fromDt = $from . ' 00:00:00';
            $toExcl = date('Y-m-d', strtotime($to . ' +1 day')) . ' 00:00:00';

            $opening = $this->loadOpeningQty($warehouseId, $itemId, $fromDt);
            $moves = $this->loadMoves($warehouseId, $itemId, $fromDt, $toExcl);

            foreach ($moves as $move) {
                $totIn += (float) ($move['qty_in'] ?? 0);
                $totOut += (float) ($move['qty_out'] ?? 0);
            }

            $moves = $this->decorateReferenceLabels($moves);
        }

        return [
            'warehouses' => $warehouses,
            'warehouse_id' => $warehouseId,
            'item_id' => $itemId,
            'from' => $from,
            'to' => $to,
            'opening' => $opening,
            'moves' => $moves,
            'tot_in' => $totIn,
            'tot_out' => $totOut,
            'meta' => $meta,
        ];
    }

    /** @return ?array<string,mixed> */
    private function loadParty(int $partyId): ?array
    {
        if ($partyId <= 0) {
            return null;
        }

        $st = $this->pdo->prepare("SELECT id, COALESCE(code, CONCAT('P',LPAD(id,5,'0'))) code, name, phone FROM parties WHERE id=? AND COALESCE(is_supplier,0)=1 LIMIT 1");
        $st->execute([$partyId]);
        $row = $st->fetch();

        return $row ?: null;
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchWarehouses(): array
    {
        return $this->pdo->query('SELECT id, name FROM warehouses ORDER BY name')->fetchAll() ?: [];
    }

    /** @param array<int,array<string,mixed>> $warehouses */
    private function resolveWarehouseName(array $warehouses, int $warehouseId): string
    {
        foreach ($warehouses as $warehouse) {
            if ((int) ($warehouse['id'] ?? 0) === $warehouseId) {
                return (string) ($warehouse['name'] ?? 'المخزن');
            }
        }

        return 'المخزن';
    }

    /** @return array{warehouse_name:string,item_name:string,item_barcode:string} */
    private function loadItemMovementMeta(int $warehouseId, int $itemId): array
    {
        $meta = ['warehouse_name' => '', 'item_name' => '', 'item_barcode' => ''];

        $st = $this->pdo->prepare('SELECT name, barcode FROM items WHERE id=? LIMIT 1');
        $st->execute([$itemId]);
        if ($row = $st->fetch()) {
            $meta['item_name'] = (string) ($row['name'] ?? '');
            $meta['item_barcode'] = (string) ($row['barcode'] ?? '');
        }

        $st = $this->pdo->prepare('SELECT name FROM warehouses WHERE id=? LIMIT 1');
        $st->execute([$warehouseId]);
        $meta['warehouse_name'] = (string) ($st->fetchColumn() ?: '');

        return $meta;
    }

    private function loadOpeningQty(int $warehouseId, int $itemId, string $fromDt): float
    {
        $st = $this->pdo->prepare(
            'SELECT COALESCE(SUM(qty_in - qty_out),0) bal FROM stock_moves WHERE warehouse_id=? AND item_id=? AND created_at < ?'
        );
        $st->execute([$warehouseId, $itemId, $fromDt]);
        return (float) $st->fetchColumn();
    }

    /** @return array<int,array<string,mixed>> */
    private function loadMoves(int $warehouseId, int $itemId, string $fromDt, string $toExcl): array
    {
        $st = $this->pdo->prepare(
            "SELECT sm.*, u.username
             FROM stock_moves sm
             LEFT JOIN users u ON u.id = sm.user_id
             WHERE sm.warehouse_id=? AND sm.item_id=?
               AND sm.created_at >= ? AND sm.created_at < ?
             ORDER BY sm.created_at ASC, sm.id ASC"
        );
        $st->execute([$warehouseId, $itemId, $fromDt, $toExcl]);
        return $st->fetchAll() ?: [];
    }

    /** @param array<int,array<string,mixed>> $moves
     *  @return array<int,array<string,mixed>>
     */
    private function decorateReferenceLabels(array $moves): array
    {
        $whIds = $opIds = $saleIds = $piIds = $prIds = [];
        foreach ($moves as $move) {
            $type = strtoupper((string) ($move['ref_type'] ?? ''));
            $refId = (int) ($move['ref_id'] ?? 0);
            if ($refId <= 0) {
                continue;
            }
            if (in_array($type, ['RECEIPT', 'ISSUE', 'TRANSFER', 'ADJUST', 'SCRAP'], true)) {
                $whIds[$refId] = true;
            } elseif ($type === 'OPENING') {
                $opIds[$refId] = true;
            } elseif ($type === 'SALE') {
                $saleIds[$refId] = true;
            } elseif ($type === 'PURCHASE_INV') {
                $piIds[$refId] = true;
            } elseif ($type === 'PURCHASE_RET') {
                $prIds[$refId] = true;
            }
        }

        $whMap = $this->fetchDocMap($whIds, 'wh_docs', 'id, doc_no, doc_type');
        $opMap = $this->fetchDocMap($opIds, 'opening_docs', 'id, doc_no');
        $saleMap = $this->fetchDocMap($saleIds, 'sales_invoices', 'id, invoice_no');
        $piMap = table_exists($this->pdo, 'purchase_invoices') ? $this->fetchScalarMap($piIds, 'purchase_invoices', 'id', 'inv_no') : [];
        $prMap = table_exists($this->pdo, 'purchase_returns') ? $this->fetchScalarMap($prIds, 'purchase_returns', 'id', 'ret_no') : [];

        foreach ($moves as &$move) {
            $type = strtoupper((string) ($move['ref_type'] ?? ''));
            $refId = (int) ($move['ref_id'] ?? 0);
            $label = $type . '#' . $refId;

            if (in_array($type, ['RECEIPT', 'ISSUE', 'TRANSFER', 'ADJUST', 'SCRAP'], true) && isset($whMap[$refId])) {
                $doc = $whMap[$refId];
                $docNo = (string) ($doc['doc_no'] ?? ($type . '#' . $refId));
                $docType = strtoupper((string) ($doc['doc_type'] ?? $type));
                $label = match ($docType) {
                    'RECEIPT' => 'إذن إضافة: ' . $docNo,
                    'ISSUE' => 'إذن صرف: ' . $docNo,
                    'TRANSFER' => 'تحويل مخزني: ' . $docNo,
                    'ADJUST' => 'تسوية مخزون: ' . $docNo,
                    'SCRAP' => 'هالك: ' . $docNo,
                    default => doc_type_label($docType) . ': ' . $docNo,
                };
            } elseif ($type === 'OPENING' && isset($opMap[$refId])) {
                $label = 'رصيد افتتاحي: ' . (string) ($opMap[$refId]['doc_no'] ?? $label);
            } elseif ($type === 'SALE' && isset($saleMap[$refId])) {
                $label = 'فاتورة مبيعات: ' . (string) ($saleMap[$refId]['invoice_no'] ?? $label);
            } elseif ($type === 'PURCHASE_INV' && isset($piMap[$refId])) {
                $label = 'فاتورة شراء: ' . (string) $piMap[$refId];
            } elseif ($type === 'PURCHASE_RET' && isset($prMap[$refId])) {
                $label = 'مرتجع شراء: ' . (string) $prMap[$refId];
            }

            $move['ref_label'] = $label;
        }
        unset($move);

        return $moves;
    }

    /** @param array<int,bool> $ids
     *  @return array<int,array<string,mixed>>
     */
    private function fetchDocMap(array $ids, string $table, string $columns): array
    {
        if ($ids === []) {
            return [];
        }
        $keys = array_keys($ids);
        $in = implode(',', array_fill(0, count($keys), '?'));
        $st = $this->pdo->prepare("SELECT {$columns} FROM {$table} WHERE id IN ({$in})");
        $st->execute($keys);

        $map = [];
        foreach ($st->fetchAll() ?: [] as $row) {
            $map[(int) ($row['id'] ?? 0)] = $row;
        }
        return $map;
    }

    /** @param array<int,bool> $ids
     *  @return array<int,string>
     */
    private function fetchScalarMap(array $ids, string $table, string $idColumn, string $valueColumn): array
    {
        if ($ids === []) {
            return [];
        }
        $keys = array_keys($ids);
        $in = implode(',', array_fill(0, count($keys), '?'));
        $st = $this->pdo->prepare("SELECT {$idColumn}, {$valueColumn} FROM {$table} WHERE {$idColumn} IN ({$in})");
        $st->execute($keys);

        $map = [];
        foreach ($st->fetchAll() ?: [] as $row) {
            $map[(int) ($row[$idColumn] ?? 0)] = (string) ($row[$valueColumn] ?? '');
        }
        return $map;
    }
}
