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

class ProcessList extends Core
{
    use Filter;

    function store()
    {

        $lead_id = $_POST['userId'];
        $name = $_POST['name'] ?? null;
        $company = $_POST['company'];
        $viewname = $_POST['viewname'] ?? null;
        $viewend = $_POST['viewend'] ?? null;
        $viewtel = $_POST['viewtel'] ?? null;
        $companyname = $_POST['companyname'] ?? null;
        $phone = $_POST['phone'] ?? null;
        $zipCode = $_POST['zipCode'] ?? null;
        $state = $_POST['state'] ?? null;
        $city = $_POST['city'] ?? null;
        $district = $_POST['district'] ?? null;
        $street = $_POST['street'] ?? null;
        $number = $_POST['number'] ?? null;
        $complement = $_POST['complement'] ?? null;
        $modalityjob = $_POST['modalityjob'];
        $sinonimo = $_POST['sinonimo'];        
        $salary = $_POST['salary'] ?? null;
        $coin = $_POST['coin'];
        $workregime = $_POST['workregime'];
        $daysWeek = $_POST['daysWeek'];
        $start_monday = $_POST['start_monday'] ?? null;
        $end_monday = $_POST['end_monday'] ?? null;
        $start_tuesday = $_POST['start_tuesday'] ?? null;
        $end_tuesday = $_POST['end_tuesday'] ?? null;
        $start_thursday = $_POST['start_thursday'] ?? null;
        $end_thursday = $_POST['end_thursday'] ?? null;
        $start_wednesday = $_POST['start_wednesday'] ?? null;
        $end_wednesday = $_POST['end_wednesday'] ?? null;
        $start_friday = $_POST['start_friday'] ?? null;
        $end_friday = $_POST['end_friday'] ?? null;
        $start_saturday = $_POST['start_saturday'] ?? null;
        $end_saturday = $_POST['end_saturday'] ?? null;
        $start_sunday = $_POST['start_sunday'] ?? null;
        $end_sunday = $_POST['end_sunday'] ?? null;
        $new_job_id = $_POST['new_job_id'] ?? null;
        $benefits = str_replace("\n", '<br/>', $_POST['benefits']);
        $description = str_replace("\n", '<br/>', $_POST['description']);
        $requirements = str_replace("\n", '<br/>', $_POST['requirements']);
        $activities = str_replace("\n", '<br/>', $_POST['activities']);
        $created_by = $_POST['created_by'];
        $tipocargo = $_POST['tipocargo'] ?? null;
        $qtd = $_POST['qtd'];
        $office = $_POST['office'] ?? null;
        $age_end = $_POST['ageEnd'] ?? null;
        $age_start = $_POST['ageStart'] ?? null;
        $gender = $_POST['gender'] ?? null;
        $white_lb_id = $this->config['WHITE_LABEL_ID'] ?? null;

        if ($state) {
            if (is_string($state) && strpos(trim($state), '{') === 0) {
                $decoded = json_decode($state, true);
                if (json_last_error() === JSON_ERROR_NONE && isset($decoded['sigla'])) {
                    $state = $decoded['sigla'];
                }
            }
        }

        $profile_data = [
            'ageEnd' => $age_end,
            'ageStart' => $age_start,
            'gender' => $gender
        ];
        
        $sql = "INSERT INTO process_list(
            name, 
            company, 
            viewname, 
            viewend, 
            viewtel, 
            companyname, 
            phone, 
            zipCode, 
            state, 
            city, 
            district, 
            street, 
            number, 
            complement, 
            modalityjob, 
            sinonimo, 
            new_job_id, 
            salary, 
            coin, 
            workregime, 
            daysWeek, 
            start_monday, 
            end_monday, 
            start_tuesday, 
            end_tuesday, 
            start_wednesday, 
            end_wednesday, 
            start_thursday, 
            end_thursday, 
            start_friday, 
            end_friday, 
            start_saturday, 
            end_saturday, 
            start_sunday, 
            end_sunday, 
            benefits, 
            requirements, 
            activities, 
            created_by, 
            qtd, 
            tipocargo, 
            description, 
            office,
            profile_data,
            white_label_id
        ) VALUES (
            :name, 
            :company, 
            :viewname, 
            :viewend, 
            :viewtel, 
            :companyname, 
            :phone, 
            :zipCode, 
            :state, 
            :city, 
            :district, 
            :street, 
            :number, 
            :complement, 
            :modalityjob, 
            :sinonimo, 
            :new_job_id, 
            :salary, 
            :coin, 
            :workregime, 
            :daysWeek, 
            :start_monday, 
            :end_monday, 
            :start_tuesday, 
            :end_tuesday, 
            :start_wednesday, 
            :end_wednesday, 
            :start_thursday, 
            :end_thursday, 
            :start_friday, 
            :end_friday, 
            :start_saturday, 
            :end_saturday, 
            :start_sunday, 
            :end_sunday, 
            :benefits, 
            :requirements, 
            :activities, 
            :created_by, 
            :qtd, 
            :tipocargo, 
            :description, 
            :office,
            :profile_data,
            :white_lb_id
        )";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':company' => $company,
            ':viewname' => $viewname,
            ':viewend' => $viewend,
            ':viewtel' => $viewtel,
            ':companyname' => $companyname,
            ':phone' => $phone,
            ':zipCode' => $zipCode,
            ':state' => $state,
            ':city' => $city,
            ':district' => $district,
            ':street' => $street,
            ':number' => $number,
            ':complement' => $complement,
            ':modalityjob' => $modalityjob,
            ':sinonimo' => $sinonimo,
            ':new_job_id' => $new_job_id,
            ':salary' => $salary,
            ':coin' => $coin,
            ':workregime' => $workregime,
            ':daysWeek' => $daysWeek,
            ':start_monday' => $start_monday,
            ':end_monday' => $end_monday,
            ':start_tuesday' => $start_tuesday,
            ':end_tuesday' => $end_tuesday,
            ':start_wednesday' => $start_wednesday,
            ':end_wednesday' => $end_wednesday,
            ':start_thursday' => $start_thursday,
            ':end_thursday' => $end_thursday,
            ':start_friday' => $start_friday,
            ':end_friday' => $end_friday,
            ':start_saturday' => $start_saturday,
            ':end_saturday' => $end_saturday,
            ':start_sunday' => $start_sunday,
            ':end_sunday' => $end_sunday,
            ':benefits' => $benefits,
            ':requirements' => $requirements,
            ':activities' => $activities,
            ':created_by' => $created_by,
            ':qtd' => $qtd,
            ':tipocargo' => $tipocargo,
            ':description' => $description,
            ':office' => $office,
            ':profile_data' => json_encode($profile_data),
            ':white_lb_id' => $white_lb_id
        ]);

        if ($stmt->errorCode() != '00000')
            throw new \Exception(json_encode($stmt->errorInfo()));

        $insert_id = parent::$conn->lastInsertId();
        $sql2 = "INSERT INTO process_list_user(process_list_id, lead_id) VALUES (?,?)";
        $stmt2 = parent::$conn->prepare($sql2);
        $stmt2->execute([$insert_id, $created_by]);

        $title = "Processo seletivo \"$name\" cadastrado!";
        $url = "/recruiter/process";
        LogEvent::log($created_by, $insert_id, $url, $title, LogEvent::CREATE_PROCESS);

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

    function update()
    {

        $id = $_POST['id'];
        $name = $_POST['name'] ?? null;
        $viewname = $_POST['viewname'];
        $viewend = $_POST['viewend'] ?? null;;
        $viewtel = $_POST['viewtel'];
        $companyname = $_POST['companyname'] ?? null;
        $phone = $_POST['phone'] ?? null;
        $zipCode = $_POST['zipCode'] ?? null;
        $state = $_POST['state'] ?? null;
        $city = $_POST['city'] ?? null;
        $district = $_POST['district'] ?? null;
        $street = $_POST['street'] ?? null;
        $number = $_POST['number'] ?? null;
        $complement = $_POST['complement'] ?? null;
        $modalityjob = $_POST['modalityjob'];
        $sinonimo = $_POST['sinonimo'];        
        $salary = isset($_POST['salary']) 
            ? 
                strtolower($_POST['salary']) == 'a combinar' || empty(trim($_POST['salary']))
                    ? null
                    : $_POST['salary'] 
            : null;
        $coin = $_POST['coin'];
        $workregime = $_POST['workregime'];
        $daysWeek = $_POST['daysWeek'];
        $start_monday = $_POST['start_monday'] ?? null;
        $end_monday = $_POST['end_monday'] ?? null;
        $start_tuesday = $_POST['start_tuesday'] ?? null;
        $end_tuesday = $_POST['end_tuesday'] ?? null;
        $start_thursday = $_POST['start_thursday'] ?? null;
        $end_thursday = $_POST['end_thursday'] ?? null;
        $start_wednesday = $_POST['start_wednesday'] ?? null;
        $end_wednesday = $_POST['end_wednesday'] ?? null;
        $start_friday = $_POST['start_friday'] ?? null;
        $end_friday = $_POST['end_friday'] ?? null;
        $start_saturday = $_POST['start_saturday'] ?? null;
        $end_saturday = $_POST['end_saturday'] ?? null;
        $start_sunday = $_POST['start_sunday'] ?? null;
        $end_sunday = $_POST['end_sunday'] ?? null;
        $new_job_id = $_POST['new_job_id'] ?? null;
        $benefits = str_replace("\n", '<br/>', $_POST['benefits']);
        $description = str_replace("\n", '<br/>', $_POST['description']);
        $requirements = str_replace("\n", '<br/>', $_POST['requirements']);
        $activities = str_replace("\n", '<br/>', $_POST['activities']);
        $tipocargo = $_POST['tipocargo'] ?? null;
        $qtd = $_POST['qtd'];
        $office = $_POST['office'] ?? null;
        $age_start = $_POST['ageStart'] ?? null;
        $age_end = $_POST['ageEnd'] ?? null;
        $gender = $_POST['gender'] ?? null;
        $white_lb_id = $this->config['WHITE_LABEL_ID'] ?? null;

        if ($state) {
            if (is_string($state) && strpos(trim($state), '{') === 0) {
                $decoded = json_decode($state, true);
                if (json_last_error() === JSON_ERROR_NONE && isset($decoded['sigla'])) {
                    $state = $decoded['sigla'];
                }
            }
        }

        $profile_data = [
            'ageEnd' => $age_end,
            'ageStart' => $age_start,
            'gender' => $gender
        ];

        $sql = "UPDATE process_list SET 
            name = :name, 
            viewname = :viewname, 
            viewend = :viewend, 
            viewtel = :viewtel, 
            companyname = :companyname, 
            phone = :phone, 
            zipCode = :zipCode, 
            state = :state, 
            city = :city, 
            district = :district, 
            street = :street, 
            number = :number, 
            complement = :complement, 
            modalityjob = :modalityjob, 
            sinonimo = :sinonimo, 
            new_job_id = :new_job_id, 
            salary = :salary, 
            coin = :coin, 
            workregime = :workregime, 
            daysWeek = :daysWeek, 
            start_monday = :start_monday, 
            end_monday = :end_monday, 
            start_tuesday = :start_tuesday, 
            end_tuesday = :end_tuesday, 
            start_wednesday = :start_wednesday, 
            end_wednesday = :end_wednesday, 
            start_thursday = :start_thursday, 
            end_thursday = :end_thursday, 
            start_friday = :start_friday, 
            end_friday = :end_friday, 
            start_saturday = :start_saturday, 
            end_saturday = :end_saturday, 
            start_sunday = :start_sunday, 
            end_sunday = :end_sunday, 
            benefits = :benefits, 
            requirements = :requirements, 
            activities = :activities, 
            qtd = :qtd, 
            tipocargo = :tipocargo, 
            description = :description, 
            office = :office,
            profile_data = :profile_data
        WHERE id = :id AND white_label_id = :white_lb_id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':viewname' => $viewname,
            ':viewend' => $viewend,
            ':viewtel' => $viewtel,
            ':companyname' => $companyname,
            ':phone' => $phone,
            ':zipCode' => $zipCode,
            ':state' => $state,
            ':city' => $city,
            ':district' => $district,
            ':street' => $street,
            ':number' => $number,
            ':complement' => $complement,
            ':modalityjob' => $modalityjob,
            ':sinonimo' => $sinonimo,
            ':new_job_id' => $new_job_id,
            ':salary' => $salary,
            ':coin' => $coin,
            ':workregime' => $workregime,
            ':daysWeek' => $daysWeek,
            ':start_monday' => $start_monday,
            ':end_monday' => $end_monday,
            ':start_tuesday' => $start_tuesday,
            ':end_tuesday' => $end_tuesday,
            ':start_wednesday' => $start_wednesday,
            ':end_wednesday' => $end_wednesday,
            ':start_thursday' => $start_thursday,
            ':end_thursday' => $end_thursday,
            ':start_friday' => $start_friday,
            ':end_friday' => $end_friday,
            ':start_saturday' => $start_saturday,
            ':end_saturday' => $end_saturday,
            ':start_sunday' => $start_sunday,
            ':end_sunday' => $end_sunday,
            ':benefits' => $benefits,
            ':requirements' => $requirements,
            ':activities' => $activities,
            ':qtd' => $qtd,
            ':tipocargo' => $tipocargo,
            ':description' => $description,
            ':office' => $office,
            ':id' => $id,
            ':profile_data' => json_encode($profile_data),
            ':white_lb_id' => $white_lb_id
        ]);

        if ($stmt->rowCount() > 0) {
            return ['type' => 'OK'];
        } else {
            return ['type' => 'FAIL'];
        }
    }

    function getById()
    {

        $id = $_GET['id'];        
        $shared = isset($_GET['shared']) ? $_GET['shared'] : null;
        $sql = "SELECT * FROM process_list WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $company_id = $result['company'];

        if($shared) {
            LogEvent::logKpi($company_id, LogEvent::ACCESS_SHARE_PROCESS);
        }

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

    function list()
    {
 
        $company = $_GET['company'];
        $white_lb_id = $this->config['WHITE_LABEL_ID'] ?? null;
 
        $sql = "SELECT P.*,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status NOT IN ('pre-approval', 'disabled')) AS candidates,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status = 'pending') AS candidatesPending,
                    CASE 
                        WHEN NOT EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id ) THEN 1 
                        WHEN EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id AND CPL.status <> 'finish' ) THEN 0 
                        ELSE 1 END AS boost_process_list,
                        CASE WHEN EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id AND CPL.status IN ('new','active') ) THEN 1 ELSE 0 END AS filter_process_list    
                FROM process_list AS P WHERE P.company = ? AND P.status <> 'archived' AND P.white_label_id = ? ORDER BY P.created_at DESC
            ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$company, $white_lb_id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return ['type' => 'OK', 'data' => $result];
    }

    function delete()
    {

        $id = $_GET['id'];
        $sql = "DELETE FROM process_list WHERE id = ? AND white_label_id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id, $this->config['WHITE_LABEL_ID'] ?? null]);

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

    function saveSelectionTest()
    {

        $tests = $_POST['tests'];
        $candidates = $_POST['candidates'];
        $process_list = $_POST['processList'];
        $user_id = $_POST['userId'];

        $tests = json_decode($tests, true);
        $candidates = json_decode($candidates, true);

        $candidates_id = join(', ', $candidates);

        $sql = "DELETE FROM process_list_test WHERE process_list = $process_list AND lead IN ($candidates_id) AND status = 'pending'";
        parent::$conn->query($sql);

        $sql = "INSERT IGNORE INTO process_list_test(process_list, selection_test, lead) VALUES(?, ?, ?)";

        $campaign = new QueueCampaign($this->getConnection());

        $candidates_emails = array();
        foreach ($tests as $test) {

            foreach ($candidates as $candidate) {

                $stmt = parent::$conn->prepare($sql);
                $stmt->execute([$process_list, $test, $candidate]);
                $user_id = $candidate;
                $this->saveSelectionTestLog($process_list, $test, $candidate, $user_id);

                $campaign->queue($candidate, 'test');
            }
        }

        $this->sendVoucher($candidates);

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

    private function saveSelectionTestLog($process_list, $test, $lead_id, $user_id) {

        $sql = "SELECT * FROM leads WHERE id = ?";
        $stmt_lead = parent::$conn->prepare($sql);
        $stmt_lead->execute([$lead_id]);
        $lead = $stmt_lead->fetch(PDO::FETCH_ASSOC);
        $name = strtoupper($lead['firstname'] . ' ' . $lead['lastname']);        

        $sql = "SELECT * FROM selection_tests WHERE id = ?";
        $stmt_test = parent::$conn->prepare($sql);
        $stmt_test->execute([$test]);
        $test_row = $stmt_test->fetch(PDO::FETCH_ASSOC);
        $test_name = $test_row['name'] ?? '';

        $process_sql = "SELECT name FROM process_list WHERE id = ?";
        $stmt_process = parent::$conn->prepare($process_sql);
        $stmt_process->execute([$process_list]);
        $process_row = $stmt_process->fetch(PDO::FETCH_ASSOC);
        $process_name = $process_row['name'] ?? '';

        $title = "Adicinou o teste \"$test_name\"\n ao processo \"$process_name\"\n para o candidato $name";
        $url = "/recruiter/process";
        
        LogEvent::log($user_id, $process_list, $url, $title, LogEvent::APPLY_TEST);

    }

    function listProcessListTest()
    {

        $process_list = $_GET['processList'];

        $sql = "SELECT A2.*, A1.lead FROM process_list_test AS A1
            LEFT JOIN selection_tests as A2 ON A2.id = A1.selection_test
            WHERE process_list = $process_list";

        $stmt = parent::$conn->query($sql);
        return ['type' => 'OK', 'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)];
    }

    function deleteProcessListTest()
    {

        $process_list = $_GET['processList'];
        $test = $_GET['test'];
        $sql = "DELETE FROM process_list_test WHERE process_list = ? AND selection_test = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list, $test]);

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

    function saveLeads()
    {

        $leads = $_POST['leads'] ?? null;
        $process_list = $_POST['processList'] ?? null;
        $source = $_POST['source'] ?? null;

        if (is_null($leads) || is_null($process_list)) {
            return ['type' => 'ERROR', 'message' => 'Leads and process list are required'];
        }

        $leads = json_decode($leads, true);
        $pre_approval = isset($_POST['preApproval']) ? 'pre-approval' : 'waiting';

        parent::$conn->beginTransaction();

        if (isset($_POST['keywords']) && !is_null($_POST['keywords']))
            $keywords = $this->saveProcessListKeywords($process_list, $_POST['keywords']);

        $sql = "INSERT INTO process_list_lead(lead, process_list, score, keywords, status, source) 
            VALUES(:lead_id, :process_id, :score, :keyword, :status, :source) 
            ON DUPLICATE KEY UPDATE score = VALUES(score), keywords = VALUES(keywords)";

        foreach ($leads as $lead) {
            $id = $lead['id'];
            $score = isset($lead['score']) ? $lead['score'] : 0;
            $keywords = isset($keywords) ? json_encode($keywords) : NULL;
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':lead_id' => $id, 
                ':process_id' => $process_list, 
                ':score' => $score, 
                ':keyword' => $keywords, 
                ':status' => $pre_approval, 
                ':source' => $source
            ]);

            $this->removeSuggestedCandidate($process_list, $id);
        }

        // $sql = "UPDATE process_list SET status = 'read' WHERE id = ?";
        // $stmt = parent::$conn->prepare($sql);
        // $stmt->execute([$process_list]);

        if (isset($keywords))
            $this->updateCandidatesKeywords($process_list, $keywords);        

        $this->addToDefaultProcessStep($leads, $process_list);

        parent::$conn->commit();

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

    function updateCandidatesScores()
    {

        if (!isset($_POST['processId']) || empty($_POST['processId'])) {
            return [
                'type' => 'ERROR',
                'message' => 'Process ID is required'
            ];
        }
        $process_list = $_POST['processId'];
        $sql = "SELECT keywords FROM process_list WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':id' => $process_list
        ]);
        $process = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($process['keywords']) {

            list($joins, $columns) = $this->getScoreJoin($process['keywords']);

            $sql = "SELECT leads.id, $columns FROM leads LEFT JOIN process_list_lead ON process_list_lead.lead = leads.id $joins WHERE process_list = :process_list";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':process_list' => $process_list
            ]);

            while ($lead = $stmt->fetch(PDO::FETCH_ASSOC)) {

                $sql_update = "UPDATE process_list_lead SET score = :score, keywords = :keywords WHERE lead = :lead_id AND process_list = :process_list";
                $stmt_update = parent::$conn->prepare($sql_update);
                $stmt_update->execute([
                    ':score' => $lead['score'],
                    ':lead_id' => $lead['id'],
                    ':process_list' => $process_list,
                    ':keywords' => json_encode($process['keywords'])
                ]);
            }
        }

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


    function updateProcessListKeywords() {

        $process_list = $_POST['processList'] ?? null;
        $sql = "SELECT sinonimo FROM process_list WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $process_list]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $sinonimo = $result['sinonimo'];        
        $keywords = $this->splitKeywords($sinonimo);
        
        $sql_update = "UPDATE process_list SET keywords = :keywords WHERE id = :process_list";
        $stmt = parent::$conn->prepare($sql_update);
        $stmt->execute([
            ':keywords' => json_encode($keywords),
            ':process_list' => $process_list
        ]);       

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

    private function updateCandidatesKeywords($process_list, $keywords)
    {
        $sql = "UPDATE process_list_lead SET keywords = :keywords WHERE process_list = :process_list";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':process_list' => $process_list,
            ':keywords' => json_encode($keywords)
        ]);
    }

    private function saveProcessListKeywords($process_list, $keywords)
    {
        
        $sql = "SELECT id, keywords FROM process_list WHERE id = :process_list";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':process_list' => $process_list]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if($result && !is_null($result['keywords'])) {            
            $old_keywords = json_decode($result['keywords']);
        } else $old_keywords = [];
        
        $keywords = array_unique(array_merge((array)$old_keywords, (array)json_decode($keywords)));
        
        $sql_update = "UPDATE process_list SET keywords = :keywords WHERE id = :process_list";
        $stmt = parent::$conn->prepare($sql_update);
        $stmt->execute([
            ':keywords' => json_encode(array_values($keywords)),
            ':process_list' => $process_list
        ]);

        return $keywords;
    }
	
	function getLeadByID()
    {

        $id = $_GET['id'] ?? null;
        $process_id = $_GET['processId'] ?? null;

        if (is_null($id) || is_null($process_id)) {
            return ['type' => 'ERROR', 'message' => 'ID and Process ID are required'];
        }

        $sql = "SELECT leads.id FROM leads
            LEFT JOIN process_list_lead ON process_list_lead.lead = leads.id
            WHERE leads.id = :id AND process_list = :process_list";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':id' => $id,
            ':process_list' => $process_id
        ]);

        return ['type' => 'OK', 'result' => $stmt->fetch(PDO::FETCH_ASSOC)];
    }

    function saveLeadByEmail()
    {

        $email = $_POST['email'];
        $process_id = $_POST['processId'];

        $sql = "SELECT id FROM leads WHERE email = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$email]);

        if ($stmt->rowCount() == 0)
            throw new \Exception('Nenhum candidato encontrado com esse e-mail', 404);

        $lead = $stmt->fetch(PDO::FETCH_ASSOC);

        $sql = "INSERT IGNORE INTO process_list_lead(lead, process_list) VALUES(?, ?)";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$lead['id'], $process_id]);

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

    function getTotalLeads()
    {

        $process_list = $_GET['processList'];
        $filter = $_GET['filter'] ?? null;
        $approval_status = $_GET['approvalStatus'] ?? null;
        $tags = $_GET['tags'] ?? null;

        if ($approval_status)
            $where = "A1.status = 'pre-approval'";
        else
            $where = "A1.status NOT IN ('disabled', 'pre-approval')";

        $sql = "SELECT count(A1.id) AS records FROM process_list_lead AS A1 
                LEFT JOIN leads AS A2 ON A1.lead = A2.id 
                WHERE A1.process_list = :id AND $where
            ";

        if ($filter) {

            $sql .= " AND (
                    A2.firstname LIKE :filter OR
                    A2.lastname LIKE :filter OR
                    A2.cpf LIKE :filter OR
                    A2.city LIKE :filter OR
                    A2.address1 LIKE :filter OR
                    A2.mobile LIKE :filter OR
                    A2.phone LIKE :filter OR
                    A2.email LIKE :filter
                    OR A2.id IN (SELECT A7.lead_id FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id AND description LIKE :filter)
                )";
        }

        if($tags) {
            $tags = json_decode($tags);

            if(sizeof($tags)) {

                $tags_string = array_map(function($tag) {
                    return "'{$tag}'";
                }, $tags);
                
                $tags_string = join(',', $tags_string);
                $sql .= " AND A1.source IN ($tags_string)";
            }
        }

        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':id', $process_list, PDO::PARAM_INT);

        if ($filter) {
            $filter = "%$filter%";
            $stmt->bindParam(':filter', $filter, PDO::PARAM_STR);
        }

        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

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

    function getLeads()
    {

        $pageIndex = $_GET['pageIndex'] ?? 0;
        $limit = $_GET['limit'] ?? 20;
        $all = $_GET['all'] ?? false;
        $filter = $_GET['filter'] ?? null;
        $approval_status = $_GET['approvalStatus'] ?? null;
        $process_list = $_GET['processList'];
        $order_by_field = $_GET['orderByField'] ?? null;
        $order = " ORDER BY total_score DESC, firstname";
        $tags = $_GET['tags'] ?? null;

        if($order_by_field) {

            $order_by_field = json_decode($order_by_field, true);

            if(sizeof($order_by_field)) {                
                $field = array_keys($order_by_field)[0];
                $dir = array_values($order_by_field)[0];

                if($field && $dir)
                    $order = " ORDER BY $field $dir";
            }

        }

        if ($approval_status) {
            $where = " A1.status = 'pre-approval'";
            $order = " ORDER BY A1.date_added DESC";
        } else
            $where = "A1.status NOT IN ('disabled', 'pre-approval')";

        $offset =  $pageIndex * $limit;
        $sql = "SELECT A2.*, 
                    A1.status AS candidateStatus, 
                    A1.id AS id2,
                    A1.score,
                    A1.keywords,
                    A1.source,
                    A1.date_added,
                    (SELECT sum(points) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead GROUP BY A4.lead) AS points,
                    (SELECT count(status) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead AND status IN('evaluated') LIMIT 1) AS status,
                    (SELECT count(*) FROM process_list_test_response AS A5 WHERE lead = A1.lead AND A5.process_list = :process_list) as responses_total,
                    (SELECT count(*) FROM process_list_test_response AS A6 WHERE lead = A1.lead AND A6.process_list = :process_list AND A6.approved = 1) as responses_approved_total,
                    (SELECT A4.accept FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview,
                    (SELECT A4.status FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list =  :process_list AND A4.lead = A1.lead ORDER BY id DESC LIMIT 1) AS accept_interview_status,
                    (SELECT count(*) FROM fit_cultural AS A8 WHERE A8.email_fit = A2.email) as responses_fit,
                    (SELECT count(A4.view) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead AND view = '0') AS accept_view_interview,
                    (SELECT count(A9.view) FROM process_list_test AS A9 WHERE process_list = :process_list AND A9.lead = A1.lead AND view = '0') AS accept_view_test,                    
                    IFNULL((SELECT COALESCE(SUM(points), 0) FROM process_list_interviews AS A3 LEFT JOIN process_list_lead_interviews AS A4 ON A4.interview = A3.id WHERE process_list = :process_list AND A4.lead = A1.lead GROUP BY A4.lead), 0) + IFNULL(A1.score, 0) AS total_score,
                    kanban_step_id,
                    kanban_item_order,                    
                    KD.order AS step_order
                FROM process_list_lead as A1
                LEFT JOIN leads as A2 ON A1.lead = A2.id            
                /*LEFT JOIN kanban_item AS K ON CAST(JSON_UNQUOTE(JSON_EXTRACT(K.data, '$.id')) AS UNSIGNED) = A2.id
                LEFT JOIN kanban_dashboard AS KD ON K.step_id = KD.id AND KD.process_id = :process_list*/
                LEFT JOIN kanban_dashboard AS KD ON kanban_step_id = KD.id AND KD.process_id = :process_list
                WHERE A1.process_list = :process_list AND $where
            ";
        if ($filter) {

            $sql .= " AND (
                    A2.firstname LIKE :filter OR
                    A2.lastname LIKE :filter OR
                    A2.cpf LIKE :filter OR
                    A2.city LIKE :filter OR
                    A2.address1 LIKE :filter OR
                    A2.mobile LIKE :filter OR
                    A2.phone LIKE :filter OR
                    A2.email LIKE :filter
                    OR A2.id IN (SELECT A7.lead_id FROM professional_experiences AS A7 WHERE A7.lead_id = A2.id AND description LIKE :filter)
                )";
        }

        
       if($tags) {
            $tags = json_decode($tags);

            if(sizeof($tags)) {

                $tags_string = array_map(function($tag) {
                    return "'{$tag}'";
                }, $tags);
                
                $tags_string = join(',', $tags_string);
                $sql .= " AND A1.source IN ($tags_string)";
            }
        }

        $sql .= $order;       

        if (!$all)
            $sql .= " LIMIT :offset, :limit";
        
        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':process_list', $process_list);

        if ($filter) {
            $filter = "%$filter%";
            $stmt->bindParam(':filter', $filter, PDO::PARAM_STR);
        }

        if (!$all) {
            $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
            $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        }

        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function deleteLead()
    {

        $process_list = $_GET['processList'];
        $lead = $_GET['lead'];

        $sql = "DELETE FROM process_list_lead WHERE process_list = ? AND lead = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list, $lead]);

        $sql = "DELETE process_list_interview_sessions, process_list_lead_interviews FROM process_list_interview_sessions
            LEFT JOIN process_list_interviews ON process_list_interviews.id = process_list_interview_sessions.process_list_interview
            LEFT JOIN process_list_lead_interviews ON process_list_lead_interviews.interview = process_list_interviews.id              
            WHERE process_list = ? AND process_list_interview_sessions.lead = ?
        ";

        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list, $lead]);

        $sql = "DELETE FROM process_list_test WHERE process_list = ? AND lead = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list, $lead]);

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

    function saveInterview()
    {
 
        $process_list = $_POST['processList'];
        $name = $_POST['name'];
        $contact = $_POST['contact'];
        $address = $_POST['address'] ?? null;
        $time_start = $_POST['timeStart'];
        $time_end = $_POST['timeEnd'];
        $date_start = $_POST['dateStart'];
        $next_days = $_POST['nextDays'] == 'true';
        $candidates_session_duration = $_POST['candidatesSessionDuration'];
        $type = isset($_POST['type']) ? $_POST['type'] : 'false';
        $use_google = isset($_POST['use_google']) ? $_POST['use_google'] : 'false';
        $meet_link = $_POST['meetLink'] ?? null;
        
        $sql = "INSERT INTO process_list_interviews(process_list, name, contact, address, date_start, time_start, time_end, next_days, candidates_session_duration, type, use_google, meet_link)
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list, $name, $contact, $address, $date_start, $time_start, $time_end, $next_days, $candidates_session_duration, $type, $use_google, $meet_link]);
 
        return ['type' => 'OK'];
    }

    function updateInterview()
    {
 
        $id = $_POST['id'];
        $name = $_POST['name'];
        $contact = $_POST['contact'];
        $address = $_POST['address'] ?? null;
        $time_start = $_POST['timeStart'];
        $time_end = $_POST['timeEnd'];
        $date_start = $_POST['dateStart'];
        $next_days = $_POST['nextDays'] == 'true';
        $candidates_session_duration = $_POST['candidatesSessionDuration'];
        $type = $_POST['type'];
        $use_google = $_POST['use_google'];
        $meet_link = $_POST['meetLink'] ?? null;
 
 
        $sql = "UPDATE process_list_interviews SET name = :name, contact = :contact, address = :address, date_start = :date_start,
            time_start = :time_start, time_end = :time_end, next_days = :next_days, candidates_session_duration = :candidates_session_duration, type = :type, use_google = :use_google, meet_link = :meet_link
            WHERE id = :id";
 
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':contact' => $contact,
            ':address' => $address,
            ':date_start' => $date_start,
            ':time_start' => $time_start,
            ':time_end' => $time_end,
            ':next_days' => $next_days,
            ':candidates_session_duration' => $candidates_session_duration,
            ':type' => $type,
            ':use_google' => $use_google,
            ':meet_link' => $meet_link,
            ':id' => $id
        ]);
 
        return ['type' => 'OK'];
    }

    function getInterview()
    {

        $process_list = $_GET['processList'];
        $sql = "SELECT * FROM process_list_interviews WHERE process_list = ? ORDER BY date_start DESC";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list/*, $user_id*/]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function deleteInterview()
    {

        $id = $_GET['id'];

        $sql = "SELECT * FROM process_list_interviews AS A1
            RIGHT JOIN process_list_lead_interviews AS A2 ON A1.id = A2.interview
            WHERE A1.id = ?
        ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);

        if ($stmt->rowCount() > 0)
            return ['type' => 'FAIL'];

        $sql = "DELETE FROM process_list_interviews WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        return ['type' => 'OK'];
    }

    function finish()
    {

        $id = $_POST['id'];
        $sql = "UPDATE process_list SET status = 'read' WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);

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

    function conclude()
    {

        $user_id = $_POST['userId'];
        $id = $_POST['id'];
        $sql = "UPDATE process_list SET status = 'finished' WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);

        $sql = "SELECT * FROM process_list WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        $process = $stmt->fetch(PDO::FETCH_ASSOC);

        $title = "Processo seletivo \"{$process['name']}\" finalido!";
        $url = "/recruiter/process";
        
        LogEvent::log($user_id, $id, $url, $title, LogEvent::FINISH_PROCESS);

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

    function hireCandidates()
    {

        $candidates = json_decode($_POST['candidates'], true);
        $process_list = $_POST['processList'];

        $sql = "UPDATE process_list_lead SET status = 'hired' WHERE lead IN (?) AND process_list = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            join(', ', $candidates),
            $process_list
        ]);

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

    function saveInterviewSessions()
    {
 
        $sessions = $_POST['sessions'];
        $interview = $_POST['interview'];
        $user_id = $_POST['userId'];
        $process_id = $_POST['processId'];
        $url_meet = !empty($_POST['url']) ? $_POST['url'] : null;
 
        if (empty($sessions) || empty($interview) || empty($user_id) || empty($process_id)) {
            return ['type' => 'ERROR', 'message' => 'All parameters are required'];
        }
 
        $sessions = json_decode($sessions, true);
        if (!is_array($sessions) || empty($sessions)) {
            return ['type' => 'ERROR', 'message' => 'Invalid sessions data'];
        }
 
        try {
 
            parent::$conn->beginTransaction();
 
            $sql = "SELECT name FROM process_list WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_id]);
            $process = $stmt->fetch(PDO::FETCH_ASSOC);
 
            $sql = "INSERT IGNORE INTO process_list_interview_sessions(process_list_interview, lead, date, time_start, time_end, blocked, link_meet)
                VALUES(:process_list_interview, :lead, :date, :time_start, :time_end, :blocked, :link_meet)
                ON DUPLICATE KEY UPDATE time_start = :time_start, time_end = :time_end, blocked = :blocked";
            $candidates_emails = array();
            $insert_id = parent::$conn->lastInsertId();
            $campaign = new QueueCampaign($this->getConnection());
 
            foreach ($sessions as $session) {
 
                $stmt = parent::$conn->prepare($sql);
                $stmt->execute([
                    ':process_list_interview' => $interview,
                    ':lead' => $session['candidate']['id'],
                    ':date' => $session['date'],
                    ':time_start' => $session['timeAt'],
                    ':time_end' => $session['timeEnd'],
                    ':blocked' => ($session['blocked'] ?? false) == 'true',
                    ':link_meet' => $url_meet
                ]);
 
                if ($session['candidate']) {
                    $sqlI = "INSERT IGNORE INTO process_list_lead_interviews(lead, interview) VALUES(?, ?)";
                    $stmt = parent::$conn->prepare($sqlI);
                    if ($stmt->execute([$session['candidate']['id'], $interview])) {
                        $campaign->queue($session['candidate']['id'], 'interview');
                    }
                }
                $date_fmt = date('d/m/Y', strtotime($session['date']));                
                $candidate_name = strtoupper($session['candidate']['firstname'] . ' ' . $session['candidate']['lastname']);
                $title = "Adicionou ${candidate_name} para uma entrevista no processo seletivo \"{$process['name']}\", das $date_fmt às {$session['timeAt']} - {$session['timeEnd']}";
                $url = "/recruiter/process";
                LogEvent::log($user_id, $insert_id, $url, $title, LogEvent::CREATE_INTERVIEW);
 
            }
 
            parent::$conn->commit();        
 
            $candidates_ids = array_map(function ($session) {
                return $session['candidate']['id'];
            }, $sessions);
 
            $this->sendVoucher($candidates_ids);        
 
            return ['type' => 'OK'];
 
        } catch (\Exception $e) {
            parent::$conn->rollBack();
            throw new \Exception($e->getMessage());
        }    
    }

    function getInterviewSessions()
    {

        $interview = $_GET['interview'];
        $sql = "SELECT A1.*, A2.id AS lead_id, firstname, lastname, avatar_profile, accept FROM process_list_interview_sessions AS A1
            LEFT JOIN leads AS A2 ON A1.lead = A2.id
            LEFT JOIN process_list_lead_interviews AS A3 ON A3.interview = A1.process_list_interview AND A3.lead = A2.id
            WHERE process_list_interview = ?
        ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$interview]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        /*var_dump(parent::$conn->errorInfo());
        var_dump($stmt->errorInfo());*/

        return $result;
    }

    function deleteInterviewSession()
    {

        $id = $_GET['id'];
        $process_id = $_GET['processId'];
        $user_id = $_GET['userId'];
        $candidate = $_GET['candidate'];

        if (!isset($_GET['id']) || !isset($_GET['processId']) || !isset($_GET['userId'])) {
            return ['type' => 'ERROR', 'message' => 'Missing required parameters: id, processId, or userId'];
        }
        
        try {
            
            parent::$conn->beginTransaction();            
            $sql = "DELETE FROM process_list_interview_sessions WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$id]);

            $sql = "SELECT name FROM process_list WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$process_id]);
            $process = $stmt->fetch(PDO::FETCH_ASSOC);

            $candidate = json_decode($candidate, true);
            $candidate_name = strtoupper($candidate['firstname'] . ' ' . $candidate['lastname']);
            $title = "Removeu o ${candidate_name} de uma entrevista do processo seletivo \"{$process['name']}\"";
            $url = "/recruiter/process";
            LogEvent::log($user_id, $id, $url, $title, LogEvent::REMOVE_INTERVIEW);

            parent::$conn->commit();

        } catch (\Exception $e) {
            parent::$conn->rollBack();
            throw new \Exception($e->getMessage());
        }

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

    private function sendVoucher($candidates)
    {

        $candidates_ids = implode(', ', $candidates);
        $sql = "SELECT A1.voucher, A4.id FROM companies AS A1 
                LEFT JOIN process_list AS A2 ON A2.company = A1.id
                LEFT JOIN process_list_lead AS A3 ON A3.process_list = A2.id                
                LEFT JOIN leads AS A4 ON A4.id = A3.lead
            WHERE A4.id IN($candidates_ids) AND A1.voucher IS NOT NULL AND COALESCE(A4.signature_status, '') != 'ACTIVE'";

        $stmt = parent::$conn->query($sql);

        if ($stmt->rowCount()) {

            $campaign = new QueueCampaign($this->getConnection());
            $candidates = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($candidates as $candidate) {

                $sql2 = "UPDATE leads SET voucher = ? WHERE id = ?";
                $stmt2 = parent::$conn->prepare($sql2);
                $stmt2->execute([$candidate['voucher'], $candidate['id']]);

                $campaign->queue($candidate['id'], 'voucher');
            }
        }
    }

    function getDisableCandidate()
    {

        $process_list = $_GET['processListId'];
        $offset = $_GET['pageIndex'] ?? 0;
        $limit = $_GET['limit'] ?? 20;

        $sql = "SELECT A1.* FROM leads AS A1 LEFT JOIN process_list_lead AS A2 ON A2.lead = A1.id
            WHERE A2.status = 'disabled' AND process_list = :process_list
            LIMIT :offset, :limit ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':process_list', $process_list);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function getTotalDisableCandidate()
    {

        $process_list = $_GET['processListId'];

        $sql = "SELECT count(*) as total FROM leads AS A1 LEFT JOIN process_list_lead AS A2 ON A2.lead = A1.id
            WHERE A2.status = 'disabled' AND process_list = ? ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_list]);
        $total = $stmt->fetch(PDO::FETCH_ASSOC);

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

    function disableCandidate()
    {

        $disable = $_POST['disable'] == 'true' ? 'disabled' : 'waiting';
        $lead = $_POST['lead'];
        $process_list = $_POST['processListId'];

        $sql = "UPDATE process_list_lead SET status = ? WHERE lead = ? AND process_list = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            $disable,
            $lead,
            $process_list
        ]);
        return ['type' => 'OK'];
    }

    function updateView()
    {

        $id = $_POST['id'];
        $process_id = $_POST['process_id'];
        $view = isset($_POST['view']) ? 'true' : 'false';

        $sql = "UPDATE process_list_lead SET viewprocess = :view WHERE process_list = :process AND lead = :lead";
        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':view', $view);
        $stmt->bindParam(':process', $process_id);
        $stmt->bindParam(':lead', $id);
        $stmt->execute();

        return ['type' => 'OK'];
        $id = $_POST['id'];
        $process_id = $_POST['process_id'];
        $view = isset($_POST['view']) ? 'true' : 'false';

        $sql = "UPDATE process_list_lead SET viewprocess = :view WHERE process_list = :process AND lead = :lead";
        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':view', $view);
        $stmt->bindParam(':process', $process_id);
        $stmt->bindParam(':lead', $id);
        $stmt->execute();

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

    function listView()
    {

        $lead_id = $_POST['id'];
        $sql = "SELECT pl.id, pl.name 
                FROM process_list_lead pll
                JOIN process_list pl ON pll.process_list = pl.id
                WHERE pll.lead = :leadId AND pll.viewprocess = 'false'";

        $stmt = parent::$conn->prepare($sql);
        $stmt->bindParam(':leadId', $lead_id);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function approveCandidate()
    {

        $user_id = $_POST['userId'];
        $id = $_POST['id'];
        $status = $_POST['approve'] == 'true' ? 'pending' : 'disabled';
        $sql = "UPDATE process_list_lead SET status = :status WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':status' => $status,
            ':id' => $id
        ]);

        $sql = "SELECT L.firstname, L.email FROM process_list_lead AS PLL 
            LEFT JOIN leads AS L ON PLL.lead = L.id
            WHERE PLL.id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        $process = $stmt->fetch(PDO::FETCH_ASSOC);

        $name = strtoupper($process['firstname']);
        $email = $process['email'];
        $title = $status = $_POST['approve'] == 'true' 
            ? "Aprovou o candidato \"{$name}\" ({$email}) no processo seletivo"
            : "Reprovou o candidato \"{$name}\" ({$email}) no processo seletivo";
        $url = "/recruiter/process";
        
        LogEvent::log($user_id, $id, $url, $title, LogEvent::APPROVE_PROCESS);

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

    function archive()
    {

        $user_id = $_POST['userId'];
        $id = $_POST['id'];
        $sql = "UPDATE process_list SET status = 'archived' WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);

        // $this->changeCandidatesStatus($id, 'disabled');

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

    function getArchived()
    {

        $white_lb_id = $this->config['WHITE_LABEL_ID'] ?? null;  
        $id = $_POST['id'];
        $sql = "SELECT * FROM process_list WHERE status = 'archived' AND company = ? AND white_label_id = ? ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id, $white_lb_id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function unArchived()
    {
        if (isset($_POST['process_id'])) {
            $ids = json_decode($_POST['process_id']);

            // Verifica se a chave 'process_id' está definida e é um array
            if (is_array($ids)) {
                // Se houver apenas um ID, converte para um array
                if (count($ids) === 1) {
                    $ids = [$ids[0]];
                }

                // Prepara a consulta para verificar o número de registros contratados
                $sqlCount = "SELECT qtd FROM process_list WHERE id = ?";
                $sqlHiredCount = "SELECT COUNT(*) FROM process_list_lead WHERE process_list = ? AND status = 'hired'";

                // Prepara a consulta para atualizar o status
                $sqlUpdate = "UPDATE process_list SET status = ? WHERE id = ?";

                $stmtCount = parent::$conn->prepare($sqlCount);
                $stmtHiredCount = parent::$conn->prepare($sqlHiredCount);
                $stmtUpdate = parent::$conn->prepare($sqlUpdate);

                foreach ($ids as $id) {
                    // Verifica a quantidade de vagas disponíveis
                    $stmtCount->execute([$id]);
                    $qtd = $stmtCount->fetchColumn();

                    // Conta o número de vagas preenchidas
                    $stmtHiredCount->execute([$id]);
                    $hiredCount = $stmtHiredCount->fetchColumn();

                    // Define o status baseado na quantidade de vagas preenchidas
                    $status = ($hiredCount >= $qtd) ? 'finished' : 'read';

                    // Atualiza o status do processo seletivo
                    $stmtUpdate->execute([$status, $id]);
                    // $this->changeCandidatesStatus($id, 'pending');
                }

                // Retorna uma resposta de sucesso
                return ['type' => 'OK'];
            } else {
                // Retorna um erro se nenhum ID válido for fornecido
                return ['type' => 'ERROR', 'message' => 'Nenhum ID válido foi fornecido'];
            }
        }
    }

    function newProcessCompany()
    {

        $name = $_POST['name'] ?? null;
        $viewname = $_POST['viewname'];
        $viewend = $_POST['viewend'];
        $viewtel = $_POST['viewtel'];
        $companyname = $_POST['companyname'] ?? null;
        $cnpj = $_POST['cnpj'];
        $email = $_POST['email'];
        $password = $_POST['password'];
        $firstname = $_POST['firstname'];
        $lastname = $_POST['lastname'];
        $phone = $_POST['phone'] ?? null;
        $zipCode = $_POST['zipCode'];
        $state = $_POST['state'];
        $city = $_POST['city'];
        $district = $_POST['district'];
        $street = $_POST['street'];
        $number = $_POST['number'];
        $complement = $_POST['complement'] ?? null;
        $modalityjob = $_POST['modalityjob'];
        $sinonimo = $_POST['sinonimo'];
        $salary = !empty($_POST['salary']) ? $_POST['salary'] : '0000';
        $coin = $_POST['coin'];
        $workregime = $_POST['workregime'];
        $daysWeek = $_POST['daysWeek'];
        $start_monday = $_POST['start_monday'] ?? null;
        $end_monday = $_POST['end_monday'] ?? null;
        $start_tuesday = $_POST['start_tuesday'] ?? null;
        $end_tuesday = $_POST['end_tuesday'] ?? null;
        $start_thursday = $_POST['start_thursday'] ?? null;
        $end_thursday = $_POST['end_thursday'] ?? null;
        $start_wednesday = $_POST['start_wednesday'] ?? null;
        $end_wednesday = $_POST['end_wednesday'] ?? null;
        $start_friday = $_POST['start_friday'] ?? null;
        $end_friday = $_POST['end_friday'] ?? null;
        $start_saturday = $_POST['start_saturday'] ?? null;
        $end_saturday = $_POST['end_saturday'] ?? null;
        $start_sunday = $_POST['start_sunday'] ?? null;
        $end_sunday = $_POST['end_sunday'] ?? null;
        $benefits = $_POST['benefits'];
        $requirements = $_POST['requirements'];
        $activities = $_POST['activities'];
        $tipocargo = $_POST['tipocargo'] ?? null;
        $qtd = $_POST['qtd'];
        $white_lb_id = $this->config['WHITE_LABEL_ID'] ?? null;

        if ($state) {
            if (is_string($state) && strpos(trim($state), '{') === 0) {
                $decoded = json_decode($state, true);
                if (json_last_error() === JSON_ERROR_NONE && isset($decoded['sigla'])) {
                    $state = $decoded['sigla'];
                }
            }
        }

        $insertCompanyQuery = "INSERT INTO `companies` (owner_id, is_published, date_added, created_by, created_by_user, social_cache, score, companyemail, companyphone, companyname, cnpj, plan_signature, signature_status, signature_status_text, signature_plan_id, white_label_id) VALUES (1, 1, NOW(), 1, 'MEMPREGA Marketing', 'a:0:{}', 0, ?, ?, ?, ?, '192,25', 'TRIAL', 'periodo de teste', 'YEARLY_BUSINESS', ?)";
        $stmt = parent::$conn->prepare($insertCompanyQuery);
        $companyResult = $stmt->execute([$email, $phone, $companyname, $cnpj, $white_lb_id]);

        if (!$companyResult) {
            return ['type' => 'FAIL', 'message' => 'Erro ao inserir na tabela companies: ' . $stmt->errorInfo()];
        }

        $company_id = parent::$conn->lastInsertId();

        $insertLeadQuery = "INSERT INTO `leads` (owner_id, is_published, date_added, points, firstname, lastname, company, email, phone, password, plan_signature, signature_status, signature_status_text, signature_plan_id, account_type, is_admin, white_label_id) VALUES (1, 1, NOW(), 0, ?, ?, ?, ?, ?, ?, '192,25', 'TRIAL', 'periodo de teste', 'YEARLY_BUSINESS', 'company','true', ?)";
        $stmt2 = parent::$conn->prepare($insertLeadQuery);
        $leadResult = $stmt2->execute([$firstname, $lastname, $companyname, $email, $phone, $password, $white_lb_id]);

        if (!$leadResult) {
            return ['type' => 'FAIL', 'message' => 'Erro ao inserir na tabela leads: ' . $stmt2->errorInfo()];
        }

        $lead_id = parent::$conn->lastInsertId();

        $insertCompaniesLeadsQuery = "INSERT INTO `companies_leads` (company_id, lead_id, date_added, is_primary) VALUES (?, ?, NOW(), 1)";
        $stmt3 = parent::$conn->prepare($insertCompaniesLeadsQuery);
        $companyleadResult = $stmt3->execute([$company_id, $lead_id]);

        if (!$companyleadResult) {
            return ['type' => 'FAIL', 'message' => 'Erro ao inserir na tabela companiesleads: ' . $stmt3->errorInfo()];
        }

        $sql = "INSERT INTO process_list(name, company, viewname, viewend, viewtel, companyname, phone, zipCode, state, city, district, street, number, complement, modalityjob, sinonimo, salary, coin, workregime, daysWeek, start_monday, end_monday, start_tuesday, end_tuesday, start_wednesday, end_wednesday, start_thursday, end_thursday, start_friday, end_friday, start_saturday, end_saturday, start_sunday, end_sunday, benefits, requirements, activities, created_by, qtd, tipocargo, white_label_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        $stmt4 = parent::$conn->prepare($sql);
        $stmt4->execute([$name, $company_id, $viewname, $viewend, $viewtel, $companyname, $phone, $zipCode, $state, $city, $district, $street, $number, $complement, $modalityjob, $sinonimo, $salary, $coin, $workregime, $daysWeek, $start_monday, $end_monday, $start_tuesday, $end_tuesday, $start_wednesday, $end_wednesday, $start_thursday, $end_thursday, $start_friday, $end_friday, $start_saturday, $end_saturday, $start_sunday, $end_sunday, $benefits, $requirements, $activities, $lead_id, $qtd, $tipocargo, $white_lb_id]);

        if ($stmt->errorCode() != '00000')
            throw new \Exception(json_encode($stmt4->errorInfo()));

        $insert_id = parent::$conn->lastInsertId();

        $sql5 = "INSERT INTO process_list_user(process_list_id, lead_id) VALUES (?,?)";
        $stmt5 = parent::$conn->prepare($sql5);
        $stmt5->execute([$insert_id, $lead_id]);

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

    function getProcessLead()
    {

        $id = $_POST['id'];
        $sql = "SELECT process_list FROM process_list_lead WHERE status not in ('pre-approval','hired','disabled') AND lead = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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


    function saveProcessName() {
    
        // Validate required parameters
        if (empty($_POST['name']) || empty($_POST['id'])) {
            return ['type' => 'ERROR', 'message' => 'Parâmetros obrigatórios ausentes: name ou id'];
        }

        $name = $_POST['name'];
        $process_id = $_POST['id'];
    
        $sql = "UPDATE process_list SET name = :name WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':name' => $name,
            ':id' => $process_id
        ]);
    
        if ($stmt->rowCount() > 0) {
            return ['type' => 'OK'];
        } else {
            return ['type' => 'FAIL', 'message' => 'Nenhuma alteração realizada'];
        }
    }

    private function removeSuggestedCandidate($process_id, $candidate_id) {

        $sql = "SELECT * FROM process_list_lead_suggestion WHERE process_id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$process_id]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $suggest_id = null;

        foreach($results as $item) {            
            $candidate = json_decode($item['data'], true);
            
            if($candidate_id == $candidate['id']) {
                $suggest_id = $item['id'];
                break;
            }
        }

        // $sql = "DELETE FROM process_list_lead_suggestion WHERE id = ?";
        $sql = "UPDATE process_list_lead_suggestion SET removedAt = NOW() WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$suggest_id]);

    }

    function getProcessListID() {
            
        $process_id = $_GET['id'];
        $company_id = $_GET['company'];
        $sql = "SELECT PL.*, C.* FROM process_list AS PL
                LEFT JOIN companies AS C ON C.id = PL.company
                WHERE PL.id = :id AND PL.company = :company";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            'company' => $company_id,
            'id' => $process_id
        ]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    }

    private function changeCandidatesStatus($process_id, $status) {
        $sql = "UPDATE process_list_lead SET status = :status WHERE process_list = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':status' => $status,
            ':id' => $process_id
        ]);
    }

    function getProcessQuota() {

        $company_id = $_GET['companyId'];

        $sql = "SELECT date_added, signature_status FROM companies WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$company_id]);
        $company = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$company) {
            return ['type' => 'OK', 'data' => false];
        }

        $dateAdded = new DateTime($company['date_added']);
        $cutoffDate = new DateTime('2025-07-30');

        if ($dateAdded >= $cutoffDate) {
            $sql = "SELECT COUNT(*) FROM process_list WHERE company = ? AND status != 'archived'";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$company_id]);
            $total = (int) $stmt->fetchColumn();
            
            return [
                'type' => 'OK', 
                'data' => $company['signature_status'] === 'TRIAL' && $total > 0
            ];
        }
        // 'A sua conta esta em modo de TRIAL e só pode ter um processo ativo. Por favor, escolha um plano para poder criar mais processos seletivos'
        return ['type' => 'OK', 'data' => false];
    }

    function listLegendCretiveProcess() {
        // Validate required parameters
        if (empty($_GET['id'])) {
            return ['type' => 'ERROR', 'message' => 'Parâmetros obrigatórios ausentes: id'];
        }
 
        $process_id = $_GET['id'];
        $sql = "SELECT legend_creative FROM process_list WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':id' => $process_id
        ]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result && isset($result['legend_creative'])) {
            return ['type' => 'OK', 'data' => $result['legend_creative']];
        } else {
            return ['type' => 'FAIL', 'data' => null];
        }
    }

    function legendCretiveProcess() {
        // Validate required parameters
        if (empty($_POST['legend']) || empty($_POST['id'])) {
            return ['type' => 'ERROR', 'message' => 'Parâmetros obrigatórios ausentes: legenda ou id'];
        }
 
        $legend = $_POST['legend'];
        $process_id = $_POST['id'];
        $sql = "UPDATE process_list SET legend_creative = :legend WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':legend' => $legend,
            ':id' => $process_id
        ]);
        if ($stmt->rowCount() > 0) {
            return ['type' => 'OK'];
        } else {
            return ['type' => 'FAIL', 'message' => 'Nenhuma alteração realizada'];
        }
    }

    function getCountProcess() {

        $company_id = $_GET['companyId']; 
        $sql = "SELECT COUNT(*) FROM process_list WHERE company = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$company_id]);
        $total = (int) $stmt->fetchColumn();

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

    }
    
    function getPermission(){
 
        $id = $_GET['id'];
 
        $sql = "SELECT P.*,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status NOT IN ('pre-approval', 'disabled')) AS candidates,
                    (SELECT count(*) FROM process_list_lead AS PLL WHERE PLL.process_list = P.id AND status = 'pending') AS candidatesPending,
                    CASE 
                        WHEN NOT EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id ) THEN 1 
                        WHEN EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id AND CPL.status <> 'finish' ) THEN 0 
                        ELSE 1 END AS boost_process_list,
                    CASE WHEN EXISTS ( SELECT 1 FROM campaign_process_list CPL WHERE CPL.process_list = P.id AND CPL.status IN ('new','active') ) THEN 1 ELSE 0 END AS filter_process_list    
                FROM process_list AS P WHERE P.id = ? AND P.status <> 'archived' ORDER BY P.created_at DESC
            ";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return ['type' => 'OK', 'data' => $result];
    }

    public function addToDefaultProcessStep($leads = null, $process_id = null) {
       
        if (isset($_POST['process_id'])) {
            $process_id = $_POST['process_id'];
        }
 
        $sql = "SELECT id FROM kanban_dashboard WHERE process_id = :process_id AND `default` = 1";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            ':process_id' => $process_id
        ]);
       
        if(isset($_POST['plan'])){
            $sql = "INSERT INTO kanban_dashboard (process_id, name, `order`, `default`)
                VALUES
               (:p1, 'Candidatos selecionados', -1, 1),
               (:p2, 'Follow-up', 1, -1),
               (:p3, 'Testes e Entrevistas', 2, -1),
               (:p4, 'Contração', 3, -1)";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':p1' => $process_id,
                ':p2' => $process_id,
                ':p3' => $process_id,
                ':p4' => $process_id
            ]);
        }else{
       
            if ($stmt->rowCount() === 0) {
                $sql = "INSERT INTO kanban_dashboard(process_id, name, `order`, `default`)
                    VALUES(:process_id, 'Candidatos selecionados', -1, 1)";
                $stmt = parent::$conn->prepare($sql);
                $stmt->execute([':process_id' => $process_id]);
                $kanban_id = parent::$conn->lastInsertId();
            } else {
                $kanban_id = $stmt->fetchColumn();
            }
       
            if(!empty($leads)){
                $sql = "UPDATE process_list_lead SET kanban_step_id = :step_id  WHERE lead = :lead AND process_list = :process_list";
       
                foreach($leads as $lead) {
                   
                    $stmt = parent::$conn->prepare($sql);
                    $stmt->execute([
                        ':step_id' => $kanban_id,          
                        ':lead' => $lead['id'],
                        ':process_list' => $process_id
                    ]);
       
                }
            }
       
        }
       
        return ['type' => 'OK'];
 
    }

    public function approveProcessList() {

        $id = $_POST['processId'];
        $sql = "UPDATE process_list SET status = 'read' WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    }

    function getProcessStats() {
        $company_id = $_GET['companyId'];
        $limitPerMonth = (int) $_GET['limit']; // Ex: 5 por mês
        $year = date('Y');
   
        //Total criado no ano atual
        $sqlTotalYear = "SELECT COUNT(*) FROM process_list
                         WHERE company = ? AND YEAR(created_at) = ?";
        $stmt = parent::$conn->prepare($sqlTotalYear);
        $stmt->execute([$company_id, $year]);
        $totalYear = (int) $stmt->fetchColumn();
   
        //Total criado no mês atual
        $month = date('n'); // número do mês (1–12)
        $sqlMonth = "SELECT COUNT(*) FROM process_list
                     WHERE company = ? AND YEAR(created_at) = ? AND MONTH(created_at) = ?";
        $stmt = parent::$conn->prepare($sqlMonth);
        $stmt->execute([$company_id, $year, $month]);
        $totalMonth = (int) $stmt->fetchColumn();
   
        // Limite anual fixo
        $limitYear = $limitPerMonth * 12;
   
        // Verifica o uso por mês
        $sqlUsedByMonth = "SELECT MONTH(created_at) as mes, COUNT(*) as total
                           FROM process_list
                           WHERE company = ? AND YEAR(created_at) = ?
                           GROUP BY MONTH(created_at)";
        $stmt = parent::$conn->prepare($sqlUsedByMonth);
        $stmt->execute([$company_id, $year]);
        $usedByMonth = $stmt->fetchAll(PDO::FETCH_ASSOC);
   
        $monthsPassed = (int)date('n'); // Ex: outubro = 10
        $maxAvailableUntilNow = $limitPerMonth * $monthsPassed; // Ex: 5 * 10 = 50
   
        $usedTotal = array_sum(array_column($usedByMonth, 'total'));
   
        $balance = $maxAvailableUntilNow - $usedTotal;
   
        $limitThisMonth = $limitPerMonth + max($balance, 0);
        $limitThisMonth = min($limitThisMonth, $limitYear - $totalYear);
   
        $canCreate = ($totalYear < $limitYear) && ($totalMonth < $limitThisMonth);
   
        return [
            'type' => 'OK',
            'data' => [
                'totalYear' => $totalYear,
                'totalMonth' => $totalMonth,
                'limitPerMonth' => $limitPerMonth,
                'limitYear' => $limitYear,
                'limitThisMonth' => $limitThisMonth,
                'canCreate' => $canCreate
            ]
        ];
    }

}
