<?php

class Analytics extends Core {

    private function applyConditionProfessional($searchs) {

        $fields = array(            
            'professionalName' => " professional_name like '%{$searchs['vacancy']}%' ",
            'branch' => " branch like '%{$searchs['branch']}%' ",
            'roleLevel' => " role_level like '%{$searchs['roleLevel']}%' ",
            'specializationName' => " specialization_name like '%{$searchs['specializationName']}%' ",
            'office' => " office like '%{$searchs['office']}%' "
        );

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

    }

    private function applyConditionCandidate($searchs) {

        $fields = array(
            'state' => "L.state = '{$searchs['state']}'",
            'gender' => " gender = '{$searchs['gender']}'",
            'educationLevel' => "(leveleducation like '%{$searchs['educationLevel']}%' OR EXISTS (SELECT id FROM education_experience WHERE education_level like '%{$searchs['educationLevel']}%' AND lead_id = leads.id))",            
            '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;
                
            }
        );

        $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 list() {

        $searchs = $_GET['searchs'];        
        $fields = array(
            'state' => 'state',
            'education' => 'leveleducation',
            'gender' => 'gender'
        );
        $field = $fields[$_GET['field']];
        $condition = '';        
        
        if($searchs) {
            $searchs = json_decode($searchs, true);
            $condition = "WHERE " . $this->applyConditionCandidate($searchs);
        }

        $sql = "SELECT count(*) AS records, COALESCE($field, '') AS field FROM leads $condition GROUP BY COALESCE($field, '') ORDER BY records DESC";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $result;

    }

    function listProfessional() {

        $searchs = $_GET['searchs'];
        $fields = array(
            'professionalName' => 'professional_name',
            'specializationName' => 'specialization_name',
            'roleLevel' => 'role_level',
            'branch' => 'branch'
        );
        $field = $fields[$_GET['field']];
        $condition = '';
        if($searchs) {

            $searchs = json_decode($searchs, true);
            $condition = $this->applyConditionProfessional($searchs);
            $conditionProfessional = $this->applyConditionCandidate($searchs);
            
            if(strlen($conditionProfessional) > 0) {
                
                $conditionProfessional = " LEFT JOIN leads AS L ON L.id = PE.lead_id WHERE " . $this->applyConditionCandidate($searchs);

                if(strlen($condition))
                    $condition = " AND $condition";
                
            } else if(strlen($condition)) $condition = " WHERE $condition";

        }

        $sql = "SELECT count(*) AS records, 
            COALESCE($field, '') AS field
            FROM professional_experiences PE $conditionProfessional $condition
            GROUP BY COALESCE($field, '') ORDER BY records DESC";
        
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);        

        return $result;

    }

}