text: -- Your SQL goes here
  CREATE TABLE users (
      id BYTEA PRIMARY KEY,
      name VARCHAR(32) NOT NULL,
      avatar VARCHAR(255) NOT NULL,
      primary_email BYTEA NOT NULL,
      password BYTEA NOT NULL
  );
  
  -- Add case insensitive unique name
  CREATE UNIQUE INDEX name_idx ON users ((lower(name)));
  
  
  CREATE TABLE tokens (
      id BYTEA PRIMARY KEY,
      token BYTEA NOT NULL,
      user_id BYTEA NOT NULL,
      name VARCHAR(32) NOT NULL,
      permissions SMALLINT NOT NULL,
      expire TIMESTAMP WITHOUT TIME ZONE NOT NULL
  );
  
  
  CREATE TABLE apps (
      id BYTEA PRIMARY KEY,
      owner BYTEA NOT NULL,
      name VARCHAR(32) NOT NULL,
      avatar VARCHAR(255) NOT NULL,
      description VARCHAR(255) NOT NULL,
      redirect_uri VARCHAR(255) NOT NULL,
      homepage VARCHAR(255) NOT NULL
  );
  
  
  CREATE TABLE app_tokens (
      id BYTEA PRIMARY KEY,
      token BYTEA NOT NULL,
      app_id BYTEA NOT NULL,
      name VARCHAR(32) NOT NULL,
      permissions SMALLINT NOT NULL
  );
  
  
  CREATE TABLE access_codes (
      id BYTEA PRIMARY KEY,
      app_id BYTEA NOT NULL,
      token_id BYTEA NOT NULL 
  );
  
  
  CREATE TABLE emails (
      id BYTEA PRIMARY KEY,
      owner BYTEA NOT NULL,
      email VARCHAR(255) NOT NULL,
      verification BYTEA NULL
  );
  
  
  -- Add case insensitive unique email
  CREATE UNIQUE INDEX email_idx ON emails ((lower(email)));
  
  
  -- FOREIGN KEYS
  ALTER TABLE users ADD FOREIGN KEY(primary_email) REFERENCES emails(id);
  -- ALTER TABLE emails ADD FOREIGN KEY(owner) REFERENCES users(id) ON DELETE CASCADE;
  ALTER TABLE access_codes ADD FOREIGN KEY(app_id) REFERENCES apps (id) ON DELETE CASCADE;
  ALTER TABLE access_codes ADD FOREIGN KEY(token_id) REFERENCES tokens (id) ON DELETE CASCADE;
  ALTER TABLE app_tokens ADD FOREIGN KEY(app_id) REFERENCES apps (id) ON DELETE CASCADE;
  ALTER TABLE apps ADD FOREIGN KEY(owner) REFERENCES users(id) ON DELETE CASCADE;
  ALTER TABLE tokens ADD FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE;
  
  
  -- DELETE EMAILS ON USER DELETE
  CREATE FUNCTION delete_email() RETURNS TRIGGER AS $_$
  BEGIN
      DELETE FROM emails WHERE owner = OLD.id;
      RETURN OLD;
  END $_$ LANGUAGE 'plpgsql';
  
  CREATE TRIGGER on_delete_user 
  BEFORE DELETE ON users 
  FOR EACH ROW 
  EXECUTE PROCEDURE delete_email();
  
  
  CREATE FUNCTION clean_account_db () 
  RETURNS void 
  AS $$
  BEGIN
      DELETE FROM tokens WHERE expire < (timezone('UTC', CURRENT_TIMESTAMP(0)) ::TIMESTAMP);
  END;
  $$ LANGUAGE plpgsql;
  