<?php
/**
 * WooCommerce aggregates for ZERTI home (SQL + short-lived cache; avoids loading every order/product).
 */

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

const ZERTI_WC_HOME_STATS_CACHE_KEY = 'zerti_home_dashboard_stats_v3';
const ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY = 'zerti_home_dashboard_stats_build_v1';

/**
 * @return bool
 */
function zerti_wc_home_uses_hpos(): bool
{
    return class_exists(\Automattic\WooCommerce\Utilities\OrderUtil::class)
        && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
}

/**
 * @return list<string>
 */
function zerti_wc_home_get_paid_statuses_for_query(): array
{
    $statuses = function_exists('wc_get_is_paid_statuses') ? wc_get_is_paid_statuses() : ['processing', 'completed'];
    $statuses = array_values(array_filter((array) $statuses, 'is_string'));

    if ($statuses === []) {
        $statuses = ['processing', 'completed'];
    }

    $expanded = [];
    foreach ($statuses as $status) {
        $status = (string) $status;
        $expanded[] = $status;
        if (strpos($status, 'wc-') !== 0) {
            $expanded[] = 'wc-' . $status;
        }
    }

    return array_values(array_unique($expanded));
}

/**
 * Status slug variants for SQL (HPOS vs legacy post_status).
 *
 * @return list<string>
 */
function zerti_wc_home_expand_status_for_query(string $status): array
{
    $status = sanitize_key($status);
    if ($status === '') {
        return [];
    }

    $expanded = [$status];
    if (strpos($status, 'wc-') === 0) {
        $expanded[] = substr($status, 3);
    } else {
        $expanded[] = 'wc-' . $status;
    }

    return array_values(array_unique($expanded));
}

/**
 * Count shop orders with a given status (all time; not date-filtered).
 */
function zerti_wc_home_count_shop_orders_by_status(string $status): int
{
    global $wpdb;

    $statuses = zerti_wc_home_expand_status_for_query($status);
    if ($statuses === []) {
        return 0;
    }

    $status_sql = implode(', ', array_fill(0, count($statuses), '%s'));

    if (zerti_wc_home_uses_hpos()) {
        $orders_table = "{$wpdb->prefix}wc_orders";
        $sql = "
            SELECT COUNT(*)
            FROM {$orders_table} o
            WHERE o.type = 'shop_order'
                AND o.status IN ({$status_sql})
        ";
    } else {
        $sql = "
            SELECT COUNT(*)
            FROM {$wpdb->posts} o
            WHERE o.post_type = 'shop_order'
                AND o.post_status IN ({$status_sql})
        ";
    }

    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders built above.
    $prepared = $wpdb->prepare($sql, $statuses);
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $count = $wpdb->get_var($prepared);

    return is_numeric($count) ? (int) $count : 0;
}

/**
 * Processing shop orders count for sidebar badge (WooCommerce status: processing).
 */
function zerti_shop_processing_orders_count(bool $refresh = false): int
{
    static $request_cache = null;

    if ($refresh) {
        $request_cache = null;
    }

    if ($request_cache !== null) {
        return $request_cache;
    }

    if (!function_exists('wc_get_orders') || !zerti_user_can_view_shop_orders_grid()) {
        $request_cache = 0;

        return 0;
    }

    $request_cache = zerti_wc_home_count_shop_orders_by_status('processing');

    return $request_cache;
}

/**
 * Badge label for nav (caps at 99+).
 */
function zerti_shop_processing_orders_badge_label(): string
{
    $count = zerti_shop_processing_orders_count();
    if ($count < 1) {
        return '';
    }

    $display = $count > 99 ? '99+' : (string) $count;

    return zerti_to_persian_digits($display);
}

function zerti_wc_home_ts_to_gmt_datetime(int $ts): string
{
    return (new DateTimeImmutable('@' . $ts))
        ->setTimezone(new DateTimeZone('UTC'))
        ->format('Y-m-d H:i:s');
}

