<?php

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

/**
 * Database handler for FlowDino
 */
class FlowDino_WC_Database {
    
    /**
     * Create database tables
     */
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Category matching table
        $table_category = $wpdb->prefix . 'flowdino_category_matching';
        $sql_category = "CREATE TABLE $table_category (
            id_woocommerce_category bigint(20) NOT NULL,
            id_flowdino_category bigint(20) NOT NULL,
            PRIMARY KEY (id_woocommerce_category)
        ) $charset_collate;";
        
        // Color matching table
        $table_color = $wpdb->prefix . 'flowdino_color_matching';
        $sql_color = "CREATE TABLE $table_color (
            id_woocommerce_color bigint(20) NOT NULL,
            id_flowdino_color bigint(20) NOT NULL,
            PRIMARY KEY (id_woocommerce_color, id_flowdino_color)
        ) $charset_collate;";
        
        // Material matching table
        $table_material = $wpdb->prefix . 'flowdino_material_matching';
        $sql_material = "CREATE TABLE $table_material (
            id_woocommerce_material bigint(20) NOT NULL,
            id_flowdino_material bigint(20) NOT NULL,
            PRIMARY KEY (id_woocommerce_material, id_flowdino_material)
        ) $charset_collate;";
        
        // Size matching table
        $table_size = $wpdb->prefix . 'flowdino_size_matching';
        $sql_size = "CREATE TABLE $table_size (
            id_woocommerce_size bigint(20) NOT NULL,
            id_flowdino_size bigint(20) NOT NULL,
            PRIMARY KEY (id_woocommerce_size, id_flowdino_size)
        ) $charset_collate;";
        
        // Brand matching table
        $table_brand = $wpdb->prefix . 'flowdino_brand_matching';
        $sql_brand = "CREATE TABLE $table_brand (
            id_woocommerce_brand bigint(20) NOT NULL,
            id_flowdino_brand bigint(20) NOT NULL,
            PRIMARY KEY (id_woocommerce_brand)
        ) $charset_collate;";
        
        // Product sync status table
        $table_sync = $wpdb->prefix . 'flowdino_product_sync';
        $sql_sync = "CREATE TABLE $table_sync (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            product_id bigint(20) NOT NULL,
            flowdino_id bigint(20) DEFAULT NULL,
            last_sync datetime DEFAULT NULL,
            sync_status varchar(20) DEFAULT 'pending',
            error_message text DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY product_id (product_id)
        ) $charset_collate;";

        // FlowDino orders import table
        $table_orders = $wpdb->prefix . 'flowdino_orders';
        $sql_orders = "CREATE TABLE $table_orders (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            id_order bigint(20) NOT NULL DEFAULT 0,
            flowdino_order_id bigint(20) NOT NULL,
            flowdino_transaction_id varchar(255) DEFAULT '',
            site_id int(11) NOT NULL DEFAULT 0,
            site_name varchar(255) DEFAULT '',
            order_status varchar(50) DEFAULT 'pending',
            amount decimal(20,6) NOT NULL DEFAULT 0.000000,
            fees decimal(20,6) NOT NULL DEFAULT 0.000000,
            currency varchar(3) DEFAULT 'EUR',
            date_order datetime DEFAULT NULL,
            date_add datetime DEFAULT NULL,
            date_upd datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY flowdino_order_id (flowdino_order_id)
        ) $charset_collate;";

        // FlowDino sales last-sync table
        $table_sales_sync = $wpdb->prefix . 'flowdino_sales_sync';
        $sql_sales_sync = "CREATE TABLE $table_sales_sync (
            site_id int(11) NOT NULL,
            last_sync_date datetime DEFAULT '2025-11-01 00:00:00',
            date_add datetime DEFAULT NULL,
            date_upd datetime DEFAULT NULL,
            PRIMARY KEY (site_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        dbDelta($sql_category);
        dbDelta($sql_color);
        dbDelta($sql_material);
        dbDelta($sql_size);
        dbDelta($sql_brand);
        dbDelta($sql_sync);
        dbDelta($sql_orders);
        dbDelta($sql_sales_sync);
    }
    
    /**
     * Drop database tables
     */
    public function drop_tables() {
        global $wpdb;
        
        $tables = array(
            $wpdb->prefix . 'flowdino_category_matching',
            $wpdb->prefix . 'flowdino_color_matching',
            $wpdb->prefix . 'flowdino_material_matching',
            $wpdb->prefix . 'flowdino_size_matching',
            $wpdb->prefix . 'flowdino_brand_matching',
            $wpdb->prefix . 'flowdino_product_sync',
            $wpdb->prefix . 'flowdino_orders',
            $wpdb->prefix . 'flowdino_sales_sync',
        );
        
        foreach ($tables as $table) {
            // Échapper le nom de la table pour la sécurité SQL
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Custom table management for plugin-specific data
            $wpdb->query("DROP TABLE IF EXISTS " . esc_sql($table));
        }
    }
    
    /**
     * Get category mapping
     */
    public function get_category_mapping($wc_category_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_category_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific category mapping
        return esc_html($wpdb->get_var($wpdb->prepare(
            "SELECT id_flowdino_category FROM " . esc_sql($table) . " WHERE id_woocommerce_category = %d",
            $wc_category_id
        )));
    }
    
    /**
     * Set category mapping
     */
    public function set_category_mapping($wc_category_id, $flowdino_category_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_category_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific category mapping
        return $wpdb->replace($table, array(
            'id_woocommerce_category' => $wc_category_id,
            'id_flowdino_category' => $flowdino_category_id
        ));
    }
    
    /**
     * Get brand mapping
     */
    public function get_brand_mapping($wc_brand_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_brand_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific brand mapping
        return $wpdb->get_var($wpdb->prepare(
            "SELECT id_flowdino_brand FROM " . esc_sql($table) . " WHERE id_woocommerce_brand = %d",
            $wc_brand_id
        ));
    }
    
    /**
     * Set brand mapping
     */
    public function set_brand_mapping($wc_brand_id, $flowdino_brand_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_brand_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific brand mapping
        return $wpdb->replace($table, array(
            'id_woocommerce_brand' => $wc_brand_id,
            'id_flowdino_brand' => $flowdino_brand_id
        ));
    }
    
    /**
     * Get color mappings
     */
    public function get_color_mappings($wc_color_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_color_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific color mapping
        return $wpdb->get_col($wpdb->prepare(
            "SELECT id_flowdino_color FROM " . esc_sql($table) . " WHERE id_woocommerce_color = %d",
            $wc_color_id
        ));
    }
    
    /**
     * Set color mapping
     */
    public function set_color_mapping($wc_color_id, $flowdino_color_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_color_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific color mapping
        return $wpdb->replace($table, array(
            'id_woocommerce_color' => $wc_color_id,
            'id_flowdino_color' => $flowdino_color_id
        ));
    }
    
    /**
     * Get material mappings
     */
    public function get_material_mappings($wc_material_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_material_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific material mapping
        return $wpdb->get_col($wpdb->prepare(
            "SELECT id_flowdino_material FROM " . esc_sql($table) . " WHERE id_woocommerce_material = %d",
            $wc_material_id
        ));
    }
    
    /**
     * Set material mapping
     */
    public function set_material_mapping($wc_material_id, $flowdino_material_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_material_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific material mapping
        return $wpdb->replace($table, array(
            'id_woocommerce_material' => $wc_material_id,
            'id_flowdino_material' => $flowdino_material_id
        ));
    }
    
    /**
     * Get size mappings
     */
    public function get_size_mappings($wc_size_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_size_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific size mapping
        return $wpdb->get_col($wpdb->prepare(
            "SELECT id_flowdino_size FROM " . esc_sql($table) . " WHERE id_woocommerce_size = %d",
            $wc_size_id
        ));
    }
    
    /**
     * Set size mapping
     */
    public function set_size_mapping($wc_size_id, $flowdino_size_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_size_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific size mapping
        return $wpdb->replace($table, array(
            'id_woocommerce_size' => $wc_size_id,
            'id_flowdino_size' => $flowdino_size_id
        ));
    }
    
    /**
     * Get product sync status
     */
    public function get_product_sync_status($product_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_product_sync';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific product sync status
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM " . esc_sql($table) . " WHERE product_id = %d",
            $product_id
        ));
    }
    
    /**
     * Update product sync status
     */
    public function update_product_sync_status($product_id, $status, $flowdino_id = null, $error_message = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_product_sync';
        $data = array(
            'product_id' => $product_id,
            'sync_status' => $status,
            'last_sync' => current_time('mysql'),
            'error_message' => $error_message
        );
        
        if ($flowdino_id) {
            $data['flowdino_id'] = $flowdino_id;
        }
        
        $existing = $this->get_product_sync_status($product_id);
        if ($existing) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update for plugin-specific product sync status
            $result = $wpdb->update($table, $data, array('product_id' => $product_id));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert for plugin-specific product sync status
            $result = $wpdb->insert($table, $data);
        }

        // Keep post meta in sync so hooks can check without a DB query
        update_post_meta($product_id, '_flowdino_sync_status', $status);
        update_post_meta($product_id, '_flowdino_last_sync', current_time('mysql'));

        return $result;
    }
    
    /**
     * Get FlowDino category ID for a WooCommerce product
     * Searches through product categories and their parent categories to find a mapping
     * @param int $wc_product_id
     * @return int|null FlowDino category ID or null if no mapping found
     */
    public function get_flowdino_category_for_product($wc_product_id) {
        $product = wc_get_product($wc_product_id);
        if (!$product) {
            return null;
        }
        
        // Get all categories for this product
        $category_ids = $product->get_category_ids();
        
        // Try to find a direct mapping for any of the product's categories
        foreach ($category_ids as $category_id) {
            $flowdino_cat_id = $this->get_category_mapping($category_id);
            if ($flowdino_cat_id) {
                return $flowdino_cat_id;
            }
        }
        
        // If no direct mapping, try parent categories
        foreach ($category_ids as $category_id) {
            $flowdino_cat_id = $this->get_flowdino_category_from_hierarchy($category_id);
            if ($flowdino_cat_id) {
                return $flowdino_cat_id;
            }
        }
        
        return null;
    }
    
    /**
     * Get FlowDino category ID by traversing category hierarchy
     * @param int $wc_category_id
     * @return int|null
     */
    private function get_flowdino_category_from_hierarchy($wc_category_id) {
        $category = get_term($wc_category_id, 'product_cat');
        if (!$category || is_wp_error($category)) {
            return null;
        }
        
        // Check current category
        $flowdino_cat_id = $this->get_category_mapping($wc_category_id);
        if ($flowdino_cat_id) {
            return $flowdino_cat_id;
        }
        
        // Check parent category recursively
        if ($category->parent > 0) {
            return $this->get_flowdino_category_from_hierarchy($category->parent);
        }
        
        return null;
    }
    
    /**
     * Get all category mappings
     * @return array Array of mappings [wc_category_id => flowdino_category_id]
     */
    public function get_all_category_mappings() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_category_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific category mappings
        $results = $wpdb->get_results("SELECT id_woocommerce_category, id_flowdino_category FROM " . esc_sql($table));
        
        $mappings = array();
        foreach ($results as $result) {
            $mappings[$result->id_woocommerce_category] = $result->id_flowdino_category;
        }
        
        return $mappings;
    }

    /**
     * Get all brand mappings
     */
    public function get_all_brand_mappings() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_brand_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query for plugin-specific brand mappings
        return $wpdb->get_results("SELECT * FROM " . esc_sql($table), ARRAY_A);
    }
    
    /**
     * Delete category mapping
     */
    public function delete_category_mapping($wc_category_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_category_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table delete for plugin-specific category mapping
        return $wpdb->delete($table, array('id_woocommerce_category' => $wc_category_id));
    }
    
    /**
     * Delete brand mapping
     */
    public function delete_brand_mapping($wc_brand_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'flowdino_brand_matching';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table delete for plugin-specific brand mapping
        return $wpdb->delete($table, array('id_woocommerce_brand' => $wc_brand_id));
    }
}
