<?php
/* 
Copyright 2025 Flávio Ribeiro

This file is part of OCOMON.

OCOMON is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 3 of the License, or
(at your option) any later version.

OCOMON is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Foobar; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/




/**
 * getTechLevels
 * Retorna a listagem de níveis técnicos cadastrados ou um nível em específico
 * @param \PDO $conn
 * @param int|null $id
 * 
 * @return array
 */
function getTechLevels(\PDO $conn, ?int $id = null) :array
{
    $terms = "";
    if ($id !== null) {
        $terms = "AND tl.id = :id ";
    }
    $sql = "SELECT
                tl.id, tl.name, tl.description,
                tp.base_price, tp.hour_price_divisor, tp.hour_price,
                tp.unit_price_divisor, tp.unit_price,
                tp.vigence_since, tp.vigence_until
            FROM 
                tech_levels tl,
                tech_prices tp
            WHERE
                tp.tech_level_id = tl.id AND
                tp.id = (SELECT MAX(id) FROM tech_prices WHERE tech_level_id = tl.id) 
                {$terms}
            ORDER BY tl.name
    ";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id, PDO::PARAM_INT);
        }
        $res->execute();
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    } catch (\PDOException $e) {
        echo $e->getMessage();
        return [];
    }
}

/**
 * getTaskCurrentPrice
 * Retorna o preço corrente de um dado tipo de solicitação com base
 *  no nível técnico e peso do tipo de solicitação informado
 *
 * @param \PDO $conn
 * @param int $task_id
 * @param bool|null $brl - Se for true, retorna o valor em BRL
 * 
 * @return string
 */
function getTaskCurrentPrice (\PDO $conn, int $task_id, ?bool $brl = false): ?string
{

    $sql = "SELECT 
                tp.unit_price * p.weight AS task_price
            FROM
                problemas p,
                tech_prices tp
            WHERE
                tp.tech_level_id = p.tech_level AND
                tp.id = (SELECT MAX(id) FROM tech_prices WHERE tech_level_id = p.tech_level) AND
                p.prob_id = :task_id
            ";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':task_id', $task_id, \PDO::PARAM_INT);
        $res->execute();
        $row = $res->fetch();

        if ($brl) {
            return number_format((!empty($row['task_price']) ? $row['task_price'] : 0), 2, ",", ".");
        }
        return $row['task_price'];
    } catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}


/**
 * getTicketPrice
 * Retorna o preço de um ticket com base no peso e nível técnico no tipo de solicitação do ticket;
 * Considera o preço vigente na data de abertura.
 * Para chamados anteriores à primeira vigência de preços, retorna o primeiro preço da série histórica relacionada;
 * @param \PDO $conn
 * @param int $ticket
 * @param bool|null $brl
 * 
 * @return string|null
 */
function getTicketPrice (\PDO $conn, int $ticket, ?bool $brl = false): ?string
{
    $sql = "SELECT 
                tp.unit_price * p.weight AS ticket_price
            FROM
                ocorrencias o,
                problemas p,
                tech_prices tp
            WHERE
                o.problema = p.prob_id AND
                tp.tech_level_id = p.tech_level AND
                o.numero = :ticket AND
                tp.id = (
                    SELECT 
                        COALESCE(
                            (
                                SELECT MAX(id) 
                                FROM tech_prices 
                                WHERE tech_level_id = p.tech_level AND
                                    vigence_since <= o.data_abertura
                            ),
                            (
                                SELECT MIN(id) 
                                FROM tech_prices 
                                WHERE tech_level_id = p.tech_level
                            )
                        )
                )
            ";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':ticket', $ticket, \PDO::PARAM_INT);
        $res->execute();
        $row = $res->fetch();
        if ($brl) {
            return number_format((!empty($row['ticket_price']) ? $row['ticket_price'] : 0), 2, ",", ".");
        }
        return $row['ticket_price'];
    } catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}


/**
 * getMailTemplates
 * Retorna uma lista das informações de templates de email ou de um template específico
 *
 * @param \PDO $conn
 * @param int|null $id
 * @param int|null $context
 * 
 * @return array|null
 */
function getMailTemplates(\PDO $conn, ?int $id = null, ?int $context = null): ?array
{
    $terms = " 1 = 1 ";
    if (!empty($id)) {
        $terms = "tpl_cod = :id";
    } elseif (!empty($context)) {
        $terms = "context = :context";
    }
    $sql = "SELECT 
                * 
            FROM 
                mail_templates

            WHERE
                {$terms}

            ORDER BY 
                tpl_sigla
    ";
    try {
        $res = $conn->prepare($sql);
        if (!empty($id)) {
            $res->bindParam(':id', $id, \PDO::PARAM_INT);
        } elseif (!empty($context)) {
            $res->bindParam(':context', $context, \PDO::PARAM_INT);
        }
        $res->execute();

        if (!$res->rowCount()) {
            return null;
        }

        if ($id) {
            return $res->fetch();
        }
        return $res->fetchAll();
    } catch (\PDOException $e) {
        echo $e->getMessage();
        return null;
    }
}


