<?php
/**
 * User: David Camacho
 * Date: 01/04/2015
 * Time: 1:34
 */

class Course extends DataBaseController{
    public function Course(){

        parent::DataBaseController();

    }


    public function getInscriptionCourses(){

        $token = $_SESSION["token"];

        $res = array("name" => array(),
                    "id_course" => array(),
                    "description" => array());

        $sql = "SELECT c.name, c.id_course, c.description
				FROM " . _db_prefix . "_course c,
				" . _db_prefix . "_course_inscription ci,
				" . _db_prefix . "_user u
				WHERE c.id_course = ci.id_course AND
				u.id_user= ci.id_user AND
                u.token = ?";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('s',$token);
            $tmp->execute();
            $tmp->bind_result($result1,$result2, $result3);
            while ($tmp->fetch()) {
                array_push($res["name"],$result1);
                array_push($res["id_course"],$result2);
                array_push($res["description"],$result3);

            }
            $tmp->close();
            return $res;
        }
    }

    public function getStudentInscriptions($id_course){

        $res = array("id_user" => array(),
            "name" => array()
            );

        $sql = "SELECT ci.id_user, u.name
				FROM " . _db_prefix . "_course_inscription ci,
				" . _db_prefix . "_user u
				WHERE ci.id_course = ? AND
				u.id_user = ci.id_user";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1, $result2);
            while ($tmp->fetch()) {
                array_push($res["id_user"],$result1);
                array_push($res["name"],$result2);
            }
            $tmp->close();
            return $res;
        }
        return false;
    }


    public function getCourseName($id_course){

        $sql = "SELECT DISTINCT name
				FROM " . _db_prefix . "_course
				WHERE id_course = ?
				";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            if ($tmp->fetch()) {
                return $result1;
            }
            $tmp->close();
            return false;
        }
        return false;
    }

    public function getCourseDescription($id_course){

        $sql = "SELECT DISTINCT description
				FROM " . _db_prefix . "_course
				WHERE id_course = ?
				";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            if ($tmp->fetch()) {
                return $result1;
            }
            $tmp->close();
            return false;
        }
        return false;
    }

    public function getCourseExerciseWeight($id_course){

        $sql = "SELECT DISTINCT exercise_tests_weight
				FROM " . _db_prefix . "_course
				WHERE id_course = ?
				";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            if ($tmp->fetch()) {
                return $result1;
            }
            $tmp->close();
            return false;
        }
        return false;
    }


    public function getCoursePracticeWeight($id_course){

        $sql = "SELECT DISTINCT practice_tests_weight
				FROM " . _db_prefix . "_course
				WHERE id_course = ?
				";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            if ($tmp->fetch()) {
                return $result1;
            }
            $tmp->close();
            return false;
        }
        return false;
    }

    public function getCoursePartialWeight($id_course){

        $sql = "SELECT DISTINCT partial_tests_weight
				FROM " . _db_prefix . "_course
				WHERE id_course = ?
				";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            if ($tmp->fetch()) {
                return $result1;
            }
            $tmp->close();
            return false;
        }
        return false;
    }



    public function getCoursesCreated(){

        $token = $_SESSION["token"];

        $res = array("name" => array(),
            "id_course" => array(),
            "description" => array());

        $sql = "SELECT c.name, c.id_course, c.description
				FROM " . _db_prefix . "_course c,
				" . _db_prefix . "_user u
				WHERE u.id_user= c.id_user_creator AND
                u.token = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('s',$token);
            $tmp->execute();
            $tmp->bind_result($result1,$result2, $result3);
            while ($tmp->fetch()) {
                array_push($res["name"],$result1);
                array_push($res["id_course"],$result2);
                array_push($res["description"],$result3);

            }
            $tmp->close();
            return $res;
        }
    }

    public function getHistoricCourses($id_course){

        $res = array("id_course" => array());

        $sql = "SELECT id_historic_course
                FROM " . _db_prefix . "_historic
                WHERE id_current_course = ?
                ";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result);
            while ($tmp->fetch()) {
                array_push($res["id_course"],$result);

            }
            $tmp->close();
            return $res;
        }
    }

    public function getTestsByCourse($id_course){

        $res = array("id_test" => array(),
            "title" => array(),
            "test_type" => array()
            );

        $sql = "SELECT t.id_test, t.title, t.test_type
                FROM " . _db_prefix . "_test t, " . _db_prefix . "_topic top
                WHERE t.id_topic = top.id_topic
                AND top.id_course = ?
                ORDER BY t.test_type = 'partial', t.test_type = 'exercise', t.test_type = 'practice'";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1,$result2, $result3);
            while ($tmp->fetch()) {
                array_push($res["id_test"],$result1);
                array_push($res["title"],$result2);
                array_push($res["test_type"],$result3);
            }
            $tmp->close();
            return $res;
        }
    }

    public function validateCurrentCourse($id_course){

        $step1_validation = false;
        $step2_validation = false;


        //STEP 1 : Check if course has a practice, exercise and partial test.
        $has_exercise = false;
        $has_practice = false;
        $has_partial = false;

        $sql = "SELECT t.test_type
                FROM " . _db_prefix . "_course c, " . _db_prefix . "_topic top, " . _db_prefix . "_test t
                WHERE c.id_course = ? AND
                c.id_course = top.id_course AND
                t.id_topic = top.id_topic
";
        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i', $id_course);
            $tmp->execute();
            $tmp->bind_result($res1);


            while ($tmp->fetch()) {
                if ($res1 == "exercise") $has_exercise = true;
                if ($res1 == "practice") $has_practice = true;
                if ($res1 == "partial") $has_partial = true;

            }
            $tmp->close();
            if ($has_partial && $has_exercise && $has_practice) $step1_validation = true;


            //STEP 2 : Check if course has at least 2 student


            $sql = "SELECT count(id_user)
                    FROM " . _db_prefix . "_course_inscription
                    WHERE id_course = ?";
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i', $id_course);
            $tmp->execute();
            $tmp->bind_result($res1);


            if ($tmp->fetch()) {
                if ($res1>1) $step2_validation = true;
            }
            $tmp->close();



            if ($step1_validation && $step2_validation) return true;
            else return false;

        }
        return false;
    }


    public function hasCourseInscription($id_user, $id_course){


        $sql = "SELECT count(id_course_inscription)
                FROM " . _db_prefix . "_course_inscription
                WHERE id_user = ?
                AND id_course = ?
                ";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ii',$id_user,$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            $tmp->fetch();
            if ($result1>0) {
                $tmp->close();
                return true;
            }
            $tmp->close();
            return false;
        }
        return false;
    }


    public function removeCourseInscription($id_user, $id_course)
    {
        $sql = "DELETE FROM " . _db_prefix . "_course_inscription
        WHERE id_user = ? AND id_course = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ii', $id_user, $id_course);
            $tmp->execute();
            $tmp->fetch();

            return  true;

        }
        return false;
    }


    public function deleteCourse($id_course)
    {
        $sql = "DELETE FROM " . _db_prefix . "_course
        WHERE id_course = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i', $id_course);
            $tmp->execute();
            $tmp->fetch();

            return  true;

        }
        return false;

    }

    public function deleteAllCourseInscription($id_course)
    {
        $sql = "DELETE FROM " . _db_prefix . "_course_inscription
        WHERE id_course = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i', $id_course);
            $tmp->execute();
            $tmp->fetch();

            return  true;

        }
        return false;
    }





    public function deleteHistoricCourse($id_current_course)
    {
        $sql = "DELETE FROM " . _db_prefix . "_historic
        WHERE id_current_course = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i', $id_current_course);
            $tmp->execute();
            $tmp->fetch();

            return  true;

        }
        return false;
    }


    public function insertHistoric($id_current_course, $id_historic_course)
    {

        $sql = "INSERT INTO " . _db_prefix . "_historic ( id_current_course, id_historic_course) VALUES( ?, ?)";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ii', $id_current_course, $id_historic_course);
            $tmp->execute();
            $tmp->fetch();

            return  $tmp->insert_id;

        }
        return false;
    }


        public function insertCourse($id_user_creator, $name, $desc, $exercise_weight, $practice_weight, $partial_weight)
    {
        $sql = "INSERT INTO " . _db_prefix . "_course(id_user_creator, name, description, exercise_tests_weight, practice_tests_weight,partial_tests_weight )
        VALUES (?, ?, ?, ?, ?, ?)";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('issiii', $id_user_creator, $name, $desc, $exercise_weight, $practice_weight, $partial_weight);
            $tmp->execute();
            $tmp->fetch();

            return  $tmp->insert_id;

        }
        return false;
    }

    public function updateCourse($id_course, $name, $desc, $exercise_weight, $practice_weight, $partial_weight)
    {

        $sql = "UPDATE " . _db_prefix . "_course
        SET  name = ?, description = ?, exercise_tests_weight = ?, practice_tests_weight = ?, partial_tests_weight = ?
        WHERE id_course = ?";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ssiiii', $name, $desc, $exercise_weight, $practice_weight, $partial_weight, $id_course);
            $tmp->execute();
            $tmp->fetch();

            return  true;

        }
        return false;
    }

    public function isCourseCreator($id_test)
    {

        $token = $_SESSION["token"];


        $sql = "SELECT COUNT(t.id_test)
                FROM " . _db_prefix . "_test t,
                " . _db_prefix . "_course c,
                " . _db_prefix . "_topic tp,
                " . _db_prefix . "_user u
                WHERE
                t.id_topic = tp.id_topic AND
                tp.id_course = c.id_course AND
                u.token = ? AND
                t.id_test = ? AND
                c.id_user_creator = u.id_user
                ;";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ss',$token,$id_test);
            $tmp->execute();
            $tmp->bind_result($result1);
            $tmp->fetch();
            if ($result1>0) {
                $tmp->close();
                return true;
            }
            $tmp->close();
            return false;
        }
        return false;
    }


    public function isCurrentCourse($id_course)
    {



        $sql = "SELECT COUNT(id_current_course)
                FROM " . _db_prefix . "_historic
                WHERE id_current_course = ?;";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('i',$id_course);
            $tmp->execute();
            $tmp->bind_result($result1);
            $tmp->fetch();
            if ($result1>0) {
                $tmp->close();
                return true;
            }
            $tmp->close();
            return false;
        }
        return false;
    }

    public function isRegisteredIntoCourse($id_test)
        {

        $token = $_SESSION["token"];


        $sql = "SELECT COUNT(t.id_test)
                FROM " . _db_prefix . "_test t,
                " . _db_prefix . "_course c,
                " . _db_prefix . "_course_inscription ci,
                " . _db_prefix . "_topic tp,
                " . _db_prefix . "_user u
                WHERE
                t.id_topic = tp.id_topic AND
                tp.id_course = c.id_course AND
                c.id_course = ci.id_course AND
                u.id_user = ci.id_user AND
                u.token = ? AND
                t.id_test = ?
                ;";

        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ss',$token,$id_test);
            $tmp->execute();
            $tmp->bind_result($result1);
            $tmp->fetch();
            if ($result1>0) {
                $tmp->close();
                return true;
            }
            $tmp->close();
            return false;
        }
         return false;
    }

    function addCourseInscription($id_user, $id_course){

        $sql = "INSERT INTO " . _db_prefix . "_course_inscription(id_user, id_course)
        VALUES (?, ?)";


        if (isset($this->db)) {
            $tmp = $this->db->prepare($sql);
            $tmp->bind_param('ii', $id_user, $id_course);
            $tmp->execute();
            $tmp->fetch();

            return  $tmp->insert_id;

        }
        return false;
    }


    public function destroy(){
        // close the mysqli connection
        $this->db->close();

    }
}