<?php

namespace App\Http\Controllers\Stock;

use App\Models\Stock\Stock;
use Illuminate\Http\Request;
use App\Models\Stock\Product;
use App\Models\Stock\StockOut;
use App\Models\Stock\StockoutItem;
use App\Models\Stock\StockReceive;
use Illuminate\Support\Facades\DB;
use App\Models\Stock\StockTransfer;
use Illuminate\Support\Facades\Log;
use App\Http\Controllers\Controller;
use App\Models\Stock\ProductTracker;
use App\Models\Stock\StockinHistory;
use Illuminate\Support\Facades\Auth;
use App\Models\Stock\ProductCategory;
use Illuminate\Support\Facades\Cache;
use App\Models\Stock\StockTransferItems;
use Carbon\Carbon;
class ReportsController extends Controller
{
    /**
     * @var $from
     */
    private $from;

    /**
     * @var $to
     */
    private $to;

    public function __construct(Request $request)
    {
        $this->from = $request->input('from');
        $this->to = $request->input('to');
        if (empty($this->from)) {
            $this->from = date('Y-m-d');
        }
    }

   /**
     * Get all stock receives wih filters
    * @param Request $request
    * @return JsonResponse
    */
    public function getReceivesReport(Request $request, $reference = NULL)
    {
        $result = StockReceive::select('*');
        if (!empty($company = $request->input('company'))) {
            $result->where('company_id', $company);
        }
        if (!empty($supplier = $request->input('supplier'))) {
            $result->where('supplier_id', $supplier);
        }
        if (!empty($reference)) {
            $result->where('reference', $reference);
        }
        if(empty($reference)){
            if (empty($this->to)) {
                $result->where('date_received', $this->from);
            } else {
                $result->where('date_received', '>=', $this->from)
                    ->where('date_received', '<=', $this->to);
            }
        }
        return response()->json([
            'status' => 1,
            'rows'   => $result->with('supplier', 'creator')
                               ->orderBy('id', 'DESC')
                               ->paginate(\request()->query('per_page') ?? 45)
        ]);
    }

    /**
     * Get all stock receives wih filters
    * @param Request $request
    * @return JsonResponse
    */
    public function getTransfersReport(Request $request)
    {
        $result = StockTransfer::select('*');
        if (!empty($company = $request->input('company'))) {
            $result->where('company_id', $company);
        }
        if (!empty($department = $request->input('department'))) {
            $result->where('department_id', $department);
        }
        if (empty($this->to)) {
            $result->where('date_transfered', $this->from);
        } else {
            $result->where('date_transfered', '>=', $this->from)
                   ->where('date_transfered', '<=', $this->to);
        }
        return response()->json([
            'status' => 1,
            'rows'   => $result->with('department', 'creator', 'employee')
                               ->orderBy('id', 'DESC')
                               ->paginate(\request()->query('per_page') ?? 45)
        ]);
    }

