<?php

/**
 * ESSB Analytics Functions
 * Additional functions for the analytics dashboard
 */

function essb_get_filterable_chart_data($detail_type, $detail_item, $date_from, $date_to) {
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;
    
    if (empty($detail_type) || empty($detail_item) || empty($date_from) || empty($date_to)) {
        return array();
    }
    
    $data = array();
    $current_date = strtotime($date_from);
    $end_date = strtotime($date_to);
    
    if ($detail_type == 'network') {
        // Get daily data for network with position and device breakdown
        $query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                essb_position as position,
                SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
                SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_service = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY DATE(essb_date), essb_position
            ORDER BY period ASC",
            $detail_item, $date_from, $date_to
        );
        
        $results = $wpdb->get_results($query);
        
        // Organize data by date
        $daily_data = array();
        foreach ($results as $row) {
            if (!isset($daily_data[$row->period])) {
                $daily_data[$row->period] = array(
                    'positions' => array(),
                    'clicks' => 0
                );
            }
            $daily_data[$row->period]['positions'][$row->position] = array(
                'clicks' => intval($row->clicks),
                'desktop' => intval($row->desktop_clicks),
                'mobile' => intval($row->mobile_clicks)
            );
            $daily_data[$row->period]['clicks'] += intval($row->clicks);
        }
        
        // Fill in missing dates
        while ($current_date <= $end_date) {
            $date_key = date('Y-m-d', $current_date);
            if (isset($daily_data[$date_key])) {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => $daily_data[$date_key]['clicks'],
                    'positions' => $daily_data[$date_key]['positions']
                );
            } else {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => 0,
                    'positions' => array()
                );
            }
            $current_date = strtotime('+1 day', $current_date);
        }
        
    } elseif ($detail_type == 'position') {
        // Get daily data for position with network and device breakdown
        $query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                essb_service as network,
                SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
                SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_position = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY DATE(essb_date), essb_service
            ORDER BY period ASC",
            $detail_item, $date_from, $date_to
        );
        
        $results = $wpdb->get_results($query);
        
        // Organize data by date
        $daily_data = array();
        foreach ($results as $row) {
            if (!isset($daily_data[$row->period])) {
                $daily_data[$row->period] = array(
                    'networks' => array(),
                    'clicks' => 0
                );
            }
            $daily_data[$row->period]['networks'][$row->network] = array(
                'clicks' => intval($row->clicks),
                'desktop' => intval($row->desktop_clicks),
                'mobile' => intval($row->mobile_clicks)
            );
            $daily_data[$row->period]['clicks'] += intval($row->clicks);
        }
        
        // Fill in missing dates
        while ($current_date <= $end_date) {
            $date_key = date('Y-m-d', $current_date);
            if (isset($daily_data[$date_key])) {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => $daily_data[$date_key]['clicks'],
                    'networks' => $daily_data[$date_key]['networks']
                );
            } else {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => 0,
                    'networks' => array()
                );
            }
            $current_date = strtotime('+1 day', $current_date);
        }
        
    } elseif ($detail_type == 'content') {
        // Get daily data for content with network, position, and device breakdown
        $query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                essb_service as network,
                essb_position as position,
                SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
                SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
                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 DATE(essb_date), essb_service, essb_position
            ORDER BY period ASC",
            intval($detail_item), $date_from, $date_to
        );
        
        $results = $wpdb->get_results($query);
        
        // Organize data by date
        $daily_data = array();
        foreach ($results as $row) {
            if (!isset($daily_data[$row->period])) {
                $daily_data[$row->period] = array(
                    'networks' => array(),
                    'positions' => array(),
                    'clicks' => 0,
                    'desktop_clicks' => 0,
                    'mobile_clicks' => 0
                );
            }
            
            if (!isset($daily_data[$row->period]['networks'][$row->network])) {
                $daily_data[$row->period]['networks'][$row->network] = array(
                    'clicks' => 0,
                    'desktop' => 0,
                    'mobile' => 0
                );
            }
            $daily_data[$row->period]['networks'][$row->network]['clicks'] += intval($row->clicks);
            $daily_data[$row->period]['networks'][$row->network]['desktop'] += intval($row->desktop_clicks);
            $daily_data[$row->period]['networks'][$row->network]['mobile'] += intval($row->mobile_clicks);
            
            if (!isset($daily_data[$row->period]['positions'][$row->position])) {
                $daily_data[$row->period]['positions'][$row->position] = array(
                    'clicks' => 0,
                    'desktop' => 0,
                    'mobile' => 0
                );
            }
            $daily_data[$row->period]['positions'][$row->position]['clicks'] += intval($row->clicks);
            $daily_data[$row->period]['positions'][$row->position]['desktop'] += intval($row->desktop_clicks);
            $daily_data[$row->period]['positions'][$row->position]['mobile'] += intval($row->mobile_clicks);
            
            $daily_data[$row->period]['clicks'] += intval($row->clicks);
            $daily_data[$row->period]['desktop_clicks'] += intval($row->desktop_clicks);
            $daily_data[$row->period]['mobile_clicks'] += intval($row->mobile_clicks);
        }
        
        // Fill in missing dates
        while ($current_date <= $end_date) {
            $date_key = date('Y-m-d', $current_date);
            if (isset($daily_data[$date_key])) {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => $daily_data[$date_key]['clicks'],
                    'desktop_clicks' => $daily_data[$date_key]['desktop_clicks'],
                    'mobile_clicks' => $daily_data[$date_key]['mobile_clicks'],
                    'networks' => $daily_data[$date_key]['networks'],
                    'positions' => $daily_data[$date_key]['positions']
                );
            } else {
                $data[] = array(
                    'period' => date('M j', $current_date),
                    'clicks' => 0,
                    'desktop_clicks' => 0,
                    'mobile_clicks' => 0,
                    'networks' => array(),
                    'positions' => array()
                );
            }
            $current_date = strtotime('+1 day', $current_date);
        }
    }
    
    return $data;
}

