-- trigger function to set timestamps
CREATE OR REPLACE FUNCTION ax_utils.tg__timestamps() RETURNS trigger AS $$
BEGIN
  NEW.created_date = (CASE WHEN TG_OP = 'INSERT' THEN (now() at time zone 'utc') ELSE OLD.created_date END);
  NEW.updated_date = (CASE WHEN TG_OP = 'UPDATE' AND OLD.updated_date  >= (now() at time zone 'utc') THEN OLD.updated_date  + interval '1 millisecond' ELSE (now() at time zone 'utc') END);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql volatile SET search_path TO pg_catalog, public, pg_temp;
COMMENT ON FUNCTION ax_utils.tg__timestamps() IS
  E'This trigger should be called on all tables with created_date , updated_date  - it ensures that they cannot be manipulated and that updated_date  will always be larger than the previous updated_date .';

-- trigger function to set created_user/updated_user from settings
-- mosaic.auth.subject_name is added by PostGraphile to the pool context. If a pool
-- is manually created the settings must be supplied as well.
CREATE OR REPLACE FUNCTION ax_utils.tg__username() RETURNS trigger AS $$
DECLARE
	username text = pg_catalog.current_setting('mosaic.auth.subject_name', true);
BEGIN
  if username IS NULL OR username::char(5) = ''::char(5) then
  	RETURN NEW;
  end if;
  
  NEW.created_user = (CASE WHEN TG_OP = 'INSERT' THEN username ELSE OLD.created_user END);
  NEW.updated_user = (CASE WHEN TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN username ELSE OLD.updated_user END);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql volatile SET search_path TO pg_catalog, public, pg_temp;

-- Trigger function to set tenant_id and environment_id from settings and to 
-- prevent accidental cross-tenant/environment updates. mosaic.tenant_id and 
-- mosaic.environment_id are added by PostGraphile to the pool context. If a 
-- pool is manually created the settings must be supplied as well. The only 
-- exception are superusers who can manually set them for e.g. local 
-- development.
-- IMPORTANT: UPDATE operations made by db owner must explicitly specify 
--    tenant_id / environment_id in a WHERE condition. If UPDATE or INSERT 
--    would affect/create rows for a tenant / environment that does not match 
--    settings then an exception will occur.
-- Usage:
-- SELECT set_config('mosaic.tenant_id', '00000000-0000-0000-0000-000000000000', TRUE);
-- SELECT set_config('mosaic.environment_id', '00000000-0000-0000-0000-000000000000', TRUE);
-- or
-- SELECT set_config('mosaic.ignore_multitenancy', 'on', TRUE); -- use when you want to apply migration for all rows in a table without updating tenant/environment ids
CREATE OR REPLACE FUNCTION ax_utils.tg__tenant_environment() RETURNS TRIGGER AS $$
DECLARE
	tenant_id text = current_setting('mosaic.tenant_id', TRUE);
  environment_id text = current_setting('mosaic.environment_id', TRUE);
  is_superuser text = current_setting('is_superuser', TRUE);
  ignore_multitenancy text = current_setting('mosaic.ignore_multitenancy', TRUE);
BEGIN
  IF tenant_id <> '' THEN   -- Not null and not empty
    IF NEW.tenant_id::TEXT <> 'ffffffff-ffff-ffff-ffff-ffffffffffff' AND NEW.tenant_id::TEXT <> tenant_id THEN
      perform ax_utils.raise_error('The "mosaic.tenant_id" set via set_config must match the updated/inserted row.', 'TENAN');
    END IF;
    NEW.tenant_id = tenant_id;
  ELSIF is_superuser <> 'on' AND ignore_multitenancy <> 'on' THEN
    perform ax_utils.raise_error('The "mosaic.tenant_id" must be set via set_config value.', 'TENAN');
  END IF;
  IF environment_id <> '' THEN   -- Not null and not empty
    IF NEW.environment_id::TEXT <> 'ffffffff-ffff-ffff-ffff-ffffffffffff' AND NEW.environment_id::TEXT <> environment_id THEN
      perform ax_utils.raise_error('The "mosaic.environment_id" set via set_config must match the updated/inserted row.', 'ENVVV');
    END IF;
    NEW.environment_id = environment_id;
  ELSIF is_superuser <> 'on' AND ignore_multitenancy <> 'on' THEN
    perform ax_utils.raise_error('The "mosaic.environment_id" must be set via set_config value.', 'ENVVV');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

