<?php
/**
 * Shop public API.
 *
 * Other plugins call these static methods instead of touching shop tables
 * directly. Keep these signatures stable across minor versions.
 *
 * Usage:
 *   if (PluginLoader::isActive('shop') && class_exists('ShopAPI')) {
 *       $orders = ShopAPI::recentOrders(10);
 *   }
 */

class ShopAPI {

    // ══════════════════════════════════════════════════════
    // PRODUCTS
    // ══════════════════════════════════════════════════════

    /** List active products, optionally filtered by category. */
    public static function products(string $category = '', int $limit = 50): array {
        $tid  = current_tenant_id();
        $args = [$tid];
        $where = "tenant_id = ? AND status = 'active'";
        if ($category !== '') {
            $where .= " AND category = ?";
            $args[] = $category;
        }
        return Database::rows(
            "SELECT id, sku, name, slug, price, sale_price, stock_qty,
                    category, image_url, short_desc
               FROM shop_products
              WHERE {$where}
              ORDER BY name ASC
              LIMIT " . max(1, min(500, $limit)),
            $args
        );
    }

    /** Get a single product by id. */
    public static function product(int $id): ?array {
        $row = Database::row(
            "SELECT * FROM shop_products WHERE id = ? AND tenant_id = ?",
            [$id, current_tenant_id()]
        );
        return $row ?: null;
    }

    /** Get effective price (sale_price if set, otherwise price). */
    public static function effectivePrice(array $product): float {
        if ($product['sale_price'] !== null && (float)$product['sale_price'] > 0) {
            return (float)$product['sale_price'];
        }
        return (float)$product['price'];
    }

    /** Decrement stock. Returns false if insufficient stock. */
    public static function decrementStock(int $productId, int $qty = 1): bool {
        $tid = current_tenant_id();
        $p   = Database::row(
            "SELECT manage_stock, stock_qty FROM shop_products WHERE id = ? AND tenant_id = ?",
            [$productId, $tid]
        );
        if (!$p) return false;
        if (!$p['manage_stock']) return true;  // unmanaged stock always ok
        if ((int)$p['stock_qty'] < $qty) return false;
        Database::update(
            'shop_products',
            ['stock_qty' => (int)$p['stock_qty'] - $qty],
            'id = ? AND tenant_id = ?',
            [$productId, $tid]
        );
        return true;
    }

    /**
     * Decrement variant stock specifically. Variants always have stock
     * tracking (the column is non-null), so there's no manage_stock
     * flag to check.
     */
    public static function decrementVariantStock(int $variantId, int $qty = 1): bool {
        $v = Database::row("SELECT stock_qty FROM shop_product_variants WHERE id = ?", [$variantId]);
        if (!$v) return false;
        $new = max(0, (int)$v['stock_qty'] - $qty);
        Database::update(
            'shop_product_variants',
            ['stock_qty' => $new],
            'id = ?',
            [$variantId]
        );
        return (int)$v['stock_qty'] >= $qty;
    }

    // ══════════════════════════════════════════════════════
    // ORDERS
    // ══════════════════════════════════════════════════════