/**
 * setUserDisabled
 * Desabilita um usuário, desvincula os ativos e altera o departamento dos ativos (caso existam)
 *
 * @param \PDO $conn
 * @param int $user_id
 * @param int $author
 * 
 * @return bool
 */
function setUserDisabled(\PDO $conn, int $user_id, int $author): bool
{
    $newAssetsDepartment = getConfigValue($conn, 'ASSETS_AUTO_DEPARTMENT');
    
    $sql = "UPDATE
                usuarios
            SET
                `nivel` = 5
            WHERE
                user_id = :user_id
    ";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':user_id', $user_id, \PDO::PARAM_INT);
        $res->execute();

        $updateAssets = updateAssetsTookOffUser($conn, $user_id, $author, $newAssetsDepartment);
        
        return $updateAssets;
    } catch (\PDOException $e) {
        echo $e->getMessage();
        return false;
    }
}


/**
 * getLastUserTermSignedInfo
 * Retorna as informações do último termo assinado do usuário, caso exista;
 *
 * @param \PDO $conn
 * @param int $user_id
 * 
 * @return array|null
 */
function getLastUserTermSignedInfo(\PDO $conn, int $user_id, ?array $fields = []): ?array
{
    if (empty($fields)) {
        $fields = "*";
    } else {
        $fields = implode(", ", $fields);
    }
    
    
    $sql = "SELECT 
                {$fields}
            FROM 
                `users_x_files` 
            WHERE 
                user_id = :user_id AND 
                signed_at = (SELECT MAX(signed_at) FROM users_x_files WHERE user_id = :user_id) 
    ";

    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':user_id', $user_id);
        $res->execute();
        if ($res->rowCount()) {
            return $res->fetch();
        }
        return null;
    } catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}


/**
 * actionFlagHasDone
 * Checa se uma action_flag_id ja foi realizada. 
 * Pois em alguns casos uma mesma action_flag_id pode estar atribuída a message_ids diferentes
 *
 * @param \PDO $conn
 * @param string $message_id (id da mensagem)
 * 
 * @return bool
 */
function actionFlagHasDone(\PDO $conn, string $message_id): bool
{

    $message_id = htmlspecialchars($message_id, ENT_QUOTES);
    
    $sql = "SELECT action_flag_id FROM emails_sent_actions WHERE message_id = :message_id";

    $row = [];
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':message_id', $message_id, PDO::PARAM_STR);
        $res->execute();
        if ($res->rowCount()) {
            $row = $res->fetch();
        }
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return true;
    }

    $sql = "SELECT action_flag_id FROM emails_sent_actions WHERE action_flag_id = :action_flag_id AND action_done_at IS NOT NULL";

    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':action_flag_id', $row['action_flag_id'], PDO::PARAM_STR);
        $res->execute();
        if ($res->rowCount()) {
            return true;
        }
        return false;
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return true;
    }
}


/**
 * hasPendingActionFromMessageId
 * Retorna a listagem de ações pendentes sobre a mensagem informada, caso existam
 * @param \PDO $conn
 * @param string $message_id (id da mensagem: já tratato com htmlspecialchars)
 * 
 * @return array|null
 */
function hasPendingActionFromMessageId (\PDO $conn, string $message_id): ?array
{
    
    $message_id = htmlspecialchars($message_id);

    $sql = "SELECT * FROM emails_sent_actions WHERE message_id = :message_id AND action_done_at IS NULL";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':message_id', $message_id, PDO::PARAM_STR);
        $res->execute();
        if ($res->rowCount()) {
            $row = $res->fetchAll();
            return $row;
        }
        return null;
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}

/**
 * hasPendingActionFromFlag
 * Retorna o id da ação pendente sobre a mensagem informada, caso exista
 * @param \PDO $conn
 * @param string $message_id (id da mensagem: NÃO tratato com htmlspecialchars)
 * @param string $flag
 * 
 * @return int|null
 */
function hasPendingActionFromFlag(\PDO $conn, string $message_id, string $flag): ?int
{
    
    if (actionFlagHasDone($conn, $message_id)) {
        return null;
    }
    
    $findPending = hasPendingActionFromMessageId($conn, $message_id);
    if (empty($findPending)) {
        return null;
    }
    foreach ($findPending as $key => $value) {
        if ($value['action_flag'] == $flag) {
            return $value['id'];
        }
    }
    return null;
}

