<?php

namespace App\Models;

use CodeIgniter\Model;

class NotificationModel extends Model
{
    protected $table;

    public function setCustomTable($tableName)
    {
        $this->table = $tableName;
    }

    // public function getTasksDueLastWeek()
    // {
    //     $now = date('Y-m-d');
    //     $oneWeekAgo = date('Y-m-d', strtotime('-7 days', strtotime($now)));

    //     return $this->where('due_date >=', $oneWeekAgo)
    //         ->where('due_date <=', $now)
    //         ->findAll();
    // }

    /** This Function used for due_date_notifications routes */
    public function getSubscriptionDueDetail($date)
    {
        // $now = date('Y-m-d');
        // $lastWeek = date('Y-m-d', strtotime('+7 days'));
        // $date_format =  date('Y-m-d', $date);
        return $this->db->table('subscription as S')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('category as CM', 'CM.id = S.scheme_id', 'left')
            ->join('business as B', 'B.business_id = S.business_id', 'left')
            ->join('book_categories as BC', 'BC.category_id = S.scheme_id', 'left')
            ->join('books as BK', 'BC.book_id = BK.book_id', 'left')
            ->select('concat("Your subscription going to expried in ",DATEDIFF(S.to_subscription, CURDATE())," days") as statement,DATEDIFF(S.to_subscription, CURDATE()) as countdays,CM.name,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name,C.email as customer_email,C.mobile_no as customer_mobile,S.sub_id,S.scheme_id,S.customer_id,S.from_subscription,S.to_subscription,S.business_id,B.title as business_name,B.address as business_address,B.city as business_city,B.state as business_state,B.postal_code as business_postal_code,B.email as business_email,B.mobile_no as business_mobile_no,S.to_subscription,S.from_subscription,BK.title,BK.book_id')
            // ->where('S.to_subscription >=', $now)->where('S.to_subscription <=',$lastWeek)
            ->where('S.to_subscription =', date('Y-m-d', strtotime($date)))
            ->groupBy('S.sub_id')
            ->get()->getResultArray();
    }

    public function getTemplateDetails()
    {
        return $this->db->table('templates as T')
            ->join('users as U1', 'U1.user_id = T.created_by', 'left')
            ->join('users as U2', 'U2.user_id = T.updated_by', 'left')
            ->select('T.template_id,T.template_name,T.mode,T.created_on,T.created_by,T.updated_on,T.updated_by,DATE_FORMAT(T.created_on, "%d/%m/%Y %h:%i %p") AS formatted_created_on,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name,T.isactive')
            // ->whereNotIn('T.template_name',array('EXPIRY NOTIFY TEMPLATE EMAIL','EXPIRY NOTIFY TEMPLATE WHATSAPP'))
            ->get()->getResultArray();
    }

    public function saveDetails($data, $pk_name, $t_name)
    {
        $id = $data[$pk_name];
        $statement_string = ucfirst(str_replace('_', ' ', $t_name));
        if ($id != '') {
            unset($data[$pk_name]); // Remove the id from the data to avoid updating it
            unset($data['created_by']); // bcoz here data Updating here.
            if ($this->db->table($t_name)->where($pk_name, $id)->update($data)) {
                $affectedRows = $this->db->affectedRows();
                $statement['success'] = $statement_string . ' has been updated successfully.';
                $statement['error'] = "";
                $statement['log'] = $statement_string . ": has been updated successfully. Updated " . $pk_name . " = " . $id . " Affected Rows = " . $affectedRows;
                $statement['insert_id'] = $id;
            } else {
                $statement['success'] = "";
                $statement['error'] = $statement_string . ' update failed. Please try again.';
                $statement['log'] = $statement_string . ":  Err Failed to update ID = " . $id;
                $statement['insert_id'] = $id;
            }
        } else {
            unset($data['updated_by']);
            if ($this->db->table($t_name)->insert($data)) {
                $insertID = $this->db->insertID();
                $statement['success'] = $statement_string . ' has been Inserted successfully.';
                $statement['error'] = "";
                $statement['log'] = $statement_string . ": has been added successfully. Inserted ID = " . $insertID;
                $statement['insert_id'] = $insertID;
            } else {
                $statement['success'] = "";
                $statement['error'] = $statement_string . " could not be added. Please try again..";
                $statement['log'] = $statement_string . ": Err could not be added. Please try again.";
                $statement['insert_id'] = "";
            }
        }
        return $statement;
    }

