<?php 
    class UserProcessList extends Core {

        function list() {

            $id = $_GET['id'];
            $sql = "SELECT A1.*, A2.status as candidateStatus, A5.voucher, A2.date_added AS processListDateAdded,
                (
                    SELECT count(*) FROM process_list_lead_interviews AS A3 
                    LEFT JOIN process_list_interviews AS A4 ON A4.id = A3.interview
                    WHERE A3.lead = A2.lead AND A4.process_list = A1.id AND accept IS NULL
                ) AS pendingInterview
                FROM process_list as A1 
                LEFT JOIN process_list_lead as A2 ON A1.id = A2.process_list
                LEFT JOIN companies AS A5 ON A1.company = A5.id
                WHERE lead = ? AND A1.status != 'new' ORDER BY A1.created_at DESC";

            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function getSelectionTest() {

            $id = $_GET['processList'];
            $lead = $_GET['userId'];
            $sql = "SELECT A1.*, A2.status FROM selection_tests as A1 
                LEFT JOIN process_list_test as A2 ON A2.selection_test = A1.id 
                WHERE process_list = ? AND lead  = ?";            
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id, $lead ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function getSelectionQuestion() {

            $id = $_GET['id'];
            $answered = $_GET['answered'] == 'true';

            //if(!$answered)
                $sql = "SELECT * FROM selection_test_questions WHERE selection_test = ?";
            /*else $sql = "SELECT A1.* FROM selection_test_questions AS A1
                selection_test_question_responses AS A2 ON A2.selection_test_question = A1.id
                WHERE selection_test = ? AND A2.id IS NOT NULL";*/

            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);
            $results = [];

            while(($row = $stmt->fetch(PDO::FETCH_ASSOC))) {

                if($row['question_type'] !== 'dissertative') {

                    $sqlQuestionOptions = "SELECT * FROM selection_test_question_options WHERE selection_test_question = ${row['id']}";
                    $stmtQuestionOptions = parent::$conn->query($sqlQuestionOptions);
                    $row['options'] = $stmtQuestionOptions->fetchAll(PDO::FETCH_ASSOC);

                }                

                $results[] = $row;

            }

            return [ 'type' => 'OK', 'data' => $results ];            

        }

        function getSelectionQuestionResponse() {

            $lead = $_GET['userId'];
            $test = $_GET['test'];
            $process_list = $_GET['processList'];
            $sql = "SELECT A1.*, A4.description FROM process_list_test_response AS A1 
                LEFT JOIN selection_test_questions AS A2 ON A2.id = A1.selection_test_question 
                LEFT JOIN selection_tests AS A3 ON A3.id = A2.selection_test
                LEFT JOIN selection_test_question_options AS A4 ON A1.selection_test_question_option = A4.id
                WHERE lead = ? AND A3.id = ? AND A1.process_list = ?
            ";
            
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $test, $process_list ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function saveSelectionQuestionResponse() {

            $questions = $_POST['questions'];
            $process_list = $_POST['processList'];
            $test = $_POST['test'];
            $lead = $_POST['userId'];

            $questions = json_decode($questions, true);
            $sql = "INSERT INTO process_list_test_response(lead, process_list, selection_test_question, response, selection_test_question_option, approved, evaluated)
                VALUES(:lead, :process_list, :question, :response, :option, :approved, :evaluated) ON DUPLICATE KEY UPDATE selection_test_question_option = :option";
            
            foreach($questions as $question) {

                if($question['type'] === 'multiple') {

                    foreach($question['response'] as $response) {
                        $stmt = parent::$conn->prepare($sql);
                        $stmt->execute([
                            ':lead' => $lead,
                            ':process_list' => $process_list,
                            ':question' => $question['id'],
                            ':response' => null,
                            ':option' => $response,
                            ':approved' => $question['isRight'],
                            ':evaluated' => false
                        ]);        
                    }

                } else {

                    $stmt = parent::$conn->prepare($sql);                
                    $stmt->execute([
                        ':lead' => $lead,
                        ':process_list' => $process_list,
                        ':question' => $question['id'],
                        ':response' => $question['type'] === 'dissertative' ? $question['response'] : null,
                        ':option' => $question['type'] !== 'dissertative' ? $question['response'] : null,
                        ':approved' => $question['isRight'],
                        ':evaluated' => $question['type'] == 'option' ? true: false
                    ]);

                }

            }
            
            $sql = "SELECT A1.* FROM process_list_test_response AS A1 
                LEFT JOIN selection_test_questions AS A2 ON A2.id = A1.selection_test_question 
                LEFT JOIN selection_tests AS A3 ON A3.id = A2.selection_test
                WHERE lead = ? AND A3.id = ? AND A1.process_list = ? AND question_type NOT IN ('dissertative', 'multiple')
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $test, $process_list ]);
            $test_status = $stmt->rowCount() ? 'evaluated' : 'answered';

            $sql = "UPDATE process_list_test SET status = '$test_status' WHERE selection_test = ? AND lead = ? AND process_list = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $test, $lead, $process_list ]);

            return ['type' => 'OK'];

        }

        function updateSelectionQuestionResponse() {

            $id = $_POST['id'];
            $approved = $_POST['approved'];

            $sql = "UPDATE process_list_test_response SET approved = ?, evaluated = 1 WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ 
                $approved == 'true' ? true : false,
                $id
            ]);
            
            return [ 'type' => 'OK' ];

        }

        function concludeTest() {

            $test = $_POST['test'];
            $lead = $_POST['userId'];
            $process_list = $_POST['processList'];
            /*$approved = $_POST['approved'];
            $approved = $approved == 'true' ? 'approved' : 'rejected';*/
            
            $sql = "UPDATE process_list_test SET status = 'evaluated' WHERE selection_test = ? AND lead = ? AND process_list = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $test, $lead, $process_list ]);

            return ['type' => 'OK'];

        }

        function getQuestionPunctuation() {

            $test = $_GET['test'];
            $lead = $_GET['userId'];
            $process_list = $_GET['processList'];

            $sql = "SELECT count(*) as total FROM process_list_test_response AS A1 
                LEFT JOIN selection_test_questions AS A2 ON A2.id = A1.selection_test_question
                LEFT JOIN selection_tests AS A3 ON A3.id = A2.selection_test
                /*LEFT JOIN selection_test_question_options AS A4 ON A4.id = A1.selection_test_question_option*/
                WHERE  lead = ? AND A3.id = ? AND A1.process_list = ? /*AND question_type = 'option' AND approved = 1*/
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $test, $process_list ]);
            $all_options = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $test = $_GET['test'];
            $lead = $_GET['userId'];
            $sql = "SELECT count(*) as total FROM process_list_test_response AS A1 
                LEFT JOIN selection_test_questions AS A2 ON A2.id = A1.selection_test_question 
                LEFT JOIN selection_tests AS A3 ON A3.id = A2.selection_test
                LEFT JOIN selection_test_question_options AS A4 ON A4.id = A1.selection_test_question_option
                WHERE lead = ? AND A3.id = ? AND A1.process_list = ?/* AND question_type = 'option'*/ AND approved = 1
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $test, $process_list ]);
            $right_options = $stmt->fetch(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => [ 'right_options' => $right_options, 'all_options' => $all_options ] ];

        }

        function saveUserInterview() {

            $lead = $_POST['userId'];
            $interview = $_POST['interview'];
            $accept = $_POST['accept'] == 'true';

            $sql = "DELETE FROM process_list_lead_interviews WHERE lead = ? AND interview = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $interview ]);

            $sql = "INSERT INTO process_list_lead_interviews(lead, interview, accept) VALUES(?, ?, ?)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $interview, $accept ]);
            
            return [ 'type' => 'OK' ];

        }

        function saveInterviewPoints() {

            $lead = $_POST['userId'];
            $interview = $_POST['interview'];
            $points = $_POST['points'];

            $sql = "UPDATE process_list_lead_interviews SET points = ?, status = 'evaluated' WHERE lead = ? AND interview = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $points, $lead, $interview ]);

            return [ 'type' => 'OK' ];

        }

        function getUserInterview() {

            $lead = $_GET['userId'];            
            $process_list = $_GET['processList'];
            
            $sql = "SELECT A1.*, A3.date, A3.time_start, accept, points              
                FROM process_list_interviews AS A1   
                LEFT JOIN process_list_interview_sessions AS A3 ON A3.process_list_interview = A1.id
                LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A1.id AND A4.lead = A3.lead
                WHERE A1.process_list = ? AND A3.lead = ? ORDER BY date_start DESC";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $process_list, $lead ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function acceptProcessList() {

            $lead = $_POST['userId'];
            $process_list = $_POST['processListId'];
            $sql = "UPDATE process_list_lead SET status = 'pending' WHERE lead = ? AND process_list = ? AND status = 'waiting'";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $process_list ]);
            
            return [ 'type' => 'OK' ];

        }

        function saveObservation() {

            $id = $_POST['candidateId'];
            $observation = $_POST['observation'];
            $user = $_POST['userSystemId'];
            $sql = "INSERT INTO lead_observation(lead_id, observation, user_id)
                VALUES(?, ?, ?)";

            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id, $observation, $user ]);

            return [ 'type' => 'OK' ];

        }

        function updateObservation() {

            $id = $_POST['id'];
            $observation = $_POST['observation'];

            $sql =  "UPDATE lead_observation SET observation = ? WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $observation, $id ]);

            return [ 'type' => 'OK' ];
            
        }

        function getObservation() {

            $id = $_GET['candidateId'];

            $sql = "SELECT A1.*, A2.firstname FROM lead_observation AS A1 
                LEFT JOIN leads AS A2 ON user_id = A2.id
                WHERE lead_id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$id]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

            return $result;

        }       

        function deleteObservation() {

            $id = $_GET['id'];

            $sql = "DELETE FROM lead_observation WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);

            return [ 'type' => 'OK' ];

        }

    }