<?php

define("FLOAT_SYSTEM_CREATE_NOTIFICATION", 92025);
define("FLOAT_SYSTEM_EMAIL_NOTIFICATION", 92027);
define('FLOAT_SYSTEM_CREATE_DYNAMICCARD', 92026);


$mysql = " SELECT ";
$mysql .= " p.id,m.id AS member_id,m.email, p.travel_date,p.duration,p.cost_raw,p.trackedemail_item_id,p.cost,p.updated,p.distance, ";
$mysql .= " CASE WHEN e.id IS NULL THEN '0' ELSE '1' END AS surge_price, ";
$mysql .= " CASE WHEN (f.id IS NOT NULL AND f.cost>f.average) THEN ROUND(f.average/f.cost,2) ELSE 0 END AS cheaper_alternative ";
$mysql .= " FROM parsedemail_item p LEFT JOIN trackedemail_item t ON t.id=p.trackedemail_item_id ";
$mysql .= " LEFT JOIN trip_surge_price e ON (e.data_source_id=p.id AND e.data_source=1) ";
$mysql .= " LEFT JOIN trip_price_comparison f ON (f.data_source_id=p.id AND e.data_source=1) LEFT JOIN members m ON m.id=t.member_id ";
$mysql .= " WHERE e.id IS NOT NULL ORDER BY t.id DESC LIMIT 500";


$trigger_query = array(
    "SURGESAVE" => array(
        "query" => $mysql,
        "message" => "You are spending too much. Let's save money",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0001" => array(
        "query" => "SELECT id AS member_id,last_acct,count_acct,last_email,count_email, min_budget,count_acct+count_email AS total_data FROM members WHERE min_budget =0 AND count_email =0 AND count_acct =0 AND id > 0 ORDER BY notification_test ASC LIMIT 500",
        "message" => "Let's examine how much you spend on travel. We can save money.",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0001KEY",
        "start_save" => true
    ),
    "ETRG0002" => array(
        "query" => "SELECT ma.*,m.min_budget from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE m.min_budget > 0 AND ma.spend_7 > 0 AND ma.spend_7/m.min_budget < 0.9 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Good Job, you're saving money",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0002KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0003" => array(
        "query" => "SELECT ma.*,m.min_budget from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE m.min_budget > 0 AND ma.spend_7 > 0 AND ma.spend_7/m.min_budget > 1.1 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "You are over budget. Let's see how we can save you money.",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0003KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0004" => array(
        "query" => "SELECT ma.*,m.min_budget from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE  ma.spend_7 > 4000 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "We can see you spent $X on rideshare this month. Here's how Float can save you money",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0004KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0005" => array(
        "query" => "SELECT b.member_id FROM members_bank_accounts b LEFT JOIN members m ON m.id=b.member_id WHERE m.id>0 AND m.country IN ('US','SG','NZ') GROUP BY b.member_id,m.notification_test ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "You have a Chase Freedom credit card. You should take Lyft this month",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0005KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    )
    ,
    "ETRG0006" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Your spending is up 23% this week. Would you like to create a budget?",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0006KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0007" => array(
        "query" => "SELECT ma.*,m.min_budget,(ma.spend_14 - 2*ma.spend_7) AS week_increase,m.country AS country 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",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0007KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0008" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "We can save you $X by taxing (rideshare or taxi) at 5pm today.",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0008KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0009" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "We found that taking public transit and scooter is faster and cheaper for you.",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0009KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0010" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Your spending is up 23% this week. Would you like to create a budget?",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0010KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0011" => array(
        "query" => "SELECT *, id AS member_id FROM members  WHERE id > 0 AND id NOT IN (SELECT member_id FROM members_trips) ORDER BY notification_test ASC  LIMIT 500",
        "message" => "Float found a better way for you to get to work. See route, save it and Float will remind you going forward.",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0011KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0012" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Your spending is up 23% this week. Would you like to create a budget?",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0012KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0014" => array(
        "query" => "SELECT ma.*,m.min_budget,(2*ma.spend_7- ma.spend_14) AS week_increase from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE  ma.spend_7 > 0 AND   2*ma.spend_7> ma.spend_14 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Your emissions output is 10% higher this week, lets see how we can lower it",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0014KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    ),
    "ETRG0015" => array(
        "query" => "SELECT ma.*,m.min_budget,(2*ma.spend_7 - ma.spend_14) AS week_decrease from members_analysis ma LEFT JOIN members m ON m.id = ma.member_id WHERE min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 < 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Hurray, you have spent X minutes less on the road this week. Shop for rewards with Float.",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0015KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    )
    ,
    "ETRG0016" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Good Job, you're saving X% more money than your peers.",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0012KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    )
    ,
    "ETRG0017" => 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 min_budget = 0 AND ma.spend_7 > 0 AND  ma.spend_14 > 2*ma.spend_7 ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "You are spending more X% money than your peers. Let's save money ",
        "feed_msg" => "",
        "active" => 0,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0017KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 1
    )
    ,
    "ETRG0019" => array(
        "query" => "SELECT * FROM members LIMIT 1",
        "message" => "THIS IS USED FOR THE POINTS SYSTEM ONLY - It is automatic",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOPOINT',
        "dynamic_key" => "ETRG0019KEY",
        "start_save" => false, "send_mail" => 1, "send_notification" => 1
    ),
    "ETRG0020" => array(
        "query" => "SELECT * FROM members LIMIT 1",
        "message" => "THIS IS USED FOR THE TRIPS SYSTEM ONLY - It is automatic",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOPOINT',
        "dynamic_key" => "ETRG0020KEY",
        "start_save" => false, "send_mail" => 1, "send_notification" => 1
    ),
    "ETRG0021" => array(
        "query" => "SELECT ma.member_id,ma.status,ma.bank_login_status FROM members_bank_accounts ma LEFT JOIN members m ON m.id=ma.member_id WHERE m.country ='SG' AND ma.bank_login_status='inactive' ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Your bank connection has expired, import your latest transactions to stay on top of your spending",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOPROFILE',
        "dynamic_key" => "ETRG0021KEY",
        "start_save" => false, "send_mail" => 1, "send_notification" => 1
    )
    ,
    "ETRG0022" => array(
        "query" => "SELECT b.member_id, count(b.id) AS total_rec,m.notification_test FROM members_bankimport b LEFT JOIN members m ON m.id=b.member_id WHERE lower(merchant_name)= LOWER('ComfortDelGro') GROUP BY b.member_id, m.notification_test ORDER BY m.notification_test ASC LIMIT 500",
        "message" => "Be careful how you pay, credit and charge card payments get hit with a 10% surcharge in a Comfort Taxi",
        "feed_msg" => "",
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0022KEY",
        "start_save" => false, "send_mail" => 0, "send_notification" => 0
    )
);


