<?php

/*
 * Vinnorokom Facebook Quiz App V.2.1.3
 *
 * All table connections
 * Fetching data
 * Only DB class using for fetching data from default database
 * Check config file to see default database.
 * Dont change anything if you dont know about these :)
 * Extent Eloquent
 *  */

class Quiz extends Eloquent {
    /*
     * @param quiz_id
     * @param group_id
     * @return quiz list by group and quiz id
     */

    public function getQuizList($quiz_id, $group_id) {
        $data = DB::table('user_quiz as q')->where('id', $quiz_id)->where('group_id', $group_id)->first();

        return $data;
    }

    /*
     * Default admin dashboard data
     */

    public function dashBoard() {
        $data['user_count'] = DB::table('users')->where('user_level', 0)->count();
        $data['is_shared'] = DB::table('user_quiz_result')->where('is_shared', 1)->count();
        $data['quiz_complete'] = DB::table('user_quiz_result')->count();
        //Last 10 days user count RAW SQL : select * from dt_table where `date` >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
        $count = DB::select('select count(*) as cnt from users where created_at >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)');
        $data['last_week_user'] = $count[0]->cnt;

        return $data;
    }

    /*
     *  Recent user quiz activity
     *
     */

    public function getRecentQuiz($group_id) {
        $data = DB::table('users as u')
                ->join('user_quiz_result as uq', 'uq.fid', '=', 'u.fbid')
                ->leftjoin('user_quiz_group as ug', 'ug.group_id', '=', 'uq.group_id')
                ->select('u.fbid', 'u.name', 'u.email', 'u.created_at', 'u.status', 'uq.total', 'ug.group_name', 'uq.true_ans', 'uq.false_ans', 'uq.avg_grade', 'uq.quiz_date')
                ->where('user_level', 0);
        if ($group_id > 0) {
            $data = $data->where('ug.group_id', $group_id);
        }
        $data = $data->orderBy('uq.quiz_date', 'desc')->paginate(10);

        return $data;
    }

    /*
     * @param quiz_id
     * @return quiz by quiz id
     */

    public function getQuizListById($quiz_id) {
        $data = DB::table('user_quiz as q')->where('id', $quiz_id)->first();

        return $data;
    }

    /*
     * @param random
     * @return if random return random
     */

    public function getOptionList($random) {
        $data = DB::table('user_quiz_options');
        if ($random == 1) {
            $data->orderBy(DB::raw('RAND()'));
        }
        $data = $data->orderBy('id', 'desc')->get();

        return $data;
    }

    /*
     * @param qid
     * @return options by quiz id
     */

    public function getOptionListById($id) {
        $data = DB::table('user_quiz_options')->where('qid', $id)->get();

        return $data;
    }

    /*
     * @param session_id
     * @param fbid
     * @param quiz_id
     *
     * @return count of user last parcipate
     */

    public function getLastParticipant($session_id, $fbid, $quiz_id) {
        $data = DB::table('user_quiz_temp')->where('uid', $fbid)
                        ->where('session_id', $session_id)
                        ->where('qid', $quiz_id)->count();

        return $data;
    }

    /*
     * @param quiz_id
     * @param group_id
     * @return quiz data by quiz and group id
     */

    public function nextQuiz($next_quiz, $group_id) {
        $data['quiz_count'] = DB::table('user_quiz')->where('group_id', '=', $group_id)->count(); // Total quiz count in a group
        $data['quiz_data'] = DB::table('user_quiz')->where('id', '>', $next_quiz)->where('group_id', '=', $group_id)->orderBy('id', 'asc')->first();
        $data['quiz_remain_count'] = DB::table('user_quiz')->where('id', '>', $next_quiz)->where('group_id', '=', $group_id)->count(); // Total remaining quiz in this group

        return $data;
    }

    public function nextQuizId($quiz_id, $group_id) {
        $data = DB::table('user_quiz')->where('id', '>', $quiz_id)->where('group_id', $group_id)->orderBy('id', 'asc')->first();

        return $data;
    }

    /*
     * @param data array
     * @return if false return last quiz_temp table id
     */

    public function savaQuizData($data) {
        $check = DB::table('user_quiz_temp')->where('qid', $data['qid'])->where('session_id', $data['session_id'])->where('uid', $data['uid'])->count();
        if ($check > 0) {
            return false;
        } else {
            $quiz = DB::table('user_quiz_temp')->insertGetId($data);

            return $quiz;
        }
    }

    /*
     * @param data array
     * @returm boolean
     *
     */

    public function insertQuizOption($qoptn) {
        DB::table('user_quiz_options')->insert($qoptn);

        return true;
    }

    /*
     * @param data array
     * @returm last inserted ans id
     *
     */

    public function insertQuizOptionGetId($data) {
        $ans_id = DB::table('user_quiz_options')->insertGetId($data);

        return $ans_id;
    }

    /*
     * @param quiz_id
     * @param ans_id
     * @return boolean
     */

    public function updateQuizById($quiz_id, $ans_id) {
        DB::table('user_quiz')->where('id', $quiz_id)->update(array('ans' => $ans_id));

        return true;
    }

    /*
     * @param session_id
     * @param user_id
     * @return user result data
     */

    public function getResult($session_id, $user_id) {
        $data = DB::select('select user_quiz.id,user_quiz.text, '
                        . '(select user_quiz_options.qtext from user_quiz_options '
                        . 'where user_quiz_temp.qid=user_quiz_options.qid and user_'
                        . 'quiz_options.id=user_quiz_temp.ur_ans) your_ans, '
                        . '(select user_quiz_options.qtext '
                        . 'from user_quiz_options where user_'
                        . 'quiz.id=user_quiz_options.qid and user_quiz_options.id=user_quiz.ans) true_ans, '
                        . "case when user_quiz.ans=user_quiz_temp.ur_ans then '1' else '0' end "
                        . "comments,user_quiz_temp.date from user_quiz,user_quiz_temp where user_quiz.id=user_quiz_temp.qid and user_quiz_temp.session_id='$session_id' and user_quiz_temp.uid=$user_id");

        return $data;
    }

    /*
     * @param data array
     * @return boolean
     */

    public function saveResults($data) {
        if (DB::table('user_quiz_result')->where('group_id', $data['group_id'])->where(DB::raw('date(quiz_date)'), date('Y-m-d'))->count() > 0) {
            return false;
        }
        if (DB::table('user_quiz_result')->where('session_id', $data['session_id'])->count() > 0) {
            return false;
        }
        $save_result = DB::table('user_quiz_result')->insertGetId($data);

        return $save_result;
    }

    /*
     * @param timestamp
     * @return boolean
     */

    public function checkResultExist($timestamp) {
        $check = DB::table('user_quiz_result')->where('timestamp', $timestamp)->count();
        if ($check > 0) {
            return true;
        }

        return false;
    }

    /*
     * @param fbid facebook user id
     * @return result data
     */

    public function getQuizResultByUser($fbid) {
        $data = DB::table('user_quiz_result as r')
                        ->join('user_quiz_group as g', 'r.group_id', '=', 'g.group_id')->where('fid', $fbid)->orderBy('quiz_date', 'desc')->paginate(10);

        return $data;
    }

    /* Get quiz results */

    public function getQuizResultByUserId($fbid) {
        $data = DB::table('user_quiz_result as r')
                        ->join('user_quiz_group as g', 'r.group_id', '=', 'g.group_id')->where('fid', $fbid)->orderBy('quiz_date', 'desc')->get();

        $total_quiz = count($data); // Total quiz
        $pass = 0;
        foreach ($data as $d) {
            if ($d->false_ans == 0) {
                ++$pass;
            }
        }
        $fails = ($total_quiz - $pass);
        $dataArray = array('total' => $total_quiz, 'pass' => $pass, 'fails' => $fails);

        return $dataArray;
    }

    /* Get all quiz list */

    public function getAllQuizList($group_id = 0) {
        $data = DB::table('user_quiz as q')->leftJoin('user_quiz_group as g', 'q.group_id', '=', 'g.group_id');
        if ($group_id > 0) {
            $data = $data->where('q.group_id', $group_id);
        }
        $data = $data->orderBy('id', 'desc')->paginate(10);
        // $data = DB::table('user_quiz as q')->leftJoin('user_quiz_group as g', 'q.group_id', '=', 'g.group_id')->orderBy('id', 'desc')->paginate(10);
        return $data;
    }

    // Delete a quiz by ID
    public function quizDelete($id) {
        DB::table('user_quiz')->where('id', $id)->delete();
        DB::table('user_quiz_options')->where('qid', $id)->delete();

        return true;
    }

    // Get quiz group
    public function getQuizGroup() {
        return DB::table('user_quiz_group')->orderBy('group_id', 'desc')->paginate(10);
    }

    // Get all quiz group
    public function getAllQuizGroup() {
        return DB::table('user_quiz_group')->orderBy('group_id', 'desc')->get();
    }

    // Get quiz group by ID
    public function getQuizGroupById($group_id) {
        return DB::table('user_quiz_group')->where('group_id', $group_id)->first();
    }

