\<?php
include('common.inc.php');
function processHourly($hourly) { 
    global $conn;
    $hfilename = str_replace(' ','_',$hourly['tgid']) . '-' . str_replace(Array(' 00:00:00+10',' 00:00:00+11'), '', $hourly['aday']) . '-' . $hourly['ahour'] . '.3gp';
 
    if (!file_exists("hourly/" . $hfilename)) {
 
        $filenames = explode(",", $hourly['filenames']);
        $cmd = "/usr/local/bin/ffmpeg";
        if (count($filenames) > 1) {
                $cmd .=" -filter_complex concat=n=" . count($filenames) . ":v=0:a=1"; // only concat when more than 1 file
        }
        $cmd .=" -i data/" . implode(" -i data/", $filenames) . " -ar 8000 -ab 4.75k -ac 1 hourly/" . $hfilename . ' 2>&1';
        //print_r($hourly);
        exec($cmd, $output, $returncode);
        echo $cmd . "<br>\n";
        if ($returncode != 0) {
            echo $returncode;
            print_r($output);
            unlink("hourly/" . $hfilename); // delete incomplete file
//            die();
        } else {
 
$time = strtotime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours");
            $q = "  insert into compilations (filename,files,datetime) VALUES ('" . $hfilename . "', ARRAY['" . implode("', '", $filenames) . "'], to_timestamp('" . $time . "') );";
$conn->query($q);
//echo $q."\n";
            foreach ($filenames as $filename) {
                $q = "UPDATE recordings SET archived = '$hfilename' WHERE filename = '$filename' ;";
$conn->query($q);
//echo $q."\n";
            }
//die();
        }
 
    }
}
/*$sth = $conn->prepare("select tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, 
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames from recordings group by tgid, ahour, aday order by  aday DESC, ahour, tgid;");
 
// TODO use tgid categories instead, tgid too specific
$sth->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach ($hourlies as $hourly) {
    processHourly($hourly);
}
$sth = $conn->prepare("select 'hour' as tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, 
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames from recordings group by ahour, aday order by  aday DESC, ahour;");
 
$sth->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach ($hourlies as $hourly) {
    processHourly($hourly);
}*/
$sth = $conn->prepare("select coalesce(category,'unknown') as tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday,
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames 
from recordings inner join tgids on recordings.tgid = tgids.tgid 
group by category, ahour, aday 
having count(archived) != count(filename)
order by  aday DESC, ahour, category;");
 
$sth->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach($hourlies as $hourly) {
    processHourly($hourly);
}