<commit_before>
CREATE TYPE verification_status AS ENUM('verified', 'bounced', 'failed', 'unverified');
CREATE TYPE delivery_type AS ENUM('email', 'sms', 'push', 'voice', 'web');

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
DECLARE
  date TIMESTAMPTZ;
BEGIN
  date := now() AT TIME ZONE tz;
  RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
  RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS TEXT CHECK ( is_timezone( value ) );

-- TODO: not enforcing uniqueness on email, sms, voice on delevopment, enforce later
CREATE TABLE account (
  id              bigserial           PRIMARY KEY,
  external_id     bigint              UNIQUE NOT NULL,
  created         timestamp           NOT NULL DEFAULT NOW(),
  updated         timestamp           NOT NULL DEFAULT NOW(),
  name            text                NOT NULL,
  email           text                NULL,
  email_status    verification_status NOT NULL DEFAULT 'unverified',
  sms             text                NULL,
  sms_status      verification_status NOT NULL DEFAULT 'unverified',
  voice           text                NULL,
  voice_status    verification_status NOT NULL DEFAULT 'unverified',
  delivery        delivery_type[]     NULL,
  language        text                NULL,
  timezone        timezone            NOT NULL DEFAULT 'America/Toronto',
  active          boolean             NOT NULL DEFAULT true
  -- geo
  -- groups
  -- tags
);

CREATE TABLE template (
  id            bigserial           PRIMARY KEY,
  created       timestamp           NOT NULL DEFAULT NOW(),
  updated       timestamp           NOT NULL DEFAULT NOW(),
  name          text                NOT NULL,
  email         text                NULL,
  sms           text                NULL,
  voice         text                NULL,
  web           text                NULL,
  push          text                NULL
);

CREATE TABLE notification (
  id            bigserial           PRIMARY KEY,
  created       timestamp           NOT NULL DEFAULT NOW(),
  updated       timestamp           NOT NULL DEFAULT NOW(),
  by            delivery_type[]     NOT NULL,
  at            timestamp           NOT NULL,
  template_id   bigint              NOT NULL REFERENCES template(id),
  required_by   delivery_type[]     NULL,
  data          jsonb               NULL,
  sent          timestamp           NULL,
  status        text                NOT NULL DEFAULT 'waiting'
  -- user_user_locale<commit_msg>keep forgetting to change dir to root file when staging changes<commit_after>
CREATE TYPE verification_status AS ENUM('verified', 'bounced', 'failed', 'unverified');
CREATE TYPE delivery_type AS ENUM('email', 'sms', 'push', 'voice', 'web');
CREATE TYPE notification_status AS ENUM('new', 'failed', 'sent', 'processing');

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
DECLARE
  date TIMESTAMPTZ;
BEGIN
  date := now() AT TIME ZONE tz;
  RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
  RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS TEXT CHECK ( is_timezone( value ) );

-- TODO: not enforcing uniqueness on email, sms, voice on delevopment, enforce later
CREATE TABLE account (
  id              bigserial           PRIMARY KEY,
  external_id     bigint              UNIQUE NOT NULL,
  created         timestamp           NOT NULL DEFAULT NOW(),
  updated         timestamp           NOT NULL DEFAULT NOW(),
  name            text                NOT NULL,
  email           text                NULL,
  email_status    verification_status NOT NULL DEFAULT 'unverified',
  sms             text                NULL,
  sms_status      verification_status NOT NULL DEFAULT 'unverified',
  voice           text                NULL,
  voice_status    verification_status NOT NULL DEFAULT 'unverified',
  delivery        delivery_type[]     NULL,
  language        text                NULL,
  timezone        timezone            NOT NULL DEFAULT 'America/Toronto',
  active          boolean             NOT NULL DEFAULT true
  -- geo
  -- groups
  -- tags
);

CREATE TABLE template (
  id            bigserial           PRIMARY KEY,
  created       timestamp           NOT NULL DEFAULT NOW(),
  updated       timestamp           NOT NULL DEFAULT NOW(),
  name          text                NOT NULL,
  email         text                NULL,
  sms           text                NULL,
  voice         text                NULL,
  web           text                NULL,
  push          text                NULL
);

CREATE TABLE notification (
  id            bigserial           PRIMARY KEY,
  created       timestamp           NOT NULL DEFAULT NOW(),
  updated       timestamp           NOT NULL DEFAULT NOW(),
  by            delivery_type[]     NOT NULL,
  at            timestamp           NOT NULL,
  template_id   bigint              NOT NULL REFERENCES template(id),
  required_by   delivery_type[]     NULL,
  data          jsonb               NULL,
  sent          timestamp           NULL,
  status        notification_status NOT NULL DEFAULT 'new'::notification_status
  -- user_user_locale
{'fraction_non_alphanumeric': 0.05695338404525063, 'fraction_numerical': 0.0, 'mean_word_length': 1.6056910569105691, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1413971', 'n_tokens_mistral': 1305, 'n_tokens_neox': 1251, 'n_words': 483}