<?php

class NewJob extends Core
{

    public function save()
    {

        $company_id = $_POST['companyId'];
        $cbo_sinonimo = $_POST['cboSinonimo'] ?? null;
        $job_title = trim($_POST['jobTitle']);

        list($cbo, $sinonimo) = is_null($cbo_sinonimo) ? [null, null] : explode(':', $cbo_sinonimo);

        $sql = "INSERT INTO new_jobs_title(cbo, sinonimo, job_title, company_id) VALUES(?, ?, ?, ?)";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([
            $cbo,
            $sinonimo,
            $job_title,
            $company_id
        ]);
        $last_id = parent::$conn->lastInsertId();

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

    public function update()
    {

        $id = $_POST['id'];
        $cbo_sinonimo = $_POST['cboSinonimo'];
        $job_title = $_POST['jobTitle'];
        $replace_job_id = $_POST['replaceJobId'] ?? null;
        if (is_null($replace_job_id)) {

            list($cbo, $sinonimo) = explode(':', $cbo_sinonimo);
            $sql = "UPDATE new_jobs_title SET cbo = :cbo, sinonimo = :sinonimo, job_title = :job_title, active = 1 WHERE id = :id";
            $stmt = parent::$conn->prepare($sql);
            $stmt->execute([
                ':cbo' => $cbo,
                ':sinonimo' => $sinonimo,
                ':job_title' => $job_title,
                ':id' => $id,
            ]);
        } else {

            $sql_job = "SELECT id, job_title FROM new_jobs_title WHERE id = ?";
            $stmt = parent::$conn->prepare($sql_job);
            $stmt->execute([$replace_job_id]);
            $new_job_title = $stmt->fetch(PDO::FETCH_ASSOC);
            $sql_process = "UPDATE process_list SET sinonimo = :job_title WHERE new_job_id = :new_job_id";
            $stmt = parent::$conn->prepare($sql_process);

            $stmt->execute([
                ':job_title' => $new_job_title['job_title'],
                ':new_job_id' => $id
            ]);

            $sql_delete_job = "DELETE FROM new_jobs_title WHERE id = ?";
            $stmt = parent::$conn->prepare($sql_delete_job);
            $stmt->execute([$id]);
        }

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

    public function listCount()
    {
        $company_id = $_GET['companyId'];
        $sql = "SELECT company_root FROM companies WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $company_id]);
        $company = $stmt->fetch(PDO::FETCH_ASSOC);
        $sql = "SELECT count(*) as records FROM new_jobs_title";

        if ($company['company_root'] != 1)
            $sql .= " WHERE company_id = " . parent::$conn->quote($company_id);

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

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

    public function list()
    {

        $page_index = $_GET['pageIndex'] ?? 0;
        $limit = 20;
        $company_id = $_GET['companyId'];
        $sql = "SELECT company_root FROM companies WHERE id = :id";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([':id' => $company_id]);
        $company = $stmt->fetch(PDO::FETCH_ASSOC);
        $sql = "SELECT * FROM new_jobs_title";

        $offset = intval($page_index) * $limit;

        if ($company['company_root'] != 1)
            $sql .= " WHERE company_id = " . parent::$conn->quote($company_id);

        $sql .= " ORDER BY created_at DESC LIMIT $offset, $limit";

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

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

    public function listAll()
    {

        // $sql = "SELECT * FROM new_jobs_title WHERE active = 1 ORDER BY job_title";
        $sql = "SELECT * FROM new_jobs_title ORDER BY job_title";
        $stmt = parent::$conn->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

    public function delete()
    {

        $id = $_GET['id'];
        $sql = "DELETE FROM new_jobs_title WHERE id = ?";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$id]);

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

    public function approve()
    {

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

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

    public function listJobsByCBO()
    {

        $cbo = $_GET['cbo'];
        $sql = "SELECT * FROM new_jobs_title WHERE cbo = ? AND active = 1";
        $stmt = parent::$conn->prepare($sql);
        $stmt->execute([$cbo]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

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