    /**
     * Create a new order with line items.
     *
     * $items  = [['product_id'=>1,'qty'=>2], ...]
     * Returns the new order id, or throws \RuntimeException on failure.
     */
    public static function createOrder(
        string $customerEmail,
        string $customerName,
        array  $items,
        array  $opts = []
    ): int {
        $tid      = current_tenant_id();
        $currency = Database::setting('shop.currency') ?: 'USD';
        $taxRate  = (float)(Database::setting('shop.tax_rate') ?: 0);

        // Validate and resolve items. Items may include an optional
        // variant_id; when present we use the variant's price + sku and
        // decrement variant stock instead of product stock at the end.
        $lineItems   = [];
        $subtotal    = 0.0;
        foreach ($items as $item) {
            $pid       = (int)($item['product_id'] ?? 0);
            $qty       = max(1, (int)($item['qty'] ?? 1));
            $variantId = !empty($item['variant_id']) ? (int)$item['variant_id'] : null;

            $p = self::product($pid);
            if (!$p) throw new \RuntimeException("Product #$pid not found.");

            $variant = null;
            if ($variantId !== null) {
                $variant = self::variant($variantId);
                if (!$variant || (int)$variant['product_id'] !== $pid) {
                    throw new \RuntimeException("Variant #$variantId is not valid for product #$pid.");
                }
            }

            // Use explicit unit_price if caller provided one (so cart's
            // already-priced lines survive); otherwise compute it.
            $price = isset($item['unit_price'])
                   ? (float)$item['unit_price']
                   : self::variantPrice($p, $variant);
            $sku   = $variant && !empty($variant['sku']) ? $variant['sku'] : $p['sku'];
            $name  = $variant
                   ? $p['name'] . ' — ' . $variant['attribute'] . ': ' . $variant['value']
                   : $p['name'];

            $lineTotal   = $price * $qty;
            $subtotal   += $lineTotal;
            $lineItems[] = [
                'product_id'   => $pid,
                'product_sku'  => $sku,
                'product_name' => mb_substr($name, 0, 255),
                'quantity'     => $qty,
                'unit_price'   => $price,
                'line_total'   => $lineTotal,
                // not persisted but used for stock decrement below
                '_variant_id'  => $variantId,
            ];
        }

        // Coupon
        $discountTotal = 0.0;
        $couponCode    = $opts['coupon_code'] ?? '';
        if ($couponCode !== '') {
            $coupon = self::applyCoupon($couponCode, $subtotal);
            if ($coupon !== null) {
                $discountTotal = $coupon['discount'];
            }
        }

        // Shipping. Build a minimal "billing" context for shipping plugins
        // from the address options on this order — USPS-like plugins use
        // the destination ZIP/country to compute live rates.
        $shippingContext = [
            'address_1' => $opts['ship_address_1'] ?? '',
            'address_2' => $opts['ship_address_2'] ?? '',
            'city'      => $opts['ship_city']      ?? '',
            'state'     => $opts['ship_state']     ?? '',
            'postcode'  => $opts['ship_postcode']  ?? '',
            'country'   => $opts['ship_country']   ?? '',
        ];
        $shippingTotal = (float)($opts['shipping_total']
            ?? self::calculateShipping($subtotal - $discountTotal, $lineItems, $shippingContext));

        // Tax
        $taxableAmount = ($subtotal - $discountTotal + $shippingTotal);
        $taxTotal      = round($taxableAmount * ($taxRate / 100), 2);
        $total         = round($subtotal - $discountTotal + $shippingTotal + $taxTotal, 2);

        // Order number is derived from the row's own auto-increment id
        // (assigned below), not COUNT(*)+1 — that collided under concurrency
        // and reused a number after a delete. We insert a unique temporary
        // value first, then stamp the final PREFIX-<id> inside the same
        // transaction, so no external reader ever sees the placeholder.
        $orderPrefix  = Database::setting('shop.order_prefix') ?: 'ORD';
        $tempOrderNum = 'TMP-' . bin2hex(random_bytes(12));

        // Resolve or create customer
        $customerId = $opts['customer_id'] ?? self::findOrCreateCustomerId($customerEmail, $customerName);

        // Wrap order create + line items + stock decrement + coupon increment
        // in a single transaction. Without this, a mid-write failure leaves
        // partial data (orphaned order row, items but no stock decrement, etc).
        // The Hook::doAction call fires AFTER commit so subscribers always see
        // a fully-persisted order, never one that's about to roll back.
        $pdo = Database::get();
        $startedTx = false;
        try {
            // Defensive: don't try to start a nested transaction if a caller
            // upstream is already inside one. PDO would throw.
            if (!$pdo->inTransaction()) {
                $pdo->beginTransaction();
                $startedTx = true;
            }

            $orderId = Database::insert('shop_orders', [
                'tenant_id'       => $tid,
                'order_number'    => $tempOrderNum,
                'view_token'      => bin2hex(random_bytes(16)),
                'customer_id'     => $customerId,
                'customer_email'  => mb_substr($customerEmail, 0, 190),
                'customer_name'   => mb_substr($customerName, 0, 255),
                'status'          => $opts['status'] ?? 'pending',
                'subtotal'        => $subtotal,
                'discount_total'  => $discountTotal,
                'shipping_total'  => $shippingTotal,
                'tax_total'       => $taxTotal,
                'total'           => $total,
                'currency'        => $currency,
                'payment_method'  => $opts['payment_method'] ?? null,
                'coupon_code'     => $couponCode !== '' ? $couponCode : null,
                'notes'           => $opts['notes'] ?? null,
                'ship_address_1'  => $opts['ship_address_1'] ?? null,
                'ship_address_2'  => $opts['ship_address_2'] ?? null,
                'ship_city'       => $opts['ship_city'] ?? null,
                'ship_state'      => $opts['ship_state'] ?? null,
                'ship_postcode'   => $opts['ship_postcode'] ?? null,
                'ship_country'    => $opts['ship_country'] ?? null,
            ]);

            // Stamp the final, collision-free order number from the id.
            $orderNumber = $orderPrefix . '-' . str_pad((string)$orderId, 5, '0', STR_PAD_LEFT);
            Database::update('shop_orders', ['order_number' => $orderNumber], 'id = ?', [$orderId]);

            // Insert line items (strip internal _variant_id used only for stock routing)
            foreach ($lineItems as $li) {
                unset($li['_variant_id']);
                $li['order_id'] = $orderId;
                Database::insert('shop_order_items', $li);
            }

            // Decrement stock — variant stock when a variant was selected,
            // otherwise the product's own stock.
            foreach ($items as $item) {
                $vid = !empty($item['variant_id']) ? (int)$item['variant_id'] : null;
                $qty = (int)($item['qty'] ?? 1);
                if ($vid !== null) {
                    self::decrementVariantStock($vid, $qty);
                } else {
                    self::decrementStock((int)$item['product_id'], $qty);
                }
            }

            // Increment coupon usage
            if ($couponCode !== '') {
                Database::query(
                    "UPDATE shop_coupons SET uses = uses + 1
                      WHERE tenant_id = ? AND code = ?",
                    [$tid, $couponCode]
                );
            }

            if ($startedTx) $pdo->commit();
        } catch (\Throwable $e) {
            if ($startedTx && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }

        Hook::doAction('shop_order_created', $orderId, $customerEmail);

        return $orderId;
    }

    /** Get a single order with its line items. */
    public static function order(int $id): ?array {
        $tid   = current_tenant_id();
        $order = Database::row(
            "SELECT * FROM shop_orders WHERE id = ? AND tenant_id = ?",
            [$id, $tid]
        );
        if (!$order) return null;
        $order['items'] = Database::rows(
            "SELECT * FROM shop_order_items WHERE order_id = ?",
            [$id]
        );
        return $order;
    }

    /** Recent orders. */
    public static function recentOrders(int $limit = 20, string $status = ''): array {
        $tid  = current_tenant_id();
        $args = [$tid];
        $where = 'tenant_id = ?';
        if ($status !== '') {
            $where .= ' AND status = ?';
            $args[] = $status;
        }
        return Database::rows(
            "SELECT id, order_number, customer_email, customer_name,
                    status, total, currency, created_at
               FROM shop_orders
              WHERE {$where}
              ORDER BY created_at DESC
              LIMIT " . max(1, min(200, $limit)),
            $args
        );
    }

    /** Update order status. */
    public static function updateStatus(int $orderId, string $status): bool {
        $allowed = ['pending','processing','on-hold','completed','cancelled','refunded'];
        if (!in_array($status, $allowed, true)) return false;
        $rows = Database::update(
            'shop_orders',
            ['status' => $status],
            'id = ? AND tenant_id = ?',
            [$orderId, current_tenant_id()]
        );
        if ($rows > 0) {
            Hook::doAction('shop_order_status_changed', $orderId, $status);
        }
        return $rows > 0;
    }

    /**
     * Reconcile an order's recorded total against the amount the payment
     * gateway actually captured. The Stripe completion paths rebuild the
     * order from the live cart, which can drift from what the customer was
     * charged if the cart changed between intent creation and payment. We
     * never reject a paid order — the money is already taken — but a mismatch
     * is parked on 'on-hold' for manual review instead of auto-advancing to
     * 'processing', and the discrepancy is logged + audited.
     *
     * @param int    $paidCents     Amount captured by the gateway, in cents.
     * @param string $paidCurrency  Gateway currency (ISO, e.g. 'USD'); '' skips the currency check.
     * @return array{ok:bool,order_cents:int,paid_cents:int,reason:?string}
     */
    public static function reconcilePaidAmount(int $orderId, int $paidCents, string $paidCurrency = ''): array {
        $order = self::order($orderId);
        if (!$order) {
            return ['ok' => false, 'order_cents' => 0, 'paid_cents' => $paidCents, 'reason' => 'order_missing'];
        }

        $orderCents = (int) round((float)$order['total'] * 100);
        $reason = null;
        if ($paidCurrency !== '' && strtoupper($paidCurrency) !== strtoupper((string)$order['currency'])) {
            $reason = 'currency_mismatch';
        } elseif ($paidCents !== $orderCents) {
            $reason = 'amount_mismatch';
        }

        if ($reason === null) {
            return ['ok' => true, 'order_cents' => $orderCents, 'paid_cents' => $paidCents, 'reason' => null];
        }

        $note = sprintf(
            '[%s] Payment reconciliation %s: gateway charged %d, order total %d (%s). Held for review.',
            date('Y-m-d H:i'), $reason, $paidCents, $orderCents,
            strtoupper($paidCurrency !== '' ? $paidCurrency : (string)$order['currency'])
        );
        $existing = trim((string)($order['notes'] ?? ''));
        Database::update('shop_orders',
            ['notes' => trim($existing . "\n" . $note), 'status' => 'on-hold'],
            'id = ? AND tenant_id = ?', [$orderId, current_tenant_id()]);

        slate_log("Shop order #$orderId payment $reason: charged $paidCents vs order $orderCents", 'error');
        if (class_exists('AuditLog')) {
            AuditLog::record('shop.payment_mismatch', (string)$orderId,
                ['reason' => $reason, 'paid_cents' => $paidCents, 'order_cents' => $orderCents]);
        }
        Hook::doAction('shop_order_status_changed', $orderId, 'on-hold');

        return ['ok' => false, 'order_cents' => $orderCents, 'paid_cents' => $paidCents, 'reason' => $reason];
    }

    // ══════════════════════════════════════════════════════
    // COUPONS
    // ══════════════════════════════════════════════════════

    /**
     * Validate and apply a coupon to a subtotal.
     * Returns ['discount' => float, 'coupon' => array] or null if invalid.
     */
    public static function applyCoupon(string $code, float $subtotal): ?array {
        $tid    = current_tenant_id();
        $coupon = Database::row(
            "SELECT * FROM shop_coupons
              WHERE tenant_id = ? AND code = ? AND active = 1
                AND (expires_at IS NULL OR expires_at > NOW())
                AND (max_uses IS NULL OR uses < max_uses)",
            [$tid, strtoupper(trim($code))]
        );
        if (!$coupon) return null;
        if ($coupon['min_order'] !== null && $subtotal < (float)$coupon['min_order']) {
            return null;
        }
        $discount = $coupon['type'] === 'percent'
            ? round($subtotal * ((float)$coupon['value'] / 100), 2)
            : min((float)$coupon['value'], $subtotal);
        return ['discount' => $discount, 'coupon' => $coupon];
    }

    // ══════════════════════════════════════════════════════
    // CUSTOMERS
    // ══════════════════════════════════════════════════════

    /** Find a customer by email, or null. */
    public static function findCustomer(string $email): ?array {
        $row = Database::row(
            "SELECT * FROM shop_customers WHERE tenant_id = ? AND email = ?",
            [current_tenant_id(), strtolower(trim($email))]
        );
        return $row ?: null;
    }

    // ══════════════════════════════════════════════════════
    // REPORTING
    // ══════════════════════════════════════════════════════

    /** Revenue summary for a date range. */
    public static function revenueSummary(string $from, string $to): array {
        $tid = current_tenant_id();
        return Database::row(
            "SELECT
                COUNT(*) AS order_count,
                COALESCE(SUM(total), 0) AS revenue,
                COALESCE(SUM(discount_total), 0) AS discounts,
                COALESCE(SUM(tax_total), 0) AS taxes,
                COALESCE(SUM(shipping_total), 0) AS shipping,
                COALESCE(AVG(total), 0) AS avg_order
               FROM shop_orders
              WHERE tenant_id = ?
                AND status IN ('processing','completed')
                AND DATE(created_at) BETWEEN ? AND ?",
            [$tid, $from, $to]
        ) ?: [];
    }

    /** Top-selling products by quantity. */
    public static function topProducts(int $limit = 10, string $from = '', string $to = ''): array {
        $tid  = current_tenant_id();
        $args = [$tid];
        $dateWhere = '';
        if ($from && $to) {
            $dateWhere = "AND DATE(o.created_at) BETWEEN ? AND ?";
            $args[] = $from;
            $args[] = $to;
        }
        return Database::rows(
            "SELECT oi.product_id, oi.product_name,
                    SUM(oi.quantity) AS qty_sold,
                    SUM(oi.line_total) AS revenue
               FROM shop_order_items oi
               JOIN shop_orders o ON o.id = oi.order_id
              WHERE o.tenant_id = ? AND o.status IN ('processing','completed')
                    {$dateWhere}
              GROUP BY oi.product_id, oi.product_name
              ORDER BY qty_sold DESC
              LIMIT " . max(1, min(100, $limit)),
            $args
        );
    }

    // ══════════════════════════════════════════════════════
    // HELPERS
    // ══════════════════════════════════════════════════════

    public static function formatMoney(float $amount, string $currency = ''): string {
        if ($currency === '') {
            $currency = Database::setting('shop.currency') ?: 'USD';
        }
        $symbols = [
            'USD' => '$', 'EUR' => '€', 'GBP' => '£',
            'CAD' => 'CA$', 'AUD' => 'A$', 'JPY' => '¥',
            'BDT' => '৳',
        ];
        $symbol = $symbols[$currency] ?? $currency . ' ';
        return $symbol . number_format($amount, 2);
    }

    public static function statusBadgeClass(string $status): string {
        return match($status) {
            'completed'  => 'badge-active',
            'processing' => 'badge-installed',
            'pending'    => 'badge-warning',
            'on-hold'    => 'badge-inactive',
            'cancelled'  => 'badge-danger',
            'refunded'   => 'badge-muted',
            default      => 'badge-inactive',
        };
    }

    // ──────────────────────────────────────────────────────
    // Private helpers
    // ──────────────────────────────────────────────────────

    private static function findOrCreateCustomerId(string $email, string $name): int {
        $tid      = current_tenant_id();
        $email    = strtolower(trim($email));
        $existing = Database::value(
            "SELECT id FROM shop_customers WHERE tenant_id = ? AND email = ?",
            [$tid, $email]
        );
        if ($existing) return (int)$existing;

        $parts = explode(' ', trim($name), 2);
        return Database::insert('shop_customers', [
            'tenant_id'  => $tid,
            'email'      => $email,
            'first_name' => $parts[0] ?? '',
            'last_name'  => $parts[1] ?? '',
        ]);
    }

    /**
     * Calculate the shipping charge for a cart.
     *
     * Hook point: plugins can register shipping methods via the
     * `shop_shipping_rate` filter, which receives:
     *   - $rate    float|null  Current rate (null = "no plugin has decided yet")
     *   - $context array       ['subtotal' => float, 'items' => array, 'billing' => array]
     * and returns the new rate (float) or null to fall through.
     *
     * Resolution order:
     *   1. Free-shipping threshold (if subtotal >= threshold → free, regardless of plugins)
     *   2. Plugin filter (first plugin to return non-null wins)
     *   3. Legacy hardcoded `shop.flat_shipping_rate` setting
     *
     * The free-shipping threshold runs FIRST so admins always have a
     * reliable "give the customer free shipping over $X" lever that
     * works no matter which shipping plugin is active.
     */
    public static function calculateShipping(float $subtotal, array $items = [], array $billing = []): float {
        // Step 1: free-shipping threshold
        $freeThreshold = (float)(Database::setting('shop.free_shipping_threshold') ?: 0);
        if ($freeThreshold > 0 && $subtotal >= $freeThreshold) return 0.0;

        // Step 2: ask plugins
        $context = ['subtotal' => $subtotal, 'items' => $items, 'billing' => $billing];
        $rate    = null;
        if (class_exists('Hook')) {
            $rate = Hook::applyFilters('shop_shipping_rate', $rate, $context);
        }
        if (is_numeric($rate)) {
            return max(0.0, (float)$rate);
        }

        // Step 3: legacy hardcoded fallback
        return (float)(Database::setting('shop.flat_shipping_rate') ?: 0);
    }

    // ══════════════════════════════════════════════════════
    // CATEGORIES
    // ══════════════════════════════════════════════════════

    public static function categories(bool $withCounts = false): array {
        $tid = current_tenant_id();
        if (!$withCounts) {
            return Database::rows(
                "SELECT * FROM shop_categories WHERE tenant_id = ?
                   ORDER BY display_order ASC, name ASC",
                [$tid]
            );
        }

        // Check whether shop_products has the category_id column before
        // attempting the correlated subquery. On hosts where the migration's
        // ALTER TABLE was silently skipped (missing ALTER privilege on shared
        // MySQL, for example), we still return categories — just with a
        // product_count of 0 — instead of fataling.
        $hasCategoryCol = false;
        try {
            $hasCategoryCol = (int)Database::value(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'shop_products'
                    AND COLUMN_NAME = 'category_id'"
            ) > 0;
        } catch (\Throwable $e) { /* leave false */ }

        if ($hasCategoryCol) {
            return Database::rows(
                "SELECT c.*, (
                     SELECT COUNT(*) FROM shop_products p
                      WHERE p.tenant_id = c.tenant_id
                        AND p.category_id = c.id
                        AND p.status = 'active'
                 ) AS product_count
                   FROM shop_categories c
                  WHERE c.tenant_id = ?
                  ORDER BY c.display_order ASC, c.name ASC",
                [$tid]
            );
        }
        return Database::rows(
            "SELECT c.*, 0 AS product_count FROM shop_categories c
              WHERE c.tenant_id = ?
              ORDER BY c.display_order ASC, c.name ASC",
            [$tid]
        );
    }

