<?php

namespace App\Http\Controllers\Backend\Dashboard;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Models\AccountTransaction;
use App\Models\Attendance;
use App\Models\ChartOfAccount;
use App\Models\Customer;
use App\Models\customerLedger;
use App\Models\Employee;
use App\Models\Expense;
use App\Models\LeaveApplication;
use App\Models\Lone;
use App\Models\Order;
use App\Models\Product;
use App\Models\ProductUse;
use App\Models\Project;
use App\Models\ProjectExpense;
use App\Models\ProjectRequisition;
use App\Models\ProjectReturn;
use App\Models\PurchaseOrder;
use App\Models\PurchaseRequisition;
use Illuminate\Pagination\LengthAwarePaginator;
use App\Models\Purchases;
use App\Models\Sale;
use App\Models\Service;
use App\Models\StockAjdustment;
use App\Models\stockinsummary;
use App\Models\Supplier;
use App\Models\supplierLedger;
use App\Models\Transection;
use App\Models\Transfer;
use App\Models\User;
use App\Models\UserRole;
use Carbon\Carbon;
use DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Http\Response;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        $user = Auth::user();

        $purchase =  getUnderAccount(22);

        $totdayPurchase = getUnderAccount(22, date('Y-m-d'));

        $sale = getUnderAccount(18, null, false);

        if ($user->branch_id == null) {
            $totalService = Service::sum('amount');
        } else {
            $totalService = Service::where('branch_id', $user->branch_id)->sum('amount');
        }

        $todaySale = getUnderAccount(18, date('Y-m-d'), false);

        $expense = getUnderAccount(19);
        $todayExpense = getUnderAccount(19, date('Y-m-d'));


        if ($user->branch_id == null) {
            $suppplierPayment = supplierLedger::where('payment_type', 'Collect')->sum('credit');
        } else {
            $suppplierPayment = supplierLedger::where('payment_type', 'Collect')->where('branch_id', $user->branch_id)->sum('credit');
        }

        $totalcashbalance =  getUnderAccount(7);
        $totalbankbalance =  getUnderAccount(8);

        $totalemployee = Employee::where('employee_status', 'present')->count();
        $todayattendance = Attendance::whereDate("date", date("Y-m-d"))->count();

        $customerDue = customerDue();

        $supplierDue = supplierDue();


        // $customerPayment = customerLedger::where('payment_type', 'Collect')->sum('debit');
        $customerPayment = customerLedger::sum('debit');
        // dd($customerPayment);
        $userrole = UserRole::where('status', 'Active')->count();
        $customer = Customer::where('status', 'Active')->count();
        $Supplier = Supplier::where('status', 'Active')->count();
        $prjectDetails = Project::where('manager_id', $user->id)->first();

        //pending branch
        $pvConditon = array(
            'status' => 'Pending',
            'purchase_type' => 'Manual',
        );
        if ($user->branch_id == null) {
            $prPending = PurchaseRequisition::where('status', 'Pending')->count();
        } else {
            $prPending = PurchaseRequisition::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $poPending = PurchaseOrder::where('status', 'Pending')->count();
        } else {
            $poPending = PurchaseOrder::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $pvPending = Purchases::where($pvConditon)->count();
        } else {
            $pvPending = Purchases::where($pvConditon)->where('branch_id', $user->branch_id)->count();
        }
        if ($user->branch_id == null) {
            $trPending = Transfer::where('status', 'Pending')->count();
        } else {
            $trPending = Transfer::where('status', 'Pending')->where('from_branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $adjPending = StockAjdustment::where('status', 'Pending')->count();
        } else {
            $adjPending = StockAjdustment::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->type == "Project") {

            $totalprojectexpence = ProjectExpense::where('project_id', $prjectDetails->id)->sum('amount');
            $todayprojectexpence = ProjectExpense::where('project_id', $prjectDetails->id)->where('date', today())->sum('amount');

            $productreq = ProjectRequisition::where('project_id', $prjectDetails->id)->sum('approve_qty');
            $productreqtoday = ProjectRequisition::where('project_id', $prjectDetails->id)->where('date', today())->sum('approve_qty');

            $useproduct = ProductUse::where('project_id', $prjectDetails->id)->sum('use_total');
            $usetotaltoday = ProductUse::where('project_id', $prjectDetails->id)->where('date', today())->sum('use_total');

            $returntotal = ProjectReturn::where('project_id', $prjectDetails->id)->sum('return_total');
            $returntoday = ProjectReturn::where('project_id', $prjectDetails->id)->where('date', today())->sum('return_total');
        }


        // Count records created today and approved
        $leave_aplication = LeaveApplication::whereDate('created_at', today())
            ->where('status', 'approved')
            ->count();
        $attendance_aplication =  Attendance::whereDate('date', date("Y-m-d"))->count();

        $activeEmployeeCount = Employee::where('status', 'Active')->where('employee_status', "present")->count();
        $total_absent = $activeEmployeeCount - $attendance_aplication;

        $approved_leave = LeaveApplication::where('status', 'approved')->count();

        $startDate = Carbon::now()->subMonth(); // One month ago
        $endDate = Carbon::now();
        $monthly_created_employee = \App\Models\Employee::where('status', 'Active')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->count();

        $monthly_lone_approved = Lone::where('status', 'approved')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->count();

        $monthly_lone = Lone::whereBetween('created_at', [$startDate, $endDate])->count();

        // dd(date('m-d-Y'));
        //pending project
        if ($user->branch_id == null) {
            $PendingReturn = ProjectReturn::where('status', 'Pending')->count();
        } else {
            $PendingReturn = ProjectReturn::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }
        if ($user->branch_id == null) {
            $PendingReq = ProjectRequisition::where('status', 'Pending')->count();
        } else {
            $PendingReq = ProjectRequisition::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        $account_Details = ChartOfAccount::whereIn("id", [getAccountByUniqueID(6)->id])->get();

        $rollper = auth()->user()->userRole->dashboard_id;
        $rollper = explode(',', $rollper);

        // $currentStock = stockinsummary::all();

        $currentStock = Stockinsummary::with('accessories')->get()->groupBy(function ($item) {
            return $item->accessories->unit ?? 'N/A';
        });

        // Pre-calculate stock-in sums grouped by accessories_id
        $stockinSums = \App\Models\Stockindetails::selectRaw('aceesories_id, SUM(kg) as total_kg, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_kg', 'aceesories_id');

        $stockinQty = \App\Models\Stockindetails::selectRaw('aceesories_id, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_qty', 'aceesories_id');

        // Pre-calculate stock-out sums grouped by accessories_id
        $stockoutSums = \App\Models\Stockoutdetails::selectRaw('aceesories_id, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_qty', 'aceesories_id');
        //dd($currentStock );

        return view('backend.pages.dashboard.index', get_defined_vars());
    }


public function bankInfo(Request $request) {
    $title = "Bank Information";
    
    // Get Parent Account by Unique ID
    $parentAccount = getAccountByUniqueID(6);
    if (!$parentAccount) {
        return redirect()->back()->with('error', 'Parent account not found');
    }
    
    // Get all child accounts recursively
    $accounts = ChartOfAccount::with('subAccount')->where('parent_id', $parentAccount->id)->get();
    
    // Flatten Accounts Recursively and create parent mapping
    function flattenAccounts($accounts, &$flattened = [], &$parentMapping = []) {
        foreach ($accounts as $account) {
            $flattened[$account->id] = $account;
            
            // Create parent mapping for each account
            $parentMapping[$account->id] = $account->parent_id;
            
            if ($account->subAccount->isNotEmpty()) {
                flattenAccounts($account->subAccount, $flattened, $parentMapping);
            }
        }
        return $flattened;
    }
    
    $parentMapping = [];
    $accountsInfo = flattenAccounts($accounts, $flattened, $parentMapping);
    
    // Function to get the top-level parent (mother account)
    function getMotherAccount($accountId, $accountsInfo, $parentMapping) {
        $current = $accountsInfo[$accountId] ?? null;
        if (!$current) return null;
        
        // Keep going up until we find an account whose parent is the main parent (ID 6)
        while ($current && isset($parentMapping[$current->id])) {
            $parentId = $parentMapping[$current->id];
            
            // If parent is the main parent account (ID 6), this is our mother account
            if ($parentId == 6) {
                return $current;
            }
            
            // Otherwise, continue up the chain
            $current = $accountsInfo[$parentId] ?? null;
        }
        
        return $current;
    }
    
    // Fetch Transactions
    $query = AccountTransaction::query()
        ->whereIn('account_id', array_keys($accountsInfo))
        ->with(['account.accountable'])
        ->orderBy('invoice', 'asc');
    
    if ($request->filled('invoice')) {
        $query->where('invoice', 'like', '%' . $request->invoice . '%');
    }
    if ($request->filled('account_id')) {
        $query->where('account_id', $request->account_id);
    }
    if ($request->filled('from_date')) {
        $query->whereDate('created_at', '>=', $request->from_date);
    }
    if ($request->filled('to_date')) {
        $query->whereDate('created_at', '<=', $request->to_date);
    }
    
    $filteredTransactions = $query->orderBy('id','desc')->get();
    
    // Format Invoices with separate bank names for advance and due payments
    $invoices = [];
    foreach ($filteredTransactions as $transaction) {
        $invoice = $transaction->invoice;
        if (!$invoice) continue;
        
        $account = $transaction->account;
        $customer = $account ? $account->accountable : null;
        
        if (!isset($invoices[$invoice])) {
            $invoices[$invoice] = [
                'invoice' => $invoice,
                'name' => $customer->name ?? 'Unknown',
                'advance_payment' => 0,
                'due_payment' => 0,
                'advance_bank_names' => [], // Separate bank names for advance payments
                'due_bank_names' => [],     // Separate bank names for due payments
                'date' => $transaction->created_at,
            ];
        }
        
        // Get the mother account name instead of child account name
        $motherAccount = getMotherAccount($transaction->account_id, $accountsInfo, $parentMapping);
        $bankName = $motherAccount ? $motherAccount->account_name : 'Unknown Bank';
        
        // Fixed logic: Check if remark is not null for advance payment
        if (!empty($transaction->remark) && $transaction->remark == 'Advance Payment') {
            // This is an advance payment
            $invoices[$invoice]['advance_payment'] += $transaction->debit ?? 0;
            
            // Add bank name to advance payment bank names (avoid duplicates)
            if (!in_array($bankName, $invoices[$invoice]['advance_bank_names'])) {
                $invoices[$invoice]['advance_bank_names'][] = $bankName;
            }
        } else {
            // This is a due payment
            $invoices[$invoice]['due_payment'] += $transaction->debit ?? 0;
            
            // Add bank name to due payment bank names (avoid duplicates)
            if (!in_array($bankName, $invoices[$invoice]['due_bank_names'])) {
                $invoices[$invoice]['due_bank_names'][] = $bankName;
            }
        }
    }
    
    $formattedInvoices = array_values($invoices);
    $currentPage = LengthAwarePaginator::resolveCurrentPage();
    $perPage = 20;
    $total = count($formattedInvoices);
    $currentItems = array_slice($formattedInvoices, ($currentPage - 1) * $perPage, $perPage);
    
    $transactionsPaginator = new LengthAwarePaginator(
        $currentItems,
        $total,
        $perPage,
        $currentPage,
        ['path' => $request->url(), 'query' => $request->query()]
    );
    
    return view('backend.pages.dashboard.bank', [
        'title' => $title,
        'invoices' => $currentItems,
        'accountsInfo' => $accountsInfo,
        'accounts' => $accounts,
        'transactionsPaginator' => $transactionsPaginator,
        'request' => $request,
    ]);
}
    public function stockInfo()
    {
        $user = Auth::user();

        $purchase =  getUnderAccount(22);

        $totdayPurchase = getUnderAccount(22, date('Y-m-d'));

        $sale = getUnderAccount(18, null, false);

        if ($user->branch_id == null) {
            $totalService = Service::sum('amount');
        } else {
            $totalService = Service::where('branch_id', $user->branch_id)->sum('amount');
        }

        $todaySale = getUnderAccount(18, date('Y-m-d'), false);

        $expense = getUnderAccount(19);
        $todayExpense = getUnderAccount(19, date('Y-m-d'));


        if ($user->branch_id == null) {
            $suppplierPayment = supplierLedger::where('payment_type', 'Collect')->sum('credit');
        } else {
            $suppplierPayment = supplierLedger::where('payment_type', 'Collect')->where('branch_id', $user->branch_id)->sum('credit');
        }

        $totalcashbalance =  getUnderAccount(7);
        $totalbankbalance =  getUnderAccount(8);

        $totalemployee = Employee::where('employee_status', 'present')->count();
        $todayattendance = Attendance::whereDate("date", date("Y-m-d"))->count();

        $customerDue = customerDue();

        $supplierDue = supplierDue();


        // $customerPayment = customerLedger::where('payment_type', 'Collect')->sum('debit');
        $customerPayment = customerLedger::sum('debit');
        // dd($customerPayment);
        $userrole = UserRole::where('status', 'Active')->count();
        $customer = Customer::where('status', 'Active')->count();
        $Supplier = Supplier::where('status', 'Active')->count();
        $prjectDetails = Project::where('manager_id', $user->id)->first();

        //pending branch
        $pvConditon = array(
            'status' => 'Pending',
            'purchase_type' => 'Manual',
        );
        if ($user->branch_id == null) {
            $prPending = PurchaseRequisition::where('status', 'Pending')->count();
        } else {
            $prPending = PurchaseRequisition::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $poPending = PurchaseOrder::where('status', 'Pending')->count();
        } else {
            $poPending = PurchaseOrder::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $pvPending = Purchases::where($pvConditon)->count();
        } else {
            $pvPending = Purchases::where($pvConditon)->where('branch_id', $user->branch_id)->count();
        }
        if ($user->branch_id == null) {
            $trPending = Transfer::where('status', 'Pending')->count();
        } else {
            $trPending = Transfer::where('status', 'Pending')->where('from_branch_id', $user->branch_id)->count();
        }

        if ($user->branch_id == null) {
            $adjPending = StockAjdustment::where('status', 'Pending')->count();
        } else {
            $adjPending = StockAjdustment::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        if ($user->type == "Project") {

            $totalprojectexpence = ProjectExpense::where('project_id', $prjectDetails->id)->sum('amount');
            $todayprojectexpence = ProjectExpense::where('project_id', $prjectDetails->id)->where('date', today())->sum('amount');

            $productreq = ProjectRequisition::where('project_id', $prjectDetails->id)->sum('approve_qty');
            $productreqtoday = ProjectRequisition::where('project_id', $prjectDetails->id)->where('date', today())->sum('approve_qty');

            $useproduct = ProductUse::where('project_id', $prjectDetails->id)->sum('use_total');
            $usetotaltoday = ProductUse::where('project_id', $prjectDetails->id)->where('date', today())->sum('use_total');

            $returntotal = ProjectReturn::where('project_id', $prjectDetails->id)->sum('return_total');
            $returntoday = ProjectReturn::where('project_id', $prjectDetails->id)->where('date', today())->sum('return_total');
        }


        // Count records created today and approved
        $leave_aplication = LeaveApplication::whereDate('created_at', today())
            ->where('status', 'approved')
            ->count();
        $attendance_aplication =  Attendance::whereDate('date', date("Y-m-d"))->count();

        $activeEmployeeCount = Employee::where('status', 'Active')->where('employee_status', "present")->count();
        $total_absent = $activeEmployeeCount - $attendance_aplication;

        $approved_leave = LeaveApplication::where('status', 'approved')->count();

        $startDate = Carbon::now()->subMonth(); // One month ago
        $endDate = Carbon::now();
        $monthly_created_employee = \App\Models\Employee::where('status', 'Active')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->count();

        $monthly_lone_approved = Lone::where('status', 'approved')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->count();

        $monthly_lone = Lone::whereBetween('created_at', [$startDate, $endDate])->count();

        // dd(date('m-d-Y'));
        //pending project
        if ($user->branch_id == null) {
            $PendingReturn = ProjectReturn::where('status', 'Pending')->count();
        } else {
            $PendingReturn = ProjectReturn::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }
        if ($user->branch_id == null) {
            $PendingReq = ProjectRequisition::where('status', 'Pending')->count();
        } else {
            $PendingReq = ProjectRequisition::where('status', 'Pending')->where('branch_id', $user->branch_id)->count();
        }

        $account_Details = ChartOfAccount::whereIn("id", [getAccountByUniqueID(6)->id])->get();

        $rollper = auth()->user()->userRole->dashboard_id;
        $rollper = explode(',', $rollper);

        // $currentStock = stockinsummary::all();

        $currentStock = Stockinsummary::with('accessories')->get()->groupBy(function ($item) {
            return $item->accessories->unit ?? 'N/A';
        });

        // Pre-calculate stock-in sums grouped by accessories_id
        $stockinSums = \App\Models\Stockindetails::selectRaw('aceesories_id, SUM(kg) as total_kg, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_kg', 'aceesories_id');

        $stockinQty = \App\Models\Stockindetails::selectRaw('aceesories_id, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_qty', 'aceesories_id');

        // Pre-calculate stock-out sums grouped by accessories_id
        $stockoutSums = \App\Models\Stockoutdetails::selectRaw('aceesories_id, SUM(in_quantity) as total_qty')
            ->groupBy('aceesories_id')
            ->pluck('total_qty', 'aceesories_id');
        //dd($currentStock );

        return view('backend.pages.dashboard.stock', get_defined_vars());
    }


    public function buyer(): Response
    {
        $buyer = Auth::guard('buyer')->user();
        $products = Product::with('category')->where('status', 'Active')->get();
        $Order = Order::where('buyer_id', auth()->user()->id)->get();
        $approve_Order = Order::where('buyer_id', auth()->user()->id)->where('status', 'approved')->get();
        $confrim_Order = Order::where('buyer_id', auth()->user()->id)->where('status', 'Confirmed')->get();
        $Pending_Order = Order::where('buyer_id', auth()->user()->id)->where('status', 'pending')->get();
        // dd(auth()->user()->id);
        return response()->view('backend.pages.dashboard.buyer', get_defined_vars());
    }

    function new_order()
    {
        $orders = Order::latest()->take(10)->get();
        return view("ajax.componene.dashboard.new_order", get_defined_vars());
    }
}