function essb_get_heatmap_color($intensity)
{
    // Blue color scale from light to dark
    if ($intensity >= 90) return '#1e3a8a'; // Dark blue - navy
    if ($intensity >= 75) return '#1e40af';
    if ($intensity >= 60) return '#2563eb';
    if ($intensity >= 45) return '#3b82f6';
    if ($intensity >= 30) return '#60a5fa';
    if ($intensity >= 15) return '#93c5fd';
    if ($intensity >= 5) return '#bfdbfe';
    return '#eff6ff'; // Very light blue - almost white
}

function essb_get_heatmap_matrix_data($date_from, $date_to, $device_filter = 'all')
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array(
            'networks' => array(),
            'positions' => array(),
            'matrix' => array(),
            'max_clicks' => 0
        );
    }

    // Build device filter condition
    $device_condition = "";
    if ($device_filter == 'desktop') {
        $device_condition = "AND essb_mobile = 'false'";
    } elseif ($device_filter == 'mobile') {
        $device_condition = "AND essb_mobile = 'true'";
    }

    $query = $wpdb->prepare(
        "SELECT 
            essb_service as network,
            essb_position as position,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        {$device_condition}
        GROUP BY essb_service, essb_position
        ORDER BY clicks DESC",
        $date_from,
        $date_to
    );

    $results = $wpdb->get_results($query);

    // Get all unique networks and positions
    $networks = array();
    $positions = array();
    $matrix = array();
    $network_totals = array();

    foreach ($results as $row) {
        $network = $row->network;
        $position = $row->position;
        $clicks = intval($row->clicks);

        if (!in_array($network, $networks)) {
            $networks[] = $network;
        }
        if (!in_array($position, $positions)) {
            $positions[] = $position;
        }

        if (!isset($matrix[$network])) {
            $matrix[$network] = array();
        }

        $matrix[$network][$position] = array(
            'clicks' => $clicks,
            'percentage' => 0
        );

        if (!isset($network_totals[$network])) {
            $network_totals[$network] = 0;
        }
        $network_totals[$network] += $clicks;
    }

    // Calculate percentages for each cell
    $max_clicks = 0;
    foreach ($networks as $network) {
        $total = $network_totals[$network];
        foreach ($positions as $position) {
            if (isset($matrix[$network][$position])) {
                $clicks = $matrix[$network][$position]['clicks'];
                $percentage = $total > 0 ? ($clicks / $total) * 100 : 0;
                $matrix[$network][$position]['percentage'] = $percentage;
                $max_clicks = max($max_clicks, $clicks);
            } else {
                $matrix[$network][$position] = array('clicks' => 0, 'percentage' => 0);
            }
        }
    }

    return array(
        'networks' => $networks,
        'positions' => $positions,
        'matrix' => $matrix,
        'max_clicks' => $max_clicks
    );
}

function essb_get_device_chart_data($date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array();
    }

    $query = $wpdb->prepare(
        "SELECT 
            DATE(essb_date) as period,
            SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
            SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_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);

    // Generate full date range with zeros
    $full_data = array();
    $current_date = strtotime($date_from);
    $end_date = strtotime($date_to);

    $data_lookup = array();
    foreach ($results as $row) {
        $data_lookup[$row->period] = array(
            'desktop' => intval($row->desktop_clicks),
            'mobile' => intval($row->mobile_clicks)
        );
    }

    while ($current_date <= $end_date) {
        $date_key = date('Y-m-d', $current_date);
        $full_data[] = array(
            'date' => $date_key,
            'label' => date('M j', $current_date),
            'desktop' => isset($data_lookup[$date_key]) ? $data_lookup[$date_key]['desktop'] : 0,
            'mobile' => isset($data_lookup[$date_key]) ? $data_lookup[$date_key]['mobile'] : 0
        );
        $current_date = strtotime('+1 day', $current_date);
    }

    return $full_data;
}