    /**
     * Get Tracker Report
     * @param Request $request
     * @return JsonResponse
     */
    public function getTrackerReport(Request $request)
    {
        $result = Product::selectRaw('products.id, products.name, products.unit_id, products.quantity as currentQty, SUM(stockin_histories.quantity) AS receiveQty, SUM(stock_transfer_items.quantity) AS transferedQty');
        if (empty($this->to)) {
            $result->leftJoin('stockin_histories', function($join) {
                $join->on('products.id', '=', 'stockin_histories.product_id');
                $join->on(DB::raw("date(stockin_histories.created_at)"), "=", DB::raw("'".$this->from."'"));
            });
            $result->leftJoin('stock_transfer_items', function($join) {
                $join->on('products.id', '=', 'stock_transfer_items.product_id');
                $join->on(DB::raw("date(stock_transfer_items.created_at)"), "=", DB::raw("'".$this->from."'"));
            });
        } else {
            $result->leftJoin('stockin_histories', function($join) {
                $join->on('products.id', '=', 'stockin_histories.product_id');
                $join->on(DB::raw("date(stockin_histories.created_at)"), ">=", DB::raw($this->from));
                $join->on(DB::raw("date(stockin_histories.created_at)"), "<=", DB::raw($this->to));
            });
            $result->leftJoin('stock_transfer_items', function($join) {
                $join->on('products.id', '=', 'stock_transfer_items.product_id');
                $join->on(DB::raw("date(stock_transfer_items.created_at)"), ">=", DB::raw($this->from));
                $join->on(DB::raw("date(stock_transfer_items.created_ats)"), "<=", DB::raw($this->to));
            });
        }

        // Log::info($result);

        return response()->json([
            'status' => 1,
            'rows'   => $result->groupBy('products.id')
                               ->with('unit')
                               ->orderBy('products.name', 'ASC')
                               ->paginate(\request()->query('per_page') ?? 45)
        ]);
    }
    /**
     * Formula for tracker
     * (45-20) + (60+44) = 
        25+104 = 129

        (129+20) - (60+44) = 

        const prevQty = 129;
        let currentQty = 45;
        let receiveQty = 20;
        let transfered = 60;
        let wasted = 44;
     */


     /**
      * Track stock item based on \App\Models\Stock\ProductTracker Model
      * @param Request $request
      * @return JsonResponse
      */

    public function trackProductsStock(Request $request)
    {
        $result = ProductTracker::select('id','name','unit_id','category_id','quantity as currentQty', 'company_id')
                                    ->where('company_id', auth()->user()->company_id);
        if (!empty($product = $request->get('product'))) {
            $result->where('id', $product)
                    ->where('company_id', auth()->user()->company_id);
        }

        if (!empty($category = $request->get('category'))) {
            $result->where('category_id', $category)
                    ->where('company_id', auth()->user()->company_id);
        }

        if (!empty($scategory = $request->get('scategory'))) {
            $result->where('category_id', $scategory)
                    ->where('company_id', auth()->user()->company_id);
        }

        return response()->json([
            'status' => 1,
            'rows'   => $result->with('unit','category','expiry')
                               ->orderBy('name', 'ASC')
                               ->paginate(\request()->query('per_page') ?? 200)
        ]);
    }

    /**
     * Remaining stock broken down per batch (lot).
     *
     * Reads product_changes (the POS-facing batch inventory) so each product
     * shows how much is left in every batch, with that batch's own cost,
     * selling price and expiry. Answers "for the old stock I have X left and
     * for the new stock I have Y left".
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function stockByBatch(Request $request)
    {
        $query = DB::table('product_changes as pc')
            ->join('products as p', 'p.id', '=', 'pc.product_id')
            ->leftJoin('units as u', 'u.id', '=', 'p.unit_id')
            ->leftJoin('product_categories as c', 'c.id', '=', 'p.category_id')
            ->whereNull('pc.deleted_at')
            ->whereNull('p.deleted_at')
            ->where('p.company_id', auth()->user()->company_id)
            ->select(
                'pc.id',
                'pc.product_id',
                'p.name as product_name',
                'p.code as product_code',
                'u.name as unit',
                'c.name as category',
                'pc.batch_nber',
                'pc.quantity',
                'pc.price as cost_price',
                'pc.selling_price',
                'pc.expiration_date'
            );

        if (!empty($product = $request->get('product'))) {
            $query->where('pc.product_id', $product);
        }

        if (!empty($category = $request->get('category'))) {
            $query->where('p.category_id', $category);
        }

        // By default only show batches that still have stock; pass in_stock=0
        // to include depleted batches too.
        if ($request->get('in_stock', '1') == '1') {
            $query->where('pc.quantity', '>', 0);
        }

        $rows = $query->orderBy('p.name', 'ASC')
                      ->orderBy('pc.expiration_date', 'ASC')
                      ->orderBy('pc.id', 'ASC')
                      ->get();

        return response()->json([
            'status' => 1,
            'rows'   => $rows,
        ]);
    }

    /**
      * Get Dashboard
      * @param Request $request
      * @return JsonResponse
      */
    public function getDashboard(Request $request)
    {
        $receiveData =  $this->getReceivesDashboard();
        $transferData = $this->getTransfersDashboard();

        $cachesData = [
            'ReceiveData' => (int)$receiveData['amount'],
            'transferData' => (int)$transferData['amount']
        ];

        Cache::put('ReceiveAndTransferStockData', $cachesData, 60);

        return response()->json([
            'stock_value' => Product::selectRaw('COALESCE(SUM(quantity * cost_price), 0) as stock_value')
                                    ->first()
                                    ->stock_value,
            'branch_stock' => Stock::selectRaw('COALESCE(SUM(stock.quantity * products.cost_price), 0) as branch_stock')
                                    ->leftJoin('products', 'stock.product_id', '=', 'products.id')
                                    ->first()
                                    ->branch_stock,
            'receives_amount'  => $receiveData['amount'],   
            'receivesInPreviousMonth'=>$receiveData['AmountInPreviousMonth'],   
            'receives_chart'   => $receiveData['chart'], 
            'transfers_amount' => $transferData['amount'],      
            'transfers_chart'  => $transferData['chart'],
            'total_products'   => Product::count(),
            'current_month'    => [date('Y-m-d', strtotime("first day of this month")), date('Y-m-d', strtotime("last day of this month"))]   
        ]);
    }

