<?php

require_once('ReportMock.php');

class Report {

    const SPEED_LIMIT_WALKING = 1.5; // 1.5 m/s x 3.6 = 5.4 km/h
    const SPEED_LIMIT_DRIVING = 9.0; // 9.0 m/s x 3.6 = 32.4 km/h
    const TRIP_DRIVING_DISTANCE = 100; // m
    const TRIP_WALKING_DISTANCE = 30; // m
    const REPORT_CATEGORIES = [
        'RideShare' => 'RideShare',
        'Taxi' => 'Taxi',
        //'ScooterRental' => 'Scooter',
        'PublicTransport' => 'Bus',
        'Subway' => 'Subway',
        'Gas' => 'Gas'
    ];

    protected function mergeWeeklySpending($result, $results) {
        foreach ($results as $key => $val) {
            if ($val['dow'] == $result['dow']) {
                $val['spent'] += $result['spent'];
                $val['total'] += $result['total'];
                $results[$key] = $val;
                break;
            }
        }
        return $results;
    }

    public function weeklySpending($db, $gpsdb, $data) {
        error_log('Report::weeklySpending($db, $data) ');
        //$db_country = pg_escape_string($country); // is not used for now...
        //$db_address = pg_escape_string($address);
        $member_id = (int) $data['member_id'];
        //$member_id = 3; // DEBUG
        if ($member_id < 1)
            return NULL;

        $found = false;

        /*
          $results = [
          ['dow'=>0,'spent'=>0,'total'=>0],
          ['dow'=>1,'spent'=>0,'total'=>0],
          ['dow'=>2,'spent'=>0,'total'=>0],
          ['dow'=>3,'spent'=>0,'total'=>0],
          ['dow'=>4,'spent'=>0,'total'=>0],
          ['dow'=>5,'spent'=>0,'total'=>0],
          ['dow'=>6,'spent'=>0,'total'=>0]
          ];
         */

        $results = [];

        $now = new DateTime("7 days ago");
        $interval = new DateInterval('P1D'); // 1 Day interval
        $period = new DatePeriod($now, $interval, 6); // 7 Days

        foreach ($period as $day) {
            $results[] = array(
                'date' => $day->format('Y-m-d'),
                'spent' => 0,
                'total' => 0,
                'dow' => $day->format('w')
            );
        }

        // Credit card transations
        $q = "SELECT extract(dow from time) AS dow, round(0.01*sum(amount)/count(amount),2) AS spent, round(0.01*sum(amount),2) AS total ";
        $q .= " FROM members_bankimport ";
        $q .= " WHERE member_id=${member_id} AND LOWER(category) IN (SELECT LOWER(category) FROM activity_listcategory) AND date_trunc('day', time) > (current_date - 8) AND date_trunc('day', time) < current_date AND amount>0 ";
        $q .= " GROUP BY dow ORDER BY dow";
        error_log($q); // DEBUG
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                $results = self::mergeWeeklySpending($f, $results);
            }
            $found = true;
        }
        // E-mail receipts
        $q = "SELECT extract(dow from a.travel_date) AS dow, round(sum(a.cost)/count(a.cost),2) AS spent, round(sum(a.cost),2) AS total ";
        $q .= " FROM parsedemail_item a, trackedemail_item b ";
        $q .= " WHERE b.id=a.trackedemail_item_id AND b.member_id=${member_id} AND a.dup_id IS NULL AND ";
        $q .= " date_trunc('day', a.travel_date) > (current_date - 8) AND date_trunc('day', a.travel_date) < current_date ";
        $q .= " GROUP BY dow ORDER BY dow";
        error_log($q); // DEBUG
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                $results = self::mergeWeeklySpending($f, $results);
            }
            $found = true;
        }
        return [$found ? $results : NULL, []];
    }

    public function spendingByCategoryLastSevenDays($db, $gpsdb, $data) {
        $member_id = (int) $data['member_id'];
        $include_items = $data['include_items'] ?? false;
        $include_items_values = $data['include_items_values'] ?? true;
        // $member_id = 3;
        $days = $data['days'] ?? 7;
        $start_date = $data['start_date'] ?? "";
        $end_date = !empty($data['end_date']) ? date("Y-m-d", strtotime($data['end_date'] . "+1 days")) : "";
        $member_country = $data['member_country'] ?? "";
        return Report::spendingByCategoryForDays($db, $member_id, $days, $include_items, $start_date, $end_date, $include_items_values, $member_country);
    }

    public function categoryItems($db, $gpsdb, $data) {

        $member_id = (int) $data['member_id'];
        $category = filter_var($data['category'], FILTER_SANITIZE_STRING);
        $days = $data['days'] ?? 30;

        $start_date = $data['start_date'] ?? "";
        $end_date = !empty($data['end_date']) ? date("Y-m-d", strtotime($data['end_date'] . "+1 days")) : "";
        $result = Report::getCategoryItems($db, $member_id, $days, $category);

        return $result;
    }

    public function spendingByCategoryAllTime($db, $gpsdb, $data) {
        $member_id = (int) $data['member_id'];
        $include_items = $data['include_items'] ?? false;
        $now = time(); // or your date as well
        $your_date = strtotime("2010-01-31");
        $datediff = $now - $your_date;
        $days = round($datediff / (60 * 60 * 24));
        return Report::spendingByCategoryForDays($db, $member_id, $days, $include_items);
    }

    public function spendingByWeekdayLastSevenDays($db, $gpsdb, $data) {
        error_log('Report::spendingByWeekdayLastSevenDays($db, $data)');
        //return ReportMock::spendingByWeekdayLastSevenDays($db, $data);
        return Report::weeklySpending($db, $data);
    }

    public function emissionsByDays($db, $gpsdb, $data) {
        $member_id = (int) $data['member_id'];
        $days = $data['days'] ?? 30;
        $start_date = $data['start_date'] ?? "";
        $end_date = !empty($data['end_date']) ? date("Y-m-d", strtotime($data['end_date'] . "+1 days")) : "";
        $member_country = $data['member_country'] ?? "";
        // $member_id = 3;
        return Report::emissionsByDaysData($db, $member_id, $days, $start_date, $end_date, $member_country);
    }

    public function travelTimeByDays($db, $gpsdb, $data) {
        $member_id = (int) $data['member_id'];
        $days = $data['days'] ?? 30;
        $start_date = $data['start_date'] ?? "";
        $end_date = !empty($data['end_date']) ? date("Y-m-d", strtotime($data['end_date'] . "+1 days")) : "";
        $member_country = $data['member_country'] ?? "";
        // $member_id = 3;
        return Report::travelTimeByDaysData($db, $member_id, $days, $start_date, $end_date, $member_country);
    }

    /**
     *  Radar chart data
     * 
     */
    public function travelTimeBreakdownVsAverage($db, $gpsdb, $data) {


        error_log('Report::travelTimeBreakdownVsAverage($db, $data)');
        $member_id = (int) $data['member_id'];
        $member_country = $data['member_country']??"";

        $count_total = 0;
        // Walking SQL
        $count_walking = 0;
        $q = "select count(*) from members_tracking_trips where member_id=${member_id} and distance>" . self::TRIP_WALKING_DISTANCE . " and address_start<>address_end and avg_speed<" . self::SPEED_LIMIT_WALKING;
        $r = pg_query($gpsdb, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $count_walking = $f[0];
            $count_total += $f[0];
        }
        // Scooter/bike SQL
        $count_bicycle = 0;
        $q = "select count(*) from members_tracking_trips where member_id=${member_id} and distance>" . self::TRIP_DRIVING_DISTANCE . " and address_start<>address_end and avg_speed>=" . self::SPEED_LIMIT_WALKING . " and avg_speed<=" . self::SPEED_LIMIT_DRIVING;
        $r = pg_query($gpsdb, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $count_bicycle = $f[0];
            $count_total += $f[0];
        }
        // Driving SQL
        $count_driving = 0;
        $q = "select count(*) from members_tracking_trips where member_id=${member_id} and distance>" . self::TRIP_DRIVING_DISTANCE . " and address_start<>address_end and avg_speed>" . self::SPEED_LIMIT_DRIVING;
        $r = pg_query($gpsdb, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $count_driving = $f[0];
            $count_total += $f[0];
        }

        $count_scooter = 0;
        $count_mycar = 0;
        $count_bus = 0;
        $count_train = 0;
        $count_plane = 0;

        // Credit card transations (user)
        $q = "SELECT count(mbi.import_id), mbi.category ";
        $q .= " FROM members_bankimport mbi ";
        $q .= " inner join activity_listcategory alc on alc.category = mbi.category "; // get only travel related categories
        $q .= " WHERE member_id=${member_id} ";
        $q .= " GROUP BY mbi.category; ";
        //error_log($q); // DEBUG
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            $mycar = [];
            while ($f = pg_fetch_row($r)) {
                switch ($f[1]) {
                    case 'ScooterRental': {
                            $count_bicycle += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'BikeRental': {
                            $count_bicycle += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'Parking': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'Gas': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'TollsFees': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'CarRental': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'CarShare': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'CarInsurance': {
                            $mycar[] = $f[0];
                            break;
                        }
                    case 'BusTickets': {
                            $count_bus += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'PublicTransport': {
                            $count_bus += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'Transportation': {
                            $count_bus += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'RideShare': {
                            $count_driving += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'Taxi': {
                            $count_driving += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    default: $count_total += $f[0];
                }
            }
            if (count($mycar)) {
                $max_mycar = 0;
                foreach ($mycar as $val) {
                    if ($val > $max_mycar) {
                        $max_mycar = $val;
                    }
                }
                $count_mycar += $max_mycar;
                $count_total += $max_mycar;
            }
        }
        //*/
        /*
          savvy=> SELECT count(*),category FROM members_bankimport WHERE member_id=3 GROUP BY category;
          count |    category
          -------+-----------------
          1 | ScooterRental
          2 | Other
          26 | PublicTransport
          1111 | RideShare
          55 | Taxi
          83 | Parking
          99 | Gas
          1 | CarShare
          31 | TollsFees
          108 | CarRental
         */

        // E-mail receipts (user)
        $q = "SELECT count(*), c.category";
        $q .= " FROM parsedemail_item a, trackedemail_item b, transport_providers c ";
        $q .= " WHERE b.id=a.trackedemail_item_id AND b.member_id=${member_id} AND a.dup_id IS NULL ";
        $q .= " AND c.id=a.transport_provider_id GROUP BY c.category";
        error_log($q); // DEBUG
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_row($r)) {
                switch ($f[1]) {
                    case 'CarShare': {
                            $count_mycar += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'RideShare': {
                            $count_driving += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    case 'Taxi': {
                            $count_driving += $f[0];
                            $count_total += $f[0];
                            break;
                        }
                    default: $count_total += $f[0];
                }
            }
        }
        /*
          SELECT count(*), c.category FROM parsedemail_item a, trackedemail_item b, transport_providers c  WHERE b.id=a.trackedemail_item_id AND b.member_id=3 AND a.dup_id IS NULL  AND c.id=a.transport_provider_id GROUP BY c.category;
          count | category
          -------+-----------
          2 | Taxi
          1800 | RideShare
          ? | CarShare
         */


        // get the user here
        $user_data = array();
        $query = "SELECT id, country, state, city FROM members WHERE id = {$member_id};";
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {

                $user_data['member_id'] = $f['id'];
                $user_data['country'] = $f['country'];
                $user_data['city'] = $f['city'];
                $user_data['state'] = $f['state'];
            }
        }

        $averages = self::getPersonalityAverages($db, $user_data['country'], $user_data['state'], $user_data['city']);
        if (!empty($averages)) {
            $radar_avrg = $averages[0];
            $total_average = $averages[1];

            $average_walking_p = $radar_avrg["EMWALK"] / $total_average;
            $average_bicycle_p = $radar_avrg["EMBYCYCLE"] / $total_average;
            $average_scooter_p = $radar_avrg["EMSCOTTER"] / $total_average;
            $average_driving_p = $radar_avrg["EMRIDESH"] / $total_average;
            $average_mycar_p = $radar_avrg["EMTAXICAR"] / $total_average;
            $average_bus_p = $radar_avrg["EMPUBLIC"] / $total_average;
            $average_train_p = $radar_avrg["EMTRAIN"] / $total_average;
        } else {
            $average_walking_p = 0;
            $average_bicycle_p = 0;
            $average_scooter_p = 0;
            $average_driving_p = 0;
            $average_mycar_p = 0;
            $average_bus_p = 0;
            $average_train_p = 0;
        }

        // not data available yet
        $count_train_p = $count_total > 0 ? sprintf("%0.02f", $count_train / $count_total) : 0;
        $count_plane_p = $count_total > 0 ? sprintf("%0.02f", $count_plane / $count_total) : 0;

        //gps data source
        $count_walking_p = $count_total > 0 ? sprintf("%0.02f", $count_walking / $count_total) : 0;
        $count_bicycle_p = $count_total > 0 ? sprintf("%0.02f", $count_bicycle / $count_total) : 0;

        // bank receipts
        $count_scooter_p = $count_total > 0 ? sprintf("%0.02f", $count_scooter / $count_total) : 0;
        $count_bus_p = $count_total > 0 ? sprintf("%0.02f", $count_bus / $count_total) : 0;

        // email and bank receipts
        $count_driving_p = $count_total > 0 ? sprintf("%0.02f", $count_driving / $count_total) : 0;
        $count_mycar_p = $count_total > 0 ? sprintf("%0.02f", $count_mycar / $count_total) : 0;

        $dataR = [
            [// average
                ["axis" => "Walk", "value" => $average_walking_p],
                ["axis" => "Bicycle", "value" => $average_bicycle_p], // Scooter / bike
                //["axis" => "Scooter/Bike", "value" => $average_bicycle_p],  // New version
                //["axis" => "Scooter","value" => $average_scooter_p],
                ["axis" => "Taxi", "value" => $average_driving_p], // Ridehare / Taxi
                //["axis" => "Rideshare/Taxi", "value" => $average_driving_p],  // New version
                ["axis" => "My Car", "value" => $average_mycar_p],
                ["axis" => "Bus", "value" => $average_bus_p], // Public transit
            //["axis" => "Public Transit", "value" => $average_bus_p],      // new version
            ],
            [// current user
                ["axis" => "Walk", "value" => $count_walking_p],
                ["axis" => "Bicycle", "value" => $count_bicycle_p], // bike and scooter are merged
                //["axis" => "Scooter/Bike", "value" => $count_bicycle_p], // new version
                //["axis" => "Bicycle","value" => $count_scooter_p],
                ["axis" => "Taxi", "value" => $count_driving_p], // Ridesare/Taxi
                //["axis" => "Rideshare/Taxi", "value" => $count_driving_p],  // new version
                ["axis" => "My Car", "value" => $count_mycar_p],
                ["axis" => "Bus", "value" => $count_bus_p], // Public transit
            //["axis" => "Public Transit", "value" => $count_bus_p], // new version
            ]
        ];


        $cards = Report::GetPageCardData($db, $member_id, 'activity-personality', $member_country);

        return [$dataR, ['cards' => $cards]];
    }

    private function getPersonalityAveragesQuery($country, $state, $city) {

        if (empty($country)) {
            // no country provided, get the US average
            $query = "SELECT * FROM emission_avrg_commute WHERE country = 'US' and state = '' and city = '' ;";
        } elseif (!empty($state) && !empty($city)) {
            // try to get as acurate as possible
            $query = "SELECT * FROM emission_avrg_commute WHERE country = '{$country}' and state = '{$state}' and city = '{$city}' ;";
        } elseif (!empty($state)) {
            // get the state wide average
            $query = "SELECT * FROM emission_avrg_commute WHERE country = '{$country}' and state = '{$state}' and city = '' ;";
        } else {
            // we only have the country
            $query = "SELECT * FROM emission_avrg_commute WHERE country = '{$country}' and state = '' and city = '' ;";
        }

        return $query;
    }

    private function processPersonalityAveragesData($res) {
        $total_average = 0;
        while ($f = pg_fetch_assoc($res)) {
            $radar_avrg[$f["mkey"]] = $f["gramskm_perc"];
            $total_average = $total_average + $f["gramskm_perc"]; // we know it is 100 but in case
        }

        return array($radar_avrg, $total_average);
    }

    private function getPersonalityAverages($db, $country, $state, $city) {

        $query = self::getPersonalityAveragesQuery($country, $state, $city);
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            return self::processPersonalityAveragesData($r);
        }

        // no results found, try to "zoom out"
        $query = self::getPersonalityAveragesQuery($country, $state, null);
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            return self::processPersonalityAveragesData($r);
        }

        // no results found, try to get the country wide averages
        $query = self::getPersonalityAveragesQuery($country, null, null);
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            return self::processPersonalityAveragesData($r);
        }

        // no results found, try to get the global data
        $query = self::getPersonalityAveragesQuery(null, null, null);
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            return self::processPersonalityAveragesData($r);
        }

        return false;
    }

    private function emissionsByDaysData($db, $member_id, $days, $start_date = "", $end_date = "", $member_country='') {

        /*
         * PREPARE THE GAS DATA
         */
        $filter_date = " AND time between  now()+'-$days days' AND now() ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " AND time >=  '$start_date' AND time < '$end_date' ";
        }
        $emiss_arr = [];
        $mysql_e = "SELECT sum(amount) AS amount,time::date FROM members_bankimport "
                . " WHERE member_id = $member_id AND  LOWER(category) ='gas' AND amount>0 "
                . $filter_date
                . "  GROUP by time::date ORDER BY time DESC";
        $re = pg_query($db, $mysql_e);
        if ($re && pg_num_rows($re)) {
            $itm_e = 0;
            while ($fe = pg_fetch_assoc($re)) {
                $gas_emission_value = 0;
                $gas_amount = $fe["amount"] * 0.01;
                $gas_date = $fe["time"];

                // $emiss_arr = array_fill_keys($gas_date, array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount));
                //array_push($gas_emission, array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount));
                // array_push($gas_emission, $emiss_arr);
                $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
            }
        }


        $data = [];

        $data_result = Report::travelTimeByDaysData($db, $member_id, $days, $start_date, $end_date, $member_country);

        $total_value = 0;
        $latest = 0;
        if (!empty($start_date) && !empty($end_date)) {
            $days = 1 + round((strtotime($end_date) - strtotime($start_date)) / 86400);
            $latest = 1;
        }
        for ($ic = $latest; $ic < $days; $ic++) {
            if (!empty($end_date)) {
                $new_date = date("Y-m-d", strtotime($end_date . "-$ic days"));
            } else {
                $new_date = date("Y-m-d", strtotime("-$ic days"));
            }

            $emission_value = 0;
            $gas_amount = 0;
            $gas_emission = 0;
            $merchant_name = "";
            $amount = 0;
//$gas_emission
            if (array_key_exists($new_date, $emiss_arr)) {
                $gas_amount = $emiss_arr[$new_date]["gas"];
                $gas_country = 'US';
                $ems_data = Report::getEmissionModel($gas_amount, 1, $gas_country);
                $gas_emission = $ems_data[0];
            }
            $emission_value = $gas_emission;

            if (array_key_exists($new_date, $data_result[0])) {
                $emission_value = $emission_value + $data_result[0][$new_date]["emission"];
                $amount = $data_result[0][$new_date]["amount"];
                $merchant_name = $data_result[0][$new_date][$new_date]["merchant_name"];
            }

            $total_value += $emission_value;
            array_push($data, array("date" => "$new_date", "value" => $emission_value, "gas" => $gas_amount, "gas_emission" => $gas_emission, "amount" => $amount, "merchant_name" => $merchant_name));
        }

        $cards = Report::GetPageCardData($db, $member_id, 'activity-emissions', $member_country);

        return [$data, ['total_value' => $total_value, 'cards' => $cards]];
    }

    /**
     * Change the function to get card data for all screens
     */
    private function GetPageCardData($db, $member_id, $screen_name, $member_country='') {

        $optionalMessageArr = [];
        $optionalMessageArr['message'] = '';
        $optionalMessageArr['icon'] = '';
        $optionalMessageArr['card_id'] = '0';

        switch ($screen_name) {
            case 'activity-emissions':
                $activity_screen = 4;
                break;
            case 'activity-time-traveled':
                $activity_screen = 3;
                break;
            case 'activity-monthly-spent':
                $activity_screen = 2;
                break;
            case 'activity-personality':
                $activity_screen = 5;
                break;
            default:
                $activity_screen = 0;
                break;
        }

        $cards = array();

        //$screen_name = 'activity-emissions';
        /*
          $query = "SELECT mn.msg, mn.expire, mn.added, et.category, et.icon ";
          $query .= " FROM members_notification mn ";
          $query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
          $query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
          $query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
          $query .= " and sc.status = 1 ";
          $query .= " and expire > now() ";
          $query .= " order by mn.added desc LIMIT 1; ";


        $query = "SELECT 0 AS card_id, mn.msg, et.category AS action, et.icon AS card_icon ,0 AS blog_id, 0 AS assign_id";
        $query .= " FROM members_notification mn ";
        $query .= " inner join email_trigger et on et.e_trigger = mn.trigger_key ";
        $query .= " inner join screen_cards sc on sc.trigger_id = mn.trigger_key ";
        $query .= " where sc.screen_name = '{$screen_name}' and mn.member_id = {$member_id} ";
        $query .= " and sc.status = 1 ";
        $query .= " and expire > now()  ";
        $query .= "  UNION ";

       
         * TEST QUERY -------------------------------------------
         
        $query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon,mc.blog_id, "
                . " ma.id AS assign_id, mc.*, ca.type AS card_action_type, ca.data AS card_action_data, ma.subscribe, ma.message "
                . " FROM members_card_assign ma "
                . " LEFT JOIN main_cards mc ON mc.id=ma.card_id "
                . " LEFT JOIN card_actions ca ON (ca.id=mc.card_action_id) "
                . "WHERE mc.activity_screen= {$activity_screen}  " // AND mc.button1_action == 'GOOFFERS'
                . ""
                . "AND ma.status = 1 AND mc.show_area = 2  "
                . "AND  mc.id=355;";
        $query = "";
        // END TEST QUERY  ---------------------------------------------


         */
        // do not need the union anymore 

        // Get user country
        if($member_country==''){
            $mysql_member_country = "SELECT country FROM members WHERE id = {$member_id}";
            $rec = pg_query($db, $mysql_member_country);
            if ($rec && pg_num_rows($rec)) {
                $rowc = pg_fetch_assoc($rec);
                $member_country= $rowc["country"];
            }
        }
                
        $query = "";

        $country_query = " AND mc.card_country  = '' ";
        if ($member_country !=''){
            $country_query = " AND mc.card_country IN ('', '$member_country')";
        }

        $query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon,mc.blog_id, "
                . " ma.id AS assign_id, mc.*, ca.type AS card_action_type, ca.data AS card_action_data, ma.subscribe, ma.message "
                . " FROM members_card_assign ma "
                . " LEFT JOIN main_cards mc ON mc.id=ma.card_id "
                . " LEFT JOIN card_actions ca ON (ca.id=mc.card_action_id) "
                . " WHERE mc.activity_screen= {$activity_screen}  " // AND mc.button1_action == 'GOOFFERS'
                . " AND ma.subscribe IS NULL "
                . " AND ma.status = 1 AND mc.show_area = 2  $country_query "
                . " AND ma.member_id= {$member_id} ORDER BY random() LIMIT 1 ";


        // get the message, icon, and action ( category )
        $re = pg_query($db, $query);
        if ($re && pg_num_rows($re)) {
            $row = pg_fetch_assoc($re);

            // defalut values to avoid nulls

            $default_to_string = array('msg', 'icon', 'category', 'card_action_type', 'card_action_data', 'message', 'subscribe');

            foreach ($default_to_string as $col) {
                if (empty($row[$col])) {
                    $row[$col] = "";
                }
            }

            $cards[] = array(
                'message' => $row["msg"],
                'icon' => $row['card_icon'],
                'action' => $row['action'],
                'button1_action' => $row['action'],
                'assign_id' => $row['assign_id'],
                'card_id' => $row['card_id'],
                'blog_id' => $row['blog_id'],
                'detail_data' => $row
            );
        }

        // old logic bellow
        /*
         * 
         *     $message = $fe["msg"];
          $icon = $fe["card_icon"];
          $action = $fe["action"];
          $card_id = $fe["card_id"];
         * 
         * 
          $query = "SELECT * FROM members_notification WHERE trigger_key IN ('ETRG0014','ETRG0007') AND member_id = $member_id"
          ." AND added between  now() + '-7 days' "
          . " AND now() ORDER BY added DESC LIMIT 1";

          $re = pg_query($db, $query);
          if ($re && pg_num_rows($re)) {
          //msg
          $row = pg_fetch_assoc($re);

          $optionalMessageArr['message'] = $row["msg"]; // "Sweet! You’ve spent almost 30 minutes less time on your commute this month!";
          $optionalMessageArr['icon'] ="fast-moving";
          }
         */

        return $cards;
    }

