<?php

namespace App\Api\Http\Controllers\Api\V2\Analytics;

use App\Http\Controllers\Controller;
use App\SalesHistory;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Api\Http\Requests\Analytics\AnalyticsListRequest;
use App\Quarter;

class AnalyticsController extends Controller
{
    public function index(AnalyticsListRequest $request)
    {

        $request_from = $request->request_from;
        $request_to = $request->request_to;
        $request_type = $request->type;

        if(auth()->user()->hasRole('sales-head')){
            $branch_head_id = auth()->user()->id;
        }
        else{
            $branch_head_id = $request->branch_head_id;
        }
        
        $sales_person_id = $request->sales_person_id;
        $team_leader_id = $request->team_leader_id;


        $quarter_from =  Quarter::where('name', $request_from)->first();
        $quarter_to =  Quarter::where('name', $request_to)->first();

        $date1 = Carbon::createFromDate($quarter_from->date_from);
        $date2 = Carbon::createFromDate($quarter_to->date_to);
        if ($date1->lessThan($date2)) {
            $smallerDate = $date1;
            $greaterDate = $date2;
        } else {
            $smallerDate = Carbon::createFromDate($quarter_to->date_from);
            $greaterDate = Carbon::createFromDate($quarter_from->date_to);
        }
        
        $monthsDifference = $smallerDate->diffInMonths($greaterDate);

        $quarters = Quarter::whereDate('date_from', '>=', $smallerDate)
            ->whereDate('date_to', '<=', $greaterDate)
            ->count();
       
        $monthsDifference = $quarters * 3;


        $totalSales = SalesHistory::select(
            DB::raw('SUM(sale_value) as total_sale_value'),
            DB::raw('COUNT(DISTINCT sale_id) as total_sale_count')
        )  ->whereIn('quarter', [$quarter_from->name, $quarter_to->name])
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->first();

            $totalSaleCountAll = SalesHistory::whereIn('quarter', [$quarter_from->name, $quarter_to->name])
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id === '' && $team_leader_id === '' && $sales_person_id === '',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->count(DB::raw('DISTINCT sale_id'));
        

        $Projects_color_code = [
            '#4285F4',
            '#EA4335',
            '#FBBC05',
            '#23DAE5',
            '#4A34BE',
            '#1CC96D',
            '#FF9228',
            '#279548',
            '#A311A6'
        ];

        $allProjects = SalesHistory::with('getProject')
        ->select('project_id', DB::raw('COUNT(DISTINCT sale_id) as total_sale_count'))
           
            ->whereBetween('sale_date', [$quarter_from->date_from, $quarter_to->date_to])
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->groupBy('project_id')
            ->get()
            ->map(function ($item, $index) use ($totalSaleCountAll, $Projects_color_code) {
                $procolorIndex = $index % count($Projects_color_code);
                $totalSaleCount = $item->total_sale_count;
                $salePercentage = ($totalSaleCountAll > 0) ? round(($totalSaleCount / $totalSaleCountAll) * 100, 2) : 0;

                return [
                    'project_name' => optional($item->getProject)->project_name,
                    'project_code' => optional($item->getProject)->project_code,
                    'total_sale_count' => $item->total_sale_count ?? 0,
                    'sale_percentage' => $salePercentage . '%',
                    'color_code' => $Projects_color_code[$procolorIndex]
                ];
            })
            ->sortByDesc('total_sale_count')
            ->values()
            ->toArray();

        $projects = array_slice($allProjects, 0, 8);
        $otherProjects = array_slice($allProjects, 8);
        $totalOtherSaleCount = collect($otherProjects)->sum('total_sale_count');

        if ($totalSales->total_sale_count > 0) {
            $otherEntry  =
                [
                    'project_name' => 'Other',
                    'project_code' => 'Other',
                    'total_sale_count' => $totalOtherSaleCount  ?? 0,
                    'sale_percentage' => ($totalSaleCountAll > 0) ? round(($totalOtherSaleCount / $totalSaleCountAll) * 100, 2) : 0 . '0%',
                    'color_code' => $Projects_color_code[8]
                ];

            $projects[] = $otherEntry;

        $totalSalesArray = $totalSales->toArray();

        $totalSalesArray = is_array($totalSalesArray) ? $totalSalesArray : [];

        $ageData = SalesHistory::whereIn('quarter', [$quarter_from->name, $quarter_to->name])
            ->whereNotNull('dob')
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->get()
            ->groupBy(function ($item) {
                $dob = $item->dob;


                if (!is_null($dob) && \Carbon\Carbon::hasFormat($dob, 'd-m-Y')) {
                    $age = \Carbon\Carbon::parse($dob)->age;

                    if ($age < 20) return 'saleCount_below_20';
                    elseif ($age >= 20 && $age <= 25) return 'saleCount_20_25';
                    elseif ($age >= 25 && $age <= 30) return 'saleCount_25_30';
                    elseif ($age >= 30 && $age <= 35) return 'saleCount_30_35';
                    elseif ($age >= 35 && $age <= 40) return 'saleCount_35_40';
                    elseif ($age >= 40 && $age <= 45) return 'saleCount_40_45';
                    elseif ($age >= 45 && $age <= 50) return 'saleCount_45_50';
                    elseif ($age >= 50 && $age <= 55) return 'saleCount_50_55';
                    elseif ($age >= 55 && $age <= 60) return 'saleCount_55_60';
                    else return 'saleCount_above_60';
                }


                return 'unknown_age_group';
            })
            ->map(function ($group) use ($totalSalesArray) {
                $totalCount = $group->count();
                $tot_sale_value = isset($totalSalesArray['total_sale_count']) ? $totalSalesArray['total_sale_count'] : 0;
                $percentage = ($tot_sale_value > 0) ? round(($totalCount / $tot_sale_value) * 100, 1) : 0;

                return [
                    'sale_count' => $totalCount  ?? 0,
                    'percentage' => $percentage . '%',
                ];
            });


        $allAgeGroups = [
            'saleCount_below_20', 'saleCount_20_25', 'saleCount_25_30', 'saleCount_30_35',
            'saleCount_35_40', 'saleCount_40_45', 'saleCount_45_50', 'saleCount_50_55',
            'saleCount_55_60', 'saleCount_above_60', 'unknown_age_group',
        ];

        $defaultAgeGroups = [
            'saleCount_below_20' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_20_25' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_25_30' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_30_35' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_35_40' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_40_45' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_45_50' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_50_55' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_55_60' => ['sale_count' => 0, 'percentage' => '0%'],
            'saleCount_above_60' => ['sale_count' => 0, 'percentage' => '0%'],
            'unknown_age_group' => ['sale_count' => 0, 'percentage' => '0%'],
        ];

        // Merge default age groups with calculated $ageData
        $finalAgeData = array_merge($defaultAgeGroups, $ageData->toArray());



        $genderData = SalesHistory::with('getProject')
            ->select('gender',  DB::raw('COUNT(DISTINCT sale_id) as total_sale_count'))
            ->whereNotNull('gender')
            ->whereIn('quarter', [$quarter_from->name, $quarter_to->name])
            ->where('gender', '<>', '')
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->groupBy('gender')
            ->get()
            ->map(function ($item) use ($totalSaleCountAll) {
                $totalSaleCount = $item->total_sale_count;
               
                $salePercentage = ($totalSaleCountAll > 0) ? round(($totalSaleCount / $totalSaleCountAll) * 100, 2) : 0;

                return [
                    $item->gender => [
                        'percentage' => $salePercentage . '%',
                        'sale_count' => $item->total_sale_count ?? 0,
                    ],
                ];
            })
            ->reduce(function ($carry, $item) {
                return array_merge($carry, $item);
            }, []);


        $defaultGenderData = [
            'Male' => ['percentage' => '0%', 'sale_count' => 0],
            'Female' => ['percentage' => '0%', 'sale_count' => 0],
        ];

        // Merge default gender groups with calculated $genderData
        $finalGenderData = array_merge($defaultGenderData, $genderData);

        $location_color_code = [
            '#4285F4',
            '#EA4335',
            '#FBBC05',
            '#23DAE5',
            '#4A34BE',
            '#1CC96D',
            '#FF9228',
            '#279548',
            '#A311A6'
        ];
        
        $alllocationData = SalesHistory::select('district', DB::raw('COUNT(CASE WHEN district NOT IN (1) AND district IS NOT NULL THEN id END) as total_sale_count'))
            ->whereNotIn('district', [1])
            ->whereNotNull('district')
            ->whereBetween('sale_date', [$quarter_from->date_from, $quarter_to->date_to])
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->groupBy('district')
            ->get()
            ->map(function ($item, $index) use ($totalSaleCountAll, $location_color_code) {
                $totalSaleCount = $item->total_sale_count;
                $salePercentage = ($totalSaleCountAll > 0) ? round(($totalSaleCount / $totalSaleCountAll) * 100, 2) : 0;
        
                $colorIndex = ($index) % count($location_color_code);
        
                return [
                    'location_name' => $item->district,
                    'total_sale_count' => $item->total_sale_count ?? 0,
                    'sale_percentage' => $salePercentage . '%',
                    'color_code' => $location_color_code[$colorIndex],
                    'colorIndex'=>$colorIndex
                ];
            })
            ->sortByDesc('total_sale_count')
            ->values()
            ->toArray();
        
        
        
        



        $locations = array_slice($alllocationData, 0, 8);
        $otherlocation = array_slice($alllocationData, 8);
        $totalOtherSaleCount = collect($otherlocation)->sum('total_sale_count');

        if ($totalSales->total_sale_count > 0) {
            $LocaotherEntry  =
                [
                    'location_name' => 'Other',
                    'total_sale_count' => $totalOtherSaleCount ?? 0,
                    'sale_percentage' => ($totalSaleCountAll > 0) ? round(($totalOtherSaleCount / $totalSaleCountAll) * 100, 2) : 0 . '0%',
                    'color_code' =>  $location_color_code[count($location_color_code)-1]
                ];

            $locations[] = $LocaotherEntry;
        }


        $Sale_color_code = [
            '#4285F4',
            '#EA4335',
            '#FBBC05',
            '#23DAE5',
            '#4A34BE',
            '#1CC96D',
            '#FF9228',
            '#279548',
            '#A311A6'
        ];

        $saleValueData = SalesHistory::select(
            'sale_value',
            DB::raw('CASE WHEN sale_value >= 2500000 AND sale_value <= 5000000 THEN "25-50" 
                               WHEN sale_value > 5000000 AND sale_value <= 7500000 THEN "50-75" 
                               WHEN sale_value > 7500000 AND sale_value <= 10000000 THEN "75-100" 
                               WHEN sale_value > 10000000 AND sale_value <= 12500000 THEN "100-125" 
                               WHEN sale_value > 12500000 AND sale_value <= 15000000 THEN "125-150"
                               WHEN sale_value > 15000000 AND sale_value <= 17500000 THEN "150-175"
                               WHEN sale_value > 17500000 AND sale_value <= 20000000 THEN "175-200"
                               WHEN sale_value > 20000000  THEN "200 Above"
                               ELSE "Other" END as sale_range'),
         DB::raw('COUNT(DISTINCT sale_id) as total_sale_count')
        )->whereBetween('sale_date', [$quarter_from->date_from, $quarter_to->date_to])
            ->when($branch_head_id, function ($query) use ($branch_head_id) {
                return $query->where('branch_head_id', $branch_head_id);
            })
            ->when($team_leader_id, function ($query) use ($team_leader_id) {
                return $query->where('team_leader_id', $team_leader_id);
            })
            ->when($sales_person_id, function ($query) use ($sales_person_id) {
                return $query->where('sales_person_id', $sales_person_id);
            })
            ->when(
                $branch_head_id ='' && $team_leader_id ='' && $sales_person_id ='',
                function ($query) {
                    $query->where('user_active', 1);
                    return $query->orWhere('team_active', 1);
                }
            )
            ->groupBy('sale_range')
            ->having('sale_range', '<>', 'Other') // Exclude the "Other" category
            ->get()
            ->map(function ($item, $index) use ($totalSaleCountAll, $Sale_color_code) {
                $totalSaleCount = $item->sale_count;
                $salePercentage = ($totalSaleCountAll > 0) ? round(($totalSaleCount / $totalSaleCountAll) * 100, 2) : 0;
                $saleIndex = $index % count($Sale_color_code);


                

                if ($item->sale_value >= 10000000) {
                    $sale_value =  round($item->sale_value /  10000000) . ' cr';
                } elseif (($item->sale_value > 99999) && ($item->sale_value < 10000000)) {
                    $sale_value =  round($item->sale_value /  100000) . ' lakh';
                } else {
                    $sale_value = $item->sale_value;
                }

                return [
                    'sale_value' => $sale_value ?? 0,
                    'sale_range' => $item->sale_range,
                    'sale_percentage' => $salePercentage . '%',
                    'sale_count' => $item->sale_count ?? 0,
                    'color_code' => $Sale_color_code[$saleIndex]
                ];
            })
            ->toArray();



        if ($totalSales->total_sale_value >= 10000000) {
            $total_sale_value =  round($totalSales->total_sale_value /  10000000) . ' cr';
        } elseif (($totalSales->total_sale_value > 99999) && ($totalSales->total_sale_value < 10000000)) {
            $total_sale_value =  round($totalSales->total_sale_value /  100000) . ' lakh';
        } else {
            $total_sale_value = $totalSales->total_sale_value;
        }

        $avrg_tot = round($totalSales->total_sale_value / $monthsDifference);


        if ($avrg_tot >= 10000000) {
            $avrg_tot = round($avrg_tot /  10000000) . ' Cr';
        } elseif (($avrg_tot > 99999) && ($avrg_tot < 10000000)) {
            $avrg_tot = round($avrg_tot /  100000) . ' Lakh';
        } else {
            $avrg_tot;
        }


       

        $jsonData = [
            'success' => true,
            'message' => '',
            'data' => [
                'total_sale' => $totalSales->total_sale_count ?? 0,
                'total_sale_value' => $total_sale_value ?? 0,
                'tenure' => strval($monthsDifference) ?? '0',
                'average_sale_value_tenure' => strval($avrg_tot) ?? '0',
                'project' => $projects,
                'age' => $finalAgeData,
                'gender' => $finalGenderData,
                'location' => $locations,
                'sale_value' => $saleValueData,
                
            ],
        ];

        return response()->json($jsonData);
    }
}


    private function generateRandomColor()
    {
        $color = sprintf("#%06x", rand(0, 0xFFFFFF));
        return $color;
    }
}
