<?php
/**
 * Product Data Handler
 * Handles all product data fetching, filtering, and sorting operations
 */

// If this file is called directly, abort.
if (!defined('ABSPATH')) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

class GGM_Commerce_Product_Data_Handler {

    /** @var array Per-request cache for get_products() results */
    private static $products_cache = [];

    /** @var array|null Per-request cache for get_categories_options() */
    private static $categories_cache = null;

    /** @var array|null Per-request cache for get_tags_options() */
    private static $tags_cache = null;

    /**
     * Get products based on query parameters
     */
    public static function get_products($args = []) {
        global $wpdb;

        $defaults = [
            'posts_per_page' => 20,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'featured_only' => false,
            'category_filter' => '',
            'tag_filter' => '',
            'stock_status' => 'all',
            'price_min' => '',
            'price_max' => '',
            'search' => '',
            'offset' => 0,
            'status' => 'active',  // Default to only showing active products
            'attribute_filters' => []
        ];

        $args = wp_parse_args($args, $defaults);

        // Per-request cache: skip DB if identical query was already run this page load
        // Normalize args for consistent cache keys (sort, cast types)
        $cache_args = $args;
        $cache_args['featured_only'] = (bool) $cache_args['featured_only'];
        $cache_args['offset'] = (int) $cache_args['offset'];
        $cache_args['posts_per_page'] = (int) $cache_args['posts_per_page'];
        ksort($cache_args);
        $cache_key = md5(serialize($cache_args));
        if (isset(self::$products_cache[$cache_key])) {
            return self::$products_cache[$cache_key];
        }

        $table_name = $wpdb->prefix . 'ggmc_products';

        // Build WHERE clauses for category/tag filtering
        $where_conditions = ['1=1'];
        $query_params = [];
        $joins = [];

        // Status filter - by default only show active products on frontend
        if (!empty($args['status'])) {
            if ($args['status'] === 'all') {
                // Don't filter by status - show all products (for admin use)
            } else {
                $where_conditions[] = 'p.status = %s';
                $query_params[] = $args['status'];
            }
        }

        // Category filter - use product_categories relationship table
        if (!empty($args['category_filter']) && is_array($args['category_filter']) && count($args['category_filter']) > 0) {
            $joins[] = "INNER JOIN {$wpdb->prefix}ggmc_product_categories pc ON p.id = pc.product_id";
            $placeholders = implode(',', array_fill(0, count($args['category_filter']), '%d'));
            $where_conditions[] = "pc.category_id IN ($placeholders)";
            $query_params = array_merge($query_params, array_map('intval', $args['category_filter']));
        }

        // Tag filter - use product_tags relationship table
        if (!empty($args['tag_filter']) && is_array($args['tag_filter']) && count($args['tag_filter']) > 0) {
            $joins[] = "INNER JOIN {$wpdb->prefix}ggmc_product_tags pt ON p.id = pt.product_id";
            $placeholders = implode(',', array_fill(0, count($args['tag_filter']), '%d'));
            $where_conditions[] = "pt.tag_id IN ($placeholders)";
            $query_params = array_merge($query_params, array_map('intval', $args['tag_filter']));
        }

        // Featured filter
        if ($args['featured_only']) {
            $where_conditions[] = 'p.featured = 1';
        }

        // Stock status filter
        if ($args['stock_status'] !== 'all') {
            switch ($args['stock_status']) {
                case 'instock':
                    $where_conditions[] = 'p.stock > 0';
                    break;
                case 'outofstock':
                    $where_conditions[] = 'p.stock = 0';
                    break;
                case 'lowstock':
                    $where_conditions[] = 'p.stock > 0 AND p.stock <= 5';
                    break;
            }
        }

        // Attribute filters — each is an EXISTS subquery for AND logic
        if (!empty($args['attribute_filters']) && is_array($args['attribute_filters'])) {
            foreach ($args['attribute_filters'] as $attr_id => $attr_value) {
                if (empty($attr_value) || empty($attr_id)) continue;
                $where_conditions[] = $wpdb->prepare(
                    "EXISTS (SELECT 1 FROM {$wpdb->prefix}ggmc_product_attribute_values pav
                     WHERE pav.product_id = p.id AND pav.attribute_id = %d AND pav.attribute_value = %s)",
                    intval($attr_id),
                    sanitize_text_field($attr_value)
                );
            }
        }

