{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49ec914d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.157103Z",
     "start_time": "2024-06-16T06:10:42.037547Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:01.322223Z",
     "iopub.status.busy": "2024-08-25T13:50:01.321792Z",
     "iopub.status.idle": "2024-08-25T13:50:01.347516Z",
     "shell.execute_reply": "2024-08-25T13:50:01.347137Z",
     "shell.execute_reply.started": "2024-08-25T13:50:01.322186Z"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from urllib.parse import quote\n",
    "\n",
    "import boto3\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "from botocore.exceptions import ClientError\n",
    "\n",
    "pd.set_option(\"display.max_rows\", 500)\n",
    "pd.set_option(\"display.max_columns\", 500)\n",
    "pd.set_option(\"display.width\", 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03adc798",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.158357Z",
     "start_time": "2024-06-16T06:10:42.158350Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:01.566375Z",
     "iopub.status.busy": "2024-08-25T13:50:01.565556Z",
     "iopub.status.idle": "2024-08-25T13:50:02.030523Z",
     "shell.execute_reply": "2024-08-25T13:50:02.029832Z",
     "shell.execute_reply.started": "2024-08-25T13:50:01.566344Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_secret():\n",
    "    secret_name = \"rds!cluster-a3b66c33-40a7-47dd-bd6e-32b1c17c9124\"\n",
    "    region_name = \"us-east-2\"\n",
    "    # Create a Secrets Manager client\n",
    "    session = boto3.session.Session()\n",
    "    client = session.client(service_name=\"secretsmanager\", region_name=region_name)\n",
    "    try:\n",
    "        get_secret_value_response = client.get_secret_value(SecretId=secret_name)\n",
    "    except ClientError as e:\n",
    "        raise e\n",
    "    secret = get_secret_value_response[\"SecretString\"]\n",
    "    return json.loads(secret)\n",
    "\n",
    "\n",
    "my_secrets = get_secret()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12c09177",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.159260Z",
     "start_time": "2024-06-16T06:10:42.159251Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:02.033056Z",
     "iopub.status.busy": "2024-08-25T13:50:02.032738Z",
     "iopub.status.idle": "2024-08-25T13:50:02.042827Z",
     "shell.execute_reply": "2024-08-25T13:50:02.042250Z",
     "shell.execute_reply.started": "2024-08-25T13:50:02.033029Z"
    }
   },
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine(\n",
    "    \"postgresql://postgres:%s@suno-main-pgdb-prod-analytics.cnfvffydbwvc.us-east-2.rds.amazonaws.com/suno_main\"\n",
    "    % quote(my_secrets[\"password\"])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f8ba40f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.159838Z",
     "start_time": "2024-06-16T06:10:42.159832Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:02.043850Z",
     "iopub.status.busy": "2024-08-25T13:50:02.043629Z",
     "iopub.status.idle": "2024-08-25T13:50:03.271071Z",
     "shell.execute_reply": "2024-08-25T13:50:03.270090Z",
     "shell.execute_reply.started": "2024-08-25T13:50:02.043827Z"
    }
   },
   "outputs": [],
   "source": [
    "df_all_tables = pd.read_sql_query(\n",
    "    \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\",\n",
    "    engine,\n",
    ")\n",
    "# should have all the basic table names here\n",
    "assert df_all_tables[\"table_name\"].nunique() >= 61"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f17f775",
   "metadata": {},
   "source": [
    "# Check one clip ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09cce26e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.160269Z",
     "start_time": "2024-06-16T06:10:42.160264Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:03.275368Z",
     "iopub.status.busy": "2024-08-25T13:50:03.274761Z",
     "iopub.status.idle": "2024-08-25T13:50:03.290778Z",
     "shell.execute_reply": "2024-08-25T13:50:03.289276Z",
     "shell.execute_reply.started": "2024-08-25T13:50:03.275335Z"
    }
   },
   "outputs": [],
   "source": [
    "def find_clip_info(clip_id, is_parent=True, print_meta=False):\n",
    "    if is_parent:\n",
    "        print(\"  Main clip query. \")\n",
    "        print(f\"check clip_id {clip_id}\")\n",
    "    else:\n",
    "        print(f\"  Child clip {clip_id}\")\n",
    "    df_clip = pd.read_sql_query(\n",
    "        f\"\"\"select * from bots_generatedclip \n",
    "        where id='{clip_id}';\"\"\",\n",
    "        engine,\n",
    "    )\n",
    "    if df_clip.shape[0] > 0:\n",
    "        if print_meta:\n",
    "            print(\"--> metadata is\", df_clip[\"metadata\"][0])\n",
    "        print(\"--> model is\", df_clip[\"model_name\"][0])\n",
    "        if \"concat_history\" in df_clip[\"metadata\"][0]:\n",
    "            for hist_clip in df_clip[\"metadata\"][0][\"concat_history\"]:\n",
    "                if not hist_clip[\"id\"].startswith(\"m_\"):\n",
    "                    _ = find_clip_info(\n",
    "                        hist_clip[\"id\"], is_parent=False, print_meta=print_meta\n",
    "                    )\n",
    "                else:\n",
    "                    print(\"-->--> is upload\")\n",
    "                    _ = find_clip_info(\n",
    "                        hist_clip[\"id\"].replace(\"m_\", \"\"),\n",
    "                        is_parent=False,\n",
    "                        print_meta=print_meta,\n",
    "                    )\n",
    "    return df_clip\n",
    "\n",
    "\n",
    "def find_request_info(request_id):\n",
    "    df_clips = pd.read_sql_query(\n",
    "        f\"\"\"select * from bots_generatedclip \n",
    "        where request_id='{request_id}';\"\"\",\n",
    "        engine,\n",
    "    )\n",
    "    print(\"found clips that match request id\", df_clips.shape[0])\n",
    "    return df_clips\n",
    "\n",
    "\n",
    "def find_playlist_info(playlist_id):\n",
    "    df_playlist = pd.read_sql_query(\n",
    "        f\"\"\"select * from bots_playlistclip \n",
    "        join bots_generatedclip on bots_playlistclip.clip_id = bots_generatedclip.id \n",
    "        join auth_user on bots_generatedclip.user_id = auth_user.id \n",
    "        where playlist_id ='{playlist_id}';\"\"\",\n",
    "        engine,\n",
    "    )\n",
    "    df_playlist.head()\n",
    "    return df_playlist\n",
    "\n",
    "\n",
    "def find_reaction_info(clip_id):\n",
    "    query = f\"\"\"\n",
    "    SELECT * FROM bots_userreaction\n",
    "    WHERE clip_id='{clip_id}'\n",
    "    \"\"\"\n",
    "    df_reactions = pd.read_sql_query(query, engine)\n",
    "    print(\"found reactions that match clip id\", df_reactions.shape[0])\n",
    "    return df_reactions\n",
    "\n",
    "\n",
    "def find_actions_info(clip_id):\n",
    "    query = f\"\"\"\n",
    "    SELECT * FROM bots_generatedclipextra\n",
    "    WHERE clip_id='{clip_id}'\n",
    "    \"\"\"\n",
    "    df_actions = pd.read_sql_query(query, engine)\n",
    "    print(\"found actions that match clip id\", df_actions.shape[0])\n",
    "    return df_actions\n",
    "\n",
    "def find_user_gens(user_id):\n",
    "    user_clips = pd.read_sql_query(\n",
    "        f\"\"\"select * from bots_generatedclip \n",
    "        where user_id='{user_id}';\"\"\",\n",
    "        engine,\n",
    "    )\n",
    "    print(\"found actions that match clip id\", user_clips.shape[0])\n",
    "    return user_clips"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29ef48f6",
   "metadata": {},
   "source": [
    "# Inspections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4f0769f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.161240Z",
     "start_time": "2024-06-16T06:10:42.161234Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-25T13:50:09.405668Z",
     "iopub.status.busy": "2024-08-25T13:50:09.405271Z",
     "iopub.status.idle": "2024-08-25T13:50:09.576033Z",
     "shell.execute_reply": "2024-08-25T13:50:09.575436Z",
     "shell.execute_reply.started": "2024-08-25T13:50:09.405640Z"
    }
   },
   "outputs": [],
   "source": [
    "clip_df = find_clip_info(\"977e2961-72ba-4bc3-84bb-fd23c05d9576\", print_meta=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59d39fc5-6418-4a6b-93fa-0c5e24221844",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-24T17:39:17.750382Z",
     "iopub.status.busy": "2024-08-24T17:39:17.749842Z",
     "iopub.status.idle": "2024-08-24T17:39:17.776649Z",
     "shell.execute_reply": "2024-08-24T17:39:17.776343Z",
     "shell.execute_reply.started": "2024-08-24T17:39:17.750314Z"
    }
   },
   "outputs": [],
   "source": [
    "clip_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4e75e91-d447-4560-9375-92f9ada923e1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-23T19:12:54.361957Z",
     "iopub.status.busy": "2024-08-23T19:12:54.361593Z",
     "iopub.status.idle": "2024-08-23T19:12:54.369768Z",
     "shell.execute_reply": "2024-08-23T19:12:54.368679Z",
     "shell.execute_reply.started": "2024-08-23T19:12:54.361930Z"
    }
   },
   "outputs": [],
   "source": [
    "clip_df[\"metadata\"].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "870cc8bb-a1ac-459d-9cfa-2d19758e0bfe",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-23T19:12:55.522548Z",
     "iopub.status.busy": "2024-08-23T19:12:55.522198Z",
     "iopub.status.idle": "2024-08-23T19:12:55.530404Z",
     "shell.execute_reply": "2024-08-23T19:12:55.529661Z",
     "shell.execute_reply.started": "2024-08-23T19:12:55.522522Z"
    }
   },
   "outputs": [],
   "source": [
    "clip_df[\"metadata\"].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f544b701-9c49-485b-8f60-fe90e3d541ce",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-24T17:39:36.201929Z",
     "iopub.status.busy": "2024-08-24T17:39:36.201559Z",
     "iopub.status.idle": "2024-08-24T17:39:36.395212Z",
     "shell.execute_reply": "2024-08-24T17:39:36.394117Z",
     "shell.execute_reply.started": "2024-08-24T17:39:36.201900Z"
    }
   },
   "outputs": [],
   "source": [
    "find_reaction_info(\"a2d682c8-6f2b-4140-874b-592f33cf2a72\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c9abeaa-f972-4e51-80dc-de9cc4abb6b8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-17T13:58:11.245421Z",
     "iopub.status.busy": "2024-07-17T13:58:11.245275Z",
     "iopub.status.idle": "2024-07-17T13:58:11.247616Z",
     "shell.execute_reply": "2024-07-17T13:58:11.247280Z",
     "shell.execute_reply.started": "2024-07-17T13:58:11.245407Z"
    }
   },
   "outputs": [],
   "source": [
    "for k, v in clip_df[\"metadata\"].values[0].items():\n",
    "    print(k)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b325fa8-5e07-437a-b3c5-0042b4f9b010",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-17T13:58:11.248362Z",
     "iopub.status.busy": "2024-07-17T13:58:11.248248Z",
     "iopub.status.idle": "2024-07-17T13:58:11.250089Z",
     "shell.execute_reply": "2024-07-17T13:58:11.249764Z",
     "shell.execute_reply.started": "2024-07-17T13:58:11.248353Z"
    }
   },
   "outputs": [],
   "source": [
    "# find_clip_info(\"7f1f3c70-b18b-495c-9a1a-bffe02bea20b\", print_meta=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f7f5319",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.161936Z",
     "start_time": "2024-06-16T06:10:42.161931Z"
    },
    "execution": {
     "iopub.execute_input": "2024-07-24T17:21:52.027487Z",
     "iopub.status.busy": "2024-07-24T17:21:52.027115Z",
     "iopub.status.idle": "2024-07-24T17:21:52.459348Z",
     "shell.execute_reply": "2024-07-24T17:21:52.458540Z",
     "shell.execute_reply.started": "2024-07-24T17:21:52.027460Z"
    }
   },
   "outputs": [],
   "source": [
    "request_df = find_request_info(\"46991798-9d26-4503-9c7f-909ca4f29bb9\")\n",
    "request_df\n",
    "# request_df[\"metadata\"].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81914e61",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.162598Z",
     "start_time": "2024-06-16T06:10:42.162586Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-02T22:26:04.992349Z",
     "iopub.status.busy": "2024-08-02T22:26:04.991938Z",
     "iopub.status.idle": "2024-08-02T22:26:05.244306Z",
     "shell.execute_reply": "2024-08-02T22:26:05.242869Z",
     "shell.execute_reply.started": "2024-08-02T22:26:04.992323Z"
    }
   },
   "outputs": [],
   "source": [
    "df_playlist = find_playlist_info(\"996dc20d-0367-456e-b520-0d577a4e9e77\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd5bf719-ed65-4c79-bb4f-5e682c09d10f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-02T22:28:26.763030Z",
     "iopub.status.busy": "2024-08-02T22:28:26.762228Z",
     "iopub.status.idle": "2024-08-02T22:28:26.769728Z",
     "shell.execute_reply": "2024-08-02T22:28:26.768744Z",
     "shell.execute_reply.started": "2024-08-02T22:28:26.762961Z"
    }
   },
   "outputs": [],
   "source": [
    "df_playlist.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce03266c-8196-41ce-a4f5-9cb748b2d7d7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-08-02T22:26:53.652692Z",
     "iopub.status.busy": "2024-08-02T22:26:53.652311Z",
     "iopub.status.idle": "2024-08-02T22:26:57.282129Z",
     "shell.execute_reply": "2024-08-02T22:26:57.281559Z",
     "shell.execute_reply.started": "2024-08-02T22:26:53.652660Z"
    }
   },
   "outputs": [],
   "source": [
    "for clip_id in df_playlist[\"s3_id\"].unique():\n",
    "    print(\"CLIP\", clip_id)\n",
    "    find_clip_info(clip_id, print_meta=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74b4c820-1fa0-4fb6-8b0a-71de10c11ed1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-17T13:58:11.798044Z",
     "iopub.status.busy": "2024-07-17T13:58:11.797031Z",
     "iopub.status.idle": "2024-07-17T13:58:11.947837Z",
     "shell.execute_reply": "2024-07-17T13:58:11.947013Z",
     "shell.execute_reply.started": "2024-07-17T13:58:11.798006Z"
    }
   },
   "outputs": [],
   "source": [
    "find_actions_info(\"236f6720-e815-4e36-8e72-504684c5100d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "426e1674",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-16T06:10:42.163092Z",
     "start_time": "2024-06-16T06:10:42.163087Z"
    },
    "execution": {
     "iopub.execute_input": "2024-08-23T20:17:50.128475Z",
     "iopub.status.busy": "2024-08-23T20:17:50.127841Z",
     "iopub.status.idle": "2024-08-23T20:17:50.445784Z",
     "shell.execute_reply": "2024-08-23T20:17:50.444706Z",
     "shell.execute_reply.started": "2024-08-23T20:17:50.128424Z"
    }
   },
   "outputs": [],
   "source": [
    "find_reaction_info(\"4f7c75a5-ba0f-4821-a82b-07586c302466\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "baddd10f-2c55-43e9-ad61-94a7f77f6b61",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-29T14:26:19.975192Z",
     "iopub.status.busy": "2024-07-29T14:26:19.974453Z",
     "iopub.status.idle": "2024-07-29T14:26:20.368728Z",
     "shell.execute_reply": "2024-07-29T14:26:20.367440Z",
     "shell.execute_reply.started": "2024-07-29T14:26:19.975132Z"
    }
   },
   "outputs": [],
   "source": [
    "df_user_clips = find_user_gens(30577922)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "312b28de-e2ca-4ac1-9073-010801c846d3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-29T14:26:20.720656Z",
     "iopub.status.busy": "2024-07-29T14:26:20.720248Z",
     "iopub.status.idle": "2024-07-29T14:26:21.193229Z",
     "shell.execute_reply": "2024-07-29T14:26:21.179586Z",
     "shell.execute_reply.started": "2024-07-29T14:26:20.720622Z"
    }
   },
   "outputs": [],
   "source": [
    "(df_user_clips[\"created_at\"].dt.hour + df_user_clips[\"created_at\"].dt.day * 24).hist(\n",
    "    bins=100\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45b932ad-a479-4739-9465-a97f17ad6df1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-26T19:12:03.255762Z",
     "iopub.status.busy": "2024-07-26T19:12:03.255380Z",
     "iopub.status.idle": "2024-07-26T19:12:03.263235Z",
     "shell.execute_reply": "2024-07-26T19:12:03.262442Z",
     "shell.execute_reply.started": "2024-07-26T19:12:03.255733Z"
    }
   },
   "outputs": [],
   "source": [
    "df_user_clips[\"play_count\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0c30128-c97d-4880-aac9-2e832cbeb1f7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-26T19:13:25.468718Z",
     "iopub.status.busy": "2024-07-26T19:13:25.468246Z",
     "iopub.status.idle": "2024-07-26T19:13:25.482614Z",
     "shell.execute_reply": "2024-07-26T19:13:25.481677Z",
     "shell.execute_reply.started": "2024-07-26T19:13:25.468672Z"
    }
   },
   "outputs": [],
   "source": [
    "df_user_clips[df_user_clips[\"created_at\"] > \"2024-07-26\"][\"model_name\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee63e750-e0ae-420f-90c7-56b38672617e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdd407c6-48a7-46b2-891c-029715025858",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-07-18T01:18:09.208534Z",
     "iopub.status.busy": "2024-07-18T01:18:09.208158Z",
     "iopub.status.idle": "2024-07-18T01:18:09.211836Z",
     "shell.execute_reply": "2024-07-18T01:18:09.211016Z",
     "shell.execute_reply.started": "2024-07-18T01:18:09.208506Z"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "455d0700-03d5-4c5a-892f-94612edf2547",
   "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.10.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}