<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class ItemStockController extends Controller
{
    /**
     * Get stock data with filtering options
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getStockData(Request $request)
    {
        // Validate request parameters
        $validator = Validator::make($request->all(), [
            'branch_id' => 'nullable|string',
            'category_id' => 'nullable|integer',
            'search' => 'nullable|string|max:100',
            'availability' => 'nullable|string|in:available,not_available',
            'page' => 'nullable|integer|min:1',
            'limit' => 'nullable|integer|min:1|max:100',
        ]);




        if ($validator->fails()) {
            return response()->json([
                'status' => 'error',
                'message' => 'Invalid parameters',
                'errors' => $validator->errors()
            ], 400);
        }

        // Get parameters
        $branchId = $request->input('branch_id');
        $categoryId = $request->input('category_id');
        $search = $request->input('search');
        $page = $request->input('page', 1);
        $availability = $request->input('availability');


        // Check if limit is explicitly set
        if ($request->has('limit')) {
            $limit = $request->input('limit');
            $offset = ($page - 1) * $limit;
        } else {
            $limit = null; // No limit, get all records
            $offset = 0;
        }

        // Build query
        $query = DB::table('stock')
            ->join('item', 'stock.item_code', '=', 'item.item_code')
            ->join('tbl_branch', 'stock.branch_id', '=', 'tbl_branch.branch_id')
            ->leftJoin('category', 'item.cat_no', '=', 'category.cat_no')
            ->select([
                'stock.item_stk_id as stock_id',
                'item.item_code as item_id',
                'item.item_name as item_name',
                'item.item_code',
                'item.barcode',
                'category.cat_name as category_name',
                'category.cat_no as category_id',
                'tbl_branch.branch_id',
                'tbl_branch.branch_name',
                'stock.qty',
                'item.re_order_level',
                'stock.cost_price',
                'stock.sales_price_1',
                'stock.sales_price_2',
                'stock.sales_price_3',
                'stock.sales_price_4',
                'stock.branch_id',
                'stock.grn_date',
                'stock.grn_no',
            ]);

        // Apply filters only if they are not null
        if ($branchId !== null) {
            $query->where('tbl_branch.branch_id', $branchId);
        }

        if ($categoryId !== null) {
            $query->where('item.cat_no', $categoryId);
        }

        if ($search !== null && $search !== '') {
            $query->where(function($q) use ($search) {
                $q->where('item.item_name', 'like', "%{$search}%")
                  ->orWhere('item.item_code', 'like', "%{$search}%")
                  ->orWhere('item.barcode', 'like', "%{$search}%");
            });
        }

        // Apply availability filter
        if ($availability !== null) {
            if ($availability === 'available') {
                $query->where('stock.qty', '>', 0);
            } else if ($availability === 'not_available') {
                $query->where('stock.qty', '<=', 0);
            }
        }

        // Get total count for pagination
        $totalCount = $query->count();

        // Clone the query to avoid issues with modifiers
        $queryResult = clone $query;

        if ($availability !== null) {
            if ($availability === 'available') {
                $query->where('stock.qty', '>', 0);
            } else if ($availability === 'not_available') {
                $query->where('stock.qty', '<=', 0);
            }
        }

        // Apply pagination only if limit is set
        if ($limit !== null) {
            $stockData = $queryResult->orderBy('item.item_name')
                ->offset($offset)
                ->limit($limit)
                ->get();

            $totalPages = ceil($totalCount / $limit);
        } else {
            $stockData = $queryResult->orderBy('item.item_name')
                ->get();

            $totalPages = 1; // Only one page when showing all
        }

        // Get all categories for filter dropdown
        $categories = DB::table('category')
            ->select(['cat_no', 'cat_name'])
            ->orderBy('cat_name')
            ->get();

        // Get all branches for filter dropdown
        $branches = DB::table('tbl_branch')
            ->select(['branch_id', 'branch_name'])
            ->orderBy('branch_name')
            ->get();

        return response()->json([
            'status' => 'success',
            'data' => [
                'stock' => $stockData,
                'pagination' => [
                    'total' => $totalCount,
                    'page' => $page,
                    'limit' => $limit,
                    'total_pages' => $totalPages
                ],
                'filters' => [
                    'categories' => $categories,
                    'branches' => $branches
                ]
            ]
        ]);
    }
}
