<?php
function essb_stats_get_network_name($network_key = '') {
	$essb_networks = essb_available_social_networks();
	$r = $network_key;
	
	foreach ($essb_networks as $key => $data) {
		if ($key == 'twitter') {
			$data['name'] = 'X';
		}
		
		if ($key == $network_key) {
			$r = $data['name'];
		}
	}
	
	if ($r == 'pinpro') {
		$r = 'Pinterest Pro';
	}
	
	return $r;
}

function essb_calculate_date_range($period, $date_from, $date_to) {
    $today = date('Y-m-d');
    
    switch($period) {
        case 'today':
            return [$today, $today];
        case 'yesterday':
            return [date('Y-m-d', strtotime('-1 day')), date('Y-m-d', strtotime('-1 day'))];
        case '7days':
            return [date('Y-m-d', strtotime('-7 days')), $today];
        case '14days':
            return [date('Y-m-d', strtotime('-14 days')), $today];
        case '30days':
            return [date('Y-m-d', strtotime('-30 days')), $today];
        case 'year':
            return [date('Y-01-01'), $today];
        case 'all':
            return ['1970-01-01', $today];
        case 'custom':
            // For custom period, use the provided dates
            if (!empty($date_from) && !empty($date_to)) {
                // Validate dates
                $from_check = strtotime($date_from);
                $to_check = strtotime($date_to);
                if ($from_check && $to_check && $from_check <= $to_check) {
                    return [$date_from, $date_to];
                }
            }
            // Fallback to last 30 days if custom dates are invalid
            return [date('Y-m-d', strtotime('-30 days')), $today];
        default:
            return [date('Y-m-d', strtotime('-30 days')), $today];
    }
}

function essb_get_period_days($period) {
    switch($period) {
        case 'today':
        case 'yesterday':
            return 1;
        case '7days':
            return 7;
        case '14days':
            return 14;
        case '30days':
            return 30;
        case 'year':
            return 365;
        case 'all':
            // For "all time", use a large number but limit to 3650 (10 years) for performance
            return 3650;
        default:
            return 30;
    }
}

function essb_get_period_stats($date_from, $date_to) {
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;
    
    $stats = new stdClass();
    $stats->total_clicks = 0;
    $stats->mobile_clicks = 0;
    $stats->desktop_clicks = 0;
    $stats->unique_posts = 0;
    $stats->networks = array();
    $stats->positions = array();
    
    if (empty($date_from) || empty($date_to)) {
        return $stats;
    }
    
    $query = $wpdb->prepare(
        "SELECT 
            COUNT(*) as total_clicks,
            SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
            SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'",
        $date_from, $date_to
    );
    
    $totals = $wpdb->get_row($query);
    if ($totals) {
        $stats->total_clicks = intval($totals->total_clicks);
        $stats->mobile_clicks = intval($totals->mobile_clicks);
        $stats->desktop_clicks = intval($totals->desktop_clicks);
    }
    
    $network_query = $wpdb->prepare(
        "SELECT 
            essb_service as network,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_service",
        $date_from, $date_to
    );
    
    $network_results = $wpdb->get_results($network_query);
    if ($network_results) {
        foreach($network_results as $row) {
            $stats->networks[$row->network] = intval($row->clicks);
        }
    }
    
    $position_query = $wpdb->prepare(
        "SELECT 
            essb_position as position,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_position",
        $date_from, $date_to
    );
    
    $position_results = $wpdb->get_results($position_query);
    if ($position_results) {
        foreach($position_results as $row) {
            $stats->positions[$row->position] = intval($row->clicks);
        }
    }
    
    $post_query = $wpdb->prepare(
        "SELECT COUNT(DISTINCT essb_post_id) as unique_posts 
         FROM {$table_name} 
         WHERE DATE(essb_date) BETWEEN %s AND %s",
        $date_from, $date_to
    );
    $post_result = $wpdb->get_row($post_query);
    if ($post_result) {
        $stats->unique_posts = intval($post_result->unique_posts);
    }
    
    return $stats;
}

function essb_get_chart_data($date_from, $date_to) {
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;
    
    if (empty($date_from) || empty($date_to)) {
        return array();
    }
    
    // Fix: Use proper prepare with correct number of placeholders
    $query = $wpdb->prepare(
        "SELECT 
            DATE(essb_date) as period,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY DATE(essb_date)
        ORDER BY period ASC",
        $date_from, $date_to
    );
    
    $results = $wpdb->get_results($query);
    
    if (empty($results)) {
        return array();
    }
    
    return $results;
}

function essb_get_top_posts($date_from, $date_to, $limit = 10) {
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;
    
    if (empty($date_from) || empty($date_to)) {
        return array();
    }
    
    $query = $wpdb->prepare(
        "SELECT 
            essb_post_id,
            COUNT(*) as total_clicks,
            SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
            SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_post_id
        ORDER BY total_clicks DESC
        LIMIT %d",
        $date_from, $date_to, $limit
    );
    
    $results = $wpdb->get_results($query);
    
    if (empty($results)) {
        return array();
    }
    
    $posts = array();
    foreach($results as $post) {
        $post->title = get_the_title($post->essb_post_id);
        $post->permalink = get_permalink($post->essb_post_id);
        $post->top_network = null;
        $post->top_network_clicks = 0;
        
        // Get top network for this post
        $network_query = $wpdb->prepare(
            "SELECT essb_service, COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_post_id = %d
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY essb_service
            ORDER BY clicks DESC
            LIMIT 1",
            $post->essb_post_id, $date_from, $date_to
        );
        $top_network = $wpdb->get_row($network_query);
        if ($top_network && isset($top_network->essb_service)) {
            $post->top_network = $top_network->essb_service;
            $post->top_network_clicks = intval($top_network->clicks);
        }
        
        $posts[] = $post;
    }
    
    return $posts;
}

function essb_get_tab_url($view, $period, $date_from, $date_to) {
    $url = admin_url('admin.php?page=essb_redirect_analytics&tab=analytics&view=' . $view . '&period=' . $period);
    
    // Always preserve custom date parameters if they exist and period is custom
    if ($period == 'custom' && !empty($date_from) && !empty($date_to)) {
        $url .= '&date_from=' . $date_from . '&date_to=' . $date_to;
    }
    
    return $url;
}

function essb_calculate_percentage_change($current, $previous) {
    if ($previous == 0) return $current > 0 ? 100 : 0;
    return round(($current - $previous) / $previous * 100, 1);
}

function essb_get_full_date_range_chart_data($date_from, $date_to) {
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;
    
    if (empty($date_from) || empty($date_to)) {
        return array();
    }
    
    // Get actual data from database
    $query = $wpdb->prepare(
        "SELECT 
            DATE(essb_date) as period,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY DATE(essb_date)
        ORDER BY period ASC",
        $date_from, $date_to
    );
    
    $results = $wpdb->get_results($query);
    
    // Create lookup array for existing data
    $data_lookup = array();
    if (!empty($results)) {
        foreach ($results as $row) {
            $data_lookup[$row->period] = intval($row->clicks);
        }
    }
    
    // Generate full date range with zeros for missing dates
    $full_data = array();
    $current_date = strtotime($date_from);
    $end_date = strtotime($date_to);
    
    while ($current_date <= $end_date) {
        $date_key = date('Y-m-d', $current_date);
        $clicks = isset($data_lookup[$date_key]) ? $data_lookup[$date_key] : 0;
        
        $full_data[] = array(
            'date' => $date_key,
            'label' => date('M j', $current_date),
            'clicks' => $clicks
        );
        
        $current_date = strtotime('+1 day', $current_date);
    }
    
    return $full_data;
}