<?php

namespace App\Http\Controllers;

use App\Exports\JobsExport;
use App\Models\AddJobsColumn;
use Illuminate\Support\Facades\Log;
use Illuminate\Http\Request;
use App\Models\JobFields;
use App\Models\TemplateCheckList;
use App\Models\Chat;
use App\Models\Templates;
use App\Models\Categories;
use App\Models\UniqueJob;
use App\Models\HopReviewPage;
use App\Repositories\ResponseRepository;
use Maatwebsite\Excel\Facades\Excel;

class JobFieldsCOntroller extends Controller
{
    public function __construct(ResponseRepository $response)
    {
        $this->response = $response;
        $this->storeRules = [
            'projects_id' => 'required',
            'field_name' => 'required',
            'field_types_id' => 'required',
            //'active_status' => 'required',

        ];
        $this->storeRulesChats = [
            'template_check_list_id' => 'required',
            'ueser_create_id' => 'required',
            'chat_description' => 'required',
            //'active_status' => 'required',

        ];
    }



    public function indexJobFields()
    {
        return $this->response->jsonResponse(false, " JobFields Details Fetched Successfully", JobFields::with('projectlist', 'fieldtypelist','uniqueJoblist')->orderBy('job_fields_id')->get(), 300);
    }

    public function indexActiveJobFields()
    {
        return $this->response->jsonResponse(false, " JobFields Details Fetched Successfully", JobFields::where('active_status', 1)->with('projectlist', 'fieldtypelist')->orderBy('job_fields_id',)->get(), 300);
    }



    public function indexAddJobs()
    {
        return $this->response->jsonResponse(false, " Addd Jobs Details Fetched Successfully", AddJobsColumn::orderBy('id', 'desc')->get(), 300);

    }
    // public function indexAddJobs($projects_id)
    // {
    //     // return $this->response->jsonResponse(false, " Addd Jobs Details Fetched Successfully", AddJobsColumn::orderBy('id', 'desc')->get(), 300);
    //     return $this->response->jsonResponse(false, 'Addd Jobs Fetched Successfully', AddJobsColumn::where('projects_id', $projects_id)->orderBy('id', 'desc')->get(), 100);


    // }
    public function getUploadJobsId($id)
    {
        // return $this->response->jsonResponse(false, " Addd Jobs Details Fetched Successfully", AddJobsColumn::orderBy('id', 'desc')->get(), 300);
        return $this->response->jsonResponse(false, 'Jobs Id Details Fetched Successfully', AddJobsColumn::where('id', $id)->orderBy('id', 'desc')->get(), 100);

    }

    // public function getJobId($job_id)
    // {
    //     $result = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")->orderBy('id', 'desc')->get();

    //     return response()->json([
    //         'success' => true,
    //         'message' => 'Job ID Details Fetched Successfully',
    //         'data' => $result
    //     ], 200);
    // }

    // public function getJobId($job_id)
    // {
    //     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")->get();
    //     $filteredData = [];
    //     foreach ($allData as $data) {
    //         $properties = $data->properties[0];
    //         foreach ($properties as $property) {
    //             if ($property['job_id'] == $job_id) {
    //                 $filteredData[] = $property;
    //                 break; // Stop after finding the matching job_id
    //             }
    //         }
    //     }
    //     return response()->json([
    //         'success' => true,
    //         'message' => 'Job ID Details Fetched Successfully',
    //         'data' => $filteredData
    //     ], 200);
    // }

    // public function getJobId($project_name, $job_id)
    // {
    //     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
    //         ->with('templateChecklists')
    //         ->get();

    //     $filteredData = [];
    //     foreach ($allData as $data) {
    //         $properties = $data->properties[0];
    //         foreach ($properties as $property) {
    //             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
    //                 $filteredData[] = $property;
    //                 break; // Stop after finding the matching job_id and project_name
    //             }
    //         }
    //     }

    //     return response()->json([
    //         'success' => true,
    //         'message' => 'Job ID Details Fetched Successfully',
    //         'data' => $filteredData
    //     ], 200);
    // }

    // public function getJobId($project_name, $job_id)
    // {
    //     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
    //         ->with('templateChecklists')
    //         ->get();

