<?php

namespace AC_SYNC\Includes\Classes\Order {

    if (!defined('ABSPATH'))
        exit; // Exit if accessed directly

    /**
     * Repository class for actionwear_orders table operations
     */
    class Action_Wear_Order_Repository
    {
        /**
         * Get table name with prefix
         */
        public static function getTableName()
        {
            global $wpdb;
            return $wpdb->prefix . 'actionwear_orders';
        }

        /**
         * Insert a new order tracking record
         *
         * @param int $wc_order_id WooCommerce order ID
         * @return bool|int Insert ID on success, false on failure
         */
        public static function insert($wc_order_id)
        {
            global $wpdb;

            if (self::exists($wc_order_id)) {
                return self::findByOrderId($wc_order_id)->id;
            }

            $result = $wpdb->insert(
                self::getTableName(),
                [
                    'wc_order_id' => $wc_order_id,
                    'sync_status' => 'pending',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql')
                ],
                ['%d', '%s', '%s', '%s']
            );

            return $result ? $wpdb->insert_id : false;
        }

        /**
         * Update an order tracking record
         *
         * @param int $wc_order_id WooCommerce order ID
         * @param array $data Data to update
         * @return bool Success status
         */
        public static function update($wc_order_id, $data)
        {
            global $wpdb;

            $data['updated_at'] = current_time('mysql');

            return $wpdb->update(
                self::getTableName(),
                $data,
                ['wc_order_id' => $wc_order_id]
            ) !== false;
        }

        /**
         * Find order record by WooCommerce order ID
         *
         * @param int $wc_order_id WooCommerce order ID
         * @return object|null Order record or null
         */
        public static function findByOrderId($wc_order_id)
        {
            global $wpdb;
            $table = self::getTableName();

            return $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT * FROM $table WHERE wc_order_id = %d",
                    $wc_order_id
                )
            );
        }

        /**
         * Get paginated list of orders
         *
         * @param int $page Page number (1-indexed)
         * @param int $per_page Items per page
         * @param array $filters Optional filters (sync_status)
         * @return array List of order records
         */
        public static function paginate($page, $per_page, $filters = [])
        {
            global $wpdb;
            $table = self::getTableName();
            $orders_table = $wpdb->prefix . 'wc_orders';
            $posts_table = $wpdb->prefix . 'posts';

            $offset = ($page - 1) * $per_page;

            $where = "1=1";
            $params = [];

            if (!empty($filters['sync_status'])) {
                $where .= " AND ao.sync_status = %s";
                $params[] = $filters['sync_status'];
            }

            // Check if HPOS is enabled (wc_orders table exists and has data)
            $hpos_enabled = $wpdb->get_var("SHOW TABLES LIKE '$orders_table'") === $orders_table;

            if ($hpos_enabled) {
                // HPOS enabled - use wc_orders table
                $sql = "SELECT
                    ao.*,
                    o.id as order_id,
                    o.status as wc_status,
                    o.date_created_gmt as order_date,
                    o.total_amount as total,
                    CONCAT(
                        COALESCE((SELECT meta_value FROM {$wpdb->prefix}wc_orders_meta WHERE order_id = o.id AND meta_key = '_billing_first_name' LIMIT 1), ''),
                        ' ',
                        COALESCE((SELECT meta_value FROM {$wpdb->prefix}wc_orders_meta WHERE order_id = o.id AND meta_key = '_billing_last_name' LIMIT 1), '')
                    ) as customer_name
                FROM $table ao
                LEFT JOIN $orders_table o ON ao.wc_order_id = o.id
                WHERE $where
                ORDER BY ao.created_at DESC
                LIMIT %d OFFSET %d";
            } else {
                // Legacy - use posts table
                $sql = "SELECT
                    ao.*,
                    p.ID as order_id,
                    p.post_status as wc_status,
                    p.post_date as order_date,
                    (SELECT meta_value FROM {$wpdb->prefix}postmeta WHERE post_id = p.ID AND meta_key = '_order_total' LIMIT 1) as total,
                    CONCAT(
                        COALESCE((SELECT meta_value FROM {$wpdb->prefix}postmeta WHERE post_id = p.ID AND meta_key = '_billing_first_name' LIMIT 1), ''),
                        ' ',
                        COALESCE((SELECT meta_value FROM {$wpdb->prefix}postmeta WHERE post_id = p.ID AND meta_key = '_billing_last_name' LIMIT 1), '')
                    ) as customer_name
                FROM $table ao
                LEFT JOIN $posts_table p ON ao.wc_order_id = p.ID
                WHERE $where
                ORDER BY ao.created_at DESC
                LIMIT %d OFFSET %d";
            }

            $params[] = $per_page;
            $params[] = $offset;

            return $wpdb->get_results(
                $wpdb->prepare($sql, ...$params)
            );
        }

        /**
         * Count total orders matching filters
         *
         * @param array $filters Optional filters
         * @return int Count
         */
        public static function count($filters = [])
        {
            global $wpdb;
            $table = self::getTableName();

            $where = "1=1";
            $params = [];

            if (!empty($filters['sync_status'])) {
                $where .= " AND sync_status = %s";
                $params[] = $filters['sync_status'];
            }

            if (empty($params)) {
                return (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE $where");
            }

            return (int) $wpdb->get_var(
                $wpdb->prepare("SELECT COUNT(*) FROM $table WHERE $where", ...$params)
            );
        }

        /**
         * Check if an order tracking record exists
         *
         * @param int $wc_order_id WooCommerce order ID
         * @return bool
         */
        public static function exists($wc_order_id)
        {
            global $wpdb;
            $table = self::getTableName();

            return (bool) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM $table WHERE wc_order_id = %d",
                    $wc_order_id
                )
            );
        }

        /**
         * Delete an order tracking record
         *
         * @param int $wc_order_id WooCommerce order ID
         * @return bool
         */
        public static function delete($wc_order_id)
        {
            global $wpdb;

            return $wpdb->delete(
                self::getTableName(),
                ['wc_order_id' => $wc_order_id],
                ['%d']
            ) !== false;
        }
    }
}
