Subversion Repositories SmartDukaan

-- DN lifecycle: add status column to debit_note, add retailer_acknowledge_timestamp to purchase_return_order

ALTER TABLE fofo.debit_note
  ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'CREATED';

ALTER TABLE fofo.purchase_return_order
  ADD COLUMN retailer_acknowledge_timestamp DATETIME NULL;

-- Backfill debit_note.status from purchase_return_order timestamps (DNs with PROs)
UPDATE fofo.debit_note dn SET status = 'APPROVED'
WHERE EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.refund_timestamp IS NOT NULL);

UPDATE fofo.debit_note dn SET status = 'REJECTED'
WHERE status = 'CREATED'
AND EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.reject_timestamp IS NOT NULL);

UPDATE fofo.debit_note dn SET status = 'RECEIVED'
WHERE status = 'CREATED'
AND EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id AND pro.receive_timestamp IS NOT NULL);

-- Backfill debit_note.status from purchase_return_item for early DNs without PROs
UPDATE fofo.debit_note dn SET status = 'APPROVED'
WHERE status = 'CREATED'
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id)
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_item pri WHERE pri.debit_note_id = dn.id AND pri.status NOT IN ('RETURNED'));

UPDATE fofo.debit_note dn SET status = 'CANCELLED'
WHERE status = 'CREATED'
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id)
AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_item pri WHERE pri.debit_note_id = dn.id AND pri.status NOT IN ('REJECTED', 'DEBIT_NOTE_CANCELLED'));

-- Index for filtering by status
CREATE INDEX idx_debit_note_status ON fofo.debit_note (status);