Subversion Repositories SmartDukaan

-- Revert vendor catalog pricing written by price drops (2026-09-15)
--
-- WHY
--   r37622 made price drops auto-approve their DP/MOP into vendor_catalog_pricing (applyPriceDrop). Decision
--   2026-09-15: price drops write only price_drop + tag_listing; vendor_catalog_pricing must not be touched.
--   Five price drops on 2026-09-15 12:48-12:57 (catalogs 1026119, 1026063, 1026064, 1026163, 1026164) created 30
--   APPROVED logs (ids 66817-66846) and moved each vendor's current DP/MOP/effected_on. TP was never changed and no
--   existing approval was rejected.
--
-- WHAT
--   Restore DP, MOP and effected_on of each affected vendor_catalog_pricing row from that vendor's previous APPROVED
--   log, but only while the row still holds the price-drop values; then delete the 30 logs. Backed up first.

USE inventory;

CREATE TABLE inventory._bak_vcpl_price_drop_20260915 AS
SELECT l.* FROM inventory.vendor_catalog_pricing_log l WHERE l.id BETWEEN 66817 AND 66846;

CREATE TABLE inventory._bak_vcp_price_drop_20260915 AS
SELECT p.* FROM inventory.vendor_catalog_pricing p
  JOIN inventory._bak_vcpl_price_drop_20260915 b ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id;

SELECT (SELECT COUNT(*) FROM inventory._bak_vcpl_price_drop_20260915) AS logs_backed_up,
       (SELECT COUNT(*) FROM inventory._bak_vcp_price_drop_20260915)  AS pricing_backed_up;

START TRANSACTION;

UPDATE inventory.vendor_catalog_pricing p
  JOIN inventory._bak_vcpl_price_drop_20260915 b
    ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id
   AND p.effected_on = b.effected_on AND ABS(p.dealer_price - b.dealer_price) < 0.01
  JOIN inventory.vendor_catalog_pricing_log prev ON prev.id = (
        SELECT l2.id FROM inventory.vendor_catalog_pricing_log l2
         WHERE l2.vendor_id = p.vendor_id AND l2.catalog_id = p.catalog_id AND l2.status = 'APPROVED'
           AND l2.id NOT BETWEEN 66817 AND 66846
         ORDER BY l2.effected_on DESC, l2.id DESC LIMIT 1)
   SET p.dealer_price = prev.dealer_price,
       p.mop          = prev.mop,
       p.effected_on  = prev.effected_on;

DELETE l FROM inventory.vendor_catalog_pricing_log l
  JOIN inventory._bak_vcpl_price_drop_20260915 b ON b.id = l.id;

COMMIT;

-- Verify: logs_left = 0, still_price_drop_values = 0
SELECT (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing_log WHERE id BETWEEN 66817 AND 66846) AS logs_left,
       (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing p
          JOIN inventory._bak_vcpl_price_drop_20260915 b
            ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id AND p.effected_on = b.effected_on) AS still_price_drop_values;

-- Rollback:
-- INSERT INTO inventory.vendor_catalog_pricing_log SELECT * FROM inventory._bak_vcpl_price_drop_20260915;
-- UPDATE inventory.vendor_catalog_pricing p JOIN inventory._bak_vcp_price_drop_20260915 b
--    ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id
--   SET p.dealer_price = b.dealer_price, p.mop = b.mop, p.effected_on = b.effected_on;