function zerti_wc_home_gmt_datetime_to_site_ymd(string $gmt): string
{
    if ($gmt === '') {
        return '';
    }

    return (new DateTimeImmutable($gmt, new DateTimeZone('UTC')))
        ->setTimezone(zerti_get_timezone())
        ->format('Y-m-d');
}

/**
 * Per-order line revenue + qty for paid orders in a GMT datetime range.
 *
 * @return list<array{order_id:int,order_date_gmt:string,line_total:float,line_tax:float,line_qty:int}>
 */
function zerti_wc_home_query_order_line_rows(int $start_ts, int $end_ts): array
{
    global $wpdb;

    if ($start_ts > $end_ts) {
        return [];
    }

    $statuses = zerti_wc_home_get_paid_statuses_for_query();
    if ($statuses === []) {
        return [];
    }

    $status_sql = implode(', ', array_fill(0, count($statuses), '%s'));
    $start_gmt = zerti_wc_home_ts_to_gmt_datetime($start_ts);
    $end_gmt = zerti_wc_home_ts_to_gmt_datetime($end_ts);

    $oi_table = "{$wpdb->prefix}woocommerce_order_items";
    $oim_table = "{$wpdb->prefix}woocommerce_order_itemmeta";

    if (zerti_wc_home_uses_hpos()) {
        $orders_table = "{$wpdb->prefix}wc_orders";
        $sql = "
            SELECT o.id AS order_id,
                o.date_created_gmt AS order_date_gmt,
                COALESCE(SUM(CAST(lt.meta_value AS DECIMAL(20, 4))), 0) AS line_total,
                COALESCE(SUM(CAST(ltx.meta_value AS DECIMAL(20, 4))), 0) AS line_tax,
                COALESCE(SUM(CAST(qty.meta_value AS UNSIGNED)), 0) AS line_qty
            FROM {$orders_table} o
            INNER JOIN {$oi_table} oi
                ON oi.order_id = o.id AND oi.order_item_type = 'line_item'
            LEFT JOIN {$oim_table} lt
                ON lt.order_item_id = oi.order_item_id AND lt.meta_key = '_line_total'
            LEFT JOIN {$oim_table} ltx
                ON ltx.order_item_id = oi.order_item_id AND ltx.meta_key = '_line_tax'
            LEFT JOIN {$oim_table} qty
                ON qty.order_item_id = oi.order_item_id AND qty.meta_key = '_qty'
            WHERE o.type = 'shop_order'
                AND o.status IN ({$status_sql})
                AND o.date_created_gmt >= %s
                AND o.date_created_gmt <= %s
            GROUP BY o.id, o.date_created_gmt
        ";
    } else {
        $sql = "
            SELECT o.ID AS order_id,
                o.post_date_gmt AS order_date_gmt,
                COALESCE(SUM(CAST(lt.meta_value AS DECIMAL(20, 4))), 0) AS line_total,
                COALESCE(SUM(CAST(ltx.meta_value AS DECIMAL(20, 4))), 0) AS line_tax,
                COALESCE(SUM(CAST(qty.meta_value AS UNSIGNED)), 0) AS line_qty
            FROM {$wpdb->posts} o
            INNER JOIN {$oi_table} oi
                ON oi.order_id = o.ID AND oi.order_item_type = 'line_item'
            LEFT JOIN {$oim_table} lt
                ON lt.order_item_id = oi.order_item_id AND lt.meta_key = '_line_total'
            LEFT JOIN {$oim_table} ltx
                ON ltx.order_item_id = oi.order_item_id AND ltx.meta_key = '_line_tax'
            LEFT JOIN {$oim_table} qty
                ON qty.order_item_id = oi.order_item_id AND qty.meta_key = '_qty'
            WHERE o.post_type = 'shop_order'
                AND o.post_status IN ({$status_sql})
                AND o.post_date_gmt >= %s
                AND o.post_date_gmt <= %s
            GROUP BY o.ID, o.post_date_gmt
        ";
    }

    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders built above.
    $prepared = $wpdb->prepare($sql, array_merge($statuses, [$start_gmt, $end_gmt]));
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $rows = $wpdb->get_results($prepared, ARRAY_A);

    if (!is_array($rows) || $rows === []) {
        return [];
    }

    $out = [];
    foreach ($rows as $row) {
        $out[] = [
            'order_id' => (int) ($row['order_id'] ?? 0),
            'order_date_gmt' => (string) ($row['order_date_gmt'] ?? ''),
            'line_total' => zerti_wc_parse_amount($row['line_total'] ?? 0),
            'line_tax' => zerti_wc_parse_amount($row['line_tax'] ?? 0),
            'line_qty' => (int) ($row['line_qty'] ?? 0),
        ];
    }

    return $out;
}