    public static function category(int $id): ?array {
        $row = Database::row("SELECT * FROM shop_categories WHERE id = ? AND tenant_id = ?",
            [$id, current_tenant_id()]);
        return $row ?: null;
    }

    public static function categoryBySlug(string $slug): ?array {
        $row = Database::row("SELECT * FROM shop_categories WHERE slug = ? AND tenant_id = ?",
            [$slug, current_tenant_id()]);
        return $row ?: null;
    }

    // ══════════════════════════════════════════════════════
    // PUBLIC-FACING PRODUCTS
    // ══════════════════════════════════════════════════════

    /** Lookup by slug. Used by storefront product pages. */
    public static function productBySlug(string $slug): ?array {
        $row = Database::row(
            "SELECT * FROM shop_products
              WHERE slug = ? AND tenant_id = ? AND status = 'active'",
            [$slug, current_tenant_id()]
        );
        return $row ?: null;
    }

    /** Public-safe listing for the storefront. */
    public static function publishedProducts(array $filters = [], int $limit = 24, int $offset = 0): array {
        $tid = current_tenant_id();
        $where = "tenant_id = ? AND status = 'active'";
        $args  = [$tid];

        if (!empty($filters['category_id'])) {
            $where .= " AND category_id = ?";
            $args[] = (int)$filters['category_id'];
        } elseif (!empty($filters['category_slug'])) {
            $cat = self::categoryBySlug($filters['category_slug']);
            if ($cat) {
                $where .= " AND category_id = ?";
                $args[] = (int)$cat['id'];
            } else {
                return [];
            }
        }
        if (!empty($filters['search'])) {
            $where .= " AND (name LIKE ? OR description LIKE ? OR sku LIKE ?)";
            $term  = '%' . $filters['search'] . '%';
            $args[] = $term; $args[] = $term; $args[] = $term;
        }
        if (!empty($filters['featured'])) {
            $where .= " AND featured = 1";
        }

        $order = match ($filters['sort'] ?? 'newest') {
            'price-asc'  => 'price ASC',
            'price-desc' => 'price DESC',
            'name'       => 'name ASC',
            'featured'   => 'featured DESC, created_at DESC',
            default      => 'created_at DESC',
        };

        $limit  = max(1, min(100, $limit));
        $offset = max(0, $offset);
        return Database::rows(
            "SELECT id, sku, name, slug, short_desc, price, sale_price,
                    stock_qty, manage_stock, image_url, featured, category_id,
                    (SELECT COUNT(*) FROM shop_product_variants v
                      WHERE v.product_id = shop_products.id) AS variant_count
               FROM shop_products
              WHERE {$where}
              ORDER BY {$order}
              LIMIT {$limit} OFFSET {$offset}",
            $args
        );
    }

