bcgov/PIMS

import { Equal, FindOptionsWhere, IsNull, Not, Raw } from 'typeorm';

/**
 * Special case for PID/PIN matching, as general text comparison is not sufficient.
 * We need to pad the results of the SELECT with LPAD() so that you can, for example,
 * query '000244' and have that match against PID 000-244-299, which is stored as the integer 244299.
 * Doing "...WHERE pid::text ILIKE '%000244%'; " fails, but doing "... WHERE LPAD(pid::text, 9, '0') ILIKE '%000244%'; " succeeds.
 * @param column
 * @param operatorValuePair
 * @returns
 */
export const constructFindOptionFromQueryPid = <T>(
  column: keyof T,
  operatorValuePair: string,
): FindOptionsWhere<T> => {
  if (operatorValuePair == null || operatorValuePair.match(/([^,]*),(.*)/) == null)
    return { [column]: undefined } as FindOptionsWhere<T>;

  // eslint-disable-next-line @typescript-eslint/no-unused-vars
  const [_, operator, value] = operatorValuePair.match(/([^,]*),(.*)/).map((a) => a.trim());
  const trimmedValue = value.replace(/[-]/g, ''); //remove all hyphens;
  // Only continue if the trimmed value is a number
  if (isNaN(parseInt(trimmedValue))) return { [column]: undefined } as FindOptionsWhere<T>;
  let internalMatcher;
  switch (operator) {
    case 'equals':
      internalMatcher = Equal;
      break;
    case 'contains':
      internalMatcher = (str: string) =>
        Raw((alias) => `LPAD( (${alias})::TEXT, 9, '0') ILIKE '%${str}%'`);
      break;
    case 'startsWith':
      internalMatcher = (str: string) =>
        Raw((alias) => `LPAD( (${alias})::TEXT, 9, '0') ILIKE '${str}%'`);
      break;
    case 'endsWith':
      internalMatcher = (str: string) =>
        Raw((alias) => `LPAD( (${alias})::TEXT, 9, '0') ILIKE '%${str}'`);
      break;
    default:
      return constructFindOptionFromQuery(column, operatorValuePair);
  }
  return { [column]: internalMatcher(trimmedValue) } as FindOptionsWhere<T>;
};

export const constructFindOptionFromQueryBoolean = <T>(
  column: keyof T,
  operatorValuePair: string, //format: "operator,value"
): FindOptionsWhere<T> => {
  if (operatorValuePair == null || operatorValuePair.match(/([^,]*),(.*)/) == null)
    return { [column]: undefined } as FindOptionsWhere<T>;

  // eslint-disable-next-line @typescript-eslint/no-unused-vars
  const [_, operator, value] = operatorValuePair.match(/([^,]*),(.*)/).map((a) => a.trim());
  let internalMatcher;
  // Empty string for when table searches for 'any'
  if (value === '') return { [column]: undefined } as FindOptionsWhere<T>;
  switch (operator) {
    case 'is':
      internalMatcher = (str: string) => Raw((alias) => `${alias} = ${str.toUpperCase()}`);
      break;
    case 'not':
      internalMatcher = (str: string) => Raw((alias) => `${alias} != ${str.toUpperCase()}`);
      break;
    default:
      return { [column]: undefined } as FindOptionsWhere<T>;
  }
  return { [column]: internalMatcher(value) } as FindOptionsWhere<T>;
};

export const constructFindOptionFromQuerySingleSelect = <T>(
  column: keyof T,
  operatorValuePair: string,
): FindOptionsWhere<T> => {
  if (operatorValuePair == null || operatorValuePair.match(/([^,]*),(.*)/) == null) {
    return { [column]: undefined } as FindOptionsWhere<T>;
  }
  const [, operator, value] = operatorValuePair.match(/([^,]*),(.*)/).map((a) => a.trim());
  const listOfValues = value.split(',');
  let internalMatcher;
  switch (operator) {
    case 'is':
      internalMatcher = Equal;
      break;
    case 'not':
      internalMatcher = (str: string) => Not(Equal(str));
      break;
    case 'isAnyOf':
      internalMatcher = () => IsAnyOfWrapper(listOfValues);
      break;
    default:
      return { [column]: undefined } as FindOptionsWhere<T>;
  }
  return { [column]: internalMatcher(value) } as FindOptionsWhere<T>;
};

/**
 * Accepts a column alias and produces a FindOptionsWhere style object.
 * This lets you plug in the return value to typeorm functions such as .find, findOne, etc.
 * @param column column name, should be a key of the TypeORM entity
 * @param operatorValuePair should be in the format of "operator,value", where operator is one of the supported statements below
 * @returns FindOptionsWhere<T>
 */