    // Get quiz active group by ID
    public function getActiveQuizGroup() {
        $group_ids = array(0);
        $group_id = $this->getActiveScheduleGroup();
        foreach ($group_id as $gp) {
            $group_ids[] = $gp->group_id;
        }

        return DB::table('user_quiz_group')->where('status', 1)->whereIn('group_id', $group_ids)->get();
    }

    // Get quiz active group by ID
    public function getActiveScheduleGroup() {
        $today = date('Y-m-d');

        return DB::select('select * from user_quiz_schedule where curdate()>=date(start_date) and curdate()<=date(end_date)');
    }

    // Update quiz option @param data if success @return true
    public function updateQuizOptions($data) {
        $quiz_up = DB::table('user_quiz')->where('id', $data['quiz_id'])->update(array('group_id' => $data['quiz_group'], 'text' => $data['quiz'], 'ans' => $data['addoptn']));
        foreach ($data['option_text'] as $key => $value) {
            DB::table('user_quiz_options')->where('id', $key)->update(array('qtext' => $value));
        }

        return true;
    }

    // Delete group by group id
    public function deleteGroupName($group_id) {
        DB::table('user_quiz_group')->where('group_id', $group_id)->delete();

        return true;
    }

    // Update group by data array
    public function updateQuizGroup($data) {
        DB::table('user_quiz_group')->where('group_id', $data['group_id'])->update(array('group_name' => $data['group_name'],
            'status' => $data['status'], 'image_path' => $data['image_path'], 'is_public' => $data['is_public'],));

        return true;
    }

    /*
     *  Update quiz setting
     *  @data array
     *  @return boolean
     */

    public function updateQuizSetting($data) {
        $max_id = DB::table('user_quiz_setting')->max('quiz_setting_id');  // Get max id
        if ($max_id == '') {
            DB::table('user_quiz_setting')->insert($data);
        } else {
            DB::table('user_quiz_setting')->where('quiz_setting_id', $max_id)->update($data);
        }
        $new_data = array();
        $new_data['start_date'] = $data['publish_from'];
        $new_data['end_date'] = $data['publish_to'];
        $new_data['group_id'] = $data['default_group'];
        $check = DB::table('user_quiz_schedule')->where('group_id', $new_data['group_id'])->count();
        if ($check > 0) {
            return false;
        } else {
            DB::table('user_quiz_schedule')->insert($new_data);
        }

        // DB::table('user_quiz_group')->update(array('status' => 0));
        // DB::table('user_quiz_group')->where('group_id', $data['default_group'])->update(array('status' => 1));

        return true;
    }

    // Get quiz setting data
    public function getQuizSetting() {
        $max_id = DB::table('user_quiz_setting')->max('quiz_setting_id'); // Get max id
        $data = DB::table('user_quiz_setting')->where('quiz_setting_id', $max_id)->first();

        return $data;
    }

    // Get page setting data
    public function getPageSetting() {
        $get_max_id = DB::table('user_page_setting')->max('page_id');  // Get max id
        $data = DB::table('user_page_setting')->where('page_id', $get_max_id)->first();

        return $data;
    }

    // Update page setting data
    public function updatePageSetting($data) {
        $get_max_id = DB::table('user_page_setting')->max('page_id');  // Get max id
        if ($get_max_id == '') {
            DB::table('user_page_setting')->insert($data);
        } else {
            $data = DB::table('user_page_setting')->where('page_id', $get_max_id)->update($data);
        }

        return true;
    }

    // Get user list
    public function getUsersList() {
        $data = DB::table('users')->where('user_level', 0)->orderBy('id', 'desc')->paginate(10);

        return $data;
    }

    /*
     * User details by user id
     * @param user fb id
     * @return user quiz result details
     *
     */

    public function getUserResultDataById($user_id) {
        $data = DB::table('users as u')->leftJoin('user_quiz_result as ul', 'ul.fid', '=', 'u.fbid')
                        ->leftJoin('user_quiz_group as g', 'g.group_id', '=', 'ul.group_id')
                        ->where('u.fbid', $user_id)
                        ->orderBy('quiz_date', 'desc')->paginate(10);

        return $data;
    }

    /*
     * Block user function
     * @param user_id
     * @return boolean
     */

    public function blockUser($uid) {
        $get_status = DB::table('users')->where('fbid', $uid)->first();
        if ($get_status->status == 1) {
            DB::table('users')->where('fbid', $uid)->where('user_level', 0)->update(array('status' => 0));
        }
        if ($get_status->status == 0) {
            DB::table('users')->where('fbid', $uid)->where('user_level', 0)->update(array('status' => 1));
        }

        return true;
    }

