Subversion Repositories SmartDukaan

-- Drop dead stock-ageing columns (2026-09-12)
--
-- WHY THESE ARE DEAD
--   warehouse.inventoryItem.rootInvoiceDate
--     No writer exists anywhere in the codebase - nothing ever calls setRootInvoiceDate().
--     Its only reader was ScheduledTasks.refreshSnapshotAgeing(), which ran every 30 minutes
--     and whose UPDATE ... JOIN therefore matched zero rows on every run.
--     Verified on hadb1: 0 of 1,528,669 rows have a non-null value.
--
--   inventory.currentinventorysnapshot.oldest_invoice_date / newest_invoice_date
--     Written only by that same no-op task; read by nothing - the entity getters on
--     SaholicInventorySnapshot have no callers, and no Reportico report on static0
--     references either column (checked all XMLs under /var/www/html/reports/projects).
--     Verified on hadb1: 0 of 11,686 rows have a non-null value.
--
-- NOT AFFECTED
--   warehouse.inventoryItem.originalInventoryItemId is NOT dropped. It is populated on
--   157,020 rows and is the "original external supplier" tag used for internal-movement
--   pricing. Live stock is largely untagged only because the backfill call in
--   Application.java was commented out (restored in the same change as this script).
--
--   Stock ageing itself is unaffected: the ageing that is actually in use resolves the
--   original external supplier at query time by serialNumber + supplier.internal = false
--   (WarehouseInventoryItem named queries findStockAgeingBy*, and the Reportico report
--   FOCO/ImeiSupplierPricing.xml). Neither reads the columns dropped here.
--
-- ORDERING: deploy the code change first (it removes the entity fields), then run this.
-- Running it against an app that still maps these fields will break Hibernate on those
-- entities.
--
-- MySQL 5.7 note: DROP COLUMN rebuilds the table. ALGORITHM=INPLACE, LOCK=NONE is stated
-- explicitly so the statement fails loudly rather than silently taking a blocking copy.
-- inventoryItem is ~1.5M rows; expect a few minutes and free disk equal to table size.

ALTER TABLE warehouse.inventoryItem
    DROP COLUMN rootInvoiceDate,
    ALGORITHM = INPLACE, LOCK = NONE;

ALTER TABLE inventory.currentinventorysnapshot
    DROP COLUMN oldest_invoice_date,
    DROP COLUMN newest_invoice_date,
    ALGORITHM = INPLACE, LOCK = NONE;

-- Verification (expect 0 rows each):
-- SELECT COLUMN_NAME FROM information_schema.COLUMNS
--  WHERE TABLE_SCHEMA='warehouse' AND TABLE_NAME='inventoryItem'
--    AND COLUMN_NAME='rootInvoiceDate';
-- SELECT COLUMN_NAME FROM information_schema.COLUMNS
--  WHERE TABLE_SCHEMA='inventory' AND TABLE_NAME='currentinventorysnapshot'
--    AND COLUMN_NAME IN ('oldest_invoice_date','newest_invoice_date');

-- Rollback (columns were empty, so restoring them loses nothing):
-- ALTER TABLE warehouse.inventoryItem ADD COLUMN rootInvoiceDate DATE NULL;
-- ALTER TABLE inventory.currentinventorysnapshot ADD COLUMN oldest_invoice_date DATE NULL,
--                                                ADD COLUMN newest_invoice_date DATE NULL;