        // Price range filter
        if (!empty($args['price_min']) && is_numeric($args['price_min'])) {
            $where_conditions[] = 'p.price >= %f';
            $query_params[] = floatval($args['price_min']);
        }

        if (!empty($args['price_max']) && is_numeric($args['price_max'])) {
            $where_conditions[] = 'p.price <= %f';
            $query_params[] = floatval($args['price_max']);
        }

        // Search filter
        if (!empty($args['search'])) {
            $where_conditions[] = '(p.name LIKE %s OR p.description LIKE %s OR p.sku LIKE %s)';
            $search_term = '%' . $wpdb->esc_like($args['search']) . '%';
            $query_params[] = $search_term;
            $query_params[] = $search_term;
            $query_params[] = $search_term;
        }

        $where_clause = implode(' AND ', $where_conditions);

        // Order by clause
        $order_by = self::get_order_by_clause($args['orderby'], $args['order']);

        // Limit and offset
        $limit = '';
        if ($args['posts_per_page'] > 0) {
            $limit = $wpdb->prepare(' LIMIT %d OFFSET %d', $args['posts_per_page'], $args['offset']);
        }

        // Build final query with table alias and joins
        $join_clause = !empty($joins) ? implode(' ', $joins) : '';
        $query = "SELECT DISTINCT p.* FROM {$table_name} p {$join_clause} WHERE {$where_clause} {$order_by} {$limit}";

