<?php

namespace SoftminalB2B\Support;

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

use SoftminalB2B\Install;
use SoftminalB2B\Models\Group;

/**
 * The numbers behind the dashboard: B2B sales (aggregated from WooCommerce
 * orders placed by group members), the customers-per-group breakdown, and the
 * "needs attention" queue. Pro-only figures (open quotes, past-due credit) are
 * read straight from the schema tables and simply come back null when there is
 * no such data — so the free plugin never renders an empty Pro widget.
 */
class DashboardStats
{
    private const REVENUE_CACHE_KEY = 'smb2b_dashboard_revenue_v3';
    private const REVENUE_CACHE_TTL = 15 * MINUTE_IN_SECONDS;

    private const SALES_CACHE_PREFIX = 'smb2b_sales_';
    private const SALES_CACHE_TTL    = 5 * MINUTE_IN_SECONDS;

    /** Days in each comparison window. */
    private const WINDOW = 30;

    /** Default span (days) for the range charts when none is given. */
    private const RECENT = 7;

    /** Hard cap (days) on a requested range, so the day loop stays bounded. */
    private const MAX_RANGE = 366;

    /**
     * @return array<string, mixed>
     */
    public static function all(): array
    {
        $memberIds = Group::allMemberIds();

        return [
            'b2b_customers'    => count($memberIds),
            'members_by_group' => self::membersByGroup(),
            'attention'        => self::attention(),
        ];
    }

    /**
     * The revenue hero block. Split out of all() and served on its own endpoint
     * so the (potentially heavy, order-scanning) computation never blocks the
     * dashboard shell — the rest of the page paints first, the hero streams in.
     *
     * @return array<string, mixed>
     */
    public static function revenueStats(): array
    {
        return self::revenue(Group::allMemberIds());
    }

    /**
     * B2B sales over the last 30 days, the change vs the previous 30, and a
     * daily series for the trend chart. Aggregated via WC_Order CRUD
     * (HPOS-safe) and cached, since scanning orders is the costly part.
     *
     * @param array<int, int> $memberIds
     * @return array<string, mixed>
     */
    private static function revenue(array $memberIds): array
    {
        if (empty($memberIds) || !function_exists('wc_get_orders')) {
            return self::emptyRevenue();
        }

        $cached = get_transient(self::REVENUE_CACHE_KEY);
        if (is_array($cached)) {
            return $cached;
        }

        $now   = time();
        $since = $now - (2 * self::WINDOW) * DAY_IN_SECONDS;

        $orders = wc_get_orders([
            'type'         => 'shop_order',
            'status'       => ['wc-completed', 'wc-processing'],
            'customer'     => array_map('strval', $memberIds),
            'date_created' => '>=' . $since,
            'limit'        => -1,
            'return'       => 'objects',
        ]);

        // Seed one bucket per day in the current window so a day with no orders
        // still plots as zero rather than leaving a gap in the trend line.
        $daily = [];
        for ($i = self::WINDOW - 1; $i >= 0; $i--) {
            $daily[wp_date('Y-m-d', $now - $i * DAY_IN_SECONDS)] = 0.0;
        }

        $currentStart = wp_date('Y-m-d', $now - (self::WINDOW - 1) * DAY_IN_SECONDS);
        $priorStart   = wp_date('Y-m-d', $now - (2 * self::WINDOW - 1) * DAY_IN_SECONDS);

        $currentTotal = 0.0;
        $currentCount = 0;
        $priorTotal   = 0.0;
        $priorCount   = 0;

        foreach ($orders as $order) {
            $created = $order->get_date_created();
            if (!$created) {
                continue;
            }

            $day   = $created->date('Y-m-d');
            $total = (float) $order->get_total();

            if ($day >= $currentStart) {
                $currentTotal += $total;
                $currentCount++;
                if (isset($daily[$day])) {
                    $daily[$day] += $total;
                }
            } elseif ($day >= $priorStart) {
                $priorTotal += $total;
                $priorCount++;
            }
        }

        $aov      = $currentCount > 0 ? $currentTotal / $currentCount : 0.0;
        $priorAov = $priorCount > 0 ? $priorTotal / $priorCount : 0.0;

        $trend = [];
        foreach ($daily as $day => $value) {
            $trend[] = [
                'label' => wp_date('M j', strtotime($day . ' 12:00:00')),
                'value' => round($value, 2),
            ];
        }

        $revenue = [
            'total'            => round($currentTotal, 2),
            'formatted'        => self::money($currentTotal),
            'orders'           => $currentCount,
            'orders_change_pct' => $priorCount > 0 ? (int) round((($currentCount - $priorCount) / $priorCount) * 100) : null,
            'aov'              => round($aov, 2),
            'aov_formatted'    => self::money($aov),
            'aov_change_pct'   => $priorAov > 0 ? (int) round((($aov - $priorAov) / $priorAov) * 100) : null,
            'change_pct'       => $priorTotal > 0 ? (int) round((($currentTotal - $priorTotal) / $priorTotal) * 100) : null,
            'currency_symbol'  => self::currencySymbol(),
            'trend'            => $trend,
            'has_orders'       => $currentCount > 0,
        ];

        set_transient(self::REVENUE_CACHE_KEY, $revenue, self::REVENUE_CACHE_TTL);

        return $revenue;
    }

