-- =============================================================================
-- MIGRATION 008: Email notification dispatch system
-- PURPOSE:  (1) email_dispatch_rules table  — maps event_type → template + recipient fields
--           (2) fn_substitute_template      — replaces {{key}} placeholders in a string
--           (3) fn_prepare_notifications    — returns rendered rows ready to send via SMTP
-- DEPENDS:  migration 002 (email_templates table must already exist)
-- USAGE:    SELECT * FROM fn_prepare_notifications('ENROLLMENT_ACTIVE', '{"tutor_name":"..."}'::jsonb);
-- NODE.JS:  notificationService.js calls this function and iterates the result with nodemailer
-- =============================================================================


-- ─────────────────────────────────────────────────────────────────────────────
-- 1. DISPATCH RULES TABLE
--    Each row maps one event_type to one template, saying which context fields
--    hold the recipient's email address and display name.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.email_dispatch_rules (
    id           SERIAL       PRIMARY KEY,
    event_type   VARCHAR(50)  NOT NULL,
    template_key VARCHAR(50)  NOT NULL,
    email_field  VARCHAR(50)  NOT NULL,   -- key in context JSONB that holds the recipient email
    name_field   VARCHAR(50)  NOT NULL,   -- key in context JSONB that holds the recipient name
    language     VARCHAR(5)   NOT NULL DEFAULT 'es',
    active       BOOLEAN      NOT NULL DEFAULT true,
    CONSTRAINT uq_dispatch_rule
        UNIQUE (event_type, template_key, language),
    CONSTRAINT fk_dispatch_template
        FOREIGN KEY (template_key, language)
        REFERENCES public.email_templates(template_key, language)
);

CREATE INDEX IF NOT EXISTS idx_dispatch_event_active
    ON public.email_dispatch_rules(event_type)
    WHERE active = true;


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. ADD CLASS_REMINDER TEMPLATE  (new — not in migration 002)
--    Must be inserted BEFORE the dispatch rules due to FK constraint.
-- ─────────────────────────────────────────────────────────────────────────────
INSERT INTO public.email_templates (template_key, language, subject, body_html, body_text)
VALUES (
    'CLASS_REMINDER_STUDENT', 'es',
    'Recordatorio — Clase de {{course_name}} · {{session_date}}',
    '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#1a4a8a;">📅 Tu clase comienza pronto</h2>
  <p>Hola <strong>{{student_name}}</strong>,</p>
  <p>Tienes una sesión de <strong>{{course_name}}</strong> programada para:</p>
  <div style="background:#e8f0fe;padding:15px;border-radius:8px;margin:20px 0;text-align:center;">
    <p style="font-size:1.2em;font-weight:bold;margin:0;">{{session_date}}</p>
  </div>
  <div style="text-align:center;margin:30px 0;">
    <a href="{{zoom_link}}"
       style="background:#1a4a8a;color:white;padding:14px 28px;
              text-decoration:none;border-radius:6px;font-size:1.1em;display:inline-block;">
      🎥 Unirse a la clase
    </a>
  </div>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    'Hola {{student_name}}, tienes clase de {{course_name}} el {{session_date}}. Zoom: {{zoom_link}}'
)
ON CONFLICT (template_key, language) DO NOTHING;


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. SEED DISPATCH RULES
--    Now that all templates exist (002 + CLASS_REMINDER above), the FK is safe.
-- ─────────────────────────────────────────────────────────────────────────────
INSERT INTO public.email_dispatch_rules (event_type, template_key, email_field, name_field, language)
VALUES
    ('ENROLLMENT_ACTIVE',   'ENROLLMENT_ACTIVE_STUDENT',  'student_email', 'student_name', 'es'),
    ('ENROLLMENT_ACTIVE',   'ENROLLMENT_ACTIVE_TUTOR',    'tutor_email',   'tutor_name',   'es'),
    ('ENROLLMENT_PENDING',  'ENROLLMENT_PENDING_STUDENT', 'student_email', 'student_name', 'es'),
    ('ENROLLMENT_PENDING',  'ENROLLMENT_PENDING_TUTOR',   'tutor_email',   'tutor_name',   'es'),
    ('ENROLLMENT_REJECTED', 'ENROLLMENT_REJECTED_TUTOR',  'tutor_email',   'tutor_name',   'es'),
    ('GUEST_PROMOTED',      'GUEST_PROMOTED_PARTNER',     'student_email', 'student_name', 'es'),
    ('CLASS_REMINDER',      'CLASS_REMINDER_STUDENT',     'student_email', 'student_name', 'es')
