-- ================================================
-- Migration: Update PT_PAP invoice_code to 'PAPSEP'
-- Date: 2026-08-09
-- Live invoice sequence numbers follow {invoiceCode}{L/F}/{seq}/{year}
-- (see invoice.service.ts generateInvoiceSequenceNumber), e.g.:
--   Online:  PAPSEPL/001/2627
--   Offline: PAPSEPF/001/2627
-- invoice_code is currently VARCHAR(5) (see 2026-06-22-02-add-invoice-code.sql)
-- and 'PAPSEP' is 6 chars, so the column is widened first.
-- ================================================

ALTER TABLE program_v1
  ALTER COLUMN invoice_code TYPE VARCHAR(6);

ALTER TABLE program_type_v1
  ALTER COLUMN invoice_code TYPE VARCHAR(6);

ALTER TABLE program_session
  ALTER COLUMN invoice_code TYPE VARCHAR(6);

--- update invoice_code in program_type_v1 (source for new PAP programs going forward)
select id, name, key, invoice_code from program_type_v1 where key = 'PT_PAP';

update program_type_v1
set invoice_code = 'PAPSEP'
where key = 'PT_PAP';

--- update invoice_code in program_v1 (existing PAP programs already inherited the old code)
select id, program, name, invoice_code from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_PAP');

update program_v1
set invoice_code = 'PAPSEP'
where program_type_id = (select id from program_type_v1 where key = 'PT_PAP');
