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

# In[2]:


import pandas as pd

query = """
    select  final.CUSTOMER_NUMBER_main as Focal_id,
            final.Credit_transaction_amount,
            final.Total_no_of_credit_transactions,
            final.Debit_transaction_amount,
            final.Total_no_of_debit_transactions,
            final.SEGMENT,
            final.RISK,
            final.SAR_FLAG
    from 
    (
        (
            select subquery.CUSTOMER_NUMBER_1 as CUSTOMER_NUMBER_main,
                    subquery.Credit_transaction_amount,
                    subquery.Total_no_of_credit_transactions,
                    case
                         when subquery.Debit_transaction_amount is NULL then 0
                         else Debit_transaction_amount
                    end as Debit_transaction_amount,
                    case
                         when subquery.Total_no_of_debit_transactions is NULL then 0
                         else Total_no_of_debit_transactions
                    end as Total_no_of_debit_transactions
            from 
            (
                (
                    select customer_number as CUSTOMER_NUMBER_1, 
                            sum(transaction_amount) as Credit_transaction_amount, 
                            count(*) as Total_no_of_credit_transactions
                    from 
                    (
                        select * 
                        from {trans_data} trans_table left join {acc_data} acc_table
                        on trans_table.benef_account_number = acc_table.account_number
                    )
                    where account_number not in ('None')
                    group by 1
                ) credit left join
                (
                    select customer_number as CUSTOMER_NUMBER_2, 
                            sum(transaction_amount) as Debit_transaction_amount, 
                            count(*) as Total_no_of_debit_transactions
                    from 
                    (
                        select * 
                        from {trans_data} trans_table left join {acc_data} acc_table
                        on trans_table.orig_account_number = acc_table.account_number
                    )
                    where account_number not in ('None')
                    group by 1
                ) debit on credit.CUSTOMER_NUMBER_1 = debit.CUSTOMER_NUMBER_2 
            ) subquery
        ) main left join 
        (
            select subquery.CUSTOMER_NUMBER_3 as CUSTOMER_NUMBER_cust,
                    subquery.SEGMENT,
                    subquery.RISK,
                    case
                         when subquery.SAR_FLAG is NULL then 'N'
                         else subquery.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
            ) subquery
        ) cust_alert on cust_alert.CUSTOMER_NUMBER_cust = main.CUSTOMER_NUMBER_main
    ) final
"""

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", "Credit_transaction_amount", "Total_no_of_credit_transactions",
                "Debit_transaction_amount", "Total_no_of_debit_transactions",
                "Segment", "Risk", "SAR_FLAG"]
        df = pd.DataFrame(row_list, columns = cols)
        return df


# In[3]:


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


# In[ ]: