"""
Compare Snowflake users and roles with Terraform definitions for Delphi and Orchard.
Sometimes Snowflake and Terraform are out of sync, for example  because Snowflake resources were modified by hands some time ago.
"""

import hcl2
import csv
from collections import defaultdict
import os


analyst_roles = [
    'SME_ANALYTICS_UK',
    'SME_ANALYTICS_GERMANY',
    'SME_ANALYTICS_FRANCE',
    'SME_ANALYTICS_BENELUX',
    'SME_ANALYTICS_FINLAND',
    'SME_ANALYTICS_BRAZIL',
    'SME_ANALYTICS_PHILIPPINES',
    'SME_ANALYTICS_INSIGHTS_REPORTING',
    'SME_ANALYTICS_INT_MKTG',
    'SME_DATA_SCIENCE_AND_ANALYTICS',
    'SME_ANALYTICS_LATINIBERIA',
    'SME_ANALYTICS_US_RCA',
    'SME_ANALYTICS_ALAMO',
    'REPORTING_DB_TEAM',
]

script_dir = os.path.dirname(os.path.abspath(__file__))

def read_terraform_users(is_delphi):
    file_name = 'delphi_users_tf.tf' if is_delphi else 'orchard_users_tf.tf'
    with open(os.path.join(script_dir, file_name), "r") as file:
        data = hcl2.load(file)

    user_roles = {}

    variables = data['variable']
    for variable in variables:
        for var_name, var_value in variable.items():
            if var_name == 'users':
                users = var_value['default']
                for user, user_data in users.items():
                    roles = user_data['roles']
                    user_roles[user] = roles
    return user_roles


def read_terraform_roles(is_delphi):
    file_name = 'delphi_roles_tf.tf' if is_delphi else 'orchard_roles_tf.tf'
    with open(os.path.join(script_dir, file_name), "r") as file:
        data = hcl2.load(file)

    roles_result = defaultdict(list)

    if is_delphi:
        roles = data['roles']
        for role_data in roles:
            role = role_data['name']
            account_roles = role_data.get('granted_account_roles', [])
            db_roles = role_data.get('granted_database_roles', [])

            roles_result[role] = account_roles + db_roles
    else:
        variables = data['variable']
        for variable in variables:
            for var_name, var_value in variable.items():
                if var_name == 'roles':
                    roles = var_value['default']
                    for role_data in roles:
                        role = role_data['name']
                        account_roles = role_data.get('granted_account_roles', [])
                        db_roles = role_data.get('granted_database_roles', [])
                        roles_result[role] = account_roles + db_roles
    return roles_result


def read_snowflake_users(is_delphi):
    """
    Snowflake SQL to get users and their roles:
        select *
        from snowflake.account_usage.grants_to_users
        where deleted_on IS NULL;
    """
    user_roles = defaultdict(list)
    file_name = 'delphi_users_snow.csv' if is_delphi else 'orchard_users_snow.csv'
    with open(os.path.join(script_dir, file_name)) as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            user = row['GRANTEE_NAME']
            role = row['ROLE']
            user_roles[user].append(role)
    return user_roles


def read_snowflake_roles(is_delphi):
    """
    Snowflake SQL to get users and their roles:
        select
           grantee_name as role,
           iff(granted_on = 'DATABASE_ROLE', concat(table_catalog, '.', name), name) as source_role,
        from
            snowflake.account_usage.grants_to_roles
        where
            deleted_on IS NULL
            and granted_to in ('ROLE', 'DATABASE_ROLE')
            and granted_on in ('ROLE', 'DATABASE_ROLE')
            and privilege='USAGE'
            and grantee_name in (
                'SME_ANALYTICS_UK',
                'SME_ANALYTICS_GERMANY',
                'SME_ANALYTICS_FRANCE',
                'SME_ANALYTICS_BENELUX',
                'SME_ANALYTICS_FINLAND',
                'SME_ANALYTICS_BRAZIL',
                'SME_ANALYTICS_PHILIPPINES',
                'SME_ANALYTICS_INSIGHTS_REPORTING',
                'SME_ANALYTICS_INT_MKTG',
                'SME_DATA_SCIENCE_AND_ANALYTICS',
                'SME_ANALYTICS_LATINIBERIA',
                'SME_ANALYTICS_US_RCA',
                'SME_ANALYTICS_ALAMO',
                'REPORTING_DB_TEAM'
            );
    """
    roles_result = defaultdict(list)
    file_name = 'delphi_roles_snow.csv' if is_delphi else 'orchard_roles_snow.csv'
    with open(os.path.join(script_dir, file_name)) as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            source_role = row['SOURCE_ROLE']
            role = row['ROLE']
            roles_result[role].append(source_role)
    return roles_result


def compare_users(is_delphi):
    tf_users = read_terraform_users(is_delphi)
    snow_users = read_snowflake_users(is_delphi)

    num = 1
    for user in tf_users:
        list1 = snow_users[user]
        list2 = tf_users.get(user, [])

        has_analyst_role = any(role in analyst_roles for role in list1 + list2)

        only_in_list1 = [x for x in list1 if x not in list2]
        only_in_list2 = [x for x in list2 if x not in list1]

        if has_analyst_role and (only_in_list1 or only_in_list2):
            print(f'{num}. User mismatch for {user}. Snow only: {only_in_list1}, TF only: {only_in_list2}')
            num += 1


def compare_roles(is_delphi):
    tf_roles = read_terraform_roles(is_delphi)
    snow_roles = read_snowflake_roles(is_delphi)

    num = 1
    for role in snow_roles:
        list1 = snow_roles.get(role, [])
        list2 = tf_roles.get(role, [])

        only_in_list1 = [x for x in list1 if x not in list2]
        only_in_list2 = [x for x in list2 if x not in list1]

        if  only_in_list1 or only_in_list2:
            print(f'{num}. Role mismatch for {role}. Snow only: {only_in_list1}, TF only: {only_in_list2}')
            num += 1

# Delphi
print('------- Delphi:')
compare_users(is_delphi=True)
compare_roles(is_delphi=True)

# print('------- Orchard:')
compare_users(is_delphi=False)
compare_roles(is_delphi=False)