<?php

namespace App\Http\Controllers\API;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Str;
use Carbon\Carbon;
use DB;

use Kreait\Firebase\Factory;
use Kreait\Firebase\Messaging\CloudMessage;

class VerseController extends Controller
{
    
    // 2. Verset par livre / chapitre / numéro GET /api/verses/Jean/3
    public function showbible()
    {
        $result = DB::table('ebm_bible')
        ->orderBy('ebm_bible.id', 'asc')
        ->get();

        //return $result;
        
        $resultbible = [
            'bibles' => $result,
        ];

        return response()->json($resultbible); 
    }
    
    // 2. Verset par livre / chapitre / numéro GET /api/verses/Jean/3
    public function showlivre($book, $chapter)
    {
        $result = DB::table('ebm_bible')
        ->where('book_name', $book)
        ->where('chapter', $chapter)->get();

        if(!$result) 
        {
            return response()->json(['message' => 'Verset non trouvé'], 404);
        }

        //return $result;
        
        $resultlivre = [
            'statusCode' => 200,
            'verses' => $result,
        ];

        return response()->json($resultlivre); 
    }
    
    // 2. Verset par livre / chapitre / numéro GET /api/verses/Jean/3
    public function showbibleversetchapt(Request $request)
    {
        $book=$request->input('book');
        $chapter=$request->input('chapter');   
        
        $result = DB::table('ebm_bible')
        ->where('book_name', $book)
        ->where('chapter', $chapter)->get();

        if(!$result) 
        {
            return response()->json(['message' => 'Verset non trouvé'], 404);
        }

        //return $result;
        
        $resultlivre = [
            'statusCode' => 200,
            'verses' => $result,
        ];

        return response()->json($resultlivre); 
    }
    
    // API Laravel qui permette de rechercher une plage de versets GET /api/verses/Matthew/5/1-4
    public function showrange($book, $chapter, $range)
    {
        // Ex : $range = "1-4"
        [$start, $end] = explode('-', $range);
        
        if($start > $end)
        { 
            return response()->json(['message' => 'Aucun verset trouvé.'], 404);
        }  
        else
        {
            $verses = DB::table('ebm_bible')
            ->where('book_name', $book)
            ->where('chapter', $chapter)
            ->whereBetween('verse', [(int)$start, (int)$end])
            ->orderBy('verse')
            ->get();
        
            if ($verses->isEmpty()) 
            {
                return response()->json(['message' => 'Aucun verset trouvé.'], 404);  
            }
        
            //return $verses;
            
            $resultlivrerange = [
                'verses' => $verses,
            ];
    
            return response()->json($resultlivrerange); 
        }
    }
    
    public function showbibleplage(Request $request)
    {
        $book=$request->input('book');
        $chapter=$request->input('chapter');   
        $range=$request->input('range');
    
        // Ex : $range = "1-4"
        [$start, $end] = explode('-', $range);
        
        if($start > $end)
        { 
            return response()->json(['message' => 'Aucun verset trouvé.'], 404);
        }  
        else
        {
            $verses = DB::table('ebm_bible')
            ->where('book_name', $book)
            ->where('chapter', $chapter)
            ->whereBetween('verse', [(int)$start, (int)$end])
            ->orderBy('verse')
            ->get();
        
            if ($verses->isEmpty()) 
            {
                return response()->json(['message' => 'Aucun verset trouvé.'], 404);  
            }
        
            //return $verses;
            
            $resultlivrerange = [
                'verses' => $verses,
            ];
    
            return response()->json($resultlivrerange); 
        }
    }
    
    
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LA LISTE DES TEMOIGNAGES --------------------------------------------------------------------------------
    
