{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e706cfb0-2234-4c4c-95d8-d1968f656aa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "query = \"\"\"\n",
    "    select  final.CUSTOMER_NUMBER_main as Focal_id,\n",
    "            final.Credit_transaction_amount,\n",
    "            final.Total_no_of_credit_transactions,\n",
    "            final.Debit_transaction_amount,\n",
    "            final.Total_no_of_debit_transactions,\n",
    "            final.SEGMENT,\n",
    "            final.RISK,\n",
    "            final.SAR_FLAG\n",
    "    from \n",
    "    (\n",
    "        (\n",
    "            select subquery.CUSTOMER_NUMBER_1 as CUSTOMER_NUMBER_main,\n",
    "                    subquery.Credit_transaction_amount,\n",
    "                    subquery.Total_no_of_credit_transactions,\n",
    "                    case\n",
    "                         when subquery.Debit_transaction_amount is NULL then 0\n",
    "                         else Debit_transaction_amount\n",
    "                    end as Debit_transaction_amount,\n",
    "                    case\n",
    "                         when subquery.Total_no_of_debit_transactions is NULL then 0\n",
    "                         else Total_no_of_debit_transactions\n",
    "                    end as Total_no_of_debit_transactions\n",
    "            from \n",
    "            (\n",
    "                (\n",
    "                    select customer_number as CUSTOMER_NUMBER_1, \n",
    "                            sum(transaction_amount) as Credit_transaction_amount, \n",
    "                            count(*) as Total_no_of_credit_transactions\n",
    "                    from \n",
    "                    (\n",
    "                        select * \n",
    "                        from {trans_data} trans_table left join {acc_data} acc_table\n",
    "                        on trans_table.benef_account_number = acc_table.account_number\n",
    "                    )\n",
    "                    where account_number not in ('None')\n",
    "                    group by 1\n",
    "                ) credit left join\n",
    "                (\n",
    "                    select customer_number as CUSTOMER_NUMBER_2, \n",
    "                            sum(transaction_amount) as Debit_transaction_amount, \n",
    "                            count(*) as Total_no_of_debit_transactions\n",
    "                    from \n",
    "                    (\n",
    "                        select * \n",
    "                        from {trans_data} trans_table left join {acc_data} acc_table\n",
    "                        on trans_table.orig_account_number = acc_table.account_number\n",
    "                    )\n",
    "                    where account_number not in ('None')\n",
    "                    group by 1\n",
    "                ) debit on credit.CUSTOMER_NUMBER_1 = debit.CUSTOMER_NUMBER_2 \n",
    "            ) subquery\n",
    "        ) main left join \n",
    "        (\n",
    "            select subquery.CUSTOMER_NUMBER_3 as CUSTOMER_NUMBER_cust,\n",
    "                    subquery.SEGMENT,\n",
    "                    subquery.RISK,\n",
    "                    case\n",
    "                         when subquery.SAR_FLAG is NULL then 'N'\n",
    "                         else subquery.SAR_FLAG\n",
    "                    end as SAR_FLAG    \n",
    "            from\n",
    "            (\n",
    "                (\n",
    "                    select customer_number as CUSTOMER_NUMBER_3, \n",
    "                            business_segment as SEGMENT,\n",
    "                            case\n",
    "                                when RISK_CLASSIFICATION = 1 then 'Low Risk'\n",
    "                                when RISK_CLASSIFICATION = 2 then 'Medium Risk'\n",
    "                                when RISK_CLASSIFICATION = 3 then 'High Risk'\n",
    "                                else 'Unknown Risk'\n",
    "                            end AS RISK\n",
    "                    from {cust_data}\n",
    "                ) cd left join\n",
    "                (\n",
    "                    select customer_number as CUSTOMER_NUMBER_4, \n",
    "                            sar_flag as SAR_FLAG\n",
    "                    from {alert_data}\n",
    "                ) ad on cd.CUSTOMER_NUMBER_3 = ad.CUSTOMER_NUMBER_4\n",
    "            ) subquery\n",
    "        ) cust_alert on cust_alert.CUSTOMER_NUMBER_cust = main.CUSTOMER_NUMBER_main\n",
    "    ) final\n",
    "\"\"\"\n",
    "\n",
    "from tms_data_interface import SQLQueryInterface\n",
    " \n",
    "class Scenario:\n",
    "    seq = SQLQueryInterface(schema=\"transactionschema\")\n",
    " \n",
    "    def logic(self, **kwargs):\n",
    "        row_list = self.seq.execute_raw(query.format(trans_data=\"transaction10m\",\n",
    "                                                    cust_data=\"customer_data_v1\",\n",
    "                                                    acc_data=\"account_data_v1\",\n",
    "                                                    alert_data=\"alert_data_v1\")\n",
    "                                       )\n",
    "        cols = [\"Focal_id\", \"Credit_transaction_amount\", \"Total_no_of_credit_transactions\",\n",
    "                \"Debit_transaction_amount\", \"Total_no_of_debit_transactions\",\n",
    "                \"Segment\", \"Risk\", \"SAR_FLAG\"]\n",
    "        df = pd.DataFrame(row_list, columns = cols)\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77134554-e1dc-4e5b-aaa2-bb432789aa01",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a13f2ec-d02f-4151-9d9a-17edd4f29063",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32c443d5-c51f-425c-be6f-bcd8c9908ef4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c34dc466-7bb5-471e-b0c5-0bfcd81b761c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "406349d3-a6d0-4790-9b04-19dc6ce01827",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}