/**
 * @return array{
 *     sales:array{
 *         today:array{qty:int,total:float},
 *         period:array{qty:int,total:float}
 *     },
 *     revenue_trend:array{labels:list<string>,totals:list<float>}
 * }
 */
function zerti_wc_home_build_sales_revenue_trend(int $chart_days = 30): array
{
    $chart_days = max(1, $chart_days);
    $tz = zerti_get_timezone();
    $now = new DateTimeImmutable('now', $tz);

    $today_key = $now->format('Y-m-d');
    $month_start_key = $now->modify('first day of this month')->format('Y-m-d');

    $chart_start = $now->modify('-' . ($chart_days - 1) . ' days')->setTime(0, 0, 0);
    $range_start = $now->modify('first day of this month')->setTime(0, 0, 0);
    if ($chart_start < $range_start) {
        $range_start = $chart_start;
    }
    $range_end = $now->setTime(23, 59, 59);

    $labels = [];
    $keys = [];
    for ($i = 0; $i < $chart_days; $i++) {
        $d = $chart_start->modify('+' . $i . ' days');
        $keys[] = $d->format('Y-m-d');
        $labels[] = zerti_format_date($d);
    }

    $totals = array_fill(0, $chart_days, 0.0);
    $key_to_idx = array_flip($keys);

    $today = ['qty' => 0, 'total' => 0.0];
    $period = ['qty' => 0, 'total' => 0.0];

    $order_rows = zerti_wc_home_query_order_line_rows(
        $range_start->getTimestamp(),
        $range_end->getTimestamp()
    );

    foreach ($order_rows as $row) {
        $ymd = zerti_wc_home_gmt_datetime_to_site_ymd($row['order_date_gmt']);
        if ($ymd === '') {
            continue;
        }

        $amount = $row['line_total'] + $row['line_tax'];
        $qty = (int) $row['line_qty'];

        if ($ymd === $today_key) {
            $today['qty'] += $qty;
            $today['total'] += $amount;
        }
        if ($ymd >= $month_start_key) {
            $period['qty'] += $qty;
            $period['total'] += $amount;
        }
        if (isset($key_to_idx[$ymd])) {
            $totals[$key_to_idx[$ymd]] += $amount;
        }
    }

    return [
        'sales' => [
            'today' => $today,
            'period' => $period,
        ],
        'revenue_trend' => [
            'labels' => $labels,
            'totals' => $totals,
        ],
    ];
}

function zerti_wc_home_build_dashboard_stats(int $chart_days = 30): array
{
    $sales_trend = zerti_wc_home_build_sales_revenue_trend($chart_days);

    return [
        'sales' => array_merge(
            $sales_trend['sales'],
            ['processing_orders' => zerti_wc_home_count_shop_orders_by_status('processing')]
        ),
        'inventory' => zerti_wc_home_compute_inventory_stats(),
        'revenue_trend' => $sales_trend['revenue_trend'],
    ];
}

/**
 * @param array{today:array{qty:int,total:float},period:array{qty:int,total:float}} $sales
 * @return array<string, int|string>
 */
