#! /usr/bin/perl

use DBI;

$dbc= DBI->connect('dbi:Pg:dbname=redacted;host=localhost', 'redacted', '', { AutoCommit=>1, RaiseError=>1, PrintError=>0 });

$sth = $dbc->prepare
    ("
    
    SELECT 
        TO_CHAR(SN_EVENTS.CREATED, 'YYYY-MM-DD HH24:MI:SS') AS DATETIMED,
        SN_CAMERAS.TYPE AS DIRECTION,
        sefs.ocr_1 as OCR
    FROM 
        SN_EVENTS
            INNER JOIN sn_events_fusions sefs ON sefs.fk_event = sn_events.pk_event
            INNER JOIN SN_CAMERAS ON SN_CAMERAS.PK_CAMERA = SN_EVENTS.FK_CAMERA 
            INNER JOIN SN_RULES ON SN_RULES.PK_RULE = SN_EVENTS.FK_RULE 

    WHERE SN_CAMERAS.TYPE = 'entrance' OR SN_CAMERAS.TYPE = 'exit'
        AND SN_RULES.key = 'plate-recognition'
        AND SN_EVENTS.FK_EVENT_STATUS = 1 

    ORDER BY SN_EVENTS.CREATED DESC

    ");

$sth->execute;
@recordset;

while (@event = $sth->fetchrow_array()){
    push @recordset, [@event];
};

for ($i=0; $i<@recordset; $i++){
    if (@recordset[$i] && (@recordset[$i]->[1] == "exit")){
        for ($j=$i+1; $j<@recordset; $j++){
            if (@recordset[$i] && (@recordset[$i]->[2] == @recordset[$j]->[2])){
                undef @recordset[$j];
            }
        }
        undef @recordset[$i];
    }
}

for $row ( @recordset ){
    print "@$row\n";
}