<?php

    class Candidates extends Core {

        function list() {

            $limit = intval($_GET['limit']);
            $page = intval($_GET['page']);            
            $offset = $limit * $page;
            $searchs = $_GET['searchs'];
            $where = "WHERE account_type != 'company' AND firstname IS NOT NULL ";
            
            if($searchs) {
                $searchs = json_decode($searchs, true);
                if ($searchs !== null) {
                    if(sizeof($searchs) > 0){
                        $where .= "AND " . $this->_applyFilter($searchs);
                    }
                }
            }
            
            $sql = "SELECT * FROM leads $where ORDER BY firstname LIMIT :offset, :limit";
            
            $stmt = parent::$conn->prepare($sql);
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

            return $result; 
            // return [ 'result' => $result, 'sql' => $sql ];

        }

        function total() {

            $searchs = $_GET['searchs'];
            $where = "WHERE account_type != 'company' AND firstname IS NOT NULL ";
            
            if ($searchs) {
                $searchs = json_decode($searchs, true);
                if ($searchs !== null && sizeof($searchs) > 0) {
                    $additionalCondition = $this->_applyFilter($searchs);
                    if (!empty($additionalCondition)) {
                        $where .= "AND " . $additionalCondition . " ";
                    }
                }
            }

            $sql = "SELECT count(id) as records FROM leads $where";
            //echo $sql.'<br/>';
            
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            return $result;

        }

        function _applyFilter($searchs)  {
            
            $fields = array(
                'educationLevel' => "(leveleducation like '%{$searchs['educationLevel']}%' OR EXISTS (SELECT id FROM education_experience WHERE education_level like '%{$searchs['educationLevel']}%' AND lead_id = leads.id))",
                //'courseName' => " EXISTS (SELECT id FROM education_experience WHERE course_name like '%{$searchs['courseName']}%' AND lead_id = leads.id)",
                'gender' => " gender = '{$searchs['gender']}'",
                'city' => " city like '%{$searchs['city']}%'",
                'district' => " district like '%{$searchs['district']}%'",
                'stateInitials' => " (city like '%, {$searchs['stateInitials']}' OR state = '{$searchs['stateInitials']}' OR state like '%{$searchs['stateName']}%')",
                'education' => function() use ($searchs) {

                    $filters = array();

                    if($searchs['courseName'])
                        $filters[] = " course_name like '%{$searchs['courseName']}%'";
                    if($searchs['institutionName'])
                        $filters[] = " institution_name like '%{$searchs['institutionName']}%'";
                    
                    $filters_where = join(' AND ', $filters);

                    $sql = "id IN(SELECT lead_id FROM education_experience WHERE lead_id = leads.id AND ($filters_where))";
                    return $sql;
                    
                },
                'professional' => function($args) use ($searchs) {

                    
                    $filters = array();
                    if($searchs['vacancy']) 
                        $filters[] = " professional_name like '%{$searchs['vacancy']}%' ";
                    if($searchs['branch'])
                        $filters[] = " branch like '%{$searchs['branch']}%' ";
                    if($searchs['roleLevel'])
                        $filters[] = " role_level like '%{$searchs['roleLevel']}%' ";
                    if($searchs['specializationName'])
                        $filters[] = " specialization_name like '%{$searchs['specializationName']}%' ";
                    if($searchs['office'])
                        $filters[] = " office like '%{$searchs['office']}%' ";
                    if($searchs['ocupacao'])
                        $filters[] = " ocupacao like '%{$searchs['ocupacao']}%' ";
                    if($searchs['sinonimo'])
                        $filters[] = " sinonimo like '%{$searchs['sinonimo']}%' ";
                        
                    $filters_where = join(' AND ', $filters);
                    
                    $sql = "id IN(SELECT lead_id FROM professional_experiences WHERE lead_id = leads.id AND ($filters_where))";
                    return $sql;
                    //
                },
                'ageRange' => function($age_range) {
                
                    list($year_init, $year_end) = explode('_', $age_range);
                    $date = new \DateTime();
                    $date_init = $date->sub(new \DateInterval("P{$year_init}Y"));
                    
                    if($year_end) {
    
                        $date = new \DateTime();
                        $date_end = $date->sub(new \DateInterval("P{$year_end}Y"));                    
                        $date_init_format = $date_init->format('Y');
                        $date_end_format = $date_end->format('Y');                        
                        $sql = " YEAR(birthdate) BETWEEN $date_end_format AND $date_init_format ";
    
                    } else {
                        $date_init_format = $date_init->format('Y');
                        $sql = " YEAR(birthdate) < $date_init_format ";
                    }
    
                    return $sql;
                    
                },
                'jobDescription' => function($cred) use ($searchs) {
                    //$terms = explode(',', $cred);
                    $whereCond = $searchs['includeAllWords'] == 'true' ? 'AND' : 'OR';
                    $terms = $cred;
                    $terms_like = array_map(function($term) {
                        return "LIKE '% $term %'";
                    }, $terms);
                    $terms_like_organization = array_map(function($term) {
                        return "LIKE '%$term%'";
                    }, $terms);
                   
                    $desc_string_term = join(" $whereCond description " , $terms_like);
                    $organization_string_term = join(" $whereCond organization " , $terms_like_organization);
                    $profissional_string_term = join(" OR professional_name " , $terms_like_organization);
                    $office_string_term = join(" OR office " , $terms_like_organization);
                    $branch_string_term = join(" OR branch " , $terms_like_organization);
                    $role_level_string_term = join(" OR role_level " , $terms_like_organization);
                    $specialization_name_string_term = join(" OR specialization_name " , $terms_like_organization);
                    $ocupacao_string_term = join(" OR ocupacao " , $terms_like_organization);
                    $sinonimo_string_term = join(" OR sinonimo " , $terms_like_organization);
                   

                    $sql = " id IN (SELECT lead_id FROM professional_experiences WHERE (( description ";
                    $sql .= $desc_string_term;
                    $sql .= ")";
                    
                    $sql .= " OR ocupacao ";
                    $sql .= $ocupacao_string_term;
                
                    $sql .= " OR sinonimo ";
                    $sql .= $sinonimo_string_term;
                
                    
                    if($whereCond === 'OR') {

                        $sql .= " OR organization ";
                        $sql .= $organization_string_term;
                        $sql .= " OR professional_name ";
                        $sql .= $profissional_string_term;
                        $sql .= " OR office ";
                        $sql .= $office_string_term;
                        $sql .= " OR branch ";
                        $sql .= $branch_string_term;
                        $sql .= " OR role_level ";
                        $sql .= $role_level_string_term;
                        $sql .= " OR specialization_name ";
                        $sql .= $specialization_name_string_term;
                        
                    }

                    $sql .= ") AND lead_id = leads.id)";

                    return $sql;
                }
            );
            $conds = [];

            foreach($searchs as $field=>$cred) {
                if(!empty($cred)) {
                    
                    if($fields[$field]) {
                        
                        if(is_string($fields[$field]))
                            $conds[] = $fields[$field];
                        else $conds[] = $fields[$field]($cred);
                    }
                }
            }
            
            return implode(' AND ', $conds);

        }

        function get() {

            $id = $_GET['id'];
            $process_list = $_GET['processListId'] ?? null;
            $condition = $process_list 
                ? "(SELECT status FROM process_list_lead AS PLL WHERE PLL.lead = leads.id AND PLL.process_list = ? AND PLL.status = 'pending' LIMIT 1) AS candidateStatus"
                : "NULL AS candidateStatus";

            $sql = "SELECT *, 
                (select professional_name FROM professional_experiences as P WHERE P.lead_id = leads.id ORDER BY final_date DESC LIMIT 1) as desired_job_title2,
                $condition FROM leads WHERE id = ?";
                
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_list, $id]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            return $result;

        }

        function updateVoucher() {

            $lead = $_POST['user'];
            $voucher = $_POST['code'];

            $sql = "SELECT A1.id FROM companies AS A1 
                LEFT JOIN process_list AS A2 ON A2.company = A1.id
                LEFT JOIN process_list_lead_actions AS A3 ON A3.process_list = A2.id
                LEFT JOIN leads AS A4 ON A4.id = A3.lead
                WHERE A1.voucher = ? AND A4.id = ?
            ";
            $stmt = parent::$conn->prepare($sql);     
            $stmt->execute([ $voucher, $lead ]);
            
            if(!$stmt->fetch(PDO::FETCH_ASSOC))
                return [ 'type' => 'FAIL' ];
            
            $sql = "UPDATE leads SET voucher = ? WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([ $voucher, $lead ]);            

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

        }
        
        function statuspay() {
            
            $email = $_GET['email'];
            
            if(!empty($email)){
                
                $sql = "SELECT signature_status FROM `leads` WHERE email = ?";
                $stmt = parent::$conn->prepare($sql);     
                $stmt->execute([ $email ]);
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                
                if(!$result || $result['signature_status'] !== 'ACTIVE')
                    return [ 'type' => 'FAIL' ];
            
                return [ 'type' => 'OK' ];
                
            }
        }

    }