<?php

echo "[" . date("Y-m-d H:i:s") . "] member_prepare_notification job is starting.\n";

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


// We will not limit the SQL we will exhaust the allowed memory
// we will process data in ${hard_limit} batches and hopefully catch up
$hard_limit = 500000;
// GPS coordinate precision to assume the same location
$precision = 3;

$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);

$db_host = $savvyext->cfgReadChar('database_replica.host');
$db_name = $savvyext->cfgReadChar('database_replica.name');
$db_user = $savvyext->cfgReadChar('database_replica.user');
$db_pass = $savvyext->cfgReadChar('database_replica.pass');
$db_port = $savvyext->cfgReadLong('database_replica.port');
$readOnlyReplicaConnstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$readOnlyReplicaConn = pg_connect($readOnlyReplicaConnstr);
include 'notifications_functions.php';

/*
  LEFT us refesh the triggertable
 */

foreach ($trigger_query AS $key => $lrow) {
    $system_id = $key;
    $mysql = $lrow["query"];
    $message = $lrow["message"];
    $active = $lrow["active"];
    $category = $lrow["cat"];
    $dynamic_key = $lrow["dynamic_key"];

    $send_mail = $lrow["send_mail"];
    $send_notification = $lrow["send_notification"];
    $icon = $lrow["icon"];


    $sq2 = "SELECT * FROM email_trigger WHERE e_trigger='$system_id'";
    $r2 = pg_query($readOnlyReplicaConn, $sq2);
    if ($r2 && pg_num_rows($r2) == 0) {

        $template_name = trim(strtolower($system_id . ".html"));
        $values = [
            "e_trigger" => "" . $system_id . "",
            "category" => "" . $category . "",
            "message" => pg_escape_string($message),
            "action_detail" => pg_escape_string($message),
            "template_name" => $template_name,
            "dynamic_key" => $dynamic_key,
            "status" => $active,
            "override_text" => "1",
            "expiration" => "EXP00000",
            "icon" => $icon,
        ];

        $values = array_filter($values, 'strlen');
        $q = "INSERT INTO email_trigger ";
        $q .= " (" . implode(", ", array_keys($values)) . ")";
        $q .= " VALUES ('" . implode("', '", $values) . "') ";
        // echo $q;
        $rs = pg_query($conn, $q);
    } else {
        $template_name = trim(strtolower($system_id . ".html"));
        /*    $values = [
          "category" => "" . $category . "",
          "message" => pg_escape_string($message),
          "template_name" => $template_name,
          "dynamic_key" => $dynamic_key,
          "send_mail" => $send_mail,
          "send_notification" => $send_notification,
          "status" => $active
          ];
         */
        $values = [
            "message" => pg_escape_string($message),
            "template_name" => $template_name,
            "dynamic_key" => $dynamic_key
        ];

        //echo "jcjcjcjcjjcc";
        $msqUpdt = "";
        $ick = 0;
        foreach ($values as $key => $val) {
            if ($ick > 0) {
                $msqUpdt .= ",";
            }
            $msqUpdt .= " $key = '" . $val . "'";
            $ick++;
        }

        if ($ick > 0) {

            $q = "UPDATE email_trigger SET " . $msqUpdt;
            $q .= " WHERE e_trigger ='$system_id'  ";

            // do not update all
            // echo $q;
            // $rs = pg_query($conn, $q);
        }
    }
}

//---------------------ATTEND TO THE CAR POOL NOTIFIFICATION  ETRG0020 ----------------------------------------
/* savvy=> SELECT * FROM members_trips WHERE member_id =1 LIMIT 1;
  id | member_id | trip_name  |                  trip_from                  |               trip_to               | country |      trip_date      | status |           added            | color
  ----+-----------+------------+---------------------------------------------+-------------------------------------+---------+---------------------+--------+----------------------------+--------
  1 |         1 | Trip 62236 | 4201 Defoors Fram Trail, Powder Springs, GA | 3500 Riverwood parkway, Atlanta, GA | US      | 2019-12-12 11:30:00 |      1 | 2019-12-16 00:10:33.354752 | 6464e2
  (1 row)
 */

$mysql_trip = "SELECT id AS trip_id, * FROM members_trips WHERE member_id > 0 AND now()+'15 minutes' < trip_date AND trip_date > now()+ '-30 minutes'   AND notify = 0 LIMIT 10";

$r = pg_query($conn, $mysql_trip);