    public static function publishedProductCount(array $filters = []): int {
        $tid = current_tenant_id();
        $where = "tenant_id = ? AND status = 'active'";
        $args  = [$tid];
        if (!empty($filters['category_id'])) {
            $where .= " AND category_id = ?";
            $args[] = (int)$filters['category_id'];
        }
        if (!empty($filters['search'])) {
            $where .= " AND (name LIKE ? OR description LIKE ?)";
            $term = '%' . $filters['search'] . '%';
            $args[] = $term; $args[] = $term;
        }
        return (int)Database::value("SELECT COUNT(*) FROM shop_products WHERE {$where}", $args);
    }

    // ══════════════════════════════════════════════════════
    // VARIANTS
    // ══════════════════════════════════════════════════════

    public static function variantsFor(int $productId): array {
        return Database::rows(
            "SELECT * FROM shop_product_variants
              WHERE product_id = ?
              ORDER BY display_order ASC, id ASC",
            [$productId]
        );
    }

    public static function variant(int $variantId): ?array {
        $row = Database::row("SELECT * FROM shop_product_variants WHERE id = ?", [$variantId]);
        return $row ?: null;
    }

    /** Effective price for a product, factoring in selected variant. */
    public static function variantPrice(array $product, ?array $variant = null): float {
        $base = self::effectivePrice($product);
        if ($variant) {
            $base += (float)$variant['price_diff'];
        }
        return round(max(0, $base), 2);
    }

