ForestAdmin/toolbelt

const { inject } = require('@forestadmin/context');
const P = require('bluebird');
const { plural, singular } = require('pluralize');
const ColumnTypeGetter = require('./sequelize-column-type-getter');
const DefaultValueExpression = require('./sequelize-default-value');
const TableConstraintsGetter = require('./sequelize-table-constraints-getter');
const EmptyDatabaseError = require('../../../../errors/database/empty-database-error');
const { isUnderscored } = require('../../../../utils/fields');

const ASSOCIATION_TYPE_BELONGS_TO = 'belongsTo';
const ASSOCIATION_TYPE_BELONGS_TO_MANY = 'belongsToMany';
const ASSOCIATION_TYPE_HAS_MANY = 'hasMany';
const ASSOCIATION_TYPE_HAS_ONE = 'hasOne';

const FOREIGN_KEY = 'FOREIGN KEY';

/** Queries database for default schema name */
async function getDefaultSchema(connection, userProvidedSchema) {
  if (userProvidedSchema) {
    return userProvidedSchema;
  }

  const dialect = connection.getDialect();
  const queries = {
    mssql: 'SELECT SCHEMA_NAME() AS default_schema',
    mysql: 'SELECT DATABASE() AS default_schema',
    mariadb: 'SELECT DATABASE() AS default_schema',
    postgres: 'SELECT CURRENT_SCHEMA() AS default_schema',
  };

  if (queries[dialect]) {
    const rows = await connection.query(queries[dialect], { type: connection.QueryTypes.SELECT });

    return rows.length && rows[0].default_schema ? rows[0].default_schema : 'public';
  }

  return 'public';
}

/** Retrieve the description of the fields in a given table. */
async function analyzeFields(queryInterface, tableName, config) {
  const dialect = queryInterface.sequelize.getDialect();
  let columnsByName;

  // Workaround bug in sequelize/dialects/mysql/query-generator#describe
  // => Don't provide the schema when using mysql/mariadb
  if (['mysql', 'mariadb'].includes(dialect)) {
    columnsByName = await queryInterface.describeTable(tableName, {});
  } else {
    columnsByName = await queryInterface.describeTable(tableName, { schema: config.dbSchema });
  }

  // Workaround bug in sequelize/dialects/(postgres|mssql)/query.js#run()
  // => Fetch the unmodified default value from the information schema
  if (dialect === 'postgres' || dialect === 'mssql') {
    const getDefaultsQuery = `
      SELECT column_name as colname, column_default as coldefault
      FROM information_schema.columns
      WHERE table_schema = ? AND table_name = ?
    `;

    const rows = await queryInterface.sequelize.query(getDefaultsQuery, {
      type: queryInterface.sequelize.QueryTypes.SELECT,
      replacements: [config.dbSchema, tableName],
    });
    rows.forEach(row => {
      columnsByName[row.colname].defaultValue = row.coldefault;
    });
  }

  Object.values(columnsByName).forEach(column => {
    const defaultValue = new DefaultValueExpression(dialect, column.type, column.defaultValue);
    // eslint-disable-next-line no-param-reassign
    column.defaultValue = defaultValue.parse();
  });

  return columnsByName;
}

async function analyzePrimaryKeys(schema) {
  return Object.keys(schema).filter(column => schema[column].primaryKey);
}

/** Retrieve table names from the provided schema. */
async function showAllTables(queryInterface, databaseConnection, schema) {
  const dbDialect = databaseConnection.getDialect();

  if (['mysql', 'mariadb'].includes(dbDialect)) {
    return queryInterface.showAllTables();
  }

  const tables = await queryInterface.sequelize.query(
    "SELECT table_name as table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ? AND table_type LIKE '%TABLE' AND table_name != 'spatial_ref_sys'",
    { type: queryInterface.sequelize.QueryTypes.SELECT, replacements: [schema] },
  );

  return tables.map(table => table.table_name);
}

function hasTimestamps(fields) {
  let hasCreatedAt = false;
  let hasUpdatedAt = false;

  fields.forEach(field => {
    if (field.name === 'createdAt') {
      hasCreatedAt = true;
    }

    if (field.name === 'updatedAt') {
      hasUpdatedAt = true;
    }
  });

  return hasCreatedAt && hasUpdatedAt;
}

function formatAliasName(columnName) {
  const { assertPresent, lodash } = inject();
  assertPresent({ lodash });

  const alias = lodash.camelCase(columnName);
  if (alias.endsWith('Id') && alias.length > 2) {
    return alias.substring(0, alias.length - 2);
  }
  if (alias.endsWith('Uuid') && alias.length > 4) {
    return alias.substring(0, alias.length - 4);
  }
  return alias;
}

// NOTICE: Look for the id column in both fields and primary keys.
function hasIdColumn(fields, primaryKeys) {
  return (
    fields.some(field => field.name === 'id' || field.nameColumn === 'id') ||
    primaryKeys?.includes('id')
  );
}

function isTechnicalTimestamp({ type, name }) {
  // NOTICE: Ignore technical timestamp fields.
  const FIELDS_TO_IGNORE = [
    'createdAt',
    'updatedAt',
    'deletedAt',
    'createDate',
    'updateDate',
    'deleteDate',
    'creationDate',
    'deletionDate',
  ];

  return type === 'DATE' && FIELDS_TO_IGNORE.includes(name);
}

function isJunctionTable(fields, constraints) {
  for (let index = 0; index < fields.length; index += 1) {
    const field = fields[index];

    // NOTICE: The only fields accepted are primary keys, technical timestamps and foreignKeys
    if (!isTechnicalTimestamp(field) && !field.primaryKey) {
      return false;
    }
  }

  const foreignKeys = constraints.filter(
    constraint =>
      constraint.foreignTableName && constraint.columnName && constraint.columnType === FOREIGN_KEY,
  );
  // NOTICE: To be a junction table it means you have 2 foreignKeys, no more no less
  return foreignKeys.length === 2;
}

// NOTICE: Check the foreign key's reference unicity
function checkUnicity(primaryKeys, uniqueIndexes, columnName) {
  const { assertPresent, lodash } = inject();
  assertPresent({ lodash });

  const isUnique =
    uniqueIndexes !== null &&
    uniqueIndexes.find(
      indexColumnName => indexColumnName.length === 1 && indexColumnName.includes(columnName),
    );

  const isPrimary = lodash.isEqual([columnName], primaryKeys);

  return isPrimary || isUnique;
}

function associationNameAlreadyExists(existingReferences, newReference) {
  return existingReferences.some(reference => reference && reference.as === newReference.as);
}

function referenceAlreadyExists(existingReferences, newReference) {
  return existingReferences.some(
    reference =>
      reference &&
      reference.ref === newReference.ref &&
      reference.association === newReference.association &&
      reference.foreignKey === newReference.foreignKey,
  );
}

// NOTICE: Format the references depending on the type of the association
function createReference(
  tableName,
  existingsReferences,
  association,
  foreignKey,
  manyToManyForeignKey,
) {
  const { assertPresent, lodash, strings } = inject();
  assertPresent({ lodash, strings });

  const foreignKeyName = lodash.camelCase(foreignKey.columnName);
  const reference = {
    foreignKey: foreignKey.columnName,
    foreignKeyName: `${foreignKeyName}Key`,
    association,
  };

  if (association === ASSOCIATION_TYPE_BELONGS_TO) {
    reference.ref = foreignKey.foreignTableName;
    reference.as = formatAliasName(foreignKey.columnName);
    if (foreignKey.foreignColumnName !== 'id') {
      reference.targetKey = foreignKey.foreignColumnName;
    }
  } else if (association === ASSOCIATION_TYPE_BELONGS_TO_MANY) {
    reference.ref = manyToManyForeignKey.foreignTableName;
    reference.otherKey = manyToManyForeignKey.columnName;
    reference.through = lodash.camelCase(strings.transformToSafeString(foreignKey.tableName));
    reference.as = lodash.camelCase(
      plural(`${manyToManyForeignKey.foreignTableName}_through_${foreignKey.tableName}`),
    );
  } else {
    reference.ref = foreignKey.tableName;

    const formater = association === ASSOCIATION_TYPE_HAS_MANY ? plural : singular;
    const prefix =
      singular(tableName) === formatAliasName(foreignKeyName)
        ? ''
        : `${formatAliasName(foreignKeyName)}_`;

    if (foreignKey.foreignColumnName !== 'id') {
      reference.sourceKey = foreignKey.foreignColumnName;
    }
    reference.as = lodash.camelCase(formater(`${prefix}${foreignKey.tableName}`));
  }

  if (referenceAlreadyExists(existingsReferences, reference)) return null;

  if (associationNameAlreadyExists(existingsReferences, reference)) {
    reference.as = lodash.camelCase(`${reference.as} ${reference.foreignKey}`);
  }

  return reference;
}

async function analyzeTable(queryInterface, tableConstraintsGetter, table, config) {
  const schema = await analyzeFields(queryInterface, table, config);

  return {
    schema,
    constraints: await tableConstraintsGetter.perform(table),
    primaryKeys: await analyzePrimaryKeys(schema),
  };
}

