<?php
/**
 * QBWS_Mapper — CRUD helpers for the product/customer/category/order
 * mapping tables. Same role as loadMappings()/insertProduct-style helpers
 * in qb_sync_master_product_records_v2.php, generalized across entity types.
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This plugin reads/writes only its own custom tables (live sync state), which must never be served from an object cache.

class QBWS_Mapper
{
    /**
     * Per-request memoization for the single-item lookups below. Cheap
     * insurance against the same id getting looked up more than once in
     * one page load (e.g. a hook chain that checks a mapping, then acts
     * on it, then something else checks it again) — never a substitute
     * for the bulk methods further down when you already know you're
     * about to look up many ids at once; those still do it in one query.
     */
    private static array $productCache = [];
    private static array $customerCache = [];

    // ── Products (variation_id = 0 for a simple product) ───────────────────

    public static function product_by_qb_id(string $qbItemId, int $variationId = 0): ?int
    {
        global $wpdb;
        $pid = $wpdb->get_var($wpdb->prepare(
            "SELECT product_id FROM {$wpdb->prefix}qbws_product_map WHERE qb_item_id = %s AND variation_id = %d",
            $qbItemId, $variationId
        ));
        return $pid ? (int) $pid : null;
    }

    public static function qb_id_by_product(int $productId, int $variationId = 0): ?string
    {
        $cacheKey = "{$productId}:{$variationId}";
        if (array_key_exists($cacheKey, self::$productCache)) {
            return self::$productCache[$cacheKey];
        }

        global $wpdb;
        $qbId = $wpdb->get_var($wpdb->prepare(
            "SELECT qb_item_id FROM {$wpdb->prefix}qbws_product_map WHERE product_id = %d AND variation_id = %d",
            $productId, $variationId
        )) ?: null;

        return self::$productCache[$cacheKey] = $qbId;
    }

    /**
     * Bulk version of qb_id_by_product() for a single parent product's
     * variations — one query instead of one per variation. Used by the
     * product edit screen's "X of Y variations synced" summary, which
     * would otherwise run a separate query per variation on every page
     * load for a variable product.
     *
     * @return array<int,string> variation_id => qb_item_id, only for variations that ARE mapped
     */
    public static function qb_ids_for_variations(int $productId, array $variationIds): array
    {
        if (empty($variationIds)) return [];

        global $wpdb;
        $placeholders = implode(',', array_fill(0, count($variationIds), '%d'));
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- the IN() list is built only from %d/%s placeholders; every value is bound by $wpdb->prepare().
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT variation_id, qb_item_id FROM {$wpdb->prefix}qbws_product_map
             WHERE product_id = %d AND variation_id IN ({$placeholders})",
            array_merge([$productId], $variationIds)
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['variation_id']] = $row['qb_item_id'];
            self::$productCache["{$productId}:{$row['variation_id']}"] = $row['qb_item_id']; // warm the single-item cache too, since we're already here
        }
        return $map;
    }

    /**
     * Bulk reverse lookup: given a batch of QB item ids (e.g. one page of
     * a QB item poll), returns which ones are already mapped to a WC
     * product — one query instead of one per item. Used by
     * QBWS_Sync_Products::run()/import_items() so a 500-product catalog
     * doesn't run 500 individual SELECTs on every poll.
     *
     * @return array<string,array{product_id:int,variation_id:int}> qb_item_id => mapping
     */
    public static function product_map_for_qb_items(array $qbItemIds): array
    {
        if (empty($qbItemIds)) return [];

        global $wpdb;
        $placeholders = implode(',', array_fill(0, count($qbItemIds), '%s'));
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- the IN() list is built only from %d/%s placeholders; every value is bound by $wpdb->prepare().
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT qb_item_id, product_id, variation_id FROM {$wpdb->prefix}qbws_product_map
             WHERE qb_item_id IN ({$placeholders})",
            $qbItemIds
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

        $map = [];
        foreach ($rows as $row) {
            $map[$row['qb_item_id']] = [
                'product_id'   => (int) $row['product_id'],
                'variation_id' => (int) $row['variation_id'],
            ];
        }
        return $map;
    }

    public static function upsert_product_map(int $productId, int $variationId, string $qbItemId, ?string $syncToken = null): void
    {
        global $wpdb;
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$wpdb->prefix}qbws_product_map (product_id, variation_id, qb_item_id, qb_sync_token, last_synced_at)
             VALUES (%d, %d, %s, %s, NOW())
             ON DUPLICATE KEY UPDATE qb_item_id = VALUES(qb_item_id), qb_sync_token = VALUES(qb_sync_token), last_synced_at = NOW()",
            $productId, $variationId, $qbItemId, $syncToken
        ));
        unset(self::$productCache["{$productId}:{$variationId}"]); // stale after a write — let the next read re-fetch
    }

    public static function all_mapped_product_ids(): array
    {
        global $wpdb;
        return array_map('intval', $wpdb->get_col("SELECT DISTINCT product_id FROM {$wpdb->prefix}qbws_product_map"));
    }

    // ── Customers ────────────────────────────────────────────────────────

    public static function customer_by_qb_id(string $qbCustomerId): ?int
    {
        global $wpdb;
        $id = $wpdb->get_var($wpdb->prepare(
            "SELECT wc_customer_id FROM {$wpdb->prefix}qbws_customer_map WHERE qb_customer_id = %s",
            $qbCustomerId
        ));
        return $id ? (int) $id : null;
    }

    public static function qb_id_by_customer(int $wcCustomerId): ?string
    {
        if (array_key_exists($wcCustomerId, self::$customerCache)) {
            return self::$customerCache[$wcCustomerId];
        }

        global $wpdb;
        $qbId = $wpdb->get_var($wpdb->prepare(
            "SELECT qb_customer_id FROM {$wpdb->prefix}qbws_customer_map WHERE wc_customer_id = %d",
            $wcCustomerId
        )) ?: null;

        return self::$customerCache[$wcCustomerId] = $qbId;
    }

    public static function upsert_customer_map(int $wcCustomerId, string $qbCustomerId, ?string $syncToken = null): void
    {
        global $wpdb;
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$wpdb->prefix}qbws_customer_map (wc_customer_id, qb_customer_id, qb_sync_token, last_synced_at)
             VALUES (%d, %s, %s, NOW())
             ON DUPLICATE KEY UPDATE qb_customer_id = VALUES(qb_customer_id), qb_sync_token = VALUES(qb_sync_token), last_synced_at = NOW()",
            $wcCustomerId, $qbCustomerId, $syncToken
        ));
        unset(self::$customerCache[$wcCustomerId]); // stale after a write — let the next read re-fetch
    }

    // ── Orders ───────────────────────────────────────────────────────────

    public static function order_by_qb_txn(string $qbTxnId, string $txnType = 'Invoice'): ?int
    {
        global $wpdb;
        $id = $wpdb->get_var($wpdb->prepare(
            "SELECT wc_order_id FROM {$wpdb->prefix}qbws_order_map WHERE qb_txn_id = %s AND qb_txn_type = %s",
            $qbTxnId, $txnType
        ));
        return $id ? (int) $id : null;
    }

    public static function order_map_row(int $wcOrderId): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}qbws_order_map WHERE wc_order_id = %d",
            $wcOrderId
        ), ARRAY_A);
        return $row ?: null;
    }

    public static function upsert_order_map(int $wcOrderId, string $qbTxnId, string $txnType, array $extra = []): void
    {
        global $wpdb;
        $data = array_merge([
            'wc_order_id' => $wcOrderId,
            'qb_txn_id'   => $qbTxnId,
            'qb_txn_type' => $txnType,
        ], $extra);

        $existing = self::order_map_row($wcOrderId);
        if ($existing) {
            $wpdb->update($wpdb->prefix . 'qbws_order_map', $data, ['wc_order_id' => $wcOrderId]);
        } else {
            $wpdb->insert($wpdb->prefix . 'qbws_order_map', $data);
        }
    }
}