-- Additive: keep the existing template catalog and all existing mail behavior.
ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS match_tags JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE email_templates ADD COLUMN IF NOT EXISTS case_request_id UUID;
CREATE UNIQUE INDEX IF NOT EXISTS email_templates_case_request_idx ON email_templates(case_request_id) WHERE case_request_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS email_template_attachments (
 id SERIAL PRIMARY KEY,
 template_id INTEGER NOT NULL REFERENCES email_templates(id) ON DELETE CASCADE,
 filename TEXT NOT NULL,
 content_type TEXT NOT NULL,
 content BYTEA NOT NULL,
 size_bytes INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS email_template_attachments_template_idx ON email_template_attachments(template_id);