Subversion Repositories SmartDukaan

-- Remove vendor catalog pricing held against INTERNAL (New Spice) suppliers (2026-09-14)
--
-- WHY
--   Internal suppliers are our own warehouses; every price they hold was copied from an external vendor. Internal
--   movement pricing (r37603-05), the internal PO item picker (r37609) and the GRN view null guard (r37606) are all
--   live and read external vendor pricing or stock instead, so these rows are unused and only go stale.
--   Supersedes drop_internal_vendor_pricing_20260912.sql, which was rolled back the same day (its _bak_ tables still
--   exist, so it cannot be re-run as written).
--
-- SCOPE
--   The 15 New Spice internal suppliers, frozen by id. Excluded: 479 G-MOBILE DEVICES (Gurugram) and 509 DEEPAK
--   MOBILE - flagged internal but their flag is unconfirmed.
--   inventory.vendoritempricing is NOT touched: warehouse billing reads it until r37622 is deployed.
--
-- REVERSIBLE: rows are copied to _bak_ tables first.

USE inventory;

CREATE TABLE inventory._bak_vcp_internal_20260914 AS
SELECT p.* FROM inventory.vendor_catalog_pricing p
 WHERE p.vendor_id IN (275, 324, 325, 326, 371, 382, 406, 413, 419, 426, 440, 457, 458, 501, 502);

CREATE TABLE inventory._bak_vcpl_internal_20260914 AS
SELECT l.* FROM inventory.vendor_catalog_pricing_log l
 WHERE l.vendor_id IN (275, 324, 325, 326, 371, 382, 406, 413, 419, 426, 440, 457, 458, 501, 502);

SELECT (SELECT COUNT(*) FROM inventory._bak_vcp_internal_20260914)  AS pricing_backed_up,
       (SELECT COUNT(*) FROM inventory._bak_vcpl_internal_20260914) AS log_backed_up;

START TRANSACTION;

DELETE p FROM inventory.vendor_catalog_pricing p
  JOIN inventory._bak_vcp_internal_20260914 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_20260914 b ON b.id = l.id;

COMMIT;

-- Verify: both 0.
SELECT (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing
         WHERE vendor_id IN (275, 324, 325, 326, 371, 382, 406, 413, 419, 426, 440, 457, 458, 501, 502)) AS pricing_left,
       (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing_log
         WHERE vendor_id IN (275, 324, 325, 326, 371, 382, 406, 413, 419, 426, 440, 457, 458, 501, 502)) AS log_left;

-- Rollback:
-- INSERT INTO inventory.vendor_catalog_pricing     SELECT * FROM inventory._bak_vcp_internal_20260914;
-- INSERT INTO inventory.vendor_catalog_pricing_log SELECT * FROM inventory._bak_vcpl_internal_20260914;