    // ══════════════════════════════════════════════════════
    // CART (session-based, guest-friendly)
    // ══════════════════════════════════════════════════════

    /** Returns the cart id for this session, creating one if needed. */
    public static function cartId(string $sessionId): int {
        $tid = current_tenant_id();
        $row = Database::row(
            "SELECT id FROM shop_carts WHERE tenant_id = ? AND session_id = ?",
            [$tid, $sessionId]
        );
        if ($row) return (int)$row['id'];
        return Database::insert('shop_carts', [
            'tenant_id'  => $tid,
            'session_id' => $sessionId,
        ]);
    }

    public static function cartItems(string $sessionId): array {
        $cartId = self::cartId($sessionId);
        return Database::rows(
            "SELECT ci.*, p.name AS product_name, p.slug AS product_slug,
                    p.image_url, p.stock_qty AS product_stock, p.manage_stock,
                    v.attribute AS variant_attribute, v.value AS variant_value
               FROM shop_cart_items ci
               JOIN shop_products p ON p.id = ci.product_id
          LEFT JOIN shop_product_variants v ON v.id = ci.variant_id
              WHERE ci.cart_id = ?
              ORDER BY ci.added_at ASC",
            [$cartId]
        );
    }

    public static function cartTotals(string $sessionId): array {
        $items    = self::cartItems($sessionId);
        $subtotal = 0.0;
        $itemCount = 0;
        foreach ($items as $i) {
            $subtotal  += (float)$i['unit_price'] * (int)$i['qty'];
            $itemCount += (int)$i['qty'];
        }
        $taxRate  = (float)(Database::setting('shop.tax_rate') ?: 0);
        $tax      = round($subtotal * ($taxRate / 100), 2);
        // Pass the items to the shipping calculator so weight-based or
        // dimension-based plugins (flat-rate-shipping, USPS, etc.) have
        // what they need. Billing isn't known at cart-view time — that
        // comes from checkout — so we pass an empty array here.
        $shipping = self::calculateShipping($subtotal, $items, []);
        $total    = $subtotal + $tax + $shipping;
        return [
            'items'      => $items,
            'item_count' => $itemCount,
            'subtotal'   => round($subtotal, 2),
            'tax'        => $tax,
            'tax_rate'   => $taxRate,
            'shipping'   => round($shipping, 2),
            'total'      => round($total, 2),
        ];
    }