function essb_get_position_clicks_by_device($position, $device, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($position) || empty($device) || empty($date_from) || empty($date_to)) {
        return 0;
    }

    // Set mobile value based on device parameter
    if ($device === 'desktop') {
        $mobile_value = 'false';
    } elseif ($device === 'mobile') {
        $mobile_value = 'true';
    } else {
        return 0;
    }

    $query = $wpdb->prepare(
        "SELECT COUNT(*) as clicks
        FROM {$table_name}
        WHERE essb_position = %s
        AND essb_mobile = %s
        AND DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'",
        $position,
        $mobile_value,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_var($query);
    return intval($result);
}

function essb_get_position_performance_by_device($date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array();
    }

    $query = $wpdb->prepare(
        "SELECT 
            essb_position,
            SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
            SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
            COUNT(*) as total_clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_position
        ORDER BY total_clicks DESC",
        $date_from,
        $date_to
    );

    $results = $wpdb->get_results($query);
    $position_data = array();

    foreach ($results as $row) {
        $total = $row->desktop_clicks + $row->mobile_clicks;
        $desktop_percent = $total > 0 ? round(($row->desktop_clicks / $total) * 100, 1) : 0;
        $mobile_percent = $total > 0 ? round(($row->mobile_clicks / $total) * 100, 1) : 0;

        $position_data[] = array(
            'name' => strtolower($row->essb_position),
            'display_name' => ESSBSocialShareAnalyticsBackEnd::position_name($row->essb_position),
            'desktop_clicks' => intval($row->desktop_clicks),
            'mobile_clicks' => intval($row->mobile_clicks),
            'total_clicks' => intval($total),
            'desktop_percent' => $desktop_percent,
            'mobile_percent' => $mobile_percent,
            'device_preference' => $row->desktop_clicks > $row->mobile_clicks ? 'Desktop' : ($row->mobile_clicks > $row->desktop_clicks ? 'Mobile' : 'Equal')
        );
    }

    return $position_data;
}

function essb_get_best_position_for_network($network, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($network) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $query = $wpdb->prepare(
        "SELECT essb_position, COUNT(*) as clicks
        FROM {$table_name}
        WHERE essb_service = %s
        AND DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_position
        ORDER BY clicks DESC
        LIMIT 1",
        $network,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);
    return ($result && isset($result->essb_position)) ? $result->essb_position : null;
}

function essb_get_best_content_for_network($network, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($network) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $query = $wpdb->prepare(
        "SELECT essb_post_id, COUNT(*) as clicks
        FROM {$table_name}
        WHERE essb_service = %s
        AND DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_post_id
        ORDER BY clicks DESC
        LIMIT 1",
        $network,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);

    if ($result && isset($result->essb_post_id)) {
        $result->title = get_the_title($result->essb_post_id);
        $result->permalink = get_permalink($result->essb_post_id);
        return $result;
    }

    return null;
}

function essb_get_top_network_for_position($position, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($position) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $query = $wpdb->prepare(
        "SELECT essb_service, COUNT(*) as clicks
        FROM {$table_name}
        WHERE essb_position = %s
        AND DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_service
        ORDER BY clicks DESC
        LIMIT 1",
        $position,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);
    return ($result && isset($result->essb_service)) ? $result->essb_service : null;
}

function essb_get_top_content_for_position($position, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($position) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $query = $wpdb->prepare(
        "SELECT essb_post_id, COUNT(*) as clicks
        FROM {$table_name}
        WHERE essb_position = %s
        AND DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_post_id
        ORDER BY clicks DESC
        LIMIT 1",
        $position,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);

    if ($result && isset($result->essb_post_id)) {
        $result->title = get_the_title($result->essb_post_id);
        $result->permalink = get_permalink($result->essb_post_id);
        return $result;
    }

    return null;
}

function essb_get_top_position_for_content($post_id, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($post_id) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $query = $wpdb->prepare(
        "SELECT essb_position, 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_position
        ORDER BY clicks DESC
        LIMIT 1",
        $post_id,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);
    return ($result && isset($result->essb_position)) ? $result->essb_position : null;
}

function essb_get_top_network_for_content($post_id, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($post_id) || empty($date_from) || empty($date_to)) {
        return null;
    }

    $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_id,
        $date_from,
        $date_to
    );

    $result = $wpdb->get_row($query);
    return ($result && isset($result->essb_service)) ? $result->essb_service : null;
}

