<commit_before>package org.folio.rest.persist.ddlgen;

import static org.hamcrest.CoreMatchers.allOf;
import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.not;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.junit.jupiter.api.Assertions.assertThrows;

import java.io.IOException;

import org.folio.dbschema.ForeignKeys;
import org.folio.dbschema.Schema;
import org.folio.dbschema.Table;
import org.folio.dbschema.TenantOperation;
import org.folio.dbschema.View;
import org.folio.dbschema.ObjectMapperTool;
import org.folio.util.ResourceUtil;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.CsvSource;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException;

import freemarker.template.TemplateException;

public class SchemaMakerTest {
  private String tidy(String s) {
    return s
        .replaceAll("-- [^\n\r]*", " ")  // remove comment
        .replaceAll("[ \n\r]+", " ")     // merge multiple whitespace characters
        .replaceAll(" *\\( *", "(")      // remove space before and after (
        .replaceAll(" *\\) *", ")")      // remove space before and after )
        .replaceAll(";", ";\n");         // one line per sql statement
  }

  /**
   * @param name resource path of the input schema json file
   * @return Schema the input file was converted into
   */
  private Schema schema(String name) throws JsonMappingException, JsonProcessingException {
    String json = ResourceUtil.asString(name);
    return ObjectMapperTool.getMapper().readValue(json, Schema.class);
  }

  /**
   * @return SchemaMaker constructed from the schemaName json file and the other parameters.
   */
  private SchemaMaker schemaMaker(String tenant, String module, TenantOperation mode,
      String previousVersion, String newVersion, String schemaName)
          throws JsonMappingException, JsonProcessingException {
    SchemaMaker schemaMaker = new SchemaMaker(tenant, module, mode, previousVersion, newVersion);
    schemaMaker.setSchema(schema(schemaName));
    return schemaMaker;
  }

