let

//--------------------------------------
// 1. AUTHENTICATION
//--------------------------------------

ClientID = ClientId,
ClientSecret = ClientSecret,
OrganizationID = OrganizationId,

WorkspaceName = WorkspaceName,
TableName = "Company Profiles",

graphqlUrl = "https://api.pitchly.com/graphql",

//----------------------------------
// AUTHENTICATION
//----------------------------------

TokenResponse =
    Web.Contents(
        "https://platform.pitchly.com",
        [
            RelativePath="api/oauth/token",
            Content=Text.ToBinary(
                Uri.BuildQueryString([
                    grant_type="client_credentials",
                    client_id=ClientID,
                    client_secret=ClientSecret,
                    organization_id=OrganizationID
                ])
            ),
            Headers=[
                #"Content-Type"="application/x-www-form-urlencoded",
                #"Accept"="application/json"
            ]
        ]
    ),

TokenJson = Json.Document(TokenResponse),
accessToken = TokenJson[access_token],


//----------------------------------
// GET WORKSPACE + TABLE LIST
//----------------------------------

workspaceQuery =
"
query organization {
  organization {
    workspaces {
      id
      name
      tables {
        id
        name
      }
    }
  }
}
",

workspaceBody = Json.FromValue([query=workspaceQuery]),

workspaceResponse =
    Web.Contents(
        graphqlUrl,
        [
            Headers=[
                #"Content-Type"="application/json",
                #"Authorization"="Bearer " & accessToken
            ],
            Content=workspaceBody
        ]
    ),

workspaceJson = Json.Document(workspaceResponse),

workspaces = workspaceJson[data][organization][workspaces],

workspaceTable =
    Table.FromList(workspaces, Splitter.SplitByNothing(), {"workspace"}),

expandWorkspace =
    Table.ExpandRecordColumn(
        workspaceTable,
        "workspace",
        {"id","name","tables"},
        {"workspace_id","workspace_name","tables"}
    ),

expandTables =
    Table.ExpandListColumn(expandWorkspace,"tables"),

tables =
    Table.ExpandRecordColumn(
        expandTables,
        "tables",
        {"id","name"},
        {"table_id","table_name"}
    ),

selectedTable =
    Table.SelectRows(
        tables,
        each [workspace_name]=WorkspaceName and [table_name]=TableName
    ),

tableId = selectedTable{0}[table_id],


//----------------------------------
// GRAPHQL PAGE FUNCTION
//----------------------------------

GetPage = (cursor as nullable text) =>

let

query =
"
query ($tableId: ID!, $cursor: String) {
  recordsConnection(tableId: $tableId, after: $cursor) {
    edges {
      node {
        id
        fields {
          fieldId
          fieldName
          stringValue
        }
      }
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}
",

body =
    Json.FromValue([
        query=query,
        variables=[
            tableId=tableId,
            cursor=cursor
        ]
    ]),

response =
    Web.Contents(
        graphqlUrl,
        [
            Headers=[
                #"Content-Type"="application/json",
                #"Authorization"="Bearer " & accessToken
            ],
            Content=body
        ]
    ),

json = Json.Document(response),

connection = json[data][recordsConnection]

in
    connection,


//----------------------------------
// PAGINATION LOOP
//----------------------------------

Pages =
    List.Generate(

        ()=>[
            Result = GetPage(null),
            Cursor = null
        ],

        each [Result] <> null,

        each
            [
                Cursor = [Result][pageInfo][endCursor],
                Result =
                    if [Result][pageInfo][hasNextPage]
                    then GetPage([Cursor])
                    else null
            ],

        each [Result][edges]

    ),

allEdges = List.Combine(Pages),

//----------------------------------
// FLATTEN GRAPHQL STRUCTURE
//----------------------------------

edgesTable =
    Table.FromList(allEdges, Splitter.SplitByNothing(), {"edge"}),

expandNode =
    Table.ExpandRecordColumn(edgesTable,"edge",{"node"}),

expandNode2 =
    Table.ExpandRecordColumn(expandNode,"node",{"id","fields"}),

expandFieldsList =
    Table.ExpandListColumn(expandNode2,"fields"),

expandFields =
    Table.ExpandRecordColumn(
        expandFieldsList,
        "fields",
        {"fieldName","stringValue"}
    ),


//----------------------------------
// PIVOT FIELDIDS
//----------------------------------

pivot =
    Table.Pivot(
        expandFields,
        List.Distinct(expandFields[fieldName]),
        "fieldName",
        "stringValue",
        List.First
    ),

#"Changed column type" = Table.TransformColumnTypes(pivot, {{"id", type text}}),
#"Flatten All Lists" = Table.TransformColumns(
    #"Changed column type",
    List.Transform(
        Table.ColumnNames(#"Changed column type"),
        (colName) =>
            {colName, each if Value.Is(_, List.Type) then Text.Combine(List.Transform(_, Text.From), ", ") else _, type text}
    )
)

in
    #"Flatten All Lists"