    public static function addToCart(string $sessionId, int $productId, int $qty = 1, ?int $variantId = null): array {
        $product = self::product($productId);
        if (!$product || $product['status'] !== 'active') {
            return ['ok' => false, 'error' => 'Product is not available.'];
        }

        $variant = null;
        if ($variantId !== null) {
            $variant = self::variant($variantId);
            if (!$variant || (int)$variant['product_id'] !== $productId) {
                return ['ok' => false, 'error' => 'Selected variant is not valid.'];
            }
        }

        $qty = max(1, min(999, $qty));

        // Stock check
        if ((int)$product['manage_stock']) {
            $available = $variant ? (int)$variant['stock_qty'] : (int)$product['stock_qty'];
            if ($available < $qty) {
                return ['ok' => false, 'error' => sprintf(
                    'Only %d available; you requested %d.', $available, $qty)];
            }
        }

        $price  = self::variantPrice($product, $variant);
        $cartId = self::cartId($sessionId);

        // Merge with existing line for same product+variant
        $existing = Database::row(
            "SELECT id, qty FROM shop_cart_items
              WHERE cart_id = ? AND product_id = ?
                AND " . ($variantId === null ? "variant_id IS NULL" : "variant_id = ?"),
            $variantId === null ? [$cartId, $productId] : [$cartId, $productId, $variantId]
        );

        if ($existing) {
            $newQty = (int)$existing['qty'] + $qty;
            Database::update('shop_cart_items',
                ['qty' => $newQty, 'unit_price' => $price],
                'id = ?', [$existing['id']]);
        } else {
            Database::insert('shop_cart_items', [
                'cart_id'    => $cartId,
                'product_id' => $productId,
                'variant_id' => $variantId,
                'qty'        => $qty,
                'unit_price' => $price,
            ]);
        }

        return ['ok' => true];
    }

