-- =============================================================================
-- MIGRATION 002: Create email_templates table
-- PURPOSE: Stores multi-language email templates for all system events.
--          PK is (template_key, language) to support i18n natively.
-- EMAIL_HOOK: Backend reads this table to build and dispatch notifications.
-- MOODLE_HOOK: May delegate to Moodle Messaging API when integrated.
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.email_templates (
    template_key  VARCHAR(50)  NOT NULL,
    language      VARCHAR(5)   NOT NULL DEFAULT 'es',
    subject       VARCHAR(255) NOT NULL,
    body_html     TEXT         NOT NULL,
    body_text     TEXT,
    active        BOOLEAN      NOT NULL DEFAULT true,
    updated_at    valid_timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (template_key, language)
);

-- Seed: placeholder templates — subject/body to be defined by content team
INSERT INTO email_templates (template_key, language, subject, body_html, body_text) VALUES
('ENROLLMENT_ACTIVE_STUDENT',  'es', 'Bienvenido al curso STC',         '<p>Hola {{student_name}}, tu inscripción ha sido confirmada.</p>',    'Hola {{student_name}}, tu inscripción ha sido confirmada.'),
('ENROLLMENT_ACTIVE_TUTOR',    'es', 'Inscripción confirmada',           '<p>Hola {{tutor_name}}, el alumno {{student_name}} ha sido inscrito.', 'Hola {{tutor_name}}, el alumno {{student_name}} ha sido inscrito.'),
('ENROLLMENT_PENDING_STUDENT', 'es', 'En lista de espera',               '<p>Hola {{student_name}}, estás en lista de espera para el curso.</p>','Hola {{student_name}}, estás en lista de espera para el curso.'),
('ENROLLMENT_PENDING_TUTOR',   'es', 'Alumno en lista de espera',        '<p>Hola {{tutor_name}}, {{student_name}} está en lista de espera.</p>','Hola {{tutor_name}}, {{student_name}} está en lista de espera.'),
('ENROLLMENT_REJECTED_TUTOR',  'es', 'Inscripción no procesada',         '<p>Hola {{tutor_name}}, la inscripción no pudo ser procesada.</p>',   'Hola {{tutor_name}}, la inscripción no pudo ser procesada.'),
('GUEST_PROMOTED_PARTNER',     'es', 'Tu cuenta ha sido activada',       '<p>Hola {{student_name}}, ya eres Partner activo en STC.</p>',        'Hola {{student_name}}, ya eres Partner activo en STC.');

-- Grants
GRANT SELECT ON public.email_templates TO "elfi1953_stc-eu";