<?php
/**
 * Product database operations class
 *
 * Handles database operations related to products
 *
 * @since      1.4.0
 * @package    Studiou_WC_Product_Cat_Manage
 * @subpackage Studiou_WC_Product_Cat_Manage/includes
 */

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

class Studiou_WC_Product_Manage_Product_DB {

    /**
     * Constructor
     */
    public function __construct() {
    }

    /**
     * Get all product categories with hierarchy and product counts
     *
     * @return array Array of product categories with hierarchy
     */
    public function get_categories_with_hierarchy() {
        $args = array(
            'taxonomy'   => 'product_cat',
            'orderby'    => 'name',
            'hide_empty' => false,
            'hierarchical' => true,
        );

        $categories = get_terms($args);
        $formatted_categories = array();

        if (!is_wp_error($categories) && !empty($categories)) {
            // Build hierarchy
            $formatted_categories = $this->build_category_hierarchy($categories);
        }

        return $formatted_categories;
    }

    /**
     * Build category hierarchy with indentation
     *
     * @param array $categories Array of WP_Term objects
     * @param int $parent_id Parent category ID
     * @param string $prefix Indentation prefix
     * @return array Formatted category hierarchy
     */
    private function build_category_hierarchy($categories, $parent_id = 0, $prefix = '') {
        $result = array();

        foreach ($categories as $category) {
            if ($category->parent == $parent_id) {
                $result[] = array(
                    'term_id' => $category->term_id,
                    'name' => $prefix . $category->name,
                    'count' => $category->count
                );

                // Recursively add children with increased indentation
                $children = $this->build_category_hierarchy($categories, $category->term_id, $prefix . '— ');
                $result = array_merge($result, $children);
            }
        }

        return $result;
    }

    /**
     * Get products by category IDs
     *
     * @param array $category_ids Array of category IDs
     * @return array Array of product IDs
     */
    public function get_products_by_categories($category_ids) {
        if (empty($category_ids)) {
            return array();
        }

        $args = array(
            'post_type' => 'product',
            'post_status' => 'any',
            'posts_per_page' => -1,
            'fields' => 'ids',
            'tax_query' => array(
                array(
                    'taxonomy' => 'product_cat',
                    'field' => 'term_id',
                    'terms' => $category_ids,
                    'operator' => 'IN'
                )
            )
        );

        $query = new WP_Query($args);
        $product_ids = $query->posts;

        // Also get variations
        foreach ($product_ids as $product_id) {
            $product = wc_get_product($product_id);
            if ($product && $product->is_type('variable')) {
                $variations = $product->get_children();
                $product_ids = array_merge($product_ids, $variations);
            }
        }

        return array_unique($product_ids);
    }

    /**
     * Get product by SKU
     *
     * @param string $sku Product SKU
     * @return WC_Product|null Product object or null if not found
     */
    public function get_product_by_sku($sku) {
        $product_id = wc_get_product_id_by_sku($sku);
        if ($product_id) {
            return wc_get_product($product_id);
        }
        return null;
    }

    /**
     * Check if SKU is unique (excluding specific product ID)
     *
     * @param string $sku Product SKU
     * @param int $exclude_id Product ID to exclude from check
     * @return bool True if unique, false otherwise
     */
    public function is_sku_unique($sku, $exclude_id = 0) {
        $product_id = wc_get_product_id_by_sku($sku);

        if (!$product_id) {
            return true; // SKU doesn't exist
        }

        if ($exclude_id > 0 && $product_id == $exclude_id) {
            return true; // SKU belongs to the product we're updating
        }

        return false; // SKU exists and belongs to another product
    }

    /**
     * Validate image URL
     *
     * @param string $url Image URL
     * @return bool True if valid, false otherwise
     */
    public function validate_image_url($url) {
        if (empty($url)) {
            return false;
        }

        // Check if URL is valid format
        if (!filter_var($url, FILTER_VALIDATE_URL)) {
            return false;
        }

        // Check if URL has common image extensions (optional check)
        $valid_extensions = array('jpg', 'jpeg', 'png', 'gif', 'webp', 'bmp');
        $path_info = pathinfo(parse_url($url, PHP_URL_PATH));
        $extension = isset($path_info['extension']) ? strtolower($path_info['extension']) : '';

        // If URL has extension, verify it's an image extension
        // If no extension, allow it (might be dynamic URL)
        if (!empty($extension) && !in_array($extension, $valid_extensions)) {
            return false;
        }

        // Don't make HTTP request during validation - let the actual download handle failures
        // This prevents timeout issues during validation phase
        return true;
    }

    /**
     * Get product attribute by name
     *
     * @param string $attribute_name Attribute name
     * @return int|null Attribute ID or null if not found
     */
    public function get_attribute_by_name($attribute_name) {
        global $wpdb;

        $attribute_id = $wpdb->get_var($wpdb->prepare(
            "SELECT attribute_id FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_name = %s",
            wc_sanitize_taxonomy_name($attribute_name)
        ));

        return $attribute_id ? intval($attribute_id) : null;
    }

    /**
     * Create product attribute if it doesn't exist
     *
     * @param string $attribute_name Attribute name
     * @return int Attribute ID
     */
    public function create_attribute_if_not_exists($attribute_name) {
        $attribute_id = $this->get_attribute_by_name($attribute_name);

        if ($attribute_id) {
            return $attribute_id;
        }

        // Create attribute
        $attribute = array(
            'slug' => wc_sanitize_taxonomy_name($attribute_name),
            'name' => $attribute_name,
            'type' => 'select',
            'order_by' => 'menu_order',
            'has_archives' => false,
        );

        $attribute_id = wc_create_attribute($attribute);

        if (is_wp_error($attribute_id)) {
            return 0;
        }

        // Register taxonomy
        register_taxonomy(
            wc_attribute_taxonomy_name($attribute_name),
            array('product'),
            array()
        );

        return $attribute_id;
    }
}