<?php

namespace App\Http\Controllers\Backend;

use App\Models\Product;
use App\Models\Customer;
use App\Models\Supplier;
use App\Models\Transaction;
use App\Models\BankTransaction;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use App\Http\Controllers\Controller;
use App\Models\BankAccount;
use App\Models\Invoice;
use App\Models\Category;
use App\Models\Expense;
use App\Models\User;
use App\Models\InvoiceItem;
use App\Models\PurchaseItem;
use Carbon\CarbonPeriod;

class ReportController extends Controller
{
    public function stock(Request $request)
    {
        $data['product_id'] = $request->product_id;
        $data['keyword'] = $request->search_keyword;
        $data['category_id'] = $request->category_id;
        $data['brand_id'] = $request->brand_id;
        //get all stock with supplier and user
        $data['products'] = Product::with('unit.related_unit')
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20);
        $data['totalPrice'] = Product::all()->sum(function ($product) {
            // dd(product_stock_balance($product));
            return product_stock_balance($product) * $product->purchase_price;
        });

        if ($request->product_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('id',$request->product_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'product_id' => $request->product_id,
                    ]);
        }
        
        if ($request->category_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('category_id',$request->category_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'category_id' => $request->category_id,
                    ]);
        }
        
        
        if ($request->brand_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('brand_id',$request->brand_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'brand_id' => $request->brand_id,
                    ]);
        }
        
        
        if ($request->search_keyword != null) {
            $data['products'] = Product::with('unit.related_unit')->where('name','like','%'.$request->search_keyword.'%' )
                    ->orWhere('barcode',$request->search_keyword)
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'search_keyword' => $request->search_keyword,
                    ]);
        }
        return view('backend.pages.report.stock', $data);
    }

    public function supplierLedger(Request $request)
    {
        $data['suppliers'] = Supplier::orderBy('created_at', 'desc')->get();
        if ($request->all() != NULL) {
            $data['oneSupplier'] = Supplier::where('id', $request->supplier_id)->where('status', 1)->first();
            $data['sdate'] = Carbon::createFromDate($request->start_date)->format('Y-m-d');
            $data['edate'] = Carbon::createFromDate($request->end_date)->format('Y-m-d');
            // dd($data['sdate'],$data['edate']);
            $data['transaction'] = Transaction::with('invoice', 'purchase', 'supplier')
                                    ->where('supplier_id', $request->supplier_id)
                                    ->whereBetween('date', [$data['sdate'], $data['edate']])
                                    ->get();
        }
        return view('backend.pages.report.supplier_ledger', $data);
    }

    public function customerLedger(Request $request)
    {
        $data['customers'] = Customer::orderBy('created_at', 'desc')->get();
        if ($request->all() != NULL) {
            $data['oneCustomer'] = Customer::where('id', $request->customer_id)->where('status', 1)->first();
            $data['sdate'] = Carbon::createFromDate($request->start_date)->format('Y-m-d');
            $data['edate'] = Carbon::createFromDate($request->end_date)->format('Y-m-d');
            // dd($data['sdate'],$data['edate']);
            $data['transaction'] = Transaction::with('invoice', 'purchase', 'customer')
                                    ->where('customer_id', $request->customer_id)
                                    ->whereBetween('date', [$data['sdate'], $data['edate']])
                                    ->get();
        }
        return view('backend.pages.report.customer_ledger', $data);
    }
    public function customerDue(Request $request){
        $data['customers'] = Customer::orderBy('id','desc')->paginate(10);
        $data['customer_id'] = $request->customer_id;
        $data['phone_no'] = $request->phone_no;
        // dd($data);
        if($request->customer_id != null){
            $data['customers'] = Customer::where('id',$request->customer_id)->orderBy('id','asc')->paginate(10);
        }
        if($request->phone_no != null){
            $data['customers'] = Customer::where('phone',$request->phone_no)->orderBy('id','asc')->paginate(10);
        }


        return view('backend.pages.report.customer_due',$data);
    }
    public function supplierDue(Request $request){
        $data['suppliers'] = Supplier::orderBy('id','asc')->paginate(10);
        $data['supplier_id'] = $request->supplier_id;
        $data['phone_no'] = $request->phone_no;
        // dd($data);
        if($request->supplier_id != null){
            $data['suppliers'] = Supplier::where('id',$request->supplier_id)->orderBy('id','asc')->paginate(10);
        }
        if($request->phone_no != null){
            $data['suppliers'] = Supplier::where('phone',$request->phone_no)->orderBy('id','asc')->paginate(10);
        }

        return view('backend.pages.report.supplier_due',$data);
    }

    public function daily(Request $request)
    { 
        if ($request->all() != NULL) {
            $data['sdate'] = Carbon::createFromDate($request->start_date)->toDateString();
            // $data['edate'] = Carbon::createFromDate($request->end_date)->toDateString();
            // $dateRange = CarbonPeriod::create($data['sdate'], $data['edate']);
            $ssdate = Carbon::createFromDate('2023-09-01')->toDateString();
            $sdate = Carbon::createFromDate($request->start_date)->toDateString();

            $data['bankAcc'] = BankAccount::where('status',1)->orderBy('id','asc')->first();
            
            $data['invoices'] = Invoice::with('invoiceItems')->where('date',$sdate)->get();
            // $data['dueInvoices'] = Invoice::with('invoiceItems')->where('due_date',$sdate)->get();
            $data['bankTrans'] = BankTransaction::where('date',$sdate)->where('pay_type','duepay')->get();
            $data['bankTransdue'] = BankTransaction::where('date',$sdate)->where('pay_type','duepay')->sum('amount');
            

            $previous_total_sale = Invoice::whereBetween('date', [$ssdate,$sdate])->sum('total_amount');

            
            
            $today_total_sale = Invoice::where('date',$sdate)->sum('total_amount');
            $privious_sale = ($previous_total_sale - $today_total_sale);
            $today_sale_amount = Invoice::where('date',$sdate)->sum('total_amount');
            $total_expense = Expense::where('date',$sdate)->get();

            return view('backend.pages.report.daily', $data ,compact('today_sale_amount','total_expense','privious_sale','previous_total_sale'));
        } else {
            return view('backend.pages.report.daily');
        }
    }

    public function sale(Request $request)
    {   
        $data['categories'] = Category::orderBy('created_at', 'desc')->get();
        $data['products'] = Product::orderBy('created_at', 'desc')->get();

        if ($request->all() != NULL) {
            $data['oneProduct'] = Product::find($request->product_id); // Using find() for efficiency
            $data['sdate'] = Carbon::parse($request->start_date)->toDateString(); // Use parse() for more flexibility
            $data['edate'] = Carbon::parse($request->end_date)->toDateString();

            $sdate = $data['sdate'];
            $edate = $data['edate'];

            $invoiceItem = Invoice::whereBetween('date', [$sdate, $edate]);

            // $invoiceItem = Invoice::whereBetween('date', [$sdate, $edate])
            // ->whereHas('invoiceItems', function($query) use ($request) {
            //     if (!empty($request->product_id)) {
            //         $query->where('product_id', $request->product_id);
            //     }
            // });

            $invoiceItem = Invoice::whereBetween('date', [$sdate, $edate])
            ->whereHas('invoiceItems', function($query) use ($request) {
                if (!empty($request->product_id) || !empty($request->category_id)) {
                    // Filter by product_id in the InvoiceItem table
                    if (!empty($request->product_id)) {
                        $query->where('product_id', $request->product_id);
                    }
    
                    // Filter by category_id in the Product table
                    if (!empty($request->category_id)) {
                        $query->whereHas('product', function($subQuery) use ($request) {
                            $subQuery->where('category_id', $request->category_id);
                        });
                    }
                }
            });

        $data['invoiceItem'] = $invoiceItem->get();
        }

        return view('backend.pages.report.sale', $data);

        }

    public function purchase(Request $request)
    {
        $data['products'] = Product::orderBy('created_at', 'desc')->get();

        if ($request->all() != NULL) {
            $data['oneProduct'] = Product::where('id', $request->product_id)->first();
            $data['sdate'] = Carbon::createFromDate($request->start_date)->toDateString();
            $data['edate'] = Carbon::createFromDate($request->end_date)->toDateString();
            $sdate = $data['sdate'];
            $edate = $data['edate'];
            $purchaseItem = PurchaseItem::with('purchase', 'product')
                                        ->whereHas('purchase', function ($query) use ($sdate, $edate) {
                                            $query->whereBetween('date', [$sdate, $edate]);
                                        });
            if($request->product_id != NULL){
                $purchaseItem = $purchaseItem->where('product_id', $request->product_id);
            }
            $data['purchaseItem'] = $purchaseItem->get();
        }
        return view('backend.pages.report.purchase', $data);
    }

    public function profitLoss(Request $request)
    {
        if ($request->all() != NULL) {
            $data['sdate'] = Carbon::createFromDate($request->start_month)->toDateString();
            $data['edate'] = Carbon::createFromDate($request->end_month)->toDateString();
            $dateRange = CarbonPeriod::create($data['sdate'], $data['edate']);

            $data['groupedDates'] = [];
            foreach ($dateRange as $date) {
                $data['groupedDates'][$date->format('Y-m')][] = $date->toDateString();
            }
            return view('backend.pages.report.profit_loss', $data);
        } else {
            return view('backend.pages.report.profit_loss');
        }
    }

    public function low_stock(Request $request)
    {
        $data['product_id'] = $request->product_id;
        $data['keyword'] = $request->search_keyword;
        $data['category_id'] = $request->category_id;
        $data['brand_id'] = $request->brand_id;
        //get all stock with supplier and user
        $data['products'] = Product::with('unit.related_unit')
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20);
        $data['low_stock'] = Product::all()->sum(function ($product) {
            
        });
        

        if ($request->product_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('id',$request->product_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'product_id' => $request->product_id,
                    ]);
        }
        
        if ($request->category_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('category_id',$request->category_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'category_id' => $request->category_id,
                    ]);
        }
        
        
        if ($request->brand_id != null) {
            $data['products'] = Product::with('unit.related_unit')->where('brand_id',$request->brand_id )
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'brand_id' => $request->brand_id,
                    ]);
        }
        
        
        if ($request->search_keyword != null) {
            $data['products'] = Product::with('unit.related_unit')->where('name','like','%'.$request->search_keyword.'%' )
                    ->orWhere('barcode',$request->search_keyword)
                    ->orderBy('created_at', 'DESC')
                    ->paginate(20)
                    ->appends([
                        'search_keyword' => $request->search_keyword,
                    ]);
        }
        return view('backend.pages.report.low_stock_report', $data);
    }

    public function user_sell(Request $request)
    {   
        $data['users'] = User::orderBy('created_at', 'desc')->get();
        
        if ($request->all() != NULL) {
            $data['sdate'] = Carbon::createFromDate($request->start_date)->toDateString();
            $data['edate'] = Carbon::createFromDate($request->end_date)->toDateString();
            $sdate = $data['sdate'];
            $edate = $data['edate'];
            $data['oneUser'] = $request->user_id;

            $invoiceItem = Invoice::where('created_by',$request->user_id)->whereBetween('date', [$sdate, $edate]);
            
            $data['invoiceItem'] = $invoiceItem->get();
        }
        return view('backend.pages.report.user_sell', $data);
    }
    
}