function essb_get_network_performance_by_device($date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array();
    }

    $query = $wpdb->prepare(
        "SELECT 
            essb_service as network,
            SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
            SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_service
        HAVING desktop_clicks > 0 OR mobile_clicks > 0",
        $date_from,
        $date_to
    );

    $results = $wpdb->get_results($query);

    foreach ($results as $result) {
        $total = $result->desktop_clicks + $result->mobile_clicks;
        $result->desktop_percentage = $total > 0 ? ($result->desktop_clicks / $total) * 100 : 0;
        $result->mobile_percentage = $total > 0 ? ($result->mobile_clicks / $total) * 100 : 0;
    }

    return $results;
}

function essb_generate_heatmap_data($date_from, $date_to, $network_filter = '', $position_filter = '', $device_filter = '')
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array();
    }

    $where_conditions = array("DATE(essb_date) BETWEEN %s AND %s");
    $params = array($date_from, $date_to);

    if (!empty($network_filter)) {
        $where_conditions[] = "essb_service = %s";
        $params[] = $network_filter;
    }

    if (!empty($position_filter)) {
        $where_conditions[] = "essb_position = %s";
        $params[] = $position_filter;
    }

    if ($device_filter == 'desktop') {
        $where_conditions[] = "essb_mobile = 'false'";
    } elseif ($device_filter == 'mobile') {
        $where_conditions[] = "essb_mobile = 'true'";
    }

    $where_conditions[] = "essb_service != 'sidebar-close'";
    $where_clause = implode(" AND ", $where_conditions);

    $query = $wpdb->prepare(
        "SELECT 
            essb_service as network,
            essb_position as position,
            COUNT(*) as clicks
        FROM {$table_name}
        WHERE {$where_clause}
        GROUP BY essb_service, essb_position
        ORDER BY clicks DESC
        LIMIT 20",
        $params
    );

    $results = $wpdb->get_results($query);
    $heatmap = array();

    foreach ($results as $result) {
        if ($result && isset($result->network) && isset($result->position)) {
            $heatmap[] = array(
                'label' => ucfirst($result->network) . ' @ ' . ESSBSocialShareAnalyticsBackEnd::position_name($result->position),
                'clicks' => intval($result->clicks)
            );
        }
    }

    return $heatmap;
}


function essb_get_all_networks()
{
    global $essb_networks;
    if (isset($essb_networks) && is_array($essb_networks)) {
        return array_keys($essb_networks);
    }
    return array();
}

function essb_get_all_positions()
{
    if (isset(ESSBSocialShareAnalyticsBackEnd::$positions) && is_array(ESSBSocialShareAnalyticsBackEnd::$positions)) {
        return ESSBSocialShareAnalyticsBackEnd::$positions;
    }
    return array();
}

