<?php
/**
 * Database operations manager for the Studiou WC Order Print Statuses plugin.
 * 
 * This class centralizes database queries for better maintainability.
 */

defined('ABSPATH') || exit;

class Studiou_DB_Manager {
    /**
     * Get orders data for printing export
     * 
     * @param array $order_ids Array of order IDs
     * @return array Array of order data
     */
    public static function get_orders_for_printing($order_ids) {
        global $wpdb;
        
        $query = "
            SELECT
                `orders`.`id` AS `order_no`,
                `t`.`name` AS `prod_cat`,
                `products`.`post_title` AS `prod_name`,
                `product_variations`.`post_title` AS `prod_var`,
                `product_variations_name`.`name` AS `prod_var_type`,
                `images`.`guid` AS `prod_img_url`,
                `order_products`.`product_qty` AS `qty`,
                `orders`.`billing_email` AS `email`
            FROM (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            `{$wpdb->prefix}wc_orders` `orders`
                                        JOIN `{$wpdb->prefix}wc_order_product_lookup` `order_products`
                                        ON
                                            (
                                                `orders`.`id` = `order_products`.`order_id`
                                            )
                                        )
                                    JOIN `{$wpdb->posts}` `products`
                                    ON
                                        (
                                            `products`.`ID` = `order_products`.`product_id`
                                        )
                                    )
                                JOIN `{$wpdb->posts}` `product_variations`
                                ON
                                    (
                                        `product_variations`.`ID` = `order_products`.`variation_id`
                                    )
                                LEFT JOIN `{$wpdb->prefix}wc_product_attributes_lookup` `product_variations_attr`
                                ON
                                    (
                                        `product_variations_attr`.`product_id` = `product_variations`.`ID` AND `product_variations_attr`.`product_or_parent_id` = `products`.`ID` AND `product_variations_attr`.`taxonomy` = 'pa_format'
                                    )
                                LEFT JOIN `{$wpdb->terms}` `product_variations_name`
                                ON
                                    (
                                        `product_variations_name`.`term_id` = `product_variations_attr`.`term_id`
                                    )
                                )
                            LEFT JOIN `{$wpdb->postmeta}` `product_meta`
                            ON
                                (
                                    `product_meta`.`post_id` = `products`.`ID` AND `product_meta`.`meta_key` = '_thumbnail_id'
                                )
                            )
                        LEFT JOIN `{$wpdb->posts}` `images`
                        ON
                            (
                                `images`.`ID` = `product_meta`.`meta_value` AND `images`.`post_type` = 'attachment'
                            )
                        JOIN `{$wpdb->term_relationships}` `tr`
                        ON
                            (`products`.`ID` = `tr`.`object_id`)
                        )
                    JOIN `{$wpdb->term_taxonomy}` `tt`
                    ON
                        (
                            `tr`.`term_taxonomy_id` = `tt`.`term_taxonomy_id`
                        )
                    )
                JOIN `{$wpdb->terms}` `t`
                ON
                    (`tt`.`term_id` = `t`.`term_id`)
                )
            WHERE
                `orders`.`id` IN (" . implode(',', array_map('intval', $order_ids)) . ") 
                AND `tt`.`taxonomy` = 'product_cat'
            ORDER BY
                `orders`.`id`
        ";
        
        $results = $wpdb->get_results($query);
        
        // Error handling for database query failures
        if ($results === false) {
            UtilsLog::log('SQL Error: ' . $wpdb->last_error);
            return array();
        }
        
        return $results;
    }

    /**
     * Set order status to 'to-print'
     * 
     * @param array $order_ids Array of order IDs
     * @return int Number of orders updated
     */
    public static function set_orders_to_prepare_printing($order_ids) {
        $updated_count = 0;
        
        foreach ($order_ids as $order_id) {
            UtilsLog::log('Setting status Order #' . $order_id . ' to "to-print"');
            $order = wc_get_order($order_id);
            if ($order) {
                $order->update_status('to-print');
                $order->update_meta_data('to_print_date', current_time('mysql'));
                $order->save();
                $updated_count++;
                UtilsLog::log('Order #' . $order_id . ' marked as "to-print"');
                UtilsLog::message('Order #' . $order_id . ' marked as "to-print"' );
            } else {
                UtilsLog::log('Order #' . $order_id . ' not found');
            }
        }
        
        return $updated_count;
    }

    /**
     * Set order status to 'in-print'
     * 
     * @param array $order_ids Array of order IDs
     * @return int Number of orders updated
     */
    public static function set_orders_to_in_printing($order_ids) {
        $updated_count = 0;
        
        foreach ($order_ids as $order_id) {
            $order = wc_get_order($order_id);
            if ($order) {
                $order->update_status('in-print');
                $order->update_meta_data('in_print_date', current_time('mysql'));
                $order->save();
                $updated_count++;
                UtilsLog::message('Order #' . $order_id . ' marked as "in-print"' );
            }
        }
        
        return $updated_count;
    }

    /**
     * Import inprint protocol data
     * 
     * @param array $csv_data Parsed CSV data
     * @return array Array with updated count and errors
     */
    public static function import_inprint_protocol($csv_data) {
        $updated_count = 0;
        $errors = array();

        foreach ($csv_data as $row) {
            if (!isset($row['order_no']) || !isset($row['externalorder']) || !isset($row['externalorderdate'])) {
                $errors[] = __('Invalid row format in CSV.', 'studiou-wc-ord-print-statuses');
                continue;
            }

            $order = wc_get_order($row['order_no']);
            if (!$order) {
                $errors[] = sprintf(__('Order %s not found.', 'studiou-wc-ord-print-statuses'), $row['order_no']);
                continue;
            }
            
            $order->update_status('in-print');
            $order->update_meta_data('in_print_date', current_time('mysql'));
            $order->update_meta_data('external_ref_ord_no', $row['externalorder']);
            $order->update_meta_data('external_ref_ord_date', $row['externalorderdate']);
            $order->save();

            $updated_count++;
        }

        return array(
            'updated_count' => $updated_count,
            'errors' => $errors
        );
    }

    /**
     * Import delivered protocol data
     * 
     * @param array $csv_data Parsed CSV data
     * @return array Array with updated count and errors
     */
    public static function import_delivered_protocol($csv_data) {
        $updated_count = 0;
        $errors = array();

        foreach ($csv_data as $row) {
            if (!isset($row['order_no'])) {
                $errors[] = __('Invalid row format in CSV.', 'studiou-wc-ord-print-statuses');
                continue;
            }
           
            $order = wc_get_order($row['order_no']);
            if (!$order) {
                $errors[] = sprintf(__('Order %s not found.', 'studiou-wc-ord-print-statuses'), $row['order_no']);
                continue;
            }

            $order->update_status('completed');
            $order->update_meta_data('external_ref_ord_delivered', current_time('mysql'));
            $order->save();

            $updated_count++;
        }

        return array(
            'updated_count' => $updated_count,
            'errors' => $errors
        );
    }

    /**
     * Parse CSV file
     * 
     * @param string $file_path Path to CSV file
     * @return array|false Parsed CSV data or false on error
     */
    public static function parse_csv($file_path) {
        $csv_data = array();
        if (($handle = fopen($file_path, "r")) !== FALSE) {
            $headers = fgetcsv($handle, 1000, ",");
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $csv_data[] = array_combine($headers, $data);
            }
            fclose($handle);
        }
        return $csv_data;
    }
}