    /**
     * Get Both total amount and chart data for receive
     * @param array $dates
     * @param int $year
     * @return  array 
     */
    private function getReceivesDashboard($dates = array(), $year = NULL)
    {
        if (!$year) {
            $year = date('Y');
        }

        $today = now(); // Gets the current date and time
        $firstDayOfPreviousMonth = $today->subMonth()->startOfMonth(); // Gets the first day of the previous month
        $lastDayOfPreviousMonth = $firstDayOfPreviousMonth->copy()->endOfMonth(); // Gets the last day of the previous month
        $AmountInPreviousMonth = StockReceive::whereBetween('date_received', [$firstDayOfPreviousMonth, $lastDayOfPreviousMonth])->sum('amount');

        $amount = StockReceive::sum('amount');
        if (!empty($dates)) {
            $amount->whereBetween('date_received', $dates);
        }
        $monthsData = [];
        for ($i = 1; $i <= 12; $i++) {
            $d = sprintf('%02d', $i);
            $monthsData[] = StockReceive::where('date_received', 'LIKE', "%{$year}-{$d}%")
                                        ->sum('amount');
        }
        return [
            'amount' => $amount,
            'chart'  => $monthsData,
            'AmountInPreviousMonth'=>$AmountInPreviousMonth
        ];
    }

     /**
     * Get Both total amount and chart data for transfer
     * @param array $dates
     * @param int $year
     * @return  array 
     */
    private function getTransfersDashboard($dates = array(), $year = NULL)
    {
        if (!$year) {
            $year = date('Y');
        }
        $amount = StockTransfer::sum('amount');
        if (!empty($dates)) {
            $amount->whereBetween('date_transfered', $dates);
        }
        $monthsData = [];
        for ($i = 1; $i <= 12; $i++) {
            $d = sprintf('%02d', $i);
            $monthsData[] = StockTransfer::where('date_transfered', 'LIKE', "%{$year}-{$d}%")
                                        ->sum('amount');
        }
        return [
            'amount' => $amount,
            'chart'  => $monthsData
        ];
    }

    /**
     * Filter dashboard cards
     * @param Request $request
     * @return JsonResponse
     */
    public function filterDashboardCards(Request $request)
    {
        $from = date('Y-m-d', strtotime($request->get('from')));
        $to   = date('Y-m-d', strtotime($request->get('to')));
        return response()->json([
            'receives_amount'  => StockReceive::whereBetween('date_received', [$from, $to])->sum('amount'), 
            'transfers_amount' => StockTransfer::whereBetween('date_transfered', [$from, $to])->sum('amount')
        ]);
    }
    


