<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Illuminate\Support\Facades\Session;
class DashboardController extends Controller
{
    public function index()
    {

        $data['orders'] = [];
        $followup_count = DB::table('bd_call_tracker')
        ->whereBetween('next_followup_time', [
            Carbon::today(),
            Carbon::now()->endOfDay()
        ])
        ->where('created_by', Auth::id())
        ->count();
        // Store in session
        Session::put('followup_count', $followup_count);
        // Optional: pass a list of all possible stages for consistency in the view
        // $data['stages'] = ['Pending', 'Interested', 'Not Interested', 'May Be Later', 'Budget Issue'];
        return view('Users.dashboard', $data);
    }
    public function monthlyDetail(Request $request)
    {
        $week  = $request->week;
        $month = $request->month;
        $year  = $request->year;
        if ($week == '' && $month == '') {
            $currentYear = now()->year;

            $data['stageWiseMonthlyData'] = DB::table('bd_leads')
                ->selectRaw('MONTH(created_at) as month, stage, COUNT(*) as count')
                ->whereYear('created_at', $currentYear)
                ->groupBy('month', 'stage')
                ->get()
                ->groupBy('month');
            $data['stageWiseData'] = [];
        } else {
            if ($week == 1) {
                $date_start = '01-' . $month . '-' . $year;
                $date_end   = '07-' . $month . '-' . $year;
            } else if ($week == 2) {
                $date_start = '08-' . $month . '-' . $year;
                $date_end   = '14-' . $month . '-' . $year;
            } else if ($week == 3) {
                $date_start = '15-' . $month . '-' . $year;
                $date_end   = '21-' . $month . '-' . $year;
            } else if ($week == 4) {
                $tot_days   = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                $date_start = '22-' . $month . '-' . $year;
                $date_end   = $tot_days . '-' . $month . '-' . $year;
            }
            $startDate = date("Y-m-d", strtotime($date_start));
            $endDate = date("Y-m-d", strtotime($date_end));
            $data['stageWiseData'] = DB::table('bd_leads')
                ->selectRaw('DATE(created_at) as date, stage, COUNT(*) as count')
                ->whereBetween('created_at', [$startDate, $endDate])
                ->groupBy('date', 'stage')
                ->orderBy('date')
                ->get();

            $data['stageWiseMonthlyData'] = [];
        }

        $data['stages'] = ['Pending', 'Interested', 'Not Interested', 'May Be Later', 'Budget Issue', 'Converted'];
        return view('Users.monthlydetail', $data);
    }
    public function leadsmonthlyDetail(Request $request)
    {
        $week  = $request->week;
        $month = $request->month;
        $year  = $request->year;
        $currentYear = $year;
        if ($week == '' && $month == '') {

            $data['leadsMonthly'] = DB::table('tb_order')
                ->selectRaw("
                    MONTH(rdate) as month,
                    COUNT(CASE WHEN order_code = '' THEN 1 END) as lead_count,
                    COUNT(CASE WHEN order_code != '' THEN 1 END) as order_count
                ")
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->whereYear('rdate', $currentYear)
                ->groupBy(DB::raw('MONTH(rdate)'))
                ->orderBy(DB::raw('MONTH(rdate)'))
                ->get();

            $data['leadsWiseData'] = [];
        } else {
            if ($week == 1) {
                $date_start = '01-' . $month . '-' . $year;
                $date_end   = '07-' . $month . '-' . $year;
            } else if ($week == 2) {
                $date_start = '08-' . $month . '-' . $year;
                $date_end   = '14-' . $month . '-' . $year;
            } else if ($week == 3) {
                $date_start = '15-' . $month . '-' . $year;
                $date_end   = '21-' . $month . '-' . $year;
            } else if ($week == 4) {
                $tot_days   = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                $date_start = '22-' . $month . '-' . $year;
                $date_end   = $tot_days . '-' . $month . '-' . $year;
            }
            $startDate = date("Y-m-d", strtotime($date_start));
            $endDate = date("Y-m-d", strtotime($date_end));

            $data['leadsWiseData'] = DB::table('tb_order')
                ->selectRaw("
                    DATE(rdate) as date,
                    COUNT(CASE WHEN order_code = '' THEN 1 END) as lead_count,
                    COUNT(CASE WHEN order_code != '' THEN 1 END) as order_count
                ")
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->whereBetween(DB::raw("DATE(rdate)"), [$startDate, $endDate])
                ->groupBy(DB::raw("DATE(rdate)"))
                ->orderBy(DB::raw("DATE(rdate)"), 'asc')
                ->get();

            $data['leadsMonthly'] = [];
        }

        return view('Users.leadsmonthlydetail', $data);
    }
    public function amountmonthlyDetail(Request $request)
    {
        $week  = $request->week;
        $month = $request->month;
        $year  = $request->year;
        $currentYear = $year;

        // Currency name mapping
        $currencyMap = [
            'POUND'        => 'GBP',
            'EURO'         => 'EUR',
            'DIRHAM'       => 'AED',
            'SAUDI RIYAL'  => 'SAR',
            'OMANI RIYAL'  => 'OMR'
        ];

        // Load all currency values
        $currencyValues = DB::table('currency_values')
            ->pluck('value', 'currency')
            ->mapWithKeys(function ($val, $key) {
                return [strtoupper(trim($key)) => $val];
            });

        if ($week == '' && $month == '') {
            // Monthly View
            $groupKey = 'month';

            // Cost based on rdate
            $costOrders = DB::table('tb_order')
                ->select('cost', 'current_value', 'curr_name', DB::raw('MONTH(rdate) as group_key'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->whereYear('rdate', $currentYear)
                ->get();

            // Paid/Due based on pay_date
            $payOrders = DB::table('tb_order')
                ->select('paid', 'current_value', 'due', 'curr_name', DB::raw('MONTH(pay_date) as group_key'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->whereYear('pay_date', $currentYear)
                ->get();
        } else {
            // Weekly View
            $groupKey = 'date';

            if ($week == 1) {
                $date_start = '01-' . $month . '-' . $year;
                $date_end   = '07-' . $month . '-' . $year;
            } else if ($week == 2) {
                $date_start = '08-' . $month . '-' . $year;
                $date_end   = '14-' . $month . '-' . $year;
            } else if ($week == 3) {
                $date_start = '15-' . $month . '-' . $year;
                $date_end   = '21-' . $month . '-' . $year;
            } else if ($week == 4) {
                $tot_days   = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                $date_start = '22-' . $month . '-' . $year;
                $date_end   = $tot_days . '-' . $month . '-' . $year;
            }

            $startDate = date("Y-m-d", strtotime($date_start));
            $endDate   = date("Y-m-d", strtotime($date_end));

            // Cost from rdate
            $costOrders = DB::table('tb_order')
                ->select('cost', 'current_value', 'curr_name', DB::raw('DATE(rdate) as group_key'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->whereBetween(DB::raw("DATE(rdate)"), [$startDate, $endDate])
                ->get();

            // Paid/Due from pay_date
            $payOrders = DB::table('tb_order')
                ->select('paid', 'current_value', 'due', 'curr_name', DB::raw('DATE(pay_date) as group_key'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->whereBetween(DB::raw("DATE(pay_date)"), [$startDate, $endDate])
                ->get();
        }

        // Initialize data store
        $groupedData = [];

        // Process cost data
        if ($costOrders != '[]') {
            foreach ($costOrders as $order) {
                $key = $order->group_key;
                $cost = (float) $order->cost;
                $curr = $order->curr_name;

                if ($curr != 'GBP' && $curr != 'POUND') {
                    // $code = $currencyMap[$curr] ?? $curr;
                    $rate = $order->current_value;                
                        $cost *= $rate;
                    
                }
              
                if (!isset($groupedData[$key])) {
                    $groupedData[$key] = [
                        $groupKey => $key,
                        'total_cost' => 0,
                        'total_paid' => 0,
                        'total_due' => 0,
                    ];
                }

                $groupedData[$key]['total_cost'] += $cost;
            }
           
            // Process paid/due data
            foreach ($payOrders as $order) {
                $key = $order->group_key;
                $paid = (float) $order->paid;
                $due = (float) $order->due;
                $curr = $order->curr_name;

                if ($curr != 'GBP' && $curr != 'POUND') {
                    $code = $currencyMap[$curr] ?? $curr;
                    $rate = $currencyValues['GBP'];                   
                        $paid *= $rate;
                        $due  *= $rate;                    
                }

                if (!isset($groupedData[$key])) {
                    $groupedData[$key] = [
                        $groupKey => $key,
                        'total_cost' => 0,
                        'total_paid' => 0,
                        'total_due' => 0,
                    ];
                }

                $groupedData[$key]['total_paid'] += $paid;
                $groupedData[$key]['total_due']  += $due;
            }
        }

        // Final output
        if ($groupKey == 'month') {
            $data['leadsMonthly'] = collect($groupedData)->sortBy($groupKey)->values();
            $data['leadsWiseData'] = [];
        } else {
            $data['leadsWiseData'] = collect($groupedData)->sortBy($groupKey)->values();
            $data['leadsMonthly'] = [];
        }
        
        return view('Users.amountdetails', $data);
    }


    public function getPaymentDetail(Request $request)
    {
        $month = $request->month;
        $year  = $request->year;
        $status = $request->status;
        // Assuming $currencyMap and $currencyValues are available or fetched above this block
        $currencyMap = [
            'POUND'        => 'GBP',
            'EURO'         => 'EUR',
            'DIRHAM'       => 'AED',
            'SAUDI RIYAL'  => 'SAR',
            'OMANI RIYAL'  => 'OMR'
        ];

        $currencyValues = DB::table('currency_values')->pluck('value', 'currency')->mapWithKeys(function ($val, $key) {
            return [strtoupper(trim($key)) => $val];
        });
        if ($status == 'paid') {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(pay_date) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->where('paid', '!=', 0)
                ->where('paid', '!=', '')
                ->whereYear('pay_date', $year)
                ->whereMonth('pay_date', $month)
                ->get();
        } else if ($status == 'due') {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(pay_date) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->where('due', '!=', 0)
                ->where('due', '!=', '')
                ->whereYear('pay_date', $year)
                ->whereMonth('pay_date', $month)
                ->get();
        } else {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(rdate) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->whereYear('rdate', $year)
                ->whereMonth('rdate', $month)
                ->get();
        }


        $groupedData = [];

        $data = [
            'paid' => 0,
            'due' => 0,
            'cost' => 0,
            'orders' => []
        ];

        foreach ($orders as $order) {
            $paid = (float) $order->paid;
            $due = (float) $order->due;
            $cost = (float) $order->cost;

            $currName = strtoupper(trim($order->curr_name));

            if (($paid > 0 || $due > 0) && $currName != 'GBP' && $currName != 'POUND') {
                $currencyCode = $currencyMap[$currName] ?? $currName;
                $rate = $currencyValues['GBP'] ?? null;

                if ($rate && $rate != 0) {
                    $paid *= $rate;
                    $due  *= $rate;
                    $cost *= $rate;
                }
            }

            $data['paid'] += $paid;
            $data['due']  += $due;
            $data['cost'] += $cost;

            // Optional: store each order info
            $data['orders'][] = [
                'id' => $order->id,
                'topic' => $order->topic,
                'status' => $order->pay_status,
                'code' => $order->order_code,
                'pay_date' => $order->pay_date,
                'paid' => round($paid),
                'due' => round($due),
                'cost' => round($cost),
            ];
        }
        return view('Users.paymenttable', $data);
    }
    public function getDatePaymentDetail(Request $request)
    {
        $month = $request->month;
        $year  = $request->year;
        $date = $request->date;
        $status = $request->status;
        // if ($week == 1) {
        //     $date_start = '01-' . $month . '-' . $year;
        //     $date_end   = '07-' . $month . '-' . $year;
        // } else if ($week == 2) {
        //     $date_start = '08-' . $month . '-' . $year;
        //     $date_end   = '14-' . $month . '-' . $year;
        // } else if ($week == 3) {
        //     $date_start = '15-' . $month . '-' . $year;
        //     $date_end   = '21-' . $month . '-' . $year;
        // } else if ($week == 4) {
        //     $tot_days   = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        //     $date_start = '22-' . $month . '-' . $year;
        //     $date_end   = $tot_days . '-' . $month . '-' . $year;
        // }

        //$startDate = date("Y-m-d", strtotime($date_start));
        // $endDate   = date("Y-m-d", strtotime($date_end));
        // Assuming $currencyMap and $currencyValues are available or fetched above this block
        $currencyMap = [
            'POUND'        => 'GBP',
            'EURO'         => 'EUR',
            'DIRHAM'       => 'AED',
            'SAUDI RIYAL'  => 'SAR',
            'OMANI RIYAL'  => 'OMR'
        ];

        $currencyValues = DB::table('currency_values')->pluck('value', 'currency')->mapWithKeys(function ($val, $key) {
            return [strtoupper(trim($key)) => $val];
        });
        if ($status == 'paid') {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(pay_date) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->where('paid', '!=', 0)
                ->where('paid', '!=', '')
                ->where('pay_date', $date)
                ->get();
        } else if ($status == 'due') {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(pay_date) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->where('due', '!=', 0)
                ->where('due', '!=', '')
                ->where('pay_date', $date)
                ->get();
        } else {
            $orders = DB::table('tb_order')
                ->select('paid', 'id', 'topic', 'pay_date', 'pay_status', 'due', 'cost', 'order_code', 'curr_name', DB::raw('MONTH(rdate) as month'))
                ->where('order_source', 'BD')
                ->where('admin_id', 1)
                ->where('order_code', '!=', '')
                ->where('rdate', $date)
                ->get();
        }


        $groupedData = [];

        $data = [
            'paid' => 0,
            'due' => 0,
            'cost' => 0,
            'orders' => []
        ];

        foreach ($orders as $order) {
            $paid = (float) $order->paid;
            $due = (float) $order->due;
            $cost = (float) $order->cost;

            $currName = strtoupper(trim($order->curr_name));

            if (($paid > 0 || $due > 0) && $currName != 'GBP' && $currName != 'POUND') {
                $currencyCode = $currencyMap[$currName] ?? $currName;
                $rate = $currencyValues['GBP'] ?? null;

                if ($rate && $rate != 0) {
                    $paid *= $rate;
                    $due  *= $rate;
                    $cost *= $rate;
                }
            }

            $data['paid'] += $paid;
            $data['due']  += $due;
            $data['cost'] += $cost;

            // Optional: store each order info
            $data['orders'][] = [
                'id' => $order->id,
                'topic' => $order->topic,
                'status' => $order->pay_status,
                'code' => $order->order_code,
                'pay_date' => $order->pay_date,
                'paid' => round($paid),
                'due' => round($due),
                'cost' => round($cost),
            ];
        }
        return view('Users.paymenttable', $data);
    }
}