<?php

set_time_limit(0); // No limit!

require('../backend.php');

$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
$encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
$encryptionKey = $savvyext->cfgReadChar('encryption.key');
$encryptionIV = $savvyext->cfgReadChar('encryption.iv');

$baseURL = $savvyext->cfgReadChar('system.api_url');

$db_host = $savvyext->cfgReadChar('database.host');
$db_name = $savvyext->cfgReadChar('database.name');
$db_user = $savvyext->cfgReadChar('database.user');
$db_pass = $savvyext->cfgReadChar('database.pass');
$db_port = $savvyext->cfgReadLong('database.port');
$connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$conn = pg_connect($connstr);

$member_id = 1;
$days = 90;


$db = $conn;

$data_result = [];
// note we don travel everyday - i am modifying this to last 30 or 60 days - not just date diiference
$q = "SELECT b.travel_date::date, c.category,count(*) AS count, sum(b.cost) AS amount  ,sum(b.duration) AS sum_duration 
                FROM trackedemail_item a, parsedemail_item b, transport_providers c 
                WHERE a.id=b.trackedemail_item_id AND a.member_id=${member_id} AND c.id=b.transport_provider_id AND b.dup_id IS NULL AND
                date_trunc('day', b.travel_date_end) > (current_date - 2260) AND date_trunc('day', b.travel_date_end) < current_date 
                GROUP BY b.travel_date::date, c.category ORDER BY b.travel_date::date DESC LIMIT ${days} ";

// error_log($q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
    $itm_count = 0;
    while ($f = pg_fetch_assoc($r)) {
        $detail_line = array("category" => $f["category"],"amount"=>$f["amount"], "duration" => $f["sum_duration"]);
        $add_line = true;
        if ($itm_count > 0) {
            if (array_key_exists($f["travel_date"], $data_result)) {
                $add_line = false; // dont add new line - modify the current one
                $data_result[$f["travel_date"]]["value"] = $data_result[$f["travel_date"]]["value"] + $f["sum_duration"];
                $data_result[$f["travel_date"]]["amount"] = $data_result[$f["travel_date"]]["amount"] + $f["amount"];
                $data_result[$f["travel_date"]][$f["travel_date"]][] = $detail_line;
            }
        }

        if (true == $add_line) {
            $data_line = array(
                "date" => $f["travel_date"],
                "value" => $f["sum_duration"],
                "amount" => $f["amount"],
                $f["travel_date"] => $detail_line
            );
            $data_result[$f["travel_date"]] = $data_line;
        }
        $itm_count++;
    }
}

/*
  travel_date | category  | count | amount | sum_duration
  -------------+-----------+-------+--------+-----
  2019-07-25  | RideShare |     1 |   6.00 |   7
  2019-07-22  | RideShare |     1 |  13.90 |  19
  2019-06-24  | RideShare |     1 |  14.50 |  20
  2019-06-22  | RideShare |     1 |   6.00 |   6
  2019-06-21  | RideShare |     1 |  14.90 |  13
  2019-06-20  | RideShare |     2 |  24.00 |  33
  2019-06-16  | RideShare |     2 |  20.10 |  21
  2019-05-28  | RideShare |     1 |  14.20 |  15
  2019-05-27  | RideShare |     4 |  40.30 |  64
  2019-05-27  | Taxi      |     1 |  13.20 |  10
  2019-05-26  | RideShare |     2 |  27.70 |  17
  2019-05-24  | RideShare |     3 |  27.50 |  53
  2019-05-24  | Taxi      |     1 |  24.50 |  36
  2019-05-23  | RideShare |     4 |  38.10 |  45

 */

//TODO: replace with real data here
$data = [

    array("date" => "2006-1-13", "value" => 60),
    array("date" => "2006-1-23", "value" => 60),
    array("date" => "2006-3-24", "value" => 55),
    array("date" => "2006-2-24", "value" => 65),
    array("date" => "2006-4-26", "value" => 50),
    array("date" => "2006-5-27", "value" => 70),
    array("date" => "2006-6-30", "value" => 60),
    array("date" => "2006-7-1", "value" => 69),
    array("date" => "2006-8-10", "value" => 45),
    array("date" => "2006-10-15", "value" => 35),
    array("date" => "2006-11-13", "value" => 45),
    array("date" => "2006-12-12", "value" => 45),
    array("date" => "2007-1-13", "value" => 60),
    array("date" => "2007-1-23", "value" => 60),
    array("date" => "2007-2-24", "value" => 65),
    array("date" => "2007-3-24", "value" => 55),
    array("date" => "2007-4-26", "value" => 50),
    array("date" => "2007-5-27", "value" => 70),
    array("date" => "2007-6-30", "value" => 60),
    array("date" => "2007-7-1", "value" => 169),
    array("date" => "2007-8-10", "value" => 45),
    array("date" => "2007-10-15", "value" => 35),
    array("date" => "2007-11-13", "value" => 45),
    array("date" => "2007-12-12", "value" => 45),
    array("date" => "2008-1-13", "value" => 21),
    array("date" => "2008-1-15", "value" => 20),
    array("date" => "2008-3-24", "value" => 30),
    array("date" => "2008-4-26", "value" => 22),
    array("date" => "2008-5-27", "value" => 34),
    array("date" => "2008-6-30", "value" => 45),
    array("date" => "2008-7-1", "value" => 25),
    array("date" => "2008-8-10", "value" => 24),
    array("date" => "2008-10-15", "value" => 35),
    array("date" => "2008-11-13", "value" => 45),
    array("date" => "2008-12-12", "value" => 45),
];

//print_r($data);
print_r($data_result);
// return [$data_result,[]];