<?php

    class ProcessList extends Core {

        function store() {

            $name = $_POST['name'] ?? null;
            $company = $_POST['company'];
            $sql = "INSERT INTO process_list(name, company) VALUES(?, ?)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $name, $company ]);
            $insert_id = parent::$conn->lastInsertId();
            return ['type' => 'OK', 'data' => $insert_id ];

        }

        function list() {

            $company = $_GET['company'];
            
            $sql = "SELECT P.*,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id) AS candidates,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status = 'pending') AS candidatesPending
                FROM process_list AS P WHERE P.company = ? AND P.status <> 'archived' ORDER BY P.created_at DESC 
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return ['type' => 'OK', 'data' => $result ];

        }

        function delete() {

            $id = $_GET['id'];
            $sql = "DELETE FROM process_list WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);
            return ['type' => 'OK'];

        }

        function archive() {

            $processId = $_POST['id'] ?? null;
            $archive = $_POST['archive'] ?? false;            

            $sql = "UPDATE process_list SET status = ? WHERE id = ?";
            $status = $archive ? 'archived' : 'read';
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                $status,
                $processId
            ]);

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

        }

        function listArchive() {

            $company = $_GET['company'];
            
            $sql = "SELECT P.*,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id) AS candidates,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status = 'pending') AS candidatesPending
                FROM process_list AS P WHERE P.company = ? AND P.status = 'archived' ORDER BY P.created_at DESC
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return ['type' => 'OK', 'data' => $result ];

        }

        function saveSelectionTest() {

            $tests = $_POST['tests'];
            $candidates = $_POST['candidates'];
            $process_list = $_POST['processList'];

            $tests = json_decode($tests, true);
            $candidates = json_decode($candidates, true);

            $candidates_id = join(', ', $candidates);
            
            $sql = "DELETE FROM process_list_test WHERE process_list = $process_list AND lead IN ($candidates_id) AND status = 'pending'";
            parent::$conn->query($sql);            

            $sql = "INSERT IGNORE INTO process_list_test(process_list, selection_test, lead) VALUES(?, ?, ?)";
            
            $campaign = new QueueCampaign($this->getConnection());

            $candidates_emails = array();
            foreach($tests as $test) {

                foreach($candidates as $candidate) {

                    $stmt = parent::$conn->prepare($sql);
                    $stmt->execute([ $process_list, $test, $candidate ]);

                    $campaign->queue($candidate, 'test');

                }

            }

            $this->sendVoucher($candidates);
            
            return ['type' => 'OK' ];

        }

        function listProcessListTest() {

            $process_list = $_GET['processList'];

            $sql = "SELECT A2.*, A1.lead FROM process_list_test AS A1 
                LEFT JOIN selection_tests as A2 ON A2.id = A1.selection_test
                WHERE process_list = $process_list";

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

        }

        function deleteProcessListTest() {

            $process_list = $_GET['processList'];
            $test = $_GET['test'];
            $sql = "DELETE FROM process_list_test WHERE process_list = ? AND selection_test = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $process_list, $test ]);
            
            return ['type' => 'OK'];

        }

        function saveLeads() {

            $leads = $_POST['leads'];
            $process_list = $_POST['processList'];
            $leads = json_decode($leads, true);

            $sql = "INSERT IGNORE INTO process_list_lead(lead, process_list) VALUES(?, ?)";

            foreach($leads as $lead) {
                $stmt = parent::$conn->prepare($sql);
                $stmt->execute([ $lead, $process_list ]);
            }

            $sql = "UPDATE process_list SET status = 'read' WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_list]);

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

        }

        function saveLeadByEmail() {

            $email = $_POST['email'];
            $process_id = $_POST['processId'];

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

            if($stmt->rowCount() == 0)
                throw new \Exception('Nenhum candidato encontrado com esse e-mail', 404);

            $lead = $stmt->fetch(PDO::FETCH_ASSOC);

            $sql = "INSERT IGNORE INTO process_list_lead(lead, process_list) VALUES(?, ?)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $lead['id'], $process_id ]);

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

        }

        function getTotalLeads() {

            $process_list = $_GET['processList'];
            $filter = $_GET['filter'] ?? null;

            $sql = "SELECT count(A1.id) AS records FROM process_list_lead AS A1 
                LEFT JOIN leads AS A2 ON A1.lead = A2.id WHERE A1.process_list = :id AND A1.status <> 'disabled'";

            if($filter) {

                $sql .= " AND (
                    A2.firstname LIKE :filter OR
                    A2.lastname LIKE :filter OR
                    A2.cpf LIKE :filter OR
                    A2.city LIKE :filter OR
                    A2.address1 LIKE :filter OR
                    A2.mobile LIKE :filter OR
                    A2.phone LIKE :filter OR
                    A2.email LIKE :filter /*OR
                    (SELECT description FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id LIMIT 1) LIKE :filter*/
                    OR A2.id IN (SELECT A7.lead_id FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id AND description LIKE :filter)
                )";

            }

            $stmt = parent::$conn->prepare($sql);
            $stmt->bindParam(':id', $process_list, PDO::PARAM_INT);

            if($filter) {
                $filter = "%$filter%";
                $stmt->bindParam(':filter', $filter, PDO::PARAM_STR);
            }                

            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function getLeads() {

            $pageIndex = $_GET['pageIndex'] ?? 0;
            $limit = $_GET['limit'] ?? 20;
            $all = $_GET['all'] ?? false;
            $filter = $_GET['filter'] ?? null;
            $join_tables = '';

            $offset =  $pageIndex * $limit;
            $process_list = $_GET['processList'];
            $sql = "SELECT A2.*/*, A5.status*/, A1.status AS candidateStatus,
                    (SELECT sum(points) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead) AS points,
                    (SELECT count(status) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND status IN('evaluated')) AS status,
                    (SELECT count(*) FROM process_list_test_response AS A5 WHERE lead = A1.lead AND A5.process_list = :process_list) as responses_total,
                    (SELECT count(*) FROM process_list_test_response AS A6 WHERE lead = A1.lead AND A6.process_list = :process_list AND A6.approved = 1) as responses_approved_total
                FROM process_list_lead as A1 
                LEFT JOIN leads as A2 ON A1.lead = A2.id                
                WHERE A1.process_list = :process_list AND A1.status <> 'disabled'
            ";

            if($filter) {

                $join_tables = "LEFT JOIN professional_experiencies AS A3 ";

                $sql .= " AND (
                    A2.firstname LIKE :filter OR
                    A2.lastname LIKE :filter OR
                    A2.cpf LIKE :filter OR
                    A2.city LIKE :filter OR
                    A2.address1 LIKE :filter OR
                    A2.mobile LIKE :filter OR
                    A2.phone LIKE :filter OR
                    A2.email LIKE :filter /*OR
                    (SELECT description FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id LIMIT 1) LIKE :filter*/
                    OR A2.id IN (SELECT A7.lead_id FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id AND description LIKE :filter)
                )";

            }

            $sql .= " ORDER BY firstname";

            if(!$all)
                $sql .= " LIMIT :offset, :limit";
            
            $stmt = parent::$conn->prepare($sql);
            $stmt->bindParam(':process_list', $process_list);

            if($filter) {
                $filter = "%$filter%";
                $stmt->bindParam(':filter', $filter, PDO::PARAM_STR);
            }

            if(!$all) {
                $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
                $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);    
            }
            
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function deleteLead() {

            $process_list = $_GET['processList'];
            $lead = $_GET['lead'];
            
            $sql = "DELETE FROM process_list_lead WHERE process_list = ? AND lead = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $process_list, $lead ]);

            $sql = "DELETE process_list_interview_sessions, process_list_lead_interviews FROM process_list_interview_sessions 
                LEFT JOIN process_list_interviews ON process_list_interviews.id = process_list_interview_sessions.process_list_interview
                LEFT JOIN process_list_lead_interviews ON process_list_lead_interviews.interview = process_list_interviews.id              
                WHERE process_list = ? AND process_list_interview_sessions.lead = ?
            ";
            
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $process_list, $lead ]);

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

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

        }

        function saveInterview() {

            $process_list = $_POST['processList'];
            $name = $_POST['name'];
            $contact = $_POST['contact'];
            $address = $_POST['address'];
            $time_start = $_POST['timeStart'];
            $time_end = $_POST['timeEnd'];
            $date_start = $_POST['dateStart'];
            $next_days = $_POST['nextDays'] == 'true';
            $candidates_session_duration = $_POST['candidatesSessionDuration'];

            $sql = "INSERT INTO process_list_interviews(process_list, name, contact, address, date_start, time_start, time_end, next_days, candidates_session_duration) 
                VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_list, $name, $contact, $address, $date_start, $time_start, $time_end, $next_days, $candidates_session_duration]);
            
            return ['type' => 'OK'];

        }

        function updateInterview() {

            $id = $_POST['id'];
            $name = $_POST['name'];
            $contact = $_POST['contact'];
            $address = $_POST['address'];
            $time_start = $_POST['timeStart'];
            $time_end = $_POST['timeEnd'];
            $date_start = $_POST['dateStart'];
            $next_days = $_POST['nextDays'] == 'true';
            $candidates_session_duration = $_POST['candidatesSessionDuration'];


            $sql = "UPDATE process_list_interviews SET name = :name, contact = :contact, address = :address, date_start = :date_start, 
                time_start = :time_start, time_end = :time_end, next_days = :next_days, candidates_session_duration = :candidates_session_duration
                WHERE id = :id";

            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':name' => $name,
                ':contact' => $contact,
                ':address' => $address,
                ':date_start' => $date_start,
                ':time_start' => $time_start,
                ':time_end' => $time_end,
                ':next_days' => $next_days,
                ':candidates_session_duration' => $candidates_session_duration,
                ':id' => $id
            ]);
            
            return ['type' => 'OK'];

        }

        function getInterview() {

            $process_list = $_GET['processList'];
            $sql = "SELECT * FROM process_list_interviews WHERE process_list = ? ORDER BY date_start DESC";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $process_list/*, $user_id*/ ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        function deleteInterview() {

            $id = $_GET['id'];

            $sql = "SELECT * FROM process_list_interviews AS A1 
                RIGHT JOIN process_list_lead_interviews AS A2 ON A1.id = A2.interview
                WHERE A1.id = ?
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);

            if($stmt->rowCount() > 0)
                return ['type' => 'FAIL'];

            $sql = "DELETE FROM process_list_interviews WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $id ]);
            return ['type' => 'OK'];

        }

        function finish() {

            $id = $_POST['id'];
            $sql = "UPDATE process_list SET status = 'read' WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$id]);
            
            return ['type' => 'OK'];

        }

        function conclude() {

            $id = $_POST['id'];
            $sql = "UPDATE process_list SET status = 'finished' WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$id]);
            
            return ['type' => 'OK'];

        }

        function hireCandidates() {

            $candidates = json_decode($_POST['candidates'], true);
            $process_list = $_POST['processList'];
            
            $sql = "UPDATE process_list_lead SET status = 'hired' WHERE lead IN (?) AND process_list = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                join(', ',$candidates),
                $process_list
            ]);

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

        }

        function saveInterviewSessions () {

            $sessions = $_POST['sessions'];
            $interview = $_POST['interview'];            
            
            $sql = "INSERT IGNORE INTO process_list_interview_sessions(process_list_interview, lead, date, time_start, time_end, blocked) 
                VALUES(:process_list_interview, :lead, :date, :time_start, :time_end, :blocked)
                ON DUPLICATE KEY UPDATE time_start = :time_start, time_end = :time_end, blocked = :blocked";
            $sessions = json_decode($sessions, true);
            $candidates_emails = array();

            $campaign = new QueueCampaign($this->getConnection());
            foreach($sessions as $session) {

                $stmt = parent::$conn->prepare($sql);
                $stmt->execute([
                    ':process_list_interview' => $interview,
                    ':lead' => $session['candidate']['id'],
                    ':date' => $session['date'],
                    ':time_start' => $session['timeAt'],
                    ':time_end' => $session['timeEnd'],
                    ':blocked' => $session['blocked'] == 'true'
                ]);

                if($session['candidate']) {
                    $sqlI = "INSERT INTO process_list_lead_interviews(lead, interview) VALUES(?, ?)";
                    $stmt = parent::$conn->prepare($sqlI);
                    if($stmt->execute([ $session['candidate']['id'], $interview ])){
                        $campaign->queue($session['candidate']['id'], 'interview');
                    }
                }
                
                // if(parent::$conn->lastInsertId())
                //     $campaign->queue($session['candidate']['id'], 'interview');    

            }


            $candidates_ids = array_map(function($session) {
                return $session['candidate']['id'];
            }, $sessions);

            $this->sendVoucher($candidates_ids);

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

        }

        function getInterviewSessions() {

            $interview = $_GET['interview'];
            $sql = "SELECT A1.*, A2.id AS lead_id, firstname, lastname, avatar_profile, accept FROM process_list_interview_sessions AS A1
                LEFT JOIN leads AS A2 ON A1.lead = A2.id
                LEFT JOIN process_list_lead_interviews AS A3 ON A3.interview = A1.process_list_interview AND A3.lead = A2.id
                WHERE process_list_interview = ?
            ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $interview ]);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

            /*var_dump(parent::$conn->errorInfo());
            var_dump($stmt->errorInfo());*/

            return $result;

        }

        function deleteInterviewSession() {

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

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

        }

        private function sendVoucher($candidates) {

            $candidates_ids = implode(', ', $candidates);
            $sql = "SELECT A1.voucher, A4.id FROM companies AS A1 
                LEFT JOIN process_list AS A2 ON A2.company = A1.id
                LEFT JOIN process_list_lead AS A3 ON A3.process_list = A2.id                
                LEFT JOIN leads AS A4 ON A4.id = A3.lead
            WHERE A4.id IN($candidates_ids) AND A1.voucher IS NOT NULL AND COALESCE(signature_status, '') != 'ACTIVE'";
            
            $stmt = parent::$conn->query($sql);

            if($stmt->rowCount()) {

                $campaign = new QueueCampaign($this->getConnection());
                $candidates = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                foreach($candidates as $candidate) {

                    $sql2 = "UPDATE leads SET voucher = ? WHERE id = ?";
                    $stmt2 = parent::$conn->prepare($sql2);
                    $stmt2->execute([ $candidate['voucher'], $candidate['id'] ]);
                    
                    $campaign->queue($candidate['id'], 'voucher');

                }

            }            

        }

        function getDisableCandidate() {

            $process_list = $_GET['processListId'];
            $offset = $_GET['pageIndex'] ?? 0; 
            $limit = $_GET['limit'] ?? 20;

            $sql = "SELECT A1.* FROM leads AS A1 LEFT JOIN process_list_lead AS A2 ON A2.lead = A1.id 
                WHERE A2.status = 'disabled' AND process_list = :process_list
                LIMIT :offset, :limit ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->bindParam(':process_list', $process_list);
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            file_put_contents('disable.txt', "$sql $process_list");
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

        }

        function getTotalDisableCandidate() {

            $process_list = $_GET['processListId'];

            $sql = "SELECT count(*) as total FROM leads AS A1 LEFT JOIN process_list_lead AS A2 ON A2.lead = A1.id 
                WHERE A2.status = 'disabled' AND process_list = ? ";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_list]);
            $total = $stmt->fetch(PDO::FETCH_ASSOC);
            
            return [ 'type' => 'OK', 'data' => $total ];

        }

        function disableCandidate() {

            $disable = $_POST['disable'] == 'true' ? 'disabled' : 'waiting';
            $lead = $_POST['lead'];
            $process_list = $_POST['processListId'];

            $sql = "UPDATE process_list_lead SET status = ? WHERE lead = ? AND process_list = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                $disable,
                $lead,
                $process_list
            ]);
            file_put_contents('disableCandidate.txt', "$sql $disable $lead $process_list");
            return [ 'type' => 'OK' ];

        }

    }