// end of emission page card

    private function getEmissionModel($gas_amount, $gas_number, $gas_country = 'US') {

// GAS MODEL ASSUMPTIONS

        $average_car_CO2_emissions = 168;
        $average_distance_travelled_pre_litre_of_petrol_km = 13;
        $average_price_per_litre_USD = 1.07;
        if ('SG' == $gas_country) {
            $average_price_per_litre_USD = 1.39;
        }


        $estimated_litres_of_fuel_purchased_litres = $gas_amount / $average_price_per_litre_USD;
        $estimated_distance_travelled_per_re_fuel_km = $estimated_litres_of_fuel_purchased_litres * $average_distance_travelled_pre_litre_of_petrol_km;

        $estimated_total_CO2_emissions_per_refuel = $estimated_distance_travelled_per_re_fuel_km * $average_car_CO2_emissions;
        $estimated_CO2_emissions_per_month = $estimated_total_CO2_emissions_per_refuel * $gas_number;
        $estimated_CO2_emissions_per_week = $estimated_CO2_emissions_per_month * 12 / 52;


        $data = [];

        $data[0] = $estimated_total_CO2_emissions_per_refuel;
        $data[1] = $estimated_CO2_emissions_per_month;
        $data[2] = $estimated_CO2_emissions_per_week;

        return $data;
    }

    /*
      Note that the travelTimeByDaysData generates data for emission plot
     */

    private function travelTimeByDaysData($db, $member_id, $days, $start_date = '', $end_date = '', $member_country) {

        $available_countries = array(
            'US' => array(
                'taxi_avg_start' => 3.5,
                'taxi_avg_fee_per_km' => 1.7,
                'taxi_avg_time_per_km' => 1.71,
                'scooter_avg_start' => 1,
                'scooter_avg_min' => 0.24,
                'gas_price_per_litre' => 1.07,
                'time_traveled_per_litre' => 22,
            ),
            'SG' => array(
                'taxi_avg_start' => 2.66,
                'taxi_avg_fee_per_km' => 0.47,
                'taxi_avg_time_per_km' => 1.29,
                'scooter_avg_start' => 1,
                'scooter_avg_min' => 0.1,
                'gas_price_per_litre' => 1.39,
                'time_traveled_per_litre' => 17,
            )
        );

        // get the user country
        $user_country = "US";
        $query = "select country from members where id = $1;";
        $r = pg_query_params($db, $query, array($member_id));
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                $user_country = $f['country'];
            }
        }

        if (!array_key_exists($user_country, $available_countries)) {
            $user_country = 'US';
        }