    /**
     * Daily B2B sales bars and top-spending B2B customers for an arbitrary date
     * range — powers the two dashboard charts, each with its own range picker.
     * Not cached: the range is user-driven, so it changes on every request.
     *
     * @return array{currency_symbol: string, bars: array<int, array<string, mixed>>, top_buyers: array<int, array<string, mixed>>, has_data: bool}
     */
    public static function salesRange(string $start, string $end): array
    {
        $symbol = self::currencySymbol();

        $startTs = strtotime($start . ' 00:00:00');
        $endTs   = strtotime($end . ' 23:59:59');

        // Fall back to the last 7 days on a missing or inverted range, and cap
        // the span so a pathological range can't scan an unbounded set of days.
        if ($startTs === false || $endTs === false || $endTs < $startTs) {
            $endTs   = time();
            $startTs = $endTs - (self::RECENT - 1) * DAY_IN_SECONDS;
        }
        if (($endTs - $startTs) > self::MAX_RANGE * DAY_IN_SECONDS) {
            $startTs = $endTs - self::MAX_RANGE * DAY_IN_SECONDS;
        }

        $memberIds = Group::allMemberIds();

        if (empty($memberIds) || !function_exists('wc_get_orders')) {
            return ['currency_symbol' => $symbol, 'bars' => [], 'top_buyers' => [], 'has_data' => false];
        }

        // Cache the whole result per (day-range × member set). The two dashboard
        // charts request the same default range on load, and users re-pick ranges
        // repeatedly — without this, each call re-scans and re-hydrates every B2B
        // order object in the window. Keyed on the member set so it self-busts
        // when membership changes; a short TTL keeps new orders fresh enough.
        sort($memberIds);
        $cacheKey = self::SALES_CACHE_PREFIX . md5(
            wp_date('Y-m-d', $startTs) . '|' . wp_date('Y-m-d', $endTs) . '|' . implode(',', $memberIds)
        );
        $cached = get_transient($cacheKey);
        if (is_array($cached)) {
            return $cached;
        }

        $orders = wc_get_orders([
            'type'         => 'shop_order',
            'status'       => ['wc-completed', 'wc-processing'],
            'customer'     => array_map('strval', $memberIds),
            'date_created' => $startTs . '...' . $endTs,
            'limit'        => -1,
            'return'       => 'objects',
        ]);

        // One bucket per day across the range so gaps plot as zero. A short
        // range is labelled by weekday; a longer one by date, to stay legible.
        $startDay = wp_date('Y-m-d', $startTs);
        $endDay   = wp_date('Y-m-d', $endTs);
        $span     = (int) floor(($endTs - $startTs) / DAY_IN_SECONDS) + 1;
        $labelFmt = $span <= 8 ? 'D' : 'M j';

        $daily = [];
        for ($ts = $startTs; wp_date('Y-m-d', $ts) <= $endDay; $ts += DAY_IN_SECONDS) {
            $daily[wp_date('Y-m-d', $ts)] = ['amount' => 0.0, 'orders' => 0, 'buyers' => []];
        }

        $buyerSpend = [];

        foreach ($orders as $order) {
            $created = $order->get_date_created();
            if (!$created) {
                continue;
            }

            $day = $created->date('Y-m-d');
            if (!isset($daily[$day])) {
                continue;
            }

            $total      = (float) $order->get_total();
            $customerId = (int) $order->get_customer_id();

            $daily[$day]['amount'] += $total;
            $daily[$day]['orders']++;
            if ($customerId > 0) {
                $daily[$day]['buyers'][$customerId] = true;
                $buyerSpend[$customerId] = ($buyerSpend[$customerId] ?? 0.0) + $total;
            }
        }

        $bars = [];
        foreach ($daily as $day => $bucket) {
            $bars[] = [
                'label'  => wp_date($labelFmt, strtotime($day . ' 12:00:00')),
                'date'   => wp_date('M j', strtotime($day . ' 12:00:00')),
                'amount' => round($bucket['amount'], 2),
                'orders' => $bucket['orders'],
                'buyers' => count($bucket['buyers']),
            ];
        }

        $result = [
            'currency_symbol' => $symbol,
            'bars'            => $bars,
            'top_buyers'      => self::topBuyers($buyerSpend),
            'has_data'        => !empty($buyerSpend),
            'range_start'     => $startDay,
            'range_end'       => $endDay,
        ];

        set_transient($cacheKey, $result, self::SALES_CACHE_TTL);

        return $result;
    }