ON CONFLICT (event_type, template_key, language) DO NOTHING;


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. IMPROVE EXISTING TEMPLATES (migration 002 had placeholder HTML)
-- ─────────────────────────────────────────────────────────────────────────────

UPDATE public.email_templates SET
    subject   = 'Bienvenido al STC — Tu inscripción está confirmada',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#2c5f2e;">¡Bienvenido al STC!</h2>
  <p>Hola <strong>{{student_name}}</strong>,</p>
  <p>Tu inscripción en <strong>{{course_name}}</strong> ha sido confirmada.</p>
  <div style="background:#f5f5f5;padding:15px;border-radius:8px;margin:20px 0;">
    <p style="margin:0;"><strong>Tu tutor:</strong> {{tutor_name}}</p>
    <p style="margin:5px 0 0;"><strong>Contacto:</strong> {{tutor_email}}</p>
  </div>
  <p>Pronto recibirás más información sobre las próximas sesiones.</p>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{student_name}}, tu inscripción en {{course_name}} está confirmada. Tu tutor es {{tutor_name}} ({{tutor_email}}).'
WHERE template_key = 'ENROLLMENT_ACTIVE_STUDENT' AND language = 'es';

UPDATE public.email_templates SET
    subject   = 'Nueva inscripción — {{student_name}} en {{course_name}}',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#2c5f2e;">✅ Nueva inscripción registrada</h2>
  <p>Hola <strong>{{tutor_name}}</strong>,</p>
  <p>Se ha inscrito un nuevo alumno en tu grupo de <strong>{{course_name}}</strong>:</p>
  <div style="background:#f5f5f5;padding:15px;border-radius:8px;margin:20px 0;">
    <p style="margin:0;"><strong>Alumno:</strong> {{student_name}}</p>
    <p style="margin:5px 0 0;"><strong>Email:</strong> {{student_email}}</p>
  </div>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{tutor_name}}, {{student_name}} ({{student_email}}) se ha inscrito en {{course_name}}.'
WHERE template_key = 'ENROLLMENT_ACTIVE_TUTOR' AND language = 'es';

UPDATE public.email_templates SET
    subject   = 'Lista de espera — {{course_name}}',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#e67e22;">⏳ En lista de espera</h2>
  <p>Hola <strong>{{student_name}}</strong>,</p>
  <p>Has sido registrado en la lista de espera para <strong>{{course_name}}</strong>.</p>
  <p>Te notificaremos en cuanto haya una plaza disponible.</p>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{student_name}}, estás en lista de espera para {{course_name}}. Te avisaremos cuando haya plaza.'
WHERE template_key = 'ENROLLMENT_PENDING_STUDENT' AND language = 'es';

UPDATE public.email_templates SET
    subject   = 'Alumno en lista de espera — {{student_name}}',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#e67e22;">⏳ Alumno en lista de espera</h2>
  <p>Hola <strong>{{tutor_name}}</strong>,</p>
  <p><strong>{{student_name}}</strong> ha intentado inscribirse en <strong>{{course_name}}</strong>
     pero el grupo está completo.</p>
  <p>Ha sido añadido a la lista de espera.</p>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{tutor_name}}, {{student_name}} está en lista de espera para {{course_name}} (grupo lleno).'
WHERE template_key = 'ENROLLMENT_PENDING_TUTOR' AND language = 'es';

UPDATE public.email_templates SET
    subject   = 'Inscripción no procesada',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#c0392b;">⚠️ Inscripción no procesada</h2>
  <p>Hola <strong>{{tutor_name}}</strong>,</p>
  <p>La inscripción de <strong>{{student_name}}</strong> en <strong>{{course_name}}</strong>
     no ha podido ser procesada.</p>
  <p>Por favor, contacta con el administrador si el problema persiste.</p>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{tutor_name}}, la inscripción de {{student_name}} en {{course_name}} no pudo ser procesada.'