COMMENT ON FUNCTION ax_utils.tg__tenant_environment() IS
  E'This trigger should be called on tables needing automatic tenant_id, environment_id - to support multi-tenancy';

-- Trigger function to prevent accidental cross-tenant/environment deletion.
-- This trigger should be applied alongside 'ax_utils.tg__tenant_environment'
-- Settings & usage are the same as for 'ax_utils.tg__tenant_environment'.
-- IMPORTANT: DELETE operations made by db owner must explicitly specify 
--    tenant_id / environment_id in a WHERE condition. If DELETE would affect
--    rows from a tenant / environment that does not match settings then an 
--    exception will occur.
CREATE OR REPLACE FUNCTION ax_utils.tg__tenant_environment_on_delete() RETURNS TRIGGER AS $$
DECLARE
	tenant_id text = current_setting('mosaic.tenant_id', TRUE);
  environment_id text = current_setting('mosaic.environment_id', TRUE);
  is_superuser text = current_setting('is_superuser', TRUE);
  ignore_multitenancy text = current_setting('mosaic.ignore_multitenancy', TRUE);
BEGIN
  IF tenant_id <> '' THEN   -- Not null and not empty
    IF OLD.tenant_id::TEXT <> tenant_id THEN
      perform ax_utils.raise_error('The "mosaic.tenant_id" set via set_config must match the deleted row.', 'TENAN');
    END IF;
  ELSIF is_superuser <> 'on' AND ignore_multitenancy <> 'on' THEN
    perform ax_utils.raise_error('The "mosaic.tenant_id" must be set via set_config value.', 'TENAN');
  END IF;
  IF environment_id <> '' THEN   -- Not null and not empty
    IF OLD.environment_id::TEXT <> environment_id THEN
      perform ax_utils.raise_error('The "mosaic.environment_id" set via set_config must match the deleted row.', 'ENVVV');
    END IF;
  ELSIF is_superuser <> 'on' AND ignore_multitenancy <> 'on' THEN
    perform ax_utils.raise_error('The "mosaic.environment_id" must be set via set_config value.', 'ENVVV');
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql VOLATILE;

COMMENT ON FUNCTION ax_utils.tg__tenant_environment_on_delete() IS
  E'This trigger should be called on tables needing automatic tenant_id, environment_id - to support multi-tenancy';


-- Trigger function to set user_id from settings mosaic.auth.user_id.
-- The setting is added by Postgraphile to the pool context referring 
-- to the 'sub' property of the AuthenticatedSubject when the type is of EndUserAccount.
-- The setting value is checked against uuid_nil()
-- to eleminate the default value.
-- Otherwise, it let's the user_id sent through the request to be used.
-- Usage:
-- SELECT set_config('mosaic.auth.user_id', '00000000-0000-0000-0000-000000000000', TRUE);
CREATE OR REPLACE FUNCTION ax_utils.tg__user_id()
 RETURNS trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  user_id_ text = current_setting('mosaic.auth.user_id', TRUE);
BEGIN
  IF user_id_ <> '' AND user_id_ <> uuid_nil()::TEXT THEN -- NOT NULL AND NOT DEFAULT
    NEW.user_id = user_id_;
  ELSEIF NEW.user_id IS NULL THEN
    perform ax_utils.raise_error('The "mosaic.auth.user_id" must be set via set_config value.', 'USRID');
  END IF;
  RETURN NEW;
END;
$$
;

COMMENT ON FUNCTION ax_utils.tg__user_id() IS
  E'This trigger should be called on tables needing automatic user_id.';