  @Test
  public void canCreateAuditedTable() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/schemaWithAudit.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("CREATE TABLE IF NOT EXISTS harvard_circ.audit_test_tenantapi"));
    assertThat(result, containsString("CREATE OR REPLACE FUNCTION harvard_circ.audit_test_tenantapi_changes() RETURNS TRIGGER"));
    // index for field in audit table
    assertThat(result, containsString("CREATE INDEX IF NOT EXISTS audit_test_tenantapi_testTenantapiAudit_id_idx "));
    // auditingSnippets
    assertThat(result, allOf(containsString("PERFORM 1;"), containsString("PERFORM 2;"), containsString("PERFORM 3;"),
                             containsString("var1 TEXT;"), containsString("var2 TEXT;"), containsString("var3 TEXT;")));
  }

  @Test
  public void canCreateIndexPath() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
        "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");

    String result = schemaMaker.getSchema().getTables().get(0).getFullTextIndex().get(0).getFinalSqlExpression("tablea");
    assertThat(result,containsString("concat_space_sql(tablea.jsonb->>'field1' , tablea.jsonb->>'field2')"));
    result = schemaMaker.getSchema().getTables().get(0).getGinIndex().get(0).getFinalSqlExpression("tablea");
    assertThat(result,containsString("concat_space_sql(tablea.jsonb->>'firstName' , tablea.jsonb->>'lastName')"));
  }

  @Test
  public void canCreateIndexPath2() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
        "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.getSchema().getTables().get(1).getGinIndex().get(0).getFinalSqlExpression("tableb");
    assertThat(result,containsString("lower(concat_space_sql(jsonb->>'city', jsonb->>'state'))"));
    result = schemaMaker.getSchema().getTables().get(1).getFullTextIndex().get(0).getFinalSqlExpression("tableb");
    assertThat(result,containsString("lower(concat_space_sql(jsonb->>'field1', jsonb->>'field2'))"));
  }

  @Test
  public void canCreateCompoundIndex() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("CREATE INDEX IF NOT EXISTS tablea_ftfield_idx_ft"));
    assertThat(result, containsString("concat_space_sql(tablea.jsonb->>'firstName' , tablea.jsonb->>'lastName')"));

    assertThat(result,containsString("((lower(concat_space_sql(concat_array_object_values(tablea.jsonb->'user','firstName') , concat_array_object_values(tablea.jsonb->'user','lastName')))) public.gin_trgm_ops)"));
    assertThat(result,containsString("((lower(concat_space_sql(concat_array_object_values(tablea.jsonb->'user'->'info','firstName') , concat_array_object_values(tablea.jsonb->'user'->'info','lastName')))) public.gin_trgm_ops)"));
    assertThat(result,containsString("( to_tsvector('simple', concat_space_sql(concat_array_object_values(tablea.jsonb->'field1','firstName') , concat_array_object_values(tablea.jsonb->'field2','lastName'))) )"));
    assertThat(result,containsString("( to_tsvector('simple', concat_space_sql(concat_array_object_values(tablea.jsonb->'field1'->'info','firstName') , concat_array_object_values(tablea.jsonb->'field2'->'info','lastName'))) )"));
  }

  @Test
  public void canCreateSQLExpressionIndex() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("lower(concat_space_sql(jsonb->>'field1', jsonb->>'field2'))"));
  }

  @Test
  public void failsWhenGenerateID() throws Exception {
    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    assertThat(assertThrows(UnrecognizedPropertyException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaGenerateId.json"));
    }).getMessage(), containsString("Unrecognized field \"generateId\""));
  }

  @Test
  public void failsWhenPkColumnName() throws Exception {
    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    assertThat(assertThrows(UnrecognizedPropertyException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaPkColumnName.json"));
    }).getMessage(), containsString("Unrecognized field \"pkColumnName\""));
  }

  @Test
  public void pkColumnName() {
    assertThat(new Table().getPkColumnName(), is("id"));
    assertThat(new View().getPkColumnName(), is("id"));
  }

  @Test
  public void failsWhenPopulateJsonWithId() throws IOException, TemplateException {

    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    IOException e = assertThrows(IOException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaPopulateJsonWithId.json"));
      schemaMaker.generateDDL();
    });
    assertThat(tidy(e.getMessage()), containsString("Unrecognized field \"populateJsonWithId\""));
  }

  @Test
  public void lowerUnaccentIndex() throws IOException, TemplateException {

    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2",
      "templates/db_scripts/caseinsensitive.json");
    assertThat(tidy(schemaMaker.generateDDL()), containsString(
        "CREATE INDEX IF NOT EXISTS item_title_idx ON harvard_circ.item ' "
        + "|| $rmb$(left(lower(f_unaccent(jsonb->>'title')),600))$rmb$)"));
  }

  @Test
  public void scriptExistsAndDoesntUpgrade() throws IOException, TemplateException {

    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,<commit_msg>RMB-705: Refactor assertThrows lambda to have only one invocation throwing an exception<commit_after>package org.folio.rest.persist.ddlgen;

import static org.hamcrest.CoreMatchers.allOf;
import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.not;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.junit.jupiter.api.Assertions.assertThrows;

import java.io.IOException;

import org.folio.dbschema.ForeignKeys;
import org.folio.dbschema.Schema;
import org.folio.dbschema.Table;
import org.folio.dbschema.TenantOperation;
import org.folio.dbschema.View;
import org.folio.dbschema.ObjectMapperTool;
import org.folio.util.ResourceUtil;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.CsvSource;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException;

import freemarker.template.TemplateException;

public class SchemaMakerTest {
  private String tidy(String s) {
    return s
        .replaceAll("-- [^\n\r]*", " ")  // remove comment
        .replaceAll("[ \n\r]+", " ")     // merge multiple whitespace characters
        .replaceAll(" *\\( *", "(")      // remove space before and after (
        .replaceAll(" *\\) *", ")")      // remove space before and after )
        .replaceAll(";", ";\n");         // one line per sql statement
  }

  /**
   * @param name resource path of the input schema json file
   * @return Schema the input file was converted into
   */
  private Schema schema(String name) throws JsonMappingException, JsonProcessingException {
    String json = ResourceUtil.asString(name);
    return ObjectMapperTool.getMapper().readValue(json, Schema.class);
  }

  /**
   * @return SchemaMaker constructed from the schemaName json file and the other parameters.
   */
  private SchemaMaker schemaMaker(String tenant, String module, TenantOperation mode,
      String previousVersion, String newVersion, String schemaName)
          throws JsonMappingException, JsonProcessingException {
    SchemaMaker schemaMaker = new SchemaMaker(tenant, module, mode, previousVersion, newVersion);
    schemaMaker.setSchema(schema(schemaName));
    return schemaMaker;
  }

  @Test
  public void canCreateAuditedTable() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/schemaWithAudit.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("CREATE TABLE IF NOT EXISTS harvard_circ.audit_test_tenantapi"));
    assertThat(result, containsString("CREATE OR REPLACE FUNCTION harvard_circ.audit_test_tenantapi_changes() RETURNS TRIGGER"));
    // index for field in audit table
    assertThat(result, containsString("CREATE INDEX IF NOT EXISTS audit_test_tenantapi_testTenantapiAudit_id_idx "));
    // auditingSnippets
    assertThat(result, allOf(containsString("PERFORM 1;"), containsString("PERFORM 2;"), containsString("PERFORM 3;"),
                             containsString("var1 TEXT;"), containsString("var2 TEXT;"), containsString("var3 TEXT;")));
  }

  @Test
  public void canCreateIndexPath() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
        "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");

    String result = schemaMaker.getSchema().getTables().get(0).getFullTextIndex().get(0).getFinalSqlExpression("tablea");
    assertThat(result,containsString("concat_space_sql(tablea.jsonb->>'field1' , tablea.jsonb->>'field2')"));
    result = schemaMaker.getSchema().getTables().get(0).getGinIndex().get(0).getFinalSqlExpression("tablea");
    assertThat(result,containsString("concat_space_sql(tablea.jsonb->>'firstName' , tablea.jsonb->>'lastName')"));
  }

  @Test
  public void canCreateIndexPath2() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
        "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.getSchema().getTables().get(1).getGinIndex().get(0).getFinalSqlExpression("tableb");
    assertThat(result,containsString("lower(concat_space_sql(jsonb->>'city', jsonb->>'state'))"));
    result = schemaMaker.getSchema().getTables().get(1).getFullTextIndex().get(0).getFinalSqlExpression("tableb");
    assertThat(result,containsString("lower(concat_space_sql(jsonb->>'field1', jsonb->>'field2'))"));
  }

  @Test
  public void canCreateCompoundIndex() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("CREATE INDEX IF NOT EXISTS tablea_ftfield_idx_ft"));
    assertThat(result, containsString("concat_space_sql(tablea.jsonb->>'firstName' , tablea.jsonb->>'lastName')"));

    assertThat(result,containsString("((lower(concat_space_sql(concat_array_object_values(tablea.jsonb->'user','firstName') , concat_array_object_values(tablea.jsonb->'user','lastName')))) public.gin_trgm_ops)"));
    assertThat(result,containsString("((lower(concat_space_sql(concat_array_object_values(tablea.jsonb->'user'->'info','firstName') , concat_array_object_values(tablea.jsonb->'user'->'info','lastName')))) public.gin_trgm_ops)"));
    assertThat(result,containsString("( to_tsvector('simple', concat_space_sql(concat_array_object_values(tablea.jsonb->'field1','firstName') , concat_array_object_values(tablea.jsonb->'field2','lastName'))) )"));
    assertThat(result,containsString("( to_tsvector('simple', concat_space_sql(concat_array_object_values(tablea.jsonb->'field1'->'info','firstName') , concat_array_object_values(tablea.jsonb->'field2'->'info','lastName'))) )"));
  }

  @Test
  public void canCreateSQLExpressionIndex() throws IOException, TemplateException {
    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
      "mod-foo-18.2.3", "mod-foo-18.2.4", "templates/db_scripts/compoundIndex.json");
    String result = schemaMaker.generateDDL();

    assertThat(result, containsString("lower(concat_space_sql(jsonb->>'field1', jsonb->>'field2'))"));
  }

  @Test
  public void failsWhenGenerateID() throws Exception {
    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    assertThat(assertThrows(UnrecognizedPropertyException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaGenerateId.json"));
    }).getMessage(), containsString("Unrecognized field \"generateId\""));
  }

  @Test
  public void failsWhenPkColumnName() throws Exception {
    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    assertThat(assertThrows(UnrecognizedPropertyException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaPkColumnName.json"));
    }).getMessage(), containsString("Unrecognized field \"pkColumnName\""));
  }

  @Test
  public void pkColumnName() {
    assertThat(new Table().getPkColumnName(), is("id"));
    assertThat(new View().getPkColumnName(), is("id"));
  }

  @Test
  public void failsWhenPopulateJsonWithId() throws IOException, TemplateException {

    SchemaMaker schemaMaker = new SchemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2");
    IOException e = assertThrows(IOException.class, () -> {
      schemaMaker.setSchema(schema("templates/db_scripts/schemaPopulateJsonWithId.json"));
    });
    assertThat(tidy(e.getMessage()), containsString("Unrecognized field \"populateJsonWithId\""));
  }

  @Test
  public void lowerUnaccentIndex() throws IOException, TemplateException {

    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.CREATE,
      "mod-foo-0.2.1-SNAPSHOT.2", "mod-foo-18.2.1-SNAPSHOT.2",
      "templates/db_scripts/caseinsensitive.json");
    assertThat(tidy(schemaMaker.generateDDL()), containsString(
        "CREATE INDEX IF NOT EXISTS item_title_idx ON harvard_circ.item ' "
        + "|| $rmb$(left(lower(f_unaccent(jsonb->>'title')),600))$rmb$)"));
  }

  @Test
  public void scriptExistsAndDoesntUpgrade() throws IOException, TemplateException {

    SchemaMaker schemaMaker = schemaMaker("harvard", "circ", TenantOperation.UPDATE,
{'fraction_non_alphanumeric': 0.1430912687199774, 'fraction_numerical': 0.01192427239333145, 'mean_word_length': 5.286323268206039, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 85, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 6}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3637761', 'n_tokens_mistral': 5546, 'n_tokens_neox': 5464, 'n_words': 893}