    public function getAllProductsProfit(Request $request)
{
    // 1️⃣ Date range
    $start = $request->start_date
        ? Carbon::parse($request->start_date)->startOfDay()
        : now()->startOfMonth();

    $end = $request->end_date
        ? Carbon::parse($request->end_date)->endOfDay()
        : now()->endOfDay();

    // 2️⃣ Product profit report (EXCLUDES deleted data)
    $data = DB::table('sale_items as si')
        ->join('products as p', 'p.id', '=', 'si.item_id')
        ->join('sales as s', 's.id', '=', 'si.sale_id')

        // 🔴 IMPORTANT: exclude soft deleted rows
        ->whereNull('si.deleted_at')
        ->whereNull('s.deleted_at')

        ->whereBetween('s.committed_date', [$start, $end])
        ->groupBy('p.id', 'p.name', 'p.cost_price', 'p.quantity')
        ->select(
            'p.id',
            'p.name',
            'p.quantity as current_stock',
            DB::raw('SUM(si.quantity) as sold_qty'),
            DB::raw('SUM(si.amount) as total_sales'),
            DB::raw('SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as total_cost'),
            DB::raw('SUM(si.amount) - SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as benefit')
        )
        ->orderBy('p.name')
        ->get();

    // 3️⃣ Calculate stock_in (initial stock + purchases during period)
    $products = collect();
    
    foreach ($data as $item) {
        // Get product to access additional info
        $product = Product::find($item->id);
        
        // Calculate stock_in: For this example, we'll use current stock + sold quantity
        // This assumes no other stock movements (adjustments, returns, etc.)
        $stock_in = $item->current_stock + $item->sold_qty;
        
        // For remaining_qty, use the current stock from products table
        $remaining_qty = $item->current_stock;
        
        $products->push([
            'id' => $item->id,
            'name' => $item->name,
            'stock_in' => $stock_in,
            'sold_qty' => $item->sold_qty,
            'remaining_qty' => $remaining_qty,
            'total_sales' => (float)$item->total_sales,
            'total_cost' => (float)$item->total_cost,
            'benefit' => (float)$item->benefit
        ]);
    }

    // 4️⃣ Response - match the expected interface
    return response()->json([
        'start_date' => $start->toDateString(),
        'end_date' => $end->toDateString(),
        'products' => $products
    ]);
}

// Alternative: More accurate calculation using stockin_histories
public function getAllProductsProfitV2(Request $request)
{
    // 1️⃣ Date range
    $start = $request->start_date
        ? Carbon::parse($request->start_date)->startOfDay()
        : now()->startOfMonth();

    $end = $request->end_date
        ? Carbon::parse($request->end_date)->endOfDay()
        : now()->endOfDay();

    // 2️⃣ Product profit report
    $data = DB::table('sale_items as si')
        ->join('products as p', 'p.id', '=', 'si.item_id')
        ->join('sales as s', 's.id', '=', 'si.sale_id')
        ->whereNull('si.deleted_at')
        ->whereNull('s.deleted_at')
        ->whereBetween('s.committed_date', [$start, $end])
        ->groupBy('p.id', 'p.name', 'p.cost_price', 'p.quantity')
        ->select(
            'p.id',
            'p.name',
            'p.quantity as current_stock',
            DB::raw('SUM(si.quantity) as sold_qty'),
            DB::raw('SUM(si.amount) as total_sales'),
            DB::raw('SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as total_cost'),
            DB::raw('SUM(si.amount) - SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as benefit')
        )
        ->orderBy('p.name')
        ->get();

    // 3️⃣ Get stock_in from stockin_histories for each product
    $products = collect();
    
    foreach ($data as $item) {
        $product = Product::find($item->id);
        
        // Get stock_in quantity from stockin_histories within the period
        $stockInHistory = DB::table('stockin_histories')
            ->where('product_id', $item->id)
            ->whereBetween('created_at', [$start, $end])
            ->sum('quantity');
        
        // If no stock_in history for the period, use current stock + sold quantity
        $stock_in = $stockInHistory > 0 ? $stockInHistory : ($item->current_stock + $item->sold_qty);
        
        // Get initial stock from product_changes
        $initialStock = DB::table('product_changes')
            ->where('product_id', $item->id)
            ->orderBy('created_at', 'asc')
            ->value('quantity') ?? 0;
        
        // For remaining_qty, use current stock
        $remaining_qty = $item->current_stock;
        
        // Alternative: Calculate from initial stock + stock_in - sold_qty
        // $remaining_qty = $initialStock + $stock_in - $item->sold_qty;
        
        $products->push([
            'id' => $item->id,
            'name' => $item->name,
            'stock_in' => $stock_in,
            'sold_qty' => $item->sold_qty,
            'remaining_qty' => $remaining_qty,
            'total_sales' => (float)$item->total_sales,
            'total_cost' => (float)$item->total_cost,
            'benefit' => (float)$item->benefit,
            // Add extra info if needed
            'initial_stock' => $initialStock,
            'current_stock' => $item->current_stock
        ]);
    }

    // 4️⃣ Response
    return response()->json([
        'start_date' => $start->toDateString(),
        'end_date' => $end->toDateString(),
        'products' => $products
    ]);
}

// Single product profit report with better stock calculation
public function getProductProfit(Request $request, $productId)
{
    // 1️⃣ Date range
    $start = $request->start_date
        ? Carbon::parse($request->start_date)->startOfDay()
        : now()->startOfMonth();

    $end = $request->end_date
        ? Carbon::parse($request->end_date)->endOfDay()
        : now()->endOfDay();

    // 2️⃣ Get product details
    $product = Product::find($productId);
    
    if (!$product) {
        return response()->json([
            'error' => 'Product not found'
        ], 404);
    }

    // 3️⃣ Get profit data for this product
    $data = DB::table('sale_items as si')
        ->join('products as p', 'p.id', '=', 'si.item_id')
        ->join('sales as s', 's.id', '=', 'si.sale_id')
        ->whereNull('si.deleted_at')
        ->whereNull('s.deleted_at')
        ->where('p.id', $productId)
        ->whereBetween('s.committed_date', [$start, $end])
        ->select(
            DB::raw('SUM(si.quantity) as total_quantity'),
            DB::raw('SUM(si.amount) as total_sales'),
            DB::raw('SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as total_cost'),
            DB::raw('SUM(si.amount) - SUM(si.quantity * COALESCE(si.cost_price, p.cost_price)) as benefit')
        )
        ->first();

    // If no sales found, return zeros
    if (!$data || is_null($data->total_quantity)) {
        $data = (object)[
            'total_quantity' => 0,
            'total_sales' => 0,
            'total_cost' => 0,
            'benefit' => 0
        ];
    }

    // 4️⃣ Response
    return response()->json([
        'product_id' => $productId,
        'start_date' => $start->toDateString(),
        'end_date' => $end->toDateString(),
        'data' => [
            'product_id' => (int)$productId,
            'product_name' => $product->name,
            'reference' => $product->reference,
            'category' => $product->category ? [
                'id' => $product->category->id,
                'name' => $product->category->name
            ] : null,
            'unit' => $product->unit ? [
                'id' => $product->unit->id,
                'name' => $product->unit->name
            ] : null,
            'total_quantity' => (float)$data->total_quantity,
            'total_sales' => (float)$data->total_sales,
            'total_cost' => (float)$data->total_cost,
            'benefit' => (float)$data->benefit
        ]
    ]);
}
}