<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Http\Requests\LambdaRequest;
use DB;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\InvoicesExport;
use Carbon\Carbon;

class CoachingtoolDashboard extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        $this->_service = 'form';
        $this->_module = 'formentity';
        $this->_viewname = 'coachingtooldashboard';
        $this->_title = ' : Submitted Data';
        $this->_base_url = '/coachingtooldashboard';
    }

    public function sbmAndMeActionPlanReport()
    {
        $permissions = json_decode(Auth::user()->permissions);
        // Generalised variables for partition, form_id, action plan label, and field names
        $partition = 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58';
        $form_id = '1742292203-0153c37f-1433-446a-a059-de3597eaca58';
        $actionPlanLabel = 'action_plan_label';

        // Generalised field names for action plan, timeline, and adherence
        $actionPlanField = 'action_plan';
        $timelineField = 'timeline_agreed_for_selected_action_plans_completion';
        $adherenceField = 'adherance_to_action_plan';

        // Generalised variable names for pipeline
        $actionPlanArrayField = 'action_plan_array';
        $actionKeyField = 'actionKey';
        $actionLabelField = 'actionLabel';
        $timelineKeyField = 'timelineKey';
        $adherenceKeyRawField = 'adherenceKeyRaw';
        $adherenceKeyField = 'adherenceKey';

        $matchConditions = [
            'form_id' => $form_id,
            'partition' => $partition,
            'status' => 'active',
        ];
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }
        
        $reqdataSBM = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                [
                    '$match' => $matchConditions,
                ],
                [
                    '$addFields' => [
                        $actionPlanArrayField => [
                            '$let' => [
                                'vars' => ['ap' => '$formdata.' . $actionPlanField],
                                'in' => [
                                    '$cond' => [
                                        ['$eq' => [['$type' => '$$ap'], 'array']],
                                        '$$ap',
                                        [
                                            '$cond' => [['$eq' => [['$type' => '$$ap'], 'string']], ['$split' => ['$$ap', ',']], []],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                [
                    '$unwind' => [
                        'path' => '$' . $actionPlanArrayField,
                        'preserveNullAndEmptyArrays' => false,
                    ],
                ],
                // Generalized: No mapping, just use the value as key and label
                [
                    '$addFields' => [
                        $actionKeyField => '$' . $actionPlanArrayField,
                        $actionLabelField => '$' . $actionPlanArrayField,
                    ],
                ],
                [
                    '$addFields' => [
                        $timelineKeyField => ['$toLower' => ['$ifNull' => ['$formdata.' . $timelineField, '']]],
                        $adherenceKeyRawField => ['$toLower' => ['$ifNull' => ['$formdata.' . $adherenceField, '']]],
                    ],
                ],
                [
                    '$addFields' => [
                        $adherenceKeyField => [
                            '$cond' => [['$eq' => ['$' . $adherenceKeyRawField, 'staus_quo']], 'status_quo', '$' . $adherenceKeyRawField],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$' . $actionKeyField,
                        'anyLabel' => ['$first' => '$' . $actionLabelField],
                        'total' => ['$sum' => 1],
                        'less_than_1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, 'less_than_1_month']], 1, 0]]],
                        '1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '1_month']], 1, 0]]],
                        '3_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '3_month']], 1, 0]]],
                        '6_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '6_month']], 1, 0]]],
                        'on_track' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'on_track']], 1, 0]]],
                        'needs_correction' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'needs_correction']], 1, 0]]],
                        'status_quo' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'status_quo']], 1, 0]]],
                    ],
                ],
                [
                    '$project' => array_merge([
                        '_id' => 0,
                        $actionPlanLabel => '$anyLabel',
                        'total' => 1,
                        'less_than_1_month' => 1,
                        '1_month' => 1,
                        '3_months' => 1,
                        '6_months' => 1,
                        'on_track' => 1,
                        'needs_correction' => 1,
                        'status_quo' => 1,
                    ]),
                ],
                [
                    '$sort' => ['total' => -1],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $reqdataSBM);
        $submissions = [];
        $actionPlanItems = [
            '100% adherence in Tour plan' => ['100_adherence_in_tour_plan'],
            '95% MCL Coverage, Visit frequency >90%, 5% Non visited doctors' => ['95_mcl_coverage_visit_frequency_90_5_non_visited_doctors'],
            '1 Camp & 4 Clinics' => ['1_camp_and_4_clinics', '1_camp_4_clinics'],
            'Tab Detailing in doctor calls' => ['tab_detailing_in_doctor_calls'],
            'New Conversions' => ['new_conversions'],
            'MCL productivity' => ['mcl_productivity'],
            'Others mention if any' => ['others_mention_if_any'],
        ];

        // Build a reverse lookup: slug => action plan label
        $slugToLabel = [];
        foreach ($actionPlanItems as $label => $slugs) {
            foreach ($slugs as $slug) {
                $slugToLabel[$slug] = $label;
            }
        }

        foreach ($sbmResponse->data->submissions as $submission) {
            $slug = \Illuminate\Support\Str::slug($submission->{$actionPlanLabel}, '_');
            // Find the matching action plan label for this slug
            $matchedLabel = null;
            foreach ($actionPlanItems as $label => $slugs) {
                if (in_array($slug, $slugs)) {
                    $matchedLabel = $label;
                    break;
                }
            }
            if ($matchedLabel === null) {
                // If not matched, skip this submission
                continue;
            }
            // Set the action plan label to the matched label (the key)
            $submission->{$actionPlanLabel} = $matchedLabel;

            if (isset($submissions[$matchedLabel])) {
                // Merge all parameters (sum numeric values, keep latest for non-numeric)
                foreach ($submission as $key => $value) {
                    if ($key === $actionPlanLabel) {
                        // Already set as matched label, skip
                        continue;
                    }
                    if (isset($submissions[$matchedLabel]->$key)) {
                        // If both are numeric, sum them
                        if (is_numeric($submissions[$matchedLabel]->$key) && is_numeric($value)) {
                            $submissions[$matchedLabel]->$key += $value;
                        } else {
                            // For non-numeric, keep the latest value (from $submission)
                            $submissions[$matchedLabel]->$key = $value;
                        }
                    } else {
                        // If key does not exist, just set it
                        $submissions[$matchedLabel]->$key = $value;
                    }
                }
            } else {
                $submissions[$matchedLabel] = clone $submission;
            }
        }
        //echo "<pre>"; print_r($submissions); exit;
        return $this->view('dashboard')->with([
            'title' => 'SBM/ME',
            'response' => $submissions,
            'actionPlanItems' => array_keys($actionPlanItems),
            'base_url' => $this->_base_url,
            'buttons' => $this->get_list_buttons(),
        ]);
    }

    public function rmActionPlanReport()
    {
        // Generalised variables for partition, form_id, action plan label, and field names
        $partition = 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e';
        $form_id = '1742297848-159745be-ac42-413b-9aee-31466671011e';
        $actionPlanLabel = 'action_plan_label';

        // Generalised field names for action plan, timeline, and adherence
        $actionPlanField = 'action_plan';
        $timelineField = 'timeline_agreed_for_selected_action_plans_completion';
        $adherenceField = 'adherance_to_action_plan';

        // Generalised variable names for pipeline
        $actionPlanArrayField = 'action_plan_array';
        $actionKeyField = 'actionKey';
        $actionLabelField = 'actionLabel';
        $timelineKeyField = 'timelineKey';
        $adherenceKeyRawField = 'adherenceKeyRaw';
        $adherenceKeyField = 'adherenceKey';

        $matchConditions = [
            'form_id' => $form_id,
            'partition' => $partition,
            'status' => 'active',
        ];
        $permissions = json_decode(Auth::user()->permissions);
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $reqdataRM = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                [
                    '$match' => $matchConditions
                ],
                [
                    '$addFields' => [
                        $actionPlanArrayField => [
                            '$let' => [
                                'vars' => ['ap' => '$formdata.' . $actionPlanField],
                                'in' => [
                                    '$cond' => [
                                        ['$eq' => [['$type' => '$$ap'], 'array']],
                                        '$$ap',
                                        [
                                            '$cond' => [['$eq' => [['$type' => '$$ap'], 'string']], ['$split' => ['$$ap', ',']], []],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                [
                    '$unwind' => [
                        'path' => '$' . $actionPlanArrayField,
                        'preserveNullAndEmptyArrays' => false,
                    ],
                ],
                // Generalized: No mapping, just use the value as key and label
                [
                    '$addFields' => [
                        $actionKeyField => '$' . $actionPlanArrayField,
                        $actionLabelField => '$' . $actionPlanArrayField,
                    ],
                ],
                [
                    '$addFields' => [
                        $timelineKeyField => ['$toLower' => ['$ifNull' => ['$formdata.' . $timelineField, '']]],
                        $adherenceKeyRawField => ['$toLower' => ['$ifNull' => ['$formdata.' . $adherenceField, '']]],
                    ],
                ],
                [
                    '$addFields' => [
                        $adherenceKeyField => [
                            '$cond' => [['$eq' => ['$' . $adherenceKeyRawField, 'staus_quo']], 'status_quo', '$' . $adherenceKeyRawField],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$' . $actionKeyField,
                        'anyLabel' => ['$first' => '$' . $actionLabelField],
                        'total' => ['$sum' => 1],
                        'less_than_1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, 'less_than_1_month']], 1, 0]]],
                        '1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '1_month']], 1, 0]]],
                        '3_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '3_month']], 1, 0]]],
                        '6_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '6_month']], 1, 0]]],
                        'on_track' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'on_track']], 1, 0]]],
                        'needs_correction' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'needs_correction']], 1, 0]]],
                        'status_quo' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'status_quo']], 1, 0]]],
                    ],
                ],
                [
                    '$project' => array_merge([
                        '_id' => 0,
                        $actionPlanLabel => '$anyLabel',
                        'total' => 1,
                        'less_than_1_month' => 1,
                        '1_month' => 1,
                        '3_months' => 1,
                        '6_months' => 1,
                        'on_track' => 1,
                        'needs_correction' => 1,
                        'status_quo' => 1,
                    ]),
                ],
                [
                    '$sort' => ['total' => -1],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $reqdataRM);

        $submissions = [];
        $actionPlanItems = [
            'Tour Plan adherence of Team' => ['tour_plan_adherence_of_team'],
            '100% Spread of performance' => ['100_spread_of_performance'],
            '100% RM Region Target achievement' => ['100_rm_region_target_achievement'],
            'SOP at Mandate Brand performance' => ['sop_at_mandate_brand_performance'],
            'RM Mandate Target Achievement' => ['rm_mandate_target_achievement'],
            'SOP at NI Brand performance' => ['sop_at_ni_brand_performance'],
            'RM NI Target Achievement' => ['rm_ni_target_achievement'],
            'Stretch working with SBMs' => ['stretch_working_with_sbms'],
            'Critical Geography time allocation' => ['critical_geography_time_allocation'],
            'Critical geography Revival' => ['critical_geography_revival'],
            'Covering all Sponsored doctors' => ['covering_all_sponsored_doctors'],
            'Ensuring equal spread at all doctors' => ['ensuring_equal_spread_at_all_doctors'],
            'Ensuring 1:5 times ROI from Sponsored doctors' => ['ensuring_1_5_times_roi_from_sponsored_doctors'],
            'Others' => ['others'],
        ];

        // Build a reverse lookup: slug => action plan label
        $slugToLabel = [];
        foreach ($actionPlanItems as $label => $slugs) {
            foreach ($slugs as $slug) {
                $slugToLabel[$slug] = $label;
            }
        }

        foreach ($sbmResponse->data->submissions as $submission) {
            $slug = \Illuminate\Support\Str::slug($submission->{$actionPlanLabel}, '_');
            // Find the matching action plan label for this slug
            $matchedLabel = null;
            foreach ($actionPlanItems as $label => $slugs) {
                if (in_array($slug, $slugs)) {
                    $matchedLabel = $label;
                    break;
                }
            }
            if ($matchedLabel === null) {
                // If not matched, skip this submission
                continue;
            }
            // Set the action plan label to the matched label (the key)
            $submission->{$actionPlanLabel} = $matchedLabel;

            if (isset($submissions[$matchedLabel])) {
                // Merge all parameters (sum numeric values, keep latest for non-numeric)
                foreach ($submission as $key => $value) {
                    if ($key === $actionPlanLabel) {
                        // Already set as matched label, skip
                        continue;
                    }
                    if (isset($submissions[$matchedLabel]->$key)) {
                        // If both are numeric, sum them
                        if (is_numeric($submissions[$matchedLabel]->$key) && is_numeric($value)) {
                            $submissions[$matchedLabel]->$key += $value;
                        } else {
                            // For non-numeric, keep the latest value (from $submission)
                            $submissions[$matchedLabel]->$key = $value;
                        }
                    } else {
                        // If key does not exist, just set it
                        $submissions[$matchedLabel]->$key = $value;
                    }
                }
            } else {
                $submissions[$matchedLabel] = clone $submission;
            }
        }

        return $this->view('dashboard')->with([
            'title' => 'RM',
            'response' => $submissions,
            'actionPlanItems' => array_keys($actionPlanItems),
            'base_url' => $this->_base_url,
            'buttons' => $this->get_list_buttons(),
        ]);
    }

    public function sbmAndMeGoalAndProficiencyTrackingReport()
    {
        $partition = 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e';
        $form_id = '1742297848-159745be-ac42-413b-9aee-31466671011e';
        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active',
            'is_deleted' => ['$ne' => true],
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $sbmCondition = [
            'partition' => 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58',
            'form_id' => '1742292203-0153c37f-1433-446a-a059-de3597eaca58',
            'pipeline' => [
                // 1) Slice
                [
                    '$match' => $matchConditions,
                ],

                // 2) Build record_month from record_date → formdata.record_date (string/date) → createdAt
                [
                    '$addFields' => [
                        '_m_from_record_date' => [
                            '$cond' => [['$eq' => [['$type' => '$record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$record_date']], 0, 7]], null],
                        ],
                        '_m_from_formdata_str' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'string']], ['$substr' => ['$formdata.record_date', 0, 7]], null],
                        ],
                        '_m_from_formdata_date' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$formdata.record_date']], 0, 7]], null],
                        ],
                        '_m_from_createdAt' => [
                            '$cond' => [['$eq' => [['$type' => '$createdAt'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$createdAt']], 0, 7]], null],
                        ],
                    ],
                ],
                [
                    '$addFields' => [
                        'record_month' => [
                            '$ifNull' => [
                                '$_m_from_record_date',
                                [
                                    '$ifNull' => [
                                        '$_m_from_formdata_str',
                                        [
                                            '$ifNull' => ['$_m_from_formdata_date', '$_m_from_createdAt'],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 3) Fan out goals
                [
                    '$project' => [
                        'record_month' => 1,
                        'user_id' => 1,
                        'goals' => [
                            [
                                'goal_name' => 'Tour Plan Adherence',
                                'goal_value' => '$formdata.proficiency-goal_1_tour_plan_adherance',
                            ],
                            [
                                'goal_name' => 'Basic Working',
                                'goal_value' => '$formdata.proficiency-goal_2_basic_working',
                            ],
                            [
                                'goal_name' => 'Activations',
                                'goal_value' => '$formdata.proficiency-goal_3_activations',
                            ],
                            [
                                'goal_name' => 'In-clinic Effectiveness',
                                'goal_value' => '$formdata.proficiency-goal_4_in_clinic_effectiveness',
                            ],
                            [
                                'goal_name' => 'Spread of Performance',
                                'goal_value' => '$formdata.proficiency-goal_5_spread_of_performance',
                            ],
                        ],
                    ],
                ],
                ['$unwind' => '$goals'],
                [
                    '$match' => [
                        'record_month' => ['$ne' => null],
                        'goals.goal_value' => ['$ne' => null],
                    ],
                ],

                // 4) NORMALIZE labels → Proficient / Competent / Intermediate / Beginner
                //    Works for values like "A - Proficient", "B - Competent", "C ...", "D ..." etc.
                [
                    '$addFields' => [
                        'goals.goal_value_norm' => [
                            '$switch' => [
                                'branches' => [['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'A']], 'then' => 'Proficient'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'B']], 'then' => 'Competent'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'C']], 'then' => 'Intermediate'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'D']], 'then' => 'Beginner']],
                                // if already a clean label or unexpected, keep original
                                'default' => '$goals.goal_value',
                            ],
                        ],
                    ],
                ],

                // 5) Count unique users per month+goal+normalized_label
                [
                    '$group' => [
                        '_id' => [
                            'month' => '$record_month',
                            'goal_name' => '$goals.goal_name',
                            'goal_value' => '$goals.goal_value_norm',
                        ],
                        'users' => ['$addToSet' => '$user_id'],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'month' => '$_id.month',
                        'goal_name' => '$_id.goal_name',
                        'goal_value' => '$_id.goal_value',
                        'user_count' => ['$size' => '$users'],
                    ],
                ],

                // 6) Nest by goal within month
                [
                    '$group' => [
                        '_id' => ['month' => '$month', 'goal_name' => '$goal_name'],
                        'values' => [
                            '$push' => [
                                'goal_value' => '$goal_value',
                                'user_count' => '$user_count',
                            ],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$_id.month',
                        'goals' => [
                            '$push' => [
                                'k' => '$_id.goal_name',
                                'v' => '$values',
                            ],
                        ],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'k' => '$_id',
                        'v' => [
                            '$arrayToObject' => [
                                '$filter' => [
                                    'input' => '$goals',
                                    'as' => 'item',
                                    'cond' => [
                                        '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 7) Collapse to { "YYYY-MM": { Goal 1: [...], Goal 2: [...] }, ... }
                [
                    '$group' => [
                        '_id' => null,
                        'entries' => [
                            '$push' => ['k' => '$k', 'v' => '$v'],
                        ],
                    ],
                ],
                [
                    '$replaceRoot' => [
                        'newRoot' => ['$arrayToObject' => '$entries'],
                    ],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $sbmCondition);

        // Fixed goal order (use your display names)
        $goalsList = ['Tour Plan Adherence', 'Basic Working', 'Activations', 'In-clinic Effectiveness', 'Spread of Performance'];
        //echo "<pre>"; print_r($sbmResponse); exit;
        return $this->view('goalandproficiency')->with([
            'title' => 'SBM/ME',
            'response' => $sbmResponse,
            'goalsList' => $goalsList,
            'base_url' => $this->_base_url,
            'buttons' => $this->get_list_buttons(),
        ]);
    }

    public function rmGoalAndProficiencyTracking()
    {
        $partition = 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e';
        $form_id = '1742297848-159745be-ac42-413b-9aee-31466671011e';
        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active',
            'is_deleted' => ['$ne' => true],
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $rmCondition = [
            'partition' => 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e',
            'form_id' => '1742297848-159745be-ac42-413b-9aee-31466671011e',
            'pipeline' => [
                // 1) Slice
                [
                    '$match' => $matchConditions,
                ],

                // 2) Build record_month from record_date → formdata.record_date (string/date) → createdAt
                [
                    '$addFields' => [
                        '_m_from_record_date' => [
                            '$cond' => [['$eq' => [['$type' => '$record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$record_date']], 0, 7]], null],
                        ],
                        '_m_from_formdata_str' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'string']], ['$substr' => ['$formdata.record_date', 0, 7]], null],
                        ],
                        '_m_from_formdata_date' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$formdata.record_date']], 0, 7]], null],
                        ],
                        '_m_from_createdAt' => [
                            '$cond' => [['$eq' => [['$type' => '$createdAt'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$createdAt']], 0, 7]], null],
                        ],
                    ],
                ],
                [
                    '$addFields' => [
                        'record_month' => [
                            '$ifNull' => [
                                '$_m_from_record_date',
                                [
                                    '$ifNull' => [
                                        '$_m_from_formdata_str',
                                        [
                                            '$ifNull' => ['$_m_from_formdata_date', '$_m_from_createdAt'],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 3) Fan out goals
                [
                    '$project' => [
                        'record_month' => 1,
                        'user_id' => 1,
                        'goals' => [
                            [
                                'goal_name' => 'Tour Plan Adherence Self & Team',
                                'goal_value' => '$formdata.proficiency-goal_1_tour_plan_adherence_self_and_team',
                            ],
                            [
                                'goal_name' => 'Spread of Performance',
                                'goal_value' => '$formdata.proficiency-goal_2_spread_of_performance',
                            ],
                            [
                                'goal_name' => 'Joint Field Work',
                                'goal_value' => '$formdata.proficiency-goal_3_joint_field_work',
                            ],
                            [
                                'goal_name' => 'KOL Engagement & ROI',
                                'goal_value' => '$formdata.proficiency-goal_4_kol_engagement_and_roi',
                            ],
                            // [
                            //     'goal_name'  => 'Spread of Performance',
                            //     'goal_value' => '$formdata.proficiency-goal_5_spread_of_performance',
                            // ],
                        ],
                    ],
                ],
                ['$unwind' => '$goals'],
                [
                    '$match' => [
                        'record_month' => ['$ne' => null],
                        'goals.goal_value' => ['$ne' => null],
                    ],
                ],

                // 4) NORMALIZE labels → Proficient / Competent / Intermediate / Beginner
                //    Works for values like "A - Proficient", "B - Competent", "C ...", "D ..." etc.
                [
                    '$addFields' => [
                        'goals.goal_value_norm' => [
                            '$switch' => [
                                'branches' => [['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'A']], 'then' => 'Proficient'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'B']], 'then' => 'Competent'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'C']], 'then' => 'Intermediate'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'D']], 'then' => 'Beginner']],
                                // if already a clean label or unexpected, keep original
                                'default' => '$goals.goal_value',
                            ],
                        ],
                    ],
                ],

                // 5) Count unique users per month+goal+normalized_label
                [
                    '$group' => [
                        '_id' => [
                            'month' => '$record_month',
                            'goal_name' => '$goals.goal_name',
                            'goal_value' => '$goals.goal_value_norm',
                        ],
                        'users' => ['$addToSet' => '$user_id'],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'month' => '$_id.month',
                        'goal_name' => '$_id.goal_name',
                        'goal_value' => '$_id.goal_value',
                        'user_count' => ['$size' => '$users'],
                    ],
                ],

                // 6) Nest by goal within month
                [
                    '$group' => [
                        '_id' => ['month' => '$month', 'goal_name' => '$goal_name'],
                        'values' => [
                            '$push' => [
                                'goal_value' => '$goal_value',
                                'user_count' => '$user_count',
                            ],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$_id.month',
                        'goals' => [
                            '$push' => [
                                'k' => '$_id.goal_name',
                                'v' => '$values',
                            ],
                        ],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'k' => '$_id',
                        'v' => [
                            '$arrayToObject' => [
                                '$filter' => [
                                    'input' => '$goals',
                                    'as' => 'item',
                                    'cond' => [
                                        '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 7) Collapse to { "YYYY-MM": { Goal 1: [...], Goal 2: [...] }, ... }
                [
                    '$group' => [
                        '_id' => null,
                        'entries' => [
                            '$push' => ['k' => '$k', 'v' => '$v'],
                        ],
                    ],
                ],
                [
                    '$replaceRoot' => [
                        'newRoot' => ['$arrayToObject' => '$entries'],
                    ],
                ],
            ],
        ];

        $rmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $rmCondition);
        //echo "<pre>"; print_r($sbmResponse); exit;

        $goalsList = ['Tour Plan Adherence Self & Team', 'Spread of Performance', 'Joint Field Work', 'KOL Engagement & ROI'];

        // $rmCondition = [
        //     'partition' => 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58',
        //     'form_id' => '1742292203-0153c37f-1433-446a-a059-de3597eaca58',
        //     'pipeline' => [
        //         [
        //             '$match' => [
        //                 'is_deleted' => false,
        //                 'formdata.record_date' => ['$exists' => true],
        //                 'partition' => 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58',
        //                 'form_id' => '1742292203-0153c37f-1433-446a-a059-de3597eaca58',
        //             ],
        //         ],
        //         [
        //             '$addFields' => [
        //                 'record_month' => [
        //                     '$cond' => [
        //                         ['$eq' => [['$type' => '$formdata.record_date'], 'string']],
        //                         [
        //                             '$dateToString' => [
        //                                 'format' => '%Y-%m',
        //                                 'date' => [
        //                                     '$dateFromString' => ['dateString' => '$formdata.record_date'],
        //                                 ],
        //                             ],
        //                         ],
        //                         [
        //                             '$cond' => [
        //                                 ['$eq' => [['$type' => '$formdata.record_date'], 'date']],
        //                                 [
        //                                     '$dateToString' => [
        //                                         'format' => '%Y-%m',
        //                                         'date' => '$formdata.record_date',
        //                                     ],
        //                                 ],
        //                                 null,
        //                             ],
        //                         ],
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         [
        //             '$project' => [
        //                 'record_month' => 1,
        //                 'user_id' => 1,
        //                 'goals' => [
        //                     [
        //                         'goal_name' => 'Tour Plan Adherence',
        //                         'goal_value' => '$formdata.proficiency-goal_1_tour_plan_adherance',
        //                     ],
        //                     [
        //                         'goal_name' => 'Basic Working',
        //                         'goal_value' => '$formdata.proficiency-goal_2_basic_working',
        //                     ],
        //                     [
        //                         'goal_name' => 'Activations',
        //                         'goal_value' => '$formdata.proficiency-goal_3_activations',
        //                     ],
        //                     [
        //                         'goal_name' => 'In-clinic Effectiveness',
        //                         'goal_value' => '$formdata.proficiency-goal_4_in_clinic_effectiveness',
        //                     ],
        //                     [
        //                         'goal_name' => 'Spread of Performance',
        //                         'goal_value' => '$formdata.proficiency-goal_5_spread_of_performance',
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         ['$unwind' => '$goals'],
        //         ['$match' => ['goals.goal_value' => ['$ne' => null]]],
        //         [
        //             '$group' => [
        //                 '_id' => [
        //                     'month' => '$record_month',
        //                     'goal_name' => '$goals.goal_name',
        //                     'goal_value' => '$goals.goal_value',
        //                 ],
        //                 'users' => ['$addToSet' => '$user_id'],
        //             ],
        //         ],
        //         [
        //             '$project' => [
        //                 'month' => '$_id.month',
        //                 'goal_name' => '$_id.goal_name',
        //                 'goal_value' => '$_id.goal_value',
        //                 'user_count' => ['$size' => '$users'],
        //                 '_id' => 0,
        //             ],
        //         ],
        //         [
        //             '$group' => [
        //                 '_id' => [
        //                     'month' => '$month',
        //                     'goal_name' => '$goal_name',
        //                 ],
        //                 'values' => [
        //                     '$push' => [
        //                         'goal_value' => '$goal_value',
        //                         'user_count' => '$user_count',
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         [
        //             '$group' => [
        //                 '_id' => '$_id.month',
        //                 'goals' => [
        //                     '$push' => [
        //                         'k' => '$_id.goal_name',
        //                         'v' => '$values',
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         [
        //             '$project' => [
        //                 '_id' => 0,
        //                 'k' => '$_id',
        //                 'v' => [
        //                     '$cond' => [
        //                         [
        //                             '$gt' => [
        //                                 [
        //                                     '$size' => [
        //                                         '$filter' => [
        //                                             'input' => '$goals',
        //                                             'as' => 'item',
        //                                             'cond' => [
        //                                                 '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
        //                                             ],
        //                                         ],
        //                                     ],
        //                                 ],
        //                                 0,
        //                             ],
        //                         ],
        //                         [
        //                             '$arrayToObject' => [
        //                                 '$filter' => [
        //                                     'input' => '$goals',
        //                                     'as' => 'item',
        //                                     'cond' => [
        //                                         '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
        //                                     ],
        //                                 ],
        //                             ],
        //                         ],
        //                         new \stdClass(),
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         [
        //             '$group' => [
        //                 '_id' => null,
        //                 'entries' => [
        //                     '$push' => [
        //                         'k' => '$k',
        //                         'v' => '$v',
        //                     ],
        //                 ],
        //             ],
        //         ],
        //         [
        //             '$replaceRoot' => [
        //                 'newRoot' => [
        //                     '$arrayToObject' => '$entries',
        //                 ],
        //             ],
        //         ],
        //     ],
        // ];
        // $rmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $rmCondition);
        //echo "<pre>"; print_r($rmResponse); exit;
        return $this->view('goalandproficiency')->with([
            'title' => 'RM',
            'response' => $rmResponse,
            'goalsList' => $goalsList,
            'base_url' => $this->_base_url,
            'buttons' => $this->get_list_buttons(),
        ]);
    }

    protected function get_list_buttons()
    {
        return [['title' => 'Add New', 'link' => $this->_base_url . '/addnew'], ['title' => 'Export', 'link' => '/export']];
    }

    public function exportsbmaction(Request $request)
    {
        //ralised variables for partition, form_id, action plan label, and field names
        $partition = 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58';
        $form_id = '1742292203-0153c37f-1433-446a-a059-de3597eaca58';

        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active'
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        // Gene
        $actionPlanLabel = 'action_plan_label';

        // Generalised field names for action plan, timeline, and adherence
        $actionPlanField = 'action_plan';
        $timelineField = 'timeline_agreed_for_selected_action_plans_completion';
        $adherenceField = 'adherance_to_action_plan';

        // Generalised variable names for pipeline
        $actionPlanArrayField = 'action_plan_array';
        $actionKeyField = 'actionKey';
        $actionLabelField = 'actionLabel';
        $timelineKeyField = 'timelineKey';
        $adherenceKeyRawField = 'adherenceKeyRaw';
        $adherenceKeyField = 'adherenceKey';

        $reqdataSBM = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                [
                    '$match' => $matchConditions,
                ],
                [
                    '$addFields' => [
                        $actionPlanArrayField => [
                            '$let' => [
                                'vars' => ['ap' => '$formdata.' . $actionPlanField],
                                'in' => [
                                    '$cond' => [
                                        ['$eq' => [['$type' => '$$ap'], 'array']],
                                        '$$ap',
                                        [
                                            '$cond' => [['$eq' => [['$type' => '$$ap'], 'string']], ['$split' => ['$$ap', ',']], []],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                [
                    '$unwind' => [
                        'path' => '$' . $actionPlanArrayField,
                        'preserveNullAndEmptyArrays' => false,
                    ],
                ],
                // Generalized: No mapping, just use the value as key and label
                [
                    '$addFields' => [
                        $actionKeyField => '$' . $actionPlanArrayField,
                        $actionLabelField => '$' . $actionPlanArrayField,
                    ],
                ],
                [
                    '$addFields' => [
                        $timelineKeyField => ['$toLower' => ['$ifNull' => ['$formdata.' . $timelineField, '']]],
                        $adherenceKeyRawField => ['$toLower' => ['$ifNull' => ['$formdata.' . $adherenceField, '']]],
                    ],
                ],
                [
                    '$addFields' => [
                        $adherenceKeyField => [
                            '$cond' => [['$eq' => ['$' . $adherenceKeyRawField, 'staus_quo']], 'status_quo', '$' . $adherenceKeyRawField],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$' . $actionKeyField,
                        'anyLabel' => ['$first' => '$' . $actionLabelField],
                        'total' => ['$sum' => 1],
                        'less_than_1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, 'less_than_1_month']], 1, 0]]],
                        '1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '1_month']], 1, 0]]],
                        '3_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '3_month']], 1, 0]]],
                        '6_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '6_month']], 1, 0]]],
                        'on_track' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'on_track']], 1, 0]]],
                        'needs_correction' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'needs_correction']], 1, 0]]],
                        'status_quo' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'status_quo']], 1, 0]]],
                    ],
                ],
                [
                    '$project' => array_merge([
                        '_id' => 0,
                        $actionPlanLabel => '$anyLabel',
                        'total' => 1,
                        'less_than_1_month' => 1,
                        '1_month' => 1,
                        '3_months' => 1,
                        '6_months' => 1,
                        'on_track' => 1,
                        'needs_correction' => 1,
                        'status_quo' => 1,
                    ]),
                ],
                [
                    '$sort' => ['total' => -1],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $reqdataSBM);
        $submissions = [];
        $actionPlanItems = [
            '100% adherence in Tour plan' => ['100_adherence_in_tour_plan'],
            '95% MCL Coverage, Visit frequency >90%, 5% Non visited doctors' => ['95_mcl_coverage_visit_frequency_90_5_non_visited_doctors'],
            '1 Camp & 4 Clinics' => ['1_camp_and_4_clinics', '1_camp_4_clinics'],
            'Tab Detailing in doctor calls' => ['tab_detailing_in_doctor_calls'],
            'New Conversions' => ['new_conversions'],
            'MCL productivity' => ['mcl_productivity'],
            'Others mention if any' => ['others_mention_if_any'],
        ];

        // Build a reverse lookup: slug => action plan label
        $slugToLabel = [];
        foreach ($actionPlanItems as $label => $slugs) {
            foreach ($slugs as $slug) {
                $slugToLabel[$slug] = $label;
            }
        }

        foreach ($sbmResponse->data->submissions as $submission) {
            $slug = \Illuminate\Support\Str::slug($submission->{$actionPlanLabel}, '_');
            // Find the matching action plan label for this slug
            $matchedLabel = null;
            foreach ($actionPlanItems as $label => $slugs) {
                if (in_array($slug, $slugs)) {
                    $matchedLabel = $label;
                    break;
                }
            }
            if ($matchedLabel === null) {
                // If not matched, skip this submission
                continue;
            }
            // Set the action plan label to the matched label (the key)
            $submission->{$actionPlanLabel} = $matchedLabel;

            if (isset($submissions[$matchedLabel])) {
                // Merge all parameters (sum numeric values, keep latest for non-numeric)
                foreach ($submission as $key => $value) {
                    if ($key === $actionPlanLabel) {
                        // Already set as matched label, skip
                        continue;
                    }
                    if (isset($submissions[$matchedLabel]->$key)) {
                        // If both are numeric, sum them
                        if (is_numeric($submissions[$matchedLabel]->$key) && is_numeric($value)) {
                            $submissions[$matchedLabel]->$key += $value;
                        } else {
                            // For non-numeric, keep the latest value (from $submission)
                            $submissions[$matchedLabel]->$key = $value;
                        }
                    } else {
                        // If key does not exist, just set it
                        $submissions[$matchedLabel]->$key = $value;
                    }
                }
            } else {
                $submissions[$matchedLabel] = clone $submission;
            }
        }

        $headers = [];

        foreach ($submissions as $obj) {
            $headers = array_unique(array_merge($headers, array_keys((array) $obj)));
        }

        $headers = array_values(array_unique(array_merge(['action_plan_label'], array_diff($headers, ['action_plan_label']))));

        $formattedHeaders = array_map(function ($header) {
            if ($header === 'action_plan_label') {
                return 'SBM /ME Action Plan';
            }
            return ucwords(str_replace('_', ' ', $header));
        }, $headers);

        $exportData = [];
        $exportData[] = $formattedHeaders;

        foreach ($submissions as $obj) {
            $row = [];
            foreach ($headers as $field) {
                $value = isset($obj->$field) ? $obj->$field : 0;
                $row[] = $value === 0 || $value === '0' ? '0' : $value;
            }
            $exportData[] = $row;
        }

        $timestamp = date('d-m-y'); // e.g., 20250814_112530
        $filename = "sbmme_actionplan_report_{$timestamp}.xlsx";
        return Excel::download(new InvoicesExport($exportData), $filename);
    }

    public function exportrmaction(Request $request)
    {
        // Generalised variables for partition, form_id, action plan label, and field names
        $partition = 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e';
        $form_id = '1742297848-159745be-ac42-413b-9aee-31466671011e';
        $actionPlanLabel = 'action_plan_label';

        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active'
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        // Generalised field names for action plan, timeline, and adherence
        $actionPlanField = 'action_plan';
        $timelineField = 'timeline_agreed_for_selected_action_plans_completion';
        $adherenceField = 'adherance_to_action_plan';

        // Generalised variable names for pipeline
        $actionPlanArrayField = 'action_plan_array';
        $actionKeyField = 'actionKey';
        $actionLabelField = 'actionLabel';
        $timelineKeyField = 'timelineKey';
        $adherenceKeyRawField = 'adherenceKeyRaw';
        $adherenceKeyField = 'adherenceKey';

        $reqdataRM = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                [
                    '$match' => $matchConditions,
                ],
                [
                    '$addFields' => [
                        $actionPlanArrayField => [
                            '$let' => [
                                'vars' => ['ap' => '$formdata.' . $actionPlanField],
                                'in' => [
                                    '$cond' => [
                                        ['$eq' => [['$type' => '$$ap'], 'array']],
                                        '$$ap',
                                        [
                                            '$cond' => [['$eq' => [['$type' => '$$ap'], 'string']], ['$split' => ['$$ap', ',']], []],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],
                [
                    '$unwind' => [
                        'path' => '$' . $actionPlanArrayField,
                        'preserveNullAndEmptyArrays' => false,
                    ],
                ],
                // Generalized: No mapping, just use the value as key and label
                [
                    '$addFields' => [
                        $actionKeyField => '$' . $actionPlanArrayField,
                        $actionLabelField => '$' . $actionPlanArrayField,
                    ],
                ],
                [
                    '$addFields' => [
                        $timelineKeyField => ['$toLower' => ['$ifNull' => ['$formdata.' . $timelineField, '']]],
                        $adherenceKeyRawField => ['$toLower' => ['$ifNull' => ['$formdata.' . $adherenceField, '']]],
                    ],
                ],
                [
                    '$addFields' => [
                        $adherenceKeyField => [
                            '$cond' => [['$eq' => ['$' . $adherenceKeyRawField, 'staus_quo']], 'status_quo', '$' . $adherenceKeyRawField],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$' . $actionKeyField,
                        'anyLabel' => ['$first' => '$' . $actionLabelField],
                        'total' => ['$sum' => 1],
                        'less_than_1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, 'less_than_1_month']], 1, 0]]],
                        '1_month' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '1_month']], 1, 0]]],
                        '3_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '3_month']], 1, 0]]],
                        '6_months' => ['$sum' => ['$cond' => [['$eq' => ['$' . $timelineKeyField, '6_month']], 1, 0]]],
                        'on_track' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'on_track']], 1, 0]]],
                        'needs_correction' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'needs_correction']], 1, 0]]],
                        'status_quo' => ['$sum' => ['$cond' => [['$eq' => ['$' . $adherenceKeyField, 'status_quo']], 1, 0]]],
                    ],
                ],
                [
                    '$project' => array_merge([
                        '_id' => 0,
                        $actionPlanLabel => '$anyLabel',
                        'total' => 1,
                        'less_than_1_month' => 1,
                        '1_month' => 1,
                        '3_months' => 1,
                        '6_months' => 1,
                        'on_track' => 1,
                        'needs_correction' => 1,
                        'status_quo' => 1,
                    ]),
                ],
                [
                    '$sort' => ['total' => -1],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $reqdataRM);

        $submissions = [];
        $actionPlanItems = [
            'Tour Plan adherence of Team' => ['tour_plan_adherence_of_team'],
            '100% Spread of performance' => ['100_spread_of_performance'],
            '100% RM Region Target achievement' => ['100_rm_region_target_achievement'],
            'SOP at Mandate Brand performance' => ['sop_at_mandate_brand_performance'],
            'RM Mandate Target Achievement' => ['rm_mandate_target_achievement'],
            'SOP at NI Brand performance' => ['sop_at_ni_brand_performance'],
            'RM NI Target Achievement' => ['rm_ni_target_achievement'],
            'Stretch working with SBMs' => ['stretch_working_with_sbms'],
            'Critical Geography time allocation' => ['critical_geography_time_allocation'],
            'Critical geography Revival' => ['critical_geography_revival'],
            'Covering all Sponsored doctors' => ['covering_all_sponsored_doctors'],
            'Ensuring equal spread at all doctors' => ['ensuring_equal_spread_at_all_doctors'],
            'Ensuring 1:5 times ROI from Sponsored doctors' => ['ensuring_1_5_times_roi_from_sponsored_doctors'],
            'Others' => ['others'],
        ];

        // Build a reverse lookup: slug => action plan label
        $slugToLabel = [];
        foreach ($actionPlanItems as $label => $slugs) {
            foreach ($slugs as $slug) {
                $slugToLabel[$slug] = $label;
            }
        }

        foreach ($sbmResponse->data->submissions as $submission) {
            $slug = \Illuminate\Support\Str::slug($submission->{$actionPlanLabel}, '_');
            // Find the matching action plan label for this slug
            $matchedLabel = null;
            foreach ($actionPlanItems as $label => $slugs) {
                if (in_array($slug, $slugs)) {
                    $matchedLabel = $label;
                    break;
                }
            }
            if ($matchedLabel === null) {
                // If not matched, skip this submission
                continue;
            }
            // Set the action plan label to the matched label (the key)
            $submission->{$actionPlanLabel} = $matchedLabel;

            if (isset($submissions[$matchedLabel])) {
                // Merge all parameters (sum numeric values, keep latest for non-numeric)
                foreach ($submission as $key => $value) {
                    if ($key === $actionPlanLabel) {
                        // Already set as matched label, skip
                        continue;
                    }
                    if (isset($submissions[$matchedLabel]->$key)) {
                        // If both are numeric, sum them
                        if (is_numeric($submissions[$matchedLabel]->$key) && is_numeric($value)) {
                            $submissions[$matchedLabel]->$key += $value;
                        } else {
                            // For non-numeric, keep the latest value (from $submission)
                            $submissions[$matchedLabel]->$key = $value;
                        }
                    } else {
                        // If key does not exist, just set it
                        $submissions[$matchedLabel]->$key = $value;
                    }
                }
            } else {
                $submissions[$matchedLabel] = clone $submission;
            }
        }

        $headers = [];

        $firstObj = reset($submissions);
        $headers = array_keys((array) $firstObj);

        $headers = array_diff($headers, ['action_plan_label']);

        $headers = array_merge(['action_plan_label'], $headers);

        $formattedHeaders = array_map(function ($header) {
            if ($header === 'action_plan_label') {
                return 'RM Action Plan';
            }
            return ucwords(str_replace('_', ' ', $header));
        }, $headers);

        $exportData = [];
        $exportData[] = $formattedHeaders;
        foreach ($submissions as $label => $obj) {
            $row = [];
            foreach ($headers as $field) {
                if ($field === 'action_plan_label') {
                    $row[] = $label;
                } else {
                    $value = isset($obj->$field) ? $obj->$field : 0;
                    $row[] = $value === 0 || $value === '0' ? '0' : $value;
                }
            }
            $exportData[] = $row;
        }

        $timestamp = date('d-m-y'); 
        $filename = "rm_actionplan_report_{$timestamp}.xlsx";
        return Excel::download(new InvoicesExport($exportData), $filename);
    }

    public function exportsbmgoals(Request $request)
    {
        $partition = 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58';
        $form_id = '1742292203-0153c37f-1433-446a-a059-de3597eaca58';

        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active',
            'is_deleted' => ['$ne' => true]
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $sbmCondition = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                // 1) Slice
                [
                    '$match' => $matchConditions,
                ],
                // 2) Build record_month from record_date → formdata.record_date (string/date) → createdAt
                [
                    '$addFields' => [
                        '_m_from_record_date' => [
                            '$cond' => [['$eq' => [['$type' => '$record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$record_date']], 0, 7]], null],
                        ],
                        '_m_from_formdata_str' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'string']], ['$substr' => ['$formdata.record_date', 0, 7]], null],
                        ],
                        '_m_from_formdata_date' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$formdata.record_date']], 0, 7]], null],
                        ],
                        '_m_from_createdAt' => [
                            '$cond' => [['$eq' => [['$type' => '$createdAt'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$createdAt']], 0, 7]], null],
                        ],
                    ],
                ],
                [
                    '$addFields' => [
                        'record_month' => [
                            '$ifNull' => [
                                '$_m_from_record_date',
                                [
                                    '$ifNull' => [
                                        '$_m_from_formdata_str',
                                        [
                                            '$ifNull' => ['$_m_from_formdata_date', '$_m_from_createdAt'],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 3) Fan out goals
                [
                    '$project' => [
                        'record_month' => 1,
                        'user_id' => 1,
                        'goals' => [
                            [
                                'goal_name' => 'Tour Plan Adherence',
                                'goal_value' => '$formdata.proficiency-goal_1_tour_plan_adherance',
                            ],
                            [
                                'goal_name' => 'Basic Working',
                                'goal_value' => '$formdata.proficiency-goal_2_basic_working',
                            ],
                            [
                                'goal_name' => 'Activations',
                                'goal_value' => '$formdata.proficiency-goal_3_activations',
                            ],
                            [
                                'goal_name' => 'In-clinic Effectiveness',
                                'goal_value' => '$formdata.proficiency-goal_4_in_clinic_effectiveness',
                            ],
                            [
                                'goal_name' => 'Spread of Performance',
                                'goal_value' => '$formdata.proficiency-goal_5_spread_of_performance',
                            ],
                        ],
                    ],
                ],
                ['$unwind' => '$goals'],
                [
                    '$match' => [
                        'record_month' => ['$ne' => null],
                        'goals.goal_value' => ['$ne' => null],
                    ],
                ],

                // 4) NORMALIZE labels → Proficient / Competent / Intermediate / Beginner
                //    Works for values like "A - Proficient", "B - Competent", "C ...", "D ..." etc.
                [
                    '$addFields' => [
                        'goals.goal_value_norm' => [
                            '$switch' => [
                                'branches' => [['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'A']], 'then' => 'Proficient'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'B']], 'then' => 'Competent'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'C']], 'then' => 'Intermediate'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'D']], 'then' => 'Beginner']],
                                // if already a clean label or unexpected, keep original
                                'default' => '$goals.goal_value',
                            ],
                        ],
                    ],
                ],

                // 5) Count unique users per month+goal+normalized_label
                [
                    '$group' => [
                        '_id' => [
                            'month' => '$record_month',
                            'goal_name' => '$goals.goal_name',
                            'goal_value' => '$goals.goal_value_norm',
                        ],
                        'users' => ['$addToSet' => '$user_id'],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'month' => '$_id.month',
                        'goal_name' => '$_id.goal_name',
                        'goal_value' => '$_id.goal_value',
                        'user_count' => ['$size' => '$users'],
                    ],
                ],

                // 6) Nest by goal within month
                [
                    '$group' => [
                        '_id' => ['month' => '$month', 'goal_name' => '$goal_name'],
                        'values' => [
                            '$push' => [
                                'goal_value' => '$goal_value',
                                'user_count' => '$user_count',
                            ],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$_id.month',
                        'goals' => [
                            '$push' => [
                                'k' => '$_id.goal_name',
                                'v' => '$values',
                            ],
                        ],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'k' => '$_id',
                        'v' => [
                            '$arrayToObject' => [
                                '$filter' => [
                                    'input' => '$goals',
                                    'as' => 'item',
                                    'cond' => [
                                        '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 7) Collapse to { "YYYY-MM": { Goal 1: [...], Goal 2: [...] }, ... }
                [
                    '$group' => [
                        '_id' => null,
                        'entries' => [
                            '$push' => ['k' => '$k', 'v' => '$v'],
                        ],
                    ],
                ],
                [
                    '$replaceRoot' => [
                        'newRoot' => ['$arrayToObject' => '$entries'],
                    ],
                ],
            ],
        ];

        $sbmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $sbmCondition);
        $submissions = $sbmResponse->data->submissions;
        $goalsList = ['Tour Plan Adherence', 'Basic Working', 'Activations', 'In-clinic Effectiveness', 'Spread of Performance'];
        $goalValues = ['Proficient', 'Competent', 'Intermediate', 'Beginner'];

        $allMonths = [];
        foreach ($submissions as $submissionObj) {
            foreach ($submissionObj as $month => $goalsObj) {
                $allMonths[] = $month;
            }
        }
        $allMonths = array_unique($allMonths);

        usort($allMonths, function ($a, $b) {
            return strtotime($a) <=> strtotime($b);
        });

        $headerRow1 = ['SBM/ME Goals'];
        $headerRow2 = [''];

        foreach ($allMonths as $month) {
            $formattedMonth = Carbon::createFromFormat('Y-m', $month)->format('M-Y');

            $headerRow1 = array_merge($headerRow1, array_fill(0, count($goalValues), $formattedMonth));
            $headerRow2 = array_merge($headerRow2, $goalValues);
        }

        $data = [];
        $data[] = $headerRow1;
        $data[] = $headerRow2;

        foreach ($goalsList as $goal) {
            $row = [$goal];

            foreach ($allMonths as $month) {
                $counts = array_fill_keys($goalValues, 0);

                foreach ($submissions as $submissionObj) {
                    if (isset($submissionObj->{$month}) && isset($submissionObj->{$month}->{$goal})) {
                        foreach ($submissionObj->{$month}->{$goal} as $item) {
                            $counts[$item->goal_value] = $item->user_count;
                        }
                        break;
                    }
                }

                foreach ($goalValues as $gv) {
                    $value = isset($counts[$gv]) ? $counts[$gv] : 0;
                    $row[] = $value === 0 || $value === '0' ? '0' : $value;
                }
            }

            $data[] = $row;
        }

        $timestamp = date('d-m-y'); 
        $filename = "sbm_goalproficiency_report_{$timestamp}.xlsx";
        return Excel::download(new \App\Exports\InvoicesExport($data), $filename);
    }

    public function exportrmgoals(Request $request)
    {
        $partition = 'rapport-pulmocare-1742297848-159745be-ac42-413b-9aee-31466671011e';
        $form_id = '1742297848-159745be-ac42-413b-9aee-31466671011e';

        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active',
            'is_deleted' => ['$ne' => true]
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $rmCondition = [
            'partition' => $partition,
            'form_id' => $form_id,
            'pipeline' => [
                // 1) Slice
                [
                    '$match' => $matchConditions,
                ],

                // 2) Build record_month from record_date → formdata.record_date (string/date) → createdAt
                [
                    '$addFields' => [
                        '_m_from_record_date' => [
                            '$cond' => [['$eq' => [['$type' => '$record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$record_date']], 0, 7]], null],
                        ],
                        '_m_from_formdata_str' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'string']], ['$substr' => ['$formdata.record_date', 0, 7]], null],
                        ],
                        '_m_from_formdata_date' => [
                            '$cond' => [['$eq' => [['$type' => '$formdata.record_date'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$formdata.record_date']], 0, 7]], null],
                        ],
                        '_m_from_createdAt' => [
                            '$cond' => [['$eq' => [['$type' => '$createdAt'], 'date']], ['$substr' => [['$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$createdAt']], 0, 7]], null],
                        ],
                    ],
                ],
                [
                    '$addFields' => [
                        'record_month' => [
                            '$ifNull' => [
                                '$_m_from_record_date',
                                [
                                    '$ifNull' => [
                                        '$_m_from_formdata_str',
                                        [
                                            '$ifNull' => ['$_m_from_formdata_date', '$_m_from_createdAt'],
                                        ],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 3) Fan out goals
                [
                    '$project' => [
                        'record_month' => 1,
                        'user_id' => 1,
                        'goals' => [
                            [
                                'goal_name' => 'Tour Plan Adherence Self & Team',
                                'goal_value' => '$formdata.proficiency-goal_1_tour_plan_adherence_self_and_team',
                            ],
                            [
                                'goal_name' => 'Spread of Performance',
                                'goal_value' => '$formdata.proficiency-goal_2_spread_of_performance',
                            ],
                            [
                                'goal_name' => 'Joint Field Work',
                                'goal_value' => '$formdata.proficiency-goal_3_joint_field_work',
                            ],
                            [
                                'goal_name' => 'KOL Engagement & ROI',
                                'goal_value' => '$formdata.proficiency-goal_4_kol_engagement_and_roi',
                            ],
                            // [
                            //     'goal_name'  => 'Spread of Performance',
                            //     'goal_value' => '$formdata.proficiency-goal_5_spread_of_performance',
                            // ],
                        ],
                    ],
                ],
                ['$unwind' => '$goals'],
                [
                    '$match' => [
                        'record_month' => ['$ne' => null],
                        'goals.goal_value' => ['$ne' => null],
                    ],
                ],

                // 4) NORMALIZE labels → Proficient / Competent / Intermediate / Beginner
                //    Works for values like "A - Proficient", "B - Competent", "C ...", "D ..." etc.
                [
                    '$addFields' => [
                        'goals.goal_value_norm' => [
                            '$switch' => [
                                'branches' => [['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'A']], 'then' => 'Proficient'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'B']], 'then' => 'Competent'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'C']], 'then' => 'Intermediate'], ['case' => ['$eq' => [['$substr' => ['$goals.goal_value', 0, 1]], 'D']], 'then' => 'Beginner']],
                                // if already a clean label or unexpected, keep original
                                'default' => '$goals.goal_value',
                            ],
                        ],
                    ],
                ],

                // 5) Count unique users per month+goal+normalized_label
                [
                    '$group' => [
                        '_id' => [
                            'month' => '$record_month',
                            'goal_name' => '$goals.goal_name',
                            'goal_value' => '$goals.goal_value_norm',
                        ],
                        'users' => ['$addToSet' => '$user_id'],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'month' => '$_id.month',
                        'goal_name' => '$_id.goal_name',
                        'goal_value' => '$_id.goal_value',
                        'user_count' => ['$size' => '$users'],
                    ],
                ],

                // 6) Nest by goal within month
                [
                    '$group' => [
                        '_id' => ['month' => '$month', 'goal_name' => '$goal_name'],
                        'values' => [
                            '$push' => [
                                'goal_value' => '$goal_value',
                                'user_count' => '$user_count',
                            ],
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$_id.month',
                        'goals' => [
                            '$push' => [
                                'k' => '$_id.goal_name',
                                'v' => '$values',
                            ],
                        ],
                    ],
                ],
                [
                    '$project' => [
                        '_id' => 0,
                        'k' => '$_id',
                        'v' => [
                            '$arrayToObject' => [
                                '$filter' => [
                                    'input' => '$goals',
                                    'as' => 'item',
                                    'cond' => [
                                        '$and' => [['$ne' => ['$$item.k', null]], ['$ne' => ['$$item.v', null]]],
                                    ],
                                ],
                            ],
                        ],
                    ],
                ],

                // 7) Collapse to { "YYYY-MM": { Goal 1: [...], Goal 2: [...] }, ... }
                [
                    '$group' => [
                        '_id' => null,
                        'entries' => [
                            '$push' => ['k' => '$k', 'v' => '$v'],
                        ],
                    ],
                ],
                [
                    '$replaceRoot' => [
                        'newRoot' => ['$arrayToObject' => '$entries'],
                    ],
                ],
            ],
        ];
        $rmResponse = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $rmCondition);
        $goalsList = ['Tour Plan Adherence Self & Team', 'Spread of Performance', 'Joint Field Work', 'KOL Engagement & ROI'];
        $goalValues = ['Proficient', 'Competent', 'Intermediate', 'Beginner'];

        // echo '<pre>';
        // print_r($rmResponse);
        // exit();
        // echo '<pre>';
        // print_r($goalsList);
        // exit();

        $submissions = $rmResponse->data->submissions;
        $allMonths = [];
        foreach ($submissions as $submissionObj) {
            foreach ($submissionObj as $month => $goalsObj) {
                $allMonths[] = $month;
            }
        }
        $allMonths = array_unique($allMonths);

        usort($allMonths, function ($a, $b) {
            return strtotime($a) <=> strtotime($b);
        });

        $headerRow1 = ['RM Goals'];
        $headerRow2 = [''];

        foreach ($allMonths as $month) {
            $formattedMonth = Carbon::createFromFormat('Y-m', $month)->format('M-Y');

            $headerRow1 = array_merge($headerRow1, array_fill(0, count($goalValues), $formattedMonth));
            $headerRow2 = array_merge($headerRow2, $goalValues);
        }

        $data = [];
        $data[] = $headerRow1;
        $data[] = $headerRow2;

        foreach ($goalsList as $goal) {
            $row = [$goal];

            foreach ($allMonths as $month) {
                $counts = array_fill_keys($goalValues, 0);

                foreach ($submissions as $submissionObj) {
                    if (isset($submissionObj->{$month}) && isset($submissionObj->{$month}->{$goal})) {
                        foreach ($submissionObj->{$month}->{$goal} as $item) {
                            $counts[$item->goal_value] = $item->user_count;
                        }
                        break;
                    }
                }

                foreach ($goalValues as $gv) {
                    $value = isset($counts[$gv]) ? $counts[$gv] : 0;
                    $row[] = $value === 0 || $value === '0' ? '0' : $value;
                }
            }

            $data[] = $row;
        }

        $timestamp = date('d-m-y'); 
        $filename = "rm_goalproficiency_report_{$timestamp}.xlsx";

        return Excel::download(new \App\Exports\InvoicesExport($data), $filename);
    }

    public function coacheereport()
    {
        $partition = 'rapport-pulmocare-1742292203-0153c37f-1433-446a-a059-de3597eaca58';
        $form_id = '1742292203-0153c37f-1433-446a-a059-de3597eaca58';

        $permissions = json_decode(Auth::user()->permissions);
        $matchConditions = [
            'partition' => $partition,
            'form_id' => $form_id,
            'status' => 'active',
        ];
        
        $allhierarchy = [];
        if(!in_array('all_data_access', $permissions)){
            $hierarchyResponse = LambdaRequest::signedrequest('get', 'ums', 'hierarchy/hierarchy_below_me', []);
            foreach($hierarchyResponse->data->hierarchies as $hier){
                $allhierarchy[] = $hier->id;
            }
            $matchConditions['hierarchy_id'] = ['$in' => $allhierarchy];
        }

        $reqdataCoache = [
                'partition' => $partition,
                'form_id' => $form_id,
                'pipeline' => [
                    [
                        '$match' => $matchConditions,
                    ],
                    [
                        '$project' => [
                            '_id' => 0,
                            'user_id' => '$user_id',
                            'status' => '$status',
                            'select_month' => '$formdata.select_month',
                            'adherance_to_action_plan' => '$formdata.adherance_to_action_plan',
                            'user_name' => '$user_info.name',
                            'record_date' => '$formdata.record_date',
                            'select_role' => '$formdata.select_role',
                            'createdAt' => '$createdAt',
                        ],
                    ],
                    [
                        '$match' => [
                            'adherance_to_action_plan' => ['$exists' => true, '$ne' => null],
                        ],
                    ],
                    // sort by user + month + createdAt descending (latest first)
                    [
                        '$sort' => [
                            'user_id' => 1,
                            'select_month' => 1,
                            'createdAt' => -1,
                        ],
                    ],
                    // group by user + month + adherence, keep latest record
                    [
                        '$group' => [
                            '_id' => [
                                'user_id' => '$user_id',
                                'select_month' => '$select_month',
                                'adherance_to_action_plan' => '$adherance_to_action_plan',
                            ],
                            'user_id' => ['$first' => '$user_id'],
                            'status' => ['$first' => '$status'],
                            'select_month' => ['$first' => '$select_month'],
                            'adherance_to_action_plan' => ['$first' => '$adherance_to_action_plan'],
                            'user_name' => ['$first' => '$user_name'],
                            'record_date' => ['$first' => '$record_date'],
                            'select_role' => ['$first' => '$select_role'],
                            'createdAt' => ['$first' => '$createdAt'],
                        ],
                    ],
                    // optional: sort final results
                    [
                        '$sort' => [
                            'user_id' => 1,
                            'select_month' => 1,
                        ],
                    ],
                ],
        ];

        $response = LambdaRequest::signedrequest('post', $this->_service, $this->_module . '/submission_aggregation', $reqdataCoache);

        echo '<pre>';
        print_r($response);
        exit();

        $groupedData = [];
        foreach ($response->data->submissions as $submission) {
            $userId = $submission->user_id;

            if (!isset($groupedData[$userId])) {
                $groupedData[$userId] = [
                    'user_name' => $submission->user_name,
                    'select_role' => $submission->select_role,
                    'months' => [],
                ];
            }

            $monthMapping = [
                'June 25' => "Jun'25",
                'July 25' => "Jul'25",
                'August 25' => "Aug'25",
                'September 25' => "Sep'25",
            ];

            $headerMonth = $monthMapping[$submission->select_month] ?? $submission->select_month;
            $groupedData[$userId]['months'][$headerMonth] = $submission->adherance_to_action_plan;
        }

        return $this->view('cochee')->with([
            'title' => 'Cochee',
            'response' => $groupedData,
            'base_url' => $this->_base_url,
            'buttons' => $this->get_list_buttons(),
        ]);
    }
}