function setSentActionDone (\PDO $conn, int $id): bool 
{
    $sql = "UPDATE emails_sent_actions SET action_done_at = NOW() WHERE id = :id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':id', $id, PDO::PARAM_STR);
        $res->execute();
        return true;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}




function getMainAreaNameFromUserId (\PDO $conn, int $userId): ?string
{
    $sql = "SELECT 
                s.sistema
            FROM 
                sistemas s,
                usuarios u
            WHERE
                s.sis_id = u.AREA AND
                u.user_id = :user_id
            ";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':user_id', $userId);
        $res->execute();

        if ($res->rowCount()) {
            return $res->fetch()['sistema'];
        }
        return null;
    } catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}





function getContractors (\PDO $conn, ?int $id = null, array $active = [0,1]): array
{
    $terms = "";
    $terms = ($id ? " WHERE id = :id " : '');
    
    if (!$id && !empty($active)) {

        $active = array_map('intval', $active);

        $terms .= ($terms ? " AND " : " WHERE ") . "active IN (".implode(',', $active).")";
    }


    $sql = "SELECT * FROM contractors {$terms} ORDER BY company_name, contact_name";
    try {
        $res = $conn->prepare($sql);
        if ($id) {
            $res->bindParam(':id', $id); 
        }
        $res->execute();
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        return [];
    }
}




function getInvoiceModels(\PDO $conn, ?int $id = null): array
{

    $terms = "";

    if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

    $sql = "SELECT
                * 
            FROM 
                invoice_models
                {$terms} 
            ORDER BY
                name    
            ";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        return [];
    }
}


function getInvoicesFromContractor($conn, int $contractorId): array
{
    $sql = "SELECT 
                i.*, f.id as file_id, f.* 
            FROM 
                invoices i, 
                invoice_files f
            WHERE 
                i.id = f.invoice_id AND
                i.contractor_id = :contractor_id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':contractor_id', $contractorId);
        $res->execute();
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return [];
    } catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
    return [];
}



function getContractorsOrganizations (\PDO $conn, ?int $id = null): array
{
	$terms = "";
	if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

	$sql = "SELECT * FROM 
				`contractors_organizations` 
				{$terms}
			ORDER BY
				name	
	";

	try {
		$res = $conn->prepare($sql);
		if ($id !== null) {
			$res->bindParam(':id', $id);
		}
		$res->execute();
		
		if ($res->rowCount()) {
			foreach ($res->fetchAll() as $row) {
				$data[] = $row;
			}
			if ($id)
				return $data[0];
			return $data;
		}
		return [];
	}
	catch (Exception $e) {
		return [];
	}

}


function getContractorsServicesTypes (\PDO $conn, ?int $id = null): array
{
    $terms = "";
    if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

    $sql = "SELECT * FROM `contractors_services_types` {$terms} ORDER BY name";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        return [];
    }
}


function getLastInvoiceNumber(\PDO $conn): ?int
{
    $sql = "SELECT invoice_number FROM invoices WHERE id = (SELECT MAX(id) FROM invoices)";
    try {
        $res = $conn->prepare($sql);
        $res->execute();
        if ($res->rowCount()) {
            $row = $res->fetch();
            return $row['invoice_number'];
        }
        return null;
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}


function getIssuesTypesToWatchFromUser(\PDO $conn, int $userId): array
{
    $sql = "SELECT * FROM users_x_issues_types_watch WHERE user_id = :user_id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':user_id', $userId);
        $res->execute();
        if ($res->rowCount()) {
            return $res->fetchAll();
        }
        return [];
    }
    catch (Exception $e) {
        return [];
    }
}


/**
 * getUsersFromIssueType
 * Retorna a lista de usuários que recebem notificações sobre chamados de um tipo de problema
 * Apenas usuários ativos
 * @param \PDO $conn
 * @param int $issueTypeId - id do tipo de problema
 * 
 * @return array - lista de usu rios com permiss o de assistir o tipo de problema
 */
function getUsersFromIssueType(\PDO $conn, int $issueTypeId): array
{
    $sql = "SELECT uxi.* FROM 
                users_x_issues_types_watch uxi, usuarios u 
            WHERE 
                uxi.user_id = u.user_id AND 
                u.nivel < 4 AND
                issue_type_id = :issue_type_id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':issue_type_id', $issueTypeId);
        $res->execute();
        if ($res->rowCount()) {
            return $res->fetchAll();
        }
        return [];
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
}




