/* Insert API-Key below!*/
const api_key = "XXXX-XXXX"

const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheet = ss.getActiveSheet()

function onOpen() {
  let ui = SpreadsheetApp.getUi()
  ui.createMenu("North Data")
    .addItem("Update all", "fillSheet")
    .addItem("Update new Entry", "updateNewEntry")
    .addItem("Premade Template - German", "loadExampleGer")
    .addItem("Premade Template - English", "loadExampleEng")
    .addToUi()
}

function loadExampleEng() {
  sheet.clear()
  sheet.appendRow([
    "Company",
    "City",
    "Status",
    "Register",
    "Address",
    "Lei",
    "EUID",
    "Phone",
    "Fax",
    "Email",
    "Website",
    "VAT Id",
    "Industry Code",
    "Total assets",
    "Earnings",
    "Revenue",
    "Return on sales",
    "Equity",
    "Equity ratio",
    "Return on equity",
    "Employee number",
    "Revenue per employee",
    "Taxes",
    "Tax ratio",
    "Receivables",
    "Liabilities",
    "Pension provisions",
    "North Data Url",
    "CEO",
    "Managing Director",
  ])
  extraStep()
}

function loadExampleGer() {
  sheet.clear()
  sheet.appendRow([
    "Firma",
    "Ort",
    "Status",
    "Register",
    "Adresse",
    "Lei",
    "EUID",
    "Telefon",
    "Fax",
    "E-Mail",
    "Website",
    "USt.-Id.",
    "Wirtschaftszweig",
    "Bilanzsumme",
    "Gewinn",
    "Umsatz",
    "Umsatzrendite",
    "Eigenkapital",
    "EK-Quote",
    "EK-Rendite",
    "Mitarbeiterzahl",
    "Umsatz pro Mitarbeiter",
    "Steuern",
    "Steuerquote",
    "Forderungen",
    "Verbindlichkeiten",
    "Pensionsrückstellung",
    "North Data Url",
    "Vorstandsvorsitzender",
    "Geschäftsführer",
  ])
  extraStep()
}

function extraStep() {
  sheet.appendRow(["Audi AG", "Ingolstadt"])
  sheet.appendRow(["Daimler AG", "Stuttgart"])
  sheet.appendRow(["ECE FLATMEDIA GMBH", "Hamburg"])
  sheet.appendRow(["TAG Immobilien AG", "Hamburg"])
  sheet.appendRow(["LAUB ELEKTROTECHNIK GMBH", "Hamburg"])
  sheet.appendRow(["Keine Firma", "Kein Ort"])
  sheet.appendRow(["Kaffeeyoda UG", "Hamburg"])
  sheet.appendRow(["Con Pane GmbH", "Lübeck"])
  sheet.appendRow(["OstseeSparkasse Rostock", "Rostock"])
  sheet.appendRow([
    "Ingenieurbüro für Umweltschutz und Technik, IUT GmbH & Co. KG",
    "Flensburg",
  ])
  sheet.appendRow(["DeMaConsulting GmbH & Co. KG", "Hamburg"])
  sheet.appendRow(["Robert Bosch GmbH", "Gerlingen"])

  sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .setBackground("#006d87")
    .setFontColor("white")
  sheet.autoResizeColumns(1, sheet.getLastRow())
}

function findCompany(companyName, companyAddress) {
  let language = "en"
  if (sheet.getRange(1, 2).getValue() == "Ort") {
    language = "de"
  }
  let response = UrlFetchApp.fetch(
    "https://www.northdata.com/_api/company/v1/company?address=" +
      encodeURIComponent(companyAddress) +
      "&name=" +
      encodeURIComponent(companyName) +
      "&financials=true&relations=true&extras=true&language=" +
      language +
      "&fuzzyMatch=true&api_key=" +
      api_key,
    { muteHttpExceptions: true }
  )

  let json = response.getContentText()
  let parsedJson = JSON.parse(json)

  if (response.getResponseCode() == 404 || parsedJson["message"] != null) {
    Logger.log(parsedJson)
  } else {
    Logger.log(companyName + " found")
    return parsedJson
  }
}

function fillSheet() {
  sheet
    .getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn())
    .clear({ formatOnly: true })

  for (row = 2; row <= sheet.getLastRow(); row++) {
    fillCompany(row)
  }
  sheet.autoResizeColumns(1, ss.getLastColumn())
}

function updateNewEntry() {
  for (row = 2; row <= sheet.getLastRow(); row++) {
    if (sheet.getRange(row, 3).isBlank()) {
      sheet
        .getRange(row, 1, 1, sheet.getLastColumn())
        .clear({ formatOnly: true })
      fillCompany(row)
    }
  }
}

function fillCompany(row) {
  let companyName = sheet.getRange(row, 1).getValue()
  let companyAddress = sheet.getRange(row, 2).getValue()

  let companyInfo = findCompany(companyName, companyAddress)

  if (companyInfo) {
    fillRow(row, companyInfo)
    sheet.getRange(row, 1, 1, 2).setBackground("#cdcdcd").setFontColor("black")
  } else {
    sheet.getRange(row, 1, 1, sheet.getLastColumn()).setBackground("#FFC7CE")
  }
}

