<?php

class MauticProfessionalExperience extends Core
{

    function store()
    {

        $vacancies = isset($_POST['vacancies']) ? json_decode($_POST['vacancies'], true) : null;
        $email = $_POST['email'];
        $firstJob = isset($_POST['firstJob']) ? $_POST['firstJob'] : null;
        $pcd_description = isset($_POST['pcdDescription']) ? $_POST['pcdDescription'] : null;
        $isPcd = isset($_POST['isPcd']) ? $_POST['isPcd'] : null;
        
        $sql = "SELECT id FROM leads WHERE email = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$email]);
        $lead = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$lead) {
            return ['type' => 'ERROR', 'message' => 'Lead not found for the provided email.'];
        }

        if ($vacancies) {

            $sql = "INSERT INTO professional_experiences(lead_id, organization, professional_name, branch, role_level, 
                    specialization_name, init_date, final_date, description, salary, office, ocupacao, sinonimo, workregime, modalityjob) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = parent::$conn->prepare($sql);

            foreach ($vacancies as $vacancy) {

                $stmt->execute([
                    $lead['id'],
                    $vacancy['company'],
                    $vacancy['vacancy'],
                    $vacancy['branch'],
                    $vacancy['roleLevel'],
                    $vacancy['specializationName'],
                    $vacancy['admissionDate'],
                    $vacancy['currentJob'] == 'true' ? null : $vacancy['departureDate'],
                    $vacancy['description'],
                    $vacancy['salary'],
                    $vacancy['office'],
                    $vacancy['ocupacao'],
                    $vacancy['sinonimo'],
                    $vacancy['workregime'],
                    $vacancy['modalityjob']
                ]);
            }
        }

        if ($firstJob) {

            $sql = "UPDATE leads SET first_job = 'true' WHERE email = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$email]);
        }

        if ($isPcd) {
            $sql = "UPDATE leads SET is_pcd = 'true' WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([$lead['id']]);
        }

        if ($pcd_description) {

            $sql = "UPDATE leads SET pcd_description = ? WHERE id = ?";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                $pcd_description,
                $lead['id']
            ]);
        }

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

    function list()
    {
        $email = $_GET['email'];
        $sql = "SELECT id FROM leads WHERE email = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$email]);
        $lead = $stmt->fetch(PDO::FETCH_ASSOC);

        $sql = "SELECT * FROM professional_experiences WHERE lead_id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $lead['id']]);
        $experiences = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    function delete()
    {

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

        $sql = 'DELETE FROM professional_experiences WHERE id = :id AND lead_id = :lead_id';
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $id, ':lead_id' => $lead['id']]);

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

    function update()
    {

        $vacancies = isset($_POST['vacancies']) ? json_decode($_POST['vacancies'], true) : null;
        $email = $_POST['email'];

        $sql = "SELECT id FROM leads WHERE email = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$email]);
        $lead = $stmt->fetch(PDO::FETCH_ASSOC);
        $sql = "UPDATE professional_experiences SET organization = :organization, description = :description,
            sinonimo = :sinonimo, init_date = :init_date, final_date = :final_date, salary = :salary,
            workregime = :workregime, modalityjob = :modalityjob
            WHERE id = :id AND lead_id = :lead_id";

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

        foreach ($vacancies as $vacancy) {

            $stmt->execute([
                ':organization' => $vacancy['company'],
                ':description' => $vacancy['description'],
                ':sinonimo' => $vacancy['sinonimo'],
                ':init_date' => $vacancy['admissionDate'],
                ':final_date' => $vacancy['departureDate'],
                ':salary' => $vacancy['salary'],
                ':workregime' => $vacancy['workregime'],
                ':modalityjob' => $vacancy['modalityjob'],
                ':id' => $vacancy['id'],
                ':lead_id' => $lead['id']
            ]);
        }

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