function getInvoiceById($conn, int $id): array
{
    $sql = "SELECT 
                i.*, c.*, f.id as file_id, f.* 
            FROM 
                invoices i, 
                contractors c,
                invoice_files f
            WHERE 
                i.id = f.invoice_id AND
                i.id = :id AND
                i.contractor_id = c.id
                ";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':id', $id);
        $res->execute();
        if ($res->rowCount()) {
            // foreach ($res->fetchAll() as $row) {
            //     $data[] = $row;
            // }
            // return $data;
            return $res->fetch();
        }
        return [];
    } catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
    return [];
}


function setInvoiceSentToContractor (\PDO $conn, int $id): bool 
{
    $sql = "UPDATE invoices SET sent_to_contractor = 1 WHERE id = :id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':id', $id, PDO::PARAM_INT);
        $res->execute();
        return true;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}


function setInvoiceSentToPaying(\PDO $conn, int $id): bool
{
    $sql = "UPDATE invoices SET sent_to_paying = 1 WHERE id = :id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':id', $id, PDO::PARAM_INT);
        $res->execute();
        return true;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}


/**
 * Grava, na tabela de fila de arquivos par envio em email, um registro com o relacionamento entre o email e o arquivo a ser enviado
 * 
 * @param \PDO $conn
 * @param int $fileId
 * @param int $mailQueueId
 * @param array $tableFileInfo
 * 
 * @return bool
 */
function setFileToMailQueue(\PDO $conn, int $fileId, int $mailQueueId, array $tableFileInfo): bool
{
    $defaultTableFileInfo = [
        'file_table' => 'invoice_files',
        'file_id_col' => 'id',
        'file_bin_col' => 'file',
        'file_name_col' => 'filename'
    ];

    $tableFileInfo = array_merge($defaultTableFileInfo, $tableFileInfo);

    $sql = "INSERT INTO mail_queue_files 
            (
                mail_queue_id,
                file_id,
                file_table,
                file_id_col,
                file_bin_col,
                file_name_col
            )
            VALUES (
                :mail_queue_id,
                :file_id,
                :file_table,
                :file_id_col,
                :file_bin_col,
                :file_name_col
            )
            ";

    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':mail_queue_id', $mailQueueId, PDO::PARAM_INT);
        $res->bindParam(':file_id', $fileId, PDO::PARAM_INT);
        $res->bindParam(':file_table', $tableFileInfo['file_table'], PDO::PARAM_STR);
        $res->bindParam(':file_id_col', $tableFileInfo['file_id_col'], PDO::PARAM_STR);
        $res->bindParam(':file_bin_col', $tableFileInfo['file_bin_col'], PDO::PARAM_STR);
        $res->bindParam(':file_name_col', $tableFileInfo['file_name_col'], PDO::PARAM_STR);
        $res->execute();
        return true;
    } catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}



function getMailingLists(\PDO $conn, ?int $listId = null): array
{
    $terms = "";
    if ($listId !== null) {
        $terms = " WHERE ml_cod = :id "; 
    }

    $sql = "SELECT * FROM mail_list {$terms} ORDER BY ml_sigla";
    try {
        $res = $conn->prepare($sql);
        if ($listId !== null) {
            $res->bindParam(':id', $listId);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($listId)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
    
}





function getImpactFactors(\PDO $conn, ?int $id = null): array
{
    $terms = "";
    if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

    $sql = "SELECT * FROM impact_factors {$terms} ORDER BY `name`";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
}




function getComplexityFactors(\PDO $conn, ?int $id = null): array
{
    $terms = "";
    if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

    $sql = "SELECT * FROM complexity_factors {$terms} ORDER BY `name`";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
}



function getDeflatorFactors(\PDO $conn, ?int $id = null): array
{
    $terms = "";
    if ($id !== null) {
        $terms = " WHERE id = :id "; 
    }

    $sql = "SELECT * FROM deflator_factors {$terms} ORDER BY `name`";
    try {
        $res = $conn->prepare($sql);
        if ($id !== null) {
            $res->bindParam(':id', $id);
        }
        $res->execute();
        
        if ($res->rowCount()) {
            foreach ($res->fetchAll() as $row) {
                $data[] = $row;
            }
            if ($id)
                return $data[0];
            return $data;
        }
        return [];
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return [];
    }
}




function getTicketWeight(\PDO $conn, int $ticket): ?float
{
    
    $issueWeight = 1;
    $complexityFactor = 1;
    $impactFactor = 1;
    $deflatorFactor = 1;
    
    $sql = "SELECT problema, complexity_factor, impact_factor, deflator_factor FROM ocorrencias WHERE numero = :ticket";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':ticket', $ticket);
        $res->execute();
        if ($res->rowCount()) {
            $ticketData = $res->fetch();
        } else {
            return null;
        }
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }

    if (empty($ticketData['problema'])) {
        return null;
    }


    $sql = "SELECT `weight` FROM problemas WHERE prob_id = :prob_id AND `weight` IS NOT NULL";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':prob_id', $ticketData['problema']);
        $res->execute();
        if ($res->rowCount()) {
            $issueWeight = $res->fetch()['weight'];
        }
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }

    if (!empty($ticketData['complexity_factor'])) {
        $complexityFactor = getComplexityFactors($conn, $ticketData['complexity_factor'])['factor'];
    }

    if (!empty($ticketData['impact_factor'])) {
        $impactFactor = getImpactFactors($conn, $ticketData['impact_factor'])['factor'];
    }

    if (!empty($ticketData['deflator_factor'])) {
        $deflatorFactor = getDeflatorFactors($conn, $ticketData['deflator_factor'])['factor'];
    }

    return floatval($issueWeight * $complexityFactor * $impactFactor * $deflatorFactor);

}