$acc = array();
if ($r && pg_num_rows($r)) {

    while ($f = pg_fetch_assoc($r)) {
        $member_id = $f["member_id"];
        $trip_id = $f["trip_id"];

        $trip_from = $f["trip_from"];
        $trip_to = $f["trip_to"];
        $trip_date = $f["trip_date"];
        $trip_name = $f["trip_name"];

        $sql_up = "UPDATE members_trips SET  notify =notify + 1 WHERE id =  $trip_id AND member_id = $member_id ";
        pg_query($conn, $sql_up);

        $extension_call = true;
        if ($extension_call == true) {
            $outX = array();
            $x = array();
            //$member_id = 1;
            $x["pid"] = 0;
            $x["member_id"] = $member_id;
            $x["notice_type"] = "INSTANT";
            $x["mmode"] = "AUTO";
            $x["trigger_key"] = "ETRG0020";
            $x["msg"] = "You have an upcoming trip $trip_name today at $trip_date to $trip_to";
            $x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
            //print_r($x);
            Fextension_call($x, $outX);
        }
    }
}

// ------------------------------------------------------------------- ----------------------------------------
/*
 *       
 *     "ETRG0007" => array(
  "query" => "SELECT ma.*,m.min_budget,(ma.spend_14 - 2*ma.spend_7) AS week_increase from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE m.decision_group IN('A1NUS25','USSF001') AND m.id NOT IN (SELECT member_id FROM members_carpool) ORDER BY m.notification_test ASC LIMIT 500",
  "message" => "Deal Carpool Try carpooling and save $X and Y emissions this month",
 *               "html_body" => pg_escape_string($htm_template),

  savvy=> select * from email_trigger;
  id | e_trigger |  category  |              action_detail               |  template_name  | status |           added            |          updated
  ----+-----------+------------+------------------------------------------+-----------------+--------+----------------------------+----------------------------
  2 | ETRG0002  | GOACTIVITY | Put Details Here                         | dealtmp022.html |      1 | 2020-02-18 15:06:46.721542 | 2020-02-18 15:06:46.721542
  3 | ETRG0003  | GOACTIVITY | Put Details Here                         | dealtmp033.html |      1 | 2020-02-18 15:06:46.725502 | 2020-02-18 15:06:46.725502
  4 | ETRG0004  | GOACTIVITY | Put Details Here                         | dealtmp044.html |      1 | 2020-02-18 15:06:46.729315 | 2020-02-18 15:06:46.729315
  5 | ETRG0005  | GOACTIVITY | Put Details Here                         | dealtmp055.html |      1 | 2020-02-18 15:06:46.733011 | 2020-02-18 15:06:46.733011
  1 | ETRG0001  | GOACTIVITY | <p><strong>Put Details Here</strong></p>+| dealtmp011.html |      1 | 2020-02-18 15:06:46.716103 | 2020-02-18 15:06:46.716103
  |           |            |                                          |                 |        |                            |
  (5 rows)


  $trigger_query = array(
  "SURGESAVE" => array(
  "query" => $mysql,
  "message" => "You are spending too much. Let's save money",
  "active" => 1,
  "cat" => 'GOACTIVITY',
  "dynamic_key" => "",
  "start_save" => false
  ),
 */

function Fextension_call($in, &$out) {
    //  logToFl("Merchant_Name count->" . $in["merchant_name"]);
    global $savvyext;
    $out = $savvyext->savvyext_api($in);
    $ret = $out["retval"];
    return $ret;
}

// clear out the expired list first
$sq2 = "UPDATE members_notification SET status =2 WHERE status = 1 AND expire < now() AND notice_type!='INSTANT'";
$r2 = pg_query($conn, $sq2);


