<?php

namespace App\Services;

use App\Models\Company;
use App\Models\GrnItemBatch;
use App\Models\RawMaterialItem;
use App\Models\StoreIssueRequisition;
use App\Models\StoreIssueRequisitionItem;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Carbon\Carbon;

class StoreIssueRequisitionService
{
    public function getAll()
    {
        $requisitions = StoreIssueRequisition::with(['chart_of_account', 'product_variation', 'batch', 'company'])->orderBy('created_at', 'desc')->get();
        return $requisitions;
    }

    public function create(array $data)
    {
        // dd($data);
        DB::beginTransaction();
        try {
            $items = $data['items'] ?? [];

            $data['chart_of_account_id'] = $data['chart_of_account'] ?? null;
            $data['sub_chart_of_account_id'] = $data['sub_chart_of_account'] ?? null;
            $data['product_variation_id'] = $data['product_variation'] ?? null;
            $data['batch_id'] = $data['batch'] ?? null;
            $data['company_id'] = $data['company'];

            $requisition = StoreIssueRequisition::create($data);

            foreach ($items as $item) {
                $this->processRequisitionItem($requisition, $item, $data);
            }

            DB::commit();
            return $requisition;
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    public function update(StoreIssueRequisition $storeIssueRequisition, array $data)
    {
        DB::beginTransaction();
        try {
            $items = $data['items'];
            $data['chart_of_account_id'] = $data['chart_of_account'] ?? null;
            $data['sub_chart_of_account_id'] = $data['sub_chart_of_account'] ?? null;
            $data['product_variation_id'] = $data['product_variation'] ?? null;
            $data['batch_id'] = $data['batch'] ?? null;
            $data['company_id'] = $data['company'];

            // Before updating, we need to revert previous stock deductions
            foreach ($storeIssueRequisition->items as $item) {
                if ($item->batch_id && $item->raw_material_item_id) {
                    $batch = GrnItemBatch::find($item->batch_id);
                    if ($batch) {
                        $batch->update([
                            'sir_issued_qty' => max(0, ($batch->sir_issued_qty ?? 0) - $item->quantity),
                        ]);
                    }
                }
            }

            $storeIssueRequisition->items()->delete();
            $storeIssueRequisition->stocks()->delete();

            $storeIssueRequisition->update($data);

            foreach ($items as $item) {
                $this->processRequisitionItem($storeIssueRequisition, $item, $data);
            }

            DB::commit();
            return $storeIssueRequisition;
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    /**
     * Process a single requisition item, performing FIFO distribution across GRN batches.
     */
    private function processRequisitionItem(StoreIssueRequisition $requisition, array $item, array $data)
    {
        if (isset($item['product'])) {
            if (str_starts_with($item['product'], 'product_')) {
                $item['product_id'] = str_replace('product_', '', $item['product']);
                $item['raw_material_item_id'] = null;
                $item['product_variation_id'] = null;
            } elseif (str_starts_with($item['product'], 'raw_')) {
                $item['raw_material_item_id'] = str_replace('raw_', '', $item['product']);
                $item['product_id'] = null;
                $item['product_variation_id'] = null;
            } elseif (str_starts_with($item['product'], 'var_')) {
                $item['product_variation_id'] = str_replace('var_', '', $item['product']);
                $item['raw_material_item_id'] = null;
                $item['product_id'] = null;
            }
        } elseif (isset($item['raw_material_item'])) {
            $item['raw_material_item_id'] = $item['raw_material_item'] ?? null;
        }

        $totalToIssue = (float) ($item['quantity'] ?? 0);

        // Find applicable GRN batches based on item type
        $batches = GrnItemBatch::whereHas('grnItem', function ($q) use ($item) {
            if (!empty($item['raw_material_item_id'])) {
                $q->where('raw_material_item_id', $item['raw_material_item_id']);
            } elseif (!empty($item['product_id'])) {
                $q->where('product_id', $item['product_id']);
            } elseif (!empty($item['product_variation_id'])) {
                $q->where('product_variation_id', $item['product_variation_id']);
            }
        })
        ->whereRaw('receiving_quantity > COALESCE(sir_issued_qty, 0)')
        ->orderBy('id', 'asc')
        ->get();

        if ($batches->count() > 0 && $totalToIssue > 0) {
            foreach ($batches as $batch) {
                if ($totalToIssue <= 0) break;

                $available = (float) $batch->receiving_quantity - (float) ($batch->sir_issued_qty ?? 0);
                if ($available <= 0) continue;

                $toTake = min($totalToIssue, $available);

                $splitItem = $item;
                $splitItem['batch_id'] = $batch->id;
                $splitItem['quantity'] = $toTake;
                $splitItem['total'] = $toTake * (float) $item['unit_price'];

                $requisition->items()->create($splitItem);
                
                $splitItem['company_id'] = $data['company_id'];
                $requisition->stocks()->create($splitItem);

                $batch->update([
                    'sir_issued_qty' => (float) ($batch->sir_issued_qty ?? 0) + $toTake,
                ]);

                $totalToIssue -= $toTake;
            }
        }

        // Fallback for any remaining quantity that couldn't be assigned to a batch
        if ($totalToIssue > 0) {
            $item['quantity'] = $totalToIssue;
            $item['total'] = $totalToIssue * (float) $item['unit_price'];
            $item['batch_id'] = $item['batch'] ?? null;
            
            $requisition->items()->create($item);
            $item['company_id'] = $data['company_id'];
            $requisition->stocks()->create($item);
        }
    }

    public function delete(StoreIssueRequisition $storeIssueRequisition)
    {
        DB::beginTransaction();
        try {
            // Revert sir_issued_qty on batches
            foreach ($storeIssueRequisition->items as $item) {
                if ($item->batch_id) {
                    $batch = GrnItemBatch::find($item->batch_id);
                    if ($batch) {
                        $batch->update([
                            'sir_issued_qty' => max(0, ($batch->sir_issued_qty ?? 0) - $item->quantity),
                        ]);
                    }
                }
            }

            $storeIssueRequisition->stocks()->delete();
            $result = $storeIssueRequisition->delete();
            
            DB::commit();
            return $result;
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    public function getById($id)
    {
        return StoreIssueRequisition::findOrFail($id);
    }

    public function createCode($company_id)
    {
        $lastRequisitionCode = StoreIssueRequisition::latest('id')->where('company_id', $company_id)->value('code');
        if ($lastRequisitionCode) {
            $numberPart = (int)substr($lastRequisitionCode, 3);
            $newNumberPart = $numberPart + 1;
            $newRequisitionCode = 'SIR' . str_pad($newNumberPart, 5, '0', STR_PAD_LEFT);
        }
        else {
            $newRequisitionCode = 'SIR00001';
        }
        return $newRequisitionCode;
    }

    public function getConsumptionReport($from_date, $to_date, $raw_material_item_id = null)
    {
       
        if (is_null($raw_material_item_id)) {
            return collect(); // empty collection
         }
        $storeIssueRequisition = StoreIssueRequisitionItem::select(
            'raw_material_item_id',
            'raw_material_items.name',
            'raw_material_items.code',
            'raw_material_items.unit',
            DB::raw('SUM(quantity) as total_quantity'),
            DB::raw('AVG(unit_price) as average_unit_price')
        )
            ->whereNotNull('raw_material_item_id')
            ->when($raw_material_item_id, function ($query) use ($raw_material_item_id) {
            if (is_array($raw_material_item_id)) {
                if (!in_array('all', $raw_material_item_id, true)) {
                    $query->whereIn('store_issue_requisition_items.raw_material_item_id', $raw_material_item_id);
                }
            } else {
                if ($raw_material_item_id !== 'all') {
                    $query->where('store_issue_requisition_items.raw_material_item_id', $raw_material_item_id);
                }
            }
        })
            ->whereHas('store_issue_requisition', function ($query) use ($from_date, $to_date) {
            $query->where('is_production', 0)->whereBetween('created_at', [
              Carbon::parse($from_date)->startOfDay(),
              Carbon::parse($to_date)->endOfDay()
          ]);
        })
            ->join('raw_material_items', 'store_issue_requisition_items.raw_material_item_id', '=', 'raw_material_items.id')
            ->groupBy('raw_material_item_id', 'raw_material_items.name', 'raw_material_items.code', 'raw_material_items.unit')
            ->get();

        return $storeIssueRequisition;
    }
}