//PREPARE THE MODEL ARRAY 
        $mysql = "SELECT mkey,substr(lower(transport_mode),0,5) AS transport_mode, grams_km,passengers FROM emission_model";
        $model_data = [];
        $r = pg_query($db, $mysql);
        if ($r && pg_num_rows($r)) {
            $itm_count = 0;
            while ($f = pg_fetch_assoc($r)) {

                $model_line = array("mkey" => $f["mkey"], "transport_mode" => $f["transport_mode"], "grams_km" => $f["grams_km"], "passengers" => $f["passengers"]);
                $model_data[$f["transport_mode"]][] = $model_line;
            }
        }

        $data_result = [];

        $filter_date = " date_trunc('day', b.travel_date) > (current_date - ${days}) AND date_trunc('day', b.travel_date) < current_date AND ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " travel_date >=  '$start_date' AND travel_date < '$end_date' AND ";
        }

        // 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, c.name as merchant_name,  sum(b.cost) AS amount  ,sum(b.duration) AS sum_duration , sum(b.distance) AS sum_distance
                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
                " . $filter_date . "
                b.transport_provider_id > 0
                GROUP BY b.travel_date::date, c.category, c.name ORDER BY b.travel_date::date DESC "; // ${days} 
        
        //error_log($q); 
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            $itm_count = 0;
            while ($f = pg_fetch_assoc($r)) {
                // pick the category
                $category_substring = strtolower(substr($f["category"], 0, 4));
                $model_read = $model_data[$category_substring][0];

                $grams_km = $model_read["grams_km"];
                $passengers = $model_read["passengers"];

                //echo "<hr>";
                //print_r( $model_read );
                $emission = $f["sum_distance"] * $grams_km / $passengers;

                $detail_line = array("category" => $f["category"], "merchant_name" => $f["merchant_name"], "amount" => $f["amount"], "duration" => $f["sum_duration"], "distance" => $f["sum_distance"], "emission" => $emission);
                $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"]]["duration"] = $data_result[$f["travel_date"]]["duration"] + $f["sum_duration"];
                        $data_result[$f["travel_date"]]["amount"] = $data_result[$f["travel_date"]]["amount"] + $f["amount"];
                        $data_result[$f["travel_date"]]["distance"] = $data_result[$f["travel_date"]]["distance"] + $f["sum_distance"];
                        $data_result[$f["travel_date"]]["emission"] = $data_result[$f["travel_date"]]["emission"] + $emission;
                        $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"],
                        "duration" => $f["sum_duration"],
                        "amount" => $f["amount"],
                        "distance" => $f["sum_distance"],
                        "emission" => $emission,
                        $f["travel_date"] => [$detail_line]
                    );
                    $data_result[$f["travel_date"]] = $data_line;
                }
                $itm_count++;
            }
        }

        $gas_transactions = array();
        $end_days = intval($days) + 1;

        // extract taxi time spent from bank receipts
        $filter_date = " date_trunc('day', time) > (current_date - ${end_days}) AND date_trunc('day', time) < current_date ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " time >=  '$start_date' AND time < '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $query = "SELECT time::date ,category, CASE WHEN merchant_name !='' THEN merchant_name ELSE description END AS merchant_name , sum(amount) as amount
        FROM members_bankimport 
        WHERE category in ('Taxi', 'ScooterRental', 'RideShare', 'Gas') AND member_id = ${member_id} AND amount>0 AND 
        " . $filter_date . " 
        GROUP BY time::date, category, (CASE WHEN merchant_name !='' THEN merchant_name ELSE description END) ORDER BY time DESC;";//echo $query;exit;
        error_log($query);
        $r = pg_query($db, $query);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {

                if ($itm_count > 0 && $f['category'] == "RideShare") {
                    // we have email receipts, ignore rideshare transactions
                    continue;
                }

                $time_traveled = 0;
                if ($f['amount'] > 0) {
                    
                    if (in_array($f['category'], array('Taxi', 'RideShare'))) {
                        $time_traveled = (($f['amount'] / 100) - $available_countries[$user_country]['taxi_avg_start'] ) / $available_countries[$user_country]['taxi_avg_fee_per_km'] * $available_countries[$user_country]['taxi_avg_time_per_km'];
                        $distance = (($f['amount'] / 100) - $available_countries[$user_country]['taxi_avg_start'] ) / $available_countries[$user_country]['taxi_avg_fee_per_km'] * $available_countries[$user_country]['taxi_avg_time_per_km'];
                    } elseif ($f['category'] == 'ScooterRental') {
                        $time_traveled = ( ($f['amount'] / 100) - $available_countries[$user_country]['scooter_avg_start'] ) / $available_countries[$user_country]['scooter_avg_min'];
                        $distance = 0;
                    } elseif ($f['category'] == 'Gas') {
                        if (!array_key_exists($f['time'], $gas_transactions)) {
                            $gas_transactions[$f['time']] = 0;
                        }
                        $gas_transactions[$f['time']] += $f['amount'] / 100;
                    }
                }
                $detail_line = array("category" => $f["category"], "merchant_name" => $f["merchant_name"], "amount" => $f["amount"], "duration" => ceil($time_traveled), "distance" => 0, "emission" => 0);
                if (!array_key_exists($f['time'], $data_result)) {
                    $data_result[$f['time']] = array(
                        "date" => $f["time"],
                        "value" => 0,
                        "amount" => 0,
                        "duration" => 0,
                        "distance" => 0,
                        "emission" => 0
                    );
                }else{
                    $data_result[$f["time"]]["duration"] = $data_result[$f["time"]]["duration"] + ceil($time_traveled);
                    $data_result[$f["time"]]["value"] = $data_result[$f["time"]]["value"] + ceil($time_traveled);
                    $data_result[$f["time"]]["amount"] = $data_result[$f["time"]]["amount"] + $f["amount"];
                    $data_result[$f["time"]][$f["time"]][] = $detail_line;
                }
            }
        }
        
        if (count($gas_transactions) > 0) {

            // calculate the time traveled based on gas transactions
            if (count($gas_transactions) == 1) {
                // we have gas refills only in one day, split the amount by total days
                $time_per_day = reset($gas_transactions) / $available_countries[$user_country]['gas_price_per_litre'] * $available_countries[$user_country]['time_traveled_per_litre'] / $days;
            } else {
                // calculate the total gas purchased between first and last gas refill
                // and create an average spent by day and use that for all days
                error_log('gas_transactions' . json_encode($gas_transactions));
                $first_date = reset(array_keys($gas_transactions));
                $last_date = end(array_keys($gas_transactions));
                $total_amount = 0;
                foreach ($gas_transactions as $date => $amount) {
                    if ($date != $last_date) {
                        $total_amount += $amount;
                    }
                }

                // get the total minutes traveled
                $total_travel = $total_amount / $available_countries[$user_country]['gas_price_per_litre'] * $available_countries[$user_country]['time_traveled_per_litre'];

                // caluclate the the differece between refills
                $f_first_date = new DateTime($first_date);
                $f_last_date = new DateTime($last_date);

                $interval = date_diff($f_last_date, $f_first_date);
                $days_diff = $interval->format("%a");

                $time_per_day = $total_travel / $days_diff;
            }

            // create or update the result array with daily gas data

            for ($i = $days; $i > 0; $i--) {
                if (!empty($end_date)) {
                    $dt = date("Y-m-d", strtotime($end_date . "-$i days"));
                } else {
                    $dt = date("Y-m-d", strtotime("-$i days"));
                }

                if (!array_key_exists($dt, $data_result)) {
                    $data_result[$dt] = array(
                        "date" => $dt,
                        "value" => 0,
                        "amount" => 0,
                        "duration" => 0,
                        "distance" => 0,
                        "emission" => 0,
                    );
                }

                // set the value here
                $data_result[$dt]['value'] += intval($time_per_day);
            }
        }

        if (count($data_result) > 0) {
            // add zero values for the chart

            $total_value = 0;
            for ($i = $days; $i > 0; $i--) {
                if (!empty($end_date)) {
                    $dt = date("Y-m-d", strtotime($end_date . "-$i days"));
                } else {
                    $dt = date("Y-m-d", strtotime("-$i days"));
                }
                if (!array_key_exists($dt, $data_result)) {

                    $data_result[$dt] = array(
                        "date" => $dt,
                        "value" => 0,
                        "duration" => 0,
                        "amount" => 0,
                        "distance" => 0,
                        "emission" => 0,
                    );
                }

                // add value for the new area chart model, increase the value of each day
                $total_value += $data_result[$dt]['value'];
                $data_result[$dt]['added_value'] = $total_value;
            }

            ksort($data_result);
        }

        $cards = Report::GetPageCardData($db, $member_id, 'activity-time-traveled', $member_country);

        return [$data_result, ['cards' => $cards]];
    }

    /*

      private function DELETEemissionsByDaysData2($db, $member_id, $days) {

      $data = [];

      for($ic=0; $ic<$days; $ic++){
      $new_date = date('Y-m-d', strtotime("-$ic days"));
      array_push($data,array("date" => "$new_date", "value" => rand(0,50)));
      }

      return [$data, []];
      }

      private function DELETEemissionsByDaysData1($db, $member_id, $days) {


      $data = [

      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),
      ];

      return [$data, []];
      }

      private function DELETEtravelTimeByDaysData1($db, $member_id, $days) {

      $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 - ${days}) AND date_trunc('day', b.travel_date_end) < current_date AND
      b.transport_provider_id > 0
      GROUP BY b.travel_date::date, c.category ORDER BY b.travel_date::date DESC "; // ${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++;
      }
      }


      $optional_message = "Sweet! You’ve spent almost 30 minutes less time on your commute this month!";
      $icon = "fast-moving";

      $options_data = array('message' => $optional_message, 'icon' => $icon);

      return [$data_result, $options_data];
      }
     */

    private function spendingByCategoryForDays($db, $member_id, $days, $include_items, $start_date = "", $end_date = "", $include_items_values = true, $member_country='') {
        $result = [
            'RideShare' => 0,
            'Taxi' => 0,
            //'Scooter' => 0,
            'Bus' => 0,
            'Subway' => 0,
            'Gas' => 0,
            'Other' => 0
        ];
        $report_categories = self::REPORT_CATEGORIES;

        //$member_id == 3 ||
        if ($member_id == 13) {
            $days = 90; // DEBUG
        }

        $fixDays = $days + 1;

        $filter_date = " date_trunc('day', time) > (current_date - ${fixDays}) AND date_trunc('day', time) < current_date ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " time >=  '$start_date' time <  '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        // Credit card transations
        $q = "SELECT category,count(*) AS count, sum(amount::decimal)/100 AS amount FROM members_bankimport ";
        $q .= " WHERE member_id=${member_id} AND LOWER(category) IN (SELECT LOWER(category) FROM activity_listcategory) AND amount>0 AND ";
        $q .= $filter_date;
        $q .= " GROUP BY category";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                if (array_key_exists($f['category'], $report_categories)) {
                    $key = $report_categories[$f['category']];
                    $result[$key] += $f["amount"];
                } else {
                    $result['Other'] += $f["amount"];
                }
            }
        }
        // E-mail receipts
        $filter_date = " date_trunc('day', b.travel_date) > (current_date - ${fixDays}) AND date_trunc('day', b.travel_date) < current_date ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " travel_date >=  '$start_date' AND travel_date < '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $q = "SELECT c.category,count(*) AS count, sum(b.cost) AS amount ";
        $q .= " FROM trackedemail_item a, parsedemail_item b, transport_providers c ";
        $q .= " 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";
        $q .= $filter_date;
        $q .= " GROUP BY c.category";
        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                if (array_key_exists($f['category'], $report_categories)) {
                    $key = $report_categories[$f['category']];
                    $result[$key] += $f["amount"];
                } else {
                    $result['Other'] += $f["amount"];
                }
            }
        }
        // We do not check for any duplicates!
        $res = [
            [
                'type' => 'RideShare',
                'spent' => sprintf("%0.2f", $result['RideShare'])
            ],
            [
                'type' => 'Taxi',
                'spent' => sprintf("%0.2f", $result['Taxi'])
            ],
            /*
              [
              'type' => 'Scooter',
              'spent' => sprintf("%0.2f",$result['Scooter'])
              ],
             */
            [
                'type' => 'Bus',
                'spent' => sprintf("%0.2f", $result['Bus'])
            ],
            [
                'type' => 'Subway',
                'spent' => sprintf("%0.2f", $result['Subway'])
            ],
            [
                'type' => 'Gas',
                'spent' => sprintf("%0.2f", $result['Gas'])
            ],
            [
                'type' => 'Other',
                'spent' => sprintf("%0.2f", $result['Other'])
            ]
        ];
        $options = [];
        if ($include_items) {
            list($res, $options) = Report::spendingByCategoryForDaysItems($db, $member_id, $days, $res, $report_categories, $start_date, $end_date, $include_items_values, $member_country);
        }
        return [$res, $options];
    }

    private function spendingByCategoryForDaysItems($db, $member_id, $days, $result, $report_categories, $start_date = "", $end_date = "", $include_items_values = true, $member_country='') {

        $items = [];
        $spent = [];
        $latest_tip = [];
        foreach ($report_categories as $key => $category) {
            $items[$category] = [];
            $spent[$category] = 0;
        }
        $spent['Other'] = 0;

        $days++;

        $items['Other'] = [];
        $filter_date = " date_trunc('day', time) > (current_date - ${days}) AND date_trunc('day', time) < current_date ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " time >=  '$start_date' AND time < '$end_date' ";
            $days = 1 + round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $q = "SELECT * FROM ";
        // Credit card transations
        $q .= "(SELECT mb.id,'0' AS trackedemail_item_id, import_id, date_trunc('seconds', time) AS travel_date,";
        $q .= "'0' AS duration,round(amount::decimal/100,2) as cost,currency, description, '0' AS transport_provider_id,";
        $q .= "merchant_name AS transport_provider_name,mb.category,provider_category, 'f' AS advice_processed, NULL as flag,";
        $q .= "'0' AS surge_price, '0' AS cheaper_alternative ";
        $q .= " FROM members_bankimport mb";
        $q .= " inner join activity_listcategory alc on LOWER(alc.category) = lower(mb.category)  ";
        $q .= " WHERE member_id=${member_id} AND  mb.amount>0 AND ";
        $q .= $filter_date;
        //$q.= " ORDER BY time DESC"; // LIMIT ?

        $q .= " UNION ";
        // E-mail receipts
        $filter_date = "  date_trunc('day', pi.travel_date) > (current_date - ${days}) AND date_trunc('day', pi.travel_date) < current_date  ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " pi.travel_date >=  '$start_date' AND pi.travel_date < '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $q .= " SELECT  pi.id, pi.trackedemail_item_id, null as import_id, pi.travel_date, pi.duration, ";
        $q .= " pi.cost, pi.cost_raw as currency, tp.name as description, pi.transport_provider_id, ";
        $q .= " tp.name AS transport_provider_name, tp.category,  tp.category as provider_category, ";
        $q .= " pi.advice_processed, ar.flag, ";
        $q .= " CASE WHEN sp.id IS NULL THEN '0' ELSE '1' END AS surge_price, ";
        $q .= " CASE WHEN (pc.id IS NOT NULL AND pc.cost>pc.average) THEN ROUND(pc.average/pc.cost,2) ELSE 0 END AS cheaper_alternative ";

        $q .= " FROM parsedemail_item pi ";

        $q .= " inner join trackedemail_item ti on ti.id = pi.trackedemail_item_id and ti.member_id = ${member_id}  ";
        $q .= " inner join transport_providers tp on tp.id = pi.transport_provider_id ";
        $q .= " LEFT JOIN parsedemail_item_advice_result ar ON (ar.parsedemail_item_id=pi.id) ";
        $q .= " LEFT JOIN trip_surge_price sp ON (sp.data_source_id=pi.id AND sp.data_source=1) ";
        $q .= " LEFT JOIN trip_price_comparison pc ON (pc.data_source_id=pi.id AND pc.data_source=1) ";

        $q .= " where pi.dup_id IS NULL AND ";
        $q .= $filter_date;
        // end email receipts
        $q .= ") AS u ORDER BY u.travel_date DESC";


        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {

            while ($f = pg_fetch_assoc($r)) {

                // get the transaction tips here
                $f['inline_tips'] = Report::getTransactionTips($f);

                if (count($f['inline_tips']) > 0) {
                    // check if this is the most recent transaction with inline tip
                    $trip_date = strtotime($f['travel_date']);

                    if (!isset($latest_tip[$f['category']])) {
                        $latest_tip[$f['category']] = $trip_date;
                    }

                    if ($latest_tip[$f['category']] < $trip_date) {
                        $latest_tip[$f['category']] == $trip_date;
                    }
                }

                if (array_key_exists($f['category'], $report_categories)) {
                    $key = $report_categories[$f['category']];
                    $items[$key][] = $f;
                    $spent[$key] += $f['cost'];
                } else {
                    $items['Other'][] = $f;
                    $spent['Other'] += $f['cost'];
                }
            }
        }

        foreach ($items as $key => $val) {
            for ($i = 0; $i < count($result); $i++) {
                if ($result[$i]['type'] == $key) {

                    if ($include_items_values) {
                        $result[$i]['items'] = $val;
                    }
                    $result[$i]['spent'] = sprintf("%0.02f", $spent[$key]);
                    $result[$i]['latest_tip_time'] = $latest_tip[$key];
                    break;
                }
            }
        }
        /* 'advice_processed' => 'TODO',
          'flag' => 'TODO', */
        $options = [
            'options' => [
                'surge_price',
                'cheaper_alternative'
            ],
            'surge_price' => [
                'text' => 'Surge pricing',
                'style' => [
                    "color" => "#0000ff",
                    "font-weight" => "normal"
                ],
                'popup' => [
                    'text' => 'Try to avoid travel during rush hour if possible, or maybe try a scooter.',
                    'icon' => 'piggy',
                    'style' => [
                        "color" => "#78849E",
                        "background-color" => "#000000",
                        "font-weight" => "normal"
                    ]
                ]
            ],
            'cheaper_alternative' => [
                'text' => 'Cheaper alternative',
                'style' => [
                    "color" => "#009900",
                    "font-weight" => "normal"
                ]
            ]
        ];

        $options['cards'] = Report::GetPageCardData($db, $member_id, 'activity-monthly-spent', $member_country);

        // 10% off with Float
        return [$result, $options]; // This needs UI update
        //return $result;
    }

    private function getCategoryItems($db, $member_id, $days, $category, $start_date = "", $end_date = "") {

        $reversed_categories = [];
        foreach (self::REPORT_CATEGORIES as $key => $val) {
            $reversed_categories[$val] = $key;
        }

        if (array_key_exists($category, $reversed_categories)) {
            $category = $reversed_categories[$category];
            $category_filter = "LOWER(tp.category) = lower('${category}')";
        } else {
            $category = "other";
            $exclude_values = array_values($reversed_categories);
            $exclude_cats = [];
            foreach ($exclude_values as $cat) {
                $exclude_cats[] = strtolower($cat);
            }

            $exclude_cats = implode("','", $exclude_cats);

            $category_filter = " LOWER(tp.category) NOT IN ( '${exclude_cats}' ) ";
        }
        $items = [];
        $spent = 0;
        $latest_tip = [];

        $days++;

        $filter_date = " date_trunc('day', time) > (current_date - ${days}) AND date_trunc('day', time) < current_date ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " time >=  '$start_date' AND time < '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $q = "SELECT * FROM ";
        // Credit card transations
        $q .= "(SELECT tp.id,'0' AS trackedemail_item_id, import_id, date_trunc('seconds', time) AS travel_date,";
        $q .= "'0' AS duration,round(amount::decimal/100,2) as cost,currency, description, '0' AS transport_provider_id,";
        $q .= "merchant_name AS transport_provider_name, tp.category,provider_category, 'f' AS advice_processed, NULL as flag,";
        $q .= "'0' AS surge_price, '0' AS cheaper_alternative ";
        $q .= " FROM members_bankimport tp";
        $q .= " inner join activity_listcategory alc on LOWER(alc.category) = lower(tp.category)  ";
        $q .= " WHERE member_id=${member_id} AND ${category_filter} AND tp.amount>0 AND ";
        $q .= $filter_date;
        //$q.= " ORDER BY time DESC"; // LIMIT ?

        $q .= " UNION ";
        // E-mail receipts
        $filter_date = "  date_trunc('day', pi.travel_date) > (current_date - ${days}) AND date_trunc('day', pi.travel_date) < current_date  ";
        if (!empty($start_date) && !empty($end_date)) {
            $filter_date = " pi.travel_date >=  '$start_date' AND pi.travel_date <  '$end_date' ";
            $days = round((strtotime($end_date) - strtotime($start_date)) / 86400);
        }
        $q .= " SELECT  pi.id, pi.trackedemail_item_id, null as import_id, pi.travel_date, pi.duration, ";
        $q .= " pi.cost, pi.cost_raw as currency, tp.name as description, pi.transport_provider_id, ";
        $q .= " tp.name AS transport_provider_name, tp.category,  tp.category as provider_category, ";
        $q .= " pi.advice_processed, ar.flag, ";
        $q .= " CASE WHEN sp.id IS NULL THEN '0' ELSE '1' END AS surge_price, ";
        $q .= " CASE WHEN (pc.id IS NOT NULL AND pc.cost>pc.average) THEN ROUND(pc.average/pc.cost,2) ELSE 0 END AS cheaper_alternative ";

        $q .= " FROM parsedemail_item pi ";

        $q .= " inner join trackedemail_item ti on ti.id = pi.trackedemail_item_id and ti.member_id = ${member_id}  ";
        $q .= " inner join transport_providers tp on tp.id = pi.transport_provider_id ";
        $q .= " LEFT JOIN parsedemail_item_advice_result ar ON (ar.parsedemail_item_id=pi.id) ";
        $q .= " LEFT JOIN trip_surge_price sp ON (sp.data_source_id=pi.id AND sp.data_source=1) ";
        $q .= " LEFT JOIN trip_price_comparison pc ON (pc.data_source_id=pi.id AND pc.data_source=1) ";

        $q .= " where pi.dup_id IS NULL AND ${category_filter} AND ";
        $q .= $filter_date;
        // end email receipts
        $q .= ") AS u ORDER BY u.travel_date DESC";

        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {

            while ($f = pg_fetch_assoc($r)) {

                // get the transaction tips here
                $f['inline_tips'] = Report::getTransactionTips($f);
                $items[] = $f;
                $spent += $f['cost'];
            }
        }

        $options = [
            'options' => [
                'surge_price',
                'cheaper_alternative'
            ],
            'surge_price' => [
                'text' => 'Surge pricing',
                'style' => [
                    "color" => "#0000ff",
                    "font-weight" => "normal"
                ],
                'popup' => [
                    'text' => 'Try to avoid travel during rush hour if possible, or maybe try a scooter.',
                    'icon' => 'piggy',
                    'style' => [
                        "color" => "#78849E",
                        "background-color" => "#000000",
                        "font-weight" => "normal"
                    ]
                ]
            ],
            'cheaper_alternative' => [
                'text' => 'Cheaper alternative',
                'style' => [
                    "color" => "#009900",
                    "font-weight" => "normal"
                ]
            ]
        ];

        return [$items, $spent];
    }

    private function getTransactionTips($transaction) {

        $found_tips = array();

        // list all the possible triggers here
        // the key of the array is the transport provider id, -1 applies to all transactions
        $triggers_list = array(
            '-1' => array('SurgePriceInlineTip', 'CheaperAlternativeInlineTip'),
            'GRAB' => array('GrabSmallTripInlineTip'),
            'GOJEK' => array('GojekSmallTripInlineTip'),
            'COMFORTDELGRO' => array('ComfortSmallTripInlineTip', 'ComfortLateNightInlineTip',
                'ComfortEarlyMorningInlineTip', 'ComfortPeekPeriodInlineTip', 'ComfortCardPaySurgeInlineTip')
        );

        // get all possible triggers for this transaction
        $triggers = $triggers_list['-1'];
        if (array_key_exists(strtoupper($transaction['transport_provider_name']), $triggers_list)) {
            $triggers = array_merge($triggers, $triggers_list[strtoupper($transaction['transport_provider_name'])]);
        }

        foreach ($triggers as $trigger) {

            // get and instantiate the class for this trigger
            $cond_class = new $trigger($transaction);

            // apply the condition and get the message if required
            if ($cond_class->applyCondition()) {
                $found_tips[] = $cond_class->getMessage();
            }
        }

        return $found_tips;
    }

}