foreach ($trigger_query AS $key => $lrow) {
    $system_id = $key;
    $mysql = $lrow["query"];
    $message = $lrow["message"];
    $active = $lrow["active"];
    $extension_call = $lrow["start_save"];

    if (1 == $active && $system_id != '') {
        //    echo $system_id. " - <hr> - ".$mysql;
        $mysql_up = "UPDATE email_trigger SET last_run = now() WHERE e_trigger = '$system_id'";
        pg_query($conn, $mysql_up);

        $r = pg_query($conn, $mysql);

        $acc = array();
        if ($r && pg_num_rows($r)) {
            //      echo $system_id. " - <hr> - ".$mysql;
            //    echo 'ddhdhdhd';
            while ($f = pg_fetch_assoc($r)) {
                $member_id = $f["member_id"];

                $extension_call = false; // does not matter - dontset to send by =default.
                if (array_key_exists($member_id, $acc)) {
                    // echo "here " . $member_id;
                    // nothing - we dont want duplicate 
                } else {

                    $sq2 = "SELECT * FROM members_notification WHERE member_id=$member_id AND trigger_key ='$system_id' AND status IN (1)";
                    $r2 = pg_query($readOnlyReplicaConn, $sq2);
                    $x["pid"] = 0;
                    $x["member_id"] = $member_id;
                    $x["notice_type"] = "GENALERT";
                    $acc[$member_id] = $member_id;
                    if ($r2 && pg_num_rows($r2) == 0) {
                        $x["msg"] = $message;
                        $extension_call = true;
                        // modify the message here 

      
                        if ($system_id == "SURGESAVE") {
                            $extension_call = false; // turn message off
                            //    print_r($x);
                            if ($f["surge_price"] > 0 && $f["cheaper_alternative"] > 0) {
                                $x["msg"] = "We have found a way to save time and money for you";
                            }

                            if ($f["surge_price"] > 0 && $f["cheaper_alternative"] == 0) {
                                $x["msg"] = "We have found a way to save time for you";
                            }
                            $extension_call = true;
                        }

                        if ($system_id == "ETRG0002") {
                            $percent_less = 100 - ($f["spend_7"] / $f["min_budget"]) * 100;
                            $x["msg"] = "Good Job, you're " . round($percent_less, 2) . "% below your budget. Let's save more.";
                        }

                        if ($system_id == "ETRG0004") {
                            $rideshare_30 = $f["rideshare_30"] * 0.01;
                            $extension_call = false; // turn message off
                            if ($rideshare_30 > 0) {
                                $x["msg"] = "We can see you spent $ $rideshare_30 on rideshare this month. Here's how Float can save you money";
                                $extension_call = true; // turn message off
                            }
                        }

                        if ($system_id == "ETRG0005") {
                            $extension_call = false;
                            $x["msg"] = getAccountBenefit($readOnlyReplicaConn, $member_id, $extension_confirm_call);
                            if ($x["msg"] != '' && $extension_confirm_call == true) {
                                $extension_call = $extension_confirm_call;
                            }
                        }


                        if ($system_id == "ETRG0006") {
                            $percent_increase = round($f["week_increase"] / $f["spend_7"]) * 100;
                            $extension_call = false;
                            if ($percent_increase > 0) {
                                $x["msg"] = "Your spending is up $percent_increase% this week. Would you like to create a budget?";
                                $extension_call = true; // turn message off
                            }
                        }

                        if ($system_id == "ETRG0007") {
                            //$percent_increase = round($f["week_increase"] / $f["spend_7"], 2) * 100;
                            $extension_call = false; // turn message off
                            $emmission_save = rand(100, 200);
                            $emmission_value = rand(100, 200);
                            $country = $f["country"];

                            $week_increase = $f["week_increase"] * 0.01;
                            if ($week_increase > 0) {

                                $emmission_save = round($week_increase, 2);
                                $emisson = getEmissionModel($week_increase, 1, $country);
                                $emmission_value = round($emisson[0], 2);

                                $x["msg"] = "Try carpooling and save $$emmission_save and $emmission_value grams of emissions this month";
                                $extension_call = true;
                            }

                            //"Your spending is up $percent_increase% this week. Let's create a budget together";
                        }

                        if ($system_id == "ETRG0014") {
                            $extension_call = false; // turn message off
                            /*
                              id  | member_id |          updated           | spend_7 | rideshare_30 | spend_14 | gas_7 | gas_30 | gas_14 | min_budget | week_increase
                              -----+-----------+----------------------------+---------+--------------+----------+-------+--------+--------+------------+---------------
                              191 |       202 | 2020-05-06 11:20:04.339684 |   92989 |        75433 |   162604 | 42949 | 174047 |  77361 |      27500 |         23374
                              117 |        76 | 2020-05-06 11:05:05.830233 |    1995 |            0 |     1995 |     0 |      0 |      0 |          0 |          1995
                             */
                            $week_increase = $f["week_increase"] * 0.01;

                            if ($week_increase >= 20) {
                                $emmission_inc = emissonIncrease($member_id, 7);
                                if ($emmission_inc > 0) {
                                    $x["msg"] = "Your emissions output is $emmission_inc% higher this week, lets see how we can lower it";
                                    $extension_call = true;
                                }
                            }
                        }

                        if ($system_id == "ETRG0015") {
                            $extension_call = false; // turn message off
                            /*
                              id  | member_id |          updated           | spend_7 | rideshare_30 | spend_14 | gas_7 | gas_30 | gas_14 | min_budget | week_decrease
                              -----+-----------+----------------------------+---------+--------------+----------+-------+--------+--------+------------+---------------
                              194 |       186 | 2020-04-28 17:45:03.507661 |  172933 |       116230 |   315990 | 66650 | 215045 | 128172 |          0 |         29876
                             */
                            $week_decrease = $f["week_decrease"] * 0.01;

                            if ($week_decrease >= 20) {
                                $less_minutes = getTripMinutes($member_id, 7);
                                if ($less_minutes > 0) {
                                    $x["msg"] = "Hurray, you have spent $less_minutes minutes less on the road this week.";
                                    $extension_call = true;
                                }
                            }
                        }

                        if ($system_id == "ETRG0019") {
                            $extension_call = false; // this is managed by another automation
                        }
                    }
                }

                pg_query($conn, "UPDATE members SET notification_test = now()+'1 day' WHERE id = $member_id");

                if ($extension_call == true) {
                    $outX = array();
                    $x["mmode"] = "AUTO";
                    $x["trigger_key"] = $system_id;
                    $x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
                    print_r($x);
                    Fextension_call($x, $outX);
                }
                // print_r($acc);
            }
        }
    }  // end if active 
}
exit();

