{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e706cfb0-2234-4c4c-95d8-d1968f656aa0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f35b1262-3c20-44a6-bbd3-2679a15551e6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from tms_data_interface import SQLQueryInterface\n",
    "seq = SQLQueryInterface(schema=\"transactionschema\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e52124e8-4f62-449d-8852-1e04f8c01ecc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['account_data_v1'],\n",
       " ['account_data_v2'],\n",
       " ['alert_data_v1'],\n",
       " ['alert_data_v2'],\n",
       " ['customer_data_v1'],\n",
       " ['customer_data_v2'],\n",
       " ['transaction10m'],\n",
       " ['transaction60m']]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "seq.execute_raw(\"show tables\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dda35e8d-8997-42d4-a472-844c208d0f49",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    select  final.CUSTOMER_NUMBER_main as Focal_id,\n",
    "            final.Credit_transaction_amount,\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",
    "            from \n",
    "            (\n",
    "                (\n",
    "                    select customer_number as CUSTOMER_NUMBER_1, \n",
    "                            sum(transaction_amount) as Credit_transaction_amount\n",
    "                    from \n",
    "                    (\n",
    "                        select * \n",
    "                        from {trans_data} as trans_table \n",
    "                        left join {acc_data} as acc_table\n",
    "                        on trans_table.benef_account_number = acc_table.account_number\n",
    "                        where trans_table.transaction_desc = 'WIRE RELATED TRANSACTION'\n",
    "                    )\n",
    "                    where account_number not in ('None')\n",
    "                    group by 1\n",
    "                ) credit\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fb0405fe-cd10-4da1-9f06-fe52cff942b4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "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\", \"Total_Wire_Deposit_Amt\",\n",
    "                \"Segment\", \"Risk\", \"SAR_FLAG\"]\n",
    "        df = pd.DataFrame(row_list, columns = cols)\n",
    "        df['Segment'] = 'Individual'\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ddc11b42-6cbb-419b-9e26-73e7606e18a6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# sen = Scenario()\n",
    "# sen.logic()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "157c4e46-2cff-4f6f-acba-faf4d73538cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#tst cmt"
   ]
  }
 ],
 "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
}