<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Background_service_model extends CI_Model {

    function __construct(){
        parent::__construct();
    }

    function update_recipient_email_status($data, $type){
        switch($type){
            case 1 :
                return $this->update_reminder_email_status($data);
                break;
            case 2 :
                return $this->update_reinvitation_email_status($data);
                break;
            default :
                echo "No action has been specified.";
                exit(0);
                break;
        };
    }
    function get_mail_recipes($event_id, $mail_id, $limit)
    {
        $result = $this->db->query("SELECT ets.number_of_send_attempt, ets.subscriber, ets.event_subscriber_id, u.email_address,
        CONCAT(u.first_name, ' ', u.last_name) AS sub_name
        FROM event_target_subscriber ets
        LEFT JOIN user u ON u.user_id = ets.subscriber
        WHERE (ets.status = 0 OR (ets.status = 3 AND ets.number_of_send_attempt <=3))
        AND ets.event_id = ?
        AND ets.mail_id = ?
        LIMIT ?", array($event_id, $mail_id, $limit));

        if ($result->num_rows() > 0) {
            return $result->result_array();
        }
        return false;
    }

    function get_reminder_subscribers($event_id, $limit)
    {
        $result = $this->db->query("SELECT
            er.subscriber as subscriber_id,
            er.registration_id AS reference_id,
            u.email_address,
            CONCAT(u.first_name, ' ', u.last_name) AS sub_name,
            ees.event_email_id,
            ees.number_of_send_attempt
        FROM
            event_registration er
        LEFT JOIN user u ON u.user_id = er.subscriber
        LEFT JOIN event_email_subscriber ees ON ees.event_id = er.event_id AND er.registration_id = ees.reference_id
        WHERE
            (er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
            OR
             er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
            )
            AND
              er.status = 1
            AND
            er.event_id = ?
            GROUP BY er.registration_id
            LIMIT ?", array($event_id, $limit));

        if ($result->num_rows() > 0) {
            return $result->result_array();
        }
        return false;
    }

    public function get_data_reminders(){
        $data = $this->reminder_with_template();

        if($data){
            return array("c_template" => true, "data" => $data);
        }
//        else {
//            return array("c_template" => false, "data" => $this->reminder_without_template());
//        }
        return false;
    }

    private function reminder_with_template(){
        $result = $this->db->select("
                e.event_id,
                e.title as event_title,
                e.event_status,
                ecl.city as event_city_location,
                et.event_type,
                e.location as event_venue,
                e.rate as event_rate,
                e.remaining_seat,
                e.total_available_seat as available_seat,
                e.description,
                eet.description as description_of_email,
                eet.email_template_id,
                efa.file_name")
            ->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
            ->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
            ->from("event_email_template eet")
            ->join("event e", "e.event_id = eet.event_id", "left")
            ->join("event_registration er", "er.event_id = eet.event_id", "left")
            ->join("event_email_subscriber ees", "ees.event_id = eet.event_id", "left")
            ->join("user u", "u.user_id = er.subscriber", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
            ->join('event_file_attachment efa', 'efa.event_id = eet.event_id AND efa.status=1', "left")
            ->where("er.status", 1)
            ->where("eet.status", 2)
            ->where("(er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
            OR
             er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
            )")
            ->where_in("e.back_office_status", array(1,2,3,4))
            ->where("(CURRENT_TIMESTAMP) >= eet.schedule")
            ->group_by("eet.event_id")
            ->get();

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    private function reminder_without_template(){
        $result = $this->db->select("
                e.event_id,
                e.title as event_title,
                e.event_status,
                ecl.city as event_city_location,
                et.event_type,
                e.location as event_venue,
                e.rate as event_rate,
                e.remaining_seat,
                e.total_available_seat as available_seat,
                e.description,
                efa.file_name")
            ->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
            ->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
            ->from("event e")
            ->join("event_registration er", "er.event_id = e.event_id", "left")
            ->join("user u", "u.user_id = er.subscriber", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
            ->join('event_file_attachment efa', 'efa.event_id = e.event_id AND efa.status=1', "left")
            ->where("er.status", 1)
            ->where("(er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
            OR
             er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
            )")
            ->where("e.event_id NOT IN(SELECT event_id FROM event_email_template WHERE status=2 AND schedule BETWEEN '".date('Y-m')."-01' AND '".date("Y-m-t")."')")
            ->where_in("e.back_office_status", array(1,2,3,4))
            ->where("TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, start_date_time) <=", 48) //by default, send email only to those who subscribed 48hrs before the event date
            ->group_by("e.event_id")
            ->get();

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    public function get_data_waitlists(){
        $result = $this->db->select("
                e.event_id,
                e.title as event_title,
                e.event_status,
                ecl.city as event_city_location,
                et.event_type,
                e.location as event_venue,
                e.rate as event_rate,
                e.remaining_seat,
                e.total_available_seat as available_seat,
                e.description,
                efa.file_name")
            ->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
            ->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
            ->from("event_email_subscriber ees")
            ->join("event e", "e.event_id = ees.event_id", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
            ->join('event_file_attachment efa', 'efa.event_id = ees.event_id AND efa.status=1', "left")
            ->where("ees.type = 2 AND (ees.status = 0 OR (ees.status = 3 AND ees.number_of_send_attempt <=3))")
            ->where_in("e.back_office_status", array(1,2,3,4))
            ->group_by("ees.event_id")
            ->get();

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    public function get_waitlists_subscribers($event_id, $limit){
        $result = $this->db->query("SELECT
            ewl.wait_list_subscriber as subscriber_id,
            ees.reference_id,
            u.email_address,
            CONCAT(u.first_name, ' ', u.last_name) AS sub_name,
            ees.event_email_id,
            ees.number_of_send_attempt
        FROM
            event_email_subscriber ees
        LEFT JOIN event_wait_list ewl ON ewl.event_id = ees.event_id AND ewl.wait_list_id = ees.reference_id
        LEFT JOIN user u ON u.user_id = ewl.wait_list_subscriber
        WHERE
            ees.type = 2
            AND
            (ees.status = 0 OR (ees.status = 3 AND ees.number_of_send_attempt <=3))
            AND
            ees.event_id = ?
            GROUP BY ees.reference_id
            LIMIT ?", array($event_id, $limit));

        if ($result->num_rows() > 0) {
            return $result->result_array();
        }
        return false;
    }

    public function get_all_published_events($dateNow)
    {
        $result = $this->db->select("
                e.event_id,
                e.title as event_title,
                e.event_status,
                ecl.city as event_city_location,
                et.event_type,
                e.location as event_venue,
                e.rate as event_rate,
                e.remaining_seat,
                e.total_available_seat as available_seat,
                e.description,
                em.mail_description as description_of_email,
                em.mail_id,
                efa.file_name")
            ->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
            ->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
            ->from("event_mail em")
            ->join("event e", "e.event_id = em.event_id", "left")
            ->join("event_target_subscriber ets", "em.event_id = ets.event_id AND ets.status = 0", "left")
            ->join("user u", "u.user_id = ets.subscriber", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
            ->join('event_file_attachment efa', 'efa.event_id = em.event_id AND efa.status=1', "left")
            ->where("ets.status", 0)
            ->where("em.status", 2)
            ->where("e.back_office_status !=", 0)
            ->where("DATE_FORMAT('" . $this->db->escape_str($dateNow) . "','%Y-%m-%d %H:%i:%s') < DATE_FORMAT(e.start_date_time, '%Y-%m-%d %H:%i:%s')")
//            ->where("DATE_FORMAT('" . $dateNow . "','%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(em.schedule_time_to_send_mail, '%Y-%m-%d %H:%i:%s')")
            ->group_by("emd.mail_id")
            ->get();

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    public function update_reminder_email_status($data){
        if(isset($data) && !empty($data)) {

            $this->db->trans_start();
            //update batch
            if($data["for_update"])
                $this->db->update_batch('event_email_subscriber', $data["for_update"], 'event_email_id');

            if($data["for_insertion"])
                //insert_batch
                $this->db->insert_batch('event_email_subscriber', $data["for_insertion"]);

            $this->db->trans_complete();
            return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
        }
        return false;
    }

    public function update_reinvitation_email_status($data){
        if(isset($data) && !empty($data)) {
            $update_emails = array_merge($data["sent_emails"], $data["failed_emails"]);
            $this->db->trans_start();
            $this->db->update_batch('event_email_recipient', $update_emails, 'email_recipient_id');
            $this->db->trans_complete();
            return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
        }
        return false;
    }
}
/*If we have a record of this email address we will have sent an email.*/