--- DB field constraints section

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_matches_pattern;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_matches_pattern",
    "   CHECK(ax_utils.constraint_matches_pattern(${2:column_name}, '${3:^[\\w]+$}', 'The ${2/[_]/ /g} does not match the pattern \"%2\\$s\"'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the property value does not match a provided pattern.",
    "The message parameter is optional. The default message includes the value and pattern which did not match but does not include the property name.",
    "By default, the used error code is 'PATRN' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_matches_pattern(
  input_value text,
  pattern text,
  error_message text default 'The value "%s" does not match the pattern "%s".',
  error_code text default 'PATRN'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF input_value !~* pattern THEN
      perform ax_utils.raise_error(error_message, error_code, input_value, pattern);
  END IF;
  RETURN true;
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_max_length;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_max_length",
    "   CHECK(ax_utils.constraint_max_length(${2:column_name}, ${3:100}, 'The ${2/[_]/ /g} must be %2\\$s characters long or less.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the length of the property value is greater than the specified max length.",
    "The message parameter is optional. The default message includes the value which was too long but does not include the property name.",
    "By default, the used error code is 'MXLEN' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_max_length(
  input_value text,
  max_length integer,
  error_message text default 'The value "%s" is too long. It must be a maximum of %s characters long.',
  error_code text default 'MXLEN'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF length(input_value) > max_length THEN
      perform ax_utils.raise_error(error_message, error_code, input_value, max_length::text);
  END IF;
  RETURN true;
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_min_length;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_min_length",
    "   CHECK(ax_utils.constraint_min_length(${2:column_name}, ${3:1}, 'The ${2/[_]/ /g} must be %2\\$s characters long or more.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the length of the property value is less than the specified min length.",
    "The message parameter is optional. The default message includes the value which was too short but does not include the property name.",
    "By default, the used error code is 'MNLEN' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_min_length(
  input_value text,
  min_length integer,
  error_message text default 'The value "%s" is not long enough. It must be at least %s characters long.',
  error_code text default 'MNLEN'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF length(input_value) < min_length THEN
  	perform ax_utils.raise_error(error_message, error_code, input_value, min_length::text);
  END IF;
  RETURN true;
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_max_value;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_max_value",
    "   CHECK(ax_utils.constraint_max_value(${2:column_name}, ${3:100}, 'The value of the ${2/[_]/ /g} is %1\\$s, but must be at most %2\\$s.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the numeric value is larger than the specified value.",
    "The message parameter is optional. The default message includes the value which was too big but does not include the property name.",
    "By default, the used error code is 'MXVAL' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_max_value(
  input_value numeric,
  max_value numeric,
  error_message text default 'The value "%s" is too big. The maximum is %s.',
  error_code text default 'MXVAL'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF input_value > max_value THEN
      perform ax_utils.raise_error(error_message, error_code, input_value::text, max_value::text);
  END IF;
  RETURN true;
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_min_value;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_min_value",
    "   CHECK(ax_utils.constraint_min_value(${2:column_name}, ${3:0}, 'The value of the ${2/[_]/ /g} is %1\\$s, but must be at least %2\\$s.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the numeric value is smaller than the specified value.",
    "The message parameter is optional. The default message includes the value which was too small but does not include the property name.",
    "By default, the used error code is 'MNVAL' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_min_value(
  input_value numeric,
  min_value numeric,
  error_message text default 'The value "%s" is too small. The minimum is %s.',
  error_code text default 'MNVAL'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF input_value < min_value THEN
  	perform ax_utils.raise_error(error_message, error_code, input_value::text, min_value::text);
  END IF;
  RETURN true;
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${3:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_not_empty;",
    "ALTER TABLE ${3:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_not_empty",
    "   CHECK(ax_utils.constraint_not_empty(${2:column_name}, 'The ${2/[_]/ /g} must not be empty.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value is null, empty string, or whitespace string.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'EMPTY' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_not_empty(
  input_value text,
  error_message text default 'The property value must not be empty.',
  error_code text default 'EMPTY'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if ax_utils.validation_not_empty(input_value) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code);
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${3:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_is_trimmed;",
    "ALTER TABLE ${3:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_is_trimmed",
    "   CHECK(ax_utils.constraint_is_trimmed(${2:column_name}, 'The ${2/[_]/ /g} must not start or end with whitespace characters.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value starts or ends with whitespace value.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'NTRIM' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_is_trimmed(
  input_value text,
  error_message text default 'The property value must not start or end with whitespace characters.',
  error_code text default 'NTRIM'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if ax_utils.validation_is_trimmed(input_value) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code);
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${3:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_is_base64;",
    "ALTER TABLE ${3:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_is_base64",
    "   CHECK(ax_utils.constraint_is_base64(${2:column_name}, 'The ${2/[_]/ /g} must be a Base64 encoded value.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value is not Base64 encoded.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'BAS64' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_is_base64(
  input_value text,
  error_message text default 'The property must be a Base64 encoded value.',
  error_code text default 'BAS64'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if ax_utils.validation_is_base64(input_value) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code);
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${3:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_is_identifier_key;",
    "ALTER TABLE ${3:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_is_identifier_key",
    "   CHECK(ax_utils.constraint_is_identifier_key(${2:column_name}, 'The ${2/[_]/ /g} must only contain letters, numbers, underscores, and dashes.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value contains characters other than letters, numbers, underscores, and dashes.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'IDKEY' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_is_identifier_key(
  input_value text,
  error_message text default 'The property must only contain letters, numbers, underscores, and dashes.',
  error_code text default 'IDKEY'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if ax_utils.validation_is_identifier_key(input_value) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code);
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_starts_with;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_starts_with",
    "   CHECK(ax_utils.constraint_starts_with(${2:column_name}, '${3:prefix}', 'The ${2/[_]/ /g} must start with \"%2\\$s\".'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value does not start with specified characters.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'STRWT' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_starts_with(
  input_value text,
  prefix_value text,
  error_message text default 'The property must start with "%2$s".',
  error_code text default 'STRWT'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if ax_utils.validation_starts_with(input_value, prefix_value) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code, input_value, prefix_value::text);
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${5:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_has_allowed_value;",
    "ALTER TABLE ${5:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_has_allowed_value",
    "   CHECK(ax_utils.constraint_has_allowed_value(${2:column_name},",
    "                                               ARRAY['${3:ALLOWED_VALUE_ONE}','${4:ALLOWED_VALUE_TWO}'],",
    "                                               'The ${2/[_]/ /g} value \"%s\" is not in the list of allowed values (\"%s\").'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value is not one of the allowed values.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'ALWDV' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_has_allowed_value(
  input_value text,
  allowed_values text[],
  error_message text default 'The value "%s" is not in the list of allowed values ("%s").',
  error_code text default 'ALWDV'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value = ANY(allowed_values) then
    return true;
  end if;
  perform ax_utils.raise_error(error_message, error_code, input_value, array_to_string(allowed_values, '", "'));
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_not_default_uuid;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_not_default_uuid",
    "   CHECK(ax_utils.constraint_not_default_uuid(input_value => ${2:column_name}, error_message => 'A valid UUID must be provied - the ${2/[_]/ /g} value \"%s\" is not valid.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the filled UUID value is empty or a default value.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'UUID0' but can be overridden using the 4th parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_not_default_uuid(
  input_value UUID,
  default_uuid UUID default 'ffffffff-ffff-ffff-ffff-ffffffffffff',
  error_message text default 'A valid UUID must be provided - the value "%s" is not valid.',
  error_code text default 'UUID0'
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value IS NULL OR input_value = default_uuid then
    perform ax_utils.raise_error(error_message, error_code, input_value::text);
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_is_url;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_is_url",
    "   CHECK(ax_utils.constraint_is_url(${2:column_name}, 'The ${2/[_]/ /g} must be a valid URL.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value is not a valid URL.",
    "Empty value is considered a valid value in this case.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'NVURL' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_is_url(
  input_value TEXT,
  error_message TEXT DEFAULT 'The property must be a valid URL.',
  error_code TEXT DEFAULT 'NVURL'
) RETURNS BOOLEAN
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF ax_utils.validation_is_optional_url(input_value) THEN
    RETURN true;
  END IF;
  perform ax_utils.raise_error(error_message, error_code);
