<?php

if (!defined('ABSPATH')) {
    exit; 
}

function aieo_get_temp_orders_table_for_insights()
{
    global $wpdb;
    $table = get_option('aieo_site_orders_table', $wpdb->prefix . 'aieo_temp_orders');
    if (!$table) {
        return '';
    }
    $table = sanitize_text_field($table);
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) {
        return '';
    }
    
    $table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
    if (!$table_exists) {
        return '';
    }
    return $table;
}

function aieo_fetch_single_product_focus($table, $limit = 10)
{
    global $wpdb;
    $cache_key = 'aieo_insight_single_' . md5($table . $limit);
    $cached = get_transient($cache_key);
    if ($cached !== false) {
        if (!empty($cached)) {
            return $cached;
        }
        delete_transient($cache_key);
    }

    
    
    $sql = $wpdb->prepare(
        "SELECT t.ParentProductId AS product_id,
                   SUM(COALESCE(t.PrimaryQty, 1)) AS total_units,
                   COUNT(*) AS order_lines
            FROM {$table} t
            INNER JOIN (
                SELECT OrderId
                FROM {$table}
                GROUP BY OrderId
                HAVING COUNT(*) = 1
            ) single ON t.OrderId = single.OrderId
            WHERE t.ParentProductId IS NOT NULL
            GROUP BY t.ParentProductId
            ORDER BY total_units DESC
            LIMIT %d",
        $limit
    );
    

    
    $rows = $wpdb->get_results($sql);
    $results = [];
    if ($rows) {
        foreach ($rows as $row) {
            $product_id = (int) $row->product_id;
            if (!$product_id) {
                continue;
            }
            $title = get_the_title($product_id);
            if (!$title) {
                continue;
            }
            $permalink = get_permalink($product_id);
            $results[] = array(
                'product_id' => $product_id,
                'title' => $title,
                'permalink' => $permalink,
                'units' => (float) $row->total_units,
                'orders' => (int) $row->order_lines,
            );
        }
    }

    if (!empty($results)) {
        set_transient($cache_key, $results, HOUR_IN_SECONDS);
    } else {
        delete_transient($cache_key);
    }
    return $results;
}

function aieo_fetch_favourite_products($table, $limit = 10)
{
    global $wpdb;
    $cache_key = 'aieo_insight_favourites_' . md5($table . $limit);
    $cached = get_transient($cache_key);
    if ($cached !== false) {
        if (!empty($cached)) {
            return $cached;
        }
        delete_transient($cache_key);
    }

    
    
    $sql = $wpdb->prepare(
        "SELECT fav.*
            FROM (
                SELECT ParentProductId AS product_id,
                       MAX(COALESCE(FirstItemSales, 0)) AS first_units,
                       MAX(COALESCE(LastItemSales, 0))  AS last_units
                FROM {$table}
                WHERE ParentProductId IS NOT NULL
                GROUP BY ParentProductId
            ) fav
            WHERE (fav.first_units + fav.last_units) > 0
            ORDER BY (fav.first_units + fav.last_units) DESC
            LIMIT %d",
        $limit
    );
    

    
    $rows = $wpdb->get_results($sql);
    $results = [];
    if ($rows) {
        foreach ($rows as $row) {
            $product_id = (int) $row->product_id;
            if (!$product_id) {
                continue;
            }
            $title = get_the_title($product_id);
            if (!$title) {
                continue;
            }
            $permalink = get_permalink($product_id);
            $first_units = (float) $row->first_units;
            $last_units = (float) $row->last_units;
            if (($first_units + $last_units) <= 0) {
                continue;
            }
            $results[] = array(
                'product_id' => $product_id,
                'title' => $title,
                'permalink' => $permalink,
                'first_units' => $first_units,
                'last_units' => $last_units,
                'score' => $first_units + $last_units,
            );
        }
    }

    if (!empty($results)) {
        set_transient($cache_key, $results, HOUR_IN_SECONDS);
    } else {
        delete_transient($cache_key);
    }
    return $results;
}

function aieo_fetch_brand_focus($table, $limit = 10)
{
    global $wpdb;
    $cache_key = 'aieo_insight_brands_' . md5($table . $limit);
    $cached = get_transient($cache_key);
    if ($cached !== false) {
        if (!empty($cached)) {
            return $cached;
        }
        delete_transient($cache_key);
    }

    
    
    $sql = $wpdb->prepare(
        "SELECT brand_name,
                   single_units,
                   first_units,
                   last_units,
                   score
            FROM (
                SELECT brand_name,
                       SUM(single_unit) AS single_units,
                       SUM(first_unit) AS first_units,
                       SUM(last_unit) AS last_units,
                       SUM(single_unit + first_unit + last_unit) AS score
                FROM (
                    SELECT COALESCE(NULLIF(TRIM(Brand), ''), 'Unbranded') AS brand_name,
                           MAX(COALESCE(UniqueItemSales, 0)) AS single_unit,
                           MAX(COALESCE(FirstItemSales, 0)) AS first_unit,
                           MAX(COALESCE(LastItemSales, 0)) AS last_unit
                    FROM {$table}
                    WHERE ParentProductId IS NOT NULL
                    GROUP BY ParentProductId, COALESCE(NULLIF(TRIM(Brand), ''), 'Unbranded')
                ) product_brand
                GROUP BY brand_name
                HAVING SUM(single_unit + first_unit + last_unit) > 0
                ORDER BY score DESC
                LIMIT %d
            ) top_brands
            ORDER BY brand_name ASC",
        $limit
    );
    

    
    $rows = $wpdb->get_results($sql);
    $results = [];
    if ($rows) {
        foreach ($rows as $row) {
            $brand = $row->brand_name;
            $single_units = (float) $row->single_units;
            $first_units = (float) $row->first_units;
            $last_units = (float) $row->last_units;
            $results[] = array(
                'brand' => $brand,
                'single_units' => $single_units,
                'first_units' => $first_units,
                'last_units' => $last_units,
                'score' => $single_units + $first_units + $last_units,
            );
        }
    }

    if (!empty($results)) {
        set_transient($cache_key, $results, HOUR_IN_SECONDS);
    } else {
        delete_transient($cache_key);
    }
    return $results;
}