-- Migration 025: course_email_templates
-- Per-course template overrides. When present, take precedence over global email_templates.

CREATE TABLE public.course_email_templates (
  id           serial PRIMARY KEY,
  course_id    integer      NOT NULL REFERENCES public.courses(course_id) ON DELETE CASCADE,
  template_key varchar(50)  NOT NULL,
  language     varchar(5)   NOT NULL,
  subject      text         NOT NULL,
  body_html    text         NOT NULL,
  body_text    text         NOT NULL DEFAULT '',
  active       boolean      NOT NULL DEFAULT true,
  updated_at   timestamptz  NOT NULL DEFAULT now(),
  CONSTRAINT uq_course_email_template UNIQUE (course_id, template_key, language)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.course_email_templates TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.course_email_templates_id_seq TO "elfi1953_stc-eu";