<?php

namespace App\Repositories;

use App\Models\Event;
use App\Models\PublishedSession;
use App\Models\Session;
use App\Models\SessionUser;
use App\Models\Slot;
use App\Models\SlotUser;
use App\Models\EventUser;
use App\Rules\SessionDateRule;
use DateTime;
use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use App\Jobs\SendSessionUpdatedMail;
use App\Jobs\SendSessionCreatedMail;
use App\Support\Collection;
use Carbon\CarbonPeriod;
use App\Lib\EmailSetupHelper;
use Log;
use Illuminate\Support\Str;
use App\Models\Presentation;
use App\Models\Abstracts;
use App\Models\ChairPerson;
use App\Models\PublishedSessionHistory;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\ExportSessionsClass;

class SessionRepository extends Repository
{

    protected $emailSetup;

    const KEY_TO_SESSION_USERS = "session_create_session_users";
    const KEY_TO_SLOT_USERS = "session_create_slot_users";

    protected $sessionFields = ["published_status", "rooms", "types"];

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

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

    public function query($as = null)
    {
        $model = $this->model();
        return $model->newQuery();
    }

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

        $sessionOverview = $request->get('sessionOverview', false);

        //todo: Optimize this to single query using joins
        $callback = function (Builder $query, $arguments) {
            $query->with([
                'users' => function ($query) use ($arguments) {
                    $query->select('users.id', 'users.email', 'users.first_name', 'users.last_name', 'users.company', 'users.country', 'users.avatar', 'users.job_title', 'eu.verification_status')
                        ->join('event_user as eu', function ($join) use ($arguments) {
                            $join->on('eu.user_id', '=', 'users.id')
                                ->where('eu.event_id', '=', $arguments->event->id);
                        })
                        ->whereIn('eu.id', function ($subquery) use ($arguments) {
                            $subquery->select(DB::raw('MIN(id)'))
                                ->from('event_user')
                                ->where('event_id', $arguments->event->id)
                                ->groupBy('user_id');
                        });
                    ;
                },
                'slots' => function ($query) use ($arguments) {
                    if (!$arguments->filter)
                        return $query->orderBy('order', 'asc');

                    $filter = json_decode($arguments->filter);

                    if ($filter->session_date ?? false) {
                        $value = $filter->session_date;
                        $query->where(
                            function ($query) use ($value) {
                                $query->orWhere('slots.start_date', 'LIKE', '%' . $value . '%');
                            }
                        );
                    }

                    $query->orderBy('order', 'asc');
                },
                'slots.users' => function ($query) use ($arguments) {
                    $query->select('users.id', 'users.email', 'users.first_name', 'users.last_name', 'users.company', 'users.country', 'users.avatar', 'users.job_title', 'eu.verification_status')
                        ->join('event_user as eu', function ($join) use ($arguments) {
                            $join->on('eu.user_id', '=', 'users.id')
                                ->where('eu.event_id', '=', $arguments->event->id);
                        })
                        ->whereIn('eu.id', function ($subquery) use ($arguments) {
                            $subquery->select(DB::raw('MIN(id)'))
                                ->from('event_user')
                                ->where('event_id', $arguments->event->id)
                                ->groupBy('user_id');
                        });
                },
                'slots.presentation.presenters' => function ($query) {
                    $query->select('users.id', 'users.email', 'users.first_name', 'users.last_name');
                },
                'slots.abstract'
            ])->orderBy('sessions.start_date', 'asc');
            return $query;
        };

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

        if ($sessionOverview) {
            return $query;
        }

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