    //     $filteredData = [];
    //     foreach ($allData as $data) {
    //         $properties = $data->properties[0];
    //         foreach ($properties as $property) {
    //             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
    //                 $templateChecklistData = TemplateCheckList::where('template_id', $property['templates_id'])->get();
    //                 $property['template_checklists'] = $templateChecklistData;
    //                 $filteredData[] = $property;
    //                 break; // Stop after finding the matching job_id and project_name
    //             }
    //         }
    //     }

    //     return response()->json([
    //         'success' => true,
    //         'message' => 'Job ID Details Fetched Successfully',
    //         'data' => $filteredData
    //     ], 200);
    // }

//     public function getJobId($project_name, $job_id)
// {
//     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
//         ->with('templateChecklists')
//         ->get();

//     $filteredData = [];
//     foreach ($allData as $data) {
//         $properties = $data->properties[0];
//         foreach ($properties as $property) {
//             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
//                 $templateId = $property['templates_id'];
//                 $template = Templates::find($templateId);

//                 if ($template) {
//                     $templateChecklistData = TemplateCheckList::where('template_id', $templateId)->get();
//                     $property['template_checklists'] = $templateChecklistData;
//                     $property['template_details'] = $template; // Add template data to the property
//                     $filteredData[] = $property;
//                 }
//                 break; // Stop after finding the matching job_id and project_name
//             }
//         }
//     }

//     return response()->json([
//         'success' => true,
//         'message' => 'Job ID Details Fetched Successfully',
//         'data' => $filteredData
//     ], 200);
// }

// public function getJobId($project_name, $job_id)
// {
//     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
//         ->with('templateChecklists')
//         ->get();
// log::info($allData);
//     $filteredData = [];
//     foreach ($allData as $data) {
//         $properties = $data->properties[0];
//         log::info($properties);

//         foreach ($properties as $property) {
//             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
//                 $templateId = $property['templates_id'];

//                 $template = Templates::find($templateId);

//                 if ($template) {
//                     // Process categories_id and categories_name into an array
//                     $categoriesIdArray = explode(',', $template->categories_id);
//                     $categoriesNameArray = explode(',', $template->categories_name);
//                     $categoriesData = [];
//                     foreach ($categoriesIdArray as $index => $categoryId) {
//                         $categoriesData[] = [
//                             'categories_id' => $categoryId,
//                             'categories_name' => $categoriesNameArray[$index]
//                         ];
//                     }

//                     $templateChecklistData = TemplateCheckList::with('hopReviewList')->where('template_id', $templateId)->get();
//                     $property['template_checklists'] = $templateChecklistData;
//                     $property['template_details']['categories'] = $categoriesData; // Add categories data to the template
//                     $filteredData[] = $property;
//                     log::info($filteredData);

//                 }
//                 break; // Stop after finding the matching job_id and project_name
//             }
//         }
//     }

//     return response()->json([
//         'success' => true,
//         'message' => 'Job ID Details Fetched Successfully',
//         'data' => $filteredData

//     ], 200);
// }

public function getJobId($project_name, $job_id)
{
    // log::info($project_name);
    // $jobsdata = HopReviewPage::where('job_id', $job_id)->get();

    $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
        ->with('templateChecklists')
        ->get();
        log::info($allData);
    $filteredData = [];

    foreach ($allData as $data) {
        foreach ($data->properties[0] as $property) {
            // log::info($property);
            if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
                $templateId = $property['templates_id'];
                $job_id = $property['job_id'];
                log::info($property);

                $template = Templates::find($templateId);
                if ($template) {
                    // Process categories_id and categories_name into an array
                    $categoriesIdArray = explode(',', $template->categories_id);
                    $categoriesNameArray = explode(',', $template->categories_name);
                    $categoriesData = [];
                    foreach ($categoriesIdArray as $index => $categoryId) {
                        $categoriesData[] = [
                            'categories_id' => $categoryId,
                            'categories_name' => $categoriesNameArray[$index]
                        ];
                    }
                    $project_id = $data->project_id;
                    // $templateChecklistData = TemplateCheckList::with('hopReviewList')->where('template_id', $templateId)->get();
                    $templateChecklistData = TemplateCheckList::with(['hopReviewList' => function($query) use ($job_id, $project_id, $templateId) {
                        $query->where('job_id', $job_id)
                              ->where('project_id', $project_id)
                              ->where('templates_id', $templateId);
                    }])->where('template_id', $templateId)->get();

                    $jobsdata = HopReviewPage::where('job_id', $job_id)->where('project_id', $project_id)->get();
                    // log::info($jobsdata);


                    $property['template_checklists'] = $templateChecklistData;
                    $property['template_details']['categories'] = $categoriesData; // Add categories data to the template
                    $property['jobsdata'] = $jobsdata;


                    // Add project_id to the property
                    $property['project_id'] = $data->project_id;


                    $filteredData[] = $property;
                }
                break; // Stop after finding the matching job_id and project_name
            }
        }
    }



