<?php

const FILTERS_PROFESSIONAL = array(
    'specializationName' => 'specialization_name',
    'roleLevel' => 'role_level',
    'vacancy' => 'professional_name',
    'branch' => 'branch'
);


class MauticVacancy extends Core {    

    private function getCondition($professional = true) {

        $FILTERS_LEAD = array(
            'educationLevel' => 'leveleducation',
            'gender' => 'gender',
            'city' => 'city',
            'state' => function($state) {              
                return "(state LIKE '%{$state['sigla']}%' OR state LIKE '%{$state['nome']}%')";
            },
            'district' => 'district',
            '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;
                
            }
        );

        $filters = $_GET['filters'] ?? null;
        $fields = array();
        
        if($filters) {

            $filters = json_decode($filters, true);
            
            foreach($filters as $param=>$value) {
                if($value) {
                    $field = $professional == true ? FILTERS_PROFESSIONAL[$param] : $FILTERS_LEAD[$param];
                    
                    if($field){
                        if(is_string($field))
                            $fields[] = " $field like '%$value%'";
                        else
                            $fields[] = $field($value);
                    }
                }
            }

        }
        
        return sizeof($fields) > 0 ? ' AND ' . join(' AND ', $fields) : '';

    }

    public function list() {

        $sub_condition = $this->getCondition(false);
        $sql = "SELECT DISTINCT(professional_name) FROM professional_experiences A1
            WHERE professional_name != '' 
            AND EXISTS(SELECT id FROM leads A2 WHERE A2.id = A1.lead_id $sub_condition)
            ORDER BY professional_name";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;

    }

    public function listSpecializationName() {

        $condition = $this->getCondition();
        $sub_condition = $this->getCondition(false);
        $sql = "SELECT DISTINCT(specialization_name) FROM professional_experiences A1
            WHERE specialization_name != '' $condition 
            AND EXISTS(SELECT id FROM leads A2 WHERE A2.id = A1.lead_id $sub_condition)
            ORDER BY specialization_name";
        
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;

    }

    public function listBranch() {

        $condition = $this->getCondition();
        $sub_condition = $this->getCondition(false);
        $sql = "SELECT DISTINCT(branch) FROM professional_experiences A1
            WHERE branch != '' $condition 
            AND EXISTS(SELECT id FROM leads A2 WHERE A2.id = A1.lead_id $sub_condition)
            ORDER BY branch";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;

    }

    public function listRoleLevel() {

        $condition = $this->getCondition();
        $sub_condition = $this->getCondition(false);
        $sql = "SELECT DISTINCT(role_level) FROM professional_experiences A1
            WHERE role_level != '' $condition 
            AND EXISTS(SELECT id FROM leads A2 WHERE A2.id = A1.lead_id $sub_condition)
            ORDER BY role_level";

        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;

    }

    public function listOffice() {

        $condition = $this->getCondition();
        $sql = "SELECT DISTINCT(office) FROM professional_experiences WHERE office != '' $condition ORDER BY office";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;

    }

}   