WHERE template_key = 'ENROLLMENT_REJECTED_TUTOR' AND language = 'es';

UPDATE public.email_templates SET
    subject   = '🎉 ¡Tu cuenta STC ha sido activada!',
    body_html = '<div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
  <h2 style="color:#2c5f2e;">🎉 ¡Cuenta activada!</h2>
  <p>Hola <strong>{{student_name}}</strong>,</p>
  <p>Ya eres Partner activo en el Sistema de Formación Continua de Zinzino.</p>
  <p>Accede a <a href="https://stc.stc-eu.com">stc.stc-eu.com</a> con tu cuenta Google
     para ver tu panel de alumno.</p>
  <p style="color:#666;font-size:.9em;">Sistema STC — Zinzino</p>
</div>',
    body_text = 'Hola {{student_name}}, ya eres Partner activo en STC. Accede en stc.stc-eu.com.'
WHERE template_key = 'GUEST_PROMOTED_PARTNER' AND language = 'es';


-- ─────────────────────────────────────────────────────────────────────────────
-- 5. fn_substitute_template
--    Pure function: replaces every {{key}} in p_template with the matching
--    value from p_context JSONB.  Missing keys are replaced with empty string.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_substitute_template(
    p_template TEXT,
    p_context  JSONB
)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $BODY$
DECLARE
    v_result TEXT := p_template;
    v_key    TEXT;
    v_value  TEXT;
BEGIN
    IF p_template IS NULL OR p_context IS NULL THEN
        RETURN p_template;
    END IF;

    FOR v_key, v_value IN
        SELECT k, v FROM jsonb_each_text(p_context) AS t(k, v)
    LOOP
        v_result := REPLACE(v_result, '{{' || v_key || '}}', COALESCE(v_value, ''));
    END LOOP;

    RETURN v_result;
END;
$BODY$;


-- ─────────────────────────────────────────────────────────────────────────────
-- 6. fn_prepare_notifications
--    Given an event_type and a context JSONB, returns one rendered row per
--    recipient (to_email, to_name, subject, body_html, body_text).
--    Node.js iterates these rows and calls sendEmail() for each one.
--
--    Example call:
--      SELECT * FROM fn_prepare_notifications('ENROLLMENT_ACTIVE', '{
--        "tutor_name":    "Pedro García",
--        "tutor_email":   "pedro@example.com",
--        "student_name":  "María López",
--        "student_email": "maria@example.com",
--        "course_name":   "STC Core"
--      }'::jsonb);
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_prepare_notifications(
    p_event_type TEXT,
    p_context    JSONB
)
RETURNS TABLE (
    to_email   TEXT,
    to_name    TEXT,
    subject    TEXT,
    body_html  TEXT,
    body_text  TEXT
)
LANGUAGE plpgsql
STABLE
AS $BODY$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            dr.email_field,
            dr.name_field,
            et.subject   AS raw_subject,
            et.body_html AS raw_html,
            et.body_text AS raw_text
        FROM   public.email_dispatch_rules dr
        JOIN   public.email_templates et
               ON  et.template_key = dr.template_key
               AND et.language     = dr.language
        WHERE  dr.event_type = p_event_type
          AND  dr.active     = true
          AND  et.active     = true
        ORDER BY dr.id
    LOOP
        to_email  := p_context ->> r.email_field;
        to_name   := p_context ->> r.name_field;
        subject   := public.fn_substitute_template(r.raw_subject, p_context);
        body_html := public.fn_substitute_template(r.raw_html,    p_context);
        body_text := public.fn_substitute_template(r.raw_text,    p_context);

        -- Skip rows where the email field resolves to null or empty
        IF to_email IS NOT NULL AND to_email <> '' THEN
            RETURN NEXT;
        END IF;
    END LOOP;
END;
$BODY$;


-- ─────────────────────────────────────────────────────────────────────────────
-- 7. GRANTS
-- ─────────────────────────────────────────────────────────────────────────────
GRANT SELECT, INSERT, UPDATE ON public.email_dispatch_rules TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_substitute_template(TEXT, JSONB)   TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_prepare_notifications(TEXT, JSONB)  TO "elfi1953_stc-eu";