<?php
/**
 * Monthly Reports Integration Class
 * 
 * Integrates Codeon Monthly Reports as an additional feature
 * Only available when license is active
 */

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

class KDSAMREF_Monthly_Reports_Integration {
    
    /**
     * Constructor
     */
    public function __construct() {
        // Always initialize, but check license in admin_page
        $this->init_hooks();
    }
    
    /**
     * Check if monthly reports feature is available
     */
    private function is_feature_available() {
        // Check if license manager exists and license is valid
            global $kdsamref_restaurant_orders;
    
    if (isset($kdsamref_restaurant_orders->license_manager)) {
        return $kdsamref_restaurant_orders->license_manager->is_license_valid();
        }
        
        return false;
    }
    
    /**
     * Initialize hooks
     */
    private function init_hooks() {
        // Hooks are initialized in main plugin class to prevent duplicates
        // HPOS compatibility is declared in main plugin file
    }
    
    /**
     * Add admin menu - Removed duplicate menu
     */
    public function add_admin_menu() {
        // Menu is already added in main plugin file
        // This prevents duplicate menu entries
        return;
    }
    

    
    /**
     * Admin page
     */
    public function admin_page() {
        // Check if license is valid
        if ($this->is_feature_available()) {
            // Show full reports page
$current_month = isset($_GET['month']) ? sanitize_text_field($_GET['month']) : gmdate('Y-m');
            $selected_category = isset($_GET['category']) ? intval($_GET['category']) : 0;
            
            // Get data for template
            $reports_data = $this->get_monthly_reports($current_month, $selected_category);
            $comparison_data = $this->get_monthly_comparison($current_month, $selected_category);
            $categories = $this->get_main_categories();
            
            // Extract variables for template
            extract(array(
                'current_month' => $current_month,
                'selected_category' => $selected_category,
                'reports_data' => $reports_data,
                'comparison_data' => $comparison_data,
                'categories' => $categories
            ));
            
            include_once KDSAMREF_PLUGIN_PATH . 'templates/monthly-reports.php';
        } else {
            // Show upgrade prompt
            include_once KDSAMREF_PLUGIN_PATH . 'templates/monthly-reports-upgrade.php';
        }
    }
    
