{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup tailscale if you haven't\n",
    "# https://tailscale.com/kb/1031/install-linux\n",
    "#!sudo tailscale up --accept-routes=true\n",
    "\n",
    "# setup autoload\n",
    "#%load_ext autoreload\n",
    "#%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "from collections import defaultdict\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "J~5Rdqk%5DBn%21umF7uLA6nDr%7CIg%5BBK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Database connection parameters\n",
    "db_config = {\n",
    "    'host': \"suno-main-postgres-prod-analytics.cnfvffydbwvc.us-east-2.rds.amazonaws.com\",  # or your server address\n",
    "    'database': \"suno_main\",\n",
    "    'user': \"suno\",\n",
    "    'password': postgres_pw,\n",
    "    'port': '5432'  # default PostgreSQL port\n",
    "}\n",
    "\n",
    "# Create connection string\n",
    "conn_string = f\"postgresql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}\"\n",
    "\n",
    "# Create engine and read table\n",
    "engine = create_engine(conn_string)\n",
    "df = pd.read_sql_table(\"label_maker_annotations\", engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actual = df[df['skipped'] == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actual = df_actual[df_actual['total_listen_time_seconds'] <= 10 * 60]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "skipped = len(df) - len(df_actual)\n",
    "prcnt_skipped = skipped / len(df) * 100\n",
    "print(f\"{skipped} tasks skipped, {prcnt_skipped:.2f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 6))\n",
    "plt.hist(df_actual['total_listen_time_seconds'], bins=30, alpha=0.7, color='skyblue', edgecolor='black')\n",
    "\n",
    "# Customize the plot\n",
    "plt.title('Distribution of Total Listen Time', fontsize=16, fontweight='bold')\n",
    "plt.xlabel('Total Listen Time (seconds)', fontsize=12)\n",
    "plt.ylabel('Frequency', fontsize=12)\n",
    "plt.grid(True, alpha=0.3)\n",
    "\n",
    "# Optional: Add statistics to the plot\n",
    "mean_time = df['total_listen_time_seconds'].mean()\n",
    "plt.axvline(mean_time, color='red', linestyle='--', linewidth=2, label=f'Mean: {mean_time:.1f}s')\n",
    "plt.legend()\n",
    "\n",
    "# Show the plot\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_winners = ['clip_honeypot_good', 'clip_bluejay_t2', 'clip_auk_t1', 'clip_honeypot_bad']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actual['winner'] = df_actual['preferences'].apply(lambda x: x['overall_sound'] if x['overall_sound'] in valid_winners else None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = df_actual['winner'].value_counts()\n",
    "percentages = df_actual['winner'].value_counts(normalize=True) * 100\n",
    "\n",
    "for winner, count in counts.items():\n",
    "    print(f\"{winner}: {count} ({percentages[winner]:.1f}%)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_candidates = set()\n",
    "for listen_dict in df_actual['listen_time_seconds']:\n",
    "    if isinstance(listen_dict, dict):\n",
    "        all_candidates.update(listen_dict.keys())\n",
    "\n",
    "all_candidates = valid_winners\n",
    "print(f\"All candidates: {all_candidates}\")\n",
    "\n",
    "# Step 2: Create confusion matrix data\n",
    "confusion_data = defaultdict(lambda: defaultdict(int))\n",
    "\n",
    "for idx, row in df_actual.iterrows():\n",
    "    winner = row['winner']\n",
    "    listen_dict = row['listen_time_seconds']\n",
    "    \n",
    "    if isinstance(listen_dict, dict) and winner in listen_dict:\n",
    "        # For each candidate that lost to the winner\n",
    "        for candidate in listen_dict.keys():\n",
    "            if candidate != winner:\n",
    "                confusion_data[winner][candidate] += 1\n",
    "\n",
    "# Step 3: Convert to DataFrame for easier viewing\n",
    "confusion_matrix = pd.DataFrame(\n",
    "    [[confusion_data[winner][loser] for loser in all_candidates] \n",
    "     for winner in all_candidates],\n",
    "    index=all_candidates,\n",
    "    columns=all_candidates\n",
    ")\n",
    "\n",
    "print(\"\\nConfusion Matrix (rows = winners, columns = losers):\")\n",
    "print(confusion_matrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 8))\n",
    "sns.heatmap(confusion_matrix, annot=True, fmt='d', cmap='Blues')\n",
    "plt.title('Confusion Matrix: How Often Each Candidate Beats Others')\n",
    "plt.xlabel('Candidates (Losers)')\n",
    "plt.ylabel('Candidates (Winners)')\n",
    "plt.xticks(rotation=45)\n",
    "plt.yticks(rotation=0)\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# Step 5: Summary statistics\n",
    "print(\"\\nWin totals by candidate:\")\n",
    "win_totals = confusion_matrix.sum(axis=1).sort_values(ascending=False)\n",
    "print(win_totals)\n",
    "\n",
    "print(\"\\nLoss totals by candidate:\")\n",
    "loss_totals = confusion_matrix.sum(axis=0).sort_values(ascending=False)\n",
    "print(loss_totals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_feedback = df_actual[df_actual['feedback'].str.len() > 10]\n",
    "print(f\"{len(has_feedback)} clips with feedback\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_feedback.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for value in has_feedback['feedback']:\n",
    "    print(value)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "suno_env",
   "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}