metadata_table = "DW_RAR.NH_METADATA_INVENTORY"

def get_query_metadata(metadata_table, owner, table_name):
    query_metadata = (
        "WITH metaDF AS ( "
        "SELECT owner, table_name, column_id, column_name, data_type, data_precision, data_scale, "
        "CASE WHEN data_precision IS NULL AND data_scale IS NULL THEN NULL "
        "WHEN data_precision IS NOT NULL AND data_scale IS NULL THEN data_precision "
        "WHEN CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN data_precision "
        "WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN data_scale "
        "WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN NULL "
        "ELSE NULL END AS data_precision_hive, "
        "CASE WHEN data_precision IS NULL AND data_scale IS NULL THEN NULL "
        "WHEN data_precision IS NOT NULL AND data_scale IS NULL THEN CAST(0 AS INT) "
        "WHEN CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN data_scale "
        "WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN data_scale "
        "WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN NULL "
        "ELSE NULL END AS data_scale_hive, "
        "CASE WHEN data_type LIKE '%NUMBER%' AND data_precision IS NULL AND data_scale IS NULL THEN 'String' "
        "WHEN data_type LIKE '%NUMBER%' AND data_precision IS NOT NULL AND data_scale IS NULL THEN 'Decimal' "
        "WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN 'Decimal' "
        "WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN 'Decimal' "
        "WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN 'String' "
        "WHEN data_type LIKE '%CHAR%' THEN 'String' "
        "WHEN data_type LIKE '%VARCHAR2%' THEN 'String' "
        "WHEN data_type LIKE '%TIMESTAMP%' THEN 'String' "
        "WHEN data_type LIKE '%DATE%' THEN 'String' "
        "ELSE 'String' END AS data_type_hive, "
        "REGEXP_REPLACE(data_description, '''', '\\''') AS data_description "
        "FROM {0} "
        "WHERE lower(owner||'.'||table_name) = lower('{1}'||'.'||'{2}') "
        "AND a_valid_to > sysdate) "
        "SELECT owner, table_name, column_id, column_name, data_type, data_precision, data_scale, "
        "data_precision_hive, data_scale_hive, data_type_hive, "
        "CASE WHEN data_type_hive = 'Decimal' THEN 'Decimal(' || COALESCE(CAST(data_precision_hive AS VARCHAR2(30)), '') || ',' || COALESCE(CAST(data_scale_hive AS VARCHAR2(30)), '') || ')' "
        "ELSE data_type_hive END AS data_type_string, data_description "
        "FROM metaDF "
        "ORDER BY CAST(column_id AS INT) "
    ).format(metadata_table, owner, table_name)
    
    return query_metadata

def get_query_metadata_access_type1(metadata_table):
    query_metadata_access_type1 = (
        "SELECT owner, table_name, list_of_sources as SOURCE, rar3_type_of_access "
        "FROM {0} "
        "WHERE a_valid_to > sysdate "
        "AND rar3_type_of_access = '1' "
        "AND list_of_sources NOT IN 'RAR' "
        "AND lower(owner||'.'||table_name) = lower($$$1) "
        "UNION "
        "SELECT owner, table_name, list_of_sources as SOURCE, rar3_type_of_access "
        "FROM {0} "
        "WHERE a_valid_to > sysdate "
        "AND rar3_type_of_access = '1' "
        "AND owner = 'CORR_REF_MAIN' "
        "AND lower(owner||'.'||table_name) = lower($$$1) "
    ).format(metadata_table)
    
    return query_metadata_access_type1

def get_query_metadata_access_type2a(metadata_table):
    query_metadata_access_type2a = (
        "WITH rar_columns AS ( "
        "SELECT owner, table_name, column_name, source, rar3_type_of_access "
        "FROM ( "
        "SELECT owner, table_name, column_name, rar3_type_of_access, list_of_sources, "
        "tms, c2d_ea, c2d_ela, c2d_mpec, c2d_uc, ceph, lm, csdb_reference, "
        "csdb_ratings, fxcd, mdp_bbg, mdp_reu, riad_cl, riad_ou, sdw_estr, sdw_fx, "
        "top, rar, rtm, led, mdp_cma "
        "FROM {0} "
        "WHERE a_valid_to > sysdate "
        "AND rar3_type_of_access = '2a' "
        "AND lower(owner || '.' || table_name) = lower($$$1) "
        "AND list_of_sources NOT LIKE '%,%' "
        "AND upper(column_name) NOT IN ('DATABASE') "
        ") a "
        "UNPIVOT ( "
        "val FOR (source) IN ( "
        "tms AS 'TMS', c2d_ea AS 'C2D_EA', c2d_ela AS 'C2D_ELA', c2d_mpec AS 'C2D_MPEC', "
        "c2d_uc AS 'C2D_UC', ceph AS 'CEPH', lm AS 'LM', csdb_reference AS 'CSDB_REFERENCE', "
        "csdb_ratings AS 'CSDB_RATINGS', fxcd AS 'FXCD', mdp_bbg AS 'MDP_BBG', mdp_reu AS 'MDP_REU', "
        "riad_cl AS 'RIAD_CL', riad_ou AS 'RIAD_OU', sdw_estr AS 'SDW_ESTR', sdw_fx AS 'SDW_FX', "
        "top AS 'TOP', rar AS 'RAR', rtm AS 'RTM', led AS 'LED', mdp_cma AS 'MDP_CMA') "
        ") "
        "ORDER BY owner, table_name, column_name "
        "), "
        "dummy_entry AS ( "
        "SELECT owner, table_name, 'RAR' as SOURCE, rar3_type_of_access "
        "FROM rar_columns "
        "FETCH FIRST ROW ONLY "
        "), "
        "disc_tec_fields AS ( "
        "SELECT owner, table_name, 'TEC_INGESTION_DATE' AS column_name, source, rar3_type_of_access "
        "FROM dummy_entry "
        "UNION "
        "( "
        "SELECT owner, table_name, 'TEC_EXECUTION_DATE' AS column_name, source, rar3_type_of_access "
        "FROM dummy_entry "
        ") "
        "UNION "
        "( "
        "SELECT owner, table_name, 'TEC_RUN_ID' AS column_name, source, rar3_type_of_access "
        "FROM dummy_entry "
        ") "
        ") "
        "SELECT owner, table_name, column_name, source, rar3_type_of_access "
        "FROM disc_tec_fields "
        "UNION "
        "SELECT owner, table_name, column_name, source, rar3_type_of_access "
        "FROM rar_columns "
    ).format(metadata_table)
    
    return query_metadata_access_type2a

def get_query_igam_roles(igam_table,service):
    if service.lower() == 'rar':
        service_entitlement='mrds'
    elif service.lower()=='mopdb':
        service_entitlement='mrds'
    else:
        service_entitlement='mrds'
    query_igam_roles = (
       "SELECT MRDS_subsource_id as Datasource, "
        "MRDS_subsource_id as subsource_id, "
        "MRDS_entitlement as IGAM_Entitlement, "
        "environment "
        "FROM {0} where lower(environment) = lower($$$1) and SERVICE_NAME='{1}'").format(igam_table,service.upper())

    return query_igam_roles