    /**
     * Top B2B customers by spend for a range, names resolved in one query.
     *
     * @param array<int, float> $buyerSpend customer id => amount
     * @return array<int, array<string, mixed>>
     */
    private static function topBuyers(array $buyerSpend): array
    {
        if (empty($buyerSpend)) {
            return [];
        }

        arsort($buyerSpend);
        $top   = array_slice($buyerSpend, 0, 5, true);
        $users = Group::usersByIds(array_keys($top));

        $rows = [];
        foreach ($top as $id => $amount) {
            $user = $users[$id] ?? null;
            $rows[] = [
                'id'        => $id,
                'name'      => $user ? $user->display_name : sprintf(
                    /* translators: %d: customer id */
                    __('Customer #%d', 'softminal-b2b-wholesale-for-woocommerce'),
                    $id
                ),
                'total'     => round($amount, 2),
                'formatted' => self::money($amount),
            ];
        }

        return $rows;
    }

    /**
     * @return array<string, mixed>
     */
    private static function emptyRevenue(): array
    {
        return [
            'total'            => 0.0,
            'formatted'        => self::money(0),
            'orders'           => 0,
            'orders_change_pct' => null,
            'aov'              => 0.0,
            'aov_formatted'    => self::money(0),
            'aov_change_pct'   => null,
            'change_pct'       => null,
            'currency_symbol'  => self::currencySymbol(),
            'trend'            => [],
            'has_orders'       => false,
        ];
    }

    /**
     * Customers per group, largest first, for the breakdown bars. Uses the same
     * one-query summary the groups list relies on.
     *
     * @return array<int, array<string, mixed>>
     */
    private static function membersByGroup(): array
    {
        $summary = Group::memberSummary();

        $rows = [];
        foreach (Group::all() as $group) {
            $id = (int) $group->id;
            $rows[] = [
                'id'    => $id,
                'name'  => $group->name,
                'count' => (int) ($summary[$id]['total'] ?? 0),
            ];
        }

        usort($rows, static fn ($a, $b) => $b['count'] <=> $a['count']);

        return array_slice($rows, 0, 6);
    }

    /**
     * The "needs attention" queue. Applications are always present; open quotes
     * and past-due credit are Pro concerns that stay null until there is data.
     *
     * @return array<string, mixed>
     */
    private static function attention(): array
    {
        return array_merge(
            ['applications' => \SoftminalB2B\Registration\Applicant::countPending()],
            self::quoteAttention(),
            self::creditAttention()
        );
    }

