CREATE TABLE IF NOT EXISTS glyph_users (
  name TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  password TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  disabled BOOLEAN NOT NULL,
  picture TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS glyph_groups (
  name TEXT PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS glyph_users_groups (
  user_name TEXT NOT NULL,
  group_name TEXT NOT NULL,
  PRIMARY KEY (user_name, group_name),
  FOREIGN KEY (user_name) REFERENCES glyph_users(name) ON DELETE CASCADE,
  FOREIGN KEY (group_name) REFERENCES glyph_groups(name) ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION glyph_update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER glyph_update_users_timestamp
BEFORE UPDATE ON glyph_users
FOR EACH ROW
EXECUTE FUNCTION glyph_update_timestamp();

CREATE OR REPLACE TRIGGER glyph_update_groups_timestamp
BEFORE UPDATE ON glyph_groups
FOR EACH ROW
EXECUTE FUNCTION glyph_update_timestamp();

CREATE OR REPLACE FUNCTION glyph_update_users_groups_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE glyph_users SET updated_at = NOW() WHERE name = NEW.user_name;
  UPDATE glyph_groups SET updated_at = NOW() WHERE name = NEW.group_name;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER glyph_update_users_groups_timestamp
AFTER INSERT OR DELETE ON glyph_users_groups
FOR EACH ROW
EXECUTE FUNCTION glyph_update_users_groups_timestamp();