<?php
namespace PrimeLink\Helpers;

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

/**
 * Database Optimization Helper
 * Provides bulk operations for database queries to improve performance
 */
class DatabaseOptimizationHelper {
    
    /**
     * Bulk insert posts (products, coupons, orders)
     * 
     * @param array $posts Array of post data arrays
     * @return array Array of inserted post IDs
     */
    public static function bulk_insert_posts( $posts ) {
        if ( empty( $posts ) ) {
            return array();
        }
        
        global $wpdb;
        
        $inserted_ids = array();
        $now = current_time( 'mysql' );
        $now_gmt = current_time( 'mysql', 1 );
        
        // Prepare values for bulk insert
        $values = array();
        $placeholders = array();
        
        foreach ( $posts as $post_data ) {
            $post_data = wp_parse_args( $post_data, array(
                'post_author'    => get_current_user_id(),
                'post_date'      => $now,
                'post_date_gmt'  => $now_gmt,
                'post_content'   => '',
                'post_title'     => '',
                'post_excerpt'   => '',
                'post_status'    => 'publish',
                'comment_status' => 'closed',
                'ping_status'    => 'closed',
                'post_password'  => '',
                'post_name'      => '',
                'to_ping'        => '',
                'pinged'         => '',
                'post_modified'  => $now,
                'post_modified_gmt' => $now_gmt,
                'post_content_filtered' => '',
                'post_parent'    => 0,
                'guid'           => '',
                'menu_order'     => 0,
                'post_type'      => 'post',
                'post_mime_type' => '',
                'comment_count'  => 0,
            ) );
            
            // Generate post_name if not provided
            if ( empty( $post_data['post_name'] ) && ! empty( $post_data['post_title'] ) ) {
                $post_data['post_name'] = sanitize_title( $post_data['post_title'] );
            }
            
            $placeholders[] = "(%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %d, %s, %s, %d)";
            $values[] = array(
                $post_data['post_author'],
                $post_data['post_date'],
                $post_data['post_date_gmt'],
                $post_data['post_content'],
                $post_data['post_title'],
                $post_data['post_excerpt'],
                $post_data['post_status'],
                $post_data['comment_status'],
                $post_data['ping_status'],
                $post_data['post_password'],
                $post_data['post_name'],
                $post_data['to_ping'],
                $post_data['pinged'],
                $post_data['post_modified'],
                $post_data['post_modified_gmt'],
                $post_data['post_content_filtered'],
                $post_data['post_parent'],
                $post_data['guid'],
                $post_data['menu_order'],
                $post_data['post_type'],
                $post_data['post_mime_type'],
                $post_data['comment_count'],
            );
        }
        
        // Build SQL query
        $sql = "INSERT INTO {$wpdb->posts} (
            post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt,
            post_status, comment_status, ping_status, post_password, post_name,
            to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered,
            post_parent, guid, menu_order, post_type, post_mime_type, comment_count
        ) VALUES ";
        
        $sql .= implode( ', ', $placeholders );
        
        // Flatten values array for prepare
        $flat_values = array();
        foreach ( $values as $row ) {
            foreach ( $row as $val ) {
                $flat_values[] = $val;
            }
        }
        
        $prepared_sql = $wpdb->prepare( $sql, $flat_values );
        $wpdb->query( $prepared_sql );
        
        // Get inserted IDs
        $last_id = $wpdb->insert_id;
        $count = count( $posts );
        for ( $i = $count - 1; $i >= 0; $i-- ) {
            $inserted_ids[] = $last_id - $i;
        }
        
