-- Liefdesbrieven: schema
-- Uitgangspunten:
--   * leeftijden zijn voorkant, geboortedata zijn achterkant (zie src/lib/dates.js)
--   * fulltext op de profieltekst én op de brieven, met de Nederlandse config
--   * matching is symmetrisch en gebeurt in één query (zie server/queries.js)

drop table if exists schema_migrations;
drop table if exists password_resets;
drop table if exists search_terms;
drop table if exists farewells;
drop table if exists departures;
drop table if exists purchases;
drop table if exists signup_slots;
drop table if exists sessions;
drop table if exists measures;
drop table if exists warnings;
drop table if exists reports;
drop table if exists blocks;
drop table if exists favorites;
drop table if exists photos;
drop table if exists letters;
drop table if exists users;
drop function if exists oldest_birthdate(int);
drop function if exists youngest_birthdate(int);
drop function if exists distance_km(double precision, double precision, double precision, double precision);

-- Leeftijdsgrens → geboortedatumgrens, identiek aan agesToBirthRange() in de frontend:
--   minBirth (oudste toegestaan)  = vandaag - (aMax + 1) jaar + 1 dag
--   maxBirth (jongste toegestaan) = vandaag - aMin jaar
create function oldest_birthdate(age_max int) returns date
  language sql stable
  as $$ select (current_date - make_interval(years => age_max + 1) + interval '1 day')::date $$;

create function youngest_birthdate(age_min int) returns date
  language sql stable
  as $$ select (current_date - make_interval(years => age_min))::date $$;

-- Grootcirkelafstand (haversine) in km; geen PostGIS nodig
create function distance_km(lat1 double precision, lon1 double precision,
                            lat2 double precision, lon2 double precision) returns double precision
  language sql immutable
  as $$
    select 6371.0 * 2 * asin(sqrt(
      power(sin(radians(lat2 - lat1) / 2), 2) +
      cos(radians(lat1)) * cos(radians(lat2)) * power(sin(radians(lon2 - lon1) / 2), 2)
    ))
  $$;

create table users (
  id             bigint primary key generated always as identity,
  -- account (e-mail wordt door de server in kleine letters opgeslagen)
  email          text        not null unique check (email = lower(email) and email like '%@%'),
  password_hash  text        not null,   -- zie server/auth.js
  created_at     timestamptz not null default now(),
  -- AVG: wanneer de gebruiker uitdrukkelijk toestemming gaf voor de gevoelige gegevens
  -- (gender, trans, relatiestatus, zoekfilter). Null = nog niet (bestaande accounts van vóór
  -- de privacyverklaring) → de app vraagt het alsnog vóór verder gebruik.
  consent_at     timestamptz,
  -- identiteit
  name           text        not null check (length(name) between 1 and 40),
  gender         char(1)     not null check (gender in ('v', 'm', 'x')),
  trans          boolean     not null default false,   -- onafhankelijk van gender: "trans man"
  birthdate      date        not null check (birthdate <= current_date - interval '18 years'),
  bio            text        not null default '',
  -- relatiestatus: heeft deze gebruiker al een romantische relatie? (in te stellen bij Mijn profiel)
  relationship   text        not null default 'vrijgezel' check (relationship in ('vrijgezel', 'relatie')),
  -- Profiel tijdelijk verbergen (vinkje onder de profieltekst): dan verschijn je niet meer in
  -- Zoeken en kan niemand een eerste brief sturen. Lopende correspondenties gaan gewoon door —
  -- verbergen is even niet gevonden willen worden, niet vertrekken. Zie MUTUAL_MATCH in
  -- server/queries.js, waar dit samenvalt met de regel dat een leeg profiel niet verschijnt.
  hidden         boolean     not null default false,
  last_active    timestamptz not null default now(),
  -- Handhaving (zie scripts/reports.js). Geschorst = suspended_at gezet én suspended_until null
  -- (onbepaalde tijd) of nog in de toekomst. Een schorsing wist niets: inloggen wordt geweigerd
  -- met de reden erbij, de sessies vervallen, en de persoon verdwijnt uit Zoeken — de brieven die
  -- er al liggen blijven staan, ook bij de mensen die ze kregen.
  suspended_at    timestamptz,
  suspended_until timestamptz,
  suspend_reason  text        not null default '',
  -- locatie (optioneel)
  lat            double precision check (lat between -90 and 90),
  lon            double precision check (lon between -180 and 180),
  -- filter: wie deze gebruiker wil zien
  seek_genders   char(1)[]   not null default '{}',
  seek_relatie   boolean     not null default false,   -- ook mensen in een relatie zien? (vrijgezellen altijd)
  seek_trans     boolean     not null default false,   -- ook trans personen zien? Beschermt: wie trans is, verschijnt
                                                       -- alleen bij wie hier bewust voor openstaat
  seek_min_birth date        not null,   -- oudste toegestane geboortedatum
  seek_max_birth date        not null,   -- jongste toegestane geboortedatum
  seek_radius_km int         check (seek_radius_km > 0),   -- null = geen afstandsfilter
  -- fulltext-kolom, automatisch bijgehouden
  bio_tsv        tsvector    generated always as (to_tsvector('dutch', bio)) stored,
  check (seek_min_birth <= seek_max_birth),
  check ((lat is null) = (lon is null)),                       -- beide of geen
  check (seek_radius_km is null or lat is not null),           -- straal vereist een locatie
  check (suspended_until is null or suspended_at is not null)  -- een einddatum zonder schorsing bestaat niet
);