function zerti_wc_home_format_sales_stats_for_client(array $sales): array
{
    return [
        'today_total' => function_exists('zerti_format_admin_price_html')
            ? zerti_format_admin_price_html($sales['today']['total'] ?? 0)
            : zerti_wc_price_html_persian($sales['today']['total'] ?? 0),
        'period_qty' => (int) ($sales['period']['qty'] ?? 0),
        'period_total' => function_exists('zerti_format_admin_price_html')
            ? zerti_format_admin_price_html($sales['period']['total'] ?? 0)
            : zerti_wc_price_html_persian($sales['period']['total'] ?? 0),
    ];
}

/**
 * @param array{available_count:int,available_value:float,near_zero_count:int,zero_count:int} $inventory
 * @return array<string, int|string>
 */
function zerti_wc_home_format_inventory_stats_for_client(array $inventory): array
{
    return [
        'available_count' => (int) ($inventory['available_count'] ?? 0),
        'available_value' => function_exists('zerti_format_admin_price_html')
            ? zerti_format_admin_price_html($inventory['available_value'] ?? 0)
            : zerti_wc_price_html_persian($inventory['available_value'] ?? 0),
        'near_zero_count' => (int) ($inventory['near_zero_count'] ?? 0),
        'zero_count' => (int) ($inventory['zero_count'] ?? 0),
    ];
}

/**
 * @return array<string, mixed>|null
 */
function zerti_wc_home_get_cached_dashboard_bundle(bool $refresh = false): ?array
{
    if ($refresh) {
        return null;
    }

    $ttl = (int) apply_filters('zerti_wc_home_stats_cache_ttl', 2 * MINUTE_IN_SECONDS);
    if ($ttl < 1) {
        return null;
    }

    $cached = get_transient(ZERTI_WC_HOME_STATS_CACHE_KEY);
    if (!is_array($cached) || !isset($cached['sales'], $cached['inventory'], $cached['revenue_trend'])) {
        return null;
    }

    return $cached;
}

/**
 * @param array{
 *     sales:array<string, mixed>,
 *     inventory:array{available_count:int,available_value:float,near_zero_count:int,zero_count:int},
 *     revenue_trend:array{labels:list<string>,totals:list<float>}
 * } $bundle
 */
function zerti_wc_home_store_dashboard_bundle(array $bundle): void
{
    $ttl = (int) apply_filters('zerti_wc_home_stats_cache_ttl', 2 * MINUTE_IN_SECONDS);
    if ($ttl > 0) {
        set_transient(ZERTI_WC_HOME_STATS_CACHE_KEY, $bundle, $ttl);
    }
}

/**
 * Build and cache the full dashboard bundle (sales section path).
 *
 * @return array{
 *     sales:array<string, mixed>,
 *     inventory:array{available_count:int,available_value:float,near_zero_count:int,zero_count:int},
 *     revenue_trend:array{labels:list<string>,totals:list<float>}
 * }
 */
function zerti_wc_home_build_and_cache_dashboard_bundle(int $chart_days = 30): array
{
    $cached = zerti_wc_home_get_cached_dashboard_bundle(false);
    if ($cached !== null) {
        return $cached;
    }

    if (!function_exists('wc_get_orders')) {
        return [
            'sales' => [
                'today' => ['qty' => 0, 'total' => 0.0],
                'period' => ['qty' => 0, 'total' => 0.0],
                'processing_orders' => 0,
            ],
            'inventory' => [
                'available_count' => 0,
                'available_value' => 0.0,
                'near_zero_count' => 0,
                'zero_count' => 0,
            ],
            'revenue_trend' => ['labels' => [], 'totals' => []],
        ];
    }

    $sales_trend = zerti_wc_home_build_sales_revenue_trend($chart_days);
    $bundle = [
        'sales' => array_merge(
            $sales_trend['sales'],
            ['processing_orders' => zerti_wc_home_count_shop_orders_by_status('processing')]
        ),
        'inventory' => zerti_wc_home_compute_inventory_stats(),
        'revenue_trend' => $sales_trend['revenue_trend'],
    ];
    zerti_wc_home_store_dashboard_bundle($bundle);

    return $bundle;
}

