<?php
    include_once("CUserSession.php");
    include_once("helper.php");
    include_once("CUtil.php");
    include_once("CProjectInfo.php");
    include_once("CEnums.php");

    /*
         This function gets the set of solutions for the given projects.
         Please note that we might have different set of solutions for different versions of standard in future
         for example : version one will have 1.1.6 in its standard but it might happen that 1.1.6 might get
         deleted in next version and hence we need to generate set of solutions from version of the UD standards
         given project beloongs to
    */

    function get_solutions_for_project( $version = 0)
    {
        $sql_conn = mysqli_connection();
       
        $ret["success"] = true;

        $sql = "SELECT credit FROM `UD_S_Headings` WHERE 1 UNION select `Standard#` from `UD_S_Solutions` where 1 ORDER BY credit";
        
        try
        {
            $result = mysqli_query($sql_conn, $sql);
            $res = array();
            while($row = mysqli_fetch_array($result)) {
                $res[] = $row["credit"];
            }
        }
        catch (Exception $e){
            $ret["success"] = false;
            $res['message'] = "Error : ".$e->getMessage();
        }

        $ret["version"] = $version;
        $ret["solutions"] = $res;
        mysqli_close($sql_conn);
        //$r = json_encode($ret);
        //echo "hello : ". $r;
        return $ret;
    }

    function create_sql_insert_query( $project_id, $solutions )
    {
        $sql = "INSERT INTO `UD_solutions`(`project_id`, `solution`) VALUES ";

        foreach ($solutions as &$solution) {
            $sql = $sql . " ('" . $project_id ."','" .$solution."') ,"; 
            //echo "<br>";
            //echo "hello : ". $solution;
        }
        $sql = substr($sql, 0, -1);
        //echo "<br>";
        //echo $sql;
        return $sql;
    }

    function execute_insert_query( $sql )
    {
        // execute the query
        $sql_conn = mysqli_connection();
        $ret["message"] =  "Project Solutions are created successfully";
        $ret["success"] = true;

        if (!($stmt = $sql_conn->prepare( $sql ))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $ret["success"] = false;
        }

        if (!($ret_sql = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["success"] = false;
        }

        $stmt->close();
        mysqli_close($sql_conn);
        return $ret;
        //echo $ret["message"];
    }

    function create_project_solution_rows( $project_id , $version=0 )
    {
        // done : check if we already create project earlier
        // if yes , return true otherwise
        // create entries for this project.
        $sql_conn = mysqli_connection();
        $sql_select = "SELECT * FROM `UD_solutions` WHERE project_id like '".$project_id."'";
        //echo $sql_select;
        //echo "<br>";
        $result = mysqli_query($sql_conn, $sql_select);
        $cnt = mysqli_num_rows( $result );
        //echo "Row count is ".$cnt;       
        mysqli_close($sql_conn);

        if( (int)$cnt > 0 )
        {
            //echo "<br>";
            //echo "Given project already exists in the DB";
            $ret["project_id"] = $project_id;
            $ret["version"] = $version;
            $ret["success"] = true;
            $ret["messsage"] = "Project already exists in the DB";
            return true;
        }

        // To do in future : since we might have different standards/solutions for
        // different version, we need to get $version of the project to get the solutions list applicable
        // to this project. So, get the version for whihc this project is created        
        $solutions =  get_solutions_for_project( $version );
        $solutions = $solutions["solutions"];

        $sql = create_sql_insert_query( $project_id, $solutions );
        $ret = execute_insert_query( $sql );

        $ret["project_id"] = $project_id;
        $ret["version"] = $version;
        return $ret;        
    }  

    // set_solution_review_checkbox_value('pr_25cd74af9a', '2.2.1', "1");

    function set_solution_review_checkbox_value( $project_id, $solution , $value )
    {
        
        $sql_insert = "INSERT INTO `UD_solutions` (`project_id`, `solution`, `review_checkbox`) VALUES ('".$project_id."','".$solution."' ,".$value." )";
        
        $insert_true = write_db( $sql_insert );

        // foreach($update_true as $i){
        //  print($i);
        // }
        if($insert_true['success'] == 0){
           $sql_update = "UPDATE `UD_solutions` SET `review_checkbox`= ".$value." WHERE project_id LIKE '".$project_id."' and solution LIKE '".$solution."'";
        
        return write_db( $sql_update );
        }
    }

    function set_solution_review_checkboxes_value( $project_id, $solution , $value )
    {
        // try{
        $sql_update = "UPDATE `UD_solutions` SET `review_checkbox`= ".$value." WHERE project_id LIKE '".$project_id."' AND solution LIKE '".$solution."%'";
        return write_db( $sql_update );
        // }
        // catch(Exception $e){
        //     $sql_insert = "INSERT INTO `UD_solutions` (`project_id`, `solution`, `review_checkbox`) VALUES ('".$project_id."','".$solution."' ,".$value." )";
        //     return write_db( $sql_insert );
        // }
    }

    function get_solution_review_checkbox_value( $project_id, $solution )
    {
        $sql_select = "SELECT `solution`, `review_checkbox` FROM `UD_solutions` WHERE project_id like '".$project_id."' AND solution LIKE '".$solution."'";
        $ret = select_from_db( $sql_select );
        $res["review_checkbox_values"] = $ret;
        $res["success"] = true;
        return $res;
    }

    function get_solution_review_checkboxes_value( $project_id, $solution )
    {
        $sql_select = "SELECT `solution`, `review_checkbox` FROM `UD_solutions` WHERE project_id like '".$project_id."' AND solution LIKE '".$solution."%'";
        if ($solution == "")
        {
            $sql_select = "SELECT `solution`, `review_checkbox` FROM `UD_solutions` WHERE project_id like '".$project_id."'";
        }
        $ret = select_from_db( $sql_select );
        $res["review_checkbox_values"] = $ret;
        $res["success"] = true;
        return $res;
    }

    function select_from_db( $sql )
    {
        $sql_conn = mysqli_connection();
        $result = mysqli_query($sql_conn, $sql);

        $res = array();
        while($row = mysqli_fetch_array($result)) {
            $solution =  $row["solution"];
            $review_checkbox_value = $row["review_checkbox"];
            $res[$solution] = $review_checkbox_value;
        }        
        mysqli_close($sql_conn);
        return $res;
    }

    function write_db($update_query)
    {
        $sql_conn = mysqli_connection();   
        
        if (!($u_stmt = $sql_conn->prepare($update_query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["success"] = false;
            // echo "<br><br> Failure inside prepare : ".$sql_conn->error;
            return $resp;
        }
        
        if (!($ret = $u_stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $u_stmt->errno . ") " . $u_stmt->error;
            $resp["success"] = false;
            //echo "<br><br> Failure inside execute : ".$u_stmt->error;
            return $resp;
        } else {
            $resp["message"] = "Successfully updated Database" ;
            $resp["success"] = true;
            //echo "<br><br> SUCCESS";
        }
        $u_stmt->close();
        return $resp;
    }

    function get_color_from_code($code)
    {
        $color = "grey";
        switch($code)
        {
            case "0":
                $color = "yellow";
                break;
            
            case "-1":
                $color = "green";
                break;
            
            case "1":
                $color = "red";
                break;

            case "2":
                $color = "grey";
                break;
        }
        return $color;
    }

    function get_review_checkbox_status($project_id, $project_dict)
    {
        $review_checkbox_values = get_solution_review_checkboxes_value($project_id,"");
        $review_checkbox_values = $review_checkbox_values["review_checkbox_values"];

        $review_checkbox_dict['green'] = 0;
        $review_checkbox_dict['yellow'] = 0;
        $review_checkbox_dict['red'] = 0;
        $review_checkbox_dict['grey'] = 0;

        foreach($review_checkbox_values as $key=>$value) 
        {
            $split_key = explode(".",$key);
            $length_of_key = count($split_key);
            if($length_of_key === 3)
            {
                $h1 = $split_key[0];
                $h2 = $split_key[1];
                $h3 = $split_key[2];
                $checked = $project_dict[$h1][$h2][$h3]["checked"] ;
                $code = $checked==1 ? $value : "2";
                $color = get_color_from_code($code);
                $review_checkbox_dict[$h1][$h2][$h3]['status'] = $color;
                $review_checkbox_dict[$color]++;
            }

            if ($length_of_key === 4)
            {
                $h1 = $split_key[0];
                $h2 = $split_key[1];
                $h3 = $split_key[2];
                $h4 = $split_key[3];
                $checked = $project_dict[$h1][$h2][$h3][$h4]["checked"] ;
                $code = $checked==1 ? $value : "2";
                $color = get_color_from_code($code);
                $review_checkbox_dict[$h1][$h2][$h3][$h4]['status'] = $color;
                $review_checkbox_dict[$color]++;
            }
        }

        return $review_checkbox_dict;
    }

    function get_review_status($project_id)
    {
        $project_dict = createDictionaryForSpecificProject($project_id);
        $review_checkbox_dict = $project_dict["review_status"];
        $review_checkbox_dict = json_encode($review_checkbox_dict);
        // echo "<script>console.log($review_checkbox_dict)</script>";
    }

    //get_review_status("pr_692e545355");
    //get_review_checkbox_status("pr_692e545355");
    //create_project_solution_rows("abc");  
   
?>