liefdesbrieven

-- Promotieteam: de vrienden die vrijwillig mond-tot-mondreclame maken.
--
-- Eigen database, met opzet los van de app. Het schema van de app wordt bij elke wijziging
-- opnieuw aangemaakt en zijn gegevens zijn wegwerpbaar (zie db/setup.js); deze lijst is dat
-- niet. Daarom staat er hier ook nergens een `drop table`: dit bestand mag zo vaak uitgevoerd
-- worden als je wilt en raakt nooit een bestaande rij aan.
--
-- Er is geen quotum en geen controle. Dit schema telt daarom niets: geen aanbrengcodes, geen
-- verwijzing naar de gebruikers van de app, geen score per persoon. Zou zoiets er staan, dan
-- gaat iemand ernaar kijken, en dan is het alsnog een quotum. Het beantwoordt één vraag:
-- wie doet er mee, en hoe bereik ik diegene als het feest er is.

create table if not exists promoters (
  id        bigint primary key generated always as identity,
  name      text not null check (length(trim(name)) between 1 and 80),
  -- Contact: e-mail, telefoon, of allebei. Minstens één van de twee, want zonder contact kun
  -- je iemand niet uitnodigen en is de rij een naam zonder feest.
  email     text check (email is null or (email = lower(email) and email like '%@%')),
  phone     text check (phone is null or length(trim(phone)) > 0),
  joined_on date not null default current_date,
  -- Gestopt. De rij blijft staan: "wie was er vorig jaar bij" blijft zo te beantwoorden, en
  -- wie weer meedoet is één klik terug. Echt weg wil hebben kan ook — dan wordt de rij gewist.
  left_on   date,
  note      text not null default '',
  check (email is not null or phone is not null),
  check (left_on is null or left_on >= joined_on)
);

create index if not exists promoters_name on promoters (lower(name));

/*
 * Vanaf hier de post. Deze database blijft staan (anders dan die van de app), dus wat er later
 * bij komt, komt er met `alter ... if not exists` bij: dit bestand blijft één keer draaien én
 * herhaalbaar, zonder een migratiestapel en zonder ooit een naam te wissen.
 */
alter table promoters add column if not exists welcomed_at timestamptz;   -- wanneer de welkomstmail eruit ging
/*
 * Gezet als het vertrek van de afmeldknop in de mail kwam, en niet van de knop Gestopt hier. Dat
 * onderscheid is nodig omdat Postmark op zijn afmeldpagina meteen een "toch weer aanmelden" biedt
 * voor wie zich vergist: verdwijnt iemand daardoor weer van hun afmeldlijst, dan hoort hij hier
 * ook terug te komen. Wie jij met de hand op Gestopt zet, staat nooit op die lijst en wordt door
 * die terugweg dus met rust gelaten.
 */
alter table promoters add column if not exists unsubscribed_at timestamptz;
/*
 * Hier stond een `unsub_token` voor een eigen afmeldlink. Die is er niet meer: bij een
 * broadcast-stream doet Postmark het afmelden zelf en is dat niet uit te zetten zonder hun
 * toestemming voor "custom unsubscribe handling". Wie afmeldt komt op hun onderdrukkingslijst, en
 * server/promotie.js haalt die op en zet diezelfde persoon hier op `left_on`. Eén afmeldweg, en
 * de kolom hoort dan ook weg te zijn in plaats van ongebruikt te blijven staan.
 */
alter table promoters drop column if exists unsub_token;

/*
 * De vaste tekst van de welkomstmail: één rij, te bewerken op /promotieteam. Hier en niet in de
 * repository, want de repository is openbaar en dit zijn jouw woorden aan je vrienden — en omdat
 * schaven aan een zin geen uitrol hoort te kosten. `{naam}` wordt de aanhef.
 */
create table if not exists welcome (
  singleton boolean primary key default true check (singleton),
  subject   text not null default 'Welkom bij het promotieteam',
  body      text not null default
'Hoi {naam},

Fijn dat je meedoet. Je hoeft niets bij te houden en er is geen quotum — vertel het gewoon door
aan wie je denkt dat er iets aan heeft.

Als dank geef ik een feestje. Je hoort van me wanneer.

Groet,
Bram'
);
insert into welcome default values on conflict do nothing;

/*
 * Wat er naar de groep is gegaan. Geen prestatiemeting van wie dan ook — het is het doorslagvel:
 * wat schreef ik, wanneer, aan hoeveel mensen, en bij wie liep het mis.
 */
create table if not exists mailings (
  id      bigint primary key generated always as identity,
  subject text        not null,
  body    text        not null,
  sent_at timestamptz not null default now(),
  aantal  int         not null default 0,   -- hoeveel er daadwerkelijk uitgingen
  mislukt text        not null default ''   -- adressen die Postmark weigerde, met de reden
);