<?php 
    include_once __dir__ .  '/../core/LogEvent.php';
    
    class UserProcessList extends Core {

        function list() {

            $id = $_GET['id'];
            $sql = "SELECT A1.*, A2.status as candidateStatus, A5.voucher, A2.viewprocess, 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,
                (
                    SELECT count(*) FROM process_list_test AS A6
                    WHERE A6.lead = A2.lead AND A6.process_list = A1.id AND view IS NULL 
                ) AS pendingTest
                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 = ?
            // ";
            
            $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 FIND_IN_SET(A4.id, A1.response) > 0 OR 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' => $question['response'],
                            ':approved' => $question['isRight'],
                            ':evaluated' => true
                        ]);        
                    //}

                } else if($question['type'] === 'dissertative') {

                    $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
                    ]);

                } else {

                    $stmt = parent::$conn->prepare($sql);                
                    $stmt->execute([
                        ':lead' => $lead,
                        ':process_list' => $process_list,
                        ':question' => $question['id'],
                        ':response' => is_array($question['response']) ? implode(',', $question['response']) : $question['response'],
                        ':option' => is_array($question['response']) ? implode(',', $question['response']) : $question['response'],
                        ':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 IN ('dissertative')
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $test, $process_list ]);
            $test_status = $stmt->rowCount() ? 'answered' : 'evaluated';
            // $test_status = 'answered';

            $sql = "UPDATE process_list_test SET status = '$test_status', view = '0' 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';
            $view = '0';

            $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, view) VALUES(?, ?, ?, ?)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead, $interview, $accept, $view ]);
            
            return [ 'type' => 'OK' ];

        }

        function saveInterviewPoints() {

            $user_id = $_POST['userId'];
            $lead_id = $_POST['candidateId'];
            $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_id, $interview ]);

            $sql = "SELECT id, firstname, lastname FROM leads WHERE id = ?";
            $stmt_sql = parent::$conn->prepare($sql);
            $stmt_sql->execute([ $lead_id ]);
            $user = $stmt_sql->fetch(PDO::FETCH_ASSOC);
            $name = $user ? strtoupper($user['firstname'] . ' ' . $user['lastname']) : '';
                       
            $title = "O candidato $name recebeu $points pontos na entrevista";
            $url = "/recruiter/process";
            
            LogEvent::log($user_id, $lead_id, $url, $title, LogEvent::SCORE_INTERVIEW);

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

        }

        function getUserInterview() {

            $lead = $_GET['userId'];            
            $process_list = $_GET['processList'];
            
            $sql = "SELECT A1.*, A3.date, A3.time_start, A3.link_meet, 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' ];

        }
        
        function saveInterviewView() { 
            $lead = $_POST['userId'];
            $interviews = json_decode($_POST['interviews'], true); // Receber um array de IDs
            $view = $_POST['view'];
        
            // Construir placeholders dinâmicos para os IDs
            $placeholders = rtrim(str_repeat('?,', count($interviews)), ',');
        
            // Criar a query dinâmica
            $sql = "UPDATE process_list_lead_interviews SET view = ? WHERE lead = ? AND interview IN ($placeholders)";
            $stmt = parent::$conn->prepare($sql);
        
            // Preparar os parâmetros
            $params = array_merge([$view, $lead], $interviews);
        
            // Executar a query
            $stmt->execute($params);
        
            return ['type' => 'OK'];
        }   
        
        function saveTestView(){
            $lead = $_POST['userId'];
            $view = $_POST['view'];
            $processId = $_POST['processId'];
        
            // Decodificar JSON para garantir que `$interviews` seja um array
            $tests = isset($_POST['testIds']) ? json_decode($_POST['testIds'], true) : [];
         
            // Validar se `$interviews` é um array e não está vazio
            if (!is_array($tests) || empty($tests)) {
                return ['type' => 'ERROR', 'message' => 'Invalid or empty interviews array'];
            }
        
            // Construir placeholders dinâmicos para os IDs
            $placeholders = rtrim(str_repeat('?,', count($tests)), ',');
        
            // Criar a query dinâmica
            $sql = "UPDATE process_list_test SET view = ? WHERE process_list = ? AND lead = ? AND selection_test IN ($placeholders)";
            $stmt = parent::$conn->prepare($sql);
        
            // Preparar os parâmetros
            $params = array_merge([$view, $processId, $lead], $tests);
        
            // Executar a query
            try {
                $stmt->execute($params);
                return ['type' => 'OK'];
            } catch (PDOException $e) {
                // Logar e retornar o erro
                error_log($e->getMessage());
                return ['type' => 'ERROR', 'message' => 'Failed to update interviews'];
            }
        }

    }