    /*
     * Update share status
     * @param data array
     * @param session id
     * @param user id
     * @return boolean
     */

    public function updateResultShareStatus($data, $session, $user_id) {
        DB::table('user_quiz_result')->where('fid', $user_id)->where('timestamp', $session)->update($data);

        return true;
    }

    /*
     * Facebook data for sharing
     * @param timestamp
     * @return data array
     */

    public function facebookDataforSharing($timestamp, $user_id) {
        $data = DB::table('users')->join('user_quiz_result', 'users.fbid', '=', 'user_quiz_result.fid')
                        ->where('user_quiz_result.timestamp', $timestamp)
                        ->where('users.fbid', $user_id)->first();

        return $data;
    }

    /* New Updates */

    // Graph data
    public function dailyQuizGraph($today) {
        $query = "select concat(TEMP.hours,':00') h, if(TEMP1.cnt is NULL,0,TEMP1.cnt) cnt from (select  1 as hours
            union
            select 2            union            select 3
            union
            select 4            union            select 5
            union
            select 6            union            select 7
            union            select 8            union
            select 9            union            select 10
            union            select 11            union
            select 12            union            select 13
            union            select 14            union
            select 15            union            select 16
            union            select 17            union
            select 18            union            select 19
            union            select 20
            union            select 21            union
            select 22            union            select 23
            union            select 00) as TEMP
             left join (select HOUR(quiz_date) h, count(*) as cnt from user_quiz_result as od where HOUR(quiz_date) and date(quiz_date)='$today' group by h) as TEMP1 on TEMP1.h=TEMP.hours ";
        $data = DB::select($query);

        return $data;
    }

    // Schedule list
    public function getQuizSchedule() {
        $data = DB::table('user_quiz_schedule')->get();

        return $data;
    }

    // Pagination schedule
    public function getQuizSchedulePaginate() {
        $data = DB::table('user_quiz_schedule as sc')
                        ->join('user_quiz_group as gp', 'gp.group_id', '=', 'sc.group_id')->orderby('schedule_id', 'desc')->paginate(10);

        return $data;
    }

    public function scheduleDelete($schdule_id) {
        if (DB::table('user_quiz_schedule')->where('schedule_id', $schdule_id)->delete()) {
            return true;
        } else {
            return false;
        }
    }

    // Upload miedia files
    public function uploadMedia($data) {
        DB::table('media_files')->insert($data);

        return true;
    }

    // Page load by slug
    public function pageLoadBySlug($slug) {
        $data = DB::table('user_pages')->where('slug', 'like', "%$slug%")->where('published', 1)->first();

        return $data;
    }

    // Get top scorer
    public function getTopScorer() {
        $top_scores = DB::table('user_quiz_result as r')
                        ->join('users as u', 'u.fbid', '=', 'r.fid')
                        ->join('user_quiz_group as g', 'g.group_id', '=', 'r.group_id')
                        ->select('u.name', 'r.avg_grade', 'r.quiz_date', 'u.fbid', 'g.group_name')->take(4)
                        ->orderby('r.avg_grade', 'desc')->get();

        return $top_scores;
    }

    // Get public quiz list for frontend

    public function getPublicQuizeGroups() {
        $data = DB::table('user_quiz_group')->where('is_public', 1)->where('status', 1)->get();

        return $data;
    }

    // Get Public quiz list by quiz group id

    public function getPublicQuizzes($group_id = 0, $question_id = 0) {
        $data = DB::table('user_quiz_group as g')
                ->join('user_quiz as q', 'q.group_id', '=', 'g.group_id')
                ->where('g.is_public', 1)->where('q.is_public', 1)
                ->where('g.status', 1);

        if ($group_id > 0) {
            $data = $data->where('g.group_id', $group_id);
        }
        if ($question_id > 0) {
            $data = $data->where('q.id', $question_id);
        } else {
            $question_id = DB::table('user_quiz')->where('group_id', $group_id)->min('id');
            $data = $data->where('q.id', $question_id);
        }

        return $data->orderBy('q.id', 'asc')->first();
    }

    // Insert public user data
    public function insertQuizTempdata($session_data, $user_id) {
        $data = array();
        $session_id = Session::getId();
        if (DB::table('user_quiz_temp')->where('uid', $user_id)->where('session_id', $session_id)->count() > 0) {
            return false;
        }
        foreach ($session_data as $s) {
            $data[] = array('uid' => $user_id,
                'ur_ans' => $s['answer_id'],
                'qid' => $s['question_id'],
                'session_id' => $session_id,
                'date' => date('Y-m-d'),);
        }
        DB::table('user_quiz_temp')->insert($data);
    }

}