function fillRow(row, companyInfo) {
  for (column = 3; column <= sheet.getLastColumn(); column++) {
    let information = informationTranslater(
      sheet.getRange(1, column).getValue()
    )
    let value = searchCriteria(companyInfo, information, row, column)
    sheet.getRange(row, column).setValue(value)
  }
}
/* Changing column name into key */
function informationTranslater(information) {
  switch (information) {
    case "Adresse":
    case "Address":
      return "address"
    case "Telefon":
    case "Phone":
      return "phone"
    case "USt.-Id.":
    case "VAT Id":
      return "vatId"
    case "E-Mail":
    case "Email":
      return "email"
    case "Website":
      return "url"
    case "Wirtschaftszweig":
    case "Industry Code":
      return "wz"
    case "Bilanzsumme":
    case "Total assets":
      return "BalanceTotal"
    case "Gewinn":
    case "Earnings":
      return "Earnings"
    case "Umsatz":
    case "Revenue":
      return "Revenue"
    case "Umsatzrendite":
    case "Return on sales":
      return "ReturnOnSales"
    case "Eigenkapital":
    case "Equity":
      return "Equity"
    case "EK-Quote":
    case "Equity ratio":
      return "EquityRatio"
    case "EK-Rendite":
    case "Return on equity":
      return "ROE"
    case "Mitarbeiterzahl":
    case "Employee number":
      return "Employees"
    case "Umsatz pro Mitarbeiter":
    case "Revenue per employee":
      return "RevPerEmpl"
    case "Steuern":
    case "Taxes":
      return "Tax"
    case "Steuerquote":
    case "Tax ratio":
      return "TaxRatio"
    case "Forderungen":
    case "Receivables":
      return "Receivables"
    case "Verbindlichkeiten":
    case "Liabilities":
      return "Liabilities"
    case "Pensionsrückstellung":
    case "Pension provisions":
      return "PensionProvisions"
    case "North Data Url":
      return "northDataUrl"
    case "Geschäftsführer":
    case "Managing Director":
      return "Geschaeftsfuehrer"
    case "Vorstandsvorsitzender":
    case "CEO":
      return "Vorstandsvorsitzender"
    default:
      return information.toLowerCase()
  }
}

function searchCriteria(companyInfo, information, row, column) {
  const companyInformation = companyInfo[information]
  const companyRegister = companyInfo["register"]
  switch (information) {
    case "lei":
    case "northDataUrl":
      return companyInformation
    case "address":
      return companyInformation["formattedValue"]
    case "status":
      coloringStatus(companyInfo[information], row, column)
      return companyInformation
    case "register":
      if(companyInformation == null){
        return ""
      }else{
        return companyRegister["id"] + ", " + companyRegister["city"]
      }
    case "Employees":
    case "BalanceTotal":
    case "Earnings":
    case "Revenue":
    case "ReturnOnSales":
    case "Equity":
    case "EquityRatio":
    case "ROE":
    case "RevPerEmpl":
    case "Tax":
    case "TaxRatio":
    case "Cash":
    case "Receivables":
    case "Liabilities":
    case "PensionProvisions":
      return financialSearch(companyInfo, information)
    case "Vorstandsvorsitzender":
    case "Vorstand":
    case "Geschaeftsfuehrer":
      return relatedSearch(companyInfo, information)
    case "phone":
    case "fax":
    case "email":
    case "url":
    case "vatId":
    case "wz":
      return extraSearch(companyInfo, information)
    default: 
      return ""
  }
}

function financialSearch(companyInfo, information) {
  let found = false
  const financialValue = companyInfo["financials"]
  if (financialValue) {
    const financialItems = financialValue["items"]
    if (financialItems.length >= 1) {
      for (i = 0; i < financialItems.length && !found; i++) {
        const financialItem = financialItems[i]
        if (information == financialItem["id"]) {
          return financialItem["formattedValue"]
        }
      }
    }
  }
}

function extraSearch(companyInfo, information) {
  let found = false
  const extra = companyInfo["extras"][0]
  if (extra) {
    const extraItems = extra["items"]
    if (extraItems.length >= 1) {
      for (i = 0; i < extraItems.length && !found; i++) {
        const extraItem = extraItems[i]
        if (information == extraItem["id"]) {
          return extraItem["value"]
        }
      }
    }
  }
}

function relatedSearch(companyInfo, information) {
  const relatedPersons = companyInfo["relatedPersons"]
  if (relatedPersons) {
    const items = relatedPersons["items"]
    if (items.length >= 1) {
      for (i = 0; i < items.length - 1; i++) {
        const item = items[i]
        const roles = item["roles"]
        for (j = 0; j < roles.length; j++) {
          let roleType = roles[j]["type"]
          if (information == roleType) {
            const personName = item["person"]["name"]
            return personName["firstName"] + " " + personName["lastName"]
          }
        }
      }
    }
  }
}

function coloringStatus(result, row, column) {
  const range = sheet.getRange(row, column)
  switch (result) {
    case "active":
      range.setBackground("lightgreen")
      break
    case "liquidation":
      range.setBackground("yellow")
      break
    case "terminated":
      range.setBackground("red")
      break
  }
}
