<?php
include_once __dir__ .  '/../core/Filter.php';

class Analytics extends Core {

    use Filter;

    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() {
        
        $fields = array(
            'state' => 'state',
            'education' => 'leveleducation',
            'gender' => 'gender',
            'desired_job_title' => 'desired_job_title '
        );
        $field = $fields[$_GET['field']] ?? 'state';
        $conditions = [];
        
        if(isset($_GET['segment'])) {

            $segment_id = $_GET['segment'];
            $conditions[] = "EXISTS(
                SELECT lead_id FROM lead_lists_leads
                    LEFT JOIN lead_lists ON lead_lists_leads.leadlist_id = lead_lists.id
                    WHERE lead_lists.id = $segment_id AND lead_lists_leads.lead_id = leads.id
            )";

        }

        if(isset($_GET['desiredJob'])) {            
            $desired_job = $_GET['desiredJob'];            
            $conditions[] = "desired_job_title LIKE '%$desired_job%'";
        }

        if(isset($_GET['dateStart']) && isset($_GET['dateEnd'])) {

            $date_start = $_GET['dateStart'];
            $date_end = $_GET['dateEnd'];
            $conditions[] = "(date_added >= '$date_start' AND date_added <= '$date_end')";

        }

        if(isset($_GET['office'])) {
            
            $office = $_GET['office'];
            $conditions[] = "id IN (
                SELECT DISTINCT lead_id 
                FROM professional_experiences
                WHERE office LIKE '%$office%'
            )";

        }

        $condition_str = '';

        if(sizeof($conditions))
            $condition_str =  'WHERE ' . implode(' AND ', $conditions);
        
        $date = date('H.i.s');
        $sql = "SELECT count(*) AS records, COALESCE($field, '') AS field FROM leads $condition_str GROUP BY COALESCE($field, '') ORDER BY records DESC";
        
        $stmt = parent::$conn->query($sql);        
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;

    }

    function listProfessional() {

        $fields = array(
            'professionalName' => 'professional_name',
            'specializationName' => 'specialization_name',
            'roleLevel' => 'role_level',
            'branch' => 'branch'
        );
        $field = $fields[$_GET['field']];
        $condition = '';
        
        $sql = "SELECT count(*) AS records, 
            COALESCE($field, '') AS field
            FROM professional_experiences PE
            GROUP BY COALESCE($field, '') ORDER BY records DESC";
        
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);        

        return $result;

    }

    function listCandidatesByDate() {

        $condition_str = "WHERE account_type = 'candidate' AND firstname IS NOT NULL ";
        
        if(isset($_GET['dateStart'])) {

            $date_start = $_GET['dateStart'];
            $date_end = $_GET['dateEnd'];
            $condition_str .= " AND (date_added >= '$date_start' AND date_added <= '$date_end')";

        } else {

            $date_end = new  DateTime('Y-m-d');
            $interval = new DateInterval('P30D');
            $date_start = date_sub($interval, $date_end)->format('Y-m-d');
            $condition_str .= " AND (date_added >= '$date_start' AND date_added <= '$date_end')";

        }
        
        $date = date('H.i.s');
        $sql = "SELECT count(*) AS records, DATE(date_added) AS date_added FROM leads $condition_str GROUP BY DATE(date_added) DESC ORDER BY date_added ASC";		
        $stmt = parent::$conn->query($sql);        
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;

    }

    function listProcessListByDate() {

        $condition_str = '';
        
        if(isset($_GET['dateStart'])) {

            $date_start = $_GET['dateStart'];
            $date_end = $_GET['dateEnd'];
            $condition_str = " WHERE (created_at >= '$date_start' AND created_at <= '$date_end')";

        } else {

            $date_end = new  DateTime('Y-m-d');
            $interval = new DateInterval('P30D');
            $date_start = date_sub($interval, $date_end)->format('Y-m-d');
            $condition_str = " WHERE (created_at >= '$date_start' AND created_at <= '$date_end')";

        }
        
        $date = date('H.i.s');
        $sql = "SELECT count(*) AS records, DATE(created_at) AS created_at FROM process_list $condition_str GROUP BY DATE(created_at) DESC ORDER BY created_at ASC";
        
        $stmt = parent::$conn->query($sql);        
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;

    }

    private function _getGroupBy($group_by) {

        $array = [
            'age' => ['(EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM birthdate)) AS age', '(EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM birthdate)) BETWEEN 0 AND 100', 'GROUP BY age', null],
            'gender' => ["COALESCE(gender, '') gender", null, 'GROUP BY gender', null],
            'state' => ["COALESCE(state, '') state", null, 'GROUP BY state', 'ORDER BY records DESC'],
            'city' => ["COALESCE(city, '') city", null, 'GROUP BY city','ORDER BY records DESC'],
            'sinonimo' => ["COALESCE(sinonimo, '') sinonimo", null, 'GROUP BY sinonimo DESC', 'ORDER BY records DESC']            
        ];

        return $array[$group_by];

    }

    function total() {

        $searchs = isset($_GET['searchs']) ? $_GET['searchs'] : null;
        $where = "WHERE account_type != 'company' AND firstname IS NOT NULL ";
        $keyword = $_GET['keyword'] ?? null;
        $group_column = $_GET['groupBy'] ?? null;
        $dates_interval = $_GET['datesInterval'] ?? null;
        $group_by_str = '';
        $order_by_str = '';
        $cols = ' count(leads.id) as records';
        
        if ($searchs) {
            $searchs = json_decode($searchs, true);
            if ($searchs !== null && sizeof($searchs) > 0) {
                $additional_condition = $this->applyFilter($searchs);
                if (!empty($additional_condition)) {
                    $where .= "AND " . $additional_condition . " ";
                }
            }            
        }

        if($keyword) {

            $keywords = explode(' ', $keyword);

            if(sizeof($keywords) > 1)
                list($firstname, $lastname) = $keywords;
            else
                $lastname = $firstname = $keywords[0];

            $leads_id = $this->_getProfessionalExperiencesByKeyword($keyword);
            
            $where .= " AND (
                firstname LIKE '%$firstname%' OR lastname LIKE '%$lastname%' OR gender = '$keyword' 
                OR state LIKE '%$keyword%' OR city LIKE '%$keyword%' OR district LIKE '%$keyword%'";

            if(sizeof($leads_id)) {

                $in_str = implode(", ", $leads_id);
                $where .= "OR leads.id IN ($in_str) ";

            }

            $where .= ")";

        }

        if($dates_interval) {
            list($date_start, $date_end) = explode(':', $dates_interval);
            $where .= " AND date_added BETWEEN '$date_start' AND '$date_end'";
        }

        if($group_column) {
            list($columns, $condition, $group_by, $order_by) = $this->_getGroupBy($group_column);

            if($columns)
                $cols .= ", " . $columns;

            if($condition)
                $where .= " AND ($condition)";

            if($group_by)
                $group_by_str = $group_by;

            if($order_by)
                $order_by_str = $order_by;
        }

        if($group_column == 'sinonimo')
            $sql = "SELECT $cols FROM professional_experiences LEFT JOIN leads ON leads.id = professional_experiences.lead_id $where $group_by_str $order_by_str";
        else     
            $sql = "SELECT $cols FROM leads LEFT JOIN leads_curriculo ON leads_curriculo.lead_id = leads.id $where $group_by_str $order_by_str";
        
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return $result;

    }

    function getUTMCampaign() {

        $start_date = $_GET['dateStart'];
        $end_date = $_GET['dateEnd'];

        $sql = "SELECT 
                REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(properties, 'utm_camp=', -1), '&', 1), '}', ''), '\"', '') AS utm_camp,
                COUNT(*) AS count
            FROM 
                `lead_event_log`
            WHERE 
                bundle = 'page' 
                AND object = 'hit' 
                AND action = 'created_contact'
                AND date_added BETWEEN :start_date AND :end_date
                AND properties LIKE '%para-candidato%'
            GROUP BY 
                utm_camp";

        $stmt = parent::$conn->prepare($sql);
        $stmt->bindValue(':start_date', $start_date, PDO::PARAM_STR);
        $stmt->bindValue(':end_date', $end_date, PDO::PARAM_STR);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $result;
    }

    function getSinonimos() {

        $sql = "SELECT count(id) as records, COALESCE(sinonimo, '') AS sinonimo FROM `professional_experiences`
            GROUP BY sinonimo ORDER BY count(id) DESC";

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

        return $result;

    }

    function getDesiredJobTitle() {
        // desired_job_title
        $sql = "SELECT count(id) as records, COALESCE(desired_job_title, '') AS desired_job_title FROM `leads`
            GROUP BY desired_job_title ORDER BY count(id) DESC";

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

        return $result;

    }

    function getRegisterSteps() {
        
        $by_date = isset($_GET['groupByDate']) ?? false;        

        if($by_date) {

            if(isset($_GET['date_start'])) {
                $date_end = $_GET['date_end'];
                $date_start = $_GET['date_start'];
            } else {
                $date_end =  new \DateTime();
                $date_start = (new \DateTime())->sub(new \DateInterval('P90D')); 
            }

            $date_added_where = "
                (date_added BETWEEN '{$date_start->format('Y-m-d')}' AND '{$date_end->format('Y-m-d')}') AND
            ";
            
            $sql = "SELECT count(id) as records, DATE(date_added) date_added FROM leads WHERE $date_added_where account_type = 'candidate' AND passo_2_concluido = 'true' AND account_type = 'candidate'  AND firstname IS NOT NULL GROUP BY DATE(date_added)";            
            $stmt_step_two = parent::$conn->query($sql);
            $result_step_two = $stmt_step_two->fetchAll(PDO::FETCH_ASSOC);
            
            $sql = "SELECT count(id) as records, DATE(date_added) date_added FROM leads WHERE $date_added_where account_type = 'candidate' AND passo_3_concluido = 'true' AND  account_type = 'candidate'  AND firstname IS NOT NULL GROUP BY DATE(date_added)";
            $stmt_step_three = parent::$conn->query($sql);
            $result_step_three = $stmt_step_three->fetchAll(PDO::FETCH_ASSOC);

            $sql = "SELECT count(id) as records, DATE(date_added) date_added FROM leads WHERE $date_added_where account_type = 'candidate' AND passo_4_concluido = 'true' AND  account_type = 'candidate'  AND firstname IS NOT NULL GROUP BY DATE(date_added)";
            $stmt_step_four = parent::$conn->query($sql);
            $result_step_four = $stmt_step_four->fetchAll(PDO::FETCH_ASSOC);

        } else {

            $sql = "SELECT count(id) as records, COALESCE(passo_2_concluido) passo_2_concluido FROM leads WHERE account_type = 'candidate' AND passo_2_concluido = 'true' AND  account_type = 'candidate' AND firstname IS NOT NULL GROUP BY passo_2_concluido";
            $stmt_step_two = parent::$conn->query($sql);
            $result_step_two = $stmt_step_two->fetch(PDO::FETCH_ASSOC);

            $sql = "SELECT count(id) as records, COALESCE(passo_3_concluido) passo_3_concluido FROM leads WHERE account_type = 'candidate' AND passo_3_concluido = 'true' AND  account_type = 'candidate' AND firstname IS NOT NULL GROUP BY passo_3_concluido";
            $stmt_step_three = parent::$conn->query($sql);
            $result_step_three = $stmt_step_three->fetch(PDO::FETCH_ASSOC);

            $sql = "SELECT count(id) as records, COALESCE(passo_4_concluido) passo_4_concluido FROM leads WHERE account_type = 'candidate' AND passo_4_concluido = 'true' AND  account_type = 'candidate' AND firstname IS NOT NULL GROUP BY passo_4_concluido";
            $stmt_step_four = parent::$conn->query($sql);
            $result_step_four = $stmt_step_four->fetch(PDO::FETCH_ASSOC);            

        }

        return [
            'stepTwo' => $result_step_two,
            'stepThree' => $result_step_three,
            'stepFour' => $result_step_four
        ];

    }

    function getCompanies() {

        $data = [
            'total' => 0,
            'by_dates' => []
        ];
        $sql = "SELECT count(*) records FROM `companies` AS CO
            INNER JOIN companies_leads AS CL ON CO.id = CL.company_id
            LEFT JOIN leads AS LE ON LE.id = CL.lead_id
            WHERE LENGTH(cpf) > 11 AND firstname IS NOT NULL";

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

        $data['total'] = $result['records'];

        if(isset($_GET['date_start'])) {
            $date_end = $_GET['date_end'];
            $date_start = $_GET['date_start'];
        } else {
            $date_end =  new \DateTime();
            $date_start = (new \DateTime())->sub(new \DateInterval('P90D')); 
        }

        $date_added_where = "CO.date_added BETWEEN '{$date_start->format('Y-m-d')}' AND '{$date_end->format('Y-m-d')}' AND LENGTH(cpf) > 11";

        $sql = "SELECT count(CO.id) records, CO.date_added FROM `companies` AS CO
            INNER JOIN companies_leads AS CL ON CO.id = CL.company_id
            LEFT JOIN leads AS LE ON LE.id = CL.lead_id
            WHERE $date_added_where AND firstname IS NOT NULL
            GROUP BY CO.date_added 
            ORDER BY CO.date_added";

        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $data['by_dates'] = $result;

        return $data;

    }

    function getLogins() {

        $sql = "SELECT count(id) AS records, IF(last_login_time IS NULL, 0, 1) AS _last_login_time FROM leads
            WHERE account_type = 'candidate' AND firstname IS NOT NULL
            GROUP BY _last_login_time
            ORDER BY _last_login_time ASC;
        ";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $result;

    }

    function getFilledFields() {
        $data = [
            'grouped' => [],
            'total_fullfield' => null,
            'total' => null
        ];
        $sql = "SELECT 
                COUNT(CASE WHEN COALESCE(email, '') != '' THEN 1 END) AS qtd_email_preenchido,
                COUNT(CASE WHEN COALESCE(cpf, '') != '' THEN 1 END) AS qtd_cpf_preenchido,
                COUNT(CASE WHEN COALESCE(phone, '') != '' THEN 1 END) AS qtd_telefone_preenchido,
                COUNT(CASE WHEN COALESCE(mobile, '') != '' THEN 1 END) AS qtd_whatsapp_preenchido,
                COUNT(CASE WHEN COALESCE(firstname, '') != '' THEN 1 END) AS qtd_primeiro_nome_preenchido,
                COUNT(CASE WHEN COALESCE(lastname, '') != '' THEN 1 END) AS qtd_sobrenome_preenchido,
                COUNT(CASE WHEN COALESCE(desired_job_title, '') != '' THEN 1 END) AS qtd_cargo_desejado_preenchido,
                COUNT(CASE WHEN COALESCE(aboutme, '') != '' THEN 1 END) AS qtd_sobre_candidato_preenchido,
                COUNT(CASE WHEN COALESCE(gender, '') != '' THEN 1 END) AS qtd_sexo_preenchido,
                COUNT(CASE WHEN COALESCE(birthdate, '') != '' THEN 1 END) AS qtd_data_de_nascimento_preenchido,
                COUNT(CASE WHEN COALESCE(zipcode, '') != '' THEN 1 END) AS qtd_cep_preenchido,
                COUNT(CASE WHEN COALESCE(state, '') != '' THEN 1 END) AS qtd_estado_preenchido,
                COUNT(CASE WHEN COALESCE(district, '') != '' THEN 1 END) AS qtd_bairro_preenchido,
                COUNT(CASE WHEN COALESCE(address1, '') != '' THEN 1 END) AS qtd_logradouro_preenchido,
                COUNT(CASE WHEN COALESCE(address2, '') != '' THEN 1 END) AS qtd_numero_preenchido,    
                COUNT(CASE WHEN COALESCE(city, '') != '' THEN 1 END) AS qtd_cidade_preenchido
            FROM leads
            WHERE firstname IS NOT NULL AND account_type = 'candidate'";
        $stmt = parent::$conn->query($sql);
        $data['grouped'] = $stmt->fetch(PDO::FETCH_ASSOC);

        $sql = "SELECT COUNT(*) AS total
            FROM leads
            WHERE account_type = 'candidate' 
                AND firstname IS NOT NULL 
                AND (
                    COALESCE(email, '') != '' AND
                    COALESCE(cpf, '') != '' AND
                    COALESCE(phone, '') != '' AND                    
                    COALESCE(mobile, '') != '' AND
                    COALESCE(firstname, '') != '' AND
                    COALESCE(lastname, '') != '' AND
                    COALESCE(desired_job_title, '') != '' AND
                    COALESCE(gender, '') != '' AND
                    COALESCE(birthdate, '') != '' AND
                    COALESCE(zipcode, '') != '' AND
                    COALESCE(state, '') != '' AND
                    COALESCE(district, '') != '' AND
                    COALESCE(address1, '') != '' AND
                    COALESCE(address2, '') != '' AND                    
                    COALESCE(city, '') != ''
                )        
        ";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $data['totalFullfield'] = $result['total'];

        $sql = "SELECT COUNT(*) AS total
            FROM leads
            WHERE account_type = 'candidate'
                AND firstname IS NOT NULL
        ";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $data['total'] = $result['total'];

        return $data;
        
    }

}