Subversion Repositories SmartDukaan

-- Remove vendor catalog pricing for internal suppliers, second pass (2026-09-15)
--
-- WHY
--   After the 2026-09-14 cleanup (drop_internal_vendor_catalog_pricing_20260914.sql) 100 internal requests were raised
--   and approved on 2026-09-14 18:07-18:25 for suppliers 325, 326, 371, 382, 406, because the internal-supplier guard
--   (r37634) was not yet deployed. Supplier 452 (NEW SPICE SOLUTIONS PRIVATE LIMITED (DL-HR)) was switched to internal
--   on 2026-09-15; confirmed by the user, so its pricing goes too.
--   Excluded as before: 479 G-MOBILE DEVICES, 509 DEEPAK MOBILE (internal flag unconfirmed).
--
-- REVERSIBLE: rows are copied to _bak_ tables first.

USE inventory;

CREATE TABLE inventory._bak_vcp_internal_20260915 AS
SELECT p.* FROM inventory.vendor_catalog_pricing p
 WHERE p.vendor_id IN (325, 326, 371, 382, 406, 452);

CREATE TABLE inventory._bak_vcpl_internal_20260915 AS
SELECT l.* FROM inventory.vendor_catalog_pricing_log l
 WHERE l.vendor_id IN (325, 326, 371, 382, 406, 452);

SELECT (SELECT COUNT(*) FROM inventory._bak_vcp_internal_20260915)  AS pricing_backed_up,
       (SELECT COUNT(*) FROM inventory._bak_vcpl_internal_20260915) AS log_backed_up;

START TRANSACTION;

DELETE p FROM inventory.vendor_catalog_pricing p
  JOIN inventory._bak_vcp_internal_20260915 b ON b.vendor_id = p.vendor_id AND b.catalog_id = p.catalog_id;

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

COMMIT;

-- Verify: all internal suppliers except 479/509 have no pricing left.
SELECT (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing p JOIN warehouse.supplier s ON s.id = p.vendor_id AND s.internal = 1
         WHERE p.vendor_id NOT IN (479, 509)) AS pricing_left,
       (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing_log l JOIN warehouse.supplier s ON s.id = l.vendor_id AND s.internal = 1
         WHERE l.vendor_id NOT IN (479, 509)) AS log_left;

-- Rollback:
-- INSERT INTO inventory.vendor_catalog_pricing     SELECT * FROM inventory._bak_vcp_internal_20260915;
-- INSERT INTO inventory.vendor_catalog_pricing_log SELECT * FROM inventory._bak_vcpl_internal_20260915;