<?php

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

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

define('EMAIL_PENDING', 1);
define('EMAIL_SENT', 5);

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

echo $email_templates_dir = $savvyext->cfgReadChar('mailsend.templates_dir');
$template_name = "How_it_works.html"; // This is test template.

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

// send peding emsils
$notice_type = 'EMAILALERT';
//savvy=> SELECT l.id AS life_id,l.* FROM members_lifecycle_emails l LEFT JOIN members m ON m.id=l.member_id WHERE l.status = 1 ;
// life_id | id | member_id | cycle_type | subject | status | added | completed 

$mysql = "SELECT l.id AS life_id,l.*,m.email AS reciever_email FROM members_lifecycle_emails l LEFT JOIN members m ON m.id=l.member_id WHERE l.status = 1";
$r0 = pg_query($readOnlyReplicaConn, $mysql);

if ($r0 && pg_num_rows($r0)) {

    while ($f0 = pg_fetch_assoc($r0)) {
        $member_id = $f0["member_id"];
        $life_id = $f0["life_id"];
        $reciever_email = $f0["reciever_email"];
        $subject = $f0["subject"];

        $qup = "UPDATE members_lifecycle_emails SET status = 4 WHERE id=$life_id";
        pg_query($conn, $qup);


        if ($notice_type == 'EMAILALERT') {
            
            $htm_template = file_get_contents($email_templates_dir . "/lifecycle/" . $template_name); // note read evertime in case we need to change some parameters
            
            if ($reciever_email != '' && $htm_template) {
                $values = [
                    "to_emails" => "{" . $reciever_email . "}",
                    "member_id" => "" . $member_id . "",
                    "subject" => $subject,
                    "html_body" => pg_escape_string($htm_template),
                    "status" => "0",
                    "created_at" => (new DateTime())->format('c'),
                    "updated_at" => (new DateTime())->format('c')
                ];

                $values = array_filter($values, 'strlen');
                $q = "INSERT INTO emails ";
                $q .= " (" . implode(", ", array_keys($values)) . ")";
                $q .= " VALUES ('" . implode("', '", $values) . "') ";
                $rs = pg_query($conn, $q);
                if ($rs) {
                      $q = "UPDATE members_lifecycle_emails SET status = " . EMAIL_SENT . ", completed=now() WHERE id = " . $life_id;
                        $rs = pg_query($conn, $q);
                }
            }
        } // if EMAIl ALERT IS NEEDED
    }
}


//genedate new list of emails

$mysql = "SELECT *, id AS member_id  FROM members WHERE id > 0 AND now() BETWEEN  added+'1 minute' AND  added+'2 day' AND id NOT IN (SELECT member_id FROM members_lifecycle_emails WHERE cycle_type ='HOWITWORKS' )";

// TEST echo 
//$mysql = "SELECT *, id AS member_id FROM members WHERE id < 5 AND id> 0 AND id NOT IN (SELECT member_id FROM members_lifecycle_emails WHERE cycle_type ='HOWITWORKS' ) ORDER BY id  ASC";
$r = pg_query($readOnlyReplicaConn, $mysql);

if ($r && pg_num_rows($r)) {

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

        $values = [
            "member_id" => "" . $member_id . "",
            "subject" => "Float - How it Works",
            "cycle_type" => "HOWITWORKS"
        ];

        $values = array_filter($values, 'strlen');
        $q = "INSERT INTO members_lifecycle_emails ";
        $q .= " (" . implode(", ", array_keys($values)) . ")";
        echo $q .= " VALUES ('" . implode("', '", $values) . "') ";


        $rs = pg_query($conn, $q);
    }
}

/*
 * 
 * 
 * CREATE TABLE members_lifecycle_emails (
  id SERIAL,
  member_id INT REFERENCES members(id),
  cycle_type VARCHAR(15) NOT NULL REFERENCES email_cycle_types(cycle_type),
  subject VARCHAR(100),
  status   integer DEFAULT 1,
  added timestamp without time zone DEFAULT now(),
  completed timestamp without time zone DEFAULT null
  );
  ALTER TABLE ONLY members_lifecycle_emails
  ADD CONSTRAINT members_lifecycle_emails_id_key UNIQUE (id);
 */

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