Subversion Repositories SmartDukaan

-- Backdated PO auto-approval: backfill of the POs still stuck awaiting an HOD click.
--
-- Backdated POs used to be parked in status 0 (INIT) until someone clicked a mailed approval link. INIT keeps a
-- PO out of the PO list, out of GRN/invoice matching and out of the auto-close sweep, so an unclicked PO is
-- simply frozen. 440 POs are sitting that way, going back to 2024.
--
-- Code now auto-approves at creation, so only POs that are still operationally live are worth moving. POScheduler
-- auto-closes READY POs older than 6 days (4 days for internal suppliers), so anything older than that window
-- would go READY -> CLOSED on the next cron tick and never be receivable. That leaves three:
--
--   53974  2026-09-15  BSB Marketing (external)          -- stays open
--   53901  2026-09-14  BSB Marketing (external)          -- stays open
--   53855  2026-09-12  NSSPL UP-DLHR (internal)          -- past its 4-day window, will auto-close
--
-- The creator of a historical PO was never recorded anywhere, so these are stamped SYSTEM (backfill) rather than
-- guessed at. New POs carry their real creator.
--
-- approvedOn is a varchar(128) holding 'YYYY-MM-DD HH:MM:SS.SSS' - that is what Hibernate writes and reads back.

-- Backup before touching anything.
CREATE TABLE IF NOT EXISTS warehouse.poapproval_backup_20260917 AS
SELECT * FROM warehouse.poapproval WHERE poId IN (53974, 53901, 53855);

CREATE TABLE IF NOT EXISTS warehouse.purchaseorder_backup_20260917 AS
SELECT id, status, updatedAt FROM warehouse.purchaseorder WHERE id IN (53974, 53901, 53855);

-- Stamp the approval rows, only where still genuinely unapproved.
UPDATE warehouse.poapproval
SET approvedOn = LEFT(DATE_FORMAT(NOW(3), '%Y-%m-%d %H:%i:%s.%f'), 23),
    approvedBy = 'SYSTEM (backfill)'
WHERE poId IN (53974, 53901, 53855)
  AND approvedOn IS NULL;

-- Release the POs, only where still INIT.
UPDATE warehouse.purchaseorder
SET status = 1,
    updatedAt = NOW()
WHERE id IN (53974, 53901, 53855)
  AND status = 0;

-- Verify: all three should read status 1 with an approvedBy set.
SELECT p.id, p.poNumber, p.status, DATE(p.createdAt) poDate, a.approvedOn, a.approvedBy
FROM warehouse.purchaseorder p
JOIN warehouse.poapproval a ON a.poId = p.id
WHERE p.id IN (53974, 53901, 53855);