    public function getCampaignDetails()
    {
        $result = $this->db->table('notification_campaign as NC')
            ->join('users as U1', 'U1.user_id = NC.created_by', 'left')
            ->join('users as U2', 'U2.user_id = NC.updated_by', 'left')
            ->join('templates as T', 'T.template_id = NC.template_id', 'left')
            ->select('NC.campaign_id,NC.campaign_name,T.template_id,T.template_name,NC.mode,NC.created_on,NC.created_by,NC.updated_on,NC.updated_by,DATE_FORMAT(NC.created_on, "%d/%m/%Y %h:%i %p") AS formatted_created_on,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name,NC.isactive,NC.group_id,NC.scheduled_date,if(NC.scheduled_time IS NULL ,"",NC.scheduled_time) as scheduled_time')
            ->whereNotIn('T.template_name', array('EXPIRY NOTIFY TEMPLATE EMAIL', 'EXPIRY NOTIFY TEMPLATE WHATSAPP','EXPIRY NOTIFY TEMPLATE SMS'))
            ->get()->getResultArray();

        if (!empty($result)) {
            foreach ($result as $i => $item) {
                $group_id = unserialize($item['group_id']);
                $group_names = [];
                $group_query = [];
                foreach ($group_id as $j) {
                    $cg_data = $this->db->table('customer_groups as CG')
                        ->select('CG.group_id, CG.group_name,CG.group_query, CG.isactive')
                        ->where(['CG.isactive' => 1, 'CG.group_id' => $j])
                        ->get()
                        ->getRowArray();

                    if ($cg_data) {
                        $group_names[] = $cg_data['group_name'];
                        $group_query[] = $cg_data['group_query'];
                        // array_push($group_sql, $cg_data['group_query']);
                    }
                }
                $result[$i]['group_name'] = !empty($group_names) ? implode(", ", $group_names) : '';
                $result[$i]['customer_group'] = !empty($group_query)  ? $this->customerGroupQueryExecution($group_query) : '';
                $result[$i]['customer_group_count'] = !empty($result[$i]['customer_group'])  ? count($this->customerGroupQueryExecution($group_query)) : 0;
            }
        }
        return $result;
    }

    public function customerGroupQueryExecution($queries)
    {

        $results = [];

        // Execute the queries
        foreach ($queries as $query) {
            if (!empty($query)) {
                $queryResult = $this->db->query($query)->getResult();
                $results[] = $queryResult;
            }
        }
        // echo "<pre>";
        // print_r($results);
        // echo "</pre>";
        // Create a new array to store the unique results based on customer_id
        $uniqueCustomers = array();

        foreach ($results as $innerArray) {
            foreach ($innerArray as $customer) {
                $customerId = $customer->customer_id;

                // Check if the customer_id already exists in $uniqueCustomers
                if (!isset($uniqueCustomers[$customerId])) {
                    $uniqueCustomers[$customerId] = $customer;
                }
            }
        }

        // Convert the $uniqueCustomers array back to a numerically indexed array
        $uniqueCustomers = array_values($uniqueCustomers);
        return $uniqueCustomers;
        // echo "<pre>";
        // print_r($uniqueCustomers);
        // echo "</pre>";
        // die;
    }
    //   $query = "INSERT IGNORE INTO notification_history_children (fkid,customer_id,receiving_entry,message,result,sent_time)
    //     VALUES (1, 1, 'somes@gmail2.com','mail msg',NULL,'2023-12-22 14:20:50'),
    //     (1, 1, '9638527411','whatapp msg',NULL,'2023-12-22 14:20:50')";
    // $result = $db->query($query)->getResultArray();
    public function getExpDate()
    {
        $db = \Config\Database::connect();

        $query = "SELECT * FROM customer_groups WHERE group_name = 'EXPIRYDATE' AND isactive = 1";

        $result = $db->query($query)->getResultArray();

        if (isset($result[0]["group_query"])) {
            return $result[0]["group_query"];
        } else {

            error_log('group_query is not available in getExpDate()');

            return;
        }
    }

    public function getExpDate2()
    {
        $db = \Config\Database::connect();

        $query = "SELECT * FROM customer_groups WHERE group_name = 'EXPIRYDATE_2' AND isactive = 1";

        $result = $db->query($query)->getResultArray();

        if (isset($result[0]["group_query"])) {
            return $result[0]["group_query"];
        } else {

            error_log('group_query is not available in getExpDate()');

            return;
        }
    }