/**
 * @return array{
 *     sales:array{
 *         today:array{qty:int,total:float},
 *         period:array{qty:int,total:float},
 *         processing_orders:int
 *     },
 *     inventory:array{available_count:int,available_value:float,near_zero_count:int,zero_count:int},
 *     revenue_trend:array{labels:list<string>,totals:list<float>}
 * }
 */
function zerti_wc_home_invalidate_dashboard_stats_cache(): void
{
    delete_transient(ZERTI_WC_HOME_STATS_CACHE_KEY);
    delete_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY);
}

/**
 * Seconds to hold the stats rebuild lock (parallel refresh requests wait instead of rebuilding).
 */
function zerti_wc_home_stats_build_lock_ttl(): int
{
    return max(15, (int) apply_filters('zerti_wc_home_stats_build_lock_ttl', 30));
}

/**
 * Bust cached stats once per refresh wave (skip when another request already started).
 */
function zerti_wc_home_invalidate_dashboard_stats_cache_for_refresh(): void
{
    if (get_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY) !== false) {
        return;
    }

    set_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY, time(), zerti_wc_home_stats_build_lock_ttl());
    delete_transient(ZERTI_WC_HOME_STATS_CACHE_KEY);
}

function zerti_wc_home_release_stats_build_lock(): void
{
    delete_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY);
}

/**
 * Wait for another request to finish building the dashboard bundle.
 *
 * @return array<string, mixed>|null
 */
function zerti_wc_home_wait_for_cached_dashboard_bundle(int $max_seconds = 10): ?array
{
    $deadline = time() + max(1, $max_seconds);

    while (time() < $deadline) {
        $cached = zerti_wc_home_get_cached_dashboard_bundle(false);
        if ($cached !== null) {
            return $cached;
        }

        if (get_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY) === false) {
            return null;
        }

        usleep(100000);
    }

    return zerti_wc_home_get_cached_dashboard_bundle(false);
}

function zerti_wc_home_get_dashboard_bundle(int $chart_days = 30, bool $refresh = false): array
{
    if ($refresh) {
        zerti_wc_home_invalidate_dashboard_stats_cache_for_refresh();
    }

    $cached = zerti_wc_home_get_cached_dashboard_bundle(false);
    if ($cached !== null) {
        zerti_wc_home_release_stats_build_lock();

        return $cached;
    }

    if (get_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY) !== false) {
        $waited = zerti_wc_home_wait_for_cached_dashboard_bundle(10);
        if ($waited !== null) {
            zerti_wc_home_release_stats_build_lock();

            return $waited;
        }
    }

    if (get_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY) === false) {
        set_transient(ZERTI_WC_HOME_STATS_BUILD_LOCK_KEY, time(), zerti_wc_home_stats_build_lock_ttl());
    }

    try {
        return zerti_wc_home_build_and_cache_dashboard_bundle($chart_days);
    } finally {
        zerti_wc_home_release_stats_build_lock();
    }
}

/**
 * Home dashboard stats formatted for live AJAX refresh in the admin UI.
 *
 * @return array{
 *     stats:array<string, int|string>,
 *     revenue_trend:array{labels:list<string>, totals:list<float>}
 * }
 */
function zerti_wc_home_dashboard_stats_client_payload(int $chart_days = 30, bool $refresh = false): array
{
    $bundle = zerti_wc_home_get_dashboard_bundle($chart_days, $refresh);
    $sales = $bundle['sales'];
    $inventory = $bundle['inventory'];

    $stats = array_merge(
        ['processing_orders' => (int) ($sales['processing_orders'] ?? 0)],
        zerti_wc_home_format_sales_stats_for_client($sales),
        zerti_wc_home_format_inventory_stats_for_client($inventory)
    );

    return [
        'stats' => $stats,
        'revenue_trend' => [
            'labels' => $bundle['revenue_trend']['labels'],
            'totals' => array_map('zerti_wc_parse_amount', $bundle['revenue_trend']['totals']),
        ],
    ];
}