    return response()->json([
        'success' => true,
        'message' => 'Job ID Details Fetched Successfully',
        'data' => $filteredData
    ], 200);
}

public function getJobHopDetailsIdTwo($project_id, $job_id, $ueser_create_id) {
    $hopReviewData = HopReviewPage::where('project_id', $project_id)
        ->where('job_id', $job_id)
        ->where(function ($query) use ($ueser_create_id) {
            $query->where('submitter', $ueser_create_id)
                  ->orWhere('reviewer', $ueser_create_id);
        })
        ->orderBy('hop_review_id', 'desc')->with('reviewerNameList','submiterNameList','templateChecklistList','submiterimage','reviewerImage','reAssignedImage')
        ->get();

        $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
        ->with('templateChecklists')
        ->get();
        // log::info($allData);
        $project_name = $allData->project_name;
        // log::info($project_name);

        $filteredData = [];

        foreach ($allData as $data) {
            foreach ($data->properties[0] as $property) {
                if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
                    $templateId = $property['templates_id'];

                    $template = Templates::find($templateId);

                    if ($template) {
                        // Process categories_id and categories_name into an array
                        $categoriesIdArray = explode(',', $template->categories_id);
                        $categoriesNameArray = explode(',', $template->categories_name);
                        $categoriesData = [];
                        foreach ($categoriesIdArray as $index => $categoryId) {
                            $categoriesData[] = [
                                'categories_id' => $categoryId,
                                'categories_name' => $categoriesNameArray[$index]
                            ];
                        }

                        $templateChecklistData = TemplateCheckList::with('hopReviewList')->where('template_id', $templateId)->get();
                        $property['template_checklists'] = $templateChecklistData;
                        $property['template_details']['categories'] = $categoriesData; // Add categories data to the template

                        // Add project_id to the property
                        $property['project_id'] = $data->project_id;

                        $filteredData[] = $property;
                    }
                    break; // Stop after finding the matching job_id and project_name
                }
            }
        }

    return $this->response->jsonResponse(false, 'Data Fetched Successfully', [
        'hop_review_data' => $hopReviewData,
        'another_model_data' => $filteredData
    ], 300);
}

// public function getJobHopDetailsId($ueser_create_id, $project_name, $job_id)
// {
//     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
//         ->with('templateChecklists')
//         ->get();
// log::info($allData);
//     $filteredData = [];

//     foreach ($allData as $data) {
//         foreach ($data->properties[0] as $property) {
//             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
//                 $templateId = $property['templates_id'];

//                 $template = Templates::find($templateId);

//                 if ($template) {
//                     // Process categories_id and categories_name into an array
//                     $categoriesIdArray = explode(',', $template->categories_id);
//                     $categoriesNameArray = explode(',', $template->categories_name);
//                     $categoriesData = [];
//                     foreach ($categoriesIdArray as $index => $categoryId) {
//                         $categoriesData[] = [
//                             'categories_id' => $categoryId,
//                             'categories_name' => $categoriesNameArray[$index]
//                         ];
//                     }

//                     $templateChecklistData = TemplateCheckList::with('hopReviewList')->where('template_id', $templateId)->get();
//                     $property['template_checklists'] = $templateChecklistData;
//                     $property['template_details']['categories'] = $categoriesData; // Add categories data to the template

//                     // Add project_id to the property
//                     $property['project_id'] = $data->project_id;

//                     $filteredData[] = $property;
//                 }
//                 break; // Stop after finding the matching job_id and project_name
//             }
//         }
//     }



