from internal.queries import rds_query


def get_collections(endpoint, req, event):
    """          {
            "version": "2018-05-29",
            "operation": "Invoke",
            "payload": {
              "field": "getCollections",
              "endpoint":"RDS",
              "userSub" : $util.toJson($ctx.identity.sub),
              #if( !$context.arguments.parentId || $util.isNullOrEmpty($context.arguments.parentId))
              "queries": [
                {
                  "query": "WITH tree (id, name, status, source, \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\") AS ( (SELECT DISTINCT C.id, name, status, source, parent_id AS \"parentId\", TO_CHAR(C.date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\", collection_type AS \"type\", (SELECT count(*) FROM <schema>.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM <schema>.collection C WHERE collection_type IN ('source','segment','collection set')) ) SELECT id, name, status, \"parentId\" AS \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\" FROM tree"
                }
              ]
              #else
              "queries": [
                {
                  "query": "WITH tree (id, name, status, source, \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\") AS ( (SELECT DISTINCT C.id, name, status, source, parent_id AS \"parentId\", TO_CHAR(C.date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\", collection_type AS \"type\", (SELECT count(*) FROM <schema>.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM <schema>.collection C WHERE parent_id=%(parentId)s AND collection_type IN ('source','segment','collection set')) ) SELECT id, name, status, \"parentId\" AS \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\" FROM tree",
                  "params": {"parentId" : $context.arguments.parentId}
                }
              ]
              #end
            }
          }
"""
    schema = (req['alliance_schema'] if 'alliance_schema' in req else None) or req['workspace_schema']
    if 'parentId' in req and req['parentId']:
        return rds_query(f"WITH tree (id, name, status, source, \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\") AS ( (SELECT DISTINCT C.id, name, status, source, parent_id AS \"parentId\", TO_CHAR(C.date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\", collection_type AS \"type\", (SELECT count(*) FROM {schema}.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM {schema}.collection C WHERE parent_id=%(parentId)s AND collection_type IN ('source','segment','collection set')) ) SELECT id, name, status, \"parentId\" AS \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\" FROM tree",
                         {"parentId": req['parentId']})
    else:
        return rds_query(f"WITH tree (id, name, status, source, \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\") AS ( (SELECT DISTINCT C.id, name, status, source, parent_id AS \"parentId\", TO_CHAR(C.date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\", collection_type AS \"type\", (SELECT count(*) FROM {schema}.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM {schema}.collection C WHERE collection_type IN ('source','segment','collection set')) ) SELECT id, name, status, \"parentId\" AS \"parentId\", \"dateCreated\", \"type\", \"totalProfiles\" FROM tree")


def get_collection(endpoint, req, event):
    """          {
            "version": "2018-05-29",
            "operation": "Invoke",
            "payload": {
              "field": "getCollection",
              "endpoint": "RDS",
              "userSub" : $util.toJson($ctx.identity.sub),
              "queries": [
                {
                  "query": "SELECT id, name, status, parent_id AS \"parentId\", TO_CHAR(date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\" , (SELECT count(*) FROM <schema>.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM <schema>.collection C WHERE C.id=%(collectionId)s",
                  "params": {"collectionId":$context.arguments.collectionId}
                }
              ]
            }
          }
"""
    schema = (req['alliance_schema'] if 'alliance_schema' in req else None) or req['workspace_schema']
    for coll in rds_query(f"SELECT id, name, status, parent_id AS \"parentId\", TO_CHAR(date_created, 'YYYY-MM-DD\"T\"HH24:MI:SS.US\"Z\"') AS \"dateCreated\" , (SELECT count(*) FROM {schema}.collection_fan CF WHERE CF.collection_id=C.id) AS \"totalProfiles\" FROM {schema}.collection C WHERE C.id=%(collectionId)s",
                          {"collectionId": req['collectionId']}):
        return coll
    else:
        raise RuntimeError('Invalid collectionId')


def get_guessed_labels(endpoint, req, event):
    """          {
            "version": "2018-05-29",
            "operation": "Invoke",
            "payload": {
              "field": "getGuessedLabels",
              "endpoint": "RDS",
              "userSub" : $util.toJson($ctx.identity.sub),
              "queries": [
                {
                  "query": "SELECT collection_id as \"collectionId\", column_number as \"columnNumber\", system_field_name as \"systemFieldName\", file_field_name as \"fileFieldName\" FROM <schema>.guessed_file_upload_fields WHERE collection_id=%(collectionId)s",
                  "params": {"collectionId":$context.arguments.collectionId}
                }
              ]
            }
          }"""
    schema = req['workspace_schema']
    return rds_query(f"SELECT concat('{schema}-', collection_id) as \"collectionId\", column_number as \"columnNumber\", system_field_name as \"systemFieldName\", file_field_name as \"fileFieldName\" FROM {schema}.guessed_file_upload_fields WHERE collection_id=%(collectionId)s",
                     {"collectionId": req['collectionId']})


def get_count_all(endpoint, req, event):
    """         {
            "version": "2018-05-29",
            "operation": "Invoke",
            "payload": {
              "field": "countAll",
              "endpoint": "RDS",
              "userSub" : $util.toJson($ctx.identity.sub),
              "queries": [
                #if( !$context.arguments.collectionId )
                {
                  "query": "SELECT c.a_system_name \"id\", c.a_name \"label\", counts.count \"value\" FROM <schema>.fan_attribute_count counts INNER JOIN commons.system_label c on c.a_id = counts.attribute_id"

                }
                #else
                {
                  "query": "WITH in_collections as (select sc.collection_id FROM <schema>.set_collection sc WHERE set_id = %(collectionId)s), counts as (SELECT attribute_id, sum(fan_count) as count FROM <schema>.fan_collection_attribute WHERE collection_id IN ( SELECT c.id FROM <schema>.collection c WHERE c.id = %(collectionId)s OR c.parent_id = %(collectionId)s OR c.id IN (SELECT * FROM in_collections) OR c.parent_id IN (SELECT * FROM in_collections) OR -1 IN (SELECT * FROM in_collections)) GROUP BY 1) SELECT sl.a_system_name \"id\", sl.a_name \"label\", counts.count \"value\" FROM counts INNER JOIN commons.system_label sl ON sl.a_id = counts.attribute_id;",
                  "params":{"collectionId": "${context.arguments.collectionId}"
                           }
                }
                #end
              ]
            }
          }"""

    schema = req['workspace_schema']
    if 'collectionId' in req and req['collectionId']:
        return rds_query(f"WITH in_collections as (select sc.collection_id FROM {schema}.set_collection sc WHERE set_id = %(collectionId)s), counts as (SELECT attribute_id, sum(fan_count) as count FROM {schema}.fan_collection_attribute WHERE collection_id IN ( SELECT c.id FROM {schema}.collection c WHERE c.id = %(collectionId)s OR c.parent_id = %(collectionId)s OR c.id IN (SELECT * FROM in_collections) OR c.parent_id IN (SELECT * FROM in_collections) OR -1 IN (SELECT * FROM in_collections)) GROUP BY 1) SELECT sl.a_system_name \"id\", sl.a_name \"label\", counts.count \"value\" FROM counts INNER JOIN commons.system_label sl ON sl.a_id = counts.attribute_id",
                         {"collectionId": req['collectionId']})
    else:
        return rds_query(f"SELECT c.a_system_name \"id\", c.a_name \"label\", counts.count \"value\" FROM {schema}.fan_attribute_count counts INNER JOIN commons.system_label c on c.a_id = counts.attribute_id")