$trigger_card = array(
    "ETRG0001" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0001KEY"
    ),
    "ETRG0002" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0002KEY"
    ),
    "ETRG0003" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0003KEY"
    ),
    "ETRG0004" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0004KEY"
    ),
    "ETRG0005" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0005KEY"
    ),
    "ETRG0006" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0006KEY"
    ),
    "ETRG0011" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0011KEY"
    ),
    "ETRG0014" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0014KEY"
    ),
    "ETRG0015" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0015KEY"
    ),
    "ETRG0019" => array(
        "active" => 1,
        "cat" => 'GOACTIVITY',
        "dynamic_key" => "ETRG0019KEY"
    ),
    "ETRG0021" => array(
        "active" => 1,
        "cat" => 'GOPROFILE',
        "dynamic_key" => "ETRG0021KEY"
    )
);

function generateCard($notice_id, $member_id, $cat) {

    $out = array();
    global $trigger_card;
    global $readOnlyReplicaConn;

    // print_r($trigger_card);
//echo "\n ~~~~~56565656 ----------------------------->>>> ".rand(100,999);

    $trigger_key = "";
    $dynamic_key = "";

    $status_msg = '';
    if ($notice_id != '' && $member_id != '') {
// echo "\n ~~~~~38383838383 ----------------------------->>>> ".rand(100,999);

        $mysql = "SELECT * FROM members_notification WHERE id=$notice_id AND member_id=$member_id";
        $r = pg_query($readOnlyReplicaConn, $mysql);


        if ($r && pg_num_rows($r)) {
            $f = pg_fetch_assoc($r);
            $trigger_key = $f["trigger_key"];
            $message = $f["msg"];
        }

        //  echo "\n<br> ~~~~~55555 -----------------------------=====>>==>> ".rand(100,999);

        $card_allowded = false;
        if (array_key_exists($trigger_key, $trigger_card)) {
            $status_msg = "The  $trigger_key is in the array";

            // print_r( $trigger_card[$trigger_key] );
            $dynamic_key = $trigger_card[$trigger_key]["dynamic_key"];
            $card_allowded = true;
        } else {
            $status_msg = "The  $trigger_key not set up for cards";
        }

        //  echo "\n<br> ~~~~~66666 -----------------------------=====>>==>> ".rand(100,999);

        if (true == $card_allowded && $message != '' && $trigger_key != '' && $dynamic_key != '') {
            $in = array();
            echo "~~~~~44444 -----------------------------";
            $in["dynamic_key"] = $dynamic_key;
            $in["member_id"] = $member_id;
            $in["trigger_key"] = $trigger_key;
            $in["message"] = $message;
            $in["notice_id"] = $notice_id;
            $in["cat"] = $cat;
            $in['action'] = FLOAT_SYSTEM_CREATE_DYNAMICCARD;

            print_r($in);

            $ret = Fextension_call($in, $out);

            print_r($out);
            if ($ret == PHP_API_OK) {
                $message = 'Completed!';
            } else {
                $message = 'Failed to  Create';
            }
        }
    }
    echo $status_msg;
}