<?php

//echo "[".date("Y-m-d H:i:s")."] Daily Report job is starting.\n";
include '../backend.php';

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


$member_id = 1;
$screen_name = "activity-time-traveled";
$screen_name = "activity-time-traveled";

$query = "SELECT 0 AS card_id, mn.msg, et.category AS action, 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 5; ";

//echo $query;


echo "<table>";


define('SCREEN_ACTIVITIES_DETAILS', 1);
define('SCREEN_ACTIVITIES_CATEGORY', 2);
define('SCREEN_ACTIVITIES_TIME_TRAVEL', 3);
define('SCREEN_ACTIVITIES_EMISSION', 4);
define('SCREEN_ACTIVITIES_PERSONALTY', 5);


$re = pg_query($conn, $query);
if ($re && pg_num_rows($re)) {
    $itm_e = 0;
    while ($fe = pg_fetch_assoc($re)) {
        $message = $fe["msg"];
        $icon = $fe["icon"];
        $action = $fe["action"];
        $card_id = 0;

        echo "<tr> <td>$message</td>  <td>$icon</td>  <td>$action</td>  <td>$card_id</td>  </tr>";
        // $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
    }
}


echo "</table><hr>";

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;
}
$query = "SELECT 0 AS card_id, mn.msg, et.category AS action, et.icon AS card_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 .= "  UNION ";


$query .= "SELECT ma.card_id,mc.description AS msg,mc.button1_action AS action,'' AS card_icon "
        . "FROM members_card_assign ma "
        . "LEFT JOIN main_cards mc ON mc.id=ma.card_id "
        . "WHERE mc.activity_screen= {$activity_screen} "
        . "AND ma.subscribe IS NULL "
        . "AND ma.status = 1 AND mc.show_area = 2 "
        . "AND ma.member_id= {$member_id} ";


/* card_id |                                          description                                           | button1_action | card_icon
  ---------+------------------------------------------------------------------------------------------------+----------------+-----------
  349 | Save money by subscribing to Float's exclusive deals that are targeted to suit your lifestyle. | GOOFFERS       |
  (1 row)
 */


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
$member_country = '';
echo $mysq_member_country = "SELECT country FROM members WHERE id = {$member_id}";
$rec = pg_query($conn, $mysq_member_country);
if ($rec && pg_num_rows($rec)) {
    $rowc = pg_fetch_assoc($rec);
    print_r($rowc);
    $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 ";


        echo $query;

echo "<hr>[[[[<table>";


$re = pg_query($conn, $query);
if ($re && pg_num_rows($re)) {
    $itm_e = 0;
    while ($fe = pg_fetch_assoc($re)) {
        $message = $fe["msg"];
        $icon = $fe["card_icon"];
        $action = $fe["action"];
        $card_id = $fe["card_id"];
        ;

        echo "<tr> <td>[$message</td>  <td>$icon</td>  <td>$action</td>  <td>$card_id]</td>  </tr>";
        // $emiss_arr[$gas_date] = array("gas_date" => "$gas_date", "value" => $gas_emission_value, "gas" => $gas_amount);
    }
}


echo "</table>]]]]]";


//SELECT ma.card_id,mc.description,mc.button1_action,'' AS card_icon FROM members_card_assign ma LEFT JOIN main_cards mc ON mc.id=ma.card_id WHERE mc.activity_screen=1 AND ma.subscribe IS NULL AND ma.status = 1  AND ma.member_id=1;


/*

SELECT short_title,description,button1_action FROM main_cards;
 * 
 

savvy=> \d main_cards;
                                            Table "public.main_cards"
       Column       |            Type             | Collation | Nullable |                Default
--------------------+-----------------------------+-----------+----------+----------------------------------------
 id                 | integer                     |           | not null | nextval('main_cards_id_seq'::regclass)
 name               | character varying(100)      |           | not null |
 title              | character varying(100)      |           | not null |
 short_title        | character varying(35)       |           | not null |
 description        | character varying(250)      |           | not null |
 background_picture | character varying(150)      |           | not null |
 button1            | character varying(35)       |           | not null |
 button1_text       | character varying(35)       |           | not null |
 button1_action     | character varying(15)       |           | not null |
 status             | integer                     |           |          | 1
 added              | timestamp without time zone |           |          | now()
 can_save           | integer                     |           |          | 0
 template           | integer                     |           |          | 0
 card_canexpire     | integer                     |           |          | 0
 card_expiration    | timestamp without time zone |           |          |
 notify             | integer                     |           |          | 0
 card_country       | character varying(2)        |           |          |
 card_action_id     | integer                     |           |          |
 titleshow          | integer                     |           |          | 1
 multiple_answer    | integer                     |           |          | 0
 use_short_title    | integer                     |           |          | 1
 deleted            | timestamp without time zone |           |          |
 list_order         | integer                     |           |          | 0
 target_key         | character varying(10)       |           |          |
 target_text        | character varying(250)      |           |          |
 long_description   | text                        |           |          |
 card_behavior      | character varying(10)       |           |          |
 card_type          | character varying(2)        |           |          | ''::character varying
 card_time          | character varying(12)       |           |          | ''::character varying
 card_location      | integer                     |           |          | 0
 card_points        | integer                     |           |          | 0
 card_reciept       | character varying(15)       |           |          |
 card_logic         | character varying(15)       |           |          | ''::character varying
 card_order         | integer                     |           |          | 100
 background_color   | character varying(8)        |           |          |
 dynamic_key        | character varying(25)       |           |          |
 blog_id            | integer                     |           |          |
 show_area          | integer                     |           |          | 0
 activity_screen    | integer                     |           |          | 0
Indexes:
    "main_cards_dynamic_key_key" UNIQUE CONSTRAINT, btree (dynamic_key)
    "main_cards_id_key" UNIQUE CONSTRAINT, btree (id)
Foreign-key constraints:
    "main_cards_blog_id_fkey" FOREIGN KEY (blog_id) REFERENCES blog_app_articles(blog_id)
    "main_cards_card_action_id_fkey" FOREIGN KEY (card_action_id) REFERENCES card_actions(id)
    "main_cards_card_behavior_fkey" FOREIGN KEY (card_behavior) REFERENCES card_behavior(key)
    "main_cards_card_country_fkey" FOREIGN KEY (card_country) REFERENCES country(code)
Referenced by:
    TABLE "decision_cards" CONSTRAINT "decision_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "member_saved_cards" CONSTRAINT "member_saved_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "members_card_assign" CONSTRAINT "members_card_assign_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "members_cardclicktrack" CONSTRAINT "members_cardclicktrack_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "members_carpool" CONSTRAINT "members_carpool_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "members_survey" CONSTRAINT "members_survey_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "onboarding_survey_cards" CONSTRAINT "onboarding_survey_cards_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "subscription_summary" CONSTRAINT "subscription_summary_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)
    TABLE "survey_card_country" CONSTRAINT "survey_card_country_card_id_fkey" FOREIGN KEY (card_id) REFERENCES main_cards(id)

savvy=>