-- Migration: Add enrollment_end_date to purchase_orders for digital license end-date tracking
-- Used to display license active period (start → end) on AR invoices for JAMF and similar
-- time-bounded digital license products.

ALTER TABLE purchase_orders
  ADD COLUMN enrollment_end_date DATE NULL AFTER enrollment_date;

-- Backfill from shipment_invoices.details JSON where available.
-- TD Synnex Electronic Delivery invoices store both start and end dates
-- in details JSON when parsed (see products/shipment-parsers/td-synnex-parser.js).
UPDATE purchase_orders po
JOIN shipment_invoices si
  ON si.sp_number = po.sp_number
 AND JSON_UNQUOTE(JSON_EXTRACT(si.details, '$.delivery_type')) = 'digital'
 AND JSON_EXTRACT(si.details, '$.enrollment_end_date') IS NOT NULL
SET po.enrollment_end_date = DATE(JSON_UNQUOTE(JSON_EXTRACT(si.details, '$.enrollment_end_date')))
WHERE po.delivery_type = 'digital'
  AND po.enrollment_end_date IS NULL;