class InlineTipBase {

    public $transaction;
    public $title;
    public $text;

    function __construct($transaction) {
        $this->transaction = $transaction;
    }

    public function getMessage() {

        //TODO: improve here
        return array(
            'title' => $this->title,
            'text' => $this->text,
        );
    }

    public function applyCondition() {
        return;
    }

}

class SurgePriceInlineTip extends InlineTipBase {

    public $title = "Surge pricing";
    public $text = "Try to avoid travel during rush hour if possible, or maybe try a scooter.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        if ($this->transaction['surge_price'] == '1') {
            return true;
        }
        return false;
    }

}

class CheaperAlternativeInlineTip extends InlineTipBase {

    public $title = "Cheaper Alternative";
    public $text = "We found some cheaper options for your recent trip.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        if (floatval($this->transaction['cheaper_alternative']) > 0) {

            // we can alter the text here to show a percentage of cheaper alternative

            return true;
        }
        return false;
    }

}

class GrabSmallTripInlineTip extends InlineTipBase {

    public $title = "Money saving tip";
    public $text = "Avoid short rideshare rides, you always get charged a minimum fee of $4 every time you jump in a Grab, no matter where you go.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        if (strtoupper($this->transaction['transport_provider_name']) == 'GRAB' && floatval($this->transaction['cost']) < 8) {
            return true;
        }
        return false;
    }

}