    public static function updateCartQty(string $sessionId, int $cartItemId, int $qty): array {
        $cartId = self::cartId($sessionId);
        $item = Database::row("SELECT * FROM shop_cart_items WHERE id = ? AND cart_id = ?",
            [$cartItemId, $cartId]);
        if (!$item) return ['ok' => false, 'error' => 'Cart item not found.'];

        if ($qty <= 0) {
            Database::delete('shop_cart_items', 'id = ? AND cart_id = ?', [$cartItemId, $cartId]);
            return ['ok' => true];
        }
        $qty = min(999, $qty);
        Database::update('shop_cart_items', ['qty' => $qty], 'id = ? AND cart_id = ?',
            [$cartItemId, $cartId]);
        return ['ok' => true];
    }

    public static function removeCartItem(string $sessionId, int $cartItemId): void {
        $cartId = self::cartId($sessionId);
        Database::delete('shop_cart_items', 'id = ? AND cart_id = ?', [$cartItemId, $cartId]);
    }

    public static function clearCart(string $sessionId): void {
        $cartId = self::cartId($sessionId);
        Database::delete('shop_cart_items', 'cart_id = ?', [$cartId]);
    }

    /** Checkout: convert cart → order, clear cart on success. */
    /**
     * Return the list of registered payment providers, sorted by priority.
     *
     * Other plugins register payment providers via the `shop_payment_providers`
     * filter, returning the array with their provider appended. Each provider
     * is an array with:
     *
     *   id          string  Unique identifier (e.g. 'stripe', 'paypal')
     *   label       string  Human-readable name shown to customers
     *   description string  Short description shown under the label (optional)
     *   priority    int     Sort order (lower = first; default 100)
     *   available   bool    True if configured + usable; false hides it
     *   render_form callable():string  Optional extra HTML rendered inside
     *               the checkout form (e.g. card-element placeholder). Most
     *               redirect-based providers don't need this.
     *   handle_checkout callable(string $sid, array $billing):array
     *               Dispatch when this method is selected on place-order.
     *               Returns one of:
     *                 ['ok' => true, 'order_id' => N]     — synchronous
     *                 ['ok' => true, 'redirect'  => $url] — off-site
     *                 ['ok' => false, 'error' => $msg]
     *
     * Shop core always provides a 'manual' provider for offline / pay-later
     * orders. Plugins that want to OFFER nothing-to-pay shouldn't replace
     * 'manual' — they should leave it alone and add their own provider.
     */
    public static function paymentProviders(): array {
        $providers = Hook::applyFilters('shop_payment_providers', [
            [
                'id'          => 'manual',
                'label'       => __('payment_manual', 'Manual / Pay later'),
                'description' => __('payment_manual_desc',
                    'Place the order now; arrange payment with the seller.'),
                'priority'    => 1000,  // last by default
                'available'   => true,
                'render_form' => null,
                'handle_checkout' => function (string $sid, array $billing): array {
                    return self::checkoutCartManual($sid, $billing);
                },
            ],
        ]);

        // Filter out anything malformed or unavailable, then sort by priority.
        $clean = [];
        foreach ($providers as $p) {
            if (!is_array($p)) continue;
            if (empty($p['id']) || empty($p['label'])) continue;
            if (!is_callable($p['handle_checkout'] ?? null)) continue;
            if (empty($p['available'])) continue;
            $p['priority'] = (int)($p['priority'] ?? 100);
            $clean[] = $p;
        }
        usort($clean, fn($a, $b) => $a['priority'] <=> $b['priority']);
        return $clean;
    }

