-- ================================================
-- Migration: Add invoice_code to program, session and program type
-- Date: 2026-06-22
-- Adds an `invoice_code` column used as the program code in invoice
-- sequence numbers (e.g. the "TAT09" in TAT09F/001/2627). It mirrors the
-- On program creation the value is inherited from the program type, and
-- a created session inherits it from its program.
-- Safe to re-run (IF NOT EXISTS).
-- ================================================

ALTER TABLE program_v1
  ADD COLUMN IF NOT EXISTS invoice_code VARCHAR(5);

ALTER TABLE program_type_v1
  ADD COLUMN IF NOT EXISTS invoice_code VARCHAR(5);

ALTER TABLE program_session
  ADD COLUMN IF NOT EXISTS invoice_code VARCHAR(5);

-- Verification
SELECT table_name, column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns
WHERE column_name = 'invoice_code'
  AND table_name IN ('program_v1', 'program_type_v1', 'program_session')
ORDER BY table_name;

--- update existing program types with their invoice codes
select * from program_type_v1 where key = 'PT_TAT';
Update program_type_v1
set invoice_code = 'TAT09'
where key = 'PT_TAT';

--- update existing programs with their invoice codes
select * from program_v1 where program_type_id = (select id from program_type_v1 where key = 'PT_TAT');

Update program_v1
set invoice_code = 'TAT09'
where program_type_id = (select id from program_type_v1 where key = 'PT_TAT');