function essb_analyze_trends($date_from, $date_to, $period = '30days')
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($date_from) || empty($date_to)) {
        return array(
            'network_trends' => array(),
            'position_trends' => array(),
            'top_growing_network' => null,
            'declining_network' => null,
            'emerging_position' => null,
            'desktop_primary' => 'content_top',
            'mobile_primary' => 'float',
            'desktop_network_order' => array(),
            'mobile_network_order' => array()
        );
    }

    $days = essb_get_period_days($period);
    $prev_date_from = date('Y-m-d', strtotime($date_from . ' - ' . $days . ' days'));
    $prev_date_to = date('Y-m-d', strtotime($date_to . ' - ' . $days . ' days'));

    // Get current period stats
    $current_networks = array();
    $network_query = $wpdb->prepare(
        "SELECT essb_service, 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) {
            $current_networks[$row->essb_service] = intval($row->clicks);
        }
    }

    // Get previous period stats
    $prev_networks = array();
    $prev_network_query = $wpdb->prepare(
        "SELECT essb_service, COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_service",
        $prev_date_from,
        $prev_date_to
    );
    $prev_network_results = $wpdb->get_results($prev_network_query);
    if ($prev_network_results) {
        foreach ($prev_network_results as $row) {
            $prev_networks[$row->essb_service] = intval($row->clicks);
        }
    }

    // Calculate network trends
    $all_networks = array_unique(array_merge(array_keys($current_networks), array_keys($prev_networks)));
    $network_trends = array();

    foreach ($all_networks as $network) {
        $current = isset($current_networks[$network]) ? $current_networks[$network] : 0;
        $previous = isset($prev_networks[$network]) ? $prev_networks[$network] : 0;
        $change = $previous > 0 ? (($current - $previous) / $previous) * 100 : ($current > 0 ? 100 : 0);

        $network_trends[] = array(
            'name' => essb_stats_get_network_name($network),
            'current' => $current,
            'previous' => $previous,
            'change' => round($change, 1)
        );
    }

    usort($network_trends, function ($a, $b) {
        return $b['change'] - $a['change'];
    });

    // Get current period positions
    $current_positions = array();
    $position_query = $wpdb->prepare(
        "SELECT essb_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) {
            $current_positions[$row->essb_position] = intval($row->clicks);
        }
    }

    // Get previous period positions
    $prev_positions = array();
    $prev_position_query = $wpdb->prepare(
        "SELECT essb_position, COUNT(*) as clicks
        FROM {$table_name}
        WHERE DATE(essb_date) BETWEEN %s AND %s
        AND essb_service != 'sidebar-close'
        GROUP BY essb_position",
        $prev_date_from,
        $prev_date_to
    );
    $prev_position_results = $wpdb->get_results($prev_position_query);
    if ($prev_position_results) {
        foreach ($prev_position_results as $row) {
            $prev_positions[$row->essb_position] = intval($row->clicks);
        }
    }

    // Calculate position trends
    $all_positions = array_unique(array_merge(array_keys($current_positions), array_keys($prev_positions)));
    $position_trends = array();

    foreach ($all_positions as $position) {
        $current = isset($current_positions[$position]) ? $current_positions[$position] : 0;
        $previous = isset($prev_positions[$position]) ? $prev_positions[$position] : 0;
        $change = $previous > 0 ? (($current - $previous) / $previous) * 100 : ($current > 0 ? 100 : 0);

        $position_trends[] = array(
            'name' => $position,
            'current' => $current,
            'previous' => $previous,
            'change' => round($change, 1)
        );
    }

    usort($position_trends, function ($a, $b) {
        return $b['change'] - $a['change'];
    });

    // Get device-specific network ordering
    $desktop_networks = essb_get_network_performance_by_device($date_from, $date_to);
    if ($desktop_networks && is_array($desktop_networks)) {
        usort($desktop_networks, function ($a, $b) {
            return $b->desktop_clicks - $a->desktop_clicks;
        });
    } else {
        $desktop_networks = array();
    }

    $mobile_networks = essb_get_network_performance_by_device($date_from, $date_to);
    if ($mobile_networks && is_array($mobile_networks)) {
        usort($mobile_networks, function ($a, $b) {
            return $b->mobile_clicks - $a->mobile_clicks;
        });
    } else {
        $mobile_networks = array();
    }

    $desktop_order = array();
    foreach ($desktop_networks as $net) {
        if (isset($net->network)) {
            $desktop_order[] = $net->network;
        }
    }

    $mobile_order = array();
    foreach ($mobile_networks as $net) {
        if (isset($net->network)) {
            $mobile_order[] = $net->network;
        }
    }

    // Determine top growing and declining networks
    $top_growing = !empty($network_trends) ? $network_trends[0] : null;
    $declining = !empty($network_trends) ? end($network_trends) : null;
    $emerging = !empty($position_trends) ? $position_trends[0] : null;

    return array(
        'network_trends' => $network_trends,
        'position_trends' => $position_trends,
        'top_growing_network' => $top_growing,
        'declining_network' => $declining,
        'emerging_position' => $emerging,
        'desktop_primary' => !empty($position_trends) ? $position_trends[0]['name'] : 'content_top',
        'mobile_primary' => 'float',
        'desktop_network_order' => $desktop_order,
        'mobile_network_order' => $mobile_order
    );
}