create index users_bio_fts   on users using gin (bio_tsv);
create index users_birthdate on users (birthdate);
create index users_gender    on users (gender);
create index users_lat_lon   on users (lat, lon);

-- Ingelogde sessies: de client bewaart het token, hier staat alleen de sha256 ervan
create table sessions (
  token_hash   text        primary key,
  user_id      bigint      not null references users (id) on delete cascade,
  created_at   timestamptz not null default now(),
  last_seen_at timestamptz not null default now()
);

create index sessions_user on sessions (user_id);

-- Wachtwoord vergeten: de mail bevat een link naar /wachtwoord op de webserver; hier staat de
-- sha256 van het token uit die link. Eén rij per persoon — opnieuw aanvragen vervangt de vorige
-- link, en dat is precies goed: er hoort er maar één te werken. De rij verdwijnt bij gebruik
-- (het token is eenmalig), bij verlopen (purgeExpired) en met het account (cascade).
create table password_resets (
  user_id    bigint      primary key references users (id) on delete cascade,
  token_hash text        not null unique,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table letters (
  id       bigint primary key generated always as identity,
  from_id  bigint      not null references users (id) on delete cascade,
  to_id    bigint      not null references users (id) on delete cascade,
  sent_at  timestamptz not null default now(),
  read_at  timestamptz,                             -- null = ontvanger heeft hem nog niet geopend
  body     text        not null check (length(body) >= 300),
  -- fulltext-kolom, automatisch bijgehouden: zoeken binnen één correspondentie. Dezelfde
  -- Nederlandse config als bij de profielteksten, dus 'boek' vindt ook 'boeken'.
  body_tsv tsvector    generated always as (to_tsvector('dutch', body)) stored,
  check (from_id <> to_id)
);

-- Postvak: alle brieven waar ik bij betrokken ben, gesorteerd op partner en datum
create index letters_from_to on letters (from_id, to_id, sent_at);
create index letters_to_from on letters (to_id, from_id, sent_at);
-- Ongelezen post per ontvanger
create index letters_unread on letters (to_id) where read_at is null;
-- Zoeken door het verleden van één correspondentie
create index letters_body_fts on letters using gin (body_tsv);

-- De foto bij een brief: hoogstens één, en de bytes staan hier, niet op schijf. Waarom in de database:
-- verwijderen cascadeert dan vanzelf mee met de brief en dus met het account (AVG-vergetelheid blijft
-- één DELETE), er is één back-up, en de server hoeft nergens naar te kunnen schrijven.
-- De app verkleint en hercodeert de foto vóór verzenden, dus hier komt nooit EXIF (gps!) binnen;
-- de server controleert daarnaast de magische bytes tegen de opgegeven mime (zie server/media.js).
-- `alt` is verplicht: het is zowel het bijschrift onder de foto als de alt-tekst voor schermlezers.
create table photos (
  id         bigint primary key generated always as identity,
  letter_id  bigint      not null unique references letters (id) on delete cascade,
  mime       text        not null check (mime in ('image/jpeg', 'image/png', 'image/webp')),
  bytes      bytea       not null check (length(bytes) between 1 and 1048576),   -- max 1 MB
  width      int         not null check (width between 1 and 10000),
  height     int         not null check (height between 1 and 10000),
  alt        text        not null check (length(alt) between 1 and 200),
  created_at timestamptz not null default now()
);

-- Favoriete correspondenten: persoonlijk en eenzijdig (de ander ziet er niets van).
-- Alleen zinvol voor mensen met wie je al brieven uitwisselt (de server controleert dat);
-- favorieten staan bovenaan in het postvak.
create table favorites (
  user_id    bigint      not null references users (id) on delete cascade,   -- wie markeert
  other_id   bigint      not null references users (id) on delete cascade,   -- wie gemarkeerd is
  created_at timestamptz not null default now(),
  primary key (user_id, other_id),
  check (user_id <> other_id)
);

-- Blokkades: stil en eenzijdig. Verbergt, wist niets: de correspondentie verdwijnt uit het
-- postvak van de blokkeerder en beiden zien elkaar niet meer in Zoeken; niemand kan de ander
-- nog schrijven. Deblokkeren (bij Mijn profiel) brengt alles terug zoals het was.
create table blocks (
  user_id    bigint      not null references users (id) on delete cascade,   -- wie blokkeert
  blocked_id bigint      not null references users (id) on delete cascade,   -- wie geblokkeerd is
  created_at timestamptz not null default now(),
  primary key (user_id, blocked_id),
  check (user_id <> blocked_id)
);
create index blocks_blocked on blocks (blocked_id);

-- Meldingen van misbruik: een persoon, of één brief van die persoon. Blokkeren is voor jezelf
-- (het verbergt), melden is voor de beheerder (die kan ingrijpen) — daarom bestaan ze naast elkaar.
-- Afhandelen gaat met `npm run reports` (er is geen beheerdersscherm in de app).
--
-- De drie sleutels vervallen bewust verschillend:
--   * reported_id  cascade  — is de gemelde persoon weg, dan valt er niets meer te beoordelen;
--   * reporter_id  set null — een melding mag niet verdwijnen doordat de melder zijn account
--                             opzegt; juist bij misbruik is dat precies wat er gebeurt. Wat
--                             overblijft is de melding zonder de naam van de melder;
--   * letter_id    set null — idem: de melding blijft leesbaar als de brief er niet meer is.
create table reports (
  id          bigint primary key generated always as identity,
  reporter_id bigint      references users (id) on delete set null,      -- null = melder bestaat niet meer
  reported_id bigint      not null references users (id) on delete cascade,
  letter_id   bigint      references letters (id) on delete set null,    -- null = de persoon, niet één brief
  reason      text        not null check (reason in
                ('minderjarig', 'intimidatie', 'seksueel', 'nep', 'oplichting', 'anders')),
  details     text        not null default '' check (length(details) <= 1000),
  created_at  timestamptz not null default now(),
  handled_at  timestamptz,                                              -- null = nog te bekijken
  check (reporter_id is null or reporter_id <> reported_id)
);

-- De werkvoorraad: openstaande meldingen, oudste eerst
create index reports_open     on reports (created_at) where handled_at is null;
-- "Hoe vaak is deze persoon al gemeld?" — de vraag die één melding pas betekenis geeft
create index reports_reported on reports (reported_id);
create index reports_reporter on reports (reporter_id);

-- Waarschuwingen: de lichtste maatregel, en de enige die de gebruiker te zien krijgt. Bij het
-- openen van de app staat een onbeantwoorde waarschuwing vóór alles; wegklikken zet
-- acknowledged_at en is meteen het bewijs dat hij gelezen is. Meerdere mogen: een tweede
-- waarschuwing over hetzelfde is een feit dat ertoe doet.
create table warnings (
  id              bigint primary key generated always as identity,
  user_id         bigint      not null references users (id) on delete cascade,
  text            text        not null check (length(text) between 1 and 1000),
  created_at      timestamptz not null default now(),
  acknowledged_at timestamptz
);

create index warnings_pending on warnings (user_id, created_at) where acknowledged_at is null;

-- Genomen maatregelen: het logboek van wat er ná een melding gebeurd is. Zonder dit is "we kijken
-- ernaar" niet te controleren en is een tweede overtreding niet van een eerste te onderscheiden.
-- Beide sleutels vervallen naar null: is het account weg, dan blijft staan dát er is opgetreden,
-- zonder nog te zeggen tegen wie — er valt dan niets meer te handhaven en dus niets meer te bewaren.
create table measures (
  id         bigint primary key generated always as identity,
  user_id    bigint      references users (id) on delete set null,
  report_id  bigint      references reports (id) on delete set null,
  kind       text        not null check (kind in
               ('waarschuwing', 'brief_verwijderd', 'schorsing', 'opheffing', 'account_verwijderd')),
  note       text        not null default '',
  created_at timestamptz not null default now()
);

create index measures_user on measures (user_id, created_at);

-- ---------- Wat een plek kost ----------
--
-- De gratis bak: hoeveel accounts er zonder betaling zijn aangemaakt. Eén rij, en met opzet een
-- teller in plaats van `select count(*) from users` — een account dat weer verwijderd wordt geeft
-- zijn plek niet terug. Zou het dat wel doen, dan is "aanmaken, wissen, opnieuw" de goedkoopste
-- manier om de bak leeg te halen, en dat is precies wat hier niet moet kunnen.
--
-- De bovengrens staat niet hier maar in src/constants.js (FREE_ACCOUNTS): dan is hij te verzetten
-- zonder de database aan te raken, en dat is bij dit schema het verschil tussen een herstart en
-- alles opnieuw opzetten. Ophogen mag altijd, verlagen sluit de deur voor wie nog niet binnen is.
create table signup_slots (
  singleton boolean primary key default true check (singleton),
  granted   int     not null default 0 check (granted >= 0)
);
insert into signup_slots default values;

-- Betaalde plekken. De betaling zelf gaat volledig buiten ons om: Google is de verkoper (straks
-- Apple erbij), en wij zien geen naam, geen kaartnummer en geen bedrag — alleen een bonnetje dat de
-- server bij de store natrekt (server/billing.js). Wat hier staat is dus geen betaalgegeven maar
-- het bewijs dát deze plek betaald is, plus de sleutel die verhindert dat één bon twee accounts
-- oplevert: `unique (provider, token)`.
--
-- user_id vervalt naar null en cascadeert met opzet níet. Zou de rij meeverdwijnen met het account,
-- dan is "account wissen en opnieuw registreren" een gratis tweede account op dezelfde aankoop.
-- Wat er na een verwijdering overblijft is een bon zonder persoon: voor ons een opaak token.
create table purchases (
  id         bigint primary key generated always as identity,
  provider   text        not null check (provider in ('google', 'apple')),
  token      text        not null,   -- Play: purchaseToken; App Store: transaction id
  product_id text        not null,
  order_id   text        not null default '',   -- het bonnummer van de store, voor terugbetalingen
  user_id    bigint      references users (id) on delete set null,
  created_at timestamptz not null default now(),
  unique (provider, token)
);

create index purchases_user on purchases (user_id);

-- ---------- Vertrek ----------
--
-- Een account dat weggaat neemt alles mee: profiel, brieven, blokkades, meldingen. Dat is precies
-- wat de AVG vraagt en het is goed zo — maar het betekent ook dat élke telling over het verleden
-- stilletjes krimpt, en altijd dezelfde kant op. Wie alleen `users` telt ziet een gemeenschap waar
-- niemand ooit is weggegaan.
--
-- Dit is de enige rij die een vertrek overleeft, en ze wijst niet naar een persoon: geen id, geen
-- naam, geen gender, geen leeftijd, geen tekst. Wat er staat is een datum, hoe lang iemand meedeed,
-- waarom hij weg is, en één bit: had hij ooit een brief geschreven? Dat laatste scheidt "geprobeerd
-- en niets gevonden" van "nooit begonnen", en dat is het verschil waar een gemeenschap iets aan kan
-- doen. Met opzet géén vreemde sleutel naar users: het hele punt is dat deze rij blijft staan.
--
-- Waarom niet afgeleid uit signup_slots.granted: die teller is een poort, geen grootboek. Hij stopt
-- bij FREE_ACCOUNTS en zegt daarna niets meer, en hij telt alleen gratis plekken. Een verschil
-- tussen die teller en het ledental is dus geen verloopcijfer maar een toevalligheid.
create table departures (
  id          bigint primary key generated always as identity,
  at          timestamptz not null default now(),
  days_member int         not null check (days_member >= 0),
  reason      text        not null check (reason in ('zelf', 'slapend', 'maatregel')),
  wrote       boolean     not null   -- had deze persoon ooit een brief verstuurd?
);

create index departures_at on departures (at);

-- De brief die iemand bij zijn vertrek achterlaat. Wie weggaat mag de beheerder schrijven: wat hij
-- hier gevonden heeft, of wat er mis was. Vrijwillig, en het houdt niemand tegen — de knop wist het
-- account met of zonder brief.
--
-- Deze rij overleeft het wissen, net als `departures`, en om dezelfde reden wijst ze naar niemand:
-- geen id, geen naam, geen e-mailadres, geen leeftijd, geen gender. Wat er staat is de tekst en
-- wanneer hij geschreven is, en dat is met opzet alles — een afscheidsbrief mét afzender zou een
-- accountverwijdering zijn die de persoon niet echt laat gaan, en een klacht die je alleen durft in
-- te dienen als je naam er niet bij staat is precies de klacht die je wilt horen.
--
-- De prijs staat er ook bij: antwoorden kan niet. Wie een antwoord wil, mailt (de app zegt dat erbij).
-- Lezen doet de beheerder met `npm run reports -- --afscheid`.
create table farewells (
  id   bigint      primary key generated always as identity,
  at   timestamptz not null default now(),
  body text        not null check (length(body) between 1 and 5000)
);

create index farewells_at on farewells (at);

-- ---------- Waar op gezocht wordt ----------
--
-- Wat mensen in Zoeken intypen is het enige signaal over wat ze hopen te vinden dat niet uit de
-- profielen zelf komt: een woord dat vaak gezocht wordt en zelden in een profieltekst staat, is een
-- gat in de gemeenschap. Daarvoor is dit er.
--
-- Wat hier níet staat is minstens zo belangrijk. Geen gebruiker, geen sessie, geen tijdstip fijner
-- dan de dag, en niet de zoekopdracht zelf maar de losse woorden eruit (`toTerms()` in
-- server/queries.js, dezelfde die naar Postgres gaat). Dat is met opzet, en het is precies wat een
-- zoekgeschiedenis onderscheidt van een telling: wie "wandelen" zoekt telt hier op bij iedereen die
-- dat ook zocht, en "marieke van der berg" valt uiteen in vier woorden die elk bij duizend anderen
-- op de hoop vallen. Er is geen rij die iets over één persoon zegt, en dus ook niets te exporteren,
-- te verwijderen of te vorderen.
--
-- Eén regel per zoekintentie, niet per toetsaanslag: de server houdt de laatste zoekopdracht per
-- gebruiker even vast en telt hem pas als er iets anders komt of als het stil blijft (zie
-- server/search-log.js). Anders telt "dostojevski" hier als do, dos, dost, dosto.
create table search_terms (
  term  text not null check (length(term) between 2 and 40),
  day   date not null default current_date,
  count int  not null default 1 check (count > 0),
  primary key (term, day)
);

create index search_terms_day on search_terms (day);

-- ---------- Migraties ----------
--
-- Sinds de productie-release (24-08-2026) is de database niet meer wegwerpbaar. Dit bestand blijft
-- de waarheid voor een vérse installatie, maar een bestáánde database verandert alleen nog via
-- db/migrations/ (npm run db:migrate — de spelregels staan in db/migrate.js). Elke wijziging
-- bestaat dus twee keer: als migratie voor de database die er al staat, en verwerkt in dit bestand
-- voor wie opnieuw begint. Na een verse opbouw registreert db/setup.js alle migraties hier als
-- uitgevoerd, want dit bestand ís dan al de nieuwste stand.
create table schema_migrations (
  filename   text        primary key,
  applied_at timestamptz not null default now()
);