    public function getTempName($name)
    {
        $db = \Config\Database::connect();
        $query = "SELECT * FROM templates WHERE isactive = 1 AND template_name = '" . $name . "'";
        $result = $db->query($query)->getResultArray();
        if (isset($result)) {
            return $result;
        } else {

            error_log('group_query is not available in getExpDate()');

            return  $query;
        }
    }
    public function update_notification_campaign_status($data, $id)
    {
        if ($id != '') {
            $where = ['isactive' => 1, 'status' => 0, 'campaign_id' => (int)$id];
            if ($this->db->table('notification_campaign')->where($where)->update($data)) {
                $affectedRows = $this->db->affectedRows();
                $statement = $affectedRows > 0 ? 'Notification Campaign Status has been updated successfully. Aff Row' . $affectedRows . " NCID = " . $id : 'Notification Campaign Status Update failed. Bcoz Already Affected Count = ' . $affectedRows . ". NCID = " . $id;
            } else {
                $statement = "Notification Campaign Status Update failed. NCID = " . $id;
            }
        } else {
            $statement = "Notification Campaign Status Update failed. Bcoz Not Found NCID";
        }
        return $statement;
    }

    public function save_notification_history_parents($data)
    {

        $this->db->table('notification_history_parents')->insert($data);
        $insertID = $this->db->insertID();
        return $insertID;
    }

    public function update_notification_history_parent_status($data, $id, $campaign_id)
    {
        if ($id != '') {
            $where = ['id' => (int)$id, 'status' => 0, 'campaign_id' => (int)$campaign_id];
            if ($this->db->table('notification_history_parents')->where($where)->update($data)) {
                $affectedRows = $this->db->affectedRows();
                $statement = $affectedRows > 0 ? 'Notification history parent Status has been updated successfully. Aff Row' . $affectedRows . " NHPID = " . $id : 'Notification history parent Status Update failed. Bcoz Already Affected Count = ' . $affectedRows . ". NHPID = " . $id;
            } else {
                $statement = "Notification history parent Status Update failed. NHPID = " . $id;
            }
        } else {
            $statement = "Notification history parent Status Update failed. Bcoz Not Found NHPID";
        }
        return $statement;
    }


    public function save_notification_history_child($data)
    {
        // $this->db->ignore(true); // Enable "INSERT IGNORE" behavior
        // $this->db->table('notification_history_children')->insert($data);
        // $insertID = $this->db->insertID(); // Get the last inserted ID
        // return $insertID;
        // $insert_query = $this->db->insert_string('notification_history_children', $data);
        // $insert_query = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insert_query);
        // $this->db->query($insert_query);
        // $insertID = $this->db->insertID(); // Get the last inserted ID
        // return $insertID;

        $table = 'notification_history_children';
        $insert_query = "INSERT IGNORE INTO $table (" . implode(', ', array_keys($data)) . ") VALUES (" . rtrim(str_repeat('?, ', count($data)), ', ') . ")";
        $this->db->query($insert_query, array_values($data));
        // print_r($this->db->getLastQuery());die;
        $insertID = $this->db->insertID();
        return $insertID;
    }

    public function save_custom_notification_history_child($data)
    {
        $this->db->table('notification_history_children')->insert($data);
        $insertID = $this->db->insertID();
        return $insertID;
    }

    public function update_notification_history_child($data, $where)
    {

        $id = isset($where['id']) ? $where['id'] : "";

        if ($id != '') {
            $updateResult = $this->db->table('notification_history_children')->where($where)->update($data);
            // print_r($this->db->getLastQuery());die;
            if ($updateResult) {
                $affectedRows = $this->db->affectedRows();
                $statement = 'Notification history children has been updated successfully. Aff Row ' . $affectedRows . ' NHCID = ' . $id;
            } else {
                $statement = 'Notification history children Update failed. NHCID = ' . $id;
            }
        } else {
            $statement = 'Notification history children Update failed. Bcoz Not Found NHCID';
        }

        return $statement;
    }