class GojekSmallTripInlineTip extends InlineTipBase {

    public $title = "Money saving tip";
    public $text = "Avoid short rideshare rides, you always get charged a minimum fee of $6 every time you jump in a GoJek, no matter where you go.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();
        if (strtoupper($this->transaction['transport_provider_name']) == 'GOJEK' && floatval($this->transaction['cost']) < 8) {
            return true;
        }
        return false;
    }

}

class ComfortSmallTripInlineTip extends InlineTipBase {

    public $title = "Money saving tip";
    public $text = "Avoid short taxi rides, you always get charged a minimum fee of $6 every time you jump in a Comfort Taxi, no matter where you go.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        if (strtoupper($this->transaction['transport_provider_name']) == 'COMFORTDELGRO' && floatval($this->transaction['cost']) < 8) {
            return true;
        }
        return false;
    }

}

class ComfortLateNightInlineTip extends InlineTipBase {

    public $title = "Late night charge";
    public $text = "Watch out for late night charges, between midnight and 5:59AM you’ll be stung with a surcharge of 50% of the metered fare with a Comfort Taxi.";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        // check the transaction time
        $time = date("H:i:s", strtotime($this->transaction['travel_date']));
        $time = DateTime::createFromFormat('H:i:s', $time);

        $start = DateTime::createFromFormat('H:i a', "12:00 am");
        $end = DateTime::createFromFormat('H:i a', "5:59 am");