    /**
     * Quotes waiting on the merchant (a submitted request with no offer yet).
     * Null when the store has no quotes at all.
     *
     * @return array<string, int|null>
     */
    private static function quoteAttention(): array
    {
        global $wpdb;

        $table = Install::tableName('quotes');
        if (!self::tableExists($table)) {
            return ['quotes' => null];
        }

        // $table is a plugin-owned identifier from Install::tableName(), not user input.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
        if ($total === 0) {
            return ['quotes' => null];
        }

        // Waiting on the merchant = submitted or in review. (This once counted
        // a 'pending' status no quote ever has, so it was always 0.)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $pending = (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}` WHERE status IN ('submitted', 'review')");

        return array_merge(['quotes' => $pending], self::quoteMessageAttention($table));
    }

    /**
     * Open quotes whose latest thread message is the buyer's — a negotiation
     * waiting on a reply. Reads the UNIFIED message store: the quote's linked
     * conversation, whose denormalised last_author_type says who spoke last (the
     * quote_messages dual-write was retired in the messaging unification). Null
     * when there is no conversation table or no threads, so free stores never
     * see an empty Pro widget. The widget key stays 'quote_messages' so the
     * dashboard renderer is unchanged.
     *
     * @return array<string, int|null>
     */
    private static function quoteMessageAttention(string $quotesTable): array
    {
        global $wpdb;

        $conversations = Install::tableName('conversations');
        if (!self::tableExists($conversations)) {
            return ['quote_messages' => null];
        }

        // Table names are plugin-owned identifiers from Install::tableName(), not user input.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $unanswered = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM `{$quotesTable}` q
             JOIN `{$conversations}` c ON c.id = q.conversation_id
             WHERE q.status IN ('submitted', 'review', 'offered')
               AND c.last_author_type = 'buyer'"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return ['quote_messages' => $unanswered > 0 ? $unanswered : null];
    }

    /**
     * Past-due credit: the balance owed on accounts that carry at least one
     * overdue charge. Charges are positive and payments negative, so an
     * account's balance is simply SUM(amount). Null when there is no ledger.
     *
     * @return array<string, mixed>
     */
    private static function creditAttention(): array
    {
        global $wpdb;

        $table = Install::tableName('credit_transactions');
        if (!self::tableExists($table)) {
            return ['overdue_amount' => null, 'overdue_formatted' => null, 'overdue_count' => null];
        }

        $today = wp_date('Y-m-d');

        // Per account: running balance, and whether any charge is past its due date.
        // The table name is passed as a %i identifier placeholder (WP 6.2+), so
        // the whole statement is prepared — no interpolation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT account_id,
                        SUM(amount) AS balance,
                        MAX(CASE WHEN type = 'debit' AND due_date IS NOT NULL AND due_date < %s THEN 1 ELSE 0 END) AS past_due
                 FROM %i
                 GROUP BY account_id",
                $today,
                $table
            ),
            ARRAY_A
        );

        if (empty($rows)) {
            return ['overdue_amount' => null, 'overdue_formatted' => null, 'overdue_count' => null];
        }

        $overdue = 0.0;
        $count   = 0;

        foreach ($rows as $row) {
            $balance = (float) $row['balance'];
            if ((int) $row['past_due'] === 1 && $balance > 0) {
                $overdue += $balance;
                $count++;
            }
        }

        return [
            'overdue_amount'    => round($overdue, 2),
            'overdue_formatted' => self::money($overdue),
            'overdue_count'     => $count,
        ];
    }

    /**
     * Cached SHOW TABLES check so a missing table (e.g. a broken activation)
     * degrades to "no data" instead of a SQL error.
     */
    private static function tableExists(string $table): bool
    {
        static $cache = [];

        if (isset($cache[$table])) {
            return $cache[$table];
        }

        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

        return $cache[$table] = ($found === $table);
    }

    private static function money($amount): string
    {
        if (function_exists('wc_price')) {
            return html_entity_decode(wp_strip_all_tags(wc_price((float) $amount)));
        }

        return number_format((float) $amount, 2);
    }

    private static function currencySymbol(): string
    {
        if (function_exists('get_woocommerce_currency_symbol')) {
            return html_entity_decode(get_woocommerce_currency_symbol());
        }

        return '';
    }
}
