-- OpenTelemetry `traceparent` span reference encoded as bytes.
-- The length is 26 bytes:
-- - 16 bytes for the u128 trace id
-- -  8 bytes for the u64 span id
-- -  1 byte for the flags
-- -  1 byte for the `is_remote` bool
-- todo: provide an impl using a composite type
create domain opentelemetry_trace_parent_bytes as bytea check (length(value) = 26);

alter table job
  add column producer_span opentelemetry_trace_parent_bytes null;

CREATE DOMAIN user_link_id AS UUID;
CREATE DOMAIN user_link_proof_id AS UUID;
CREATE DOMAIN outbound_email_id AS UUID;
CREATE DOMAIN outbound_email_request_id AS UUID;
CREATE DOMAIN idempotency_key AS UUID;
CREATE DOMAIN discord_user_id AS varchar(256);
CREATE DOMAIN gitlab_user_id AS varchar(256);
create domain digest_sha2_256 as bytea check (length(value) = 32);
create domain digest_sha3_256 as bytea check (length(value) = 32);
CREATE TYPE user_link_type AS ENUM ('Discord', 'Email', 'Gitlab');
CREATE TYPE user_link_visibility AS ENUM ('Private', 'Friend', 'Public');
CREATE TYPE email_payload_kind AS ENUM ('Marktwin', 'ResetPassword', 'VerifyEmail');
CREATE TYPE email_delivery_status AS ENUM ('Pending', 'Cancelled', 'Sent', 'Acknowledged');
CREATE TYPE email_request_status AS ENUM ('Pending', 'Cancelled', 'Timeout', 'Ok', 'Error');

CREATE TABLE gitlab_user (
  "gitlab_user_id" gitlab_user_id not null,
  primary key (gitlab_user_id)
);

CREATE TABLE discord_user (
  "discord_user_id" discord_user_id not null,
  primary key (discord_user_id)
);

CREATE TABLE user_link (
  "user_link_id" user_link_id not null,
  "period" period_lower not null,
  "user_id" user_id not null,
  "created_by" user_id not null,
  "deleted_by" user_id null,
  "user_link_type" user_link_type not null,
  "email_address_hash" email_address_hash null,
  "discord_user_id" discord_user_id null,
  "gitlab_user_id" gitlab_user_id null,
  primary key (user_link_id),
  constraint is_discord__ck check ((user_link_type = 'Discord') = (discord_user_id is not null)),
  constraint is_email__ck check ((user_link_type = 'Email') = (email_address_hash is not null)),
  constraint is_gitlab__ck check ((user_link_type = 'Gitlab') = (gitlab_user_id is not null)),
  constraint deleted_by__ck check ((deleted_by is not null) = (upper(period) is not null)),
  exclude using gist (period WITH &&, discord_user_id with =) where (user_link_type = 'Discord'),
  exclude using gist (period WITH &&, email_address_hash with =) where (user_link_type = 'Email'),
  exclude using gist (period WITH &&, gitlab_user_id with =) where (user_link_type = 'Gitlab'),
  constraint user_link__user__fk foreign key (user_id) references users(user_id) on delete restrict on update cascade,
  constraint user_link__created_by__fk foreign key (created_by) references users(user_id) on delete restrict on update cascade,
  constraint user_link__deleted_by__fk foreign key (deleted_by) references users(user_id) on delete restrict on update cascade,
  constraint user_link__email__fk foreign key (email_address_hash) references email_addresses(_hash) match simple on delete restrict on update cascade,
  constraint user_link__discord__fk foreign key (discord_user_id) references discord_user(discord_user_id) match simple on delete restrict on update cascade,
  constraint user_link__gitlab__fk foreign key (gitlab_user_id) references gitlab_user(gitlab_user_id) match simple on delete restrict on update cascade
);

-- rank for sorting
--
-- links are sorted by (rank, user_link_id)
CREATE TABLE user_link_rank_history (
  "user_link_id" user_link_id not null,
  "period" period_lower not null,
  "created_by" user_id not null,
  "deleted_by" user_id null,
  "rank" u16 not null,
  primary key (user_link_id, period),
  constraint deleted_by__ck check ((deleted_by is not null) = (upper(period) is not null)),
  constraint user_link_rank__created_by__fk foreign key (created_by) references users(user_id) on delete restrict on update cascade,
  constraint user_link_rank__deleted_by__fk foreign key (deleted_by) references users(user_id) on delete restrict on update cascade
);

