{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f9b9285-93d2-4603-859e-2b1677ec1cac",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from IPython.display import Markdown as md"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a5adf9e-a6ba-4b87-89f6-d2f613bfee44",
   "metadata": {},
   "source": [
    "# TiDB Cloud User Classification - NA report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b35233f5-d5c8-403a-8e98-4d1c1dcd7a16",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta, datetime, timezone, date\n",
    "\n",
    "begin = datetime(2022, 7, 1, tzinfo=timezone.utc)\n",
    "\n",
    "md(f'''\n",
    "report generate time: **{datetime.now().date()}**\n",
    "\n",
    "only include tenants signup after **{begin.date()}**\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "082b18ae-7797-4235-bdf5-a6549916fd72",
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import pandas as pd\n",
    "\n",
    "S3_BUCKET = \"pingcap-plg\"\n",
    "s3 = boto3.client('s3')\n",
    "\n",
    "_date = datetime.now().date() - timedelta(days=1)\n",
    "\n",
    "class_1_1 = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-1-1-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "class_1_2 = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-1-2-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "class_1_3 = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-1-3-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "class_2 = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-2-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "class_3 = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-3-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "class_other = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/class-4-{_date}.csv')['Body'], low_memory=False, keep_default_na=False)\n",
    "\n",
    "total_users = pd.concat([class_1_1, class_1_2, class_1_3, class_2, class_3, class_other])\n",
    "users = total_users[total_users.Region==\"NA\"]\n",
    "\n",
    "md(f'After **{begin.date()}**, there are **{len(users)}** NA users signup')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea6c3fd3-240c-4436-aa4b-b44dc1fb8561",
   "metadata": {},
   "source": [
    "### Class 1\n",
    "\n",
    "conditions (match any):\n",
    "- connect and import\n",
    "- web shell page stay time > 60s\n",
    "- import page stay time > 60s\n",
    "- max qps > 10\n",
    "- max connection > 10\n",
    "- PoC Form Submission\n",
    "- has bind card"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc81590b-71f8-4099-9846-0868043bed06",
   "metadata": {},
   "outputs": [],
   "source": [
    "def set_enter_date(df_in, _class, begin, end):\n",
    "    table_list = {}\n",
    "    df = df_in.copy()\n",
    "    df.loc[:, 'Enter date'] = end\n",
    "    for i, row in df.iterrows():\n",
    "        enter_date = begin\n",
    "        while enter_date < end:\n",
    "            # Because the data of 2022-10-31 is overwrited, we have to skip it\n",
    "            if enter_date == date(2022, 10, 31):\n",
    "                enter_date += timedelta(days=1)\n",
    "                if enter_date >= end:\n",
    "                    break\n",
    "            if table_list.get(f'{_class}-{enter_date}') is None:\n",
    "                try:\n",
    "                    table = pd.read_csv(s3.get_object(Bucket=S3_BUCKET, Key=f'data/classes/{_class}-{enter_date}.csv')['Body'], low_memory=False)\n",
    "                except Exception as e:\n",
    "                    print(e)\n",
    "                    table = pd.DataFrame()\n",
    "                table_list[f'{_class}-{enter_date}'] = table\n",
    "            if row['Tenant ID'] in table_list.get(f'{_class}-{enter_date}')['Tenant ID'].values:\n",
    "                #row['Enter date'] = enter_date\n",
    "                df.loc[i, 'Enter date'] = enter_date\n",
    "                break\n",
    "            enter_date += timedelta(days=1)\n",
    "    return df\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12f0a1c0-01f8-48cf-8575-cafc92bf44ec",
   "metadata": {},
   "source": [
    "#### Class 1.1\n",
    "\n",
    "conditions (match any):\n",
    "- use business email\n",
    "- PoC Form Submission\n",
    "- has bind card"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b731c718-1a9d-483c-9e23-e027a60e3654",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_1_1 = set_enter_date(class_1_1[class_1_1.Region==\"NA\"], 'class-1-1', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_1_1.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_1_1.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6b11e0a-fa89-41a1-bf2d-f8e6a87aa27e",
   "metadata": {},
   "source": [
    "#### Class 1.2\n",
    "\n",
    "conditions:\n",
    "- use international personal mail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "017a05a1-f12a-4603-8493-c3a105cb7d14",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_1_2 = set_enter_date(class_1_2[class_1_2.Region==\"NA\"], 'class-1-2', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_1_2.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_1_2.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab9a5f66-ab1f-436d-a348-081370d0fd05",
   "metadata": {},
   "source": [
    "#### Class 1.3\n",
    "\n",
    "conditions:\n",
    "- use chinese personal mail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14818928-7e5f-46a0-93ed-b9d9116d745f",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_1_3 = set_enter_date(class_1_3[class_1_3.Region==\"NA\"], 'class-1-3', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_1_3.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_1_3.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23871930-c94b-4345-a44e-e0fb035fb18d",
   "metadata": {},
   "source": [
    "### Class 2\n",
    "\n",
    "conditions:\n",
    "- connect and no import and max qps > 1\n",
    "- web shell page stay time > 60s and event types > 20\n",
    "- import page stay time > 60s and event types > 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "192de3ac-dfd8-4a31-86b5-b639751e6598",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_2 = set_enter_date(class_2[class_2.Region==\"NA\"], 'class-2', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_2.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_2.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dee12228-1e92-4062-b895-dabbc79a9847",
   "metadata": {},
   "source": [
    "### Class 3\n",
    "\n",
    "conditions:\n",
    "- has create cluster and event types > 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a940e0d-27dd-40f0-97be-60613cd55b69",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_3 = set_enter_date(class_3[class_3.Region==\"NA\"], 'class-3', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_3.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_3.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0dd9d9d-3216-41cf-a8d4-c8bebccfeb82",
   "metadata": {},
   "source": [
    "### Others"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6df23ea5-2151-41e5-9412-fbfed0c63866",
   "metadata": {},
   "outputs": [],
   "source": [
    "class_other = set_enter_date(class_other[class_other.Region==\"NA\"], 'class-4', begin.date(), datetime.now(tz=timezone.utc).date())\n",
    "class_other.drop(['Region','Country'], inplace=True, axis=1)\n",
    "class_other.sort_values(by=['Event Types'], ascending=False, ignore_index=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.15 ('venv': venv)",
   "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.15"
  },
  "vscode": {
   "interpreter": {
    "hash": "58df2586bfa58a077d3724de15aeb118eace8b52e120c3852ce4ecef8931606a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}