    public function get_noifications_acknowlegdement_data()
    {
        $query = $this->db->table("notification_history_children");
        $query->select('
            notification_history_children.id AS child_id,
            notification_history_children.fkid AS parent_id,
            notification_history_children.customer_id,
            CONCAT_WS(" ", customers.first_name, customers.last_name) AS customers_name,
            notification_history_children.receiving_entity,
            notification_history_children.receiving_status,
            notification_history_children.result,
            notification_history_children.sent_time,
            DATE_FORMAT(notification_history_children.sent_time, "%d/%m/%Y %h:%i %p") AS formatted_sent_date_time,
            DATE_FORMAT(notification_history_children.sent_time, "%d/%m/%Y") AS formatted_sent_date,
            notification_history_parents.campaign_id,
            notification_campaign.campaign_name,
            notification_history_parents.status AS parents_status,
            notification_history_parents.start_date_time,
            DATE_FORMAT(notification_history_parents.start_date_time, "%d/%m/%Y %h:%i %p") AS formatted_start_date_time,
            DATE_FORMAT(notification_history_parents.start_date_time, "%d/%m/%Y") AS formatted_start_date
        ');

        $query->join('notification_history_parents', 'notification_history_parents.id = notification_history_children.fkid', 'left');
        $query->join('customers', 'customers.customer_id = notification_history_children.customer_id', 'left');
        $query->join('notification_campaign', 'notification_campaign.campaign_id = notification_history_parents.campaign_id', 'left');
        $query->groupBy('notification_history_children.id');

        return $query->get()->getResultArray();
    }

    public function getAllCustomerMobileNumber()
    {

        $arr = $this->select('mobile_no')
            ->where('mobile_no IS NOT NULL')
            ->where('mobile_no !=', '')
            ->groupBy('mobile_no')
            ->having('COUNT(*) >', 1)
            ->findAll();
        return count($arr) > 0 ? $arr : [];
    }
    public function checkAlreadyExistingInHistory($email, $customerId, $subscriptionId)
    {
        return $this->db->table('notification_history_children as nhc')
            ->join('customers as C', 'C.customer_id = nhc.customer_id', 'left')
            ->join('subscription as S', 'S.sub_id = nhc.subscription_id', 'left')
            ->where('nhc.receiving_entity =', $email)
            ->where('C.customer_id =', $customerId)
            ->where('S.sub_id =', $subscriptionId)
            ->where('nhc.sent_time >', date('Y-m-d', strtotime('-5 days')))
            ->where('nhc.receiving_status = ', 1)
            ->get()->getResultArray();
    }

    public function get_email_data($f_date = null, $t_date = null)
    {
        $builder = $this->db->table('notification_history_parents as nhp');
        $builder->select('nhp.*, nhc.*, nc.*,C.first_name,C.last_name,C.email, S.sub_id,S.membership_id, DATEDIFF(S.to_subscription, CURDATE()) AS countdays');
        $builder->join('notification_history_children as nhc', 'nhc.fkid = nhp.id');
        $builder->join('notification_campaign as nc', 'nc.campaign_id = nhp.campaign_id');
        $builder->join('customers as C', 'C.customer_id = nhc.customer_id');
        $builder->join('subscription as S', 'S.sub_id = nhc.subscription_id');
        $builder->where('DATE(nhp.start_date_time) >=', $f_date);
        $builder->where('DATE(nhp.start_date_time) <=', $t_date);
        $builder->whereIn('nhp.campaign_id', [1, 2,getenv("SMS_CAMPAIGN_ID")]);
        $builder->where('nc.isactive', 1);

        $query = $builder->get();
        $results = $query->getResult();
        log_message('info', json_encode($results));
        return $results;
    }

    public function saveSmSHistory($where, $messageType, $message, $result, $membershipID)
    {

        try {

            $customer = $this->db->table('customers')->where($where)->get()->getRowArray();
            $subscriber = $this->db->table('subscription')->where('membership_id',$membershipID)->get()->getRowArray();

            $customer_id = $customer['customer_id'];
            $subscriptionId = $subscriber['sub_id'];
            $campaign_id = $messageType;
            $status = str_contains($result, 'success') ? 1 : 0;

            $data = [
                'campaign_id' => $campaign_id,
                'status' => $status
            ];

            $this->db->table("notification_history_parents")->insert($data);

            $insertID = $this->db->insertID();

            $child_data = [
                'fkid' => $insertID,
                'customer_id' => $customer_id,
                'receiving_entity' => $where['mobile_no'],
                'receiving_status' => $status,
                'message' => $message,
                'subscription_id' => $subscriptionId,
                'result' => json_encode($result)
            ];

            $this->db->table("notification_history_children")->insert($child_data);
            $insertID = $this->db->insertID();

            return $insertID;
        } catch (\Exception $e) {
            // Handle the exception
            log_message('error', 'Error in saveSmSHistory: ' . $e->getMessage());
            return false; 
        }
    }

    public function getCampaignID($template_id){

        $result = $this->db->table("notification_campaign")->where("template_id",$template_id)->get()->getRowArray()['campaign_id'];

        return $result;
    }
}