BEGIN;
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  password TEXT NOT NULL,
  enabled BOOLEAN DEFAULT false,
  admin BOOLEAN DEFAULT false,
  pubkey TEXT UNIQUE NOT NULL,
  relays TEXT DEFAULT NULL,
  create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE  FUNCTION users_update_ts()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_ts = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_update_ts
    BEFORE UPDATE
    ON
        users
    FOR EACH ROW
EXECUTE PROCEDURE users_update_ts();

CREATE TABLE IF NOT EXISTS nip05s (
  id SERIAL PRIMARY KEY,
  owner_id INT NOT NULL,
  name TEXT UNIQUE NOT NULL,
  domain TEXT NOT NULL,
  create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT fk_owner FOREIGN KEY(owner_id) REFERENCES users(id),
  CONSTRAINT ck_name CHECK (name ~ '^[a-z0-9]*$')
);

CREATE  FUNCTION nip05s_update_ts()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_ts = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_nip05s_update_ts
    BEFORE UPDATE
    ON
        nip05s
    FOR EACH ROW
EXECUTE PROCEDURE nip05s_update_ts();

COMMIT;