        return $inserted_ids;
    }
    
    /**
     * Bulk update post meta
     * 
     * @param array $meta_data Array of arrays with keys: post_id, meta_key, meta_value
     * @return int Number of rows affected
     */
    public static function bulk_update_post_meta( $meta_data ) {
        if ( empty( $meta_data ) ) {
            return 0;
        }
        
        global $wpdb;
        
        // Use REPLACE INTO for upsert behavior (update if exists, insert if not)
        $values = array();
        $placeholders = array();
        
        foreach ( $meta_data as $meta ) {
            if ( empty( $meta['post_id'] ) || empty( $meta['meta_key'] ) ) {
                continue;
            }
            
            $placeholders[] = "(%d, %s, %s)";
            $values[] = array(
                absint( $meta['post_id'] ),
                sanitize_key( $meta['meta_key'] ),
                maybe_serialize( $meta['meta_value'] ),
            );
        }
        
        if ( empty( $values ) ) {
            return 0;
        }
        
        $sql = "INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value) VALUES ";
        $sql .= implode( ', ', $placeholders );
        $sql .= " ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)";
        
        // Flatten values
        $flat_values = array();
        foreach ( $values as $row ) {
            foreach ( $row as $val ) {
                $flat_values[] = $val;
            }
        }
        
        $prepared_sql = $wpdb->prepare( $sql, $flat_values );
        return $wpdb->query( $prepared_sql );
    }
    
    /**
     * Bulk update user meta
     * 
     * @param array $meta_data Array of arrays with keys: user_id, meta_key, meta_value
     * @return int Number of rows affected
     */
    public static function bulk_update_user_meta( $meta_data ) {
        if ( empty( $meta_data ) ) {
            return 0;
        }
        
        global $wpdb;
        
        $values = array();
        $placeholders = array();
        
        foreach ( $meta_data as $meta ) {
            if ( empty( $meta['user_id'] ) || empty( $meta['meta_key'] ) ) {
                continue;
            }
            
            $placeholders[] = "(%d, %s, %s)";
            $values[] = array(
                absint( $meta['user_id'] ),
                sanitize_key( $meta['meta_key'] ),
                maybe_serialize( $meta['meta_value'] ),
            );
        }
        
        if ( empty( $values ) ) {
            return 0;
        }
        
        $sql = "INSERT INTO {$wpdb->usermeta} (user_id, meta_key, meta_value) VALUES ";
        $sql .= implode( ', ', $placeholders );
        $sql .= " ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)";
        
        // Flatten values
        $flat_values = array();
        foreach ( $values as $row ) {
            foreach ( $row as $val ) {
                $flat_values[] = $val;
            }
        }
        
        $prepared_sql = $wpdb->prepare( $sql, $flat_values );
        return $wpdb->query( $prepared_sql );
    }
    
    /**
     * Batch lookup products by SKU
     * 
     * @param array $skus Array of SKU strings
     * @return array Associative array: SKU => post_id
     */
    public static function batch_lookup_products_by_sku( $skus ) {
        if ( empty( $skus ) ) {
            return array();
        }
        
        global $wpdb;
        
        $skus = array_map( 'sanitize_text_field', $skus );
        $skus = array_filter( $skus );
        
        if ( empty( $skus ) ) {
            return array();
        }
        
        $placeholders = implode( ',', array_fill( 0, count( $skus ), '%s' ) );
        $sql = $wpdb->prepare(
            "SELECT pm.post_id, pm.meta_value as sku
             FROM {$wpdb->postmeta} pm
             INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
             WHERE pm.meta_key = '_sku'
             AND pm.meta_value IN ($placeholders)
             AND p.post_type = 'product'
             AND p.post_status != 'trash'",
            $skus
        );
        
        $results = $wpdb->get_results( $sql, ARRAY_A );
        
        $sku_map = array();
        foreach ( $results as $row ) {
            $sku_map[ $row['sku'] ] = absint( $row['post_id'] );
        }
        
        return $sku_map;
    }
    
    /**
     * Batch lookup users by email
     * 
     * @param array $emails Array of email addresses
     * @return array Associative array: email => user_id
     */
    public static function batch_lookup_users_by_email( $emails ) {
        if ( empty( $emails ) ) {
            return array();
        }
        
        global $wpdb;
        
        $emails = array_map( 'sanitize_email', $emails );
        $emails = array_filter( $emails, 'is_email' );
        
        if ( empty( $emails ) ) {
            return array();
        }
        
        $placeholders = implode( ',', array_fill( 0, count( $emails ), '%s' ) );
        $sql = $wpdb->prepare(
            "SELECT ID, user_email
             FROM {$wpdb->users}
             WHERE user_email IN ($placeholders)",
            $emails
        );
        
        $results = $wpdb->get_results( $sql, ARRAY_A );
        
        $email_map = array();
        foreach ( $results as $row ) {
            $email_map[ strtolower( $row['user_email'] ) ] = absint( $row['ID'] );
        }
        
        return $email_map;
    }
    
    /**
     * Batch lookup products by title (for fallback matching)
     * 
     * @param array $titles Array of product titles
     * @return array Associative array: title => post_id
     */
    public static function batch_lookup_products_by_title( $titles ) {
        if ( empty( $titles ) ) {
            return array();
        }
        
        global $wpdb;
        
        $title_map = array();
        
        // Process in smaller batches to avoid query limits
        $batches = array_chunk( $titles, 100 );
        
        foreach ( $batches as $batch ) {
            $batch = array_map( 'sanitize_text_field', $batch );
            $batch = array_filter( $batch );
            
            if ( empty( $batch ) ) {
                continue;
            }
            
            $placeholders = implode( ',', array_fill( 0, count( $batch ), '%s' ) );
            $sql = $wpdb->prepare(
                "SELECT ID, post_title
                 FROM {$wpdb->posts}
                 WHERE post_title IN ($placeholders)
                 AND post_type = 'product'
                 AND post_status != 'trash'
                 ORDER BY ID DESC",
                $batch
            );
            
            $results = $wpdb->get_results( $sql, ARRAY_A );
            
            foreach ( $results as $row ) {
                // Use first match for each title
                if ( ! isset( $title_map[ $row['post_title'] ] ) ) {
                    $title_map[ $row['post_title'] ] = absint( $row['ID'] );
                }
            }
        }
        
        return $title_map;
    }
    
    /**
     * Batch lookup coupons by code
     * 
     * @param array $codes Array of coupon codes
     * @return array Associative array: code => post_id
     */
    public static function batch_lookup_coupons_by_code( $codes ) {
        if ( empty( $codes ) ) {
            return array();
        }
        
        global $wpdb;
        
        $codes = array_map( 'sanitize_text_field', $codes );
        $codes = array_filter( $codes );
        
        if ( empty( $codes ) ) {
            return array();
        }
        
        $placeholders = implode( ',', array_fill( 0, count( $codes ), '%s' ) );
        $sql = $wpdb->prepare(
            "SELECT ID, post_title as code
             FROM {$wpdb->posts}
             WHERE post_title IN ($placeholders)
             AND post_type = 'shop_coupon'
             AND post_status != 'trash'",
            $codes
        );
        
        $results = $wpdb->get_results( $sql, ARRAY_A );
        
        $code_map = array();
        foreach ( $results as $row ) {
            $code_map[ $row['code'] ] = absint( $row['ID'] );
        }
        
        return $code_map;
    }
    
    /**
     * Batch lookup products by ID
     * 
     * @param array $product_ids Array of product IDs
     * @return array Array of WC_Product objects indexed by ID
     */
    public static function batch_get_products( $product_ids ) {
        if ( empty( $product_ids ) ) {
            return array();
        }
        
        $product_ids = array_map( 'absint', $product_ids );
        $product_ids = array_filter( $product_ids );
        
        if ( empty( $product_ids ) ) {
            return array();
        }
        
        $products = array();
        
        // Get posts in batch
        $posts = get_posts( array(
            'post_type'      => 'product',
            'post__in'       => $product_ids,
            'posts_per_page' => -1,
            'post_status'    => 'any',
        ) );
        
        // Cache products to avoid multiple wc_get_product calls
        foreach ( $posts as $post ) {
            $product = wc_get_product( $post->ID );
            if ( $product ) {
                $products[ $post->ID ] = $product;
            }
        }
        
        return $products;
    }
}

