<?php

if (!defined('ABSPATH')) {
    exit;
}
    
class AmbisnSubscriptions{

    public function get_products($email = '') {
    
        global $wpdb;
    
        $subscription_table = $wpdb->prefix . 'wc_admail_subscriptions';
    
        if (!empty($email)) {
            
            $product_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT DISTINCT product_id 
                FROM $subscription_table 
                WHERE email = %s",
                $email
            ));
            
        } else {
            
            $product_ids = $wpdb->get_col("SELECT DISTINCT product_id FROM $subscription_table");
            
        }

        return $product_ids;
    }
    
    public function get_subscriptions($email = "") {
    
        global $wpdb;
    
        $subscription_table = $wpdb->prefix . 'wc_admail_subscriptions';
    
        $subscriptions = [];

        if (!empty($email)) {
        
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT product_id FROM $subscription_table WHERE email = %s",
                $email
            ));
        
            foreach ($results as $row) {
            
                $subscriptions[] = $row->product_id;
            
            }

        } else {
        
            $results = $wpdb->get_results("SELECT email, product_id FROM $subscription_table");

            foreach ($results as $row) {
            
                $subscriptions[$row->email][] = $row->product_id;
            
            }
        
        }

        return $subscriptions;
    }
    
    public function get_subscription_data($email, $product_id) {
        
        global $wpdb;
    
        $subscription_table = $wpdb->prefix . 'wc_admail_subscriptions';

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT status, sessionID AS sessionID, lang, subscription_date AS date 
            FROM $subscription_table 
            WHERE email = %s AND product_id = %d",
            $email, $product_id
        ), ARRAY_A);

        return $row ?: [];
    }

    
    public function get_filtered_subscriptions($filter = '') {
        
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'wc_admail_subscriptions';

        $sql = "SELECT email, product_id, status, sessionID, lang, subscription_date FROM $table_name";
        
        $where_conditions = array();

        if ($filter !== 'sent') {
            
            $where_conditions[] = "status != 'sent'";
            
        }

        if ($filter === 'single-user-subscriptions' && is_user_logged_in()) {
            
            $current_user = wp_get_current_user();
            
            $user_email = esc_sql($current_user->user_email);
            
            $where_conditions[] = "email = '$user_email'";
            
        }

        if ($filter !== '' && !in_array($filter, ['single-user-subscriptions', 'instock', 'ofs'])) {
            
            $where_conditions[] = "status = '" . esc_sql($filter) . "'";
            
        }

        if ($filter === 'instock' || $filter === 'ofs') {
            
            $product_stock_status = ($filter === 'instock') ? 'instock' : 'outofstock';
            
            $where_conditions[] = "product_id IN (
                SELECT post_id FROM {$wpdb->prefix}postmeta 
                WHERE meta_key = '_stock_status' AND meta_value = '$product_stock_status'
            )";
        }

        if (!empty($where_conditions)) {
            
            $sql .= " WHERE " . implode(" AND ", $where_conditions);
            
        }

        $sql .= " ORDER BY subscription_date DESC";

        $results = $wpdb->get_results($sql, ARRAY_A);

        return $results;
    }



    public function get_emails($date = "all"){
        
        if($date == "all"){
            
            $email_lists = ambisn_get_data('emails', $date);
            
        }else{
            
            $email_lists = array(ambisn_get_data('emails', $date));
            
        }
        
        $emails = array();
        
        if(is_array($email_lists)){
            
            foreach($email_lists as $list){
            
                $list_emails = array_filter(explode(',',$list));
                
                $emails = array_merge($emails, $list_emails);
            
            }
        }
        
        return $emails;
        
    }    
    
    
    
    public function get_chart_html($column){
    
        $dateRange = ambisn_dateRange(7);
    
        $values = array();
    
        foreach ($dateRange as $date) {
        
            $date = $date->format('Y-m-d');
        
            if ($column == 'collected_emails') {
                
                if(ambisn_empty_data()){
                    
                    $values[] = rand(0, 99);
                    
                }else{
                    
                    $emails = $this->get_emails($date);
                    
                    $values[] = count($emails);
                    
                }
            
               
            
            } else {
                
                if(ambisn_empty_data()){
                    
                    $values[] = rand(0, 99);
                    
                }else{
            
                    $value = intval(ambisn_get_data($column, $date));
            
                    if(empty($value)){
                
                        $value = 0;
                
                    }
                    $values[] = $value;
                
                }
            }
        
        }
        
        $maxValue = max($values);
        
        $chartIndex = ($maxValue != 0) ? 100 / $maxValue : 0;
        
        ob_start();
    
        foreach ($dateRange as $date) {
        
            $dayName = $date->format('D');
        
            $date = $date->format('Y-m-d');
            
            if ($column == 'collected_emails') {
                
                if(ambisn_empty_data()){
                    
                    $value = rand(0, 99);
                    
                }else{
            
                    $value = count($this->get_emails($date));
                
                }
            
            } else {
                
                if(ambisn_empty_data()){
            
                    $value = rand(0, 99);
                
                }else{
                    
                    $value = ambisn_get_data($column, $date);
                    
                }
            
            }
        
            if (empty($value)) {
            
                $value = '0';
            
            }
        
            $chartHeight = $value * $chartIndex;
        
            ?>
            
            <div class="chart-element-container">
                <div style="height:<?php echo esc_attr($chartHeight); ?>%;" class="chart-element"><span><?php echo esc_html($value); ?></span></div>
                <span><?php echo esc_html($dayName); ?></span>
            </div>
            
            <?php
        }
        
        return ob_get_clean();
    
    }    
   
}