//     return response()->json([
//         'success' => true,
//         'message' => 'Job ID Details Fetched Successfully',
//         'data' => $filteredData
//     ], 200);
// }


// public function getJobHopDetailsId($project_id, $job_id) {
//     log::info($project_id);
//     log::info($job_id);

//     return $this->response->jsonResponse(false, 'Hop Review Page Details Fetched Successfully', HopReviewPage::where('project_id', $project_id)->where('job_id', $job_id)->orderBy('hop_review_id', 'desc')->get(), 300);
// }

public function getJobHopDetailsId($project_id, $job_id, $ueser_create_id) {
    return $this->response->jsonResponse(false, 'Hop Review Page Details Fetched Successfully', HopReviewPage::where('project_id', $project_id)
        ->where('job_id', $job_id)
        ->where(function ($query) use ($ueser_create_id) {
            $query->where('submitter', $ueser_create_id)
                  ->orWhere('reviewer', $ueser_create_id);
        })
        ->with('reviewerNameList','submiterNameList','templateChecklistList','submiterimage','chatDetails','uniqueJobDetails')
        ->get(), 300);
}


public function chatStored(Request $request) {
    $validate = $this->response->validate($request->all(), $this->storeRulesChats);
    if($validate === true) {
        return $this->response->jsonResponse(false, $this->response->message('Chat', 'store'), Chat::create($request->all()), 301);
    } else {
        return $validate;
    }
}




// public function getUsersCreateDetailsId($ueser_create_id) {
//     return $this->response->jsonResponse(false, 'Users Create Details Fetched Successfully', UsersCreate::where('ueser_create_id', $ueser_create_id)->with('clientslist','contractorlist')->first(), 201);
// }
public function getJobsId($project_name, $job_id)
{
    $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
        ->with('templateChecklists.hopReviewLists') // Eager load hopReviewLists
        ->get();

    $filteredData = [];

    foreach ($allData as $data) {
        $properties = $data->properties[0];

        foreach ($properties as $property) {
            if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
                $templateId = $property['templates_id'];
                $template = Templates::find($templateId);

                if ($template) {
                    // ... (Your existing code)
                    $categoriesIdArray = explode(',', $template->categories_id);
                    $categoriesNameArray = explode(',', $template->categories_name);
                    $categoriesData = [];
                    foreach ($categoriesIdArray as $index => $categoryId) {
                        $categoriesData[] = [
                            'categories_id' => $categoryId,
                            'categories_name' => $categoriesNameArray[$index]
                        ];
                    }

                    // Retrieve hop review lists
                    $templateChecklistData = TemplateCheckList::with('hopReviewLists')->where('template_id', $templateId)->get();
                    $property['template_checklists'] = $templateChecklistData;

                    if ($property['template_checklists']) {
                        foreach ($property['template_checklists'] as $checklist) {
                            if (isset($checklist['hop_review_lists'])) {
                                // Retrieve reviewer and submitter details for each hop review list
                                foreach ($checklist['hop_review_lists'] as $hopReviewList) {
                                    $hopReviewList->reviewerDetails = $hopReviewList->reviewerDetails()->toArray();
                                    $hopReviewList->submitersDetails = $hopReviewList->submitersDetails()->toArray();
                                }
                            }
                        }

                    }

                    $property['template_details']['categories'] = $categoriesData; // Add categories data to the template
                    $filteredData[] = $property;
                }
                break;
            }
        }
    }

    return response()->json([
        'success' => true,
        'message' => 'Job ID Details Fetched Successfully',
        'data' => $filteredData
    ], 200);
}


//     public function getJobId($project_name, $job_id)
// {
//     $allData = AddJobsColumn::whereRaw("JSON_CONTAINS(properties, '[{\"job_id\": $job_id}]')")
//         ->with('templateChecklists.categorieslist') // Load categorieslist relationship
//         ->get();

//     $filteredData = [];
//     foreach ($allData as $data) {
//         $properties = $data->properties[0];
//         foreach ($properties as $property) {
//             if ($property['job_id'] == $job_id && $property['project_name'] == $project_name) {
//                 $templateChecklistData = TemplateCheckList::where('template_id', $property['templates_id'])
//                     ->with('categorieslist') // Load categorieslist relationship
//                     ->get();
//                 $property['template_checklists'] = $templateChecklistData;
//                 $filteredData[] = $property;
//                 break; // Stop after finding the matching job_id and project_name
//             }
//         }
//     }

//     return response()->json([
//         'success' => true,
//         'message' => 'Job ID Details Fetched Successfully',
//         'data' => $filteredData
//     ], 200);
// }


    // public function getJobCoulumnId($id)
    // {
    //     $data = AddJobsColumn::select('properties')->where('project_id', $id)->get();

    //     $excelData = [];
    //     foreach ($data as $row) {
    //         $propertiesArray = $row->properties;
    //         foreach ($propertiesArray[0] as $key => $object) {

    //             $excelData[] = $object;
    //         }
    //     }
    //     return $this->response->jsonResponse(false, " JobFields Details Fetched Successfully", $excelData, 300);
    // }

    // // original code
    // public function getJobCoulumnId($id)
    // {
    //     $data = AddJobsColumn::select('id', 'properties')->where('project_id', $id)->get();

    //     $excelData = [];
    //     foreach ($data as $row) {
    //         $propertiesArray = $row->properties;
    //         foreach ($propertiesArray[0] as $key => $object) {
    //             $excelData[] = array_merge(['id' => $row->id], (array)$object);
    //         }
    //     }

    //     return $this->response->jsonResponse(false, "JobFields Details Fetched Successfully", $excelData, 300);
    // }

    // this is my customise code
//     public function getJobCoulumnId($id)
// {
//     $data = AddJobsColumn::select('id', 'project_id', 'properties')->where('project_id', $id)->get();
//     log::info($data);
//     $excelData = [];
//     foreach ($data as $row) {
//         $propertiesArray = $row->properties;
//         log::info($propertiesArray);
//         foreach ($propertiesArray[0] as $key => $object) {

//             $excelData[] = array_merge(['id' => $row->id, 'project_id' => $row->project_id], (array)$object);
//         }
//         log::info($excelData);

//     }
//     return $this->response->jsonResponse(false, "JobFields Details Fetched Successfully", $excelData, 300);
// }

public function getJobCoulumnId($id)
{
    $data = AddJobsColumn::select('id', 'project_id', 'properties')->where('project_id', $id)->get();
    log::info($data);

    $excelData = [];

    foreach ($data as $row) {
        $propertiesArray = $row->properties;

        foreach ($propertiesArray[0] as $key => $object) {
            $excelData[] = array_merge(['id' => $row->id, 'project_id' => $row->project_id], (array)$object);
        }
                log::info($excelData);

    }

    return $this->response->jsonResponse(false, "JobFields Details Fetched Successfully", $excelData, 300);
}


    // public function getaddJobsColumnDetailsId($id) {
    //     return $this->response->jsonResponse(false, 'AddJobsColumn Details Fetched Successfully', AddJobsColumn::where('id', $id)->first(), 201);


    // }

    public function getaddJobsColumnDetailsId($id) {
        $addJobsColumn = AddJobsColumn::where('id', $id)->first();

        if (!$addJobsColumn) {
            return $this->response->jsonResponse(true, 'AddJobsColumn not found', null, 404);
        }

        $properties = $addJobsColumn->properties ?? [];

        return $this->response->jsonResponse(false, 'Properties Fetched Successfully', $properties, 200);
    }

    public function getActiveJobFields()
    {
        return $this->response->jsonResponse(false, "JobFields Detail Fetched Successfully",  JobFields::where('active_status', 1)->orderBy('job_fields_id', 'desc')->get(), 300);
    }

    public function store(Request $request)
    {
        $validate = $this->response->validate($request->all(), $this->storeRules);
        if ($validate === true) {
            return $this->response->jsonResponse(false, $this->response->message('JobFields', 'store'), JobFields::create($request->all()), 301);
        } else {
            return $validate;
        }
    }

    public function show($id)
    {
        return $this->response->jsonResponse(false, $this->response->message('JobFields', 'show'), $this->findJobFields($id), 302);
    }

    public function updateJobFields(Request $request)
    {
        $validate = $this->response->validate($request->all(), [
            'projects_id' => 'required',
            'field_name' => 'required',
            'field_types_id' => 'required',
            // 'active_status' => 'required',
        ]);
        if ($validate === true) {
            return $this->response->jsonResponse(false, $this->response->message('JobFields', 'update'), $this->findJobFields($request->job_fields_id)->update($request->all()), 303);
        } else {
            return $validate;
        }
    }

    public function destroy($id)
    {
        $size = $this->findJobFields($id);
        if ($size) {
            return $this->response->jsonResponse(false, $this->response->message('JobFields', 'destroy'), $size->delete(), 304);
        }
        return $this->response->jsonResponse(true, 'JobFields Not Exists', [], 305);
    }

    public function findJobFields($id)
    {
        return JobFields::find($id);
    }

    public function jobFieldsSwitch($id)
    {
        $size = $this->findJobFields($id);
        if ($size) {
            $value = $size->active_status === 1 ? 0 : 1;
            $msg = $value === 0 ? 'Deactivated' : 'Activated';
            return $this->response->jsonResponse(false, 'JobFields ' . $msg . ' Successfully', $size->update(['active_status' => $value]), 306);
        }
        return $this->response->jsonResponse(true, 'JobFields Not Exists', [], 307);
    }

    public function searchJobFields($search)
    {
        if ($search === "null") {
            return $this->response->jsonResponse(false, $this->response->message('JobFields', 'search'), [], 910);
        }
        return $this->response->jsonResponse(false, $this->response->message('JobFields', 'search'), JobFields::with('projectlist', 'fieldtypelist')->where('field_name', 'LIKE', '%' . $search . '%')->get(), 911);
    }

    // public function downloadJobsExcel($project_id)
    // {
    //     return Excel::download(new JobsExport($project_id), 'jobs_excel.xlsx');
    // }
    public function downloadJobsExcel($project_id, $templates_id, $accountId, $clientId)
{
    return Excel::download(new JobsExport($project_id, $templates_id, $accountId, $clientId), 'jobs_excel.xlsx');
}

    // public function upload(Request $request)
    // {
    //     $request->validate([
    //         'file' => 'required|mimes:xlsx,xls',
    //         'projects_id' => [
    //             'required',
    //             'exists:projects,projects_id',
    //         ],
    //     ]);
    //     if ($request->hasFile('file')) {
    //         $file = $request->file('file');
    //         $projectId = $request->projects_id;
    //         $data = Excel::toArray(new JobsExport($projectId), $file);
    //         foreach ($data as $key => $item) {
    //             foreach ($item as $index => $column) {
    //                 $data[$key][$index]['job_id'] = $index +1;
    //             }
    //         }
    //         if (count($data) > 0) {
    //             $existance = AddJobsColumn::where('project_id', $projectId);
    //             if ($existance->exists()) {
    //                 $existance->delete();
    //             }
    //             $jobs = new AddJobsColumn();
    //             $jobs->project_id = $projectId;
    //             $jobs->properties = $data;
    //             $jobs->save();
    //             return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
    //         } else {
    //             return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
    //         }
    //     }
    //     return response()->json(['message' => 'No file provided'], 400);
    // }

//  Correctly Worked
    // public function upload(Request $request)
    // {
    //     $request->validate([
    //         'file' => 'required|mimes:xlsx,xls',
    //         'projects_id' => [
    //             'required',
    //             'exists:projects,projects_id',
    //         ],
    //         'templates_id' => 'required', // Add this validation rule
    //     ]);
    //     log::info($request);

    //     if ($request->hasFile('file')) {
    //         $file = $request->file('file');
    //         $projectId = $request->projects_id;
    //         $templatesId = $request->templates_id; // Get the templates_id from the request
    //         log::info($projectId);
    //         log::info($templatesId);



    //         $data = Excel::toArray(new JobsExport($projectId), $file);
    //         log::info($data);
    //         foreach ($data as $key => $item) {
    //             foreach ($item as $index => $column) {
    //                 $data[$key][$index]['job_id'] = $index + 1;
    //                 $data[$key][$index]['templates_id'] = $templatesId; // Add templates_id
    //             }
    //         }
    //         log::info($data);
    //         if (count($data) > 0) {
    //             $existance = AddJobsColumn::where('project_id', $projectId);
    //             if ($existance->exists()) {
    //                 $existance->delete();
    //             }
    //             $jobs = new AddJobsColumn();
    //             $jobs->project_id = $projectId;
    //             $jobs->properties = $data;
    //             $jobs->save();
    //             return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
    //         } else {
    //             return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
    //         }
    //     }
    //     return response()->json(['message' => 'No file provided'], 400);
    // }

    // public function upload(Request $request)
    // {
    //     $request->validate([
    //         'file' => 'required|mimes:xlsx,xls',
    //         'projects_id' => [
    //             'required',
    //             'exists:projects,projects_id',
    //         ],
    //         'templates_id' => 'required',
    //     ]);

    //     if ($request->hasFile('file')) {
    //         $file = $request->file('file');
    //         $projectId = $request->projects_id;
    //         $templatesId = $request->templates_id;

    //         $data = Excel::toArray(new JobsExport($projectId), $file);

    //         // Extract unique_job_ids from UniqueJob table
    //         $uniqueJobIds = UniqueJob::where('projects_id', $projectId)
    //             ->pluck('unique_job_id')
    //             ->toArray();

    //         $properties = [];

    //         foreach ($data as $key => $item) {
    //             foreach ($item as $index => $column) {
    //                 $column['job_id'] = $index + 1;
    //                 $column['templates_id'] = $templatesId;
    //                 $column['unique_job_id'] = $uniqueJobIds[0] ?? null; // Set the same unique_job_id for all rows
    //                 $properties[] = $column;

    //             }



    //         }

    //         if (count($properties) > 0) {
    //             $existance = AddJobsColumn::where('project_id', $projectId);
    //             if ($existance->exists()) {
    //                 $existance->update(['properties' => $properties]);
    //             } else {
    //                 $jobs = new AddJobsColumn();
    //                 $jobs->project_id = $projectId;
    //                 $jobs->properties = $properties;
    //                 $jobs->save();
    //             }

    //             return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
    //         } else {
    //             return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
    //         }
    //     }

    //     return response()->json(['message' => 'No file provided'], 400);
    // }

//     public function upload(Request $request)
// {
//     $request->validate([
//         'file' => 'required|mimes:xlsx,xls',
//         'projects_id' => [
//             'required',
//             'exists:projects,projects_id',
//         ],
//         'templates_id' => 'required',
//         'account_id' => 'required',
//         'client_id' => 'required',
//     ]);

//     if ($request->hasFile('file')) {
//         $file = $request->file('file');
//         $projectId = $request->projects_id;
//         $templatesId = $request->templates_id;
//         $accountId = $request->projects_id;
//         $clientId = $request->projects_id;

//         $data = Excel::toArray(new JobsExport($projectId, $templatesId, $accountId, $clientId), $file);
//         // log::info($data);


//         // Extract unique_job_ids from UniqueJob table
//         $uniqueJobIds = UniqueJob::where('projects_id', $projectId)->orderBy('created_at','desc')
//             ->pluck('job_fields_id')
//             ->toArray();
//             $uniqueJobFirst = $uniqueJobIds[0];
//             // log::info($uniqueJobFirst);
//             $hlo = strtolower(str_replace(' ', '_', $uniqueJobFirst));
//             // log::info($hlo);

//             $properties = [];

//             foreach ($data as $key => $item) {
//                 foreach ($item as $index => $column) {
//                     $column['job_id'] = $index + 1;
//                     $column['templates_id'] = $templatesId;
//                     $column['job_fields_id'] = $uniqueJobIds[0] ?? null;

//                     // Extract specific values from $column using $hlo as keys
//         $desiredKeys = explode(',', $hlo); // Convert $hlo to array
//         // log::info($desiredKeys);
//         $extractedValues = array_intersect_key($column, array_flip($desiredKeys));

//         $properties[] = $column;
//         // log::info($column);
//         // log::info($properties);

//         // Format the extracted values as a string
//         $formattedValues = '(' . implode(', ', $extractedValues) . ')';
//         $column['unique_job_identifier'] = $formattedValues;
//         log::info($formattedValues);



//                 }
//             }

//         if (count($properties) > 0) {
//             $existance = AddJobsColumn::where('project_id', $projectId);
//             // log::info($existance);
//             if ($existance->exists()) {
//                 $existance->update(['properties' => [$properties]]);
//             } else {
//                 $jobs = new AddJobsColumn();
//                 $jobs->project_id = $projectId;
//                 $jobs->properties = [$properties];

//                 $jobs->save();
//                 log::info($jobs);

//             }

//             return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
//         } else {
//             return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
//         }
//     }

//     return response()->json(['message' => 'No file provided'], 400);
// }

public function upload(Request $request)
{
    $request->validate([
        'file' => 'required|mimes:xlsx,xls',
        'projects_id' => [
            'required',
            'exists:projects,projects_id',
        ],
        'templates_id' => 'required',
        'account_id' => 'required',
        'client_id' => 'required',
    ]);

    if ($request->hasFile('file')) {
        $file = $request->file('file');
        $projectId = $request->projects_id;
        $templatesId = $request->templates_id;
        $accountId = $request->projects_id;
        $clientId = $request->projects_id;

        $data = Excel::toArray(new JobsExport($projectId, $templatesId, $accountId, $clientId), $file);
        log::info($data);

        // Extract unique_job_ids from UniqueJob table
        $uniqueJobIds = UniqueJob::where('projects_id', $projectId)
            ->orderBy('created_at', 'desc')
            ->pluck('job_fields_id')
            ->toArray();

        $uniqueJobFirst = $uniqueJobIds[0];
        $hlo = strtolower(str_replace(' ', '_', $uniqueJobFirst));
        log::info($hlo);

        $properties = [];

        foreach ($data as $key => $item) {
            foreach ($item as $index => $column) {
                $column['job_id'] = $index + 1;
                $column['templates_id'] = $templatesId;

                // Extract specific values from $column using $hlo as keys
                $desiredKeys = explode(',', $hlo); // Convert $hlo to array
                $extractedValues = array_intersect_key($column, array_flip($desiredKeys));
                log::info($extractedValues);

                // Format the extracted values as a string
                $formattedValues = implode('-', $extractedValues);
                log::info($formattedValues);
                $column['unique_job_identifier'] = $formattedValues;

                $properties[] = $column;
                log::info($column);

            }
        }

        if (count($properties) > 0) {
            $existance = AddJobsColumn::where('project_id', $projectId);

            if ($existance->exists()) {
                $existance->update(['properties' => [$properties]]);
            } else {
                $jobs = new AddJobsColumn();
                $jobs->project_id = $projectId;
                $jobs->properties = [$properties];
                $jobs->save();
                // log::info($jobs);

            }

            return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
        } else {
            return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
        }
    }

    return response()->json(['message' => 'No file provided'], 400);
}








    // public function upload(Request $request)
    // {
    //     $request->validate([
    //         'file' => 'required|mimes:xlsx,xls',
    //         'projects_id' => [
    //             'required',
    //             'exists:projects,projects_id',
    //         ],
    //         'templates_id' => [
    //             'required', // Validate templates_id
    //         ],
    //     ]);
    //     if ($request->hasFile('file')) {
    //         $file = $request->file('file');
    //         $projectId = $request->projects_id;
    //         $templatesId = $request->templates_id; // Get templates_id from request

    //         $data = Excel::toArray(new JobsExport($projectId), $file);
    //         foreach ($data as $key => $item) {
    //             foreach ($item as $index => $column) {
    //                 $data[$key][$index]['job_id'] = $index +1;
    //             }
    //         }
    //         if (count($data) > 0) {
    //             $existance = AddJobsColumn::where('project_id', $projectId);
    //             if ($existance->exists()) {
    //                 $existance->delete();
    //             }
    //             $jobs = new AddJobsColumn();
    //             $jobs->project_id = $projectId;
    //             $jobs->templates_id = $templatesId;
    //             $jobs->properties = $data;
    //             $jobs->save();
    //             return $this->response->jsonResponse(false, 'Data uploaded successfully', [], 200);
    //         } else {
    //             return $this->response->jsonResponse(false, 'No Excel Data', [], 201);
    //         }
    //     }
    //     return response()->json(['message' => 'No file provided'], 400);
    // }
    public function test()
    {
        $jobs = new AddJobsColumn();
        $jobs->project_id = 101;
        $jobs->properties = ['color' => 'blue', 'size' => 'medium'];
        $jobs->save();
        return response()->json(['message' => 'success'], 200);
    }
}
