<?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 Poll extends Eloquent {

    // Get poll result 
    public function getPollWithNumber($poll_id) {
        $query = 'SELECT op.* , votes.number '
                . 'FROM user_poll_options as op '
                . 'LEFT JOIN (SELECT poll_option_id, count(*) number '
                . 'FROM user_poll_voters '
                //  . "WHERE date(created_at)='$date'"
                . 'GROUP BY poll_option_id) as votes '
                . 'ON op.poll_option_id = votes.poll_option_id '
                . "WHERE op.poll_id=$poll_id ORDER BY votes.number desc";
        $data = DB::select($query);

        return $data;
    }

    // Get Poll Id by Publish date 
    public function getPollIdByDate($date) {
        $data = DB::table('user_polls')->where(DB::raw('date(publish_date)'), $date)->first();
        $data = isset($data->poll_id) ? $data->poll_id : 0;

        return $data;
    }

// Get Poll text by poll id
    public function getPollTextById($poll_id) {
        $data = DB::table('user_polls')->where('poll_id', $poll_id)->first();
        $data = isset($data->poll_text) ? $data->poll_text : '';

        return $data;
    }

    // Get Only Poll details by poll id
    public function getOnlyPollTextById($poll_id) {
        $data = DB::table('user_polls')->where('poll_id', $poll_id)->first();

        return $data;
    }

    // get Only poll list
    public function getOnlyPolls($poll_limit) {
        $data = DB::table('user_polls')->take($poll_limit)->where('status', 1)->orderBy('poll_id', 'desc')->get();

        return $data;
    }

    // get Only poll list
    public function getPollOptionsById($poll_id) {
        $data = DB::table('user_poll_options')->where('poll_id', $poll_id)->orderBy('poll_option_id', 'desc')->get();

        return $data;
    }

    // get Only poll list
    public function getPollsWithPagination() {
        $data = DB::table('user_polls')->orderBy('poll_id', 'desc')->paginate(10);

        return $data;
    }

    // get random Poll ID

    public function getRandomPollId() {
        $data = DB::table('user_polls')->where('status', 1)->orderBy(DB::raw('RAND()'))->first();

        return $data->poll_id;
    }

    // Get poll by publish date
    public function getPollList($date) {
        $data = DB::table('user_polls as p')
                ->join('user_poll_options as o', 'o.poll_id', '=', 'p.poll_id');
        if ($date != '') {
            $data = $data->where(DB::raw('date(p.publish_date)'), $date);
        }
        $data = $data->where('status', 1)->get();

        return $data;
    }

    public function getAllPollList($poll_limit) {
        $data = DB::table('user_polls')->take($poll_limit)->where('status', 1)->orderBy('poll_id', 'desc')->get();

        return $data;
    }

    // Get poll by poll id 
    public function getPollById($poll_id) {
        $data = DB::table('user_polls as p')
                ->join('user_poll_options as o', 'o.poll_id', '=', 'p.poll_id');
        if ($poll_id != '') {
            $data = $data->where('p.poll_id', $poll_id);
        }
        $data = $data->where('status', 1)->get();

        return $data;
    }

    // User vote save
    public function vote($data) {
        $check_expiry = 0;
        $date = date('Y-m-d');
        $check_voting_date = DB::table('user_polls')->select(DB::raw('date(voting_last_date) as voting_last_date'))->where('status', 1)->where('poll_id', $data['poll_id'])->first();
        $check_voting_date = $check_voting_date->voting_last_date;
        $todaytime = strtotime($date);
        $expiretime = strtotime($check_voting_date);

        if ($expiretime <= $todaytime) {
            $check_expiry = 1;
        }
        $check = DB::table('user_poll_voters')->where('fbid', $data['fbid'])
                        ->where('poll_id', $data['poll_id'])->count();
        //->where(DB::raw('date(created_at)'), $date)->count();
        if ($check_expiry > 0) {
            return 2;
        }
        if ($check > 0) {
            return 0;
        } else {
            DB::table('user_poll_voters')->insert($data);

            return 1;
        }
    }

    // @poll_id
    // Get poll next ID 
    public function getPollNextId($poll_id) {
        $data = DB::table('user_polls')->where('poll_id', '<', $poll_id)->orderBy('poll_id', 'desc')->first();

        return isset($data->poll_id) ? $data->poll_id : 0;
    }

    // @short_code 
    // Get Poll By Short Code
    public function getPollByShortCode($short_code) {
        $data = DB::table('user_polls')->where('short_code', $short_code)->orderBy('poll_id', 'desc')->first();

        return isset($data->poll_id) ? $data->poll_id : 0;
    }

    // Get Poll short code by ID
    public function getPollShortCodeById($poll_id) {
        $data = DB::table('user_polls')->where('poll_id', $poll_id)->first();
        $data = isset($data->short_code) ? $data->short_code : '';

        return $data;
    }

    // Save new poll 
    public function savePoll($poll_data, $option_data) {
        $p_data = array();
        $poll_id = DB::table('user_polls')->insertGetId($poll_data);
        $p_data['poll_id'] = $poll_id;
        foreach ($option_data as $op) {
            $p_data['poll_option_text'] = $op;
            DB::table('user_poll_options')->insert($p_data);
        }

        return true;
    }

    // Save new Poll option
    public function saveNewPollOption($data) {
        DB::table('user_poll_options')->insert($data);

        return true;
    }

    // Change poll status 
    public function changePollStatusById($poll_id, $status) {
        $data = DB::table('user_polls')->where('poll_id', $poll_id)->update(array('status' => $status));
        if ($data) {
            return true;
        } else {
            return false;
        }
    }

    public function changePollFeatureStatusById($poll_id, $status) {
        $data = DB::table('user_polls')->where('poll_id', $poll_id)->update(array('is_featured' => $status));

        return true;
    }

    // Delete poll by Id 
    public function deletePollById($poll_id) {
        DB::table('user_polls')->where('poll_id', $poll_id)->delete();
        DB::table('user_poll_options')->where('poll_id', $poll_id)->delete();

        return true;
    }

    // Update poll option by Id 
    public function updatePollOptionsById($poll_option_id, $poll_op_data) {
        DB::table('user_poll_options')->where('poll_option_id', $poll_option_id)->update($poll_op_data);

        return true;
    }

    // Update poll by Id 
    public function updatePollById($poll_id, $poll_data) {
        DB::table('user_polls')->where('poll_id', $poll_id)->update($poll_data);

        return true;
    }

    // Update poll by Id 
    public function deletePollOptionById($poll_option_id) {
        DB::table('user_poll_options')->where('poll_option_id', $poll_option_id)->delete();

        return true;
    }

    // User polls
    public function getPollsByUser($user_id) {
        $data = DB::table('user_polls as p')->take(10)
                        ->select('p.poll_id', 'p.short_code', 'p.poll_text', 'v.created_at', 'u.name')
                        ->join('user_poll_voters as v', 'p.poll_id', '=', 'v.poll_id')
                        ->join('users as u', 'u.fbid', '=', 'v.fbid')
                        ->where('u.fbid', $user_id)->orderBy('v.poll_voter_id', 'desc')->get();

        return $data;
    }

}
