#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd


# In[3]:


from tms_data_interface import SQLQueryInterface
seq = SQLQueryInterface(schema="transactionschema")


# In[4]:


seq.execute_raw("show tables")


# In[5]:


query = """
    select final.CUSTOMER_NUMBER_main as Focal_id,
        CAST(final.Total_hrc_transaction_amount AS DECIMAL(18, 2)) AS Total_hrc_transaction_amount,
        final.Unique_country_codes,
        final.SEGMENT,
        final.RISK,
        final.SAR_FLAG
    from 
    (
        (
            select subquery.CUSTOMER_NUMBER_1 as CUSTOMER_NUMBER_main,
                   subquery.Total_hrc_transaction_amount,
                   subquery.Unique_country_codes
            from 
            (
                select customer_number as CUSTOMER_NUMBER_1, 
                       sum(transaction_amount) as Total_hrc_transaction_amount, 
                       array_join(array_agg(DISTINCT benef_cntry_code), ',') AS unique_country_codes
                from 
                (
                    select * 
                    from {trans_data} trans_table 
                    left join {acc_data} acc_table
                    on trans_table.benef_account_number = acc_table.account_number
                ) trans
                where account_number not in ('None')
                group by customer_number
            ) subquery
        ) main 
        left join 
       (
            select cd.CUSTOMER_NUMBER_3 as CUSTOMER_NUMBER_cust,
                   cd.SEGMENT,
                   cd.RISK,
                   case
                        when ad.SAR_FLAG is NULL then 'N'
                        else ad.SAR_FLAG
                   end as SAR_FLAG    
            from
            (
                select customer_number as CUSTOMER_NUMBER_3, 
                       business_segment as SEGMENT,
                       case
                           when RISK_CLASSIFICATION = 1 then 'Low Risk'
                           when RISK_CLASSIFICATION = 2 then 'Medium Risk'
                           when RISK_CLASSIFICATION = 3 then 'High Risk'
                           else 'Unknown Risk'
                       end AS RISK
                from {cust_data}
            ) cd 
            left join
            (
                select customer_number as CUSTOMER_NUMBER_4, 
                       sar_flag as SAR_FLAG
                from {alert_data}
            ) ad 
            on cd.CUSTOMER_NUMBER_3 = ad.CUSTOMER_NUMBER_4
        ) as cust_alert
        on cust_alert.CUSTOMER_NUMBER_cust = main.CUSTOMER_NUMBER_main
    ) as final
"""


# In[6]:


from tms_data_interface import SQLQueryInterface

class Scenario:
    seq = SQLQueryInterface(schema="transactionschema")

    def logic(self, **kwargs):
        row_list = self.seq.execute_raw(query.format(trans_data="transaction10m",
                                                    cust_data="customer_data_v1",
                                                    acc_data="account_data_v1",
                                                    alert_data="alert_data_v1")
                                       )
        cols = ["Focal_id", "Total_hrc_transaction_amount", "Unique_country_codes",
                "Segment", "Risk", "SAR_FLAG"]
        df = pd.DataFrame(row_list, columns = cols)
        return df


# In[7]:


# sen = Scenario()
# sen.logic()


# In[ ]: