<?php

namespace App\Repositories;

use App\Jobs\SendAbstractSubmittedMails;
use App\Jobs\SendAbstractEditStatusChangeMail;
use App\Jobs\SendAbstractStatusChangeMail;
use App\Jobs\SendAbstractEditTrackMail;
use App\Models\Abstracts;
use App\Models\AbstractUser;
use Illuminate\Support\Facades\Log;
use App\Models\Event;
use App\Models\EventUser;
use App\Models\File;
use App\Models\Presentation;
use App\Models\Score;
use App\Models\User;
use App\Rules\AbstractDataRule;
use App\Support\Entity;
use App\Support\Query;
use Exception;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\AbstractExportClass;
use App\Models\Slot;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Str;
use Hamcrest\Type\IsArray;

class AbstractsRepository extends Repository
{
    protected $abstractFields = ["category", "subcategory", "submission_status", "selection_status", "presentation_invite"];

    public function __construct($event = null)
    {
        $this->for($event);
    }

    public function model(): Abstracts
    {
        return new Abstracts;
    }

    public function fetchSingleAbstract($id, $role = null, $request)
    {
        // event
        $event = $request->get('event');

        if ($role == 'event_reviewer') {
            // reviewer id
            $reviewerId = authUser()->id;
            // event user
            $eventUser = EventUser::whereEventId($event->id)->whereUserId($reviewerId)->first();
            // event profile data
            $eventProfileData = $eventUser->event_profile_data;
            // form settings
            $formSettings = collect($eventUser->event->form_settings->toArray());
            // form settings fields
            $dataFields = $formSettings->pluck('field_id')->toArray();

            // query
            $query = Abstracts::with(['users', 'scores' => function ($query) use ($reviewerId) {
                $query->where('user_id', '=', $reviewerId);
            }])->whereEventId($event->id);

            $this->reviewerAbstractQuery($eventProfileData, $dataFields, $query, $eventUser->user->id, $reviewerId, true);
            $abstract = $query->find($id);
        } else {
            $abstract = Abstracts::with(['users'])->find($id);
            $eventId = $abstract->event->id;
            if ($role == 'event_admin' || $role == 'event_co_chair') {
                $scores = $abstract->scores()
                    ->join('users', 'scores.user_id', '=', 'users.id')
                    ->join('event_user as eu', 'users.id', '=', 'eu.user_id')
                    ->where('eu.event_id', '=', $eventId)
                    ->join('model_has_roles as mhs', 'mhs.model_id', '=', 'eu.id')
                    ->where('mhs.model_type', '=', EventUser::class)
                    ->join('roles as r', 'r.id', '=', 'mhs.role_id')
                    ->groupBy('id')
                    ->addSelect([
                        'scores.*',
                        DB::raw("CONCAT(users.first_name, ' ', users.last_name) as reviewer_name"),
                        DB::raw("GROUP_CONCAT(r.name) as reviewer_roles")
                    ])
                    ->get();
                $abstract->scores = $scores;
            }
        }
        if (!$abstract)
            return null;

        return $abstract;
    }

    /**
     * @param Request $request
     * @param User|null $submitter
     * @return array
     * @throws \Illuminate\Validation\ValidationException
     * @throws Exception
     */
    public function createOrUpdate(Request $request): array
    {
        $input = $request->input();
        $continue = $request->get('continue', true);
        $message = null;
        $step = $input['step'];
        $abstractEditTrackData = [];
        $abstractChangeFieldsIds =[];
        $isUserUpdated = false;

        $user = authUser();
        $input = cleanInput($input);
        $event = $request->get('event');
        $abstract = Abstracts::find($input['id'] ?? null);
        $role = $request->get('role');
        switch ($step) {
            case 1:
                if (!$abstract && $role != 'event_submitter')
                    validationErrorResponse(['Access denied']);

                $platformSettings = $event->platform_settings->toArray();
                if (!$abstract && !$platformSettings['submitter_registration'])
                    validationErrorResponse(['New abstract submission is closed']);

                $continue = !($continue == 'false');
                $validator = Validator::make($input, [
                    'data' => ['required', new AbstractDataRule($event)],
                    'step' => ['required']
                ]);

                if ($validator->fails() && $continue)
                    validationErrorResponse($validator->errors());

                $message = "Abstract updated successfully";
                if (!$abstract) {
                    $abstract = new Abstracts([
                        'user_id' => authUser()->id,
                        'step' => 0,
                        'event_id' => $event->id,
                    ]);
                    $abstract->save();


                    //attaching abstract user role
                    $submitter = $user;
                    $submitter->useRepository()->attachUserToAbstract($abstract, $submitter, 'abstract_submitter');
                    $message = "Abstract created successfully";
                } else {
                    $abstractSubmitter = $abstract->users('abstract_submitter')->first();
                    if (($role != 'event_admin' && $user->id != $abstractSubmitter->id) || $abstract->event_id != $event->id)
                        validationErrorResponse(['Access denied']);
                }

                $data = json_decode($input['data'], true);

                //handling abstract files
                // $fileFields = getField($event->form_settings, 'file', 'type');

                $fileFields = getField($event->form_settings, 'file', 'type');
                $attachmentField = null;

                foreach ($event->form_settings as $setting) {
                    if ($setting['field_id'] === 'subsection' && $setting['enabled']) {
                        $subsectionFields = $setting['fields'] ?? [];

                        foreach ($subsectionFields as $subsectionField) {
                            if ($subsectionField['field_id'] === 'subsection_attachment') {
                                $attachmentField = $subsectionField;
                                break 2; // Break both loops
                            }
                        }
                    }
                }

                if ($attachmentField) {
                    $fileFields[] = $attachmentField;
                }

                $data = $this->handleAbstractFiles($request, $abstract, $data, $fileFields);

                if ($role === 'event_submitter' && $abstract->submission_flag) {
                    foreach ($data as $field) {
                        $oldData = array_filter($abstract->data->toArray(), fn($abstract) => $abstract['field_id'] == $field['field_id']);
                        $key = array_keys($oldData);
                        $event = Event::whereId($abstract->event_id)->first();
                        $formSettings = array_filter($event->form_settings->toArray(), fn($settings) => $settings['field_id'] == $field['field_id']);
                        $formSettingsKey = array_keys($formSettings);
                        $removedFiles = $request->get('removed_files');
                        $removedFiles = json_decode($removedFiles, true);

                        if ($field['field_id'] === 'subsection') {
                            foreach ($field['fields'] as $subsectionField) {
                                $inArray = in_array($subsectionField['field_id'], ['subsection_content', 'subsection_attachment']);
                                $valueChanged = count($oldData) > 0 &&  $inArray && isset($subsectionField['value']) && is_string($subsectionField['value']) && strcmp($oldData[$key[0]]['fields'][0]['value'], $subsectionField['value']) !== 0;
                                $attachmentChanged = $inArray && ($request->hasFile($subsectionField['field_id']) || isset($removedFiles['subsection']));

                                if (
                                    $inArray && (
                                        ($subsectionField['field_id'] == 'subsection_content' && $valueChanged) ||
                                        ($subsectionField['field_id'] == 'subsection_attachment' && $attachmentChanged)
                                    )
                                ) {
                                    $abstractEditTrackData[] = [
                                        'field_id' => $subsectionField['field_id'],
                                        'label' => $formSettings[$formSettingsKey[0]]['label'] ?? $subsectionField['field_id'],
                                        'edit_date' => date('Y-m-d H:i:s'),
                                        'old_value' => count($oldData) > 0 && isset($oldData[$key[0]]['fields'][$subsectionField['field_id'] == 'subsection_content' ? 0 : 1]['value']) ?
                                        $oldData[$key[0]]['fields'][$subsectionField['field_id'] == 'subsection_content' ? 0 : 1]['value'] : null,
                                        'new_value' => $subsectionField['value'],
                                    ];

                                }
                            }
                        } else {
                            $attachmentChanged = in_array($field['field_id'], ['attachment']) && ($request->hasFile($field['field_id']) || isset($removedFiles['attachment']));
                            $valueChanged = count($oldData) > 0 && isset($field['value']) && is_string($field['value']) && strcmp($oldData[$key[0]]['value'], $field['value']) !== 0;
                            $commentChanged = count($oldData) > 0 && isset($field['comment']) && strcmp($oldData[$key[0]]['comment']['value'], $field['comment']['value']) !== 0;

                            if ($attachmentChanged || $valueChanged) {
                                $editField = [
                                    'field_id' => $field['field_id'],
                                    'label' => $formSettings[$formSettingsKey[0]]['label'] ?? $field['field_id'],
                                    'edit_date' => date('Y-m-d H:i:s'),
                                    'old_value' => count($oldData) > 0 ? $oldData[$key[0]]['value'] : null,
                                    'new_value' => $field['value'],
                                ];
                                $abstractEditTrackData[] = $editField;
                            }

                            if ($commentChanged) {
                                $editField = [
                                    'field_id' => $field['field_id'],
                                    'label' => $formSettings[$formSettingsKey[0]]['label'] ?? $field['field_id'],
                                    'edit_date' => date('Y-m-d H:i:s'),
                                    'old_value' => count($oldData) > 0 ? $oldData[$key[0]]['comment']['value'] : null,
                                    'new_value' => $field['comment']['value'],
                                ];
                                $abstractEditTrackData[] = $editField;
                            }

                        }
                    }
                }

                // old abstract edit track data
                $oldAbstractEditTrackData = json_decode(json_encode($abstract->edit_track), true);

                // if new changes exists
                if ($abstractEditTrackData) {
                    if ($oldAbstractEditTrackData) {
                        $abstract->edit_track = array_merge($oldAbstractEditTrackData,  $abstractEditTrackData);
                    } else {
                        $abstract->edit_track = $abstractEditTrackData;
                    }
                } else {
                    $abstract->edit_track = $oldAbstractEditTrackData;
                }

                $abstract->data = $data;

                if ($abstract->step < $step && $continue)
                    $abstract->step = $input['step'];
                $abstract->save();

                // update abstract related slots
                if($role === 'event_admin' && $abstract->submission_flag){
                    $slotRepository = new SlotRepository();
                    $slotRepository->updateRelatedSlots($abstract,null);
                }

                break;
            case 2:
                if (!$abstract)
                    validationErrorResponse(['Abstract not found']);

                $abstractSubmitter = $abstract->users('abstract_submitter')->first();

                if (($role != 'event_admin' && $user->id != $abstractSubmitter->id) || $abstract->event_id != $event->id)
                    validationErrorResponse(['Access denied']);

                $continue = !($continue == 'false');

                $users = json_decode($input['users'], true);
                // Checking the submitter created presenter limit exceeds
                if ($role == 'event_submitter') {
                    // presenter count from request
                    $presentersCount = $this->submitterCreatedPresenterCount($users, $abstract);
                    // checking submitter presenter section enabled or not
                    $submitterPresenterSectionEnabled = $abstract->event->general_settings['submitter_abstract_presenters_section']['enabled'];
                    // submitter creating presenter limit
                    $limit = $abstract->event->general_settings['submitter_abstract_presenters_section']['no_of_presenters_allowed'];

                    if ($submitterPresenterSectionEnabled) {
                        // validation
                        if ($presentersCount > $limit) {
                            validationErrorResponse(['Allowed presenter count exceeds']);
                        }
                        $resultCount = $presentersCount < 0 ? 0 : $presentersCount;
                        $abstract->submitter_presenters_count = $resultCount;
                    }
                }

                $files = $request->allFiles();
                $removedUsers = json_decode($input['removed_users'], true);

                $valid = $role == 'event_admin';

                $userManageStatus = $this->manageAbstractUsers($abstract, $users, $files, $removedUsers, $valid, $request, $isUserUpdated);

                if ($abstract->edit_track) {
                    $abstractChangeFieldsIds = array_column(json_decode(json_encode($abstract->edit_track), true), 'field_id');
                }

                // handling abstract user edit
                if ($role == 'event_submitter' && $abstract->submission_flag &&  $userManageStatus &&  !in_array("abstract_user", $abstractChangeFieldsIds)) {

                    $abstractUserEditData[] =  array(
                        'field_id'  => 'abstract_user',
                        'label' => 'Abstract user',
                        'edit_date' => date('Y-m-d H:i:s', time()),
                    );

                    // old abstract edit track data
                    $oldAbstractEditTrackData = json_decode(json_encode($abstract->edit_track), true);

                    // if new changes exists
                    if ($oldAbstractEditTrackData) {
                        $abstract->edit_track = array_merge($oldAbstractEditTrackData, $abstractUserEditData);
                    } else {
                        $abstract->edit_track = $abstractUserEditData;
                    }
                }
                if ($abstract->step < $step && $continue)
                    $abstract->step = $input['step'];


                $abstract->save();

                // update abstract related slots
                if($role === 'event_admin' && $abstract->submission_flag){
                    $slotRepository = new SlotRepository();
                    $slotRepository->updateRelatedSlots($abstract,null);
                }

                $message = "Updated abstract users successfully";
                break;
            case 3:
                if (!$abstract)
                    validationErrorResponse(['Abstract not found']);

                $abstractSubmitter = $abstract->users('abstract_submitter')->first();

                if ($role != 'event_submitter' || $user->id != $abstractSubmitter->id || $abstract->event_id != $event->id)
                    validationErrorResponse(['Access denied']);

                $abstractPresenters = $abstract->users('abstract_presenter')->pluck('id')->toArray();

                $abstractAuthors = $abstract->users('abstract_author')->pluck('id')->toArray();
                // validation
                if (!$abstractPresenters)
                    validationErrorResponse(['Add at least one presenter']);

                if (!$abstractSubmitter)
                    validationErrorResponse(['Submitter is required']);

                if (!$abstractAuthors)
                    validationErrorResponse(['Add at least one author']);

                if ($abstract->step <= $step && $continue) {
                    $abstract->step = $input['step'];
                    $abstract->submission_status = 'submitted';
                    $abstract->submission_date = date('Y-m-d H:i:s', time());
                    $abstract->last_edited_data = $abstract->edit_track;
                    $abstract->edit_track = null;
                    $abstract->save();

                    if ($abstract->last_edited_data) {
                        $abstractChangeFieldsIds = array_column(json_decode(json_encode($abstract->last_edited_data), true), 'label');
                        dispatch(new SendAbstractEditTrackMail($abstract, $request->get('event'), $abstractChangeFieldsIds));
                    }

                    $mail = dispatch(new SendAbstractSubmittedMails($abstract, $request->get('event'),$abstract->submission_flag));
                    // update abstract related slots
                    if($role === 'event_submitter' && $abstract->submission_flag){
                        $slotRepository = new SlotRepository();
                        $slotRepository->updateRelatedSlots($abstract,null);
                    }
                    // set submission flag
                    $abstract->submission_flag = true;
                    $abstract->save();
                }
                $message = "Abstract submitted successfully";
                break;
            default:
                validationErrorResponse(['Invalid step']);
                break;
        }

        return [$abstract, $message];
    }

    /**
     * submitter created presenter count
     */
    public function submitterCreatedPresenterCount($users, $abstract)
    {
        $count = 0;
        //abstract presenters
        $abstractPresenters = $abstract->users('abstract_presenter')->pluck('id')->toArray();

        foreach ($users as  $user) {
            if (str_contains($user['abstract_roles'], 'abstract_presenter')) {
                $count++;
            }
        }

        // if submitter presenter count exist in table
        if ($abstract->submitter_presenters_count) {

            // presenters count from table
            $submitterCreatedPresentersCount =
                $abstract->submitter_presenters_count;

            // new created presenters count
            $newCreatedPresenters =
                $count - count($abstractPresenters);

            // total presenters
            $totalPresenters =
                $submitterCreatedPresentersCount + $newCreatedPresenters;

            $count = $totalPresenters;
        }


        return $count;
    }

    /**
     * @throws Exception
     */
    public function manageAbstractUsers(Abstracts $abstract, $users, $files, $removedUsers = [], $valid = false, $request, $isUserUpdated)
    {

        if ($removedUsers) {
            $this->removeUsersFromAbstract($abstract, $removedUsers);
            // user updated status change
            $isUserUpdated = true;
        }

        $userRepository = new UserRepository();

        //add/edit users in the abstract
        foreach ($users as $key => $person) {
            $user = User::find($person['id'] ?? null);
            $authUser = authUser();
            $isEdit = true;
            if (!$user) {
                $validator = Validator::make($person, [
                    'first_name' => ['required', 'string', 'max:255'],
                    'last_name' => ['required', 'string', 'max:255'],
                    'email' => ['required', 'string', 'email', 'max:255', 'unique:users'],
                ]);

                if ($validator->fails())
                    validationErrorResponse($validator->errors());

                $user = new User();
                $user->password = Hash::make(randomStringGenerator(8));
                $user->valid = $valid;
                $user->email = trim($person['email']);
                $user->created_by = $authUser->id;
                $user->valid = true;
                // is it new user
                $user->new_user = true;
                $user->save();
                $user->assignRole('user');
                $isEdit = false;
                // user updated status change
                $isUserUpdated = true;
            } else {
                // is it new user
                $user->new_user = false;
                $user->save();
            }
            $eventUser = getEventUser($abstract->event->id, $user->id);
            if (!$eventUser) {
                $eventUser = $userRepository->attachUserToEvent($abstract->event, $user, 'event_submitter');
                // type
                $eventUser->type = "strategic";
                // user added via
                $eventUser->user_added_via = "Abstract";
                // save
                $eventUser->save();
            }
            unset($person['pivot']);
            unset($person['id']);
            unset($person['checkboxes']);
            unset($person['login_email_count']);
            unset($person['valid']);
            unset($person['forgot_password']);
            unset($person['avatar']);
            unset($person['company_logo']);
            unset($person['email']);
            unset($person['user_secondary_image']);
            $user->fill($person);
            //keep changes field lists
            $changes = $user->getDirty();

            // Track modification if users table data changed
            if (!empty($changes)) {
                $userRepository->trackUserModification($user, authUser()->id, $abstract->event_id);
                $userRepository->trackVipMarkedBy($user, $changes);
            }

            $user->save();

            if ($changes) {
                // user updated status change
                $isUserUpdated = true;
            }

            $removeAvatar = false;
            $removeCompanyLogo = false;
            $removeUserSecondaryImage = false;

            if (isset($request->get('avatar')[$key])) {
                if ($request->get('avatar')[$key] === "null" && $user->avatar) {
                    $removeAvatar = true;
                }
            }

            if (isset($request->get('company_logo')[$key])) {
                if ($request->get('company_logo')[$key] === "null" && $user->company_logo) {
                    $removeCompanyLogo = true;
                }
            }
            if (isset($request->get('user_secondary_image')[$key])) {
                if ($request->get('user_secondary_image')[$key] === "null" && $user->user_secondary_image) {
                    $removeUserSecondaryImage = true;
                }
            }
            if ($isEdit) {

                // image files
                $imageFiles = [
                    'avatar' => isset($files['avatar'][$key]) ? $files['avatar'][$key] : null,
                    'company_logo' => isset($files['company_logo'][$key]) ? $files['company_logo'][$key] : null,
                ];

                // Get the changed fields using the common function
                $changeFields = $userRepository->getChangedFields($changes, $imageFiles, $removeAvatar, $removeCompanyLogo);

                if ($changeFields) {

                    // published session status changing
                    $userRepository->publishedSessionStatusUpdate($user);

                    $userRepository->userVerificationFieldsChanges($changeFields, $user);
                }
            }

            // user updated status change
            if (isset($files['avatar'][$key]) || $removeAvatar) {
                $isUserUpdated = true;
            }

            // user updated status change
            if (isset($files['company_logo'][$key]) || $removeCompanyLogo) {
                $isUserUpdated = true;
            }

            $abstractUser = getAbstractUser($abstract->id, $user->id);
            if (!$abstractUser) {
                $abstractUser = $userRepository->attachUserToAbstract($abstract, $user);
                // user updated status change
                $isUserUpdated = true;
            }
            // new roles
            $roles = explode(',', $person['abstract_roles']);

            // previous roles
            $previousRoles = $abstractUser->roles()->pluck('name', 'id');
            // previous roles array
            $previousRolesArray = json_decode(json_encode($previousRoles), true);

            // role difference
            $roleDifferent1 = array_diff($previousRolesArray, $roles);
            $roleDifferent2 = array_diff($roles, $previousRolesArray);

            if ($roleDifferent1 || $roleDifferent2) {
                // user updated status change
                $isUserUpdated = true;
            }

            //sync roles
            $abstractUser->syncRoles($roles);

            // remove user images
            $userRepository->removeUserImages($user, $removeAvatar, $removeCompanyLogo,$removeUserSecondaryImage);

            //handling user images
            $userFiles = [];
            if (isset($files['avatar'][$key]))
                $userFiles['avatar'] = $files['avatar'][$key];
            if (isset($files['company_logo'][$key]))
                $userFiles['company_logo'] = $files['company_logo'][$key];
            if (isset($files['user_secondary_image'][$key]))
                $userFiles['user_secondary_image'] = $files['user_secondary_image'][$key];

            $userRepository->updateUserImages($userFiles, $user, authUser()->id, $abstract->event_id);
        }

        return $isUserUpdated;
    }

    /**
     * @throws Exception
     */
    public function removeUsersFromAbstract(Abstracts $abstract, $users)
    {
        foreach ($users as $user) {
            if ($user) {
                $abstractUser = getAbstractUser($abstract->id, $user);
                if ($abstractUser) {
                    if ($abstractUser->hasRole('abstract_submitter'))
                        throw new Exception('Cannot remove abstract submitter');
                    $abstractUser->syncRoles([]);
                    $abstractUser->delete();
                }
            }
        }
    }

    public function handleAbstractFiles(Request $request, Abstracts $abstract, $data, $fileFields)
    {


        $removedFiles = $request->get('removed_files');
        $removedFiles = json_decode($removedFiles, true);
        //handling uploaded files
        foreach ($fileFields as $fileField) {
            if (!$fileField['enabled'])
                continue;
            $fieldId = $fileField['field_id'];


            if ($request->hasFile($fieldId)) {
                $files = $request->file($fieldId);

                $fileContents = [];
                foreach ($files as $file) {

                    $filePath = ($fieldId == "subsection_attachment") ? "{$abstract->id}/files/subsection/" : "{$abstract->id}/files/";
                    $context = ($fieldId == "subsection_attachment") ? "abstract.file.subsection" : "abstract.file";

                    $fileObj = $abstract->saveFile(
                        $file,
                        null,
                        $filePath,
                        $context,
                        null,
                        false
                    );
                    $fileContents[] = $fileObj->getFileObject();
                }
                $dataField = getAllAttachmentFields($data, $fieldId);
                $dataField = array_shift($dataField);
                $dataFieldValue = $dataField['value'] ? $dataField['value'] : [];
                $dataFieldValue = array_values(array_filter(array_merge($dataFieldValue, $fileContents)));
                $data = updateAllAttachmentFields($data, $fieldId, 'value', $dataFieldValue);
            }

            //handling removed files
            if (isset($removedFiles[$fieldId]) || isset($removedFiles['subsection'])) {
                $fileIds = $removedFiles[$fieldId] ?? $removedFiles['subsection'];
                foreach ($fileIds as $fileId) {
                    $this->deleteFile($fileId);
                }
            }
        }


        return $data;
    }

    /**
     * @return string[]
     * Default select columns
     */
    public function selectColumns(): array
    {
        return [
            'abstracts.id',
            'abstracts.user_id',
            'abstracts.event_id',
            'abstracts.data',
            'abstracts.step',
            'abstracts.submission_status',
            'abstracts.submission_date',
            'abstracts.selection_status',
            'abstracts.presentation_type',
            'abstracts.presentation_invite',
            'abstracts.rank',
            'abstracts.score',
            'abstracts.no_of_votes',
            'abstracts.created_at',
            'abstracts.updated_at',
        ];
    }

    public function applyEventScope(Builder $query, $arguments): Builder
    {
        if ($arguments->event) {
            $query->where('abstracts.event_id', '=', $arguments->event->id);
        }

        return $query;
    }

    public function applyOrder(Builder $query, Query $arguments): Builder
    {
        if (!$arguments->sort)
            return $query;

        $abstractColumns = [
            'id' => 'abstracts.id',
            'submission_status' => 'abstracts.submission_status',
            'presentation_invite' => 'abstracts.presentation_invite',
            'submission_date' => 'abstracts.submission_date',
            'score' => 'abstracts.score',
            'rank' => 'abstracts.rank',
            'step' => 'abstracts.step',
            'created_at' => 'abstracts.created_at',
            'submitter_name' => 'submitter_name',
            'no_of_votes' => 'no_of_votes'
        ];

        $sorts = is_array($arguments->sort) ? json_decode(json_encode($arguments->sort), FALSE) : json_decode($arguments->sort, true);

        $formSettings = collect($arguments->event->form_settings->toArray());
        $dataFields = $formSettings->pluck('field_id')->toArray();
        foreach ($sorts as $sort => $method) {
            if (array_key_exists($sort, $abstractColumns)) {
                $query->orderBy($sort, $method);
            } else if (in_array($sort, $dataFields)) {
                $key = array_search($sort, $dataFields);
                $query->orderByRaw("JSON_EXTRACT(abstracts.data, '$[$key].value') $method");
            } else if ($arguments->role == 'event_admin' && $sort == 'submitter_name')
                $query->orderBy($sort, $method);
            else if ($arguments->role == 'event_admin' && $sort == 'submitter_country')
                $query->orderBy($sort, $method);
            else if ($arguments->role == 'event_admin' && $sort == 'company')
                $query->orderBy($sort, $method);
            else if ($arguments->role == 'event_submitter' && $sort == 'abstract_roles')
                $query->orderBy($sort, $method);
        }

        return $query;
    }

    public function applyFilter(Builder $query, $arguments, $userFilter = false): Builder
    {
        if (!$arguments->filter)
            return $query;

        $filter = is_array($arguments->filter) ? json_decode(json_encode($arguments->filter), FALSE) : json_decode($arguments->filter);

        $formSettings = collect($arguments->event->form_settings->toArray());
        $dataFields = $formSettings->pluck('field_id')->toArray();

        if ($filter->category ?? false) {
            $key = array_search('category', $dataFields);
            $values = $filter->category;

            $query->where(function ($query) use ($values, $key) {
                foreach ($values as $value) {
                    //$query->orWhereRaw(DB::raw("JSON_EXTRACT(abstracts.data, '$[$key].value') = ?"), [$value]);
                    $query->orWhereJsonContains('abstracts.data', [
                        ['field_id' => 'category', 'value' => $value]
                    ]);
                }
            });
        }

        if ($filter->subcategory ?? false) {
            $key = array_search('subcategory', $dataFields);
            $values = $filter->subcategory;

            $query->where(function ($query) use ($values, $key) {
                foreach ($values as $value) {
                    //$query->orWhereRaw(DB::raw("JSON_EXTRACT(abstracts.data, '$[$key].value') = ?"), [$value]);
                    $query->orWhereJsonContains('abstracts.data', [
                        ['field_id' => 'subcategory', 'value' => $value]
                    ]);
                }
            });
        }

        if (isset($filter->company) && !$userFilter) {
            $values = $filter->company;
            $query->where(function ($query) use ($values) {
                foreach ($values as $value) {
                    $query->orWhere('u.company', '=', $value);
                }
            });
        }

        if ($filter->selection_status ?? false) {
            $values = $filter->selection_status;
            $query->where(function ($query) use ($values) {
                foreach ($values as $value) {
                    if ($value == 'Not Set') {
                        $query->orWhere('abstracts.selection_status', '=', null);
                    } else {
                        $query->orWhere('abstracts.selection_status', '=', $value);
                    }
                }
            });
        }

        if ($filter->submission_status ?? false) {
            $values = $filter->submission_status;
            $query->where(function ($query) use ($values) {
                foreach ($values as $value) {
                    $query->orWhere('abstracts.submission_status', '=', $value);
                }
            });
        }

        if ($filter->presentation_invite ?? false) {
            $values = $filter->presentation_invite;
            $query->where(function ($query) use ($values) {
                foreach ($values as $value) {
                    if ($value == 'Not Set') {
                        $query->orWhere('abstracts.presentation_invite', '=', null);
                    } else {
                        $query->orWhere('abstracts.presentation_invite', '=', $value);
                    }
                }
            });
        }
        if ($filter->score_min ?? false) {
            $query->where('abstracts.score', '>=', $filter->score_min);
        }

        if ($filter->score_max ?? false) {
            $query->where('abstracts.score', '<=', $filter->score_max);
        }

        if ($arguments->role == 'event_submitter' && $filter->presentations) {
            $query->whereIn('r.name', ['abstract_submitter', 'abstract_presenter']);
        }

        if ($filter->no_of_votes_min ?? false) {
            $query->where('abstracts.no_of_votes', '>=', $filter->no_of_votes_min);
        }

        if ($filter->no_of_votes_max ?? false) {
            $query->where('abstracts.no_of_votes', '<=', $filter->no_of_votes_max);
        }

        if ($filter->scoreStatus ?? false) {
            $values = $filter->scoreStatus;
            $query->where(function ($query) use ($values) {
                foreach ($values as $value) {
                    if ($value == 'Unmarked') {
                        $query->orWhere('abstracts.score', '=', null);
                    } else {
                        $query->orWhere('abstracts.score', '!=', null);
                    }
                }
            });
        }

        return $query;
    }

    public function applySearch(Builder $query, $arguments): Builder
    {
        if ($arguments->search) {
            $abstractColumns = [
                'id' => 'abstracts.id',
                'submission_status' => 'abstracts.submission_status',
                'submission_date' => 'abstracts.submission_date',
                'score' => 'abstracts.score',
                'rank' => 'abstracts.rank',
                'created_at' => 'abstracts.created_at',
                'no_of_votes' => 'abstracts.no_of_votes'
            ];
            $search = strtolower($arguments->search);
            $query->where(function ($query) use ($arguments, $search, $abstractColumns) {
                $query->orWhereRaw(DB::raw("lower(JSON_EXTRACT(abstracts . data, '$[*].value')) like ? "), ["%{$search}%"]);
                foreach ($abstractColumns as $abstractColumn) {
                    $query->orWhere(DB::raw("lower($abstractColumn)"), 'like', "%{$search}%");
                }
                if ($arguments->role == 'event_admin')
                    $query->orWhereRaw(DB::raw("lower(CONCAT(u.first_name, ' ', u.last_name)) like ?"), ["%{$search}%"]);
                if ($arguments->role == 'event_submitter')
                    $query->orWhereRaw(DB::raw("lower(r.name) like ?"), ["%{$search}%"]);
            });
        }

        return $query;
    }

    public function applyUserScope(Builder $query, $arguments): Builder
    {
        if ($arguments->role == 'event_submitter') {
            $query->join('abstract_user as au', 'au.abstracts_id', '=', 'abstracts.id')
                ->where('au.user_id', '=', $arguments->event_user->user_id)
                ->join('model_has_roles as mhs', 'mhs.model_id', '=', 'au.id')
                ->where('mhs.model_type', '=', AbstractUser::class)
                ->join('roles as r', 'r.id', '=', 'mhs.role_id')
                ->groupBy('au.id')
                ->addSelect([
                    DB::raw("GROUP_CONCAT(r.name) as abstract_roles")
                ]);
        } elseif ($arguments->role == 'event_admin' && !isset($arguments->reviewer_id)) {
            $query->join('abstract_user as au', 'au.abstracts_id', '=', 'abstracts.id')
                ->join('users as u', 'u.id', '=', 'au.user_id')
                ->join('model_has_roles as mhs', 'mhs.model_id', '=', 'au.id')
                ->where('mhs.model_type', '=', AbstractUser::class)
                ->join('roles as r', 'r.id', '=', 'mhs.role_id')
                ->where('r.name', '=', 'abstract_submitter')
                ->addSelect([
                    DB::raw("CONCAT(u.first_name, ' ', u.last_name) as submitter_name"),
                    'u.first_name',
                    'u.last_name',
                    'u.email',
                    'u.company as company',
                    'u.country as submitter_country',
                    DB::raw("CASE WHEN do_not_send_emails = 1 THEN cc_emails ELSE email END  as submitter_email"),
                ]);
        } elseif ($arguments->role == 'event_co_chair') {
            //selecting on submitted abstracts
            $query->join('users as u', 'abstracts.user_id', '=', 'u.id');
            $query->where('submission_status', '=', 'submitted')
                ->where('abstracts.user_id', '!=', authUser()->id);
        } elseif ($arguments->role == 'event_reviewer' || ($arguments->role == 'event_admin' && $arguments->reviewer_id)) {
            if ($arguments->role == 'event_admin' && $arguments->reviewer_id) {
                $eventUser = getEventUser($arguments->event->id, (int)$arguments->reviewer_id);
                $reviewerId = (int)$arguments->reviewer_id;
            } else {
                $eventUser = $arguments->event_user;
                $reviewerId = authUser()->id;
            }
            $query->join('users as u', 'abstracts.user_id', '=', 'u.id');
            $eventProfileData = $eventUser->event_profile_data;

            $formSettings = collect($arguments->event->form_settings->toArray());
            $dataFields = $formSettings->pluck('field_id')->toArray();

            $this->reviewerAbstractQuery($eventProfileData, $dataFields, $query, $reviewerId);
        }

        if ($arguments->next ?? false) {
            $query->leftJoin('scores as s', function ($join) use ($arguments) {
                $join->on('abstracts.id', '=', 's.abstract_id')
                    ->where('s.user_id', '=', authUser()->id);
            })->addSelect([DB::raw("COUNT(s.id) as score_count")])
                ->groupBy('abstracts.id');
        }

        return $query;
    }

    public function applyScope(Builder $query, $arguments): Builder
    {
        $this->applyEventScope($query, $arguments);

        $this->applyUserScope($query, $arguments);

        $this->applyOrder($query, $arguments);

        $this->applyFilter($query, $arguments);

        $this->applySearch($query, $arguments);

        return $query;
    }

    /**
     * @throws Exception
     */
    public function scope($arguments, $callback = null): Builder
    {
        //get query builder
        $query = $this->query()
            ->select($this->selectColumns());

        //applying different scopes based on the arguments
        $this->applyScope($query, $arguments);

        //resolve if there is any callback functions available
        return $this->resolve($query, $arguments, $callback);
    }

    /**
     * @throws Exception
     */
    public function listing(Request $request, $paginate)
    {
        //building arguments
        $arguments = $this->arguments($request);

        //building query
        $query = $this->scope($arguments);

        return ($paginate && $arguments->paging != 'All') ? $query->paginate($arguments->paging, ['*'], 'page', $arguments->page) : $query->get();
    }

    /**
     * @param Abstracts $abstract
     */
    public function deleteAbstract(Abstracts $abstract)
    {

        $slotCount = Slot::whereAbstractId($abstract->id)
            ->count();
        if ($slotCount > 0)
            return response([
                'status' => false,
                'message' => 'Cannot delete the abstract. The abstract is added to a slot'
            ]);


        //deleting presentation
        $presentation = Presentation::whereAbstractId($abstract->id)->first();
        if ($presentation) {
            $presentationRepo = new PresentationRepository();
            $presentationDelete = $presentationRepo->deletePresentation($presentation);
            if (!$presentationDelete) {
                return response([
                    'status' => false,
                    'message' => 'Cannot delete the abstract. A presentation created with this abstract is added to a slot'
                ]);
            }
        }

        //detaching abstract users
        $abstractUsers = AbstractUser::whereAbstractsId($abstract->id)->get();
        foreach ($abstractUsers as $abstractUser) {
            $abstractUser->delete();
        }
        $event = $abstract->event;

        //deleting abstract files
        $fileFields = getField($event->form_settings, 'file', 'type');
        $data = $abstract->data->toArray();
        foreach ($fileFields as $fileField) {
            $dataField = getField($data, $fileField['field_id']);
            $dataField = array_shift($dataField);
            $values = $dataField['value'] ?? [];
            $values = $values != "" ? $values : [];
            foreach ($values as $value) {
                $fileId = $value['file_id'];
                $this->deleteFile($fileId);
            }
        }

        //deleting abstract scores
        $scores = Score::whereAbstractId($abstract->id)->get();
        $scoreRepo = new ScoreRepository();
        foreach ($scores as $score) {
            $scoreRepo->delete($score);
        }

        Storage::deleteDirectory('private/media/abstracts/' . $abstract->id);

        $abstract->delete();

        return response([
            'status' => true,
            'message' => 'Successfully deleted abstract'
        ]);
    }

    public function handleEditRequest(Abstracts $abstract, $status)
    {
        switch ($status) {
            case 'accept':
                $abstract->step = 0;
                $abstract->submission_status = 'draft';
                $abstract->edit_request = false;
                $abstract->save();

                //mail to submitter
                dispatch(new SendAbstractEditStatusChangeMail($abstract, $status));

                break;
            case 'reject':
                $abstract->edit_request = false;
                $abstract->save();

                //mail to submitter
                dispatch(new SendAbstractEditStatusChangeMail($abstract, $status));

                break;
            case 'change':

                // validation
                if ($abstract->submission_status === 'draft') {
                    $abstractSubmitter = $abstract->users('abstract_submitter')->first();

                    $abstractPresenters = $abstract->users('abstract_presenter')->pluck('id')->toArray();

                    $abstractAuthors = $abstract->users('abstract_author')->pluck('id')->toArray();

                    if (!$abstractPresenters)
                        validationErrorResponse(['The presenter does not exist. Add at least one presenter']);

                    if (!$abstractSubmitter)
                        validationErrorResponse(['The submitter does not exist. Submitter is required']);

                    if (!$abstractAuthors)
                        validationErrorResponse(['The author does not exist. Add at least one author']);
                }

                $abstract->submission_status = $abstract->submission_status === 'draft' ? 'submitted' : 'draft';
                $abstract->step = $abstract->submission_status === 'submitted' ? 3 : 0;
                $abstract->edit_request = false;
                $abstract->save();

                //mail to submitter
                dispatch(new SendAbstractStatusChangeMail($abstract));

                break;
            default:
                validationErrorResponse(['Invalid status']);
        }

        return $abstract;
    }

    public function getAbstractStatusCount(Event $event): Builder
    {
        $query = $this->query();
        $query->where('event_id', '=', $event->id);
        $query->select([
            DB::raw("SUM(CASE WHEN abstracts.submission_status='submitted' THEN 1 ELSE 0 END) as submitted"),
            DB::raw("SUM(CASE WHEN abstracts.submission_status='draft' THEN 1 ELSE 0 END) as draft")
        ]);
        $query->join('abstract_user as au', 'au.abstracts_id', '=', 'abstracts.id')
            ->join('users as u', 'u.id', '=', 'au.user_id')
            ->join('model_has_roles as mhs', 'mhs.model_id', '=', 'au.id')
            ->where('mhs.model_type', '=', AbstractUser::class)
            ->join('roles as r', 'r.id', '=', 'mhs.role_id')
            ->where('r.name', '=', 'abstract_submitter');

        return $query;
    }

    public function getAbstractCountryCount(Event $event): Builder
    {
        $query = $this->getAbstractStatusCount($event);
        $query
            ->where('u.country', '!=', null)
            ->addSelect(['u.country as country'])
            ->groupBy('u.country');

        return $query;
    }

    public function getAbstractCompanyCount(Event $event): Builder
    {
        $query = $this->getAbstractStatusCount($event);
        $query
            ->where('u.company', '!=', null)
            ->addSelect(['u.company as company'])
            ->groupBy('u.company');

        return $query;
    }

    public function getAbstractCategoryCount(Event $event): array
    {
        return $this->getAbstractFieldCount($event, 'category');
    }

    public function getAbstractSubcategoryCount(Event $event): array
    {
        return $this->getAbstractFieldCount($event, 'subcategory');
    }

    private function getAbstractFieldCount(Event $event, string $fieldId): array
    {
        // Fetch abstracts directly with necessary fields
        $query = Abstracts::select('abstracts.id', 'abstracts.data', 'abstracts.submission_status')
            ->where('event_id', $event->id)
            ->join('abstract_user as au', 'au.abstracts_id', '=', 'abstracts.id')
            ->join('users as u', 'u.id', '=', 'au.user_id')
            ->join('model_has_roles as mhs', 'mhs.model_id', '=', 'au.id')
            ->where('mhs.model_type', '=', AbstractUser::class)
            ->join('roles as r', 'r.id', '=', 'mhs.role_id')
            ->where('r.name', '=', 'abstract_submitter');

        $abstracts = $query->get();

        $fieldCounts = [];

        foreach ($abstracts as $abstract) {
            $submissionStatus = $abstract->submission_status;
            $data = is_string($abstract->data) ? json_decode($abstract->data, true) : (array) $abstract->data;

            // Find the field value directly using array functions
            $fieldValue = null;
            foreach ($data as $item) {
                if ($item['field_id'] === $fieldId && isset($item['value'])) {
                    $fieldValue = $item['value'];
                    break;
                }
            }

            if ($fieldValue !== null) {
                // Initialize field count if it doesn't exist
                if (!isset($fieldCounts[$fieldValue])) {
                    $fieldCounts[$fieldValue] = [
                        'draft' => 0,
                        'submitted' => 0,
                        $fieldId => $fieldValue,
                    ];
                }

                // Increment count based on submission status
                if ($submissionStatus === 'draft') {
                    $fieldCounts[$fieldValue]['draft']++;
                } elseif ($submissionStatus === 'submitted') {
                    $fieldCounts[$fieldValue]['submitted']++;
                }
            }
        }

        // Extract values from associative array to get a simple indexed array
        $result = array_values($fieldCounts);

        return $result;
    }

    public function clientTimezoneDateTime($timeZone, $value)
    {
        $getFirstChar = substr($timeZone, 0, 1);

        if ($getFirstChar === '-') {
            $timeZone = str_replace("-", "", $timeZone);
            $convertTime = sprintf('%02d:%02d', (float)$timeZone, fmod($timeZone, 1) * 60);
            $timeZone = '-' . $convertTime;
        } else {
            $timeZone = $getFirstChar . sprintf('%02d:%02d', (float)$timeZone, fmod($timeZone, 1) * 60);
        }

        $date = new \DateTime($value);
        $timezone = new \DateTimeZone($timeZone);
        $convert = $date->setTimezone($timezone);
        $newDateTime = date_format($convert, 'Y-m-d H:i:s');
        $newConvertedTime = date('h:i:s A', strtotime($newDateTime));
        $newDate = date('M/d/Y', strtotime($newDateTime));

        return $newDate . ' ' . $newConvertedTime;
    }

    public function exportAbstract($abstracts, $timeZone, $role, $eventUser, $isAwardSubmission = false)
    {
        // Get auth user for access check
        $authUser = authUser();
        $event = Event::whereId($abstracts->first()->event_id)->first();
        $authEventUser = getEventUser($event, $authUser);
        $maskedValue = '********';
        
        // Check if authors should be hidden for this event
        $hideAuthorsFromReviewers = false;
        if ($role == 'event_reviewer' || $role == 'event_co_chair') {
            $hideAuthorsFromReviewers = $event->slug_name === config('eventBasedRequirements.hideAuthorsFromReviewersSlug');
        }

        //abstract headings
        $heading =
            [
                $isAwardSubmission ? 'Submission ID' : 'Abstract ID',
                'Status',
                'Avg Score',
                'Score Count',
                'Rank',
                'Selection Status',
                'Presentation Invite Status',
                'Presentation Invite Accepted Or Rejected Date',
                'Created At',
                'Submitted At',
                'Comments',
                'Speaker Comments',
                'Submitter First Name',
                'Submitter Last Name',
                'Submitter Company',
                'Submitter Mobile Number',
                'Submitter Country',
                'Submitter Job Title',
                'Submitter Email',
                'Submitter Industry',
            ];

        if ($role == 'event_reviewer' || $role == 'event_co_chair') {
            if ($hideAuthorsFromReviewers) {
                $heading =
                    [
                        $isAwardSubmission ? 'Submission ID' : 'Abstract ID',
                        'Status',
                        'Your Score',
                        'Created At',
                        'Submitted At',
                    ];
            } else {
                $heading =
                    [
                        $isAwardSubmission ? 'Submission ID' : 'Abstract ID',
                        'Status',
                        'Your Score',
                        'Created At',
                        'Submitted At',
                        'Submitter First Name',
                        'Submitter Last Name',
                        'Submitter Company',
                        'Submitter Mobile Number',
                        'Submitter Country',
                        'Submitter Job Title',
                        'Submitter Email',
                        'Submitter Industry',
                    ];
            }
        }

        $personHeadingCount = $role == 'event_reviewer' || $role == 'event_co_chair' ? ($hideAuthorsFromReviewers ? 5 : 13) : 20;
        $personHeading = [];
        $maxPeople = 0;

        // First pass: find the maximum number of people across all abstracts
        if (!$hideAuthorsFromReviewers) {
            foreach ($abstracts as $abstract) {
                $people = $abstract->users()->get();
                $maxPeople = max($maxPeople, count($people));
            }

            // Generate person headings based on max people count
            for ($i = 1; $i <= $maxPeople; $i++) {
                $personHeading[] = "Person $i First Name";
                $personHeading[] = "Person $i Last Name";
                if ($role != 'event_reviewer' && $role != 'event_co_chair')
                    $personHeading[] = "Person $i Email";
                $personHeading[] = "Person $i Job Title";
                $personHeading[] = "Person $i Company Name";
                if ($role != 'event_reviewer' && $role != 'event_co_chair') {
                    $personHeading[] = "Person $i Address";
                    $personHeading[] = "Person $i Town/City";
                    $personHeading[] = "Person $i State/Province";
                }
                $personHeading[] = "Person $i Country";
                if ($role != 'event_reviewer' && $role != 'event_co_chair')
                    $personHeading[] = "Person $i Phone Number";
                $personHeading[] = "Person $i Industry Area";
                $personHeading[] = "Person $i Biography";
                $personHeading[] = "Person $i Headshot";
                $personHeading[] = "Person $i Company Logo";
                $personHeading[] = "Person $i Author";
                $personHeading[] = "Person $i Presenter";
                $personHeading[] = "Person $i Co-author";
            }
        }

        $excelData = [];
        foreach ($abstracts as $abstract) {

            //abstract users
            $people = $abstract->users()->get();
            //event details
            $event = Event::whereId($abstract->event_id)->first();
            //abstract headings
            $abstractHeading = [];
            foreach ($event->form_settings as $key => $formSetting) {
                $enabled = isset($formSetting['enabled']) ? $formSetting['enabled'] : true;
                if ($enabled && $formSetting['field_id'] != 'subsection') {
                    $abstractHeading[] = $formSetting['label'];
                }
            }

            //user submitter data - skip if hiding authors
            $submitter = !$hideAuthorsFromReviewers ? $abstract->users('abstract_submitter')->first() : null;

            //setting export data
            $data = [];
            $data['abstract_id'] = $abstract->id;
            $data['status'] = $abstract->submission_status;

            if ($role == 'event_reviewer' || $role == 'event_co_chair') {
                if (sizeof($abstract->scores)) {
                    foreach ($abstract->scores as $key => $score) {
                        if ($eventUser['user_id'] == $score['user_id']) {
                            $data['your_score'] = $score['score']['average'];
                        }
                        if (!isset($data['your_score'])) {
                            $data['your_score'] = null;
                        }
                    }
                } else {
                    $data['your_score'] = null;
                }
            } else {
                $data['avg_score'] = $abstract->score;
                $data['score_count'] = $abstract->scores->count();
                $data['rank'] = $abstract->rank;
                $data['selection_status'] = $abstract->selection_status;
                $data['presentation_invite'] = $abstract->presentation_invite;
                $data['presentation_invite_accepted_or_rejected_date'] = $abstract->presentation_invite_date ? $this->clientTimezoneDateTime($timeZone, $abstract->presentation_invite_date) : '';
            }
            $data['created_at'] = $abstract->created_at ? $this->clientTimezoneDateTime($timeZone, $abstract->created_at) : '';
            $data['submitted_at'] = $abstract->submission_date ? $this->clientTimezoneDateTime($timeZone, $abstract->submission_date) : '';

            if ($role != 'event_reviewer' && $role != 'event_co_chair') {
                $data['comment'] = null;
                $data['speaker_comments'] = null;
            }

            // Only add submitter data if not hiding authors and submitter exists
            if (!$hideAuthorsFromReviewers && $submitter) {
                $maskContact = shouldRestrictContact($submitter, $authEventUser);
                $data['submitter_first_name'] = $submitter->first_name;
                $data['submitter_last_name'] = $submitter->last_name;
                $data['submitter_company'] = $submitter->company;
                $data['submitter_mobile_number'] = $maskContact ? $maskedValue : $submitter->mobile;
                $data['submitter_country'] = $submitter->country;
                $data['submitter_job_title'] = $submitter->job_title;
                $data['submitter_email'] = $maskContact ? $maskedValue : $submitter->email;
                $data['submitter_industry'] = $submitter->industry_code;
            }

            // Get abstract data and form settings
            $abstractData = $abstract->data;
            $formSettings = $event->form_settings;

            // Iterate through form settings
            foreach ($formSettings as $formSetting) {
                // Get field ID
                $fieldId = $formSetting['field_id'];

                // Filter abstract data for the current field ID
                $abstractItem = array_filter(
                    (array) $abstractData,
                    fn($data) => $fieldId === $data['field_id']
                );

                // Check if the field is enabled
                $enabled = isset($formSetting['enabled']) ? $formSetting['enabled'] : true;

                if ($abstractItem) {
                    // Retrieve the first matching abstract item
                    $abstractItem = reset($abstractItem);

                    if ($fieldId !== 'subsection' && $enabled) {
                        // Set field value from abstract item or default to empty
                        $data[$fieldId] = $abstractItem['value'] ?? '';

                        // For attachment fields, concatenate filenames
                        if ($fieldId === 'attachment' && isset($abstractItem['value'][0]['filename'])) {
                            $data[$fieldId] = implode(',', array_column($abstractItem['value'], 'filename'));
                        }
                    }
                } else {

                    if ($fieldId !== 'subsection' && $enabled) {
                        // Set field value to empty
                        $data[$fieldId] = '';
                    }
                }
            }

            //setting export abstract users data - skip if hiding authors
            if (!$hideAuthorsFromReviewers) {
                foreach ($people as $key => $person) {
                    $maskContact = shouldRestrictContact($person, $authEventUser);

                    $data["person_" . ($key + 1) . "_first_name"] = $person['name'];
                    $data["person_" . ($key + 1) . "_last_name"] = $person['last_name'];
                    if ($role != 'event_reviewer' && $role != 'event_co_chair')
                        $data["person_" . ($key + 1) . "_email"] = $maskContact ? $maskedValue : $person['email'];
                    $data["person_" . ($key + 1) . "_job_title"] = $person['job_title'];
                    $data["person_" . ($key + 1) . "_company"] = $person['company'];
                    if ($role != 'event_reviewer' && $role != 'event_co_chair') {
                        $data["person_" . ($key + 1) . "_address"] = $person['company_address'];
                        $data["person_" . ($key + 1) . "_city"] = $person['city'];
                        $data["person_" . ($key + 1) . "_state"] = $person['state'];
                    }
                    $data["person_" . ($key + 1) . "_country"] = $person['country'];
                    if ($role != 'event_reviewer' && $role != 'event_co_chair')
                        $data["person_" . ($key + 1) . "_phone"] = $maskContact ? $maskedValue : $person['phone'];
                    $data["person_" . ($key + 1) . "_industry_code"] = $person['industry_code'];
                    $data["person_" . ($key + 1) . "_biography"] = $person['biography'];
                    $data["person_" . ($key + 1) . "_headshot"] = $person['avatar'] ? config('app.url') . '/' . $person['avatar']['value'] : null;
                    $data["person_" . ($key + 1) . "_company_logo"] = $person['company_logo'] ? config('app.url') . '/' . $person['company_logo']['value'] : null;
                    $data["person_" . ($key + 1) . "_author"] = str_contains($person['abstract_roles'], "author") && !str_contains($person['abstract_roles'], "co_author") ? "Yes" : "No";
                    $data["person_" . ($key + 1) . "_presenter"] = str_contains($person['abstract_roles'], "presenter") ? "Yes" : "No";
                    $data["person_" . ($key + 1) . "_co_author"] = str_contains($person['abstract_roles'], "co_author") ? "Yes" : "No";
                }
            }

            $excelData[] = $data;
        }
        $disclaimerHeading = ['The data collected through the ' . $event->event_name . ' campaign are intended for and confidential to the addressee and the event organiser, dmg events. They may be legally privileged and should not be communicated to, or relied upon, by any other party without our written consent. '];
        $heading = array_merge($heading, $abstractHeading, $personHeading);
        $finalData = array_merge([$disclaimerHeading, $heading], $excelData);

        $excel = Excel::download(new AbstractExportClass($finalData, count($abstractHeading), $role), 'abstracts.xlsx');

        return $excel;
    }

    public function calculateAbstractScore(Abstracts $abstract): Abstracts
    {
        $abstract->update([
            'score' => $abstract->scores()->avg('score->average')
        ]);

        return $abstract;
    }

    public function calculateAbstractsRank($eventId, $mode)
    {
        $abstracts = Abstracts::where('score', '!=', null)
            ->where('submission_status', '=', 'submitted')
            ->where('event_id', '=', $eventId)
            ->orderBy('score', 'DESC')
            ->get();

        $i = 1;
        foreach ($abstracts as $abstract) {
            $abstract->rank = $i;
            $abstract->save();
            $i++;
        }

        if($mode == 'delete'){
            // Reset ranks for abstracts without scores
            Abstracts::whereNull('score')
            ->where('submission_status', 'submitted')
            ->where('event_id', $eventId)
            ->whereNotNull('rank')
            ->update(['rank' => null]);
        }

        return true;
    }

    public function getReviewerAbstract(EventUser $eventUser, $reviewer = false, $request = false)
    {
        $eventProfileData = $eventUser->event_profile_data;

        $formSettings = collect($eventUser->event->form_settings->toArray());
        $dataFields = $formSettings->pluck('field_id')->toArray();

        $query = Abstracts::query()->whereEventId($eventUser->event->id)
            ->join('users as u', 'abstracts.user_id', '=', 'u.id');


        $this->reviewerAbstractQuery($eventProfileData, $dataFields, $query, $eventUser->user->id, $reviewer);
        $query->addSelect(['abstracts.id']);

        if ($request) {
            $arguments = $this->arguments($request);
            // Convert the Eloquent builder to a query builder
            $queryBuilder = $query->getQuery();
            // filter using submitter abstracts data
            $this->applyFilter($queryBuilder, $arguments, true);
        }

        return $query->get();
    }

    /**
     * @param $eventProfileData
     * @param array $dataFields
     * @param $query
     * @param $reviewerId
     */
    public function reviewerAbstractQuery($eventProfileData, array $dataFields, $query, $reviewerId, $reviewer = true, $view = false)
    {
        if ($reviewer) {
            if ($eventProfileData['category'] ?? false) {
                $key = array_search('category', $dataFields);
                $values = $eventProfileData['category'];
                $query->where(function ($query) use ($values, $key) {
                    foreach ($values as $value) {
                        //$query->orWhereRaw(DB::raw("JSON_EXTRACT(abstracts.data, '$[$key].value') = ?"), [$value['value']]);
                        $query->orWhereJsonContains('abstracts.data', [
                            ['field_id' => 'category', 'value' => $value['value']]
                        ]);
                    }
                });
            }

            if ($eventProfileData['subcategory'] ?? false) {
                $key = array_search('subcategory', $dataFields);
                $values = $eventProfileData['subcategory'];

                $query->where(function ($query) use ($values, $key) {
                    foreach ($values as $value) {
                        //$query->orWhereRaw(DB::raw("JSON_EXTRACT(abstracts.data, '$[$key].value') = ?"), [$value['value']]);
                        $query->orWhereJsonContains('abstracts.data', [
                            ['field_id' => 'subcategory', 'value' => $value['value']]
                        ]);
                    }
                });
            }

            //excluding abstracts from excluded companies
            if ($eventProfileData['excluded_companies'] ?? false) {
                if (!$view) {
                    $excludedCompanies = $eventProfileData['excluded_companies'];
                    foreach ($excludedCompanies as $excludedCompany) {
                        $query->where('u.company', '!=', $excludedCompany['value']);
                    }
                }
            }
        }
        //selecting only submitted abstracts
        $query->where('abstracts.submission_status', '=', 'submitted')
            ->where('abstracts.user_id', '!=', $reviewerId);

        return $query;
    }

    public function pdfExportAbstract($abstracts, $role, $event, $isAwardSubmission = false)
    {
        // Score settings
        $scoresArray = $event->score_settings;
        // Create an array to hold score labels
        $scoreLabels = [];

        // Get score labels and append to score labels array
        if (isset($scoresArray['scores'])) {
            foreach ($scoresArray['scores'] as $value) {
                if (!empty($value['label'])) {
                    $scoreLabels[] = $value['label'];
                }
            }
        }

        $abstracts = [
            'abstracts' => $abstracts,
            'role' => $role,
            'form_settings' => $event->form_settings,
            'scoreLabels' => $scoreLabels,
            'isAwardSubmission' => $isAwardSubmission,
            'hideAuthorsFromReviewers' => ($role == 'event_reviewer' || $role == 'event_co_chair') && $event->slug_name === config('eventBasedRequirements.hideAuthorsFromReviewersSlug')
        ];

        //abstract export to pdf
        $pdf = App::make('dompdf.wrapper');
        $pdf->loadView('pdf.abstract', $abstracts);
        return $pdf->stream();
    }

    public function countNoOfVotesForAbstract(Abstracts $abstract): Abstracts
    {
        $abstract->update([
            'no_of_votes' => $abstract->scores()->count()
        ]);

        return $abstract;
    }


    /**
     * getScheduleMailData()
     *
     * @return array
     */
    public function getScheduleMailData(object $data):Array{
        $filter     = json_decode($data->filter, 1);
        $to         = [];
        $email      = [];
        // storing recipients
        if(isset($filter['recipients'])){
            foreach($filter['recipients'] as $recipients){
                $to[] = $recipients['email'];
            }
        }
        // getting all users if there is no user selected
        if(!isset($filter['users']) || count($filter['users']) <= 0){
            $filter['users'] = $this->getAllUsersByEmailType('Abstract');
        }

        $formatedFilter = [];
        foreach($this->abstractFields as $fields){
            $arrayColumn = (isset($filter[$fields])) ? $filter[$fields] : [];
            $formatedFilter[$fields] = array_column($arrayColumn, 'value');
        }
        $event   = Event::find($data->event_id);
        $request = new Request();
        $request->replace([
            'paging'  => 'all',
            'sort'    => '{}',
            'filter'  => json_encode($formatedFilter),
            'role'    => 'event_admin',
            'event'   => $event,
        ]);
        $result = $this->listing($request, false);
        //getting users emails
        foreach($result as $key => $res){
            $abstract = Abstracts::with(['users'])->find($res->id);
            $body     = $this->replaceMacros((array)$res, $data, $event->event_name, $abstract);
            $bcc      = [];
            foreach($filter['users'] as $user){
                switch($user['value']){
                    case "Submitters":
                        $users = $abstract->users('abstract_submitter')->get();
                        break;
                    case "Authors":
                        $users = $abstract->users('abstract_author')->get();
                        break;
                    case "Co-Authors":
                        $users = $abstract->users('abstract_co_author')->get();
                        break;
                    case "Presenters":
                        $users = $abstract->users('abstract_presenter')->get();
                        break;
                    case "Abstract users":
                        $users = $abstract->users()->get();
                        break;

                }
                $mails = getUsersEmails($users);
                $bcc   = [...$bcc, ...$mails['emails']];
            }
            $bcc = array_unique($bcc);
            $email[] = [
                'to'          => $to,
                'bcc'         => $bcc,
                'subject'     => $data->subject,
                'body'        => $body,
                'attachments' => $data->attachments,
                'event'       => $event,
                'event_id'    => $data->event_id,
                'id'          => $data->id,
            ];

        }
        return $email;
    }
    /**
     * Replace macros
     *
     * @return string
     */
    public function replaceMacros(array $row, object $data, string $event_name, $abstract): String {
        $variables  = config('schedule_mail_settings')->Abstract->variables;
        $category   = $subcategory = $title = "";
        foreach(json_decode($row['data'], 1) as $key => $value){

            switch($value['field_id']){
                case "category":
                    $category       = $value['value'];
                    break;
                case "subcategory":
                    $subcategory    = $value['value'];
                    break;
                case "title":
                    $title          = $value['value'];
                    break;
            }
        }

        $presentersName = $abstract->users('abstract_presenter')->get()->pluck('name')->toArray();
        $presentersNames = empty($presentersName) ? '' : implode(',', $presentersName);
        $submitterName      = $row['submitter_name'];
        $abstracId          = $row['id'];
        $submitterCompany   = $row['company'];
        $body               = $data->body;
        $body               = Str::replace("{category}", $category, $body);
        $body               = Str::replace("{abstract_id}", $abstracId, $body);
        $body               = Str::replace("{sub_category}", $subcategory, $body);
        $body               = Str::replace("{title}", $title, $body);
        $body               = Str::replace("{submitter_name}", $submitterName, $body);
        $body               = Str::replace("{submitter_company}", $submitterCompany, $body);
        $body               = Str::replace("{event_name}", $event_name, $body);
        $body = Str::replace("{presenters_name}", $presentersNames, $body);
        return $body;
    }
}