let
FuncType = type function (
    apiKey as (type text meta [
        Documentation.FieldCaption = "Токен доступа к сервису",
        Documentation.FieldDescription = "Токен, который сгенерировал сервис BiTeam" ]), 
    sqlServer as (type text meta [ 
        Documentation.FieldCaption = "Имя сервера с ms sql базой 1с",
        Documentation.FieldDescription = "Здесь нужно указать сервер где лежат базы 1с",
        Documentation.SampleValues = {"serv\sqlexpress"}]),
    sqlDb as (type text meta [
        Documentation.FieldCaption = "Имя базы 1с",
        Documentation.FieldDescription = "Здесь нужно указать имя базы 1с",
        Documentation.SampleValues = {""}])
		) 
as table meta [
        Documentation.Name = "Bi-team 1c connector"],
baseUrl = "http://connector.bi-team.ru/api",  // ; "http://192.168.1.76:5010/api";  // "http://192.168.20.254:5010/api"; // 
ItemHierarchyDATABASE = "Database", ItemHierarchyFOLDER = "Folder", ItemHierarchyTABLE = "Table", ItemHierarchyNONE = "None", ItemHierarchyEND = "End",
ItemHierarchy = {ItemHierarchyNONE, ItemHierarchyDATABASE, ItemHierarchyFOLDER, ItemHierarchyTABLE, ItemHierarchyEND },
GetMetaObjectsHeaders = (apiKey as text) as table =>
    let
        get = Http.Request("metamodel/headers", apiKey), 
        json = Json.Document(get, null),
        tableWithRecords = Table.FromList(json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        result = if Table.IsEmpty(tableWithRecords) then error "Сервис вернул пустой результат. Вероятно вы не загрузили метаданные"
                                                    else Table.ExpandRecordColumn(tableWithRecords, "Column1", {"label","source", "children", "isLeaf", "kind"}),
        dbPathed = Table.AddColumn(result, "dbPath", (row) => { "" }), //, //(row) => { row[label] }
        buffered = Table.Buffer(dbPathed)
    in  buffered,
GetModelParams = (apiKey as text) as record =>
    let
        get = Http.Request("metamodel/params", apiKey), 
        json = Json.Document(get, null)
    in  json,
GetObjectFromModel = (model as list, dbPath as list) as table => 
    let
        childrenTable = Table.FromList(model, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        result = Table.ExpandRecordColumn(childrenTable, "Column1", {"label", "children", "isLeaf", "kind" }),
        dbPathed = Table.AddColumn(result, "dbPath", (row) => dbPath & { row[label] } )
    in  dbPathed,       
GetFieldsMetaFromModel = (parentRow as record) as table => 
    let
        childrenTable = Table.FromList(parentRow[children], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        result = if (not Table.IsEmpty(childrenTable)) then let
                expanded = Table.ExpandRecordColumn(childrenTable, "Column1", {"label", "references","metadataType", "dataType" }),
                expanded2 =   Table.TransformColumns(Table.ExpandListColumn(expanded, "references"), { "references", (v) => if v <> null then v[label] else " "})
            in Table.RenameColumns(expanded2, { {"label", "Поле"}, {"references", "ссылка"}, {"metadataType", "тип"}, {"dataType", "тип в базе"}})
                else #table({"Нет метаданных"}, {})
    in result,
GetFieldsMetaFromApi = (parentRow as record, apiKey as text) as table => 
    let
        uriPath = "metamodel/table" & Text.Combine(parentRow[dbPath], "/"),
        //_uriPath = Diagnostics.LogValue("Path: ", uriPath),
        get = Http.Request(uriPath , apiKey), 
        json = Json.Document(get, null),
        t = GetFieldsMetaFromModel(json)
    in t,
GetNextHierarchyItem = (prevItem as number) as text => if ItemHierarchy{prevItem} = ItemHierarchyEND then ItemHierarchyEND else  ItemHierarchy{prevItem + 1},
MakeInnerTableOfMetatableHeader = () =>  #table({"label", "isLeaf", "ItemKind", "ItemName", "Data"},{}),
MakeInnerTableOfMetatable = (header as table, parentRow as record, tableName as text, dataFunc, metadataTab as table, kind as text) => Table.Combine({header, 
    Table.FromRows({{tableName, true, kind, kind, dataFunc(parentRow)},
                   {tableName & "Метаинформация", true, kind, kind, metadataTab}},
                   {"label", "isLeaf", "ItemKind", "ItemName", "Data"})
}),
BuildNavTableForTableItem = (parentRow as record,  dataViewFunc as function, apiKey as text) as table => 
    let
        tableElems = parentRow[children],  
        nestedTablesList = List.Select(tableElems, (f) => (f[kind] = 5) or (f[kind]) = 8 ),
        nestedTables = List.Accumulate(nestedTablesList, MakeInnerTableOfMetatableHeader(), (header, current) => let
            pr = GetObjectFromModel({current}, parentRow[dbPath]){0}
                in MakeInnerTableOfMetatable(header, pr, pr[label] & " ", dataViewFunc, GetFieldsMetaFromApi(pr, apiKey),  "DefinedName")), 
        t = MakeInnerTableOfMetatable(MakeInnerTableOfMetatableHeader(), parentRow, parentRow[label],  dataViewFunc, GetFieldsMetaFromApi(parentRow, apiKey), "Table"),   
        nt = Table.Combine({t, nestedTables})
    in  TableToNavigationTable(nt, {"label"}, "label", "Data", "ItemKind", "ItemName", "isLeaf"),
NavTableFromMeta= (parentRow as record, itemKind as text, apiKey as text, database as text, driver as text) as table =>
    let
        nextkind = GetNextHierarchyItem(parentRow[kind]),
        navTable = (row) => if List.IsEmpty(row[children]) then "" else  @NavTableFromMeta(row, "Table",  apiKey, database, driver ),
        dataTable = (row) => GetTableView(row, apiKey, database, driver), // #table({},{}), 
        dataMetaTable = (row) => BuildNavTableForTableItem(row, dataTable, apiKey),
        metaobject = GetObjectFromModel(parentRow[children], parentRow[dbPath] ),
        isLeafDeleted = Table.RemoveColumns(metaobject, "isLeaf"),
        isLeaf = if nextkind = ItemHierarchyTABLE then true else false, 
        t0 = Table.AddColumn(isLeafDeleted, "isLeaf", (row) => false ), 
        dataCol = if isLeaf then dataMetaTable else navTable,
        t1 = Table.AddColumn(t0, "Data", dataCol),
        t2 = Table.AddColumn(t1, "ItemKind", (row) => itemKind),
        t3 = Table.AddColumn(t2, "ItemName", (row) => itemKind)
    in  TableToNavigationTable(t3, {"label"}, "label", "Data", "ItemKind", "ItemName", "isLeaf"),
GetRootNavTable = (apiKey as text, database as text) as table =>
    let 
        metaObjectsTable = GetMetaObjectsHeaders(apiKey),
        modelParams = GetModelParams(apiKey),
        t0 = Table.AddColumn(metaObjectsTable, "Data", (row) => NavTableFromMeta(row, "Folder",  apiKey, database, modelParams[odbcDriver] )), 
        t1 = Table.AddColumn(t0, "ItemKind", (row) => "Database"),
        t2 = Table.AddColumn(t1, "ItemName", (row) => "Database"),
        navTable = TableToNavigationTable(t2, {"label"}, "label", "Data", "ItemKind", "ItemName", "isLeaf")
    in navTable,
GetTableView = (row as record, apiKey as text, database as text, driver as text) =>
    let
      innerView = (state as record) => Table.View(null, [
        GetType = () => Value.Type(GetTableContent(row,  apiKey, database, driver, state & [pagesize="1"])),
        GetRows = () => GetTableContent(row, apiKey, database, driver, state),
        OnTake = (count as number) => 
            let
                newState = state & [pagesize=Number.ToText(count)]
            in @innerView(newState),
        OnSkip = (count as number) =>
            let 
                newState = state & [position=Number.ToText(count)]
            in @innerView(newState),
        OnSelectColumns = (columns as list) =>
            let
                strFields = List.Accumulate(columns, null, (str, it) => if (str = null) then it else (str & "," & it)),
                newState = state & [fields=strFields]
            in  @innerView(newState),
        OnSort = (order as list) =>
            let
                sorting = List.Transform(order, (o) => 
                    let
                        column = o[Name],
                        order = o[Order],
                        orderText = if (order = Order.Ascending) then "asc" else "desc"
                    in  column & " " & orderText),
                orderByStr = Text.Combine(sorting, ", "),
                newState = state & [orderby=orderByStr]
            in  @innerView(newState)
        ])
    in
        innerView([]),
Http.Request = (uri as text, apiKey as text, optional query as record)  => 
    let
        headers = [
            #"X-Apikey" = apiKey,
            #"X-ConnectorVersion" = "2.1"
        ],
        q = if query=null then [] else query,
        content = Web.Contents(baseUrl, [RelativePath = uri, Query = q, Headers = headers]) //, ApiKeyName = "ApiKey"
    in content,
GetSelectForTable = (uriPath as text, apiKey as text, optional query as record) as text => 
    Json.Document(Http.Request("buildquery" & uriPath, apiKey, query)),
GetTableContent = (row as record, apiKey as text, database as text, driver as text, optional query as record) as table => 
    let 
       uriPath = Text.Combine(row[dbPath], "/"),
       sql = GetSelectForTable(uriPath, apiKey, query),
       db = Odbc.Query("Driver=" & driver & ";" & database, sql)     //"Driver={SQL Server Native Client 11.0};Server=win2016\sqlexpress;Database=" & database & ";"
       //db = Odbc.Query("dsn=fake1c", sql)
       //db = Odbc.Query("dsn=fake1c", "Select * from " & row[source])
       //db = Sql.Database("WIN2016\SQLEXPRESS", "fake1c")
       //result = db{[Schema="dbo",Item=tableName]}[Data]
    in db,
TableToNavigationTable = (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,
            NavigationTable.ItemKindColumn = itemKindColumn, 
            Preview.DelayColumn = itemNameColumn, 
            NavigationTable.IsLeafColumn = isLeafColumn
        ],
        navigationTable = Value.ReplaceType(table, newTableType)
    in  navigationTable,
	func = (apiKey as text, sqlServer as text, sqlDb as text) => 
    GetRootNavTable(apiKey, "Server=" & sqlServer & ";Database=" & sqlDb  ),
	BiTeamConnectorVer2Data  = Value.ReplaceType(func, FuncType) 
in BiTeamConnectorVer2Data