    public function listviewstemoignage()   
    {
        
        global $postId;
        
        $temoignageData = [];
        $dataviewtemoignage= DB::select('select * from ebm_temoignage order by created_at DESC');
        foreach ($dataviewtemoignage as $rowviewtemoignage)
        {
            $postId=$rowviewtemoignage->id_temoignage;
            
            $temoignageData[$postId]= [
                'id' =>$rowviewtemoignage->id_temoignage,
                'usersebm' => [],
                'categoryebm' => [],
                'content' => $rowviewtemoignage->content,
                'audio_url' => $rowviewtemoignage->audio_url,
                'input_type' => $rowviewtemoignage->input_type,
                'created_at' => $rowviewtemoignage->created_at,
            ];
            
            $dataviewuserebm= DB::select('select * from ebm_users where id_user=?',[$rowviewtemoignage->id_user]);
            foreach ($dataviewuserebm as $rowviewuserebm)
            {
                $temoignageData[$postId]['usersebm'][] = [
                    'id_user' => $rowviewuserebm->id_user,
                    'name' =>    $rowviewuserebm->name,
                    'email' =>   $rowviewuserebm->email,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $dataviewcategoryebm= DB::select('select * from ebm_categories where id_category=?',[$rowviewtemoignage->id_category]);
            foreach ($dataviewcategoryebm as $rowviewcategoryebm)
            {
                $temoignageData[$postId]['categoryebm'][] = [
                    'id_category' => $rowviewcategoryebm->id_category,
                    'name' =>    $rowviewcategoryebm->name,
                    'created_at' =>   $rowviewcategoryebm->created_at,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
               
        }
        
        $resultemoignageData = [
            'statDatatemoignage' => 'success',
            'datatemoignageData' => array_values($temoignageData),  
        ];
        return response()->json($resultemoignageData);
    }
    
    //Enregistre un utilisateur dans la base de données
    
    public function saveusersebm(Request $request)   
    {
        $userid=$request->input('uid');
        $userprenom=$request->input('first_name');
        $usernom=$request->input('last_name');
        $useremail=$request->input('email');
        $avatarData = $request->input('avatar');
        
       

        // Vérifie si le format base64 est valide
        if (preg_match('/^data:image\/(\w+);base64,/', $avatarData, $type)) {
            $avatarData = substr($avatarData, strpos($avatarData, ',') + 1);
            $extension = strtolower($type[1]);

            if (!in_array($extension, ['jpg', 'jpeg', 'png', 'gif'])) {
                return response()->json(['error' => 'Type d\'image non supporté'], 422);
            }

            $avatarData = base64_decode($avatarData);
            if ($avatarData === false) {
                return response()->json(['error' => 'Base64 invalide'], 422);
            }
        } else {
            return response()->json(['error' => 'Format d\'image invalide'], 422);
        }

        // Crée le dossier public/avatars s’il n'existe pas
        $avatarFolder = public_path('avatars');
        if (!file_exists($avatarFolder)) {
            mkdir($avatarFolder, 0755, true);
        }

        // Génère un nom de fichier unique
        $fileName = Str::uuid() . '.' . $extension;
        $filePath = $avatarFolder . '/' . $fileName;

        // Écrit l'image dans le dossier public/avatars
        file_put_contents($filePath, $avatarData);

        //'avatar_url' => asset('avatars/' . $fileName),
        
        $urlfilestore='https://touszone.com/mobileappebm/public/avatars/'.''.$fileName;
        
        DB::table('ebm_users')->insert(
            [
                'id_user' => $userid,
                'nom' => $usernom,
                'prenom' => $userprenom,
                'email' => $useremail, 
                'avatar' => $urlfilestore, 
                'created_at' => date('Y-m-d H:i:s'), 
                'updated_at' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    
    //----------------------------------------------------------------  AFFICHER AVATAR USERCONNECT  ----------------------------------------------------------------------
    
    public function avatar_userconnect(Request $request)   
    {
        global $avatar,$nameavatar;
        $userid=$request->input('uid');

        $avatardata = DB::table('ebm_users')->where('id_user', $userid)->first();
        
        if(isset($avatardata->id_user) and $avatardata->id_user !='')
        {
             $avatar=$avatardata->avatar;
             $nameavatar=$avatardata->nom.' '.$avatardata->prenom;
        }
        
        return response()->json([
            'success' => 'true',
            'urlavatar' => $avatar,
            'nameuser' => $nameavatar
        ]);
    }
    
    //-------------------------------------------------------------------------- API POSTER FORUM EBM --------------------------------------------------------------------------------
    
    public function posterforumebm(Request $request)   
    {
        $userid=$request->input('id_user');
        $title=$request->input('title');
        $content=$request->input('content');
        
        DB::table('ebm_forum_posts')->insert(
            [
                'id_user' => $userid,
                'title' => $username,
                'content' => $useremail, 
                'date_post' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API LIKE FORUM EBM POST --------------------------------------------------------------------------------
    
    public function likeforumebmposte(Request $request)   
    {
        $idpost=$request->input('id_post');
        $iduser=$request->input('id_user');
        
        DB::table('ebm_forum_post_likes')->insert(
            [
                'id_post' => $idpost,
                'id_user' => $iduser,
                'viewed_at' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API REACTION FORUM EBM POST --------------------------------------------------------------------------------
    
    public function reactionforumebmposte(Request $request)   
    {
        $idpost=$request->input('id_post');
        $iduser=$request->input('id_user');  
        $type=$request->input('type');
        
        DB::table('ebm_forum_reactions')->insert(
            [
                'id_post' => $idpost,
                'id_user' => $iduser,
                'type' => $type,
                'date_reaction' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API REPONSES FORUM EBM POST --------------------------------------------------------------------------------
    
    public function reponsesforumebmposte(Request $request)   
    {
        $idpost=$request->input('id_post');
        $iduser=$request->input('id_user');  
        $content=$request->input('content');
        
        DB::table('ebm_forum_reponses')->insert(
            [
                'id_post' => $idpost,
                'id_user' => $iduser,
                'content' => $type,
                'date_reponse' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API LIKE REPONSES FORUM EBM POST --------------------------------------------------------------------------------
    
    public function likeforumebmreponsesposte(Request $request)   
    {
        $idreponses=$request->input('id_reponses');
        $iduser=$request->input('id_user');
        
        DB::table('ebm_forum_reponses_likes')->insert(
            [
                'id_reponses' => $idreponses,
                'id_user' => $iduser,
                'created_at' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API VIEWS FORUM EBM POST --------------------------------------------------------------------------------
    
    public function viewsforumebmposte(Request $request)   
    {
        $idpost=$request->input('id_post');
        $iduser=$request->input('id_user');
        
        DB::table('ebm_forum_posts_views')->insert(
            [
                'id_post' => $idpost,
                'id_user' => $iduser,
                'viewed_at' => date('Y-m-d H:i:s')
            ]
        );
        
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API POUR AVOIR LA LISTE DES POSTES(FORUM) --------------------------------------------------------------------------------
    
    public function listviewsforum()   
    {
        
        global $postId;
        
        $forumData = [];
        $dataviewforum= DB::select('select * from ebm_forum_posts');
        foreach ($dataviewforum as $rowviewforum)
        {
            $postId=$rowviewforum->id_post;
            
            $forumData[$postId]= [
                'id' =>$rowviewforum->id_post,
                'usersebm' => [],
                'title' => $rowviewforum->title,
                'content' => $rowviewforum->content,
                'vues' => $rowviewforum->vues,
                //'date_post' => $rowviewforum->date_post,  
                'forumpostlike' => [],
                'forumreaction' => [],
                'forumreponses' => [],
                'forumreponseslike' => [],
                'forumvues' => [],
            ];
            
            $dataviewnbrlikepost= DB::select('select count(id_likes_pos) as nbrlikeposte from ebm_forum_post_likes where id_post=?',[$rowviewforum->id_post]);
            foreach ($dataviewnbrlikepost as $rowviewnbrlikepost)
            {
                $forumData[$postId]['forumpostlike'][] = [
                    'nbrlikepostes' => $rowviewnbrlikepost->nbrlikeposte,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $dataviewnbrpostreaction= DB::select('select count(id_reactions) as nbrpostereaction from ebm_forum_reactions where id_post=?',[$rowviewforum->id_post]);
            foreach ($dataviewnbrpostreaction as $rowviewnbrpostreaction)
            {
                $forumData[$postId]['forumreaction'][] = [
                    'nbrpostereaction' => $rowviewnbrpostreaction->nbrpostereaction,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $dataviewnbrpostreponse= DB::select('select count(id_reactions) as nbrpostereponses from ebm_forum_reponses where id_post=?',[$rowviewforum->id_post]);
            foreach ($dataviewnbrpostreponse as $rowviewnbrpostreponse)
            {
                $forumData[$postId]['forumreponses'][] = [
                    'nbrpostereponses' => $rowviewnbrpostreponse->nbrpostereponses,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $datapostreponse= DB::select('select * from ebm_forum_reponses where id_post=?',[$rowviewforum->id_post]);
            foreach ($datapostreponse as $rowpostreponse)
            {
                $dataviewnbrlikereponses= DB::select('select count(id_likes_reponses) as nbrlikereponse from ebm_forum_reponses_likes where id_reponses=?',[$rowpostreponse->id_reponses]);
                foreach ($dataviewnbrlikereponses as $rowviewnbrlikereponses)
                {
                    $forumData[$postId]['forumreponseslike'][] = [
                        'nbrlikereponse' => $rowviewnbrlikereponses->nbrlikereponse,
                        // Ajoutez d'autres attributs d'image ici
                    ];
                }
            }
            
            $datanbrviewsposts= DB::select('select count(id_views) as nbrviewsposts from ebm_forum_posts_views where id_post=?',[$rowviewforum->id_post]);
            foreach ($datanbrviewsposts as $rowviewsnbrposts)
            {
                $forumData[$postId]['forumvues'][] = [
                    'nbrviewsposts' => $rowviewsnbrposts->nbrviewsposts,   
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
        
        }
        
        $resultforumData = [
            'statforumData' => 'success',
            'dataforumData' => array_values($forumData),  
        ];
        return response()->json($resultforumData);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LA LISTE DES GROUPES --------------------------------------------------------------------------------
    
    public function viewsgroupe()   
    {
        $groupeData = [];
        $dataviewgroupe= DB::select('select * from ebm_groupe');
        foreach ($dataviewgroupe as $rowviewgroupe)
        {
            $groupeId=$rowviewgroupe->id_groupe;
            
            $groupeData[$groupeId]= [
                'id' =>$rowviewgroupe->id_groupe,
                'titregroupe' => $rowviewgroupe->titregroupe,
                'description' => $rowviewgroupe->description,
                'est_actif' => $rowviewgroupe->est_actif,
                'prochaine_session' => $rowviewgroupe->prochaine_session,
                'created_at' => $rowviewgroupe->created_at,
                'nbrmenbregroup' =>[],
            ];
            
            
            $datanbrmenbregroup= DB::select('select count(id_user) as menbregroupes from ebm_rejoindre_groupe where id_groupe=?',[$rowviewgroupe->id_groupe]);
            foreach ($datanbrmenbregroup as $rowmenbregroupgroup)
            {
                $groupeData[$groupeId]['nbrmenbregroup'][] = [
                    'nbrmenbregroupes' => $rowmenbregroupgroup->menbregroupes,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
        }
        
        $resultegroupeData = [
            'statDatagroupe' => 'success',
            'datagroupeData' => array_values($groupeData),  
        ];
        return response()->json($resultegroupeData);
    }
    
    //-------------------------------------------------------------------------- API POUR REJOINDRE UN GROUPE --------------------------------------------------------------------------------
    
    public function rejoindregroupe(Request $request)   
    {
        $idgroupe=$request->input('id_groupe');
        $iduser=$request->input('id_user');
        
        $datadbgroupe = DB::table('ebm_categories')
        ->where('id_groupe', $idgroupe)
        ->where('id_user', $iduser)
        ->first();
        
        if(isset($datadbgroupe->id_rejdrgrpe) and $datadbgroupe->id_rejdrgrpe >0)
        {}
        else
        {
            DB::table('ebm_categories')->insert(
                [
                    'id_groupe' => $idgroupe,
                    'id_user' => $iduser,
                    'viewed_at' => date('Y-m-d H:i:s')
                ]
            );
        }
        return response()->json(['success' => true], 200);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LA LISTE DES PUBLICATIONS DES GROUPES --------------------------------------------------------------------------------
    
    public function listviewspostsgroupe()   
    {
        
        $postsgroupeData = [];
        $dataviewpostsgroupe= DB::select('select * from ebm_groupe');
        foreach ($dataviewpostsgroupe as $rowviewgroupe)
        {
            $groupeId=$rowviewgroupe->id_groupe;
            
            $postsgroupeData[$groupeId]= [
                'id' =>$rowviewgroupe->id_groupe,
                'titregroupe' => $rowviewgroupe->titregroupe,
                'description' => $rowviewgroupe->description,
                'est_actif' => $rowviewgroupe->est_actif,
                'prochaine_session' => $rowviewgroupe->prochaine_session,
                'created_at' => $rowviewgroupe->created_at,
                'nbrmenbregroup' =>[],
                'publicationgroup' =>[],
            ];
            
            
            $datanbrmenbregroup= DB::select('select count(id_user) as menbregroupes from ebm_rejoindre_groupe where id_groupe=?',[$rowviewgroupe->id_groupe]);
            foreach ($datanbrmenbregroup as $rowmenbregroupgroup)
            {
                $postsgroupeData[$groupeId]['nbrmenbregroup'][] = [
                    'nbrmenbregroupes' => $rowmenbregroupgroup->menbregroupes,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $dataviewpostgroupe= DB::select('select * from ebm_groupe_posts where id_groupe=? order by date_publication DESC',[$rowviewgroupe->id_groupe]); 
            foreach ($dataviewpostgroupe as $rowviewpostsgroup)
            {
                $postsgroupeData[$groupeId]['publicationgroup'][] = [
                    'id_groupepost' => $rowviewpostsgroup->id_groupepost,
                    'title' =>    $rowviewpostsgroup->title,
                    'content' =>   $rowviewpostsgroup->content,
                    'audio_url' =>   $rowviewpostsgroup->audio_url,
                    'type_contenu' =>   $rowviewpostsgroup->type_contenu,
                    'date_publication' =>   $rowviewpostsgroup->date_publication,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
        }
        
        $resultepostgroupeData = [
            'statDatapostgroupe' => 'success',
            'datagroupepostview' => array_values($postsgroupeData),  
        ];
        return response()->json($resultepostgroupeData);
    }
    
    //------------------------------------------------ API POUR AFFICHER LA LISTE DES PUBLICATIONS DES GROUPES PAR RAPPORT À UN USERS ------------------------------------------------------
    
    public function usersviewspostsgroupe(Request $request)   
    {
        $iduser=$request->input('id_user');
        
        $postsgroupeData = [];
        $dataviewpostsgroupe= DB::select('select ebm_groupe.* from ebm_groupe,ebm_rejoindre_groupe where ebm_groupe.id_groupe=ebm_rejoindre_groupe.id_groupe and id_user=?',[$iduser]);
        foreach ($dataviewpostsgroupe as $rowviewgroupe)
        {
            $groupeId=$rowviewgroupe->id_groupe;
            
            $postsgroupeData[$groupeId]= [
                'id' =>$rowviewgroupe->id_groupe,
                'titregroupe' => $rowviewgroupe->titregroupe,
                'description' => $rowviewgroupe->description,
                'est_actif' => $rowviewgroupe->est_actif,
                'prochaine_session' => $rowviewgroupe->prochaine_session,
                'created_at' => $rowviewgroupe->created_at,
                'nbrmenbregroup' =>[],
                'publicationgroup' =>[],
            ];
            
            
            $datanbrmenbregroup= DB::select('select count(id_user) as menbregroupes from ebm_rejoindre_groupe where id_groupe=?',[$rowviewgroupe->id_groupe]);
            foreach ($datanbrmenbregroup as $rowmenbregroupgroup)
            {
                $postsgroupeData[$groupeId]['nbrmenbregroup'][] = [
                    'nbrmenbregroupes' => $rowmenbregroupgroup->menbregroupes,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
            $dataviewpostgroupe= DB::select('select * from ebm_groupe_posts where id_groupe=? order by date_publication DESC',[$rowviewgroupe->id_groupe]); 
            foreach ($dataviewpostgroupe as $rowviewpostsgroup)
            {
                $postsgroupeData[$groupeId]['publicationgroup'][] = [
                    'id_groupepost' => $rowviewpostsgroup->id_groupepost,
                    'title' =>    $rowviewpostsgroup->title,
                    'content' =>   $rowviewpostsgroup->content,
                    'audio_url' =>   $rowviewpostsgroup->audio_url,
                    'type_contenu' =>   $rowviewpostsgroup->type_contenu,
                    'date_publication' =>   $rowviewpostsgroup->date_publication,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
            
        }
        
        $resultepostgroupeData = [
            'statDatapostgroupe' => 'success',
            'datagroupepostview' => array_values($postsgroupeData),  
        ];
        return response()->json($resultepostgroupeData);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LA LISTE DES CATEGORIES --------------------------------------------------------------------------------
    
    public function viewscatgroupe()   
    {
        $dataviewcatgroupe= DB::select('select * from ebm_categories');
        
        $resultecatgroupeData = [
            'statcatgroupe' => 'success',
            'catgroupe' => array_values($dataviewcatgroupe),  
        ];
        return response()->json($resultecatgroupeData);
    }
    
    //-------------------------------------------------------------------------- API POUR PLAN DE LECTURE EN COUR --------------------------------------------------------------------------------
    
    public function currentplan(Request $request)
    {
        // Récupérer un paramètre GET ou POST avec 'input'
        $uidusers = $request->input('uid'); 
        
        $postsgroupeData = [];
        $dataviewpostsgroupe= DB::select('select ebm_user_reading_progress.* from ebm_user_reading_progress where id_user=?',[$uidusers]);
        foreach ($dataviewpostsgroupe as $rowviewgroupe)
        {
            $groupeId=$rowviewgroupe->id_reading_plan;
            
            $postsgroupeData[$groupeId]= [
                'id' =>$rowviewgroupe->id_groupe,
                'titregroupe' => $rowviewgroupe->titregroupe,
                'description' => $rowviewgroupe->description,
                'est_actif' => $rowviewgroupe->est_actif,
                'prochaine_session' => $rowviewgroupe->prochaine_session,
                'created_at' => $rowviewgroupe->created_at,
                'nbrmenbregroup' =>[],
                'publicationgroup' =>[],
            ];
            
        }
        
        $sectionplanactu= DB::select('select * from ebm_reading_plans,ebm_reading_plan_days
        where ebm_reading_plans.id_reading_plan=ebm_reading_plan_days.id_reading_plan and  id_groupe=? order by date_publication DESC',[$rowviewgroupe->id_groupe]); 
        
        return view('exemple.show', compact('searchTerm', 'filter'));
    }
    
    //-------------------------------------------------------------------------- API POUR LA LISTE DES PLANS DE LECTURE --------------------------------------------------------------------------------
    
    public function reading_plans()
    {
        $datareadingplans = [];
        //$datasqlreadingplans= DB::select('select ebm_reading_plans.* from ebm_reading_plans');
        
        $datasqlreadingplans = DB::select('SELECT ebm_reading_plans.* FROM ebm_reading_plans
        WHERE ebm_reading_plans.id_reading_plan IN (SELECT ebm_reading_plan_days.id_reading_plan FROM ebm_reading_plan_days)');

        foreach ($datasqlreadingplans as $rowsqlreadingplans)
        {
            
            $datareadingplans[]=[
                'id' =>$rowsqlreadingplans->id_reading_plan,
                'title' => $rowsqlreadingplans->title,
                'subtitle' => $rowsqlreadingplans->subtitle,
                'description' => $rowsqlreadingplans->description,
                'icon' => $rowsqlreadingplans->icon,
                'color' => $rowsqlreadingplans->color,
            ];
            
        }
    
        $resultereadingplans = [
            'statreadingplans' => 'success',
            'datareadingplans' => array_values($datareadingplans),  
        ];
        return response()->json($resultereadingplans);
    }
    
    //-------------------------------------------------------------------------- API POUR SÉLECTIONNER LE PLAN DE LECTURE --------------------------------------------------------------------------------
    
    public function select_plan(Request $request)
    {
        global $varreference;
        // Récupérer un paramètre GET ou POST avec 'input'
        $uidusers = $request->input('user_id'); 
        $plan_id = $request->input('plan_id'); 
        
        $datadbreadingprogress = DB::table('ebm_user_reading_progress')
        ->where('id_user', $uidusers)
        ->where('id_reading_plan', $plan_id)
        ->first();
        
        $datareference = DB::table('ebm_reading_plan_days')
        ->where('id_reading_plan', $plan_id)
        ->first();
        if(isset($datareference->id_reading_plan_days) and $datareference->id_reading_plan_days >0)
        {
            $varreference=$datareference->reference;
        }
        
        if(isset($datadbreadingprogress->id_user_reading_progress) and $datadbreadingprogress->id_user_reading_progress >0)
        {}
        else
        {
            DB::table('ebm_user_reading_progress')->insert(
                [
                    'id_user' => $uidusers,
                    'id_reading_plan' => $plan_id,
                    'last_read_at' => date('Y-m-d')
                ]
            );
        }  
        
        return response()->json([
            'success' => 'success',
            'reference' => $varreference,
            'message' => 'Plan sélectionné avec succès'
        ]);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LES EVENEMENTS TODAYS SUR HOME PAGE --------------------------------------------------------------------------------
    
    public function today_home_events()
    {
        $dataeventshomescreen = [];
        $events_home_screen= DB::select('select ebm_events_home_screen.* from ebm_events_home_screen where date_evenement=? and type_users=?',[date('Y-m-d'),'Admin']);
        foreach ($events_home_screen as $roweventshome)
        {
            $dataeventshomescreen[]=[
                'idevents' =>$roweventshome->idevents,
                'titre' => $roweventshome->titre,
                'description' => $roweventshome->description,
                'heure' => $roweventshome->heure,
                'date_evenement' => $roweventshome->date_evenement,
                'type' => $roweventshome->type,
                'statut' => $roweventshome->statut,
                'icone' => $roweventshome->icone,
                'couleur' => $roweventshome->couleur,
            ];
        }
    
        $resultereadingplans = [
            'statevents' => 'success',
            'dataevents' => array_values($dataeventshomescreen),  
        ];
        return response()->json($resultereadingplans);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LES DETAILS DES EVENEMENTS TODAYS  --------------------------------------------------------------------------------
    
    public function today_detail_events(Request $request)
    {
        $idevents = $request->input('id'); 
        
        /*
        $priere = DB::table('ebm_events_prieres')
        ->join('ebm_events_home_screen', 'ebm_events_prieres.idevents', '=', 'ebm_events_home_screen.idevents')
        ->where('ebm_events_prieres.idevents', $idevents)
        ->select(
            'ebm_events_prieres.idevents',
            'ebm_events_home_screen.titre',
            'ebm_events_home_screen.description',
            'ebm_events_home_screen.date_evenement',
            'ebm_events_home_screen.heure',
            'ebm_events_home_screen.statut',
            'ebm_events_home_screen.type',
            'ebm_events_home_screen.icone',
            'ebm_events_home_screen.couleur',
            'ebm_events_prieres.verset',
            'ebm_events_prieres.transcription',
            'ebm_events_prieres.audio_url',
            'ebm_events_prieres.fichier_transcription',
            'ebm_events_prieres.vues',
            'ebm_events_prieres.duree_minutes'
        )
        ->first();
        */
        
        //Requête principale sans alias
        $priere = DB::table('ebm_events_home_screen')
        ->where('idevents', $idevents)
        ->select(
            'ebm_events_home_screen.idevents',
            'ebm_events_home_screen.titre',
            'ebm_events_home_screen.description',
            'ebm_events_home_screen.date_evenement',
            'ebm_events_home_screen.heure',
            'ebm_events_home_screen.statut',
            'ebm_events_home_screen.type',
            'ebm_events_home_screen.icone',
            'ebm_events_home_screen.couleur',
            'ebm_events_home_screen.verset',
            'ebm_events_home_screen.transcription',
            'ebm_events_home_screen.audio_url',
            'ebm_events_home_screen.fichier_transcription',
            'ebm_events_home_screen.vues',
            'ebm_events_home_screen.duree_minutes'
        )
        ->first();
        
        if(!$priere) 
        {
            return response()->json(['message' => 'Prière non trouvée'], 404);
        }
        
        // Incrémenter le nombre de vues
        DB::table('ebm_events_home_screen')->where('idevents', $idevents)->increment('vues');
        
        //Récupération des ressources liées (sans alias)
        $ressources = DB::table('ebm_events_ressources_liees')
        //->join('ebm_events_prieres', 'ebm_events_ressources_liees.id_events_prieres', '=', 'ebm_events_prieres.id_events_prieres')
        ->join('ebm_events_home_screen', 'ebm_events_ressources_liees.idevents', '=', 'ebm_events_home_screen.idevents')
        ->where('ebm_events_home_screen.idevents', $idevents)
        ->select('ebm_events_ressources_liees.type as type', 'ebm_events_ressources_liees.titre as titre', 'ebm_events_ressources_liees.note as note', 'ebm_events_ressources_liees.url as url')
        ->get();
        
        //Structuration finale
        $result = [
            'id' => $priere->idevents,
            'titre' => $priere->titre,
            'description' => $priere->description,
            'date' => $priere->date_evenement,
            'heure' => $priere->heure,
            'statut' => $priere->statut,
            'type' => $priere->type,
            'icone' => $priere->icone,
            'couleur' => $priere->couleur,
            'verset' => $priere->verset,
            'transcription' => $priere->transcription,
            'audio_url' => $priere->audio_url,
            'fichier_transcription' => $priere->fichier_transcription,  
            'duree' => $priere->duree_minutes, 
            'vues' => $priere->vues + 1,
            'ressources_liees' => $ressources
        ];
        
        return response()->json($result);

    } 
    
    //-------------------------------------------------------------------------- API POUR SAVE/DELETE FAVORIE IN DETAIL PRIERE(prayer_detail_screen --------------------------------------------------------------------------------
    
    public function save_delete_favorie(Request $request)
    {
        $idusers = $request->input('userId'); 
        $idprayer = $request->input('idprayer'); 
        
        $datadbprayerfavorie = DB::table('ebm_events_favoris_prieres')
        ->where('id_user', $idusers)
        ->where('idevents', $idprayer)
        ->first();
        
        if(isset($datadbprayerfavorie->id_favoris_prieres) and $datadbprayerfavorie->id_favoris_prieres >0)
        {
            $deletedfavorie = DB::delete('delete from ebm_events_favoris_prieres where id_user=? and idevents=?',[$idusers,$idprayer]);
            
            return response()->json([
                'success' => 'true',
                'action' => 'removed',
                'message' => 'Favori supprimé'
            ]);
        }
        else
        {
            DB::table('ebm_events_favoris_prieres')->insert(
                [
                    'id_user' => $idusers,
                    'idevents' => $idprayer,
                    'created_at' => date('Y-m-d H:i:s')
                ]
            );
            
            return response()->json([
                'success' => 'true',
                'action' => 'added',
                'message' => 'Favori ajouté'
            ]);
        }  
    }
    
    //-------------------------------------------------------------------------- API POUR VERIFIER UN PLAN DE LECTURE --------------------------------------------------------------------------------
    
    public function user_reading_plan(Request $request)
    {
        global $planid;
        $idusers = $request->input('user_id');
        $progresplanlcture = DB::table('ebm_user_reading_progress')
        ->where('id_user', $idusers)
        ->first();
        
        if(isset($progresplanlcture->id_user_reading_progress) and $progresplanlcture->id_user_reading_progress >0)
        {
            $planid=$progresplanlcture->id_reading_plan;
        }
        
        return response()->json([
            'status' => 'success',
            'plan_id' => $planid
        ]);
    }
    
    //-------------------------------------------------------------------------- API POUR LE CCONTENU DES PLAN DE LECTURE --------------------------------------------------------------------------------
    
    public function plan_contents(Request $request)
    {
        global $planid;
        $planid = $request->input('plan_id');
        
        $readingplandays = [];
        $datareadingplandays= DB::select('select ebm_reading_plan_days.* from ebm_reading_plan_days where id_reading_plan=?',[$planid]);
        foreach ($datareadingplandays as $rowreadingplandays)
        {
            $readingplandays[]=[
                'id' =>$rowreadingplandays->id_reading_plan_days,
                'daynumber' => $rowreadingplandays->day_number,
                'reference' => $rowreadingplandays->reference
            ];
        }
    
        $resultreadingplandays = [
            'statevents' => 'success',
            'dataevents' => array_values($readingplandays),  
        ];
        return response()->json($resultreadingplandays);
    }
    
    //-------------------------------------------------------------------------- API POUR AFFICHER LES PARAMETRES DU CCONTENU DES PLAN DE LECTURE SELECTIONNER--------------------------------------------------------------------------------
    
    public function plan_contents_select(Request $request)
    {
        global $curentday_save,$currentDay,$totalDays,$livre,$chapter,$verset,$daynumber,$progress,$reference,$dbactivereaderstoday;
        $contentid = $request->input('content_id');
        $userId = $request->input('user_id');
        $plan_id = $request->input('plan_id');
        
        
        $readingplandays = DB::table('ebm_reading_plan_days')
        ->where('id_reading_plan_days', $contentid)
        ->first();
        
        if(isset($readingplandays->id_reading_plan_days) and $readingplandays->id_reading_plan_days >0)
        {
            $livre =$readingplandays->livre;
            $chapter =$readingplandays->chapter;
            $verset =$readingplandays->verset;
            $currentDay =$readingplandays->day_number;
            
            if(isset($userId) && $userId!='')
            {
                $activereaderstoday= DB::select('select active_readers_today.* from active_readers_today where id_user=? and read_at=?',[$userId,date('Y-m-d')]);
                foreach ($activereaderstoday as $rowactivereaderstoday){$dbactivereaderstoday=$rowactivereaderstoday->id;}
                    
                if($dbactivereaderstoday >0){}
                else
                {
                    DB::table('active_readers_today')->insert(
                        [
                            'id_user' => $userId,
                            'read_at' => DB::raw('NOW()'),
                            'created_at' => DB::raw('NOW()')
                        ]
                    );
                }
            }
        }
        
        $userReading = DB::table('ebm_user_reading_progress')
        ->where('id_user', $userId)
        ->first();

        if ($userReading && $userReading->last_read_at) 
        {
            $curentday_save = $userReading->current_day;
            
            $Readingplan = DB::table('ebm_reading_plans')
            ->where('id_reading_plan', $userReading->id_reading_plan)
            ->first();
            if(isset($Readingplan->id_reading_plan) and $Readingplan->id_reading_plan >0) 
            {
                $totalDays = $Readingplan->total_days;
                $idreadingplan = $Readingplan->id_reading_plan;
            }
            
            /*
            $startedAt = Carbon::parse($userReading->last_read_at);
            $today = Carbon::today();
            $currentDay = $startedAt->diffInDays($today) + 1;
        
            // Limiter au nombre total de jours du plan
            $currentDay = min($currentDay, $totalDays);
            */
        }
        
        
        //$totalDays = $plan->days;
        // Calcul du pourcentage d’avancement
        
        $progress = min(100, round(($currentDay / $totalDays) * 100, 2));
        
        
        // Création ou mise à jour
        if($currentDay > $curentday_save)
        {
            DB::table('ebm_user_reading_progress')
            ->where('id_user', $userId)
            ->where('id_reading_plan', $idreadingplan)
            ->update([
                'current_day' => $currentDay,
                'completed' => $progress
            ]);
        }
        
        if(isset($userId) && isset($plan_id))
        {
            $userReading2 = DB::table('ebm_user_reading_progress')
            ->where('id_user', $userId)
            ->where('id_reading_plan', $plan_id)
            ->first();
            
            $progress = $userReading2->completed;
            $currentDay = $userReading2->current_day;
        }
        
        $readingplandays2 = DB::table('ebm_reading_plan_days')
        ->where('id_reading_plan', $plan_id)
        ->first();
        
        if(isset($readingplandays2->id_reading_plan_days) and $readingplandays2->id_reading_plan_days >0)
        {
            $reference =$readingplandays2->reference;
        }
        
        
        
        return response()->json([
            'success' => 'true',
            'book' => $livre,
            'chapter' => $chapter,
            'range' => $verset,
            'progression' => $progress,
            'current_day' => $currentDay,
            'total_days' => $totalDays,
            'reference' => $reference
        ]);
    }

    
    //-------------------------------------------------------------------------- API POUR COMPTER LES LECTEURS ACTIFS AUJOURD'HUI --------------------------------------------------------------------------------
    
    
    //public function active_readers_today(Request $request) / $userid = $request->query('user_id');
    public function active_readers_today()
    {
        global $rownbrconnect;
           
        $activereaderstoday= DB::select('select count(id) as nbrconnect from active_readers_today where read_at=?',[date('Y-m-d')]);
        foreach ($activereaderstoday as $rowactivereaderstoday){$rownbrconnect=$rowactivereaderstoday->nbrconnect;}
        
        return response()->json([
            'success' => 'true',
            'active_readers' => $rownbrconnect
        ]);
        
    }
    
    //-------------------------------------------------------------------------- API POUR L'AVATAR LES LECTEURS ACTIFS AUJOURD'HUI --------------------------------------------------------------------------------
    
    public function view_avatar_active_readers_today()
    {
        global $planiduser;
    
        $readingplanactivedays = [];
        
        $datareadingdays= DB::select('select * from active_readers_today where read_at=?',[date('Y-m-d')]);
        foreach ($datareadingdays as $rowdatareadingdays)
        {
            $planiduser = $rowdatareadingdays->id_user;;
            $readingplanactivedays[$planiduser]=[
                'avatar' => [],
            ];
            
            $avatareadactive= DB::select('select * from ebm_users where id_user=?',[$rowdatareadingdays->id_user]); 
            foreach ($avatareadactive as $rowavatareadactive)
            {
                $readingplanactivedays[$planiduser]['avatar'][] = [
                    'id_user' => $rowavatareadactive->id_user,
                    'avatar_url' => $rowavatareadactive->avatar,
                    //'nameavatar' => $rowavatarforums->nom.' '.$rowavatarforums->prenom,
                    // Ajoutez d'autres attributs d'image ici
                ];
            }
        }
    
        $resultreadingplandaysactive = [
            'status' => 'success',
            'datareadingdaysactive' => array_values($readingplanactivedays),  
        ];
        return response()->json($resultreadingplandaysactive);
    }

    
    //---------------------------------------------------- ENVOIE DE TOKEN APRES CONNEXION REUSSIR ---------------------------------------------------------
    

    public function update_fcm_token(Request $request)
    {
        $idusers=$request->get('user_id');
        $fcm_token=$request->get('fcm_token');
        
        DB::table('ebm_users')
        ->where('id_user', $idusers)
        ->update([
            'fcm_token' => $fcm_token
        ]);
        
        return response()->json([
            'statusCode' => 200
        ]);
    }

}
