<?php
    include_once("CUserSession.php");
    include_once("helper.php");
    include_once("CUtil.php");
    include_once("CProjectDocument.php");
    include_once("CStandard.php");
    
    // error_reporting(0);

    class UD_Project{

        public $sl_no;
        public $user_id;
        public $project_id;
        public $applicable_credits;
        public $earned_credits;
        public $bonus_credits;
        public $innovative_credits;
        public $innovative_credits_submitted_count;
        public $award_category;
        public $award_percentage;
        public $header_mask;
        public $h1_credits;
        /*
            Each solution credit fields are dynamic
            due to large count : ~500
        */ 

        function populateFromDBRow( $row ){
            
            $this->sl_no = $row["sl_no"];
            $this->user_id = $row["user_id"];
            $this->project_id = $row["project_id"];
            $this->applicable_credits = $row["applicable_credits"];
            $this->earned_credits = $row["earned_credits"];
            $this->bonus_credits = $row["bonus_credits"];
            $this->innovative_credits = $row["innovative_credits"];
            $this->innovative_credits_submitted_count = $row["innovative_credits_submitted_count"];
            $this->award_category = $row["award_category"];
            $this->award_percentage = $row["award_percentage"];
            $this->header_mask = $row["header_mask"];
            $this->h1_credits = $row["h1_credits"];
        }
        
    }
	
	// Getting a Project	
	function getProject($project_id){
        
		$sql_conn = mysqli_connection();
        if(isLoggedIn()){
            
            $where = "WHERE `user_id` like '".$_SESSION['user']->_id."'";
            if($project_id != -1){
                $where = $where." AND `project_id` like '".$project_id."'";
            }

            $sql = "SELECT  * FROM  UD_P_Projects ".$where." ORDER BY `sl_no`";
            $udProjectInfo = array();
            $res = array();
            try{
                $result = mysqli_query($sql_conn, $sql);
                while($row = mysqli_fetch_array($result)) {
                    //$project = new UD_Project();
                    //$project->populateFromDBRow($row);

                    // hide fields
                    unset($row["user_id"]);
                    unset($row["sl_no"]);
                    foreach($row as $key => $val){
                        if(gettype($key) === "integer"){
                            unset($row[$key]);
                        }
                    }
                    $udProjectInfo[] = $row;
                }
                mysqli_close($sql_conn);
                $res = $udProjectInfo;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
        } else {
            $res['message'] = "Error : User not logged in";
        }
        
        $r = json_encode($res);
        return $r;
		
	}
    
    function createProject($sql_conn, $project_id, $user_id, $applicable_credits, $earned_credits, $bonus_credits, $innovative_credits, 
                           $innovative_credits_submitted_count, $award_percentage, $award_category){
        
        if($sql_conn == null){
            $sql_conn = mysqli_connection();
        }


        if(isLoggedIn()){
            $project = new UD_Project();
            $project->project_id = $project_id;
            $project->user_id = $user_id;
            $project->applicable_credits = $applicable_credits;
            $project->earned_credits = $earned_credits;
            $project->bonus_credits = $bonus_credits;
            $project->innovative_credits = $innovative_credits;
            $project->innovative_credits_submitted_count = $innovative_credits_submitted_count;
            $project->award_percentage = $award_percentage;
            $project->award_category = $award_category;

            $project = Util::escapeObject($project);
            $ret["response"] = false;
            $header_mask = '';
            $h1_credits_str = '';
            $h1_credits_earned_str = '';

            // create header mask
            $header_info = build_header_mask();
            $header_mask =  $header_info["header_mask"];
            $h1_credits_str = $header_info["h1_credits_str"];
            $h1_credits_earned_str = $header_info["h1_credits_earned_str"];
            $ret['header_mask'] = $header_mask;
            $ret['h1_credits_str'] = $header_mask;
            $ret['$h1_credits_earned_str'] = $h1_credits_earned_str;

            if (!($stmt = $sql_conn->prepare("INSERT INTO `UD_P_Projects`(project_id, user_id, applicable_credits, earned_credits, bonus_credits,
                                            innovative_credits, innovative_credits_submitted_count, award_percentage, award_category, header_mask, h1_credits_applicable, h1_credits_earned) 
                                            VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"))){
                $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
                print($ret["message"]);
            }

            if(!($stmt->bind_param("ssiiiiidssss",
                        $project->project_id,
                        $project->user_id,
                        $project->applicable_credits,
                        $project->earned_credits,
                        $project->bonus_credits,
                        $project->innovative_credits,
                        $project->innovative_credits_submitted_count,
                        $project->award_percentage,
                        $project->award_category,
                        $header_mask,
                        $h1_credits_str,
                        $h1_credits_earned_str
                        ))){
                $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
            }

            if (!($res = $stmt->execute())) {
                $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
                $ret["response"] = false;
            } else {
                $ret["response"] = true;
                $ret["message"] = $res;
            }
        } else {
            $ret["message"] = "Error : User not logged in";
        }
        
        $stmt->close();
        return $ret;
    }

    function deleteProject($project_id, $user_id){
        
        if($user_id == "heamchand-f2526f2c37")
        {
            return delete_project_admin($project_id);           
        }

        $sql_conn = mysqli_connection();
        $sql_conn->begin_transaction();

        if(isLoggedIn()){
            $ret["response"] = [false, false];

            // UD_P_Projects
            if (!($stmt = $sql_conn->prepare("DELETE FROM `UD_P_Projects` WHERE `project_id` = ? AND `user_id` = ?"))){
                $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
                print($ret["message"]);
            }

            if(!($stmt->bind_param("ss",
                        $project_id,
                        $user_id
                        ))){
                $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
            }

            if (!($res = $stmt->execute())) {
                $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
                $ret["response"][0] = false;
            } else {
                $ret["response"][0] = true;
                $ret["message"] = $res;
            }
            $stmt->close();

            // UD_P_Info
            if (!($stmt = $sql_conn->prepare("DELETE FROM `UD_P_Info` WHERE `_id` = ? AND `creating_user_id` = ?"))){
                $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
                print($ret["message"]);
            }

            if(!($stmt->bind_param("ss",
                        $project_id,
                        $user_id
                        ))){
                $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
            }

            if (!($res = $stmt->execute())) {
                $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
                $ret["response"][1] = false;
            } else {
                $ret["response"][1] = true;
                $ret["message"] = $res;
            }
            $stmt->close();

            if($ret["response"][0] && $ret["response"][1]){
                $sql_conn->commit();
                $ret["commit"] = true;
            } else {
                $sql_conn->rollback();
                $ret["commit"] = false;                
            }
        } else {
            $ret["message"] = "Error : User not logged in";
        }
        
        $ret["user"] = $user_id;
        $ret["project"] = $project_id;
        
        $sql_conn->close();
        return json_encode($ret);
    }

    function submitProjectForReview($project_id, $submit_type = "self_certification"){
        
        $sql_conn = mysqli_connection();

        // submitProjectCheck function and function which it calls is heavily dependent on the the session user id.
        // and hence if it is called from silent_post.php file which is called by the UBF checkout used for payment processing
        // we have to by pass this check and assume that if user is at the payment page then 
        // it already passing all the criteria for the submission.
        
        // $submit_check = submitProjectCheck($project_id, $submit_type);
        // if( $submit_check["success"] === false )
        
        $ret["success"] = false;

        // UD_P_Info
        if (!($stmt = $sql_conn->prepare("UPDATE `UD_P_Info` SET `isud_status` = 2 WHERE `_id` = ?"))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            print($ret["message"]);
        }

        if(!($stmt->bind_param("s",
                    $project_id
                    //$_SESSION['user']->_id
                    ))){
            $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
        }

        if (!($res = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["success"] = false;
        } else {
            $ret["success"] = true;
            $ret["message"] = $res;
        }
        
        $stmt->close();        
        $sql_conn->close();
        return json_encode($ret);
    }
	
    function updateProjectSolutionValues($id,$data, $target, $applicable_credits, $earned_credits, $bonus_credits, $innovative_credits, $innovative_credits_submitted_count, $award_percentage, $award_category, $h1_credits_applicable, $h1_credits_earned){
        if(!isLoggedIn()){
            $resp["message"] = "User not logged in";
            $resp["response"] = false;
            return json_encode($resp);
        }

        $sql_conn = mysqli_connection();
        $header_mask = '';

        /*SELECT `sl_no`, `header_mask` FROM (SELECT * from `UD_P_Projects` WHERE `project_id` like 'pr_4e814507fa') as a
JOIN `project_team` as b on a.project_id = b.project_id
WHERE b.`user_id` like 'sred-31506c47d3'*/

        /* check for valid project */
        $query = "SELECT `sl_no`, `header_mask` FROM (SELECT * from `UD_P_Projects` WHERE `project_id` like ?) as a JOIN `project_team` as b on a.project_id = b.project_id WHERE b.`user_id` like ?";

        if (!($stmt = $sql_conn->prepare($query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["response"] = false;
            return json_encode($resp);
        }
        
        if(!($stmt->bind_param("ss",
                $id,
                $_SESSION['user']->_id
        ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
            $resp["response"] = false;
            return json_encode($resp);
        }
        
        if (!($ret = $stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
            return json_encode($resp);
        } else {
            $stmt->store_result();
            if($stmt->num_rows <=0){
                $resp["message"] =  "Execute failed: Current user doens't have a project with the given id";
                $resp["response"] = false;
                return json_encode($resp);
            } else {
                $stmt->bind_result($row_sl_no, $row_header_mask);
                while($stmt->fetch()) {
                    $header_mask = $row_header_mask;
                }

                $header_mask_new = '';
                $header_mask_arr = explode(";",$header_mask);

                $update_clause = "";
                $fields = "";

                list($h1,$h2,$h3) = explode("-",$target);
                $prefix = "`s_".$h1."_".$h2;
                $val = $data["enabled"] === "true" ? 1:0;
                if (intval($h3,10) > 0)
                {
                     $prefix = "`s_".$h1."_".$h2."_".$h3;
                }
                else
                {
                    $h3 = "-1";
                    $prefix = "`s_".$h1."_".$h2;
                }
                
                
                foreach($data as $e2_key => $e2){
                    if(gettype($e2) === "array" && (gettype($e2["checked"]) !== "NULL")){
                        $update_clause .= $prefix."_".$e2_key."` = ".$e2["checked"].", ";                        
                    }

                    if(gettype($e2["1"]) === "array" && (gettype($e2["1"]["checked"]) !== "NULL")){
                        foreach($e2 as $e3_key => $e3){
                            if(gettype($e3) === "array" && (gettype($e3["checked"]) !== "NULL")){
                                $update_clause .= $prefix."_".$e2_key."_".$e3_key."` = ".$e3["checked"].", ";
                            }
                        }
                    }
                }
                
                $header_mask_test = "";
                $header_mask_test = build_header_mask_from_data_dict($data,$header_mask_arr,$h1,$h2,$h3);

                $update_clause .= " `applicable_credits` = {$applicable_credits}, `earned_credits` = {$earned_credits}, `bonus_credits` = {$bonus_credits}, `innovative_credits` = {$innovative_credits}, `innovative_credits_submitted_count` = {$innovative_credits_submitted_count}, `award_percentage` = {$award_percentage}, `award_category` = '{$award_category}', `header_mask` = '{$header_mask_test}', `h1_credits_applicable` = '{$h1_credits_applicable}', `h1_credits_earned` = '{$h1_credits_earned}'";
                $update_query = "UPDATE `UD_P_Projects` SET ".$update_clause." WHERE `project_id` like '".$id."'";
                if (!($u_stmt = $sql_conn->prepare($update_query))){
                    $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
                    $resp["response"] = false;
                    return json_encode($resp);
                }
                
                if (!($ret = $u_stmt->execute())) {
                    $resp["message"] =  "Execute failed: (" . $u_stmt->errno . ") " . $u_stmt->error;
                    $resp["response"] = false;
                    return json_encode($resp);
                } else {
                    $resp["message"] =  $ret." q: ";
                    $resp["response"] = true;
                }

                $u_stmt->close();
            }
        }

        $stmt->close();

        mysqli_close($sql_conn);
        return json_encode($resp);
    }

    // TO DO: Add admin check to update the Reviewer 
    // i.e. allow only admin user access to update the reviewer setting
    function updateProjectReviewer($id,$reviewer)
    {
        if(!isLoggedIn())
        {
            $res["message"] = "User not logged in";
            $res["response"] = false;
            return json_encode($resp);
        }

        $sql_conn = mysqli_connection();

        /* check for valid project */
        $query = "SELECT `project_title` FROM `UD_P_Info` WHERE _id = '".$id ."'";
        if (!($stmt = $sql_conn->prepare($query))){
            $res["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $res["response"] = false;
            return json_encode($res);
        }
                  
        if (!($stmt->execute())) {
            $res["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $res["response"] = false;
            return json_encode($res);
        }
        
        if (!($ret = $stmt->get_result())) {
            $res["message"] =  "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
            $res["response"] = false;
            return json_encode($res);
        }

        if ($ret->num_rows <= 0) 
        {
            $res["message"] =  "Got ZERO records for given project ID";
            $res["response"] = true;
            $res["num_rows"] = $ret->num_rows;
            return json_encode($res);
        }

        // update the project title for now.
        $sql = "UPDATE UD_P_Info SET reviewer='" .$reviewer. "' WHERE _id = '".$id ."'";
        if ($sql_conn->query($sql) === TRUE)
        {
            $res["message"] =  "Record updated successfully";;
            $res["response"] = true;
            return json_encode($res);
        }
        else 
        {
            $res["message"] =  "Failed to update the record" .$sql_conn->error ;
            $res["response"] = false;
            return json_encode($res);
        }

        $res = array();
        while($row = mysqli_fetch_array($ret)) 
        {
           $res[] = $row;
        }

        $stmt->close();
        mysqli_close($sql_conn);
        return json_encode($res);
    }

    // Adding level 3 toggle button write stream_supports 
    // to do: correct this code to handle colon seperator for level 3
    function build_header_mask_from_data_dict($data,$header_mask_arr, $h1, $h2, $h3)
    {
        // new value for the header $h1->$h2->$h3
        $new_value = $data["enabled"] === "true" ? 1:0;
        
        foreach($header_mask_arr as $key_h1=>$mask_1)
        {
            $h2_mask = explode(",",$mask_1);
            foreach($h2_mask as $key_h2=>$mask_2)
            {
                if(($key_h1+1) == intval($h1,10) && ($key_h2+1) == intval($h2,10))
                {
                    if (intval($h3,10) == -1)
                    {
                        $mask_2 = substr_replace($mask_2,$new_value,0,1);
                    }
                    else
                    {
                        $h3_mask = explode(":",$mask_2);
                        foreach($h3_mask as $key_h3=>$mask_3)
                        {
                            if ( ($key_h3) === intval($h3,10) )
                            {
                                $mask_3 = $new_value;
                            }
                            $mask_2_new .= $mask_3;
                            $mask_2_new .= ":"; 
                        }
                        $mask_2_new = substr($mask_2_new,0,-1);
                        $mask_2 = $mask_2_new;
                    }
                }
                // else
                {
                    $header_mask_new .= $mask_2;
                    $header_mask_new .= ",";
                }
            }
            $header_mask_new = substr($header_mask_new,0,-1); // remove trailing ,
            $header_mask_new .= ";";
        }
        $header_mask_new = substr($header_mask_new,0,-1); // remove trailing ;
        return $header_mask_new;
    }

    // get the credit threshold condition information
    function is_percentage_threshold_condition_reached($project_dict)
    {
        $PERCENTAGE_THRESHOLD = (int)$project_dict["total_enabled_sections"];
        $completed_percentage = (int)$project_dict["award_percentage"];
        if ( $completed_percentage < $PERCENTAGE_THRESHOLD )
        {
            return false;
        }
        return true;
    }

    function is_applicable_credits_threshold_reached($project_dict)
    {
        $applicable_credits_threshold = $project_dict["project_scope"] == 0? 55 : 60;
        $applicable_credits = (int)($project_dict["applicable_credits"]);
        if ( $applicable_credits < $applicable_credits_threshold )
        {
            return false;
        }
        return true;
    }

    function submitProjectCheck($project_id, $submit_type = "self_certification")
    {
        $required_doc_types = get_required_doc_types_details($project_id);
        $project_dict = createDictionaryForSpecificProject($project_id);
        $threshold_passed = is_percentage_threshold_condition_reached($project_dict);
        $sufficient_applicable_credits = is_applicable_credits_threshold_reached($project_dict);
        $habitable_floor_area = (int)$project_dict["habitable_floor_area"];

        $ret["success"] = true;
        $ret["project_dict"] = $project_dict;
        $ret["status"]["large_habitable_floor_area_check"] = true;
        $ret["status"]["credit_threshold_check"] = true;
        $ret["status"]["required_solutions_check"] = true;
        $ret["status"]["minimum_credit_check"] = true;
        $ret["status"]["required_doc_types_check"] = true;
        $ret["status"]["available_credit_threshold_check"] = true;

        if(!isLoggedIn())
        {
            $ret["message"] = "user_not_logged_in";
            $ret["success"] = false;
        }
        
        if( $habitable_floor_area >= 100000 )
        {
            $ret["message"] = "large_habitable_floor_area";
            $ret["status"]["large_habitable_floor_area_check"] = false;
            // $ret["project_dict"] = $project_dict;
            $ret["success"] = false;
        }
        
        // completed percentage should be more than 60%
        if( !$threshold_passed )
        {
            $ret["message"] = "threshold_not_reached";
            $ret["status"]["credit_threshold_check"] = false;
            // $ret["project_dict"] = $project_dict;
            $ret["success"] = false;
        }

        // applicable credits for users project should be more than 55 or 60 depending upon the project type
        if( !$sufficient_applicable_credits )
        {
            $ret["message"] = "available_credit_threshold_not_reached";
            $ret["status"]["available_credit_threshold_check"] = false;
            // $ret["project_dict"] = $project_dict;
            $ret["success"] = false;
        }
        
        if($project_dict["required_solutions"]["all_enabled_required_solutions_checked"] == false )
        {
             $ret["success"] = false;
             $ret["status"]["required_solutions_check"] = false;
             $ret["message"] = "all_rquired_solutions_not_checked";
             // $ret["project_dict"] = $project_dict;
        }
        
        if($project_dict["minimum_credit_check"]["overall_minimum_credit_check"] == false )
        {
             // $ret["success"] = false;
             $ret["status"]["minimum_credit_check"] = false;
             $ret["message"] = "minimum_credit_check_failure";
             // $ret["project_dict"] = $project_dict;
        }
        // to do : add user subscription check here
        if( $required_doc_types['success'] == false && $submit_type === "isUD_certification" )
        {
             $ret["success"] = false;
             $ret["message"] = "all_rquired_doc_types_not_submitted";
             $ret["status"]["required_doc_types_check"] = false;
             // $ret["project_dict"] = $project_dict;
             $ret["project_dict"]["required_doc_types"] = $required_doc_types;
        }
        
        return $ret; 
    }

    // build header mask for CREATE_PROJECT API call
    function build_header_mask ()
    {
        if(isset($sql_conn) == null)
        {
            $sql_conn = mysqli_connection();
        }

        try
        {
            if (!($stmt1 = $sql_conn->prepare("SELECT  `H1#` , `H2#` , COUNT( DISTINCT (`H3#`) ), `H2_Credits` as count FROM  `UD_S_Headings` GROUP BY  `H1#`,`H2#`,`H2_Credits` LIMIT 0 , 100")))
            {
                $ret["message"] =  "Mask Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
                $ret["response"] = false;
                throw new Exception($ret["message"]);
            }

            if (!($res = $stmt1->execute())) 
            {
                $ret["message"] =  "Execute failed: (" . $stmt1->errno . ") " . $stmt1->error;
                $ret["response"] = false;
                throw new Exception($ret["message"]);
            } 
            else 
            {
                $ret["message"] = "start";
                $stmt1->bind_result($h1, $h2, $h3_count, $h2_credits);
                $last_chapter = "1";
                $header_credits_sum = 0;
                while($stmt1->fetch()) 
                {
                    if ( intval($h2_credits,10) > 0 )
                    {
                        $header_credits_sum = $header_credits_sum + intval($h2_credits,10);
                        if ($last_chapter == $h1)
                        {
                            $header_mask .= ",";
                        }
                        else
                        {
                            $header_mask .= ";";
                            $last_chapter = $h1;                           
                        }

                        $header_mask .= "1";
                        $c = intval($h3_count,10)-1;
                        for($i=0; $i<$c; $i++)
                        {
                            $header_mask .= ":1";
                        }
                    }
                    else
                    {
                        $h1_credits_str .= $header_credits_sum;
                        $h1_credits_str .= ",";
                        $header_credits_sum = 0;

                        $h1_credits_earned_str .= "0,";
                    }
                }
                $header_mask = substr($header_mask,1); // remove starting ,
                $h1_credits_str .=  $header_credits_sum;
                $h1_credits_str = substr($h1_credits_str,2); 
                $h1_credits_earned_str = substr($h1_credits_earned_str,0,-1);
            }
        } 
        catch (Exception $e)
        {
            /*
                hard coded from ver.1
                1 - 2
                2 - 4
                3 - 12
                4 - 4 (4-1-3, 4-3-2)
                5 - 8 (5-7-2)
                6 - 16 (6-2-8,6-8-2,6-10-3)
                7 - 12
                8 - 6
                9 - 3
            */
            // $header_mask = '1,1;1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1;1,1,1,1;1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1;1,1,1,1,1,1;1,1,1';
            $header_mask = '1,1;1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1;1:1:1:1,1,1:1:1,1;1,1,1,1,1,1,1:1:1,1;1,1:1:1:1:1:1:1:1:1,1,1,1,1,1,1:1:1,1,1:1:1:1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1,1,1,1;1,1,1,1,1,1;1,1,1';
            $h1_credits_str = '4,7,15,7,15,30,12,6,4';
        }

        $stmt1 ->close();

        $ret['header_mask'] = $header_mask;
        $ret['h1_credits_str'] = $h1_credits_str;
        $ret['h1_credits_earned_str'] = $h1_credits_earned_str;
        return $ret;
    }

    function delete_project_admin($project_id)
    {
        $sql_conn = mysqli_connection();
        $sql_conn->begin_transaction();
        $ret["response"] = [false, false];

        // UD_P_Projects
        if (!($stmt = $sql_conn->prepare("DELETE FROM `UD_P_Projects` WHERE `project_id` = ?"))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            print($ret["message"]);
        }

        if(!($stmt->bind_param("s",
                    $project_id
                    ))){
            $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
        }

        if (!($res = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["response"][0] = false;
        } else {
            $ret["response"][0] = true;
            $ret["message"] = $res;
        }
        $stmt->close();

        // UD_P_Info
        if (!($stmt = $sql_conn->prepare("DELETE FROM `UD_P_Info` WHERE `_id` = ?"))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            print($ret["message"]);
        }

        if(!($stmt->bind_param("s",
                    $project_id
                    ))){
            $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
        }

        if (!($res = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["response"][1] = false;
        } else {
            $ret["response"][1] = true;
            $ret["message"] = $res;
        }
        $stmt->close();

        if($ret["response"][0] && $ret["response"][1]){
            $sql_conn->commit();
            $ret["commit"] = true;
        } else {
            $sql_conn->rollback();
            $ret["commit"] = false;                
        }

        $sql_conn->close();
        $ret["project"] = $project_id;        
        return json_encode($ret);
    }

    // this chnages the project from under_review(2) to under_review_for_discussion(3)
    function submitProjectForDiscussion($project_id){
        $ret["success"] = false;
        $project_dict = createDictionaryForSpecificProject($project_id);

        if($project_dict["isud_status"] != "2" )
        {
            $ret["message"] = "not_in_review_state";
            $ret["success"] = false;
            //$ret = json_encode($ret);
            //echo "<script>console.log($ret)</script>";
            return $ret;
        }

        if($project_dict["review_status"]["yellow"] != 0 )
        {
            $ret["message"] = "review_all_solutions";
            $ret["review_status"] = $project_dict["review_status"];
            $ret["success"] = false;
            //$ret = json_encode($ret);
            //echo "<script>console.log($ret)</script>";
            return $ret;
        }
              
        $sql_conn = mysqli_connection();
        // UD_P_Info
        if (!($stmt = $sql_conn->prepare("UPDATE `UD_P_Info` SET `isud_status` = 3 WHERE `_id` = ?"))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            print($ret["message"]);
        }

        if(!($stmt->bind_param("s",
                    $project_id
                    //$_SESSION['user']->_id
                    ))){
            $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
        }

        if (!($res = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["success"] = false;
        } else {
            $ret["success"] = true;
            $ret["message"] = $res;
        }
        
        $stmt->close();        
        $sql_conn->close();
        //$ret = json_encode($ret);
        //echo "<script>console.log($ret)</script>";
        return ($ret);
    }

    // this chnages the project from under_review_for_discussion(3) to complete(1)
    function submitProjectForCompletion($project_id){
        $ret["success"] = false;
        $project_dict = createDictionaryForSpecificProject($project_id);

        if($project_dict["isud_status"] != "3" )
        {
            $ret["message"] = "not_in_review_state";
            $ret["success"] = false;
            //$ret = json_encode($ret);
            //echo "<script>console.log($ret)</script>";
            return $ret;
        }

        if($project_dict["review_status"]["yellow"] != 0 || $project_dict["review_status"]["red"] != 0)
        {
            $ret["message"] = "review_all_solutions_and_make_green";
            $ret["review_status"] = $project_dict["review_status"];
            $ret["success"] = false;
            //$ret = json_encode($ret);
            //echo "<script>console.log($ret)</script>";
            return $ret;
        }
               
        $sql_conn = mysqli_connection();
        // UD_P_Info
        if (!($stmt = $sql_conn->prepare("UPDATE `UD_P_Info` SET `isud_status` = 1 WHERE `_id` = ?"))){
            $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            print($ret["message"]);
        }

        if(!($stmt->bind_param("s",
                    $project_id
                    //$_SESSION['user']->_id
                    ))){
            $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error; 
        }

        if (!($res = $stmt->execute())) {
            $ret["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $ret["success"] = false;
        } else {
            $ret["success"] = true;
            $ret["message"] = $res;
        }
        
        $stmt->close();        
        $sql_conn->close();
        //$ret = json_encode($ret);
        //echo "<script>console.log($ret)</script>";
        return ($ret);
    }

    //submitProjectForDiscussion("pr_e0fcee53e6");
    //submitProjectForCompletion("pr_e0fcee53e6");    

?>