function essb_generate_recommendations($trends_analysis)
{
    $recommendations = array();

    if (empty($trends_analysis)) {
        return $recommendations;
    }

    // ── Network recommendations ───────────────────────────────────────────────

    if (
        isset($trends_analysis['declining_network'])
        && $trends_analysis['declining_network']
        && $trends_analysis['declining_network']['change'] < -30
    ) {
        $net = essb_stats_get_network_name($trends_analysis['declining_network']['name']);
        $recommendations[] = array(
            'icon'    => '⚠️',
            'type'    => 'Alert',
            'subject' => $net,                                         // ← added
            'title'   => 'Remove Underperforming Network',
            'message' => $net . ' has dropped by ' . abs($trends_analysis['declining_network']['change']) . '% in the last period. Users are not engaging with this network.',
            'action'  => 'Remove ' . $net . ' from your share buttons to reduce clutter and improve user experience.',
        );
    } elseif (
        isset($trends_analysis['declining_network'])
        && $trends_analysis['declining_network']
        && $trends_analysis['declining_network']['change'] < -15
    ) {
        $net = essb_stats_get_network_name($trends_analysis['declining_network']['name']);
        $recommendations[] = array(
            'icon'    => '📉',
            'type'    => 'Warning',
            'subject' => $net,                                         // ← added
            'title'   => 'Monitor Declining Network',
            'message' => $net . ' is losing popularity (' . abs($trends_analysis['declining_network']['change']) . '% decrease).',
            'action'  => 'Consider moving ' . $net . ' to a less prominent position or replacing it if the trend continues.',
        );
    }

    if (
        isset($trends_analysis['top_growing_network'])
        && $trends_analysis['top_growing_network']
        && $trends_analysis['top_growing_network']['change'] > 30
    ) {
        $net = essb_stats_get_network_name($trends_analysis['top_growing_network']['name']);
        $recommendations[] = array(
            'icon'    => '🚀',
            'type'    => 'Opportunity',
            'subject' => $net,                                         // ← added
            'title'   => 'Capitalize on Growing Network',
            'message' => $net . ' is exploding with ' . $trends_analysis['top_growing_network']['change'] . '% growth!',
            'action'  => 'Move ' . $net . ' to the top position and feature it prominently on your site.',
        );
    } elseif (
        isset($trends_analysis['top_growing_network'])
        && $trends_analysis['top_growing_network']
        && $trends_analysis['top_growing_network']['change'] > 15
    ) {
        $net = essb_stats_get_network_name($trends_analysis['top_growing_network']['name']);
        $recommendations[] = array(
            'icon'    => '📈',
            'type'    => 'Suggestion',
            'subject' => $net,                                         // ← added
            'title'   => 'Promote Growing Network',
            'message' => $net . ' is growing steadily (' . $trends_analysis['top_growing_network']['change'] . '% increase).',
            'action'  => 'Give ' . $net . ' better visibility by placing it higher in your button order.',
        );
    }

    // ── Position recommendations ──────────────────────────────────────────────

    $inactive_positions = array();
    if (isset($trends_analysis['position_trends']) && is_array($trends_analysis['position_trends'])) {
        foreach ($trends_analysis['position_trends'] as $p) {
            if ($p['current'] == 0 && $p['previous'] == 0) {
                $inactive_positions[] = ESSBSocialShareAnalyticsBackEnd::position_name($p['name']);
            }
        }
    }

    if (! empty($inactive_positions)) {
        $positions_list = array_slice($inactive_positions, 0, 3);
        $recommendations[] = array(
            'icon'    => '🗑️',
            'type'    => 'Cleanup',
            'subject' => implode(', ', $positions_list),             // ← added
            'title'   => 'Remove Unused Positions',
            'message' => 'These positions have had zero clicks: ' . implode(', ', $positions_list),
            'action'  => 'Remove these positions from your site to simplify your setup and focus on what works.',
        );
    }

    if (
        isset($trends_analysis['emerging_position'])
        && $trends_analysis['emerging_position']
        && $trends_analysis['emerging_position']['change'] > 25
    ) {
        $pos = ESSBSocialShareAnalyticsBackEnd::position_name($trends_analysis['emerging_position']['name']);
        $recommendations[] = array(
            'icon'    => '🔥',
            'type'    => 'Hot Spot',
            'subject' => $pos,                                         // ← added
            'title'   => 'New Hot Position Detected',
            'message' => 'The ' . $pos . ' position is seeing explosive growth (' . $trends_analysis['emerging_position']['change'] . '%).',
            'action'  => 'Add ' . $pos . ' to more pages and consider using it as a primary placement.',
        );
    }

    // ── Device recommendations ────────────────────────────────────────────────

    if (! empty($trends_analysis['mobile_network_order']) && isset($trends_analysis['mobile_network_order'][0])) {
        $net = essb_stats_get_network_name($trends_analysis['mobile_network_order'][0]);
        $recommendations[] = array(
            'icon'    => '📱',
            'type'    => 'Mobile Optimization',
            'subject' => $net,                                         // ← added
            'title'   => 'Mobile Users Prefer ' . $net,
            'message' => 'Mobile visitors are clicking ' . $net . ' more than any other network.',
            'action'  => 'Place ' . $net . ' at the top of your mobile share buttons.',
        );
    }

    if (! empty($trends_analysis['desktop_network_order']) && isset($trends_analysis['desktop_network_order'][0])) {
        $net = essb_stats_get_network_name($trends_analysis['desktop_network_order'][0]);
        $recommendations[] = array(
            'icon'    => '💻',
            'type'    => 'Desktop Optimization',
            'subject' => $net,                                         // ← added
            'title'   => 'Desktop Users Love ' . $net,
            'message' => 'On desktop, ' . $net . ' is the clear favourite.',
            'action'  => 'Keep ' . $net . ' prominent in your desktop share button layout.',
        );
    }

    // ── Order recommendations ─────────────────────────────────────────────────

    if (! empty($trends_analysis['desktop_network_order']) && count($trends_analysis['desktop_network_order']) >= 3) {
        $top_three = array_map('essb_stats_get_network_name', array_slice($trends_analysis['desktop_network_order'], 0, 3));
        $recommendations[] = array(
            'icon'    => '🎯',
            'type'    => 'Order Optimization',
            'subject' => 'Desktop order',                              // ← added
            'title'   => 'Optimal Desktop Network Order',
            'message' => 'Based on usage data, your most effective order is: ' . implode(' → ', $top_three),
            'action'  => 'Rearrange your desktop share buttons to: ' . implode(' → ', $top_three) . '.',
        );
    }

    if (! empty($trends_analysis['mobile_network_order']) && count($trends_analysis['mobile_network_order']) >= 3) {
        $top_three = array_map('essb_stats_get_network_name', array_slice($trends_analysis['mobile_network_order'], 0, 3));
        $recommendations[] = array(
            'icon'    => '📱',
            'type'    => 'Mobile Order',
            'subject' => 'Mobile order',                               // ← added
            'title'   => 'Optimal Mobile Network Order',
            'message' => 'Mobile users respond best to: ' . implode(' → ', $top_three),
            'action'  => 'Update your mobile share button order to: ' . implode(' → ', $top_three) . '.',
        );
    }

    return $recommendations;
}

