<?php

    const CANDIDATES_FIELD = array(
        'education' => 'leveleducation',
        'gender' => 'gender',
        'state' => 'state'
    );
    const PROFESSIONAL_FIELD = array(
        'professionalName' => 'professional_name',
        'specializationName' => 'specialization_name',
        'roleLevel' => 'role_level',
        'branch' => 'branch'
    );

    class Segment extends Core {

        public function list() {

            $name = $_GET['name'] ?? null;
            if($name)
                $where = " WHERE name like '%$name%'";

            $sql = "SELECT *, (SELECT count(*) FROM lead_lists_leads WHERE leadlist_id = id) as members
                FROM lead_lists $where ORDER BY name ASC";            
            $stmt = parent::$conn->query($sql);
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);        
            
            return [ 'type' => 'OK', 'data' => $result ];

        }

        public function save() {

            $name = $_POST['name'];
            $alias = str_replace(' ', '-', strtolower($name));
            $sql = "INSERT INTO lead_lists(name, is_published, date_added, created_by, description, alias, is_global, is_preference_center) 
                VALUES(:name, 1, NOW(), 'MEMPREGA Marketing', :description, :alias, 1, 0)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':name' => $name,
                ':description' => $name,
                ':alias' => $alias
            ]);

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

        }   

        public function scheduleSegmentImport() {

            $filters = $_POST['filters'];
            $filters = json_decode($filters, true);

            foreach($filters as $filter) {

                $filter_attr = $filter['filterName'];
                $filter_value = $filter['filterValue'];
                $parts = explode(':', $filter_attr);
                $total_record = 0;
                
                if(sizeof($parts) === 1) {
    
                    $field = CANDIDATES_FIELD[$filter_attr];
                    $sql = "SELECT count(*) as records FROM leads WHERE COALESCE($field, '') = ?";
                    $stmt = parent::$conn->prepare($sql);                
                    $stmt->execute([ $filter_value ]);
    
                } else {
    
                    $field = PROFESSIONAL_FIELD[$parts[1]];
    
                    $sql_professional = "SELECT distinct lead_id FROM professional_experiences WHERE COALESCE($field, '') = ?";                
                    $stmt = parent::$conn->prepare($sql_professional);
                    $stmt->execute([ $filter_value ]);              
    
                    $prof_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    $prof_ids = array_map(function($exp) {
                        return $exp['lead_id'];
                    }, $prof_results);
                    $prof_ids_string = implode(', ', $prof_ids);
                    
                    $sql = "SELECT count(*) as records FROM leads WHERE id IN($prof_ids_string)";
                    $stmt = parent::$conn->query($sql);
    
                }
                
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $total_record = $result['records'];
                
                $segments = $_POST['segments'];
                $segments = json_decode($segments, true);
                $sql = "INSERT INTO schedule_segment_import(leadlist_id, filter_attr, filter_value, total_record)
                    VALUES(:leadlist_id, :filter_attr, :filter_value, :total_record)";
                
                foreach($segments as $segment_id) {
    
                    $stmt = parent::$conn->prepare($sql);
                    $stmt->execute([
                        ':leadlist_id' => $segment_id,
                        ':filter_attr' => $filter_attr,
                        ':filter_value' => $filter_value,
                        ':total_record' => $total_record
                    ]);
    
                }    

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

        }

        function getScheduledSegment() {

            $filters = $_GET['filters'];
            $filters = json_decode($filters, true);
            $conditions = [];
            
            foreach($filters as $filter) {

                $filter_attr = $filter['filterName'];
                $filter_value = $filter['filterValue'];

                $conditions[] = "(filter_attr = '{$filter_attr}' AND filter_value = '{$filter_value}')";

            }

            $where = implode(' OR ', $conditions);
            $sql = "SELECT * FROM schedule_segment_import 
                WHERE $where";

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

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

        }

    }