function emissonIncrease($member_id, $days_gap) {

    $emmission_inc = rand(0, 28);
    return $emmission_inc;
}

function getTripMinutes($member_id, $days_gap) {

    $less_minutes = rand(12, 99);
    return $less_minutes;
}

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;
}

function getAccountBenefit($conn, $member_id, &$extension_confirm_call) {
    $msg_text = "";
    $extension_confirm_call = false;
    //  $msql = "SELECT b.id AS bank_id,ccb.benefit from members_bank_accounts mb LEFT JOIN banks b ON b.bank_name = mb.bank_name LEFT JOIN credit_card_benefits ccb ON ccb.credit_card_id = b.id   WHERE mb.member_id = $member_id AND ccb.benefit IS NOT NULL LIMIT 1";
    $myqsl = "SELECT b.id AS bank_id,ccb.benefit from members_bank_accounts mb "
            . " LEFT JOIN credit_cards b ON b.card_name= mb.description "
            . " LEFT JOIN credit_card_benefits ccb ON ccb.credit_card_id = b.id  "
            . " WHERE mb.member_id = $member_id AND ccb.benefit IS NOT NULL "
            . " ORDER BY random() LIMIT 1";
    $r2 = pg_query($conn, $myqsl);
    if ($r2 && pg_num_rows($r2) > 0) {

        while ($row = pg_fetch_row($r2)) {
            $msg_text = $row[1];
            // echo 'HERE 22222->' . $msg_text;
            $extension_confirm_call = true;
        }
    }
    return $msg_text;
}

$r = pg_query($conn, $mysql);
$acc = array();
if ($r && pg_num_rows($r)) {
    // echo 'ddhdhdhd';
    while ($f = pg_fetch_assoc($r)) {
        $member_id = $f["member_id"];

        $extension_call = false;
        if (array_key_exists($member_id, $acc)) {
            // echo "here " . $member_id;
            // nothing - we dont want duplicate 
        } else {

            $sq2 = "SELECT * FROM members_notification WHERE member_id=$member_id AND status IN (1)";
            $r2 = pg_query($readOnlyReplicaConn, $sq2);
            $x["pid"] = 0;
            $x["member_id"] = $member_id;
            $x["notice_type"] = "GENALERT";
            $acc[$member_id] = $member_id;
            if ($r2 && pg_num_rows($r2) == 0) {
                $x["msg"] = "We have found a way to save time for you";


                //    print_r($x);
                if ($f["surge_price"] > 0 && $f["cheaper_alternative"] > 0) {
                    $x["msg"] = "We have found a way to save time and money for you";
                }

                if ($f["surge_price"] > 0 && $f["cheaper_alternative"] == 0) {
                    $x["msg"] = "We have found a way to save time for you";
                }

                $extension_call = true;
            }
        }


        if ($extension_call == true) {
            $outX = array();
            $x["mmode"] = "AUTO";
            $x["action"] = FLOAT_SYSTEM_CREATE_NOTIFICATION;
            //   Fextension_call($x, $outX);
        }
        print_r($acc);
    }
}


/*
  $q = "SELECT * FROM members_notification WHERE status = 1 LIMIT 5";
  $r = pg_query($conn, $q);
  if ($r && pg_num_rows($r)) {
  while ($f = pg_fetch_row($r)) {
  $member_id = $f[0];
  echo "[" . date("Y-m-d H:i:s") . "] Processing member_id=" . $member_id . " (total member records: " . $f[1] . ")\n";
  //if ($f[0]==22) continue; // Crash!
  // $p = processMemberGPSDataPartition($member_id);
  echo "[" . date("Y-m-d H:i:s") . "] Processed member_id=" . $member_id . " (processed records: " . $p . ")\n";
  }
  }

  function processMemberGPSDataPartition($member_id) {
  global $conn, $precision, $hard_limit;

  }
 */

pg_close($conn);
pg_close($readOnlyReplicaConn);
echo "[" . date("Y-m-d H:i:s") . "]  member_prepare_notification job complete.\n";