<?php
// cms/api/alerts/validatealerts
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    $out = new stdClass();
    $db->autocommit(FALSE);
    
    // Este servicio permite eliminar las alertas que hayan sido verificadas y lleven 12 meses o mas existiendo
    $sql = "SELECT alerts.id AS alertid, ".
    "       usr.id AS userid, ".
    "       usr.usr AS usr, ".
    "       usr.risklevel AS risklevel, ".
    "       IF(usr.type = 1, ".
    "           CONCAT(usrperson.fname,IF(".
    "               usrperson.sname IS NOT NULL, CONCAT(' ',usrperson.sname), '')), ".
    "           usrcompany.name) AS name, ".
    "       IF(usr.type = 1, ".
    "           CONCAT(usrperson.flastname,IF(".
    "               usrperson.slastname IS NOT NULL, CONCAT(' ',usrperson.slastname), '')), ".
    "           usrcompany.comercialname) AS secondname, ".
    "       IF(usr.type = 1, usrperson.politicallyexposed, usrcompany.politicallyexposed) AS pep,".
    "       IF(usr.type = 1, usrperson.doctype , 1) AS doctype, ".
    "       IF(usr.type = 1, usrperson.doc, usrcompany.doc) AS doc,".
    "       alertsupdates.status AS alertstatus, ".
    "       alerts.type AS alerttype, ".
    "       alerts.dsc AS alertdsc, ".
    "       alerts.lastupdate AS lastupdate, ".
    "       alerts.createddate AS createddate".
    "       FROM alerts  ".
    "       LEFT JOIN (".
    "           SELECT alertsupdates.date AS date,".
    "           alertsupdates.alertid AS alertid,".
    "           alertsupdates.status AS status".
    "           FROM alertsupdates".
    "           INNER JOIN (SELECT MAX(id) AS id FROM alertsupdates GROUP BY alertid) max " .
    "           ON alertsupdates.id = max.id " .
    "           GROUP BY alertsupdates.alertid".
    "       ) AS alertsupdates ON alertsupdates.alertid = alerts.id".
    "       LEFT JOIN users usr ON usr.id = alerts.userid ".
    "       LEFT JOIN userpersons usrperson ON alerts.userid = usrperson.usrid ".
    "       LEFT JOIN usercompanies usrcompany ON alerts.userid = usrcompany.usrid ".
    "       WHERE alerts.id IS NOT NULL".
    "       AND DATE(alerts.createddate) <= DATE_SUB(NOW(), INTERVAL 12 MONTH) ".
    "       AND alertsupdates.status = 1";
    if (!$rs=$db->query($sql)){
        $error = determinateDBError($db);
        $db->rollback();
        $db->close();
        badEnd("500", array("msg"=>$error));
    }
    
    $out->sql = $sql;
    // Recorremos cada alerts para eliminar
    while($row = $rs->fetch_assoc()){
        // Revalidamos el status
        $out->records [] = $row;
        if ($row['alertstatus'] == 1) {
            // Eliminamos los updates
            $sql = "DELETE FROM alertsupdates".
            "       WHERE alertsupdates.alertid = ".$row['alertid'];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
                
            // Eliminamos los datos de la alerta
            $sql = "DELETE FROM alerts".
            "       WHERE alerts.id = ".$row['alertid'];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            $db->commit();
        }
    }
    
    $db->close();
        
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
?>