END;
$$;

/*-snippet
{
  "body": [
    "ALTER TABLE ${4:app_public}.${1:table_name} DROP CONSTRAINT IF EXISTS ${2:column_name}_not_empty_array;",
    "ALTER TABLE ${4:app_public}.${1:table_name} ADD CONSTRAINT ${2:column_name}_not_empty_array",
    "   CHECK(ax_utils.constraint_not_empty_array(${2:column_name}, 'The ${2/[_]/ /g} must not be an empty array or contain empty/whitespace elements.'));"
  ],
  "description": [
    "Adds a constraint that throws an error if the value is an empty array or an array containing empty or whitespace elements.",
    "The message parameter is optional. The default message does not include the property name.",
    "By default, the used error code is 'EMPTY' but can be overridden using the 3rd parameter."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.constraint_not_empty_array(input_value TEXT[], error_message TEXT DEFAULT 'The property must not be an empty array or contain empty/whitespace elements.'::TEXT, error_code TEXT DEFAULT 'EMPTY'::TEXT)
 RETURNS BOOLEAN
 LANGUAGE plpgsql
AS $$
BEGIN
  IF ax_utils.validation_not_empty_array(input_value) THEN
    RETURN true;
  END IF;
  perform ax_utils.raise_error(error_message, error_code);
END;
$$
;

--- reusable validation functions section

/*-snippet
{
  "body": [
    "ax_utils.validation_not_empty(${1:column_or_variable})"
  ],
  "description": [
    "Returns true if the value is not null, not an empty string, and not a whitespace string. Returns false otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_not_empty(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value IS NULL OR input_value !~* '.*\S.*' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_is_trimmed(${1:column_or_variable})"
  ],
  "description": [
    "Returns false if the value starts or ends with a whitespace character. Returns true otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_is_trimmed(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value !~* '^(?!.*^[\s])(?!.*[\s]$).*$' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_is_base64(${1:column_or_variable})"
  ],
  "description": [
    "Returns true if the value is a base64 encoded string. Returns false otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_is_base64(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value !~* '^([A-Za-z0-9+/]{4})*([A-Za-z0-9+/]{3}=|[A-Za-z0-9+/]{2}==)?$' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_is_identifier_key(${1:column_or_variable})"
  ],
  "description": [
    "Returns true if the value only contains letters, numbers, underscores, and dashes.",
    "Returns false otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_is_identifier_key(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value !~* '^([A-Za-z0-9_\-])*$' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_is_url(${1:column_or_variable})"
  ],
  "description": [
    "Returns true if the value is a valid URL. Returns false otherwise, including in cases when value is an empty string."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_is_url(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value !~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}(\.[a-z]{2,9})?\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_is_optional_url(${1:column_or_variable})"
  ],
  "description": [
    "Returns true if the value is a valid URL or an empty string. Returns false otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_is_optional_url(
  input_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if not ax_utils.validation_not_empty(input_value) THEN
    return true;
  end if;

  if input_value !~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}(\.[a-z]{2,9})?\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$' then
  	return false;
  end if;
  return true;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_starts_with(${1:column_or_variable}, '${3:prefix}')"
  ],
  "description": [
    "Returns true if the value starts with the specified prefix value. Returns false otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_starts_with(
  input_value text,
  prefix_value text
) RETURNS boolean
  LANGUAGE plpgsql
  AS $$
begin
  if input_value like prefix_value || '%' then
  	return true;
  end if;
  return false;
end;
$$;

/*-snippet
{
  "body": [
    "ax_utils.validation_not_empty_array(${1:column_or_variable})"
  ],
  "description": [
    "Returns false if the value is an empty array or an array containing empty elements. Returns true otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_not_empty_array(input_value TEXT[])
 RETURNS BOOLEAN
 LANGUAGE plpgsql
AS $$
DECLARE
  val_ TEXT;
BEGIN
  IF array_length(input_value, 1) IS NULL THEN 
    RETURN FALSE;
  END IF;
 
 
  FOREACH val_ IN ARRAY input_value LOOP
 	  IF val_ IS NULL OR val_ !~* '.*\S.*' THEN
  	  	RETURN FALSE;
  	END IF;
  END LOOP;
  
  RETURN TRUE;
END;
$$
;

/*-snippet
{
  "body": [
    "ax_utils.validation_valid_url_array(${1:column_or_variable})"
  ],
  "description": [
    "Returns false if at least one element of an array is not a valid URL or an empty string. Returns true otherwise."
  ]
}
snippet-*/
CREATE OR REPLACE FUNCTION ax_utils.validation_valid_url_array(input_value TEXT[])
 RETURNS BOOLEAN
 LANGUAGE plpgsql
AS $$
DECLARE
  val_ TEXT;
BEGIN
  IF array_length(input_value, 1) IS NOT NULL THEN 
    FOREACH val_ IN ARRAY input_value LOOP
      IF val_ IS NOT NULL THEN
        IF NOT ax_utils.validation_is_url(val_) THEN
          RETURN FALSE;
        END IF;
      ELSIF val_ IS NULL OR val_ !~* '.*\S.*' THEN
  	  	RETURN FALSE;
      END IF;
    END LOOP;
  END IF;
  RETURN TRUE;
END;
$$
;

-- Section to put obsolete functions DROP expressions (unless you replace them with another, then add DROP expression before the definition of a function that replaces it). 
-- One-year retention policy. Should be enough for the library to be updated and the drop statement to be executed...
-- TODO: Remove these after 01.01.2023
DROP FUNCTION IF EXISTS ax_utils.constraint_not_url(TEXT, TEXT, TEXT);
DROP FUNCTION IF EXISTS ax_utils.constraint_not_invalid_url_array(TEXT[], TEXT, TEXT)
