<?php

namespace App\Services;

use App\Models\Item;
use App\Models\Warehouse;
use App\Models\InventoryDocument;
use App\Models\InventoryDocumentItem;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class InventoryReportService
{
    /**
     * ✅ حساب رصيد الصنف الفعلي من جميع الحركات (بدون تكرار)
     * يحسب الرصيد بشكل صحيح حتى لو كان هناك عدة حركات لنفس الصنف
     *
     * @param int $itemId - معرف الصنف
     * @param int $companyId - معرف الشركة
     * @param int|null $warehouseId - معرف المخزن (اختياري)
     * @return float - الرصيد الفعلي
     */
    private function calculateItemBalance($itemId, $companyId, $warehouseId = null)
    {
        $query = DB::table('inventory_document_items as idi')
            ->select(DB::raw('COALESCE(SUM(CASE
                WHEN idt.effect = "in" THEN idi.quantity
                WHEN idt.effect = "out" THEN -idi.quantity
                ELSE 0
            END), 0) as balance'))
            ->join('inventory_documents as idoc', 'idoc.id', '=', 'idi.document_id')
            ->join('inventory_document_types as idt', 'idt.id', '=', 'idoc.document_type_id')
            ->where('idi.item_id', $itemId)
            ->where('idoc.company_id', $companyId)
            ->where('idoc.status', '!=', 'cancelled');

        if ($warehouseId) {
            $query->where(function ($q) use ($warehouseId) {
                $q->where('idoc.to_warehouse_id', $warehouseId)
                  ->orWhere('idoc.from_warehouse_id', $warehouseId);
            });
        }

        return (float)($query->value('balance') ?? 0);
    }

    /**
     * الحصول على أرصدة المخزون مع الوحدات وتحويل الوحدات
     * ✅ إصلاح شامل: حساب الرصيد لكل وحدة على حدة مع معامل التحويل الصحيح
     *
     * @param array $filters
     * @return array
     */
     public function getStockBalances($filters = [])
{
    $companyId = $filters["company_id"] ?? auth()->user()->company_id;
    $warehouseFilter = $filters["warehouse_id"] ?? null;
    $categoryFilter = $filters["category_id"] ?? null;
    $itemFilter = $filters["item_id"] ?? null;

    // جلب الأصناف
    $itemsQuery = DB::table("items as i")
        ->select([
            "i.id", "i.code", "i.name", "i.category_id",
            "i.primary_unit_id", "i.min_quantity", "i.sale_price",
            "ic.name as category_name", "pu.name as primary_unit_name"
        ])
        ->leftJoin("item_categories as ic", "i.category_id", "=", "ic.id")
        ->leftJoin("units as pu", "i.primary_unit_id", "=", "pu.id")
        ->where("i.company_id", $companyId)
        ->where("i.is_active", true);

    if ($categoryFilter) {
        $itemsQuery->where("i.category_id", $categoryFilter);
    }
    if ($itemFilter) {
        $itemsQuery->where("i.id", $itemFilter);
    }

    $items = $itemsQuery->get();
    $stockBalances = [];

  foreach ($items as $item) {
    // الحصول على المخازن الفريدة
    $allWarehouses = DB::table("inventory_document_items as idi")
        ->select(DB::raw("DISTINCT CASE
            WHEN idoc.to_warehouse_id IS NOT NULL THEN idoc.to_warehouse_id
            ELSE idoc.from_warehouse_id
        END as warehouse_id"))
        ->join("inventory_documents as idoc", "idoc.id", "=", "idi.document_id")
        ->where("idi.item_id", $item->id)
        ->where("idoc.company_id", $companyId)
        ->where("idoc.status", "!=", "cancelled")
        ->get();

    foreach ($allWarehouses as $wh) {
        if (!$wh->warehouse_id) continue;
        if ($warehouseFilter && (int)$wh->warehouse_id !== (int)$warehouseFilter) continue;

        // حساب الحركات الداخلة (الإيداع)
        $inQuantity = DB::table("inventory_document_items as idi")
            ->join("inventory_documents as idoc", "idoc.id", "=", "idi.document_id")
            ->where("idi.item_id", $item->id)
            ->where("idoc.to_warehouse_id", $wh->warehouse_id)
            ->where("idoc.company_id", $companyId)
            ->where("idoc.status", "!=", "cancelled")
            ->sum("idi.quantity");

        // حساب الحركات الخارجة (الصرف)
        $outQuantity = DB::table("inventory_document_items as idi")
            ->join("inventory_documents as idoc", "idoc.id", "=", "idi.document_id")
            ->where("idi.item_id", $item->id)
            ->where("idoc.from_warehouse_id", $wh->warehouse_id)
            ->where("idoc.company_id", $companyId)
            ->where("idoc.status", "!=", "cancelled")
            ->sum("idi.quantity");

        $currentQuantity = ($inQuantity ?? 0) - ($outQuantity ?? 0);
        // ✅ تم إزالة: if ($currentQuantity <= 0) continue;
        // ✅ الآن نعرض الأصناف برصيد سالب (عجز) وصفر (فارغ)

        $warehouse = DB::table("warehouses")
            ->where("id", $wh->warehouse_id)
            ->where("company_id", $companyId)
            ->first();

        if (!$warehouse) continue;

        // ✅ تحديد حالة الرصيد
        $status = 'متاح';
        $statusColor = 'green';
        if ($currentQuantity < 0) {
            $status = 'عجز';
            $statusColor = 'red';
        } elseif ($currentQuantity == 0) {
            $status = 'فارغ';
            $statusColor = 'yellow';
        } elseif ($currentQuantity < (int)($item->min_quantity ?? 0)) {
            $status = 'منخفض';
            $statusColor = 'orange';
        }

        $stockBalances[] = (object) [
            "id" => $item->id,
            "code" => $item->code,
            "name" => $item->name,
            "category_id" => $item->category_id,
            "category_name" => $item->category_name,
            "warehouse_id" => $wh->warehouse_id,
            "warehouse_name" => $warehouse->name ?? "-",
            "primary_unit_id" => $item->primary_unit_id,
            "primary_unit_name" => $item->primary_unit_name ?? "غير محدد",
            "current_quantity" => round($currentQuantity, 2),
            "reorder_point" => (int)($item->min_quantity ?? 0),
            "unit_price" => (float)($item->sale_price ?? 0),
            "total_value" => round($currentQuantity * (float)($item->sale_price ?? 0), 2),
            "status" => $status,
            "status_color" => $statusColor
        ];
    }
}


    return $stockBalances;
}


    /**
     * الحصول على الأصناف بكميات منخفضة
     *
     * @param array $filters
     * @return array
     */
    public function getLowStockItems($filters = [])
    {
        $stockBalances = $this->getStockBalances($filters);

        $lowStockItems = $stockBalances->filter(function ($item) {
            return $item->current_quantity < $item->reorder_point;
        })->values();

        return $lowStockItems;
    }

    /**
     * الحصول على الأصناف الراكدة
     *
     * @param array $filters
     * @return array
     */
    public function getSlowMovingItems($filters = [])
    {
        $days = $filters['days'] ?? 90;
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        $cutoffDate = Carbon::now()->subDays($days);

        return DB::table('items as i')
            ->select(
                'i.id',
                'i.code',
                'i.name',
                'i.created_at',
                'i.sale_price',
                DB::raw('COALESCE(MAX(idi.created_at), i.created_at) as last_issued_date'),
                DB::raw('DATEDIFF(NOW(), COALESCE(MAX(idi.created_at), i.created_at)) as days_since_issue'),
                DB::raw('COALESCE(SUM(CASE
                    WHEN idt.effect = "in" THEN idi.quantity
                    WHEN idt.effect = "out" THEN -idi.quantity
                    ELSE 0
                END), 0) as current_quantity'),
                DB::raw('i.sale_price as unit_price'),
                DB::raw('COALESCE(SUM(CASE
                    WHEN idt.effect = "in" THEN idi.quantity
                    WHEN idt.effect = "out" THEN -idi.quantity
                    ELSE 0
                END), 0) * COALESCE(i.sale_price, 0) as total_value')
            )
            ->leftJoin('inventory_document_items as idi', 'idi.item_id', '=', 'i.id')
            ->leftJoin('inventory_documents as idoc', 'idoc.id', '=', 'idi.document_id')
            ->leftJoin('inventory_document_types as idt', 'idt.id', '=', 'idoc.document_type_id')
            ->where('i.company_id', $companyId)
            ->where(function ($q) use ($cutoffDate) {
                $q->whereNull('idi.created_at')
                    ->orWhere('idi.created_at', '<', $cutoffDate);
            })
            ->groupBy('i.id', 'i.code', 'i.name', 'i.created_at', 'i.sale_price')
            ->orderBy('last_issued_date', 'asc')
            ->get();
    }

    /**
     * الحصول على الأصناف منتهية الصلاحية
     *
     * @param array $filters
     * @return array
     */
    public function getExpiryItems($filters = [])
    {
        $days = $filters['days_warning'] ?? 30;
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        $expiryDate = Carbon::now()->addDays($days);

        return DB::table('items as i')
            ->select(
                'i.id',
                'i.code',
                'i.name',
                DB::raw('COALESCE(SUM(CASE
                    WHEN idt.effect = "in" THEN idi.quantity
                    WHEN idt.effect = "out" THEN -idi.quantity
                    ELSE 0
                END), 0) as current_quantity'),
                DB::raw('i.sale_price as unit_price'),
                DB::raw('COALESCE(SUM(CASE
                    WHEN idt.effect = "in" THEN idi.quantity
                    WHEN idt.effect = "out" THEN -idi.quantity
                    ELSE 0
                END), 0) * COALESCE(i.sale_price, 0) as total_value')
            )
            ->leftJoin('inventory_document_items as idi', 'idi.item_id', '=', 'i.id')
            ->leftJoin('inventory_documents as idoc', 'idoc.id', '=', 'idi.document_id')
            ->leftJoin('inventory_document_types as idt', 'idt.id', '=', 'idoc.document_type_id')
            ->where('i.company_id', $companyId)
            ->groupBy('i.id', 'i.code', 'i.name', 'i.sale_price')
            ->orderBy('i.name', 'asc')
            ->get();
    }

    /**
     * الحصول على حركة المخزون الشاملة
     * ✅ FIXED: Corrected unit join - idi.unit_id goes directly to u.id, not through item_units
     *
     * @param array $filters
     * @return array
     */
    public function getInventoryMovements($filters = [])
    {
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        $query = DB::table('inventory_documents as idoc')
            ->select(
                'idoc.id',
                'idoc.document_number',
                'idoc.document_date',
                'w_to.name as to_warehouse',
                'w_from.name as from_warehouse',
                'i.code as item_code',
                'i.name as item_name',
                'u.name as unit_name',
                'idi.quantity',
                'idi.unit_cost as unit_price',
                DB::raw('idi.quantity * COALESCE(idi.unit_cost, 0) as total_cost'),
                'idi.notes',
                DB::raw('idt.effect as movement_type'),
                // ✅ أعمدة منفصلة للوارد والمنصرف والمحول
                DB::raw('CASE WHEN idt.effect = "in" THEN idi.quantity ELSE 0 END as inbound_qty'),
                DB::raw('CASE WHEN idt.effect = "out" THEN idi.quantity ELSE 0 END as outbound_qty'),
                DB::raw('CASE WHEN idt.effect = "transfer" THEN idi.quantity ELSE 0 END as transfer_qty'),
                // ✅ نوع الحركة العربي
                DB::raw('CASE 
                    WHEN idt.effect = "in" THEN "وارد"
                    WHEN idt.effect = "out" THEN "منصرف"
                    WHEN idt.effect = "transfer" THEN "تحويل"
                    ELSE "أخرى"
                END as movement_ar'),
                // ✅ نوع الحركة الإنجليزي (للـ Logic)
                DB::raw('CASE 
                    WHEN idt.effect = "in" THEN "receipt"
                    WHEN idt.effect = "out" THEN "issue"
                    ELSE "transfer"
                END as document_type')
            )
            ->leftJoin('warehouses as w_to', 'idoc.to_warehouse_id', '=', 'w_to.id')
            ->leftJoin('warehouses as w_from', 'idoc.from_warehouse_id', '=', 'w_from.id')
            ->join('inventory_document_items as idi', 'idi.document_id', '=', 'idoc.id')
            ->join('items as i', 'idi.item_id', '=', 'i.id')
            ->leftJoin('units as u', 'idi.unit_id', '=', 'u.id')
            ->join('inventory_document_types as idt', 'idoc.document_type_id', '=', 'idt.id')
            ->where('idoc.company_id', $companyId);

        // ✅ تطبيق فلتر المخزن بشكل صحيح (بدون تكرار)
        if (!empty($filters['warehouse_id']) && $filters['warehouse_id'] != 0) {
            $warehouseId = (int)$filters['warehouse_id'];
            $query->where(function ($q) use ($warehouseId) {
                $q->where('idoc.to_warehouse_id', $warehouseId)
                  ->orWhere('idoc.from_warehouse_id', $warehouseId);
            });
        }

        if (!empty($filters['item_id'])) {
            $query->where('idi.item_id', $filters['item_id']);
        }

        if (!empty($filters['date_from'])) {
            $query->where('idoc.document_date', '>=', $filters['date_from']);
        }

        if (!empty($filters['date_to'])) {
            $query->where('idoc.document_date', '<=', $filters['date_to']);
        }

        $query->orderBy('idoc.document_date', 'asc')
              ->orderBy('idoc.id', 'asc');

        return $query->get();
    }

    /**
     * الحصول على قيمة المخزون
     *
     * @param array $filters
     * @return array
     */
    public function getInventoryValue($filters = [])
    {
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;
        $valuationMethod = $filters['valuation_method'] ?? 'fifo';

        $stockBalances = $this->getStockBalances([
            'company_id' => $companyId,
            'warehouse_id' => $filters['warehouse_id'] ?? null,
            'item_id' => $filters['item_id'] ?? null,
        ]);

        $totalQuantity = $stockBalances->sum('current_quantity');
        $totalValue = $stockBalances->sum('total_value');
        $averageValue = $totalQuantity > 0 ? $totalValue / $totalQuantity : 0;

        return [
            'items' => $stockBalances,
            'summary' => [
                'total_quantity' => $totalQuantity,
                'total_value' => $totalValue,
                'average_unit_cost' => $averageValue,
                'valuation_method' => $valuationMethod,
            ]
        ];
    }

    /**
     * الحصول على إيصالات الاستقبال
     *
     * @param array $filters
     * @return array
     */
    public function getReceiptsReport($filters = [])
    {
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        return $this->getInventoryMovements(array_merge($filters, [
            'company_id' => $companyId,
            'document_type' => ['in'],
        ]));
    }

    /**
     * الحصول على إذن الصرف
     *
     * @param array $filters
     * @return array
     */
    public function getIssuesReport($filters = [])
    {
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        return $this->getInventoryMovements(array_merge($filters, [
            'company_id' => $companyId,
            'document_type' => ['out'],
        ]));
    }

    /**
     * الحصول على تحويلات المخزن (من مخزن إلى آخر)
     *
     * @param array $filters
     * @return array
     */
    public function getTransfersReport($filters = [])
    {
        $companyId = $filters['company_id'] ?? auth()->user()->company_id;

        $query = DB::table('inventory_documents as idoc')
            ->select(
                'idoc.id',
                'idoc.document_number',
                'idoc.document_date',
                'w_from.name as from_warehouse',
                'w_to.name as to_warehouse',
                'i.code as item_code',
                'i.name as item_name',
                'u.name as unit_name',
                'idi.quantity',
                'idi.unit_cost',
                DB::raw('idi.quantity * COALESCE(idi.unit_cost, 0) as total_cost'),
                'idi.notes'
            )
            ->join('warehouses as w_from', 'idoc.from_warehouse_id', '=', 'w_from.id')
            ->join('warehouses as w_to', 'idoc.to_warehouse_id', '=', 'w_to.id')
            ->join('inventory_document_items as idi', 'idi.document_id', '=', 'idoc.id')
            ->join('items as i', 'idi.item_id', '=', 'i.id')
            ->leftJoin('units as u', 'idi.unit_id', '=', 'u.id')
            ->where('idoc.company_id', $companyId);

        if (!empty($filters['warehouse_id'])) {
            $query->where(function ($q) use ($filters) {
                $q->where('idoc.to_warehouse_id', $filters['warehouse_id'])
                  ->orWhere('idoc.from_warehouse_id', $filters['warehouse_id']);
            });
        }

        if (!empty($filters['item_id'])) {
            $query->where('idi.item_id', $filters['item_id']);
        }

        if (!empty($filters['date_from'])) {
            $query->where('idoc.document_date', '>=', $filters['date_from']);
        }

        if (!empty($filters['date_to'])) {
            $query->where('idoc.document_date', '<=', $filters['date_to']);
        }

        return $query->orderBy('idoc.document_date', 'desc')->get();
    }

    public function calculateTotalValue($items)
    {
        return collect($items)->sum('total_value');
    }

    public function calculateTotalQuantity($items)
    {
        return collect($items)->sum('current_quantity');
    }

    public function getStatistics($items)
    {
        $quantities = collect($items)->pluck('current_quantity');
        $values = collect($items)->pluck('total_value');

        return [
            'count' => count($items),
            'total_quantity' => $quantities->sum(),
            'average_quantity' => $quantities->average() ?? 0,
            'max_quantity' => $quantities->max() ?? 0,
            'min_quantity' => $quantities->min() ?? 0,
            'total_value' => $values->sum(),
        ];
    }
}