        if (!empty($query_params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is built safely above, interpolated values are sanitized
            $query = $wpdb->prepare($query, $query_params);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is prepared above or contains no user input
        $results = $wpdb->get_results($query);
        self::$products_cache[$cache_key] = $results;
        return $results;
    }
    
    /**
     * Get total product count for pagination
     */
    public static function get_products_count($args = []) {
        global $wpdb;

        $defaults = [
            'featured_only' => false,
            'category_filter' => '',
            'tag_filter' => '',
            'stock_status' => 'all',
            'price_min' => '',
            'price_max' => '',
            'search' => '',
            'status' => 'active'  // Default to only counting active products
        ];

        $args = wp_parse_args($args, $defaults);

        $table_name = $wpdb->prefix . 'ggmc_products';

        // Build where conditions (same logic as get_products)
        $where_conditions = ['1=1'];
        $query_params = [];
        $joins = [];

        // Status filter - by default only count active products on frontend
        if (!empty($args['status'])) {
            if ($args['status'] === 'all') {
                // Don't filter by status - count all products (for admin use)
            } else {
                $where_conditions[] = 'p.status = %s';
                $query_params[] = $args['status'];
            }
        }

        // Category filter - use product_categories relationship table
        if (!empty($args['category_filter']) && is_array($args['category_filter']) && count($args['category_filter']) > 0) {
            $joins[] = "INNER JOIN {$wpdb->prefix}ggmc_product_categories pc ON p.id = pc.product_id";
            $placeholders = implode(',', array_fill(0, count($args['category_filter']), '%d'));
            $where_conditions[] = "pc.category_id IN ($placeholders)";
            $query_params = array_merge($query_params, array_map('intval', $args['category_filter']));
        }

        // Tag filter - use product_tags relationship table
        if (!empty($args['tag_filter']) && is_array($args['tag_filter']) && count($args['tag_filter']) > 0) {
            $joins[] = "INNER JOIN {$wpdb->prefix}ggmc_product_tags pt ON p.id = pt.product_id";
            $placeholders = implode(',', array_fill(0, count($args['tag_filter']), '%d'));
            $where_conditions[] = "pt.tag_id IN ($placeholders)";
            $query_params = array_merge($query_params, array_map('intval', $args['tag_filter']));
        }

        // Featured filter
        if ($args['featured_only']) {
            $where_conditions[] = 'p.featured = 1';
        }

        // Stock status filter
        if ($args['stock_status'] !== 'all') {
            switch ($args['stock_status']) {
                case 'instock':
                    $where_conditions[] = 'p.stock > 0';
                    break;
                case 'outofstock':
                    $where_conditions[] = 'p.stock = 0';
                    break;
                case 'lowstock':
                    $where_conditions[] = 'p.stock > 0 AND p.stock <= 5';
                    break;
            }
        }

        // Attribute filters — each is an EXISTS subquery for AND logic
        if (!empty($args['attribute_filters']) && is_array($args['attribute_filters'])) {
            foreach ($args['attribute_filters'] as $attr_id => $attr_value) {
                if (empty($attr_value) || empty($attr_id)) continue;
                $where_conditions[] = $wpdb->prepare(
                    "EXISTS (SELECT 1 FROM {$wpdb->prefix}ggmc_product_attribute_values pav
                     WHERE pav.product_id = p.id AND pav.attribute_id = %d AND pav.attribute_value = %s)",
                    intval($attr_id),
                    sanitize_text_field($attr_value)
                );
            }
        }

        // Price range filter
        if (!empty($args['price_min']) && is_numeric($args['price_min'])) {
            $where_conditions[] = 'p.price >= %f';
            $query_params[] = floatval($args['price_min']);
        }

        if (!empty($args['price_max']) && is_numeric($args['price_max'])) {
            $where_conditions[] = 'p.price <= %f';
            $query_params[] = floatval($args['price_max']);
        }

        // Search filter
        if (!empty($args['search'])) {
            $where_conditions[] = '(p.name LIKE %s OR p.description LIKE %s OR p.sku LIKE %s)';
            $search_term = '%' . $wpdb->esc_like($args['search']) . '%';
            $query_params[] = $search_term;
            $query_params[] = $search_term;
            $query_params[] = $search_term;
        }

        $where_clause = implode(' AND ', $where_conditions);
        $join_clause = !empty($joins) ? implode(' ', $joins) : '';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- variables are built safely above
        $query = "SELECT COUNT(DISTINCT p.id) FROM {$table_name} p {$join_clause} WHERE {$where_clause}";

        if (!empty($query_params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is built safely above, $query_params are sanitized
            $query = $wpdb->prepare($query, $query_params);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is prepared above or contains no user input
        return (int) $wpdb->get_var($query);
    }
    
    /**
     * Get order by clause based on parameters
     */
    private static function get_order_by_clause($orderby, $order) {
        $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC';
        
        switch ($orderby) {
            case 'name':
                return "ORDER BY name {$order}";
            case 'price':
                return "ORDER BY price {$order}";
            case 'stock':
                return "ORDER BY stock {$order}";
            case 'featured':
                return "ORDER BY featured {$order}, created_at DESC";
            case 'updated_at':
                return "ORDER BY updated_at {$order}";
            case 'created_at':
            default:
                return "ORDER BY created_at {$order}";
        }
    }
    
    /**
     * Get available categories for filter options
     */
    public static function get_categories_options() {
        if (self::$categories_cache !== null) {
            return self::$categories_cache;
        }

        global $wpdb;

        $table_name = $wpdb->prefix . 'ggmc_categories';

        // Check if table exists first
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) == $table_name;

        if (!$table_exists) {
            self::$categories_cache = [];
            return [];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safely constructed from $wpdb->prefix
        $categories = $wpdb->get_results("SELECT id, name FROM {$table_name} WHERE status = 'active' ORDER BY name ASC");

        $options = [];

        foreach ($categories as $category) {
            $options[$category->id] = $category->name;
        }

        self::$categories_cache = $options;
        return $options;
    }

    /**
     * Get available tags for filter options
     */
    public static function get_tags_options() {
        if (self::$tags_cache !== null) {
            return self::$tags_cache;
        }

        global $wpdb;

        $table_name = $wpdb->prefix . 'ggmc_tags';

        // Check if table exists first
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) == $table_name;

        if (!$table_exists) {
            self::$tags_cache = [];
            return [];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safely constructed from $wpdb->prefix
        $tags = $wpdb->get_results("SELECT id, name FROM {$table_name} WHERE status = 'active' ORDER BY name ASC");

        $options = [];

        foreach ($tags as $tag) {
            $options[$tag->id] = $tag->name;
        }

        self::$tags_cache = $options;
        return $options;
    }
    
    /**
     * Get a single product by ID
     */
    public static function get_product($product_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'ggmc_products';
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table_name is safely constructed from $wpdb->prefix
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE id = %d",
            $product_id
        ));
    }
}