if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Get an overview of all tables over all spaces")
    parser.add_argument("-f", "--file", required=True, help="Path of parameter file")
    args = parser.parse_args()

    with open(args.file, 'r') as f:
        config = json.load(f)

    secrets_file = config["SETTINGS"]["secrets_file"]
    token_file = config["SETTINGS"]["token_file"]
    dsp_host = config["DATASPHERE"]["dsp_host"]

    # Now roll the dice and go to work
    print("Started...")
    get_database_tables()
    print("Ended")

def get_database_tables():
    header = utils.initializeGetOAuthSession(token_file, secrets_file)
    database_tables = []

    url = utils.get_url(dsp_host, 'list_of_spaces')
    response = requests.get(url,headers=header)
    space_list = response.json()

    for spaceID in space_list:
        url = utils.get_url(dsp_host, 'space_tables').format(**{"spaceID": spaceID})
        response = requests.get(url, headers=header)
        space_json = response.json()
        try:
            for table in space_json[spaceID]['tables']:
                tableName = table['tableName']
                usedDisk = round((table['usedDisk'] / 1000 / 1000),2) # MB
                usedMemory = round(table['usedMemory'] / 1000 / 1000,2)
                records = table['recordCount']
                database_tables.append((spaceID, tableName, usedDisk, usedMemory, records))
        except KeyError:
            continue

    df = pd.DataFrame(database_tables, columns=['Space', 'Table Name', 'Used Disk', 'Used Memory', 'Records'])
    df.to_excel(excel_file, sheet_name='Sheet1', index=False)

if __name__ == "__main__":

    parser = argparse.ArgumentParser(description="Get all exposed views of a space")

    parser.add_argument("-f", "--file", required=True, help="Path of parameter file")
    parser.add_argument("-s", "--space", required=True, help="Space")

    args = parser.parse_args()

    with open(args.file, 'r') as f:
        config = json.load(f)

    dsp_host = config["DATASPHERE"]["dsp_host"]
    dsp_space = args.space
    hdb_address = config["HDB"]["hdb_address"]
    hdb_port = config["HDB"]["hdb_port"]
    hdb_user = config["HDB"]["hdb_user"]
    hdb_password = config["HDB"]["hdb_password"]

    # Now roll the dice and go to work
    print("Started...")
    get_exposed_views()
    print("Ended")

{
  "DATASPHERE": {
    "dsp_host": "https://xyz.eu10.hcs.cloud.sap"
  },
  "HDB": {
    "hdb_address": "xyz.hana.prod-eu10.hanacloud.ondemand.com",
    "hdb_port": 443,
    "hdb_user": "user",
    "hdb_password": "password"
  },
  "SETTINGS": {
    "secrets_file": "secret.json",
    "token_file": "token.json",
    "deploy_wait_time_in_seconds": 0,
    "export_folder_path": "",   
    "view_name_prefix": "1GV",
    "start_technical_name": 7
  }
}

def get_csn_files():
    # Connect to HDB
    conn = dbapi.connect(
        address=hdb_address,
        port=int(hdb_port),
        user=hdb_user,
        password=hdb_password
    )
    cursor = conn.cursor()

    # select statement to fetch csn's. Selection on highest ARTIFACT_VERSION for each object.
    st = f'''
        SELECT A.ARTIFACT_NAME, A.CSN, A.ARTIFACT_VERSION
        FROM "{dsp_space}$TEC"."$$DEPLOY_ARTIFACTS$$" A
        INNER JOIN (
          SELECT ARTIFACT_NAME, MAX(ARTIFACT_VERSION) AS MAX_ARTIFACT_VERSION
          FROM "{dsp_space}$TEC"."$$DEPLOY_ARTIFACTS$$"
          WHERE SCHEMA_NAME = '{dsp_space}' AND ARTIFACT_NAME NOT LIKE '%$%' AND PLUGIN_NAME in ('tableFunction', 'InAModel')
          GROUP BY ARTIFACT_NAME

        ) B
        ON A.ARTIFACT_NAME = B.ARTIFACT_NAME
        AND A.ARTIFACT_VERSION = B.MAX_ARTIFACT_VERSION;
    '''
    cursor.execute(st)
    rows = cursor.fetchall()
    conn.close()
    total_rows = len(rows)
    print('Total rows: ' + str(total_rows))
    return rows

def get_exposed_views():
    exposedViews = []
    dac_objects = []
    dac_items = []

    # Get objects which are exposed
    csn_files = get_csn_files()

    for csn in csn_files:
        csn = csn[1]
        csn_loaded = json.loads(csn)
        dac_objects.clear()
        dac_items.clear()

        objectName = list(csn_loaded['definitions'].keys())[0]
        label = csn_loaded['definitions'][objectName]['@EndUserText.label']
        try:
            exposed = csn_loaded['definitions'][objectName]['@DataWarehouse.consumption.external']
        except KeyError:
            exposed = False

        try:
            for dac in csn_loaded['definitions'][objectName]['@DataWarehouse.dataAccessControl.usage']:

                if len(dac['on']) == 3: # one column mapping
                    dac_items.append(dac['on'][0]['ref'][0])

                if len(dac['on']) == 7: # two column mapping
                    dac_items.append(dac['on'][0]['ref'][0])
                    dac_items.append(dac['on'][4]['ref'][0])

                dac_objects.append(dac["target"])

        except KeyError:
            dac_items.clear()
            dac_objects.clear()

        exposedViews.append((dsp_space, objectName, label, exposed, ', '.join(f'{item}' for item in dac_items), ', '.join(f'{object}' for object in dac_objects)))

    df = pd.DataFrame(exposedViews, columns=['Space', 'Object', 'Description', 'Exposed', 'DAC Item', 'DAC Object'])
    df.to_excel(excel_file, sheet_name='Sheet1', index=False)

    # Format Excel
    utils.format_excel(excel_file)