<?php
require_once RUTA_APP . 'Conexion.php';
require_once 'Respuesta.php';
require_once 'GestionDatos.php';
class RespuestaDatos
{
    public static function getRespuestaById(int $id): ?Respuesta{
        $resp = null;
        $con = getConnection();
        $query = "SELECT id,Gestion,Campo,Respuesta FROM dialvox_crm_gestion_respuesta WHERE id = '" . limpia($id) . "' LIMIT 1";
        if ($result = sv_mysqli_query($con, $query)) {
            if ($row = sv_mysqli_fetch_array($result)) {
                $resp = new Respuesta($row["id"], GestionDatos::getGestionById($row["Gestion"]), CampoDatos::getCampoById($row["Campo"]), $row["Respuesta"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getRespuestasByGestion(Gestion $gestion): array{
        $resp = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion_respuesta WHERE Gestion = '" . limpia($gestion->getId()) . "'";
        if ($result = sv_mysqli_query($con, $query)) {
            while ($row = sv_mysqli_fetch_array($result)) {
                $resp[] = self::getRespuestaById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }        
    public static function getGestionesByCampoAndResultado(Campo $campo, string $respuesta):array{
        $resp = array();
        $con = getConnection();
        $query = "SELECT G.id AS Gestion,G.Lead AS Lead FROM dialvox_crm_gestion AS G INNER JOIN dialvox_crm_gestion_respuesta AS R ON R.Gestion = G.id WHERE R.Campo = '".limpia($campo->getId())."' AND R.Respuesta LIKE '%".limpia($respuesta)."%' ORDER BY G.Fecha DESC";        
        $leadsUsados = array();
        if($result = sv_mysqli_query($con,$query)){
            while($row = sv_mysqli_fetch_array($result)){
                if(!array_key_exists("lead_".$row["Lead"],$leadsUsados)){
                    $leadsUsados["lead_".$row["Lead"]] = true;
                    $resp[] = GestionDatos::getGestionById($row["Gestion"]);
                }                                
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function countGestionesByCampoAndResultado(Campo $campo, string $respuesta):int{
        $resp = 0;
        $con = getConnection();
        $query = "SELECT COUNT(G.id) AS cuenta AS Gestion,G.Lead AS Lead FROM dialvox_crm_gestion AS G INNER JOIN dialvox_crm_gestion_respuesta AS R ON R.Gestion = G.id WHERE R.Campo = '".limpia($campo->getId())."' AND R.Respuesta LIKE '%".limpia($respuesta)."%' GROUP BY G.Lead ORDER BY G.Fecha DESC";        
        if($result = sv_mysqli_query($con,$query)){
            if($row = sv_mysqli_fetch_array($result)){
                $resp = $row["cuenta"];
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }

    public static function getGestionesByPerfiles(array $perfiles):array{
        $resp = array();
        $con = getConnection();

        $arrLeadIds = array();
        $strLeadIds = "";

        for ($i = 0;$i<count($perfiles);$i++) {
            $arrLeadIds[] = $perfiles[$i]->getLeadId();
        }
        $strLeadIds = implode(',', $arrLeadIds);

        $query = "SELECT G.id AS Gestion,G.Lead AS Lead FROM dialvox_crm_gestion AS G WHERE G.Lead IN ($strLeadIds) ORDER BY G.Fecha DESC";        
        $leadsUsados = array();
        if($result = sv_mysqli_query($con,$query)){
            while($row = sv_mysqli_fetch_array($result)){
                if(!array_key_exists("lead_".$row["Lead"],$leadsUsados)){
                    $leadsUsados["lead_".$row["Lead"]] = true;
                    $resp[] = GestionDatos::getGestionById($row["Gestion"]);
                }                                
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }

    public static function countGestionesByPerfiles(array $perfiles): int {
        $resp = 0;
        $con = getConnection();

        $arrLeadIds = array();
        $strLeadIds = "";

        for ($i = 0;$i<count($perfiles);$i++) {
            $arrLeadIds[] = $perfiles[$i]->getLeadId();
        }
        $strLeadIds = implode(',', $arrLeadIds);

        $query = "SELECT COUNT(G.id) AS cuenta AS Gestion,G.Lead AS Lead FROM dialvox_crm_gestion AS G INNER JOIN dialvox_crm_gestion_respuesta AS R ON R.Gestion = G.id WHERE G.Lead IN ($strLeadIds) GROUP BY G.Lead ORDER BY G.Fecha DESC";        
        if($result = sv_mysqli_query($con,$query)){
            if($row = sv_mysqli_fetch_array($result)){
                $resp = $row["cuenta"];
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }

    public static function getRespuestasByCampoAndRespuesta(Campo $campo, string $respuesta):array{
        $resp = array();
        $con = getConnection();
        $query = "SELECT id FROM dialvox_crm_gestion_respuesta WHERE Campo = '".limpia($campo->getId())."' AND Respuesta = '".limpia($respuesta)."' AND Gestion IN (SELECT id FROM dialvox_crm_gestion GROUP BY Lead ORDER BY FECHA DESC)";
        if($result = sv_mysqli_query($con,$query)){
            while($row = sv_mysqli_fetch_array($result)){
                $ret[] = self::getRespuestaById($row["id"]);
            }
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function insertRespuesta(respuesta $respuesta):int{
        $resp = 0;
        $con = getConnection();
        $query = "INSERT INTO dialvox_crm_gestion_respuesta (Gestion, Campo, Respuesta) VALUES ('".limpia($respuesta->getGestion()->getId())."','".limpia($respuesta->getCampo()->getId())."','".limpia($respuesta->getRespuesta())."')";
        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 updateRespuesta(respuesta $respuesta):int{
        $resp = 0;
        $con = getConnection();
        $query = "UPDATE dialvox_crm_gestion_respuesta SET Respuesta = '".limpia($respuesta->getRespuesta())."' WHERE id = ".limpia($respuesta->getId())."";
        
        if($result = sv_mysqli_query($con,$query)){
            $resp = 1;
        }else{
            $resp = abs(mysqli_errno($con))*-1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
}
