<?php
/**
 * Database operations class
 *
 * Handles database operations related to product categories
 *
 * @since      1.0.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_Cat_Manage_DB {
    
    /**
     * Constructor
     */
    public function __construct() {
    }
    
    /**
     * Get all product categories
     * 
     * @return array Array of product categories with all required properties
     */
    public function get_all_product_categories() {
        // Get all categories regardless of hierarchy, including empty ones
        $args = array(
            'taxonomy'   => 'product_cat',
            'orderby'    => 'term_id', // Order by ID to maintain creation order
            'hide_empty' => false,
            'hierarchical' => false, // Don't nest them, get a flat list
            'get' => 'all', // Get all fields
        );
        
        $categories = get_terms($args);
        $formatted_categories = array();
        
        if (!is_wp_error($categories) && !empty($categories)) {
            // Sort by hierarchy: parent categories first, then children
            $sorted_categories = $this->sort_categories_by_hierarchy($categories);
            
            foreach ($sorted_categories as $category) {
                $formatted_categories[] = $this->format_category($category);
            }
        }
        
        return $formatted_categories;
    }
    
    /**
     * Sort categories by hierarchy - parents first, then children
     * 
     * @param array $categories Array of WP_Term objects
     * @return array Sorted array of categories
     */
    private function sort_categories_by_hierarchy($categories) {
        $parents = array();
        $children = array();
        
        // Separate parents and children
        foreach ($categories as $category) {
            if ($category->parent == 0) {
                $parents[] = $category;
            } else {
                $children[] = $category;
            }
        }
        
        // Sort parents alphabetically
        usort($parents, function($a, $b) {
            return strcmp($a->name, $b->name);
        });
        
        // Recursively add children after their parents
        $sorted = array();
        foreach ($parents as $parent) {
            $sorted[] = $parent;
            $sorted = array_merge($sorted, $this->get_children_recursive($parent->term_id, $children));
        }
        
        // Add any orphaned children (categories with non-existent parents)
        foreach ($children as $child) {
            if (!$this->is_child_added($child, $sorted)) {
                $sorted[] = $child;
            }
        }
        
        return $sorted;
    }
    
    /**
     * Recursively get children of a category
     * 
     * @param int $parent_id Parent category ID
     * @param array $all_children Array of all child categories
     * @return array Array of child categories
     */
    private function get_children_recursive($parent_id, $all_children) {
        $result = array();
        $direct_children = array();
        
        // Find direct children of this parent
        foreach ($all_children as $child) {
            if ($child->parent == $parent_id) {
                $direct_children[] = $child;
            }
        }
        
        // Sort direct children alphabetically
        usort($direct_children, function($a, $b) {
            return strcmp($a->name, $b->name);
        });
        
        // Add each child and its descendants
        foreach ($direct_children as $child) {
            $result[] = $child;
            $result = array_merge($result, $this->get_children_recursive($child->term_id, $all_children));
        }
        
        return $result;
    }
    
    /**
     * Check if a child category has already been added to the sorted array
     * 
     * @param WP_Term $child Child category to check
     * @param array $sorted Array of already sorted categories
     * @return bool True if child is already in sorted array
     */
    private function is_child_added($child, $sorted) {
        foreach ($sorted as $category) {
            if ($category->term_id == $child->term_id) {
                return true;
            }
        }
        return false;
    }
    
   /**
     * Format category data
     * 
     * @param object $category WP_Term object
     * @return array Formatted category data
     */
    private function format_category($category) {
        // Get parent category name
        $parent_name = '';
        if ($category->parent > 0) {
            $parent = get_term($category->parent, 'product_cat');
            if (!is_wp_error($parent)) {
                $parent_name = $parent->name;
            }
        }
        
        // Get display type
        $display_type = get_term_meta($category->term_id, 'display_type', true);
        if (empty($display_type)) {
            $display_type = 'default';
        }
        
        // Get protected category data
        $visibility = 'public';
        $protected_passwords = '';
        
        // Check if WooCommerce Protected Categories plugin is active
        // The debug shows the Barn2 Util class exists, so let's use it first
        if (class_exists('\\Barn2\\Plugin\\WC_Protected_Categories\\Util')) {
            try {
                $visibility_obj = \Barn2\Plugin\WC_Protected_Categories\Util::get_category_visibility($category->term_id);
                if ($visibility_obj && method_exists($visibility_obj, 'get_visibility')) {
                    $visibility = $visibility_obj->get_visibility();
                    
                    // Get passwords using the utility method
                    if ($visibility === 'protected') {
                        $passwords = \Barn2\Plugin\WC_Protected_Categories\Util::get_term_passwords($category->term_id);
                        if (!empty($passwords) && is_array($passwords)) {
                            $protected_passwords = implode('|', $passwords);
                        }
                    }
                }
            } catch (Exception $e) {
                // Fallback to direct meta access if Util methods fail
                $visibility = 'public';
                $protected_passwords = '';
            }
        }
        
        // Fallback to direct meta access if the Util class didn't work
        if ($visibility === 'public') {
            // Get visibility from meta
            $visibility = get_term_meta($category->term_id, 'visibility', true);
            if (empty($visibility)) {
                $visibility = 'public';
            }
            
            // Get passwords if protected
            if ($visibility === 'protected') {
                $password_meta = get_term_meta($category->term_id, 'password', true);
                
                if (!empty($password_meta)) {
                    // The debug shows passwords are stored as serialized arrays
                    // like: a:1:{i:0;s:8:"jH7xK9pF";}
                    
                    // First, check if it's already an array (unserialized)
                    if (is_array($password_meta)) {
                        $passwords = $password_meta;
                    } else {
                        // If it's a string, try to unserialize it
                        $passwords = maybe_unserialize($password_meta);
                    }
                    
                    // Now extract the actual passwords
                    if (is_array($passwords)) {
                        // Filter out empty values and get the actual password strings
                        $password_array = array();
                        foreach ($passwords as $password) {
                            if (!empty($password)) {
                                $password_array[] = $password;
                            }
                        }
                        
                        if (!empty($password_array)) {
                            $protected_passwords = implode('|', $password_array);
                        }
                    } elseif (is_string($passwords) && !empty($passwords)) {
                        // If it's a simple string, use it directly
                        $protected_passwords = $passwords;
                    }
                }
            }
        }
        
        return array(
            'name' => $category->name,
            'url-name' => $category->slug,
            'parent-name' => $parent_name,
            'description' => $category->description,
            'display-type' => $display_type,
            'visibility' => $visibility,
            'protected-passwords' => $protected_passwords,
            'operation' => 'U' // Default operation for existing categories
        );
    }
    
    /**
     * Find category by name
     * 
     * @param string $name Category name
     * @return WP_Term|null Category term object or null if not found
     */
    public function find_category_by_name($name) {
        $args = array(
            'taxonomy'   => 'product_cat',
            'name'       => $name,
            'hide_empty' => false,
        );
        
        $categories = get_terms($args);
        
        if (!is_wp_error($categories) && !empty($categories)) {
            return $categories[0];
        }
        
        return null;
    }
    
    /**
     * Create product category
     * 
     * @param array $category_data Category data
     * @return array Result with status and message
     */
    public function create_category($category_data) {
        // Check if category already exists
        $existing = $this->find_category_by_name($category_data['name']);
        if ($existing) {
            return array(
                'status' => 'skipped',
                'message' => sprintf(
                    __("Product category '%s' already exists. Skipped.", 'studiou-wc-product-cat-manage'),
                    $category_data['name']
                )
            );
        }
        
        // Find parent category ID if parent name is provided
        $parent_id = 0;
        if (!empty($category_data['parent-name'])) {
            $parent = $this->find_category_by_name($category_data['parent-name']);
            if ($parent) {
                $parent_id = $parent->term_id;
            } else {
                return array(
                    'status' => 'error',
                    'message' => sprintf(
                        __("Parent category '%s' not found.", 'studiou-wc-product-cat-manage'),
                        $category_data['parent-name']
                    )
                );
            }
        }
        
        // Create category
        $args = array(
            'description' => $category_data['description'],
            'parent' => $parent_id,
            'slug' => $category_data['url-name'],
        );
        
        $result = wp_insert_term($category_data['name'], 'product_cat', $args);
        
        if (is_wp_error($result)) {
            return array(
                'status' => 'error',
                'message' => $result->get_error_message()
            );
        }
        
        $term_id = $result['term_id'];
        
        // Set display type
        update_term_meta($term_id, 'display_type', $category_data['display-type']);
        
        // Set visibility and password protection if WC Protected Categories is active
        if (class_exists('WC_Protected_Categories') || function_exists('wc_protected_categories')) {
            // Set visibility
            update_term_meta($term_id, 'visibility', $category_data['visibility']);
            
            // Set password protection if visibility is protected and passwords are provided
            if ($category_data['visibility'] === 'protected' && !empty($category_data['protected-passwords'])) {
                $passwords = explode('|', $category_data['protected-passwords']);
                // Filter out empty passwords
                $passwords = array_filter($passwords);
                
                if (empty($passwords)) {
                    // If no valid passwords after filtering, set a default one
                    $passwords = ['password123'];
                }
                
                // Store the passwords array as a single meta entry
                update_term_meta($term_id, 'password', $passwords);
            }
        }
        
        return array(
            'status' => 'created',
            'message' => sprintf(
                __("Product category '%s' created.", 'studiou-wc-product-cat-manage'),
                $category_data['name']
            )
        );
    }
    
    /**
     * Update product category
     * 
     * @param array $category_data Category data
     * @return array Result with status and message
     */
    public function update_category($category_data) {
        // Check if category exists
        $existing = $this->find_category_by_name($category_data['name']);
        if (!$existing) {
            return array(
                'status' => 'error',
                'message' => sprintf(
                    __("Product category '%s' not found.", 'studiou-wc-product-cat-manage'),
                    $category_data['name']
                )
            );
        }

        // M5 (v1.6.2) — tri-state parent handling on update:
        //   * blank `parent-name`      → leave the existing parent untouched
        //                                (previously silently re-parented to root)
        //   * literal `__ROOT__`       → explicitly move to root
        //   * any other non-blank name → resolve and set as before
        // `parent-name` is still a required header; only the value semantics
        // change on the update path. `A` (add) still treats blank as root,
        // since a brand-new category has nothing to "leave alone."
        $raw_parent = isset($category_data['parent-name']) ? trim($category_data['parent-name']) : '';
        $parent_provided = ($raw_parent !== '');
        $parent_id = 0;

        if ($parent_provided) {
            if ($raw_parent === '__ROOT__') {
                $parent_id = 0;
            } else {
                $parent = $this->find_category_by_name($raw_parent);
                if ($parent) {
                    $parent_id = $parent->term_id;
                } else {
                    return array(
                        'status' => 'error',
                        'message' => sprintf(
                            __("Parent category '%s' not found.", 'studiou-wc-product-cat-manage'),
                            $raw_parent
                        )
                    );
                }
            }
        }

        // Update category — only include `parent` in the args when the CSV
        // actually asked us to manage it. Otherwise wp_update_term would
        // silently re-parent every updated child to root on any partial CSV.
        $args = array(
            'description' => $category_data['description'],
            'slug' => $category_data['url-name'],
        );
        if ($parent_provided) {
            $args['parent'] = $parent_id;
        }

        $result = wp_update_term($existing->term_id, 'product_cat', $args);
        
        if (is_wp_error($result)) {
            return array(
                'status' => 'error',
                'message' => $result->get_error_message()
            );
        }
        
        // Set display type
        update_term_meta($existing->term_id, 'display_type', $category_data['display-type']);
        
        // Set visibility and password protection if WC Protected Categories is active
        if (class_exists('WC_Protected_Categories') || function_exists('wc_protected_categories')) {
            // First remove existing protection meta
            delete_term_meta($existing->term_id, 'password');
            delete_term_meta($existing->term_id, 'user_roles');
            delete_term_meta($existing->term_id, 'users');
            
            // Set visibility
            update_term_meta($existing->term_id, 'visibility', $category_data['visibility']);
            
            // Set password protection if visibility is protected and passwords are provided
            if ($category_data['visibility'] === 'protected' && !empty($category_data['protected-passwords'])) {
                $passwords = explode('|', $category_data['protected-passwords']);
                // Filter out empty passwords
                $passwords = array_filter($passwords);
                
                if (empty($passwords)) {
                    // If no valid passwords after filtering, set a default one
                    $passwords = ['password123'];
                }
                
                // Store the passwords array as a single meta entry
                update_term_meta($existing->term_id, 'password', $passwords);
            }
        }
        
        return array(
            'status' => 'updated',
            'message' => sprintf(
                __("Product category '%s' updated.", 'studiou-wc-product-cat-manage'),
                $category_data['name']
            )
        );
    }
    
    /**
     * Delete product category
     * 
     * @param string $category_name Category name
     * @return array Result with status and message
     */
    public function delete_category($category_name) {
        // Check if category exists
        $existing = $this->find_category_by_name($category_name);
        if (!$existing) {
            return array(
                'status' => 'error',
                'message' => sprintf(
                    __("Product category '%s' not found.", 'studiou-wc-product-cat-manage'),
                    $category_name
                )
            );
        }
        
        // Delete category
        $result = wp_delete_term($existing->term_id, 'product_cat');
        
        if (is_wp_error($result)) {
            return array(
                'status' => 'error',
                'message' => $result->get_error_message()
            );
        }
        
        return array(
            'status' => 'deleted',
            'message' => sprintf(
                __("Product category '%s' deleted.", 'studiou-wc-product-cat-manage'),
                $category_name
            )
        );
    }
}