#!/usr/bin/perl
#
# Script designado para realizar a liberacao de bloqueios
# realizados pelo fail2ban 
#
# Necessário instalar DBI e Time::Piece
#


use DBI;


# variaveis do banco de dados

$database = "pabxvirtual";
$dbhost = "localhost";
$dbport   = "3306";
$dbuser   = "asteriskuser";
$dbpass   = "2sf__d09AJDKaef";

$pbxhost = 'pabxvirtual';

@fwTables = ("f2b-asterisk-udp", "f2b-asterisk-tcp");


# Atribuicao de valores para localtime
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);




# preparando o banco de dados

$dsn = "DBI:mysql:database=$database;host=$dbhost;port=$dbport";
$dbh = DBI->connect($dsn, $dbuser, $dbpass);


# verificando solicitacoes de liberacao de ip 
$query = "SELECT a.id,a.ip,a.acao,b.servidor FROM pabxvirtual_firewall_regras a
                JOIN pabxvirtual_firewall b ON b.srcIP=a.ip
		where servidor = '$pbxhost'";


my $sth = $dbh->prepare($query);

$sth->execute();

if($sth->rows > 0){

  # iniciando loop nos resultados
  while(my $qResult = $sth->fetchrow_hashref()){

	print "Solicitacao de liberacao para $qResult->{ip} em $qResult->{servidor}\n";
	foreach $fwTable (@fwTables){
		$command = "/sbin/iptables -D $fwTable -s $qResult->{ip} -j REJECT";
		print "Removendo $qResult->{ip} de $fwTable\n";
		system($command);	
	}

	push @queryDelete , "DELETE FROM pabxvirtual_firewall_regras WHERE ip = '$qResult->{ip}' limit 1";
	push @queryDelete , "DELETE FROM pabxvirtual_firewall WHERE srcIP = '$qResult->{ip}' limit 1";


  }

  foreach $qDel (@queryDelete){
	$sth = $dbh->prepare($qDel);
	if(!$sth->execute()){
		print "Falha na execucao da query: $qDel\n";
	}
  }


}else{
  print "Sem resultado do banco\n";
}