-- auto populate the meta data schema

-- on create of user record - pull creation date and creator into dedicated column - rmb makes auto-populates these fields in the md fields
CREATE OR REPLACE FUNCTION set_creation_date_users()
RETURNS TRIGGER AS $$
BEGIN
  NEW.creation_date = to_timestamp(NEW.jsonb->'metaData'->>'createdDate', 'YYYY-MM-DD"T"HH24:MI:SS.MS');
  NEW.created_by = NEW.jsonb->'metaData'->>'createdByUserId';
  RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER set_creation_date_users_trig BEFORE INSERT ON myuniversity_mymodule.users FOR EACH ROW EXECUTE PROCEDURE  set_creation_date_users();

-- on update populate md fields from the creation date and creator fields
CREATE OR REPLACE FUNCTION set_creation_date_users_json()
RETURNS TRIGGER AS $$
DECLARE
  createdDate timestamp WITH TIME ZONE;
  createdBy text ;
  updatedDate timestamp WITH TIME ZONE;
  updatedBy text ;
  injectedId text;
BEGIN
  createdBy = NEW.created_by;
  createdDate = NEW.creation_date;
  updatedDate = NEW.jsonb->'metaData'->>'updatedDate';
  updatedBy = NEW.jsonb->'metaData'->>'updatedByUserId';
  
  if createdBy ISNULL then
    createdBy = 'undefined';
  end if;
  if updatedBy ISNULL then
    updatedBy = 'undefined';
  end if;
injectedId = '{"createdDate":"'||to_char(createdDate,'YYYY-MM-DD"T"HH24:MI:SS.MS')||'" , "createdByUserId":"'||createdBy||'", "updatedDate":"'||to_char(updatedDate,'YYYY-MM-DD"T"HH24:MI:SS.MSOF')||'" , "updatedByUserId":"'||updatedBy||'"}';
NEW.jsonb = jsonb_set(NEW.jsonb, '{metaData}' ,  injectedId::jsonb , false);
RETURN NEW;

END;
$$ language 'plpgsql';
CREATE TRIGGER set_creation_date_users_json_trig BEFORE UPDATE ON myuniversity_mymodule.users FOR EACH ROW EXECUTE PROCEDURE  set_creation_date_users_json();

-- --- end auto populate meta data schema ------------


GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myuniversity_mymodule TO myuniversity_mymodule;