-- =============================================================================
-- MIGRATION 003: Add language column to registration_messages
-- PURPOSE: Enables multi-language system messages.
--          PK changes from (message_key) to (message_key, language).
-- =============================================================================

-- Add language column with default 'es'
ALTER TABLE public.registration_messages
    ADD COLUMN IF NOT EXISTS language VARCHAR(5) NOT NULL DEFAULT 'es';

-- Tag all existing messages as Spanish
UPDATE public.registration_messages SET language = 'es';

-- Rebuild PK to include language
ALTER TABLE public.registration_messages DROP CONSTRAINT registration_messages_pkey;
ALTER TABLE public.registration_messages ADD PRIMARY KEY (message_key, language);

-- Seed missing keys used by fn_enroll_partner_system
INSERT INTO registration_messages (message_key, message_text, language) VALUES
('ERR_ALREADY_PARTNER',  'REJECTED: This email is already registered as a partner.',          'es'),
('ERR_STUDENT_MISMATCH', 'REJECTED: Student identity mismatch. ID linked to different email.', 'es')
ON CONFLICT (message_key, language) DO NOTHING;