function createBelongsToReference(referenceTable, tableReferences, constraint) {
  const referenceColumnName = constraint.foreignColumnName;
  const referencePrimaryKeys = referenceTable.primaryKeys;
  const referenceUniqueConstraint = referenceTable.constraints.find(({ columnType }) =>
    ['UNIQUE', 'PRIMARY KEY'].includes(columnType),
  );
  const referenceUniqueIndexes = referenceUniqueConstraint
    ? referenceUniqueConstraint.uniqueIndexes
    : null;
  const isReferencePrimaryOrUnique = checkUnicity(
    referencePrimaryKeys,
    referenceUniqueIndexes,
    referenceColumnName,
  );

  if (isReferencePrimaryOrUnique) {
    return createReference(null, tableReferences, ASSOCIATION_TYPE_BELONGS_TO, constraint);
  }
  return null;
}

// NOTICE: Use the foreign key and reference properties to determine the associations
//         and push them as references of the table.
function createAllReferences(databaseSchema, schemaGenerated) {
  const references = {};
  Object.keys(databaseSchema).forEach(tableName => {
    references[tableName] = [];
  });

  Object.keys(databaseSchema).forEach(tableName => {
    const table = databaseSchema[tableName];
    const { constraints, primaryKeys } = table;
    const { isJunction } = schemaGenerated[tableName].options;

    const foreignKeysWithExistingTable = constraints.filter(
      constraint =>
        constraint.columnType === FOREIGN_KEY && databaseSchema[constraint.foreignTableName],
    );

    foreignKeysWithExistingTable.forEach(constraint => {
      const { columnName } = constraint;
      const uniqueIndexes = constraint.uniqueIndexes || null;

      const isPrimaryOrUnique = checkUnicity(primaryKeys, uniqueIndexes, columnName);

      const referenceTableName = constraint.foreignTableName;

      if (isJunction) {
        const manyToManyKeys = foreignKeysWithExistingTable.filter(
          otherKey => otherKey.columnName !== constraint.columnName,
        );

        manyToManyKeys.forEach(manyToManyKey => {
          references[referenceTableName].push(
            createReference(
              referenceTableName,
              references[referenceTableName],
              ASSOCIATION_TYPE_BELONGS_TO_MANY,
              constraint,
              manyToManyKey,
            ),
          );
        });
      } else {
        references[referenceTableName].push(
          createReference(
            referenceTableName,
            references[referenceTableName],
            isPrimaryOrUnique ? ASSOCIATION_TYPE_HAS_ONE : ASSOCIATION_TYPE_HAS_MANY,
            constraint,
          ),
        );
      }

      references[tableName].push(
        createBelongsToReference(
          databaseSchema[referenceTableName],
          references[tableName],
          constraint,
        ),
      );
    });
  });

  // remove null references
  return Object.entries(references).reduce((accumulator, [tableName, tableReferences]) => {
    accumulator[tableName] = tableReferences.filter(Boolean);
    return accumulator;
  }, {});
}

function isOnlyJoinTableWithId(schema, constraints) {
  const idColumn = Object.keys(schema).find(columnName => columnName === 'id');

  if (!idColumn) return false;

  const possibleForeignColumnNames = Object.keys(schema).filter(
    columnName => !isTechnicalTimestamp(schema[columnName]) && columnName !== 'id',
  );

  const columnWithoutForeignKey = possibleForeignColumnNames.find(
    columnName =>
      !constraints.find(
        constraint => constraint.columnName === columnName && constraint.columnType === FOREIGN_KEY,
      ),
  );

  return !columnWithoutForeignKey;
}

async function createTableSchema(
  columnTypeGetter,
  { schema, constraints, primaryKeys },
  tableName,
) {
  const { assertPresent, lodash } = inject();
  assertPresent({ lodash });

  const fields = [];

  await P.each(Object.keys(schema), async columnName => {
    const columnInfo = schema[columnName];
    const type = await columnTypeGetter.perform(columnInfo, columnName, tableName);
    const foreignKey = constraints.find(
      constraint => constraint.columnName === columnName && constraint.columnType === FOREIGN_KEY,
    );
    const isValidField =
      type &&
      (!foreignKey ||
        !foreignKey.foreignTableName ||
        !foreignKey.columnName ||
        columnInfo.primaryKey);
    // NOTICE: If the column is of integer type, named "id" and primary, Sequelize will handle it
    //         automatically without necessary declaration.
    const isIdIntegerPrimaryColumn =
      columnName === 'id' && ['INTEGER', 'BIGINT'].includes(type) && columnInfo.primaryKey;
    // NOTICE: But in some cases we want to force the id to be still generated.
    //         For example, Sequelize will not use a default id field on a model
    //         that has only foreign keys, so if the id primary key is present, we need to force it.
    const forceIdColumn = isIdIntegerPrimaryColumn && isOnlyJoinTableWithId(schema, constraints);

    if (isValidField && (!isIdIntegerPrimaryColumn || forceIdColumn)) {
      // NOTICE: sequelize considers column name with parenthesis as raw Attributes
      // do not try to camelCase the name for avoiding sequelize issues
      const hasParenthesis = columnName.includes('(') || columnName.includes(')');
      const name = hasParenthesis ? columnName : lodash.camelCase(columnName);
      let isRequired = !columnInfo.allowNull;
      if (isTechnicalTimestamp({ name, type })) {
        isRequired = false;
      }

      const field = {
        name,
        nameColumn: columnName,
        type,
        primaryKey: columnInfo.primaryKey,
        defaultValue: columnInfo.defaultValue,
        isRequired,
      };

      fields.push(field);
    }
  });

  const options = {
    underscored: isUnderscored(fields),
    timestamps: hasTimestamps(fields),
    hasIdColumn: hasIdColumn(fields, primaryKeys),
    hasPrimaryKeys: Boolean(primaryKeys?.length),
    isJunction: isJunctionTable(fields, constraints),
  };

  return {
    fields,
    primaryKeys,
    options,
  };
}

