-- READ ONLY. Order lines that froze the variant's purchase price while the product had a purchase
-- price set by hand on the admin form (pricing_manual_prices.purchase."*") - the bug fixed on
-- 2026-10-22 (SellerPricing::purchasePrice / variantPurchaseFromProduct). Changes nothing.
-- expected = the product's manual purchase + how much the variant's seller price is above the
-- product's (seller prices as they are now). Dollars (BASE vs RAW: equal for exchange-type products).

SELECT t.*,
       ROUND(t.frozen_line_total - t.quantity * t.per_piece_expected, 2) AS overpaid_to_seller
FROM (
    SELECT
        od.id                                   AS order_detail_id,
        o.id                                    AS order_id,
        o.combined_order_id,
        FROM_UNIXTIME(o.date)                   AS order_date,
        o.payment_status,
        od.delivery_status,
        p.id                                    AS product_id,
        p.user_id                               AS seller_user_id,
        od.variation,
        od.quantity,
        od.purchase_price                                      AS frozen_line_total,
        ROUND(od.purchase_price / GREATEST(od.quantity, 1), 2) AS per_piece_frozen,
        ROUND(CAST(JSON_UNQUOTE(JSON_EXTRACT(p.pricing_manual_prices, '$.purchase."*"')) AS DECIMAL(20, 2))
              + GREATEST(0, COALESCE(ps.seller_price, 0) - COALESCE(p.seller_price, ps.seller_price, 0)), 2) AS per_piece_expected
    FROM order_details od
    JOIN orders o   ON o.id = od.order_id
    JOIN products p ON p.id = od.product_id
    JOIN product_stocks ps ON ps.product_id = p.id AND ps.variant = od.variation
    WHERE p.pricing_mode_override = 'manual'
      AND JSON_VALID(p.pricing_manual_prices)
      AND JSON_EXTRACT(p.pricing_manual_prices, '$.purchase."*"') IS NOT NULL
      AND od.variation IS NOT NULL AND od.variation <> ''
      -- the variant had no purchase price of its own
      AND JSON_EXTRACT(p.pricing_manual_prices, CONCAT('$.purchase."', od.variation, '"')) IS NULL
      AND od.purchase_price IS NOT NULL
) t
WHERE ABS(t.per_piece_frozen - t.per_piece_expected) >= 0.01
ORDER BY t.order_id DESC;

-- Variants the migration 2026_10_22_000008 will change (before deploying):
SELECT p.id AS product_id, p.user_id, p.seller_price, p.purchase_price,
       JSON_UNQUOTE(JSON_EXTRACT(p.pricing_manual_prices, '$.purchase."*"')) AS manual_purchase,
       ps.variant, ps.seller_price AS variant_seller_price, ps.purchase_price AS variant_purchase_now,
       ROUND(CAST(JSON_UNQUOTE(JSON_EXTRACT(p.pricing_manual_prices, '$.purchase."*"')) AS DECIMAL(20, 2))
             + GREATEST(0, COALESCE(ps.seller_price, 0) - COALESCE(p.seller_price, ps.seller_price, 0)), 2) AS variant_purchase_after
FROM products p
JOIN product_stocks ps ON ps.product_id = p.id AND ps.variant <> ''
WHERE p.pricing_mode_override = 'manual'
  AND JSON_VALID(p.pricing_manual_prices)
  AND JSON_EXTRACT(p.pricing_manual_prices, '$.purchase."*"') IS NOT NULL
  AND JSON_EXTRACT(p.pricing_manual_prices, CONCAT('$.purchase."', ps.variant, '"')) IS NULL
HAVING variant_purchase_now IS NULL OR ABS(variant_purchase_now - variant_purchase_after) >= 0.005
ORDER BY p.id;