let
  pagesize = 10000,
  #"Access token" = "**access token**",
  HasLimit = (url as text) as logical =>
    if Text.Contains(url, "?") then
      let
        queryIdx = Text.PositionOf(url, "?"),
        query = Text.Middle(url, queryIdx)
      in
        Text.Contains(query, "limit")
    else
      false,
  GetNextLink = (response as binary, optional offset as number) =>
    let
      stepSize = pagesize,
      json = Json.Document(response),
      newOffset = if offset <> null and offset > 0 then offset + stepSize else stepSize
    in
      if List.IsEmpty(json) then null else newOffset,

  CWContents = (url as text, optional offset as number, optional access_token as text) =>
    let
      access = if access_token <> null then access_token else #"Access token",
      query = if HasLimit(url) then [] else [limit = Number.ToText(pagesize)],
      queryWithOffset = if offset <> null and offset > 0 then Record.Combine({query, [offset=Number.ToText(offset)]}) else query,
      content = Web.Contents("https://uwomgevingnaam.clockwise.info/api/v2", [
        RelativePath=url,
        Query=queryWithOffset,
        Headers=[#"Accept" = "application/json",
                 #"Authorization" = "Bearer " & access]
      ]),
      newOffset = GetNextLink(content, offset),
      json = Json.Document(content),
      table = Table.FromList(json, Splitter.SplitByNothing())
    in
      if List.IsEmpty(json) then null else table meta [Next=newOffset],
  Table.GenerateByPage = (getNextPage as function) as table =>
    let
      listOfPages = List.Generate(
        () => getNextPage(null),
        (lastPage) => lastPage <> null,
        (lastPage) => getNextPage(lastPage)
      ),
      tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
      firstRow = tableOfPages{0}?
    in
      if(firstRow = null) then
        Table.FromRows({})
      else
        Value.ReplaceType(
          Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
          Value.Type(firstRow[Column1])
        ),
  PagedTable = (url as text, optional access_token as text) => Table.GenerateByPage((previous) =>
    let
      next = if(previous <> null) then Value.Metadata(previous)[Next] else null,
      current = if (previous <> null and next = null) then null else CWContents(url, next, access_token),
      link = if(current <> null) then Value.Metadata(current)[Next] else null
    in current meta [Next=link]
  ),
  ParsedTable = (url as text, optional rename as record) =>
    let
      access_token = #"Access token",
      recordtable = if HasLimit(url) then CWContents(url, null, access_token) else PagedTable(url, access_token),
      fieldnames = Record.FieldNames(Table.Column(recordtable, "Column1"){0}),
      defaultName = if rename <> null then Record.FieldOrDefault(rename, "_", "") else "",
      newFieldnames = if rename <> null then List.Transform(fieldnames, (name) =>
        let
          defaultFieldname = defaultName & name
        in
          Record.FieldOrDefault(rename, name, defaultFieldname)
      ) else null,
      result = Table.ExpandRecordColumn(recordtable, "Column1", fieldnames, newFieldnames),
      id = if rename <> null then Record.FieldOrDefault(rename, "ID") else null
    in
      if id <> null then Table.AddKey(result, {id}, true) else result,
  parseQueryParameters = (queryParameters as list) as text => let
    typedQueryParameters = Value.ReplaceType(queryParameters, type {nullable text}),
    strippedQueryParameters = List.Select(typedQueryParameters, each _ <> null),
    trimmedQueryParameters = List.Transform(strippedQueryParameters, each Text.TrimStart(_, "?")),
    parameters = List.Transform(trimmedQueryParameters, each Text.Split(_, "&")),
    splittedParameters = List.Transform(parameters, (list) => List.Transform(list, each Text.Split(_, "="))),
    namesAndValues = List.Transform(splittedParameters, List.Zip),
    namesAndSplittedValues = List.Transform(namesAndValues, (pair) => {pair{0}, List.Transform(pair{1}, each Text.Split(_, ","))}),
    records = List.Transform(namesAndSplittedValues, each Record.FromList(_{1}, _{0})),
    updateField = (#"record" as record, colname as text) =>
      (lst) =>
        let
          newList = Record.Field(#"record", colname)
        in
          if lst = null then newList else lst & newList,
    newRecord = List.Accumulate(records, [], (memo, current) =>
      let updateFields = List.Transform(Record.FieldNames(current), (name) => {name, updateField(current, name)})
      in Record.TransformFields(memo, updateFields, MissingField.UseNull)
    ),
    queryRecord = Record.TransformFields(newRecord, List.Transform(Record.FieldNames(newRecord),
      (name) => {name, each Text.Combine(_, ",")})
    )
  in Uri.BuildQueryString(queryRecord),
  Table.ToNavigationTable = (
    table as table,
    keyColumns as list,
    nameColumn as text,
    dataColumn as text,
    itemKindColumn as text,
    itemNameColumn as text,
    isLeafColumn as text
  ) as table =>
    let
      tableType = Value.Type(table),
      newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
      [
        NavigationTable.NameColumn = nameColumn,
        NavigationTable.DataColumn = dataColumn,
        NaviagationTable.ItemKindColumn = itemKindColumn,
        Preview.DelayColumn = itemNameColumn,
        NavigationTable.isLeafColumn = isLeafColumn
      ],
      navigationTable = Value.ReplaceType(table, newTableType)
    in
      navigationTable,
  CreateNavTable = (base as table) as table =>
    let
      withItemKind = Table.AddColumn(base, "ItemKind", each "Table", type text),
      withItemName = Table.AddColumn(withItemKind, "itemName", each "Table", type text),
      withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical)
    in
      Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf"),
  RelationShips = (rates as nullable logical, queryparameters as record) =>
    let
      extrafields =  if rates = true then "fields=salesfields,purchasefields" else null,
      extraparameters = (#"type" as text) => Record.FieldOrDefault(queryparameters, #"type"),
      hoursIgnoreFields = {"resource_parent_id", "resource_status", "employee_id", "employee_name",
        "department_name_path", "department_id_path", "department_code_path", 
        "customer_id_path", "customer_name_path", "customer_code_path",
        "project_id_path", "project_code_path", "project_name_path"
      },
      resourcesIgnoreFields = {"employee_name", "department_id_path", "department_name_path", "department_code_path",
        "customer_id_path", "customer_name_path", "customer_code_path",
        "project_id_path", "project_code_path", "project_name_path",
        "reference_number", "project_type_inherited", "projectmanagers"
      },
      HoursFlat = ParsedTable("/report/flat/hours?" & parseQueryParameters({
        "fields=defaultfields,hourstatusfields,otherfields",
        extrafields,
        "fields=" & Text.Combine(List.Transform(hoursIgnoreFields, each "!" & _), ","),
        extraparameters("Hour")
      }), [
        _ = "hours.",
        ID = "hours.id"
      ]),
      ResourcesFlat = ParsedTable("/report/flat/resources?" & parseQueryParameters({
        "fields=defaultfields,projectfields",
        extrafields,
        "fields=" & Text.Combine(List.Transform(resourcesIgnoreFields, each "!" & _), ","),
        extraparameters("Resource")
      }), [
        _="resources.",
        resource_parent_id="resources.project_id",
        id = "resources.resource_id",
        ID = "resources.resource_id"
      ]),
      ProjectsFlat = ParsedTable("/report/flat/projects?" & parseQueryParameters({
        "fields=defaultfields,projectfields",
        extrafields,
        extraparameters("Project")
      }), [
        _="projects.",
        id = "projects.project_id",
        ID = "projects.project_id"
      ]),
      EmployeesFlat = ParsedTable("/report/flat/employees?" & parseQueryParameters({
        "fields=defaultfields,employeefields",
        extrafields,
        extraparameters("Employee")
      }), [
        _ = "employees.",
        id = "employees.employee_id",
        ID = "employees.employee_id"
      ]),
      types = {
        {"date", type date},
        {"end_date", type date},
        {"start_date", type date},
        {"hourstatus_modified_at", type datetime},
        {"start", type time},
        {"project_id", Int64.Type},
        {"employee_id", Int64.Type},
        {"resource_id", Int64.Type},
        {"id", Int64.Type}
      },

      oldTypes = (table as table) as list =>
        List.Select(List.Transform(Table.ColumnNames(table), (name) =>
          {name, Value.Type(List.First(List.Select(
            Table.Column(table, name),
            each _ <> null
          ),""))}
        ), each not Type.Is(type null, _{1})),

      convertTypes = (columnPrefix as text, table as table) as table => let
        castedTable = Table.TransformColumnTypes(table, oldTypes(table))
        in List.Accumulate(types, castedTable, (current, typeEntry) => let
            newTypeEntry = {columnPrefix & typeEntry{0}, typeEntry{1}},
            transformedTable = Table.TransformColumns(current, {newTypeEntry{0}, (txt) =>
              if txt = null or (Type.Is(Value.Type(txt), type text) and Text.StartsWith(txt, "0000-00-00")) then null  else txt},
            null, MissingField.Ignore)
          in
            try Table.TransformColumnTypes(transformedTable, {newTypeEntry}) otherwise transformedTable),

      employees = convertTypes("employees.", EmployeesFlat),
      projects = convertTypes("projects.", ProjectsFlat),
      resources = convertTypes("resources.", ResourcesFlat),
      hours = convertTypes("hours.", HoursFlat),
      base = #table(type table [ Name = Text.Type, Data = Table.Type ], {
        { "Employees", employees },
        { "Projects", projects },
        { "Resources", resources},
        { "Hours", hours}
      }),

      nav = CreateNavTable(base)
    in
      nav,
  Contents = (optional #"Verkrijg tarieven" as logical, optional hourParameters as text, optional resourceParameters as text,
    optional projectParameters as text, optional employeeParameters as text) =>
      RelationShips(#"Verkrijg tarieven", [Hour = hourParameters, Resource = resourceParameters, Project = projectParameters, Employee = employeeParameters]),
  Bron = Contents()
in
  Bron