// NOTICE: This detects two generated fields (regular or reference's alias) with the same name
//         and rename reference's alias as `Linked${collectionReferenced}` to prevent Sequelize
//         from crashing at startup.
function fixAliasConflicts(wholeSchema) {
  const { assertPresent, lodash } = inject();
  assertPresent({ lodash });

  const tablesName = Object.keys(wholeSchema);

  if (!tablesName.length) {
    return;
  }

  tablesName.forEach(tableName => {
    const table = wholeSchema[tableName];

    if (table.references.length && table.fields.length) {
      const fieldNames = table.fields.map(field => field.name);

      table.references.forEach((reference, index) => {
        if (fieldNames.includes(reference.as)) {
          table.references[index].as = `linked${lodash.upperFirst(reference.as)}`;
        }
      });
    }
  });
}

async function analyzeSequelizeTables(connection, config, allowWarning) {
  const { assertPresent, lodash } = inject();
  assertPresent({ lodash });

  // User provided a schema, check if it exists
  if (config.dbSchema) {
    const schemas = await connection.query(
      'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?;',
      { type: connection.QueryTypes.SELECT, replacements: [config.dbSchema] },
    );

    if (!schemas.length) {
      const message = 'This schema does not exists.';

      return inject().terminator.terminate(1, {
        errorCode: 'database_authentication_error',
        errorMessage: message,
        logs: [message],
      });
    }
  }

  // If dbSchema was not provided by user, get default one.
  const configWithSchema = {
    ...config,
    dbSchema: await getDefaultSchema(connection, config.dbSchema),
  };

  // Build the db schema.
  const schemaAllTables = {};
  const queryInterface = connection.getQueryInterface();
  const databaseSchema = {};
  const tableNames = await showAllTables(queryInterface, connection, configWithSchema.dbSchema);
  const constraintsGetter = new TableConstraintsGetter(connection, configWithSchema.dbSchema);

  await P.each(tableNames, async tableName => {
    const { schema, constraints, primaryKeys } = await analyzeTable(
      queryInterface,
      constraintsGetter,
      tableName,
      configWithSchema,
    );
    databaseSchema[tableName] = {
      schema,
      constraints,
      primaryKeys,
      references: [],
    };
  });

  const columnTypeGetter = new ColumnTypeGetter(
    connection,
    configWithSchema.dbSchema,
    allowWarning,
  );

  await P.each(tableNames, async tableName => {
    schemaAllTables[tableName] = await createTableSchema(
      columnTypeGetter,
      databaseSchema[tableName],
      tableName,
    );
  });

  // NOTICE: Fill the references field for each table schema
  const referencesPerTable = createAllReferences(databaseSchema, schemaAllTables);
  Object.keys(referencesPerTable).forEach(tableName => {
    schemaAllTables[tableName].references = lodash.sortBy(
      referencesPerTable[tableName],
      'association',
    );

    // NOTE: When a table contains no field, it will be considered camelCased
    //       by default, so we need to check its references to ensure whether
    //       it is camelCased or not.
    if (!schemaAllTables[tableName].fields.length) {
      schemaAllTables[tableName].options.underscored = isUnderscored(
        schemaAllTables[tableName].references.map(({ foreignKey }) => ({ nameColumn: foreignKey })),
      );
    }
  });

  if (Object.keys(schemaAllTables).length === 0) {
    throw new EmptyDatabaseError('no tables found', {
      orm: 'sequelize',
      dialect: connection.getDialect(),
    });
  }

  fixAliasConflicts(schemaAllTables);

  return schemaAllTables;
}

module.exports = analyzeSequelizeTables;