    /**
     * Look up one provider by id. Returns null if not registered or
     * unavailable. Use this from the checkout dispatcher.
     */
    public static function paymentProvider(string $id): ?array {
        foreach (self::paymentProviders() as $p) {
            if ($p['id'] === $id) return $p;
        }
        return null;
    }

    /**
     * The original synchronous checkout — used by the 'manual' provider
     * and as a fallback when no payment provider is selected.
     */
    public static function checkoutCartManual(string $sessionId, array $billing): array {
        return self::checkoutCart($sessionId, $billing);
    }

    public static function checkoutCart(string $sessionId, array $billing): array {
        $items = self::cartItems($sessionId);
        if (empty($items)) {
            return ['ok' => false, 'error' => 'Your cart is empty.'];
        }
        $email = trim($billing['email'] ?? '');
        $name  = trim(($billing['first_name'] ?? '') . ' ' . ($billing['last_name'] ?? ''));
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            return ['ok' => false, 'error' => 'A valid email is required.'];
        }
        if ($name === ' ' || $name === '') {
            return ['ok' => false, 'error' => 'A name is required.'];
        }

        // Build line items for createOrder(). Pass variant_id so stock
        // decrements against the right row, and unit_price so the
        // already-resolved cart price (which includes variant diff) is
        // honored even if the underlying product price has since changed.
        $orderItems = [];
        foreach ($items as $i) {
            $orderItems[] = [
                'product_id' => (int)$i['product_id'],
                'qty'        => (int)$i['qty'],
                'variant_id' => !empty($i['variant_id']) ? (int)$i['variant_id'] : null,
                'unit_price' => (float)$i['unit_price'],
            ];
        }

        try {
            $orderId = self::createOrder($email, $name, $orderItems, [
                'ship_address_1' => $billing['address_1'] ?? '',
                'ship_address_2' => $billing['address_2'] ?? '',
                'ship_city'      => $billing['city']      ?? '',
                'ship_state'     => $billing['state']     ?? '',
                'ship_postcode'  => $billing['postcode']  ?? '',
                'ship_country'   => $billing['country']   ?? '',
                'notes'          => $billing['notes']     ?? '',
            ]);
        } catch (\Throwable $e) {
            return ['ok' => false, 'error' => $e->getMessage()];
        }

        // Clear cart on success
        self::clearCart($sessionId);

        return ['ok' => true, 'order_id' => $orderId];
    }
}