function essb_get_detail_data($detail_type, $detail_item, $date_from, $date_to)
{
    global $wpdb;
    $table_name = $wpdb->prefix . ESSB3_TRACKER_TABLE;

    if (empty($detail_type) || empty($detail_item) || empty($date_from) || empty($date_to)) {
        return array(
            'total_clicks' => 0,
            'desktop_clicks' => 0,
            'mobile_clicks' => 0,
            'desktop_percent' => 0,
            'mobile_percent' => 0,
            'positions' => array(),
            'content' => array(),
            'networks' => array(),
            'trend' => array()
        );
    }

    $data = array(
        'total_clicks' => 0,
        'desktop_clicks' => 0,
        'mobile_clicks' => 0,
        'desktop_percent' => 0,
        'mobile_percent' => 0,
        'positions' => array(),
        'content' => array(),
        'networks' => array(),
        'trend' => array()
    );

    if ($detail_type == 'network') {
        // Get data for a specific network
        $query = $wpdb->prepare(
            "SELECT 
                essb_position as position,
                essb_mobile,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_service = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY essb_position, essb_mobile",
            $detail_item,
            $date_from,
            $date_to
        );

        $results = $wpdb->get_results($query);

        // Check if results exist and is array before foreach
        if (!empty($results) && is_array($results)) {
            foreach ($results as $row) {
                if (!$row) continue;

                $data['total_clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['mobile_clicks'] += intval($row->clicks);
                } else {
                    $data['desktop_clicks'] += intval($row->clicks);
                }

                if (!isset($data['positions'][$row->position])) {
                    $data['positions'][$row->position] = array('clicks' => 0, 'desktop' => 0, 'mobile' => 0);
                }
                $data['positions'][$row->position]['clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['positions'][$row->position]['mobile'] += intval($row->clicks);
                } else {
                    $data['positions'][$row->position]['desktop'] += intval($row->clicks);
                }
            }
        }

        // Get content data for this network
        $content_query = $wpdb->prepare(
            "SELECT 
                essb_post_id,
                SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
                SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
                COUNT(*) as total_clicks
            FROM {$table_name}
            WHERE essb_service = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY essb_post_id
            ORDER BY total_clicks DESC
            LIMIT 20",
            $detail_item,
            $date_from,
            $date_to
        );

        $content_results = $wpdb->get_results($content_query);

        // Check if content results exist and is array before foreach
        if (!empty($content_results) && is_array($content_results)) {
            foreach ($content_results as $row) {
                if ($row && isset($row->essb_post_id)) {
                    $data['content'][] = array(
                        'post_id' => $row->essb_post_id,
                        'clicks' => intval($row->total_clicks),
                        'desktop' => intval($row->desktop_clicks),
                        'mobile' => intval($row->mobile_clicks)
                    );
                }
            }
        }

        // Get trend data
        $trend_query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_service = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY DATE(essb_date)
            ORDER BY period ASC",
            $detail_item,
            $date_from,
            $date_to
        );

        $trend_results = $wpdb->get_results($trend_query);

        // Check if trend results exist and is array before foreach
        if (!empty($trend_results) && is_array($trend_results)) {
            foreach ($trend_results as $row) {
                if ($row && isset($row->period)) {
                    $data['trend'][] = array(
                        'period' => $row->period,
                        'clicks' => intval($row->clicks)
                    );
                }
            }
        }
    } elseif ($detail_type == 'position') {
        // Get data for a specific position
        $query = $wpdb->prepare(
            "SELECT 
                essb_service as network,
                essb_mobile,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_position = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY essb_service, essb_mobile",
            $detail_item,
            $date_from,
            $date_to
        );

        $results = $wpdb->get_results($query);

        // Check if results exist and is array before foreach
        if (!empty($results) && is_array($results)) {
            foreach ($results as $row) {
                if (!$row) continue;

                $data['total_clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['mobile_clicks'] += intval($row->clicks);
                } else {
                    $data['desktop_clicks'] += intval($row->clicks);
                }

                if (!isset($data['networks'][$row->network])) {
                    $data['networks'][$row->network] = array('clicks' => 0, 'desktop' => 0, 'mobile' => 0);
                }
                $data['networks'][$row->network]['clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['networks'][$row->network]['mobile'] += intval($row->clicks);
                } else {
                    $data['networks'][$row->network]['desktop'] += intval($row->clicks);
                }
            }
        }

        // Get content data for this position
        $content_query = $wpdb->prepare(
            "SELECT 
                essb_post_id,
                SUM(CASE WHEN essb_mobile = 'false' THEN 1 ELSE 0 END) as desktop_clicks,
                SUM(CASE WHEN essb_mobile = 'true' THEN 1 ELSE 0 END) as mobile_clicks,
                COUNT(*) as total_clicks
            FROM {$table_name}
            WHERE essb_position = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY essb_post_id
            ORDER BY total_clicks DESC
            LIMIT 20",
            $detail_item,
            $date_from,
            $date_to
        );

        $content_results = $wpdb->get_results($content_query);

        // Check if content results exist and is array before foreach
        if (!empty($content_results) && is_array($content_results)) {
            foreach ($content_results as $row) {
                if ($row && isset($row->essb_post_id)) {
                    $data['content'][] = array(
                        'post_id' => $row->essb_post_id,
                        'clicks' => intval($row->total_clicks),
                        'desktop' => intval($row->desktop_clicks),
                        'mobile' => intval($row->mobile_clicks)
                    );
                }
            }
        }

        // Get trend data
        $trend_query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                COUNT(*) as clicks
            FROM {$table_name}
            WHERE essb_position = %s
            AND DATE(essb_date) BETWEEN %s AND %s
            AND essb_service != 'sidebar-close'
            GROUP BY DATE(essb_date)
            ORDER BY period ASC",
            $detail_item,
            $date_from,
            $date_to
        );

        $trend_results = $wpdb->get_results($trend_query);

        // Check if trend results exist and is array before foreach
        if (!empty($trend_results) && is_array($trend_results)) {
            foreach ($trend_results as $row) {
                if ($row && isset($row->period)) {
                    $data['trend'][] = array(
                        'period' => $row->period,
                        'clicks' => intval($row->clicks)
                    );
                }
            }
        }
    } elseif ($detail_type == 'content') {
        // Get data for a specific post/page
        $query = $wpdb->prepare(
            "SELECT 
                essb_service as network,
                essb_position as position,
                essb_mobile,
                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, essb_position, essb_mobile",
            intval($detail_item),
            $date_from,
            $date_to
        );

        $results = $wpdb->get_results($query);

        // Check if results exist and is array before foreach
        if (!empty($results) && is_array($results)) {
            foreach ($results as $row) {
                if (!$row) continue;

                $data['total_clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['mobile_clicks'] += intval($row->clicks);
                } else {
                    $data['desktop_clicks'] += intval($row->clicks);
                }

                if (!isset($data['networks'][$row->network])) {
                    $data['networks'][$row->network] = array('clicks' => 0, 'desktop' => 0, 'mobile' => 0);
                }
                $data['networks'][$row->network]['clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['networks'][$row->network]['mobile'] += intval($row->clicks);
                } else {
                    $data['networks'][$row->network]['desktop'] += intval($row->clicks);
                }

                if (!isset($data['positions'][$row->position])) {
                    $data['positions'][$row->position] = array('clicks' => 0, 'desktop' => 0, 'mobile' => 0);
                }
                $data['positions'][$row->position]['clicks'] += intval($row->clicks);
                if ($row->essb_mobile == 'true') {
                    $data['positions'][$row->position]['mobile'] += intval($row->clicks);
                } else {
                    $data['positions'][$row->position]['desktop'] += intval($row->clicks);
                }
            }
        }

        // Get trend data
        $trend_query = $wpdb->prepare(
            "SELECT 
                DATE(essb_date) as period,
                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 DATE(essb_date)
            ORDER BY period ASC",
            intval($detail_item),
            $date_from,
            $date_to
        );

        $trend_results = $wpdb->get_results($trend_query);

        // Check if trend results exist and is array before foreach
        if (!empty($trend_results) && is_array($trend_results)) {
            foreach ($trend_results as $row) {
                if ($row && isset($row->period)) {
                    $data['trend'][] = array(
                        'period' => $row->period,
                        'clicks' => intval($row->clicks)
                    );
                }
            }
        }
    }

    // Calculate percentages
    if ($data['total_clicks'] > 0) {
        $data['desktop_percent'] = ($data['desktop_clicks'] / $data['total_clicks']) * 100;
        $data['mobile_percent'] = ($data['mobile_clicks'] / $data['total_clicks']) * 100;
    }

    return $data;
}