export const constructFindOptionFromQuery = <T>(
  column: keyof T,
  operatorValuePair: string, //format: "operator,value"
): FindOptionsWhere<T> => {
  if (operatorValuePair == null || operatorValuePair.match(/([^,]*),(.*)/) == null)
    return { [column]: undefined } as FindOptionsWhere<T>;

  // eslint-disable-next-line @typescript-eslint/no-unused-vars
  const [_, operator, value] = operatorValuePair.match(/([^,]*),(.*)/).map((a) => a.trim());
  let internalMatcher;
  switch (operator) {
    case 'equals':
      internalMatcher = Equal;
      break;
    case 'contains':
      internalMatcher = (str: string) => ILikeWrapper(str, 'contains');
      break;
    case 'startsWith':
      internalMatcher = (str: string) => ILikeWrapper(str, 'startsWith');
      break;
    case 'endsWith':
      internalMatcher = (str: string) => ILikeWrapper(str, 'endsWith');
      break;
    case 'is':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '=');
      break;
    case 'not':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '!=');
      break;
    case 'after':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '>');
      break;
    case 'before':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '<');
      break;
    case 'onOrAfter':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '>=');
      break;
    case 'onOrBefore':
      internalMatcher = (str: string) => TimestampComparisonWrapper(str, '<=');
      break;
    case 'isNotEmpty':
      internalMatcher = () => Not(IsNull());
      break;
    case 'isEmpty':
      internalMatcher = IsNull;
      break;
    default:
      return { [column]: undefined } as FindOptionsWhere<T>;
  }
  return { [column]: internalMatcher(value) } as FindOptionsWhere<T>;
};

type ILikeWrapperMode = 'contains' | 'startsWith' | 'endsWith';
/**
 * Returns a FindOptionsWhere type object formatted to provide common ILIKE style matching.
 * The column will be automatically cast to Postgres text type for maximum compatibility.
 * Ex: { Name: ILikeWrapper('foo') } will produce SQL like "... WHERE name::text ILIKE '%foo%' "
 * @param query string to match against
 * @param mode contains | startsWith | endsWith, determines wildcard char position
 * @returns FindOperatorWhere<T>
 */
export const ILikeWrapper = (query: string | undefined, mode: ILikeWrapperMode = 'contains') => {
  if (query == undefined) {
    return undefined;
  } else {
    let searchText = '';
    if (mode === 'startsWith') {
      searchText = `${query}%`;
    } else if (mode === 'endsWith') {
      searchText = `%${query}`;
    } else {
      searchText = `%${query}%`;
    }
    return Raw((alias) => `(${alias})::TEXT ILIKE '${searchText}'`);
  }
};

type TimestampOperator = '=' | '!=' | '<=' | '>=' | '<' | '>';
/**
 * Simple wrapper that takes a JS style date string and yields a postgres time comparison.
 * @param tsValue JS Style date string
 * @param operator '=' | '!=' | '<=' | '>=' | '<' | '>'
 * @returns FindOptionsWhere<T>
 */
export const TimestampComparisonWrapper = (tsValue: string, operator: TimestampOperator) => {
  if (operator === '=') {
    return Raw((alias) => `(${alias})::DATE = '${toPostgresTimestamp(new Date(tsValue))}'::DATE`);
  } else if (operator === '!=') {
    return Raw((alias) => `(${alias})::DATE != '${toPostgresTimestamp(new Date(tsValue))}'::DATE`);
  }
  return Raw((alias) => `${alias} ${operator} '${toPostgresTimestamp(new Date(tsValue))}'`);
};

/**
 * Simple wrapper to produce an IN ARRAY style query when you want to match against any of several different specific options.
 * @param elements An array of elements to match against.
 * @returns FindOptionsWhere<T>
 */
export const IsAnyOfWrapper = (elements: string[]) => {
  return Raw((alias) => `${alias} IN (${elements.map((a) => `'${a}'`).join(',')})`);
};

//The behavior of the Raw function seems bugged under certain query formats.
//It will use the correct table alias name, but not the correct column.
//ie. It will pass Project.ProjectNumber instead of "Project_project_number" (correct column alias constructed by TypeORM)
//or "Project".project_number (correct table alias plus non-aliased column access)
//Thankfully, it's not too difficult to manually format this.

export const fixColumnAlias = (str: string) => {
  const [tableAlias, columnAlias] = str.split('.');
  const fixedColumn = columnAlias
    .split(/\.?(?=[A-Z])/)
    .join('_')
    .toLowerCase(); // ExamplePascalCase -> example_pascal_case
  return `"${tableAlias}".${fixedColumn}`;
};

/**
 * Converstion of JS Date object type to the equivalent Postgres timestamp format string.
 * @param date JS Date object
 * @returns string
 */
export const toPostgresTimestamp = (date: Date) => {
  const pad = (num: number, size = 2) => {
    let s = String(num);
    while (s.length < size) s = '0' + s;
    return s;
  };

  const year = date.getFullYear();
  const month = pad(date.getUTCMonth() + 1); // getMonth() is zero-based
  const day = pad(date.getUTCDate());
  const hours = pad(date.getUTCHours());
  const minutes = pad(date.getUTCMinutes());
  const seconds = pad(date.getUTCSeconds());
  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
};

export const getDaysBetween = (earlierDate: Date, laterDate: Date): number => {
  return Math.trunc((laterDate.getTime() - earlierDate.getTime()) / (1000 * 60 * 60 * 24));
};