<?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`,
                    `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}` `images` ON `images`.`post_parent` = `products`.`ID` AND `images`.`post_type` = 'attachment'
                    JOIN `{$wpdb->posts}` `product_variations` ON `product_variations`.`ID` = `order_products`.`variation_id`
                    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();
    }
}