-- link visibility
CREATE TABLE user_link_visibility_history (
  "user_link_id" user_link_id not null,
  "period" period_lower not null,
  "created_by" user_id not null,
  "deleted_by" user_id null,
  "visibility" user_link_visibility not null,
  primary key (user_link_id, period),
  constraint deleted_by__ck check ((deleted_by is not null) = (upper(period) is not null)),
  constraint user_link_visibility__created_by__fk foreign key (created_by) references users(user_id) on delete restrict on update cascade,
  constraint user_link_visibility__deleted_by__fk foreign key (deleted_by) references users(user_id) on delete restrict on update cascade
);

-- Outbound transactional email with a single recipient
--
-- rows indicate emails we want to send; it may not be delivered yet.
CREATE TABLE outbound_email (
  -- primary key for this email
  "outbound_email_id" outbound_email_id not null,
  -- key used for deduplication
  "idempotency_key" idempotency_key not null,
  -- user who sent the email
  "created_by" user_id not null,
  -- When this row was created (email ready for submission)
  "submitted_at" instant not null,
  -- If the email still not delivered after deadline, abort
  "deadline" instant not null,
  -- Usually `support@eternaltwin.org`
  "sender" email_address_hash not null,
  -- Recipient email
  "recipient" email_address_hash not null,
  -- payload kind / email template
  "payload_kind" email_payload_kind not null,
  -- email template version
  "payload_version" u8 not null,
  -- encrypted email payload
  "payload" bytea not null,
  "text_size" u16 not null,
  "text_sha2_256" digest_sha2_256 not null,
  "text_sha3_256" digest_sha3_256 not null,
  "html_size" u16 not null,
  "html_sha2_256" digest_sha2_256 not null,
  "html_sha3_256" digest_sha3_256 not null,
  -- overall delivery status across the different requests
  "delivery_status" email_delivery_status not null,
  -- when the next request submission attempt is scheduled
  "next_request_at" instant null,
  -- when the recipient read the email for the first time
  "read_at" instant null,
  primary key (outbound_email_id),
  constraint outboud_email__idempotency_key__uk unique (idempotency_key),
  constraint deadline_ck check (submitted_at < deadline),
  constraint next_request_at_ck check (submitted_at <= next_request_at and next_request_at < deadline),
  constraint outboud_email__created_by__fk foreign key (created_by) references users(user_id) on delete restrict on update cascade,
  constraint outboud_email__sender__fk foreign key (sender) references email_addresses(_hash) on delete restrict on update cascade,
  constraint outboud_email__recipient__fk foreign key (recipient) references email_addresses(_hash) on delete restrict on update cascade
);

-- -- email delivery priority. A higher value indicates higher priority.
-- CREATE TABLE outbound_email_priority_history (
--   "outbound_email_id" outbound_email_id not null,
--   "period" period_lower NOT NULL,
--   "priority" i8 not null default 0
-- );

-- Outbound email requests
--
-- Requests are handled as following:
-- 1. insert DB row in the table, commit
-- 2. send email request
-- 3. store result, commit
--
-- This ensures that an email is sent only if we add the row (and ensures there's no spam)
CREATE TABLE outbound_email_request (
  "outbound_email_request_id" outbound_email_request_id not null,
  -- email to send
  "outbound_email_id" outbound_email_id not null,
  -- the upper is always non-null
  -- if the state is pending, the request did not complete yet and it is a timeout
  -- else, it is the completion time
  "period" period_lower NOT NULL CHECK (not upper_inf(period)),
  "status" email_request_status not null,
  -- if `result` is not null, format version
  "result_version" u8 null,
  -- encrypted result if relevant
  "result" bytea null,
  primary key (outbound_email_request_id),
  exclude using gist (outbound_email_id with =, period with &&),
  constraint result_ck check (result is null OR (result is not null and result_version is not null)),
  constraint outbound_email_request__outbound_email__fk foreign key (outbound_email_id) references outbound_email(outbound_email_id) on delete restrict on update restrict
);

CREATE TABLE user_link_proof_manual (
  "user_link_proof_id" url NOT NULL,
  "user_link_id" user_id not null,
  "period" period_lower not null,
  "created_by" user_id not null,
  "deleted_by" user_id null
);

CREATE TABLE user_link_proof_email (
  "user_link_proof_id" url NOT NULL,
  "user_link_id" user_id not null,
  "period" period_lower not null,
  "created_by" user_id not null,
  "outbound_email_id" outbound_email_id not null,
  "deleted_by" user_id null
);