#!/usr/bin/perl
#Kommt auf LSH zum Einsatz

use DBI;
use strict;

# Variable für SQL Befehle
my $sql;

# Gerätelogin
my ($DEVICEUSER, $DEVICEIP, $DEVICEPASS) = ('admin', '192.168.100.7', 'admin');

# DB Parameter
my ($db_user, $db_name, $db_pass, $db_host) = ('1_mfi', '1_mfi', 'K01v1kk0!', 'panel.agserver.de');

# DB connect
my $dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass) or die "Cannot connect to DB\n";

# Läuft ein Prozess bereits?
if (-e "/scripts/mFi2/read_queue") {
   print "already running\n";
   exit 0;
}

open HANDLE, ">/scripts/mFi2/read_queue" || die "cannot touch 'read_queue'\n"; 
close HANDLE;

# Zeilen in Queue vorhanden? Wenn nicht beenden
$sql = "select * from queue";
my $rows = $dbh->do("$sql");
if ($rows == 0) {
   print "nothing to do\n";
   unlink "/scripts/mFi2/read_queue";
   exit 0;
}

for (my $row=1; $row<=$rows; $row++) {
# Queue auslesen
$sql = "select id, port_id, state, source from queue order by id asc";
my @row_queue_ary = $dbh->selectrow_array("$sql");

if ($row_queue_ary[2] == 0 or $row_queue_ary[2] == 1) {
   # Ändert sich der Portstatus?
   # neu: $row_queue_ary[2]
   $sql = "select state from port where id = $row_queue_ary[1]";
   my @row_old = $dbh->selectrow_array("$sql");
   # alt: $row_old[0]
   if ($row_queue_ary[2] != $row_old[0]) {
      
      # Portstatus in DB setzen
      $sql = "update port set state = $row_queue_ary[2] where id = $row_queue_ary[1]";
      $dbh->do("$sql") or die "$dbh->errstr";
  
      # Portnummer des Gerätes ermitteln
      $sql = "select nr, descr, dev_id from port where id = $row_queue_ary[1]";
      my @row_port_ary = $dbh->selectrow_array("$sql");

      # Geräteinfo holen
      $sql = "select id, name, ip, user, pass from device where id = $row_port_ary[2]";
      my @row_dev_ary = $dbh->selectrow_array("$sql");
  
      # Logeintrag
      # $row_queue_ary[3] source
      my $msg = "Setze Port auf neuen Status"; 
      $sql = "insert into log (msg, port, port_id, device, device_id, status, source, descr) values ('$msg', '$row_port_ary[0]', '$row_queue_ary[1]', '$row_dev_ary[1]', '$row_dev_ary[0]', '$row_queue_ary[2]', '$row_queue_ary[3]', '$row_port_ary[1]')"; 
      $dbh->do("$sql");

      # Gerät steuern
      # Port $row_port_ary[0] (portnummer; evtl -1? da portzählung im gerät bei 0 beginnt) auf on|off setzen ($row_queue_ary[2] entw 0 oder 1)
      # print "Setze Port $row_port_ary[0] mit ID $row_queue_ary[1] '$row_port_ary[1]' auf Status $row_queue_ary[2]\n";
      
      print "send commands to device\n";
      my $dev = $row_port_ary[0]; # - 1;
      my $com = "echo $row_queue_ary[2] > /dev/output$dev";
      my $cmd = "expect /scripts/mFi2/port.exp $row_dev_ary[3] $row_dev_ary[2] $row_dev_ary[4] '$com' &";
      `$cmd`;
   }
   print "no change on port with $row_queue_ary[1]\n";
}

# Eintrag aus Queue löschen
$sql = "delete from queue where id = $row_queue_ary[0]";
$dbh->do("$sql") or die "$dbh->errstr";
}

print "job done\n";
unlink "/scripts/mFi2/read_queue";
exit 0;