<?php
// app/api/transactions/verifyvalidation

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // Consultamos las transacciones que tengan validator y vencimiento menor a hora actual
    $sql = "SELECT * FROM transactions ".
    "       WHERE validator IS NOT NULL".
    "       AND amount = 0".
    "       AND validthru < DATE_SUB(NOW() , INTERVAL 10 MINUTE) ".
    "       GROUP BY txid";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
       
    $db->autocommit(FALSE);
      
    while($txdata = $rs->fetch_assoc()){
        
        $out->test [] = $txdata;
        
        $sqlDeposits =  "SELECT         t.id, t.dsc, ".
                        "               IF(d.valid IS NOT NULL, 1, " . //Si es valido, si no validamos el rechazado
                        "               IF(d.rejected IS NOT NULL, -1, 0)) AS status, " . //Si tampoco est�1�7�1�7 rechazado pendiente
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                    	"		        t.accountid, ".
                    	"               IF(t.id <> t.txid, 'Comisi&oacuten', 'Dep&oacutesito') AS typedsc, ".
                        "               IF(t.id <> t.txid, 'FEE', 'DEP') AS type, ".
                        "               'DEP' AS 'group' " .
                        "FROM           transactions t ".
                        "INNER JOIN     deposits d ON d.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               AND a.entityusrid = -1 ";
    
        $sqlWithdraw =  "SELECT         t.id, t.dsc, ".
                        "               IF(d.valid IS NOT NULL, 1, " . //Si es valido, si no validamos el rechazado
                        "               IF(d.rejected IS NOT NULL, -1, 0)) AS status, " . //Si tampoco est�1�7�1�7 rechazado pendiente
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                    	"		        t.accountid, ".
                    	"               IF(t.id <> t.txid, 'Comisi&oacuten', 'Retiro') AS typedsc, ".
                        "               IF(t.id <> t.txid, 'FEE', 'WITH') AS type, ".
                        "               'WITH' AS 'group' " .
                        "FROM           transactions t ".
                        "INNER JOIN     withdrawals d ON d.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               AND a.entityusrid = -1 ";
                    
        $sqlXch     =   "SELECT         t.id, t.dsc, ".
                        "               '1' AS status, " .
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                    	"		        t.accountid, ".
                    	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Intercambio') AS typedsc, ".
                        "               IF(adest.paymentmethodid<>-1, 'FEE', 'XCH') AS type, ".
    		            "               'XCH' AS 'group' " .
                        "FROM           transactions t " . 
                        "INNER JOIN     orders o ON o.id = t.orderid " .
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               AND a.entityusrid = -1 " .
                    	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination ";
                	
                	
        $sqlXfer     =  "SELECT         t.id, t.dsc, ".
                        "               IF(t.paypending = 1, 0, 1) AS status, " .
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                    	"		        t.accountid, ".
                    	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Transferencia') AS typedsc, ".
                        "               IF(adest.paymentmethodid<>-1, 'FEE', 'XFER') AS type, ".
    		            "               'XFER' AS 'group' " .
                        "FROM           transactions t " . 
                    	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                        "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                        "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                        "LEFT JOIN 	    paytxdetails ptx ON ptx.transactionid = t.txid ".
                    	"INNER JOIN 	accounts a  ".
                    	"ON    	   	    a.id = t.accountid  ".
                    	"               AND a.entityusrid = -1 ".
                    	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                    	"WHERE 		    d.transactionid IS NULL ". 
                    	"               AND w.transactionid IS NULL ".
                        "               AND ptx.transactionid IS NULL ".
                        "               AND pm.transactionid IS NULL ".
                    	"               AND t.orderid IS NULL ".
                    	"               AND t.accountorigin IS NOT NULL ";
                	
        $sqlPaytx =     "SELECT         t.id, t.dsc, ".
                        "               IF(t.paypending = 1, 0, 1) AS status, " .
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                    	"		        t.accountid, ".
                    	"               IF(adest.paymentmethodid <> -1, 'Comisi&oacuten', 'Pagos AFX Pay') AS typedsc, ".
                        "               IF(adest.paymentmethodid <> -1, 'FEE', 'PAYTX') AS type, " .
                        "               'PAYTX' AS 'group' " .
                        "FROM           transactions t ".
                        "INNER JOIN     paytxdetails pd ON pd.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               AND a.entityusrid = -1 " .
                        "LEFT JOIN      accounts adest ON adest.id = t.accountdestination";
                    
        //Movimientos de movil sin sumar las comisiones
        $sqlPM =        "       SELECT  	    t.id, t.txid, t.dsc, pd.ref AS ref, " .
                        "                       t.accountorigin, t.accountdestination, " .
                        "                       IF(t.amount = 0, t.amountghost, t.amount) AS amount, " .
                        "                       DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated,  ". 
                        "                       t.amountghost AS amountghost, " .
    			        "                       t.accountid, t.paypending AS paypending, t.validator AS validator, " .
    			        "                       IF(adest.paymentmethodid <> -7, 'Comisi&oacuten', 'Cripto Pago') AS typedsc, ".
    			        "                       IF(adest.paymentmethodid <> -7, 'FEE', 'PMPAY') AS type " .
    			        "       FROM            transactions t " .
        		        "       INNER JOIN      accounts a ON t.accountid = a.id ".
        		        "       INNER JOIN      pagomovildetails pd ON pd.transactionid = t.txid ".
    			        "       LEFT JOIN       accounts adest ON adest.id = t.accountdestination" .
    			        "       ORDER BY        t.id ";
    
        $sqlPMGroup =   "SELECT         PM.id, PM.dsc, ".
                        "               IF(PM.ref IS NOT NULL, 1, 0) AS status, " .
                        "               MAX(PM.accountorigin) AS accountorigin, MAX(PM.accountdestination)  AS accountdestination, " .
                        "               IF(SUM(PM.amount) = 0, SUM(PM.amountghost), SUM(PM.amount)) AS amount, ".
                        "               DATE_FORMAT(PM.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               PM.amountghost AS amountghost,  ".
                    	"		        PM.accountid, ".
                    	"               PM.typedsc AS typedsc, " .
                        "               PM.type AS type, " .
                        "               'PMPAY' AS 'group' " .
                        "FROM           (" . $sqlPM . ") PM " .
                        "INNER JOIN      accounts a ON PM.accountid = a.id ".
                        "WHERE          PM.id IS NOT NULL " .
    			        "GROUP BY       PM.id";
    
        $sql =  "SELECT     A.*, " .
            "       currencies.id AS currid, " .
            "       currencies.name AS currname, " .
            "       currencies.decimals AS decimals " .
            "FROM ( " . $sqlDeposits . " UNION " .
            "       " . $sqlWithdraw . " UNION " .
            "       " . $sqlXch .      " UNION " .
            "       " . $sqlXfer .     " UNION " .
            "       " . $sqlPaytx .    " UNION " .
            "       " . $sqlPMGroup .  " ) A " .
            "INNER JOIN accounts ON A.accountid = accounts.id " .
            "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
            "       WHERE A.id = ".$txdata["txid"];
        if (!$res = $db->query($sql)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error));
        }
        
    /*
        // consultamos para saber el tipo
        $sql = "SELECT t.id AS txid, ".
        "       CASE ".
        "       WHEN t.dsc LIKE '%comis%' THEN 'Comisión' ".
        "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 'Ajuste' ".
        "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 'Intercambio' ".
        "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 'Depósito' ".
        "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 'Retiro' ".
        "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 'Transferencia' END AS type".
        "       FROM transactions t".
        "       WHERE t.txid = ".$txdata["id"];
        if (!$res = $db->query($sql)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error));
        }*/
        
        $row = $res->fetch_assoc();
        
        $out->testing []  = $row;
        
        if($row['type'] == "XFER" || $row['type'] == 'PMPAY'){
            
            //eliminar la transaccion
            $sql = "DELETE FROM transactions ".
            "       WHERE id = ".$txdata["id"].
            "       OR txid = ".$txdata["id"];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            if ($row['type'] == 'PMPAY') {
                //eliminar la transaccion
                $sql = "DELETE FROM pagomovildetails ".
                "       WHERE transactionid = ".$txdata["id"];
                if (!$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
            }
        }else{
            
            // cancelamos el movimiento
            $sql = "UPDATE transactions SET ".
            "       validator = NULL, ".
            "       validthru = NULL, ".
            "       paypending = -1, ".
            "       amountghost = amount + amountghost, ".
            "       amount = 0 ".
            "       WHERE txid = ".$txdata["id"];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            //Colocamos la fecha de validacion del retiro
            $sql = "UPDATE withdrawals SET".
            "       rejected = NOW() ".
            "       WHERE transactionid = ".$txdata["id"];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            //Colocamos la fecha de validacion del deposito
            $sql = "UPDATE deposits SET".
            "       rejected = NOW() ".
            "       WHERE transactionid = ".$txdata["id"];
            if (!$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            //Consultamos que se rechaze el movimiento MAIN
            $sql = "SELECT COUNT(id) AS qty ".
            "       FROM transactions ".
            "       WHERE id = txid ".
            "       AND id = ".$txdata['id'];
            if (!$rs=$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
            
            $row = $rs->fetch_assoc();
            
            if($row['qty'] == 1){
                
                // Obtenemos el id del usuario
                $sql = "SELECT users.id AS userid ".
                "       FROM users, transactions, accounts ".
                "       WHERE transactions.id = ".$txdata['id'].
                "       AND transactions.accountid = accounts.id ".
                "       AND accounts.entityusrid = -1 ".
                "       AND accounts.userid = users.id";
                if (!$rs=$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
                
                $row = $rs->fetch_assoc();
                //auditoria
                setAuditByUserId($db, "OPERACIONES", $row['userid'], "APP", "Movimiento rechazado por falta de confirmación / Vencimiento de código (".$txdata['id'].")");
            }
        }
        
        $db->commit();
    }
    
    $db->close();
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
?>
