<?php
/**
 * Database Assets Manager for Studiou WC Order Print Statuses
 *
 * This class handles the creation and dropping of database assets (views)
 * during plugin activation and deactivation.
 */

class DB_Assets_Manager {
    /**
     * Create the necessary database views.
     */
    public static function create_views() {
        global $wpdb;

        $sql = "CREATE OR REPLACE VIEW vsu_orders_car_prod_qty_img AS
                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`
                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`.`status` = 'wc-processing' AND `tt`.`taxonomy` = 'product_cat'
                ORDER BY
                    `orders`.`id`;";



        $wpdb->query($sql);
        UtilsLog::log('View created');
    }

    /**
     * Drop the database views.
     */
    public static function drop_views() {
        global $wpdb;

        $sql = "DROP VIEW IF EXISTS vsu_orders_car_prod_qty_img";

        $wpdb->query($sql);
        UtilsLog::log('View dropped');
    }

    /**
     * Activation hook callback.
     */
    public static function on_activate() {
        self::create_views();
    }
    
    /**
     * Deactivation hook callback.
     */
    public static function on_deactivate() {
        self::drop_views();
    }
}