{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c8d247d-86fa-44a4-96cb-6a7dfe2edc94",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T04:54:17.674351800Z",
     "start_time": "2023-11-19T04:54:17.460200900Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import boto3\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown as md\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import isoweek\n",
    "import datetime\n",
    "from users import load_users, User\n",
    "from datetime import timedelta, date, datetime, timezone\n",
    "import json\n",
    "import ast\n",
    "import time\n",
    "from DataAPIRequester import DataAPIRequester\n",
    "\n",
    "os.environ[\"S3_BUCKET\"] = \"pingcap-plg\" \n",
    "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \"/home/ubuntu/gcpbigquery.json\"\n",
    "os.environ[\"CLINIC_API_KEY\"] = os.getenv(\"NOTEBOOK_CLINIC_API_KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cff1d76b-f235-4f98-813f-6e223c39ab09",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T04:54:18.592395Z",
     "start_time": "2023-11-19T04:54:18.576781400Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "end = datetime.now(timezone.utc)\n",
    "begin = end - timedelta(days=7)\n",
    "\n",
    "md(f'''\n",
    "report generate time: **{datetime.now().date()}**\n",
    "\n",
    "only include tenants signup after **{begin.date()}**\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc6db4c-0211-41db-b3eb-bd4b4ea7f461",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T05:09:48.667927900Z",
     "start_time": "2023-11-19T04:54:19.387815600Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "users = load_users(begin)\n",
    "all_users = [u for u in users if not u.is_pingcap()]\n",
    "\n",
    "users = all_users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "220e2d40-65f5-4f6a-972b-68240ab98170",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T05:09:48.824643800Z",
     "start_time": "2023-11-19T05:09:48.824643800Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def deal_str(data):\n",
    "    data = str(data) \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be8401dc-c0d5-4c9e-af18-27e236d08149",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T05:09:48.824643800Z",
     "start_time": "2023-11-19T05:09:48.824643800Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def send_chart_data(table: str, data: pd.DataFrame):\n",
    "    requester = DataAPIRequester(table, endpoint=\"https://us-east-1.data.tidbcloud.com/api/v1beta/app/dataapp-xcDiMBOB/endpoint/\", public_key=\"00K9G550\", private_key=os.getenv(\"KPI_DASHBOARD_PRIVATE_KEY\"))\n",
    "    \n",
    "    return requester.send_data(data)\n",
    "                                 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "448466c1-18e0-4763-ba65-26243a19540f",
   "metadata": {},
   "source": [
    "## Signup Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42b6a4d7-293e-4547-99ee-6a3732cb833d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T05:10:43.700918900Z",
     "start_time": "2023-11-19T05:09:48.824643800Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "tenant_df = users[0]._tenant_df\n",
    "tenant_df = tenant_df[tenant_df.tenant_created_at.dt.date >= begin.date()]\n",
    "df = pd.DataFrame(columns=['tenant_id', 'signup_time', 'name', 'email', 'country', 'region', 'status', 'kind', 'utm_source', 'utm_campaign', 'utm_medium'])\n",
    "df['tenant_id'] = tenant_df.tenant_id\n",
    "df['signup_time'] = tenant_df.tenant_created_at.dt.date\n",
    "df['name'] = tenant_df.tenant_name\n",
    "df['email'] = tenant_df.tenant_email\n",
    "df['country'] = 'unknown'\n",
    "df['region'] = tenant_df.tenant_region\n",
    "df['status'] = tenant_df.tenant_status\n",
    "df['kind'] = tenant_df['tenant_internal_user'].apply(lambda x: 'internal' if x else 'external')\n",
    "\n",
    "for u in all_users:\n",
    "    if df[df['tenant_id'] == u.id()].empty:\n",
    "        continue\n",
    "    utm_info = u.utm_info()\n",
    "    utm_source = utm_info['utm_source']\n",
    "    utm_medium = utm_info['utm_medium']\n",
    "    utm_campaign = utm_info['utm_campaign']\n",
    "    df.loc[df['tenant_id'] == u.id(), ['utm_source', 'utm_medium', 'utm_campaign']] = [utm_source, utm_medium, utm_campaign]\n",
    "\n",
    "\n",
    "df['utm_source'] = df['utm_source'].fillna('unknown')\n",
    "df['signup_time'] = df['signup_time'].map(deal_str)\n",
    "df = df.fillna('unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96d7900c-ae79-4082-8488-b8f3d363b7f9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "send_chart_data('user_info_batch', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83637772-6142-4aaf-b9ff-251f2de220b2",
   "metadata": {},
   "source": [
    "## Engaged Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3213455a-33a0-4ffe-bec8-60cbd60bff53",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T02:13:51.239375900Z",
     "start_time": "2023-11-19T02:13:51.017471800Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "ru_df = users[0]._daily_billing_cost_df\n",
    "ru_df = ru_df[ru_df.billing_cost_date >= begin.date()]\n",
    "ru_df = ru_df[(ru_df.unit_category == 'Serverless Request Unit Cost')]\n",
    "ru_df = ru_df.sort_values('billing_cost_date')\n",
    "ru_df['cost_7_days'] = 0\n",
    "for i, row in ru_df.iterrows():\n",
    "    current_date = row['billing_cost_date']\n",
    "    seven_days_ago = current_date - pd.Timedelta(days=7)\n",
    "    cost_sum = ru_df.loc[(ru_df['billing_tenant_id'] == row['billing_tenant_id']) & (ru_df['billing_cost_date'] > seven_days_ago) & (ru_df['billing_cost_date'] <= current_date), 'raw_cost'].sum()\n",
    "    ru_df.at[i, 'cost_7_days'] = cost_sum\n",
    "\n",
    "ru_df.rename(columns={\"billing_tenant_id\": \"tenant_id\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f19f670e-3de0-44a6-b51d-d4f592a62048",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T02:13:51.239375900Z",
     "start_time": "2023-11-19T02:13:51.239375900Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "ru_df['tenant_id'] = ru_df['tenant_id'].map(deal_str)\n",
    "ru_df['billing_cost_date'] = ru_df['billing_cost_date'].map(deal_str)\n",
    "ru_df['cost_price'] = ru_df['cost_price'].map(deal_str)\n",
    "ru_df['raw_price'] = ru_df['raw_price'].map(deal_str)\n",
    "ru_df['raw_cost'] = ru_df['raw_cost'].map(deal_str)\n",
    "ru_df['cost_7_days'] = ru_df['cost_7_days'].map(deal_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0887d1e9-a3fd-470b-bb7d-fb1c53a96dc0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "send_chart_data('engaged_user_info_batch', ru_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c11ca850-ceed-4a14-a51f-8f04c8d91254",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Active Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "379bb1d5-8c9b-4275-a9b8-e934fd48b85c",
   "metadata": {
    "is_executing": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "cluster_df = users[0]._cluster_df\n",
    "\n",
    "gw_access_df = users[0]._gw_access_df\n",
    "cluster_df.id = cluster_df.id.astype(str)\n",
    "gw_access_df.cluster_id = gw_access_df.cluster_id.astype(str)\n",
    "gw_access_df = gw_access_df[gw_access_df.duration > 1]\n",
    "gw_access_df = gw_access_df[~gw_access_df.gateway_instance.str.contains('-inner-')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6711461-f725-49f9-b192-4f677d3196d6",
   "metadata": {
    "is_executing": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Merge, set index, and select specific columns\n",
    "af = pd.merge(cluster_df[['id', 'tenant_id']], gw_access_df, left_on='id', right_on='cluster_id')\n",
    "af['ts'] = pd.to_datetime(af.ts, unit='s', utc=True).dt.date\n",
    "af = af[['tenant_id', 'ts', 'duration', 'in_bytes', 'out_bytes', 'program_name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f66587a-766a-4bb3-9931-665e28b486cc",
   "metadata": {
    "collapsed": false,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import re\n",
    "# error_categories = {\n",
    "#     \"no instances to route\": \"Routing Error\",\n",
    "#     \"remote error: tls: user canceled\": \"TLS Connection Error\",\n",
    "#     \"failed to read the connection\": \"Connection Error\",\n",
    "#     \"Access denied for user\": \"Authentication Error\",\n",
    "#     \"connect: connection refused\": \"Connection Error\",\n",
    "#     \"this is an error from backend\": \"Backend Error\",\n",
    "#     \"Unknown database\": \"Database Selection Error\",\n",
    "#     \"i/o timeout\": \"Timeout Error\",\n",
    "# }\n",
    "\n",
    "# error_df = pd.DataFrame(columns=['tenant_id', 'ts', 'error', 'category'])\n",
    "# for _, error_msg in af[~af.error.isna()].iterrows():\n",
    "#     category = \"Unknown\"\n",
    "#     for keyword, error_category in error_categories.items():\n",
    "#         if re.search(keyword, error_msg.error):\n",
    "#             category = error_category\n",
    "#             break\n",
    "#     error_df = error_df.append({\n",
    "#         'tenant_id': error_msg.tenant_id,\n",
    "#         'ts': error_msg.ts,\n",
    "#         'error': error_msg.error,\n",
    "#         'category': category,\n",
    "#     }, ignore_index=True)\n",
    "# error_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00f5ca44-262c-44df-9132-6313ffebb3cd",
   "metadata": {
    "is_executing": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Group by 'tenant_id' and 'ts', and calculate statistics\n",
    "grouped = af.groupby(['tenant_id', 'ts']).agg({\n",
    "    'duration': ['min', 'max', 'mean', 'count'],\n",
    "    'in_bytes': ['min', 'max', 'mean'],\n",
    "    'out_bytes': ['min', 'max', 'mean'],\n",
    "    'program_name': lambda x: ','.join(x.astype(str).unique()),\n",
    "}).reset_index()\n",
    "\n",
    "# Flatten the multi-level column index\n",
    "grouped.columns = [col[0] if col[0] in ['tenant_id', 'ts'] else '_'.join(col).strip() for col in grouped.columns.values]\n",
    "grouped.rename(columns={\"program_name_<lambda>\": \"program_name\", \"duration_count\": \"log_count\"}, inplace=True)\n",
    "grouped['tenant_id'] = grouped['tenant_id'].map(deal_str)\n",
    "grouped['ts'] = grouped['ts'].map(deal_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc1651ad-3e48-45ff-ad8b-9c77c2e86c7f",
   "metadata": {
    "is_executing": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "send_chart_data('active_user_info_batch', grouped)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff1c4243-c70b-436f-b2d3-d23d24016a86",
   "metadata": {},
   "source": [
    "## Paid Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "133faa58-98ef-42e3-b930-7b644f7f7bbf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-19T05:23:47.044517600Z",
     "start_time": "2023-11-19T05:23:46.373482800Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "spend_limit_df = users[0]._spend_limit_df\n",
    "spend_limit_df = spend_limit_df[spend_limit_df.status == 'fit']\n",
    "spend_limit_df['invoice_limit'] = spend_limit_df['biz_data'].apply(lambda x: float(eval(x)[\"invoice_limit\"]))\n",
    "spend_limit_df = spend_limit_df[spend_limit_df.invoice_limit > 0]\n",
    "spend_limit_df['created_date'] = spend_limit_df['created_at'].dt.date\n",
    "spend_limit_df = spend_limit_df[['tenant_id', 'invoice_limit', 'created_date']]\n",
    "spend_limit_df['tenant_id'] = spend_limit_df['tenant_id'].map(deal_str)\n",
    "spend_limit_df['created_date'] = spend_limit_df['created_date'].map(deal_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3e41a6b-200f-4be9-a6d7-7dab17bf2469",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "send_chart_data('paid_user_info_batch',spend_limit_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0da36f1-0dd2-4c3c-8bbb-f0c689d8be97",
   "metadata": {
    "tags": []
   },
   "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}