    public function scope($arguments, $callback = null)
    {
        $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);
    }



    public function selectColumns(): array
    {
        return [
            'sessions.id',
            'sessions.event_id',
            'sessions.start_date',
            'sessions.end_date',
            'sessions.title',
            'sessions.type',
            'sessions.room',
            'sessions.category',
            'sessions.hosted',
            'sessions.assets',
            'sessions.tags',
            'sessions.live_stream',
            'sessions.color',
            'sessions.hide_session',
            'sessions.description',
            'sessions.heading',
            'sessions.sub_heading',
            'sessions.session_categories',
            'sessions.description2',
            'sessions.published_status',
            'sessions.exclude_from_print',
            'sessions.created_at',
            'sessions.updated_at',
        ];
    }

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

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

        return $query;
    }

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

        $filter = is_array($arguments->filter) ? (object) $arguments->filter : json_decode($arguments->filter);

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

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

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

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

        if ($filter->session_date ?? false) {
            $value = $filter->session_date;
            $query->where(function ($query) use ($value) {
                $query->whereRaw('? between sessions.start_date and sessions.end_date', [$value]);
                $query->orWhere('sessions.start_date', 'LIKE', '%' . $value . '%');
            });
        }

        // Handle session categories filters
        foreach ($filter as $key => $values) {
            if (strpos($key, 'session_categories_') === 0 && !empty($values)) {
                $fieldId = str_replace('session_categories_', '', $key);
                $query->where(function ($query) use ($fieldId, $values) {
                    foreach ($values as $value) {
                        $query->orWhereJsonContains('sessions.session_categories', [['id' => $fieldId, 'options' => [['value' => $value]]]]);
                    }
                });
            }
        }

        return $query;
    }

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

        return $query;
    }

    /**
     * @throws Exception
     */
    public function createOrUpdate(Request $request)
    {
        $sessionId = $request->get('id');
        $input = $request->input();
        $event = $request->get('event');
        $isEdit = false;
        $isUpdated = false;
        $validator = Validator::make($input, [
            'start_date' => ['required', new SessionDateRule],
            'end_date' => ['required', new SessionDateRule],
            'type' => ['required', 'string', 'max:255'],
            'title' => ['required', 'string', 'max:255'],
            'room' => ['string', 'max:255', 'nullable'],
            'hosted' => ['string', 'max:255', 'nullable'],
            'assets' => ['string', 'nullable'],
            'tags' => ['string', 'nullable'],
            'color' => ['string', 'nullable'],
            'hide_session' => ['boolean'],
            'break_session' => ['boolean'],
            'featured_by' => ['boolean'],
            'sponsored_by' => ['boolean'],
            'category' => ['string', 'max:255', 'nullable'],
            'description' => ['string', 'nullable'],
            'heading' => ['string', 'nullable'],
            'sub_heading' => ['string', 'nullable'],
            'description2' => ['string', 'nullable'],
            'session_categories' => ['nullable'],
            'sponsored_logo_links' => ['nullable'],
            'cta_sessions' => ['nullable'],
            'live_stream' => ['nullable', 'url'],
        ]);

        if ($validator->fails())
            validationErrorResponse($validator->errors());
        $validInputs = $validator->validated();
        if (!$sessionId) {
            $validInputs['event_id'] = $event->id;
            $session = Session::create($validInputs);
            $session->save();
            $session->refresh();
        } else {
            $session = Session::find($sessionId);
            if (!$session)
                validationErrorResponse(['Invalid session id']);

            $session->fill($validInputs);

            // keep changes field lists
            $changes = $session->getDirty();

            // changing session published status
            if ($changes && $session->published_status) {
                $session->published_status = false;
                $isUpdated = true;
            }

            $session->save();
            // keep status its edit or not
            $isEdit = true;
        }

        $sessionUsers = $input['users'] ?? [];
        $removedSessionUsers = $input['removed_users'] ?? [];
        $sessionUserUpdated = $this->manageSessionUsers($session, $sessionUsers, $removedSessionUsers, $isEdit, $isUpdated);

        $slots = $input['slots'] ?? [];
        $removedSlots = $input['removed_slots'] ?? [];
        $slotUpdated = $this->manageSlots($session, $slots, $removedSlots, $isEdit, $isUpdated);

        /**
         *Send mail for newly created session
         *Mail send to session users and slot users
         */
        if (!$sessionId) {
            /**
             * Get the email coonfig
             * check the email send option is enabled or disabled
             */
            $emailSessionContents = $this->getEmailContents($session->event->id, self::KEY_TO_SESSION_USERS);
            $emailSlotContents = $this->getEmailContents($session->event->id, self::KEY_TO_SLOT_USERS);


            if (!empty($sessionUsers) && $emailSessionContents->status) {
                foreach ($sessionUsers as $user) {
                    // dispatch(new SendSessionCreatedMail($session, $user, 'added to session', [], $emailSessionContents));
                    $this->sendSessionUserAndSlotUserCreatedMail($session, $user, 'added to session', null, $emailSessionContents);
                }
            }
            if (!empty($slots) && $emailSlotContents->status) {
                foreach ($slots as $slot) {
                    $slotUsers = $slot['users'];
                    if (!empty($slotUsers)) {
                        foreach ($slotUsers as $user) {
                            // dispatch(new SendSessionCreatedMail($session, $user, 'added to slot', $slot, $emailSlotContents));
                            $this->sendSessionUserAndSlotUserCreatedMail($session, $user, 'added to slot', $slot, $emailSlotContents);
                        }
                    }
                }
            }
        }

        // sending published session update mail
        if ($isUpdated || $sessionUserUpdated || $slotUpdated) {
            dispatch(new SendSessionUpdatedMail($session, $user = false, 'session change'));
        }

        return $session;
    }

    public function sendSessionUserAndSlotUserCreatedMail($session, $user, $subject, $slot = null, $contents)
    {
        dispatch(new SendSessionCreatedMail($session, $user, $subject, $slot, $contents));
    }

    public function getEmailContents($eventId, $key)
    {
        return $this->emailSetup->getEmailData($eventId, $key);
    }


    public function manageSessionUsers(Session $session, $users, $removedUsers = [], $isEdit, $isUpdated)
    {
        $emailSessionContents = $this->getEmailContents($session->event->id, self::KEY_TO_SESSION_USERS);
        foreach ($users as $user) {
            $sessionUser = SessionUser::whereEventId($session->event_id)
                ->whereSessionId($session->id)
                ->whereUserId($user['id'])
                ->first();
            if (!$sessionUser) {
                $session->users()->attach($user['id'], ['event_id' => $session->event_id, 'role' => $user['role'], 'is_moderator' => isset($user['is_moderator']) ? (int) $user['is_moderator'] : 0, 'order' => isset($user['order']) ? $user['order'] : null]);
                if ($isEdit) {
                    $this->sendSessionUserAndSlotUserCreatedMail($session, $user, 'added to session', null, $emailSessionContents);
                }
                if ($isEdit && $session->published_status) {
                    // changing session published status
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }
            } else {
                $sessionUser->role = $user['role'];
                $sessionUser->is_moderator = isset($user['is_moderator']) ? (int) $user['is_moderator'] : 0;
                $sessionUser->order = isset($user['order']) ? $user['order'] : null;
                //keep changes field lists
                $changes = $sessionUser->getDirty();
                // changing session published status
                if ($changes && $session->published_status) {
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }
                $sessionUser->save();
            }
        }

        // removing users from slot
        if (!empty($removedUsers)) {
            SessionUser::whereSessionId($session->id)
                ->whereEventId($session->event_id)
                ->whereIn('user_id', $removedUsers)
                ->delete();
            // changing session published status
            if ($session->published_status) {
                $session->published_status = false;
                $session->save();
                $isUpdated = true;
            }
        }
        return $isUpdated;
    }

    /**
     * @throws Exception
     */
    public function manageSlots(Session $session, $slots, $removedSlots = [], $isEdit, $isUpdated)
    {
        foreach ($slots as $slotData) {
            $slotId = $slotData['id'] ?? null;

            // checking using presentation array
            $presentationId = $slotData['presentation'] ?? false ? $slotData['presentation']['id'] : null;

            // checking using abstract array
            $abstractId = $slotData['abstract'] ?? false ? $slotData['abstract']['id'] : null;

            if (!$presentationId && !isset($slotData['presentation'])) {
                // checking using presentation id
                $presentationId = $slotData['presentation_id'] ?? false ? $slotData['presentation_id'] : null;
            }

            if (!$abstractId && !isset($slotData['abstract'])) {
                // checking using abstract id
                $abstractId = $slotData['abstract_id'] ?? false ? $slotData['abstract_id'] : null;
            }

            if ($slotId) {
                $slot = Slot::find($slotId);
                $slot->start_date = $slotData['start_date'];
                $slot->end_date = $slotData['end_date'];
                $slot->duration = $slotData['duration'];
                $slot->title = $slotData['title'];
                $slot->type = $slotData['type'];
                $slot->break_slot = isset($slotData['break_slot']) ? $slotData['break_slot'] : false;
                $slot->featured_by = isset($slotData['featured_by']) ? $slotData['featured_by'] : false;
                $slot->sponsored_by = isset($slotData['sponsored_by']) ? $slotData['sponsored_by'] : false;
                $slot->order = $slotData['order'];
                $slot->reserved = $slotData['reserved'];
                $slot->exclude_from_website = $slotData['exclude_from_website'];
                $slot->presentation_id = $presentationId;
                $slot->abstract_id = $abstractId;

                // keep changes field lists
                $changes = $slot->getDirty();
                // changing session published status
                if ($changes && $session->published_status) {
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }
                $slot->save();
            } else {
                $slot = new Slot([
                    'event_id' => $session->event_id,
                    'session_id' => $session->id,
                    'start_date' => $slotData['start_date'],
                    'end_date' => $slotData['end_date'],
                    'duration' => $slotData['duration'],
                    'title' => $slotData['title'],
                    'type' => $slotData['type'],
                    'break_slot' => isset($slotData['break_slot']) ? $slotData['break_slot'] : false,
                    'featured_by' => isset($slotData['featured_by']) ? $slotData['featured_by'] : false,
                    'sponsored_by' => isset($slotData['sponsored_by']) ? $slotData['sponsored_by'] : false,
                    'order' => $slotData['order'] ?? null,
                    'reserved' => $slotData['reserved'] ?? false,
                    'exclude_from_website' => $slotData['exclude_from_website'] ?? false,
                    'presentation_id' => $presentationId,
                    'abstract_id' => $abstractId,
                ]);
                $slot->save();
                $slot->refresh();
                if ($isEdit && $session->published_status) {
                    // changing session published status
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }
            }

            // abstract_presentation_track save
            if ($slot->abstract_id) {
                $this->setAbstractPresentationTrack($slot, ['id' => $slot->abstract_id, 'model' => Abstracts::class], 'abstract');
            }
            if ($slot->presentation_id) {
                $this->setAbstractPresentationTrack($slot, ['id' => $slot->presentation_id, 'model' => Presentation::class], 'presentation');
            }

            $slotUsers = $slotData['users'] ?? [];
            $removedSlotUsers = $slotData['removed_users'] ?? [];
            $slotUserUpdated = $this->manageSlotUsers($session, $slot, $slotUsers, $removedSlotUsers, $isEdit, $isUpdated);
            if ($slotUserUpdated) {
                $isUpdated = true;
            }
        }

        // removing users from slot
        if (!empty($removedSlots)) {
            Slot::whereIn('id', $removedSlots)
                ->delete();

            // changing session published status
            if ($session->published_status) {
                $session->published_status = false;
                $session->save();
                $isUpdated = true;
            }
        }

        $this->generateSlotIdentifierForEvent($session->event);

        return $isUpdated;
    }

    // set abstract presentation track
    public function setAbstractPresentationTrack($slot, $dataArray, $fieldKey)
    {
        if ($dataArray['id']) {
            $model = $dataArray['model']::where('id', $dataArray['id'])->first();
            $titleField = collect($model->data)->firstWhere('field_id', 'title');
            $presenters = $fieldKey == 'abstract' ? $model->users('abstract_presenter')->pluck('id')->toArray()
                : $model->presenters->pluck('id')->toArray();

            $slot->abstract_presentation_track = [
                'field' => $fieldKey,
                'title' => $titleField ? $titleField['value'] : '',
                'speakers' => $presenters,
            ];
            $slot->save();
        }
    }

    /**
     * @throws Exception
     */
    public function generateSlotIdentifierForEvent(Event $event)
    {
        $sessions = $event->sessions;
        foreach ($sessions as $session) {
            $this->generateSlotIdentifier($session);
        }
    }

    // /**
    //  * @throws Exception
    //  */
    // public function generateSlotIdentifier(Session $session)
    // {
    //     $slots = $session->slots()->get();
    //     $sessionIdentifier = $this->getSessionIdentifier($session);

    //     Slot::query()
    //         ->whereIn('id', $slots->pluck('id')->toArray())
    //         ->update([
    //             'identifier' => DB::raw("CONCAT(\"$sessionIdentifier\",LEFT(slots.type,1),slots.order)")
    //         ]);
    // }

    // /**
    //  * @throws Exception
    //  */
    // public function getSessionIdentifier(Session $session): string
    // {
    //     $sessions = Session::query()->where('event_id', '=', $session->event_id)
    //         ->orderBy('start_date', 'asc')->get();

    //     $day = 0;
    //     $currentDay = null;
    //     $sessionDay = $session->start_date->format('Y-m-d');
    //     foreach ($sessions as $s) {
    //         $sDay = $s->start_date->format('Y-m-d');
    //         if ($sDay != $currentDay) {
    //             $day++;
    //             if ($sessionDay == $sDay)
    //                 break;
    //             $currentDay = $sDay;
    //         }
    //     }

    //     $daySessions = Session::query()->where('event_id', '=', $session->event_id)
    //         ->where('start_date', 'like', '%' . $sessionDay . '%')
    //         ->orderBy('start_date', 'asc')
    //         ->get();
    //     $sessionIndex = 0;
    //     foreach ($daySessions as $daySession) {
    //         $sessionIndex++;
    //         if ($daySession->start_date == $session->start_date)
    //             break;
    //     }

    //     return 'D' . $day . 'S' . $sessionIndex;
    // }


    /**
     * @throws Exception
     */
    public function generateSlotIdentifier(Session $session)
    {
        // session slots
        $slots = $session->slots()->get();

        $i = 0;
        foreach ($slots as $key => $s) {
            // find slot model
            $slot = Slot::find($s->id);
            // fetch session identifier
            $sessionIdentifier = $this->getSessionIdentifier($session, $s);
            // slot number
            $slotNumber = $session->start_date->format('Y-m-d') == $slot->start_date->format('Y-m-d') ? ++$key : ++$i;
            // slot identifier
            $slot->identifier = $sessionIdentifier . mb_substr($s->type, 0, 1) . $slotNumber;
            // slot save
            $slot->save();
        }
    }

    /**
     * @throws Exception
     */
    public function getSessionIdentifier(Session $session, Slot $slot): string
    {
        // get all session dates
        $sessionDates = Session::select(DB::raw('DATE(start_date) AS start_date'), DB::raw('DATE(end_date) AS end_date'))->where('event_id', '=', $session->event_id)->orderBy('start_date', 'asc')
            ->distinct()->get();

        $dates = [];
        foreach ($sessionDates as $sessionDate) {
            // single session period
            $period = CarbonPeriod::create($sessionDate['start_date'], $sessionDate['end_date']);
            // Convert the period to an array of dates
            $dates = array_unique(array_merge($dates, $period->toArray()));
        }

        // reordering index
        $resultDates = array_values($dates);

        $day = 0;
        $currentDay = null;
        // session index
        $sessionIndex = 0;
        // slot start date
        $slotDay = $slot->start_date->format('Y-m-d');
        foreach ($resultDates as $s) {
            // session day
            $sDay = $s->format('Y-m-d');
            if ($sDay != $currentDay) {
                $day++;
                if ($slotDay == $sDay)
                    break;
                $currentDay = $sDay;
            }
        }

        // all sessions from a day
        $daySessions = Session::query()
            ->where(function ($q) use ($slotDay) {
                $q->where('start_date', 'like', '%' . $slotDay . '%')
                    ->orWhere('end_date', 'like', '%' . $slotDay . '%');
            })
            ->where('event_id', '=', $session->event_id)
            ->get();

        foreach ($daySessions as $key => $daySession) {
            // session index
            $sessionIndex = ++$key;
            // condition
            if ($daySession->start_date->format('Y-m-d') == $session->start_date->format('Y-m-d') && $session->id == $daySession->id)
                break;
        }


        return 'D' . $day . 'S' . $sessionIndex;
    }


    public function manageSlotUsers(Session $session, Slot $slot, $users = [], $removedUsers = [], $isEdit, $isUpdated)
    {
        $emailSlotContents = $this->getEmailContents($session->event->id, self::KEY_TO_SLOT_USERS);
        foreach ($users as $user) {
            $slotUser = SlotUser::whereEventId($slot->event_id)
                ->whereSlotId($slot->id)
                ->whereUserId($user['id'])
                ->first();
            if (!$slotUser) {
                $slot->users()->attach($user['id'], ['event_id' => $slot->event_id, 'role' => $user['role'], 'is_moderator' => isset($user['is_moderator']) ? (int) $user['is_moderator'] : 0, 'order' => isset($user['order']) ? $user['order'] : null]);
                if ($isEdit) {
                    $this->sendSessionUserAndSlotUserCreatedMail($session, $user, 'added to slot', $slot, $emailSlotContents);
                }
                if ($isEdit && $session->published_status) {
                    // changing session published status
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }
            } else {
                $slotUser->role = $user['role'];
                $slotUser->is_moderator = isset($user['is_moderator']) ? (int) $user['is_moderator'] : 0;
                $slotUser->order = isset($user['order']) ? $user['order'] : null;
                // keep changes field lists
                $changes = $slotUser->getDirty();
                // changing session published status
                if ($changes && $session->published_status) {
                    $session->published_status = false;
                    $session->save();
                    $isUpdated = true;
                }

                $slotUser->save();
            }
        }

        // removing users from slot
        if (!empty($removedUsers)) {
            SlotUser::whereSlotId($slot->id)
                ->whereEventId($slot->event_id)
                ->whereIn('user_id', $removedUsers)
                ->delete();

            // changing session published status
            if ($session->published_status) {
                $session->published_status = false;
                $session->save();
                $isUpdated = true;
            }
        }

        return $isUpdated;
    }

    public function getSession($sessionId, $eventId)
    {
        // Get auth user and event user for contact restriction check once
        $authUser = authUser();
        $authEventUser = getEventUser(Event::find($eventId), $authUser);
        $isLimitedAccess = $authEventUser && $authEventUser->access_level === 'limited';
        $vipTypes = implode("','", VIP_PROFILE_TYPES);
        $allowedCategories = $isLimitedAccess ? ($authEventUser->vip_categories ?? []) : [];
        $contactRestrictedSql = $isLimitedAccess
            ? (empty($allowedCategories)
                ? "(users.profile_type IN ('{$vipTypes}')) as contact_restricted"
                : "(users.profile_type IN ('{$vipTypes}') AND users.profile_type NOT IN ('" . implode("','", $allowedCategories) . "')) as contact_restricted")
            : "0 as contact_restricted";

        //todo: Optimize this to single query using joins
        $session = Session::with([
            'users' => function ($query) use ($eventId, $contactRestrictedSql) {
                $query->select(
                    'users.id', 
                    'users.email', 
                    'users.first_name', 
                    'users.last_name', 
                    'users.profile_type', 
                    'eu.verification_status',
                    DB::raw($contactRestrictedSql)
                )
                    ->join('event_user as eu', function ($join) use ($eventId) {
                        $join->on('eu.user_id', '=', 'users.id')
                            ->where('eu.event_id', '=', $eventId);
                    })
                    ->whereIn('eu.id', function ($subquery) use ($eventId) {
                        $subquery->select(DB::raw('MIN(id)'))
                            ->from('event_user')
                            ->where('event_id', $eventId)
                            ->groupBy('user_id');
                    });
            },
            'slots' => function ($query) {
                $query->orderBy('order', 'asc');
            },
            'slots.users' => function ($query) use ($eventId, $contactRestrictedSql) {
                $query->select(
                    'users.id', 
                    'users.email', 
                    'users.first_name', 
                    'users.last_name', 
                    'users.profile_type', 
                    'eu.verification_status',
                    DB::raw($contactRestrictedSql)
                )
                    ->join('event_user as eu', function ($join) use ($eventId) {
                        $join->on('eu.user_id', '=', 'users.id')
                            ->where('eu.event_id', '=', $eventId);
                    })
                    ->whereIn('eu.id', function ($subquery) use ($eventId) {
                        $subquery->select(DB::raw('MIN(id)'))
                            ->from('event_user')
                            ->where('event_id', $eventId)
                            ->groupBy('user_id');
                    });
            },
            'slots.presentation.presenters' => function ($query) {
                $query->select('users.id', 'users.email', 'users.first_name', 'users.last_name');
            },
            'slots.abstract'
        ])->find($sessionId)->append('slug_name');
        
        return $session;
    }

    public function delete(Session $session)
    {
        $session->users()->detach();

        $slots = $session->slots()->get();
        foreach ($slots as $slot) {
            $this->deleteSlot($slot);
        }

        $publishedSessions = PublishedSession::where('session_id', '=', $session->id)->first();

        if ($publishedSessions) {
            // Delete published session history first
            PublishedSessionHistory::where('published_session_id', $publishedSessions->id)->delete();
            $publishedSessions->delete();
        }

        $session->delete();
    }

    public function publish(Session $session)
    {
        $data = $this->getPublishedSessionData($session);

        $loginUser = authUser();

        $publishedSession = PublishedSession::where('session_id', '=', $data->id)->first();


        if ($publishedSession && $publishedSession->published_data) {
            $this->savePublishedSessionHistory($publishedSession);
        }


        if (!$publishedSession) {
            $publishedSession = new PublishedSession([
                'event_id' => $data->event_id,
                'session_id' => $data->id,
            ]);
        }

        $publishedSession->published_by = $loginUser->id;
        $publishedSession->published_date = date('Y-m-d H:i:s', time());
        $publishedSession->published_data = json_decode(json_encode($data), true);

        $publishedSession->save();

        // session published status save
        $session->published_status = true;
        $session->save();

        return $publishedSession;
    }

    // Save published session history
    private function savePublishedSessionHistory($publishedSession)
    {
        PublishedSessionHistory::create([
            'event_id' => $publishedSession->event_id,
            'published_session_id' => $publishedSession->id,
            'published_by' => $publishedSession->published_by,
            'published_data' => $publishedSession->published_data,
            'published_date' => $publishedSession->published_date,
        ]);

        // Keep only last 5
        $histories = PublishedSessionHistory::where('published_session_id', $publishedSession->id)
            ->orderByDesc('published_date')
            ->get();

        if ($histories->count() > 5) {
            $histories->slice(5)->each->delete();
        }
    }

    // get whole session data
    public function getPublishedSessionData($session)
    {

        if (!$session)
            return null;

        $publishedSession = Session::with([
            'users' => function ($query) use ($session) {
                $query->select($this->selectUserColumns())
                    ->join(
                        'event_user as eu',
                        function ($join) use ($session) {
                            $join->on('eu.user_id', '=', 'users.id')
                                ->where('eu.event_id', '=', $session->event_id);
                        }
                    )->addSelect([
                            DB::raw("eu.verification_status as verification_status"),
                            DB::raw("eu.order as speaker_order"),
                            DB::raw("eu.type as type"),
                            DB::raw("eu.verified_data as verified_data"),
                            DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.avatar')) as avatar"),
                            DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.company_logo')) as company_logo"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.mobile')),'null') as mobile"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.job_title')),'null') as verified_job_title"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.biography')),'null') as biography")
                        ])->whereIn('eu.id', function ($subquery) use ($session) {
                            $subquery->select(DB::raw('MIN(id)'))
                                ->from('event_user')
                                ->where('event_id', $session->event->id)
                                ->groupBy('user_id');
                        });
                ;
            },
            'slots' => function ($query) {
                $query->orderBy('order', 'asc');
            },
            'slots.users' => function ($query) use ($session) {
                $query->select($this->selectUserColumns())
                    ->join(
                        'event_user as eu',
                        function ($join) use ($session) {
                            $join->on('eu.user_id', '=', 'users.id')
                                ->where('eu.event_id', '=', $session->event_id);
                        }
                    )->addSelect([
                            DB::raw("eu.verification_status as verification_status"),
                            DB::raw("eu.order as speaker_order"),
                            DB::raw("eu.type as type"),
                            DB::raw("eu.verified_data as verified_data"),
                            DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.avatar')) as avatar"),
                            DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.company_logo')) as company_logo"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.mobile')),'null') as mobile"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.job_title')),'null') as verified_job_title"),
                            DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.biography')),'null') as biography")
                        ])->whereIn('eu.id', function ($subquery) use ($session) {
                            $subquery->select(DB::raw('MIN(id)'))
                                ->from('event_user')
                                ->where('event_id', $session->event->id)
                                ->groupBy('user_id');
                        });
            },
            'slots.presentation',
            'slots.abstract',
            'event' => function ($query) {
                // Select only the fields you need
                $query->select('id', 'event_name', 'slug_name', 'year');
            }
        ])->find($session->id)->append('slug_name');

        return $publishedSession;
    }

    // select user columns
    public function selectUserColumns(): array
    {
        return [
            'users.id',
            'users.first_name',
            'users.last_name',
            DB::raw('concat(users.first_name," ",users.last_name) as name'),
            'users.email',
            'users.company',
            'users.country',
            'users.job_title',
            DB::raw('CASE WHEN users.salutation = "Prefer not to say" THEN NULL ELSE users.salutation END as salutation'),
            'users.phone',
            'users.fax',
            'users.po_box',
            'users.street',
            'users.city',
            'users.state',
            'users.post_code',
            'users.linkedin_link',
            'users.company_address',
            'users.company_country',
            'users.profile_type',
        ];
    }

    // Function to capitalize user fields
    private function capitalizeUserFields(&$user)
    {
        $fieldsToCapitalize = [
            'name',
            'first_name',
            'last_name',
            'salutation',
            'company_address',
            'city',
            'state',
            'country',
            'company_country',
        ];

        foreach ($fieldsToCapitalize as $field) {
            if (isset($user->$field)) {
                $user->$field = ucwords(strtolower($user->$field));
            }
        }

        return $user;
    }

    // list of published sessions
    public function publishedSessions(Request $request, $eventIds, $paginate)
    {
        // Building arguments
        $arguments = $this->arguments($request);

        // Base query for published sessions
        $query = $this->buildPublishedSessionBaseQuery($eventIds);

        // Apply filters
        $filter = json_decode($arguments->filter);
        $this->applyPublishedSessionFilters($query, $filter, $eventIds);

        // Paginate or fetch all results
        $publishedSessions = $this->fetchPublishedSessions($query, $paginate, $arguments->paging, $arguments->page);

        // Filter slots based on session date, if provided
        if ($filter->session_date ?? false) {
            $publishedSessions = $this->filterPublishedSessionSlots($publishedSessions, $filter->session_date);
        }

        return $publishedSessions;
    }

    protected function buildPublishedSessionBaseQuery($eventIds)
    {
        return PublishedSession::select('id', 'published_data', 'published_date')
            ->whereIn('event_id', $eventIds)
            ->orderByRaw('FIELD(event_id, ' . implode(',', $eventIds) . ')')
            ;
    }

    protected function applyPublishedSessionFilters($query, $filter, $eventIds)
    {
        // Filter based on session date
        if ($filter->session_date ?? false) {
            $value = $filter->session_date;
            $query->whereRaw("? between json_unquote(json_extract(`published_data`, '$.start_date')) and
            json_unquote(json_extract(`published_data`, '$.end_date'))", [$value])
                ->orWhere('published_data->start_date', 'LIKE', '%' . $value . '%')
                ->whereIn('event_id', $eventIds);
        }

        // Filter based on last synced date
        if ($filter->last_synced_date ?? false) {
            $query->where('updated_at', '>=', $filter->last_synced_date);
        }
    }

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

    protected function filterPublishedSessionSlots($publishedSessions, $sessionDate)
    {
        foreach ($publishedSessions as $key => $publishedSession) {
            if ($publishedSession->published_data['slots']) {
                $filteredSlots = array_filter($publishedSession->published_data['slots'], function ($slot) use ($sessionDate) {
                    return date('Y-m-d', strtotime($slot['start_date'])) == $sessionDate;
                });

                $items = $publishedSessions[$key]['published_data'];

                $items['slots'] = $filteredSlots;

                $publishedSession->published_data = $items;
            }
        }

        return $publishedSessions;
    }


    public function deleteSlot(Slot $slot)
    {
        $slot->users()->detach();
        $slot->delete();
    }

    public function getFilterOptions(Event $event): array
    {
        $rooms = [];
        $types = [];

        // Use general settings if session room and type fields are enabled
        if ($event->general_settings['session_room_type_fields']['enabled'] ?? false) {
            $roomOptions = $event->general_settings['session_room_type_fields']['room_options'] ?? [];
            $typeOptions = $event->general_settings['session_room_type_fields']['type_track_options'] ?? [];

            $rooms = collect($roomOptions)->pluck('value')->toArray();
            $types = collect($typeOptions)->pluck('value')->toArray();
        }
        // If disabled, leave both empty (do not load from sessions)

        return [
            'rooms' => $rooms,
            'types' => $types
        ];
    }

    public function getMySessions(Request $request)
    {
        //building arguments
        $arguments = $this->arguments($request);

        $event = $request->get('event');

        $slots = Session::select(
            'sessions.id',
            'su.id as slot_user_id',
            'su.role as role',
            'sessions.title as session_title',
            'sessions.start_date as session_start_date',
            'sessions.end_date as session_end_date',
            's.title as slot_title',
            's.reserved as slot_reserved',
            's.start_date as slot_start_date',
            's.end_date as slot_end_date',
            'sessions.room as room',
        );

        $slots->join('slots as s', function ($join) {
            $join->on('s.session_id', '=', 'sessions.id');
        });

        $slots->join('slot_users as su', function ($join) use ($arguments) {
            $join->on('su.slot_id', '=', 's.id')
                ->where('su.user_id', '=', $arguments->id);
        });

        $slots->where('sessions.event_id', '=', $event->id);

        $session = Session::select(
            'sessions.id',
            'eu.id as session_user_id',
            'eu.role as role',
            'sessions.title as session_title',
            'sessions.start_date as session_start_date',
            'sessions.end_date as session_end_date',
            'sessions.room as room',
        );

        $session->join('session_users as eu', function ($join) use ($arguments) {
            $join->on('eu.session_id', '=', 'sessions.id')
                ->where('eu.user_id', '=', $arguments->id);
        });

        $session->where('sessions.event_id', '=', $event->id);

        $allItems = new Collection();
        $allItems = $allItems->merge($slots->get());
        $allItems = $allItems->merge($session->get());


        return $allItems;
    }
    /**
     * 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('Session');
        }

        $formatedFilter = [];
        foreach ($this->sessionFields as $key => $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,
            'source' => 'schedule_mail'
        ]);
        $result = $this->listing($request, false);
        //getting users emails
        foreach ($result as $key => $res) {
            $body = $this->replaceMacros($res, $data, $event->event_name);
            $bcc = [];
            foreach ($filter['users'] as $user) {
                switch ($user['value']) {
                    case "Session users":
                        $mails = getUsersEmails($res->users);
                        $bcc = [...$bcc, ...$mails['emails']];
                        break;
                    case "Slot users":
                        $mailUsers = [];
                        foreach ($res->slots as $slots) {
                            foreach ($slots->users as $slotUsers) {
                                $mailUsers[] = $slotUsers;
                            }
                        }
                        $mails = getUsersEmails($mailUsers);
                        $bcc = [...$bcc, ...$mails['emails']];
                        break;
                }
            }
            $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($row, object $data, string $event_name): string
    {
        $variables = config('schedule_mail_settings')->Session->variables;
        $body = $data->body;
        foreach ($variables as $key => $var) {
            if ($var->value == 'event_name')
                continue;
            $replace = $row[$var->value];

            if ($var->value == 'session_id')
                $replace = $row['id'];

            $body = Str::replace("{" . $var->value . "}", $replace, $body);
        }
        $body = Str::replace("{event_name}", $event_name, $body);
        return $body;
    }
    // create or update chairperson
    public function createOrUpdateChairPerson(Request $request, $event)
    {
        $chairPersonId = $request->get('chairPersonId');
        $input = $request->input();

        $validator = Validator::make($input, [
            'user_id' => ['required'],
            'session_date' => ['required'],
            'option_data' => ['nullable'],
            'text' => ['string', 'nullable'],
        ]);

        if ($validator->fails())
            validationErrorResponse($validator->errors());
        $validInputs = $validator->validated();
        if (!$chairPersonId) {
            $validInputs['event_id'] = $event->id;
            $chairPerson = ChairPerson::create($validInputs);
            $chairPerson->save();

        } else {
            $chairPerson = ChairPerson::find($chairPersonId);
            if (!$chairPerson)
                validationErrorResponse(['Invalid chairperson id']);

            $chairPerson->fill($validInputs);
            // save
            $chairPerson->save();

        }
        return $chairPerson;
    }

    // list of chairpersons
    public function listChairPersons(Request $request, Event $event, $paginate)
    {
        //building arguments
        $arguments = $this->arguments($request);

        // Get auth user and event user for contact restriction check
        $authUser = authUser();
        $authEventUser = getEventUser($event, $authUser);

        // chairperson select query
        $query = ChairPerson::with([
            'user' => function ($query) use ($event) {
                $query->join('event_user as eu', function ($join) use ($event) {
                    $join->on('eu.user_id', '=', 'users.id')
                        ->where('eu.event_id', '=', $event->id);
                })
                    ->select($this->selectUserColumns())
                    ->addSelect([
                        'eu.verification_status',
                        'eu.order as speaker_order',
                        'eu.access_level',
                        DB::raw("eu.type as type"),
                        DB::raw("eu.verified_data as verified_data"),
                        DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.avatar')) as avatar"),
                        DB::raw("JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.company_logo')) as company_logo"),
                        DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.mobile')),'null') as mobile"),
                        DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.job_title')),'null') as verified_job_title"),
                        DB::raw("NULLIF(JSON_UNQUOTE(JSON_EXTRACT(eu.verified_data, '$.biography')),'null') as biography")
                    ]);
            }
        ])->where('chair_persons.event_id', '=', $event->id);
        // filter values
        $filter = json_decode($arguments->filter);

        // filter based on session date
        if ($filter->session_date ?? false) {
            $value = $filter->session_date;
            // Filter based on session date
            $query->whereDate('chair_persons.session_date', '=', $value);
        }

        // Get the chairpersons
        $chairPersons = ($paginate && $arguments->paging != 'All') ? $query->paginate($arguments->paging, ['*'], 'page', $arguments->page) : $query->get();

        // Add contact_restricted flag to each user
        $chairPersons->each(function ($chairPerson) use ($authEventUser) {
            if ($chairPerson->user) {
                $chairPerson->user->contact_restricted = shouldRestrictContact($chairPerson->user, $authEventUser);
            }
        });

        // Group chairpersons by session_date
        $groupedChairPersons = $chairPersons->groupBy('session_date');

        // Convert to array and return
        return $groupedChairPersons->toArray();
    }

    // export sessions
    public function exportSessions($sessions)
    {
        // Get event from first session to check session form settings
        $firstSession = $sessions->first();
        $event = $firstSession ? $firstSession->event : null;
        $sessionFormEnabled = $event && isset($event->general_settings['session_form_settings']['enabled']) ? $event->general_settings['session_form_settings']['enabled'] : false;
        $categories = $sessionFormEnabled && isset($event->general_settings['session_form_settings']['categories']) ? $event->general_settings['session_form_settings']['categories'] : [];

        // Build headers
        $headers = [
            'Session ID',
            'Session Name',
            'Start Date',
            'Start Time',
            'End Date',
            'End Time',
            'Track/Type',
            'Room'
        ];

        // Add category headers if enabled
        if ($sessionFormEnabled && !empty($categories)) {
            foreach ($categories as $category) {
                $headers[] = $category['category_label'] ?? 'Category';
            }
        }

        // Add speaker headers
        $headers = array_merge($headers, [
            'Speaker Email',
            'Speaker First Name',
            'Speaker Last Name',
            'Speaker Company',
            'Speaker Job Title'
        ]);

        $excelData = [$headers];

        foreach ($sessions as $session) {
            $users = $session->users()->get();

            if ($users->isEmpty()) {
                $excelData[] = $this->getExportData($session, null, $categories, $sessionFormEnabled);
            } else {
                foreach ($users as $user) {
                    $excelData[] = $this->getExportData($session, $user, $categories, $sessionFormEnabled);
                }
            }
        }

        return Excel::download(new ExportSessionsClass($excelData), 'sessions.xlsx');
    }
    // get export data
    private function getExportData($session, $user = null, $categories = [], $sessionFormEnabled = false)
    {
        $startDate = $this->formatDateTime($session->start_date);
        $endDate = $this->formatDateTime($session->end_date);

        $baseData = [
            $session->id,
            $session->title,
            $startDate['date'],
            $startDate['time'],
            $endDate['date'],
            $endDate['time'],
            $session->type,
            $session->room,
        ];

        // Add category data if enabled
        if ($sessionFormEnabled && !empty($categories)) {
            $sessionCategories = $session->session_categories ?? [];
            
            foreach ($categories as $category) {
                $categoryId = $category['field_id'];
                $selectedCategory = collect($sessionCategories)->firstWhere('id', $categoryId);
                
                if ($selectedCategory && !empty($selectedCategory['options'])) {
                    $optionLabels = collect($selectedCategory['options'])->pluck('label')->toArray();
                    $baseData[] = implode(', ', $optionLabels);
                } else {
                    $baseData[] = '';
                }
            }
        }

        // Add user data
        $userData = ($user !== null) ? [
            $user->email,
            $user->first_name,
            $user->last_name,
            $user->company,
            $user->job_title,
        ] : ['', '', '', '', ''];

        return array_merge($baseData, $userData);
    }
    // format date and time
    public function formatDateTime($dateTimeString)
    {
        $dateTime = new DateTime($dateTimeString);
        return [
            'date' => $dateTime->format('d-m-Y'),
            'time' => $dateTime->format('H:i'),
        ];
    }

    /**
     * Update sessions when room type fields change.
     * This method updates sessions when room or track names change in the event settings.
     *
     * @param Event $event
     * @param array $oldSettings
     * @param array $newSettings
     */
    public function updateSessionsOnRoomTypeFieldsChange(Event $event, array $oldSettings, array $newSettings)
    {
        // Check if room or track options have changed
        $this->updateSessionsOnFieldNameChange(
            $event,
            $oldSettings['room_options'] ?? [],
            $newSettings['room_options'] ?? [],
            'room'
        );

        $this->updateSessionsOnFieldNameChange(
            $event,
            $oldSettings['type_track_options'] ?? [],
            $newSettings['type_track_options'] ?? [],
            'type'
        );
    }

    /**
     * Update sessions when field names change.
     * This method checks if the field name has changed and updates the sessions accordingly.
     *
     * @param Event $event
     * @param array $oldOptions
     * @param array $newOptions
     * @param string $fieldKey
     */
    public function updateSessionsOnFieldNameChange($event, $oldOptions, $newOptions, $fieldKey)
    {
        $oldMap = collect($oldOptions)->keyBy('field_id');
        foreach ($newOptions ?? [] as $newOption) {
            if (isset($oldMap[$newOption['field_id']])) {
                $oldOption = $oldMap[$newOption['field_id']];
                if ($oldOption['value'] !== $newOption['value']) {
                    $this->updateSessionsOnNameChange($event, $oldOption['value'], $newOption['value'], $fieldKey);
                }
            }
        }
    }


    /**
     * Update sessions when room or track name changes
     */
    public function updateSessionsOnNameChange(Event $event, string $oldName, ?string $newName, string $type): array
    {
        if (is_null($newName) || $oldName === $newName) {
            return [
                'status' => true,
                'message' => 'No changes made to the name',
                'updated_count' => 0,
                'published_sessions_count' => 0,
            ];
        }

        $sessions = Session::where('event_id', $event->id)
            ->where($type === 'room' ? 'room' : 'type', $oldName)
            ->get();

        if ($sessions->isEmpty()) {
            return [
                'status' => true,
                'message' => 'No sessions found using the old name',
                'updated_count' => 0,
                'published_sessions_count' => 0,
            ];
        }

        DB::beginTransaction();

        try {
            $publishedSessions = $sessions->filter(fn($s) => $s->published_status);
            $sentEmails = []; // Track sessions for which emails have been sent

            foreach ($sessions as $session) {
                $session->{$type} = $newName;
                if ($session->published_status) {
                    $session->published_status = false;
                    // Only send email if we haven't sent one for this session yet
                    if (!in_array($session->id, $sentEmails)) {
                        dispatch(new SendSessionUpdatedMail($session, null, 'session change'));
                        $sentEmails[] = $session->id;
                    }
                }
                $session->save();
            }

            DB::commit();

            return [
                'status' => true,
                'message' => 'Sessions updated successfully',
                'updated_count' => $sessions->count(),
                'published_sessions_count' => $publishedSessions->count(),
            ];
        } catch (Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    /**
     * Validate external session data
     */
    public function validateExternalSession($sessionData, $event)
    {
        $errors = [];
        $generalSettings = $event->general_settings;
        $formSettings = $event->form_settings;

        // Validate session ID belongs to current event (CRITICAL SECURITY CHECK)
        if (!empty($sessionData['id'])) {
            $existingSession = Session::where('id', $sessionData['id'])
                                    ->where('event_id', $event->id)
                                    ->first();
            if (!$existingSession) {
                $errors[] = "Session with ID {$sessionData['id']} not found in this event";
            }
        }

        // Validate date/time logic - end date must be after start date
        if (!empty($sessionData['start_date']) && !empty($sessionData['end_date'])) {
            $startDate = strtotime($sessionData['start_date']);
            $endDate = strtotime($sessionData['end_date']);

            if ($startDate === false) {
                $errors[] = "Invalid start_date format. Use 'YYYY-MM-DD HH:MM:SS' format";
            }
            if ($endDate === false) {
                $errors[] = "Invalid end_date format. Use 'YYYY-MM-DD HH:MM:SS' format";
            }

            if ($startDate !== false && $endDate !== false && $endDate <= $startDate) {
                $errors[] = "End date must be after start date";
            }
        }

        // Validate room if room options are enabled
        if ($generalSettings['session_room_type_fields']['enabled'] ?? false) {
            $roomOptions = collect($generalSettings['session_room_type_fields']['room_options'] ?? [])
                ->pluck('value')->toArray();

            if (!empty($sessionData['room']) && !in_array($sessionData['room'], $roomOptions)) {
                $errors[] = "Room '{$sessionData['room']}' not found in available options";
            }
        }

        // Validate type if type options are enabled
        if ($generalSettings['session_room_type_fields']['enabled'] ?? false) {
            $typeOptions = collect($generalSettings['session_room_type_fields']['type_track_options'] ?? [])
                ->pluck('value')->toArray();

            if (!empty($sessionData['type']) && !in_array($sessionData['type'], $typeOptions)) {
                $errors[] = "Type '{$sessionData['type']}' not found in available options";
            }
        }

        // Validate category
        if (!empty($sessionData['category'])) {
            $categoryField = collect($formSettings)->firstWhere('field_id', 'category');
            if ($categoryField) {
                $categoryOptions = collect($categoryField['options'] ?? [])->pluck('value')->toArray();
                if (!in_array($sessionData['category'], $categoryOptions)) {
                    $errors[] = "Category '{$sessionData['category']}' not found in available options";
                }
            }
        }

        return $errors;
    }

    /**
     * Format description fields - convert plain text to HTML for rich text fields only
     */
    private function formatDescriptionFields(&$sessionData)
    {
        $htmlFields = ['description', 'description2'];

        foreach ($htmlFields as $field) {
            if (!empty($sessionData[$field]) && strip_tags($sessionData[$field]) === $sessionData[$field]) {
                $sessionData[$field] = '<p>' . $sessionData[$field] . '</p>';
            }
        }
    }

    /**
     * Validate all sessions before processing - collect all validation errors
     */
    private function validateAllSessions($sessionsData, $event)
    {
        $allErrors = [];

        foreach ($sessionsData as $index => $sessionData) {
            $validationErrors = $this->validateExternalSession($sessionData, $event);
            if (!empty($validationErrors)) {
                $allErrors[] = [
                    'session_index' => $index + 1,
                    'session_title' => $sessionData['title'] ?? "Session " . ($index + 1),
                    'errors' => $validationErrors
                ];
            }
        }

        return $allErrors;
    }

    /**
     * Bulk create or update sessions for external API
     */
    public function bulkCreateOrUpdate($sessionsData, $event)
    {
        // First, validate ALL sessions and collect ALL errors
        $allValidationErrors = $this->validateAllSessions($sessionsData, $event);

        // If any validation errors exist, return them all without processing
        if (!empty($allValidationErrors)) {
            throw new \Exception(json_encode([
                'type' => 'validation_errors',
                'message' => 'Validation errors found in multiple sessions',
                'errors' => $allValidationErrors
            ]));
        }

        DB::beginTransaction();

        try {
            $results = [];

            foreach ($sessionsData as $index => $sessionData) {
                // Auto-convert plain text descriptions to HTML
                $this->formatDescriptionFields($sessionData);

                // Create request object for existing method
                $request = new \Illuminate\Http\Request();
                $request->merge($sessionData);
                $request->merge(['event' => $event]);

                // Use existing createOrUpdate method
                $session = $this->createOrUpdate($request);

                // Mark as external
                $session->created_from_external = true;
                $session->save();

                // Handle publishing
                if ($sessionData['published'] ?? false) {
                    $this->publish($session);
                }

                $results[] = [
                    'id' => $session->id,
                    'title' => $session->title,
                    'action' => $sessionData['id'] ?? false ? 'updated' : 'created',
                    'published' => ($sessionData['published'] ?? false) ? 'published' : 'not published'
                ];
            }

            DB::commit();
            return $results;

        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    /**
     * Update sessions when form settings change.
     * This method handles category and option changes in the form settings.
     *
     * @param string $event
     * @param array $oldFormSettings
     * @param array $newFormSettings
     */
    public function updateSessionsOnFormSettingsChange($event, $oldFormSettings, $newFormSettings)
    {
        foreach ($oldFormSettings['categories'] as $oldCat) {
            $newCat = collect($newFormSettings['categories'])->firstWhere('field_id', $oldCat['field_id']);
            if (!$newCat) {
                // Category deleted
                $this->propagateCategoryOptionChanges($oldCat['field_id'], null, []);
                continue;
            }
            // Category renamed
            if ($oldCat['category_label'] !== $newCat['category_label']) {
                $this->propagateCategoryOptionChanges($oldCat['field_id'], $newCat['category_label'], []);
            }
            // Option changes
            $oldOptions = collect($oldCat['category_options']);
            $newOptions = collect($newCat['category_options']);
            $optionUpdates = [];
            foreach ($oldOptions as $oldOpt) {
                $newOpt = $newOptions->firstWhere('option_id', $oldOpt['option_id']);
                if (!$newOpt) {
                    $optionUpdates[$oldOpt['option_id']] = null; // deleted
                } elseif ($oldOpt['option_label'] !== $newOpt['option_label']) {
                    $optionUpdates[$oldOpt['option_id']] = $newOpt['option_label']; // renamed
                }
            }
            if ($optionUpdates) {
                $this->propagateCategoryOptionChanges($oldCat['field_id'], null, $optionUpdates);
            }
        }
    }


    public function propagateCategoryOptionChanges($categoryId, $newCategoryLabel = null, $optionUpdates = [])
    {
        // Start a DB transaction for safety
        DB::beginTransaction();

        try {
            $sentEmails = []; // Initialize to avoid undefined variable and duplicate notifications

            // Find all sessions using this category
            $sessions = Session::whereJsonContains('session_categories', [['id' => $categoryId]])->get();

            foreach ($sessions as $session) {
                $changed = false;
                $categories = $session->session_categories ?? [];

                foreach ($categories as &$cat) {
                    if ($cat['id'] === $categoryId) {
                        // Update category label if changed
                        if ($newCategoryLabel && isset($cat['label']) && $cat['label'] !== $newCategoryLabel) {

                            $cat['label'] = $newCategoryLabel;
                            $changed = true;
                        }

                        // Update/delete options
                        if (!empty($optionUpdates)) {
                            $newOptions = [];
                            foreach ($cat['options'] as $opt) {
                                $optionId = $opt['id'];
                                if (array_key_exists($optionId, $optionUpdates)) {
                                    $newLabel = $optionUpdates[$optionId];
                                    if ($newLabel !== null) {
                                        // Only update the label for the matching option
                                        if ($opt['label'] !== $newLabel) {
                                            $opt['label'] = $newLabel;
                                            $opt['value'] = $newLabel;
                                            $changed = true;
                                        }
                                        $newOptions[] = $opt;
                                    } else {
                                        // Option deleted
                                        $changed = true;
                                    }
                                } else {
                                    $newOptions[] = $opt;
                                }
                            }
                            $cat['options'] = $newOptions;
                        }
                    }
                }

                if ($changed) {
                    $session->session_categories = $categories;
                    // Unpublish if published
                    if ($session->published_status) {
                        $session->published_status = false;
                        // Send notification only once per session
                        if (!in_array($session->id, $sentEmails)) {
                            dispatch(new SendSessionUpdatedMail($session, null, 'session change'));
                            $sentEmails[] = $session->id;
                        }
                    }
                    $session->save();
                }
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }
}