    /**
     * Get monthly reports data
     */
    public function get_monthly_reports($month, $category_id = 0) {
        global $wpdb;
        
        // Build month start/end in site timezone and convert to GMT for queries
        $start_local = $month . '-01 00:00:00';
$end_local = gmdate('Y-m-t 23:59:59', strtotime($start_local));

        try {
            $wp_timezone = wp_timezone();
            $start_dt = new \DateTime($start_local, $wp_timezone);
            $end_dt = new \DateTime($end_local, $wp_timezone);
            $start_date = $start_dt->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i:s');
            $end_date = $end_dt->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            // Fallback to original strings if conversion fails
            $start_date = $start_local;
            $end_date = $end_local;
        }
        
        $data = array(
            'total_orders' => 0,
            'gross_total' => 0,
            'net_total' => 0,
            'tips_total' => 0,
            'tips_count' => 0,
            'daily_breakdown' => array()
        );
        
        // Check if HPOS is enabled
        $is_hpos = $this->is_hpos_enabled();
        
        if ($is_hpos) {
            $orders_table = $wpdb->prefix . 'wc_orders';
            $order_meta_table = $wpdb->prefix . 'wc_orders_meta';
            $date_field = 'date_created_gmt';
            $id_field = 'id';
        } else {
            $orders_table = $wpdb->posts;
            $order_meta_table = $wpdb->postmeta;
            $date_field = 'post_date_gmt';
            $id_field = 'ID';
        }
        
        // Query to get orders for the month
        // Allowed order statuses to include common and custom in both prefixed/unprefixed forms
        $allowed_statuses = array(
            'completed','processing','preparing','ready-pickup',
            'wc-completed','wc-processing','wc-preparing','wc-ready-pickup'
        );

        if ($is_hpos) {
            if ($category_id > 0) {
                // Get main category + subcategories
                $category_ids = $this->get_category_and_children($category_id);
                // Sanitize category IDs to integers to prevent SQL injection
                $safe_category_ids = array_map('absint', $category_ids);
                $category_ids_string = implode(',', $safe_category_ids);
                
                // With category filter (main + subcategories)
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $orders_query = $wpdb->prepare("
                    SELECT DISTINCT o.id, o.total_amount, o.tax_amount AS tax_total, o.date_created_gmt,
                           DATE(o.date_created_gmt) as order_date
                    FROM {$orders_table} o
                    INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON o.id = oi.order_id
                    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
                    INNER JOIN {$wpdb->prefix}term_relationships tr ON oim.meta_value = tr.object_id
                    WHERE o.type = 'shop_order'
                    AND o.status IN ('completed','processing','preparing','ready-pickup','wc-completed','wc-processing','wc-preparing','wc-ready-pickup')
                    AND o.date_created_gmt >= %s
                    AND o.date_created_gmt <= %s
                    AND oi.order_item_type = 'line_item'
                    AND oim.meta_key = '_product_id'
                    AND tr.term_taxonomy_id IN (
                        SELECT term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy 
                        WHERE term_id IN ($category_ids_string) AND taxonomy = 'product_cat'
                    )
                    ORDER BY o.date_created_gmt DESC
                ", $start_date, $end_date);
            } else {
                // Without category filter
                $orders_query = $wpdb->prepare("
                    SELECT o.id, o.total_amount, o.tax_amount AS tax_total, o.date_created_gmt,
                           DATE(o.date_created_gmt) as order_date
                    FROM {$orders_table} o
                    WHERE o.type = 'shop_order'
                    AND o.status IN ('completed','processing','preparing','ready-pickup','wc-completed','wc-processing','wc-preparing','wc-ready-pickup')
                    AND o.date_created_gmt >= %s
                    AND o.date_created_gmt <= %s
                    ORDER BY o.date_created_gmt DESC
                ", $start_date, $end_date);
            }
        } else {
            if ($category_id > 0) {
                // Get main category + subcategories
                $category_ids = $this->get_category_and_children($category_id);
                // Sanitize category IDs to integers to prevent SQL injection
                $safe_category_ids = array_map('absint', $category_ids);
                $category_ids_string = implode(',', $safe_category_ids);
                
                // With category filter (main + subcategories)
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $orders_query = $wpdb->prepare("
                    SELECT DISTINCT p.ID as id, 
                           pm1.meta_value as total_amount,
                           pm2.meta_value as tax_amount,
                           p.post_date_gmt as date_created_gmt,
                           DATE(p.post_date_gmt) as order_date
                    FROM {$orders_table} p
                    LEFT JOIN {$order_meta_table} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_order_total'
                    LEFT JOIN {$order_meta_table} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_order_tax'
                    INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON p.ID = oi.order_id
                    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
                    INNER JOIN {$wpdb->prefix}term_relationships tr ON oim.meta_value = tr.object_id
                    WHERE p.post_type = 'shop_order'
                    AND p.post_status IN ('wc-completed', 'wc-processing')
                    AND p.post_date_gmt >= %s
                    AND p.post_date_gmt <= %s
                    AND oi.order_item_type = 'line_item'
                    AND oim.meta_key = '_product_id'
                    AND tr.term_taxonomy_id IN (
                        SELECT term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy 
                        WHERE term_id IN ($category_ids_string) AND taxonomy = 'product_cat'
                    )
                    ORDER BY p.post_date_gmt DESC
                ", $start_date, $end_date);
            } else {
                // Without category filter
                $orders_query = $wpdb->prepare("
                    SELECT p.ID as id, 
                           pm1.meta_value as total_amount,
                           pm2.meta_value as tax_amount,
                           p.post_date_gmt as date_created_gmt,
                           DATE(p.post_date_gmt) as order_date
                    FROM {$orders_table} p
                    LEFT JOIN {$order_meta_table} pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_order_total'
                    LEFT JOIN {$order_meta_table} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_order_tax'
                    WHERE p.post_type = 'shop_order'
                    AND p.post_status IN ('wc-completed', 'wc-processing')
                    AND p.post_date_gmt >= %s
                    AND p.post_date_gmt <= %s
                    ORDER BY p.post_date_gmt DESC
                ", $start_date, $end_date);
            }
        }
        
        // Execute the prepared query
        // The query is prepared with $wpdb->prepare() above using placeholders for dates
        // Category IDs are sanitized with absint() before interpolation for security
        // This approach prevents SQL injection while handling dynamic IN clauses
        $orders = $wpdb->get_results($orders_query);
        
        foreach ($orders as $order) {
            $order_id = $order->id;
            $gross_amount = floatval($order->total_amount);
            // HPOS uses tax_total alias above; classic uses tax_amount
            $tax_amount = isset($order->tax_total) ? floatval($order->tax_total) : floatval($order->tax_amount);
            $net_amount = $gross_amount - $tax_amount;
            $order_date = $order->order_date;
            
            // Calculate totals
            $data['total_orders']++;
            $data['gross_total'] += $gross_amount;
            $data['net_total'] += $net_amount;
            
            // Initialize day if necessary
            if (!isset($data['daily_breakdown'][$order_date])) {
                $data['daily_breakdown'][$order_date] = array(
                    'orders' => 0,
                    'gross' => 0,
                    'net' => 0,
                    'tips' => 0
                );
            }
            
            $data['daily_breakdown'][$order_date]['orders']++;
            $data['daily_breakdown'][$order_date]['gross'] += $gross_amount;
            $data['daily_breakdown'][$order_date]['net'] += $net_amount;
            
            // Look for tips (fees with name "Tip")
            $tip_amount = $this->get_order_tip_amount($order_id, $is_hpos);
            if ($tip_amount > 0) {
                $data['tips_total'] += $tip_amount;
                $data['tips_count']++;
                $data['daily_breakdown'][$order_date]['tips'] += $tip_amount;
            }
        }
        
        // Sort days by date
        ksort($data['daily_breakdown']);
        
        return $data;
    }

    /**
     * Fallback builder using WooCommerce API when direct SQL returns no results
     */
    private function build_reports_via_wc_api(string $start_local, string $end_local, int $category_id = 0) {
        $data = array(
            'total_orders' => 0,
            'gross_total' => 0,
            'net_total' => 0,
            'tips_total' => 0,
            'tips_count' => 0,
            'daily_breakdown' => array()
        );

        if (!function_exists('wc_get_orders')) {
            return $data;
        }

        // Prepare date filters in site timezone (use DateTime to avoid wc query strtotime(array) bug)
        try {
            $tz = wp_timezone();
            $start_dt = new \DateTime($start_local, $tz);
            $end_dt = new \DateTime($end_local, $tz);
        } catch (\Throwable $e) {
            $start_dt = new \DateTime($start_local);
            $end_dt = new \DateTime($end_local);
        }

        $statuses = array('wc-completed','wc-processing','wc-preparing','wc-ready-pickup');

        $orders = wc_get_orders(array(
            'status'       => $statuses,
            'limit'        => -1,
            'type'         => 'shop_order',
            'date_created' => $start_dt->format('Y-m-d H:i:s') . '...' . $end_dt->format('Y-m-d H:i:s'),
            'return'       => 'objects',
        ));

        foreach ($orders as $order) {
            // Filter by category if requested (check line items)
            if ($category_id > 0 && !$this->order_contains_category($order, $category_id)) {
                continue;
            }

            $gross_amount = (float) $order->get_total();
            $tax_amount = (float) $order->get_total_tax();
            $net_amount = $gross_amount - $tax_amount;

            $order_date = $order->get_date_created();
$order_date_str = $order_date ? $order_date->date_i18n('Y-m-d') : gmdate('Y-m-d');

            $data['total_orders']++;
            $data['gross_total'] += $gross_amount;
            $data['net_total'] += $net_amount;

            if (!isset($data['daily_breakdown'][$order_date_str])) {
                $data['daily_breakdown'][$order_date_str] = array(
                    'orders' => 0,
                    'gross'  => 0,
                    'net'    => 0,
                    'tips'   => 0,
                );
            }

            $data['daily_breakdown'][$order_date_str]['orders']++;
            $data['daily_breakdown'][$order_date_str]['gross'] += $gross_amount;
            $data['daily_breakdown'][$order_date_str]['net'] += $net_amount;

            // Tip amount via fees
            $tip_amount = 0;
            foreach ($order->get_fees() as $fee) {
                $name = strtolower($fee->get_name());
                if (in_array($name, array('pourboire','tip','bahşış','trinkgeld'))) {
                    $tip_amount += (float) $fee->get_total();
                }
            }
            if ($tip_amount > 0) {
                $data['tips_total'] += $tip_amount;
                $data['tips_count']++;
                $data['daily_breakdown'][$order_date_str]['tips'] += $tip_amount;
            }
        }

        ksort($data['daily_breakdown']);
        return $data;
    }

    private function build_wc_date_range($start_dt, $end_dt) { return array(); }

    private function order_contains_category($order, int $category_id): bool {
        // Include subcategories
        $cat_ids = $this->get_category_and_children($category_id);
        foreach ($order->get_items('line_item') as $item) {
            $product = $item->get_product();
            if (!$product) continue;
            $product_id = $product->get_id();
            $terms = get_the_terms($product_id, 'product_cat');
            if (is_array($terms)) {
                foreach ($terms as $term) {
                    if (in_array((int)$term->term_id, $cat_ids, true)) {
                        return true;
                    }
                }
            }
        }
        return false;
    }
    
    /**
     * Get order tip amount
     */
    private function get_order_tip_amount($order_id, $is_hpos = false) {
        global $wpdb;
        
        // Tips are stored in wp_woocommerce_order_items table as fee type
        // Meta information is in wp_woocommerce_order_itemmeta table
        
        $tip_amount = 0;
        
        // Different tip names in different languages
        $tip_names = array('Pourboire', 'Tip', 'tip', 'pourboire', 'Bahşış', 'Trinkgeld');
        
        foreach ($tip_names as $tip_name) {
            // Find fee type items
            $fee_items = $wpdb->get_results($wpdb->prepare("
                SELECT oi.order_item_id, oi.order_item_name
                FROM {$wpdb->prefix}woocommerce_order_items oi
                WHERE oi.order_id = %d
                AND oi.order_item_type = 'fee'
                AND oi.order_item_name = %s
            ", $order_id, $tip_name));
            
            // Get meta value for each fee item
            foreach ($fee_items as $fee_item) {
                $fee_amount = $wpdb->get_var($wpdb->prepare("
                    SELECT meta_value 
                    FROM {$wpdb->prefix}woocommerce_order_itemmeta 
                    WHERE order_item_id = %d 
                    AND meta_key = '_fee_amount'
                ", $fee_item->order_item_id));
                
                if ($fee_amount) {
                    $tip_amount += floatval($fee_amount);
                }
            }
        }
        
        return $tip_amount;
    }
    
    /**
     * Check if HPOS is enabled
     */
    private function is_hpos_enabled() {
        if (class_exists('\Automattic\WooCommerce\Utilities\OrderUtil')) {
            return \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
        }
        return false;
    }
    
    /**
     * Get main categories
     */
    public function get_main_categories() {
        $categories = get_terms(array(
            'taxonomy' => 'product_cat',
            'hide_empty' => false,
            'parent' => 0, // Only main categories
            'orderby' => 'name',
            'order' => 'ASC'
        ));
        
        return is_wp_error($categories) ? array() : $categories;
    }
    
    /**
     * Get category and children
     */
    private function get_category_and_children($category_id) {
        $category_ids = array($category_id);
        
        // Find subcategories recursively
        $children = get_term_children($category_id, 'product_cat');
        
        if (!is_wp_error($children) && !empty($children)) {
            $category_ids = array_merge($category_ids, $children);
        }
        
        return array_map('intval', $category_ids);
    }
    
    /**
     * Get monthly comparison data
     */
    public function get_monthly_comparison($current_month, $category_id = 0) {
        $comparison_data = array();
        
        // Collect data for last 6 months
        for ($i = 5; $i >= 0; $i--) {
$month = gmdate('Y-m', strtotime("-$i months", strtotime($current_month . '-01')));
            $month_data = $this->get_monthly_reports($month, $category_id);
            
            $comparison_data[] = array(
                'month' => $month,
                'total_orders' => $month_data['total_orders'],
                'gross_total' => $month_data['gross_total'],
                'net_total' => $month_data['net_total'],
                'tips_total' => $month_data['tips_total'],
                'is_current' => ($month === $current_month)
            );
        }
        
        return $comparison_data;
    }
} 