Subversion Repositories SmartDukaan

-- Reconcile inventory.currentinventorysnapshot with scan truth
-- Only for active tag_listing items, skips zero rows

-- Step 1: Detect drift (use this output for email alert)
-- Returns items where snapshot != scan truth (both under-counted and phantom)
SELECT scan_truth.itemId item_id, scan_truth.currentWarehouseId warehouse_id,
  scan_truth.avail AS scan_avail, IFNULL(cis.availability, 0) AS snap_avail,
  (scan_truth.avail - IFNULL(cis.availability, 0)) AS delta
FROM (
  SELECT ii.itemId, ii.currentWarehouseId,
    SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
             WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
             ELSE 0 END) avail
  FROM warehouse.inventoryItem ii
  JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
  JOIN catalog.tag_listing tl ON tl.item_id = ii.itemId AND tl.active = 1
  WHERE ii.currentWarehouseId > 0
  GROUP BY ii.itemId, ii.currentWarehouseId
  HAVING avail > 0
) scan_truth
LEFT JOIN inventory.currentinventorysnapshot cis
  ON cis.item_id = scan_truth.itemId AND cis.warehouse_id = scan_truth.currentWarehouseId
WHERE IFNULL(cis.availability, -1) != scan_truth.avail;

-- Step 2: Sync — upsert scan truth for active tag_listing items with positive stock
INSERT INTO inventory.currentinventorysnapshot (item_id, warehouse_id, availability)
SELECT scan_truth.itemId, scan_truth.currentWarehouseId, scan_truth.avail
FROM (
  SELECT ii.itemId, ii.currentWarehouseId,
    SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
             WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
             ELSE 0 END) avail
  FROM warehouse.inventoryItem ii
  JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
  JOIN catalog.tag_listing tl ON tl.item_id = ii.itemId AND tl.active = 1
  WHERE ii.currentWarehouseId > 0
  GROUP BY ii.itemId, ii.currentWarehouseId
  HAVING avail > 0
) scan_truth
LEFT JOIN inventory.currentinventorysnapshot cis
  ON cis.item_id = scan_truth.itemId AND cis.warehouse_id = scan_truth.currentWarehouseId
WHERE IFNULL(cis.availability, -1) != scan_truth.avail
ON DUPLICATE KEY UPDATE availability = VALUES(availability);

-- Step 3: Zero out phantom stock for tag_listing items (snapshot > 0 but scans say 0 or item not in tag_listing)
UPDATE inventory.currentinventorysnapshot cis
SET cis.availability = 0
WHERE cis.availability > 0
AND (
  cis.item_id NOT IN (SELECT DISTINCT item_id FROM catalog.tag_listing WHERE active = 1)
  OR NOT EXISTS (
    SELECT 1 FROM warehouse.inventoryItem ii
    JOIN warehouse.scanNew s ON s.inventoryItemId = ii.id
    WHERE ii.itemId = cis.item_id AND ii.currentWarehouseId = cis.warehouse_id
    GROUP BY ii.itemId, ii.currentWarehouseId
    HAVING SUM(CASE WHEN s.type IN ('PURCHASE','SALE_RET','MARKED_GOOD') THEN s.quantity
                     WHEN s.type IN ('SALE','LOST_IN_WAREHOUSE','PURCHASE_RETURN','MARKED_USED','MARKED_BAD') THEN -s.quantity
                     ELSE 0 END) > 0
  )
);