/**
 * Partial home dashboard payload for progressive client hydration.
 *
 * @return array{section:string,stats?:array<string,int|string>,revenue_trend?:array{labels:list<string>,totals:list<float>}}
 */
function zerti_wc_home_dashboard_stats_section_payload(string $section, int $chart_days = 30, bool $refresh = false): array
{
    $section = sanitize_key($section);
    $allowed = ['processing', 'inventory', 'sales', 'leaderboards'];
    if (!in_array($section, $allowed, true)) {
        return zerti_wc_home_dashboard_stats_client_payload($chart_days, $refresh);
    }

    if ($section === 'leaderboards') {
        return array_merge(
            ['section' => 'leaderboards'],
            function_exists('zerti_wc_home_leaderboards_client_payload')
                ? zerti_wc_home_leaderboards_client_payload(5, $refresh)
                : ['leaderboards' => []]
        );
    }

    if ($section === 'processing') {
        if ($refresh) {
            zerti_wc_home_invalidate_dashboard_stats_cache_for_refresh();
        }

        $bundle = zerti_wc_home_get_cached_dashboard_bundle(false);
        $count = $bundle
            ? (int) ($bundle['sales']['processing_orders'] ?? 0)
            : zerti_wc_home_count_shop_orders_by_status('processing');

        return [
            'section' => 'processing',
            'stats' => [
                'processing_orders' => $count,
            ],
        ];
    }

    if ($section === 'inventory') {
        $bundle = zerti_wc_home_get_dashboard_bundle($chart_days, $refresh);

        return [
            'section' => 'inventory',
            'stats' => zerti_wc_home_format_inventory_stats_for_client($bundle['inventory']),
        ];
    }

    $bundle = zerti_wc_home_get_dashboard_bundle($chart_days, $refresh);

    return [
        'section' => 'sales',
        'stats' => zerti_wc_home_format_sales_stats_for_client($bundle['sales']),
        'revenue_trend' => [
            'labels' => $bundle['revenue_trend']['labels'],
            'totals' => array_map('zerti_wc_parse_amount', $bundle['revenue_trend']['totals']),
        ],
    ];
}

/**
 * @return array{
 *     today:array{qty:int,total:float},
 *     period:array{qty:int,total:float},
 *     processing_orders:int
 * }
 */
function zerti_wc_home_get_dashboard_sales_stats(): array
{
    return zerti_wc_home_get_dashboard_bundle()['sales'];
}

/**
 * @return array{
 *     available_count:int,
 *     available_value:float,
 *     near_zero_count:int,
 *     zero_count:int
 * }
 */
function zerti_wc_home_get_inventory_stats(): array
{
    return zerti_wc_home_get_dashboard_bundle()['inventory'];
}

/**
 * @return array{labels:list<string>,totals:list<float>}
 */
function zerti_wc_home_get_daily_revenue_last_days(int $days = 30): array
{
    return zerti_wc_home_get_dashboard_bundle($days)['revenue_trend'];
}

/**
 * Bust cached home dashboard stats (e.g. after order status change).
 */
function zerti_wc_home_flush_stats_cache(): void
{
    zerti_wc_home_invalidate_dashboard_stats_cache();
}

add_action('woocommerce_order_status_changed', 'zerti_wc_home_flush_stats_cache', 10, 0);
add_action('woocommerce_new_order', 'zerti_wc_home_flush_stats_cache', 10, 0);
add_action('woocommerce_update_product', 'zerti_wc_home_flush_stats_cache', 10, 0);
add_action('save_post_product', 'zerti_wc_home_flush_stats_cache', 10, 0);
add_action('save_post_product_variation', 'zerti_wc_home_flush_stats_cache', 10, 0);
add_action('woocommerce_product_import_batch_complete', 'zerti_wc_home_flush_stats_cache', 10, 0);
