<?php
require_once 'Gestion.php';
require_once 'CRMDatos.php';
require_once RUTA_APP . 'application/controllers/Clases/UsuarioDatos.php';
require_once RUTA_APP . 'Conexion.php';
class GestionDatos
{
    public static function getGestionById(int $id): ?Gestion
    {
        $ret = null;
        $con = getConnection();
        $query = "SELECT id, CRM, Lead, Fecha, Telefono, Agente, Calificacion FROM dialvox_crm_gestion WHERE id = '" . limpia($id) . "' LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $ret = new Gestion($row["id"], CRMDatos::getCRMById($row["CRM"]), $row["Lead"], $row["Fecha"], $row["Telefono"], UsuarioDatos::getUsuarioByUsuario($row["Agente"]), $row["Calificacion"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByUsuario(Usuario $usuario, int $pagina, int $limite, bool $orden): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE Usuario = '" . limpia($usuario->getUsuario()) . "' ORDER BY Fecha " . $orden ? 'DESC' : 'ASC' . " LIMIT " . ($pagina * $limite) . "," . $limite;
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function countGestionesByUsuario(Usuario $usuario, int $pagina, int $limite): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm_gestion WHERE Usuario = '" . limpia($usuario->getUsuario()) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret = $row["CUENTA"];
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByCRM(CRM $crm, int $pagina, int $limite, bool $orden): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "' ORDER BY Fecha " . $orden ? 'DESC' : 'ASC' . " LIMIT " . ($pagina * $limite) . "," . $limite;
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByCRMAndFechas(CRM $crm, $fechaIni, $fechaFin): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "' AND DATE(Fecha) BETWEEN DATE('" . limpia($fechaIni) . "') AND DATE('" . limpia($fechaFin) . "') ORDER BY Fecha DESC";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function countGestionesByCRM(CRM $crm, int $pagina, int $limite): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret = $row["CUENTA"];
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByLeadAndCRM(string $lead, CRM $crm, int $pagina, int $limite, bool $orden): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE Lead = '" . limpia($lead) . "' AND CRM = '" . limpia($crm->getId()) . "' ORDER BY Fecha " . $orden ? 'DESC' : 'ASC' . " LIMIT " . ($pagina * $limite) . "," . $limite;
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByLeadAndCRMWithoutLimit(string $lead, CRM $crm): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE Lead = '" . limpia($lead) . "' AND CRM = '" . limpia($crm->getId()) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function listGestionesByLeadAndCRM(string $lead, CRM $crm): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id,Fecha,Calificacion,Agente FROM dialvox_crm_gestion WHERE Lead = '" . limpia($lead) . "' AND CRM = '" . limpia($crm->getId()) . "' ORDER BY Fecha DESC";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = new Gestion($row["id"], $crm, $lead, $row["Fecha"], "", UsuarioDatos::getUsuarioByUsuario($row["Agente"]), $row["Calificacion"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function countGestionesByLead(string $lead, int $pagina, int $limite): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm_gestion WHERE Lead = '" . limpia($lead) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret = $row["CUENTA"];
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getGestionesByTelefono(string $telefono, int $pagina, int $limite, bool $orden): array
    {
        $ret = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE Telefono = '" . limpia($telefono) . "' ORDER BY Fecha " . $orden ? 'DESC' : 'ASC' . " LIMIT " . ($pagina * $limite) . "," . $limite;
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret[] = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function countGestionesByTelefono(string $telefono, int $pagina, int $limite): int
    {
        $ret = 0;
        $con = getConnection();
        $query = "SELECT COUNT(id) AS CUENTA FROM dialvox_crm_gestion WHERE Telefono = '" . limpia($telefono) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $ret = $row["CUENTA"];
            }
        }
        sv_mysqli_close($con);
        return $ret;
    }
    public static function getLastestGestionByCRMAgenteAndLead(CRM $crm, Usuario $agente, string $lead)
    {
        $resp = null;
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "' AND Lead = '" . limpia($lead) . "' AND Agente = '" . limpia($agente->getUsuario()) . "' ORDER BY Fecha DESC LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $resp = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getLastestGestionByCRMAndLead(CRM $crm, string $lead): ?Gestion
    {
        $resp = null;
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "' AND Lead = '" . limpia($lead) . "' ORDER BY Fecha DESC LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $resp = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getLastestGestionByCRMAndLeadByFechas(CRM $crm, string $lead, $fechaInic, $fechaFin): ?Gestion
    {
        $resp = null;
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion WHERE CRM = '" . limpia($crm->getId()) . "' AND Lead = '" . limpia($lead) . "' AND DATE(Fecha) BETWEEN DATE('" . limpia($fechaIni) . "') AND DATE('" . limpia($fechaFin) . "') ORDER BY Fecha DESC LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $resp = self::getGestionById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function addGestion(Gestion $gestion): ?int
    {
        $resp = 0;
        $con = getConnection();
        $query = "INSERT INTO dialvox_crm_gestion (CRM,Lead,Fecha,Telefono,Agente,Calificacion) VALUES ('" . limpia($gestion->getCRM()->getId()) . "','" . limpia($gestion->getLead()) . "','" . limpia($gestion->getFecha()) . "','" . limpia($gestion->getTelefono()) . "','" . limpia($gestion->getAgente()->getUsuario()) . "','" . limpia($gestion->getCalificacion()) . "')";
        if ($result = sv_mysqli_query($con, $query)) {
            $resp = mysqli_insert_id($con);
        } else {
            $resp = abs(mysqli_errno($con)) * -1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function updateGestion(Gestion $gestion): ?int
    {
        $resp = 0;
        $con = getConnection();
        $query = "UPDATE dialvox_crm_gestion SET CRM = '" . limpia($gestion->getCRM()->getId()) . "', Lead = '" . limpia($gestion->getLead()) . "', Fecha = '" . limpia($gestion->getFecha()) . "', Telefono = '" . limpia($gestion->getTelefono()) . "', Agente = '" . limpia($gestion->getAgente()->getUsuario()) . "', Calificacion = '" . limpia($gestion->getCalificacion()) . "' WHERE id = '' LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            $resp = 1;
        } else {
            $resp = abs(mysqli_errno($con)) * -1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getFecha(): ?string
    {
        $resp = null;
        $con = getConnection();
        $query = "SELECT NOW() AS FECHA";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $resp = $row["FECHA"];
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }

    public static function updateFecha(
        $fecha,
        $idGestion
    ): ?int
    {
        if (is_null($fecha)) {
            $fecha  = date('Y-m-d H:i:s');
        }

        $resp = 0;
        $con = getConnection();
        $query = "UPDATE dialvox_crm_gestion SET Fecha = '" . limpia($fecha) . "' WHERE id = '" . limpia($idGestion) . "' LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            $resp = 1;
        } else {
            $resp = abs(mysqli_errno($con)) * -1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
}