        // only comfort email transactions between 12am and 6am
        if (strtoupper($this->transaction['transport_provider_name']) == 'COMFORTDELGRO' && intval($this->transaction['trackedemail_item_id']) > 0 && $time > $start && $time < $end) {
            return true;
        }
        return false;
    }

}

class ComfortEarlyMorningInlineTip extends InlineTipBase {

    public $title = "Late night charge";
    public $text = "Watch out for peak period charges, between 6AM and 9:29AM you’ll be stung with a surcharge 25% of the metered fare with a Comfort Taxi";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        // check the transaction time
        $time = date("H:i:s", strtotime($this->transaction['travel_date']));
        $time = DateTime::createFromFormat('H:i:s', $time);

        $start = DateTime::createFromFormat('H:i a', "6:00 am");
        $end = DateTime::createFromFormat('H:i a', "9:39 am");

        // only comfort email transactions between 6am and 9:40 am
        if (strtoupper($this->transaction['transport_provider_name']) == 'COMFORTDELGRO' && intval($this->transaction['trackedemail_item_id']) > 0 && $time > $start && $time < $end) {
            return true;
        }
        return false;
    }

}

class ComfortPeekPeriodInlineTip extends InlineTipBase {

    public $title = "Late night charge";
    public $text = "Watch out for peak period charges, between 6PM and 11:59PM you’ll be stung with a surcharge 25% of the metered fare with a Comfort Taxi";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        // check the transaction time
        $time = date("H:i:s", strtotime($this->transaction['travel_date']));
        $time = DateTime::createFromFormat('H:i:s', $time);

        $start = DateTime::createFromFormat('H:i a', "6:00 pm");
        $end = DateTime::createFromFormat('H:i a', "11:59 pm");

        // only comfort email transactions between 12am and 6am
        if (strtoupper($this->transaction['transport_provider_name']) == 'COMFORTDELGRO' && intval($this->transaction['trackedemail_item_id']) > 0 && $time > $start && $time < $end) {
            return true;
        }
        return false;
    }

}

class ComfortCardPaySurgeInlineTip extends InlineTipBase {

    public $title = "Surcharge Alert";
    public $text = "Be careful how you pay, credit and charge card payments get hit with a 10% surcharge in a Comfort Taxi";

    public function __construct($transaction) {
        parent::__construct($transaction);
    }

    public function applyCondition() {
        parent::applyCondition();

        // only comfort email transactions between 12am and 6am
        if (strtoupper($this->transaction['transport_provider_name']) == 'COMFORTDELGRO' && intval($this->transaction['trackedemail_item_id']) == 0) {
            return true;
        }
        return false;
    }

}

// vi:ts=2