/**
 * Cria ou atualiza um projeto
 * 
 * Caso o parametro $id seja nulo, cria um novo projeto com os dados fornecidos em $data.
 * Caso o parâmetro $id seja um valor número diferente de nulo, atualiza o projeto com os dados fornecidos em $data.
 * 
 * @param \PDO $conn Conexao com o banco de dados
 * @param int|null $id Id do projeto a ser atualizado. Se nulo, cria um novo projeto.
 * @param array $data Dados do projeto a ser criado ou atualizado. Se vazio, grava um nome e descrição automáticos para o projeto
 * 
 * @return int|null Id do projeto recém criado ou atualizado
 */
function createOrUpdateProject(\PDO $conn, ?int $id = null, array $data = []):?int
{
    $now_microtime = DateTime::createFromFormat('U.u', microtime(true));
    $autoName = $now_microtime->format("YmdHis.u");
    
    if (array_key_exists('name', $data) && !empty($data['name'])) {
        $data['name'] = noHtml($data['name']);
    } else {
        $data['name'] = $autoName;
    }
    if (array_key_exists('description', $data) && !empty($data['description'])) {
        $data['description'] = noHtml($data['description']);
    } else {
        $data['description'] = $autoName;
    }

    if ($id !== null) {
        $sql = "UPDATE 
                    projects 
                SET 
                    `name` = :name, 
                    `description` = :description 
                WHERE 
                    id = :id";
        try {
            $res = $conn->prepare($sql);
            $res->bindParam(':name', $data['name']);
            $res->bindParam(':description', $data['description']);
            $res->bindParam(':id', $id);
            $res->execute();
            return $id;
        }
        catch (Exception $e) {
            echo $e->getMessage();
            return null;
        }
    }

    $sql = "INSERT INTO projects (`name`, `description`) VALUES (:name, :description)";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':name', $data['name']);
        $res->bindParam(':description', $data['description']);
        $res->execute();
        return $conn->lastInsertId();
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}


/**
 * Adiciona um ticket a um projeto
 * 
 * Atualiza a tabela ocodeps com o id do projeto em todos os registros que possuem o ticket como pai ou filho 
 * e que nao possuam um projeto associado.
 * 
 * @param \PDO $conn Conexao o com o banco de dados
 * @param int $ticket Número do ticket a ser adicionado ao projeto
 * @param int $project ID do projeto a ser adicionado o ticket
 * 
 * @return bool true se a operação for realizada com sucesso, false caso contrário
 */
function addTicketToProject(\PDO $conn, int $ticket, int $project):bool
{
    $sql = "UPDATE 
                ocodeps 
            SET 
                proj_id = :project 
            WHERE 
                (
                    dep_pai = :ticket OR 
                    dep_filho = :ticket
                ) AND 
                proj_id IS NULL";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':project', $project);
        $res->bindParam(':ticket', $ticket);
        $res->execute();
        return true;
    }
    catch (Exception $e) {
        echo $e->getMessage();
        return false;
    }
}


function getUserName (\PDO $conn, int $id): ?string
{
    $sql = "SELECT nome FROM usuarios WHERE user_id = :id";
    try {
        $res = $conn->prepare($sql);
        $res->bindParam(':id', $id);
        $res->execute();
        if ($res->rowCount()) {
            return $res->fetch()['nome'];
        }
        return null;
    } catch (Exception $e) {
        echo $e->getMessage();
        return null;
    }
}