Subversion Repositories SmartDukaan

-- Remove vendor catalog pricing held against INTERNAL suppliers (2026-09-12)
--
-- WHY
--   Internal suppliers are our own warehouses. They never negotiated a price with anyone - every row they
--   hold was copied there from some other vendor, which is exactly what made internal movement prices drift
--   further from reality on each hop. Since r37603-05 nothing reads them: an internal movement is priced
--   from the stock being moved, resolved back to the ORIGINAL EXTERNAL vendor, and receiving no longer
--   validates price for those movements at all. The rows are now worse than useless - a stale one would
--   silently win over the derived price if any lookup were ever reintroduced.
--
-- ORDERING - deploy the fofo WAR carrying the warehouse-grn-request-items.vm fix FIRST.
--   That template rendered $vendorCatalogPricingLogMap.get($catalogId).getTransferPrice() with no null
--   guard, so with these rows gone every internal-supplier GRN line would print the raw reference text in
--   the System Price column. Velocity fails quietly, so it would not error - it would just look broken.
--   The fix renders "-" instead, which is honest: there is no independent system price for an internal
--   movement. V2FofoGrnController returns the same map as JSON and is unaffected by an empty map.
--
-- REVERSIBLE: every deleted row is copied to a _bak_ table first. To restore, INSERT ... SELECT back.
--   Membership is frozen into the backups at run time, so a supplier whose internal flag changes later
--   cannot silently widen or narrow what was removed.

-- Multi-table DELETE (the "DELETE alias FROM" form) needs a default schema even when every table is
-- fully qualified, so select one up front rather than relying on how the client was invoked.
USE inventory;

CREATE TABLE inventory._bak_vcp_internal_20260912 AS
SELECT p.* FROM inventory.vendor_catalog_pricing p
  JOIN warehouse.supplier s ON s.id = p.vendor_id AND s.internal = 1;

CREATE TABLE inventory._bak_vcpl_internal_20260912 AS
SELECT l.* FROM inventory.vendor_catalog_pricing_log l
  JOIN warehouse.supplier s ON s.id = l.vendor_id AND s.internal = 1;

CREATE TABLE inventory._bak_vip_internal_20260912 AS
SELECT v.* FROM inventory.vendoritempricing v
  JOIN warehouse.supplier s ON s.id = v.vendor_id AND s.internal = 1;

-- Expected as of 2026-09-12: 12,372 / 17,241 / 33,252 rows across 16 internal suppliers.
SELECT (SELECT COUNT(*) FROM inventory._bak_vcp_internal_20260912)  AS pricing_backed_up,
       (SELECT COUNT(*) FROM inventory._bak_vcpl_internal_20260912) AS log_backed_up,
       (SELECT COUNT(*) FROM inventory._bak_vip_internal_20260912)  AS itempricing_backed_up;

-- Delete against the frozen backups rather than re-reading supplier.internal, so the rows removed are
-- exactly the rows preserved.
DELETE p FROM inventory.vendor_catalog_pricing p
  JOIN inventory._bak_vcp_internal_20260912 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_20260912 b ON b.id = l.id;

DELETE v FROM inventory.vendoritempricing v
  JOIN inventory._bak_vip_internal_20260912 b
    ON b.vendor_id = v.vendor_id AND b.item_id = v.item_id;

-- Verify: all three must be 0.
SELECT (SELECT COUNT(*) FROM inventory.vendor_catalog_pricing p
          JOIN warehouse.supplier s ON s.id = p.vendor_id AND s.internal = 1) 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) AS log_left,
       (SELECT COUNT(*) FROM inventory.vendoritempricing v
          JOIN warehouse.supplier s ON s.id = v.vendor_id AND s.internal = 1) AS itempricing_left;

-- Rollback:
-- INSERT INTO inventory.vendor_catalog_pricing     SELECT * FROM inventory._bak_vcp_internal_20260912;
-- INSERT INTO inventory.vendor_catalog_pricing_log SELECT * FROM inventory._bak_vcpl_internal_20260912;
-- INSERT INTO inventory.vendoritempricing          SELECT * FROM inventory._bak_vip_internal_20260912;