CREATE extension IF NOT EXISTS pgcrypto;
SET TIMEZONE='UTC';

-- AuthN
--
--DROP TABLE IF EXISTS "authn";
CREATE TABLE IF NOT EXISTS "authn" (
    "id" TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    "ppid" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "verified_at" TIMESTAMPTZ NOT NULL DEFAULT ('0001-01-01 00:00:00' AT TIME ZONE 'UTC'),
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
    "updated_at" TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
    "deleted_at" TIMESTAMPTZ NOT NULL DEFAULT ('0001-01-01 00:00:00' AT TIME ZONE 'UTC')
);
--CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_ppid" ON "authn" ("ppid");
CREATE INDEX IF NOT EXISTS "idx_ppid" ON "authn" ("ppid");
CREATE INDEX IF NOT EXISTS "idx_email" ON "authn" ("email");

-- Events
--
--DROP TABLE IF EXISTS "events";
CREATE TABLE IF NOT EXISTS "events" (
    "id" TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    "action" TEXT NOT NULL,
    "table" TEXT NOT NULL,
    "record" TEXT NOT NULL,
    "by" TEXT NOT NULL,
    "at" TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')
);
--CREATE INDEX CONCURRENTLY IF NOT EXISTS "idx_record" ON "events" ("record");
CREATE INDEX IF NOT EXISTS "idx_record" ON "events" ("record");
CREATE INDEX IF NOT EXISTS "idx_by" ON "events" ("by");