{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.insert(0, '/Users/joel/src/thundr/tracker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import prettytable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from dateutil import parser\n",
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "\n",
    "from tracker import config\n",
    "\n",
    "\n",
    "scope = ['https://spreadsheets.google.com/feeds']\n",
    "\n",
    "credentials = ServiceAccountCredentials.from_json_keyfile_dict(\n",
    "    config.google_auth_dict,\n",
    "    scopes=scope\n",
    ")\n",
    "\n",
    "client = gspread.authorize(credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s = client.get_spreadsheets_feed()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "de = client.open('Data Exports')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from tracker import db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating session....\n"
     ]
    }
   ],
   "source": [
    "db.setup_session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.Session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(postgres://bot:***@whitelist.cbn1zk7uet6r.eu-west-1.rds.amazonaws.com:5432/whitelist)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Session.get_bind()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def array_to_sheet(arr, sheet, start_row=0, start_col=0):\n",
    "    mapping = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'\n",
    "    range_str = '{start_col}{start_row}:{end_col}{end_row}'.format(\n",
    "        start_col=mapping[start_col],\n",
    "        start_row=1,\n",
    "        end_col=mapping[len(arr[0])+start_col-1],\n",
    "        end_row=len(arr)\n",
    "    )\n",
    "    # Select a range\n",
    "    cell_list = sheet.range(range_str)\n",
    "    \n",
    "    cell_stack = list(reversed(cell_list))\n",
    "    \n",
    "    for row in arr:\n",
    "        for field in row:\n",
    "            cell = cell_stack.pop()\n",
    "            cell.value = field\n",
    "\n",
    "    # Update in batch\n",
    "    sheet.update_cells(cell_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def query_to_sheet(query, sheet, params=None, start_col=0):\n",
    "    result_proxy = db.Session.execute(query, params)\n",
    "    names = [a.name for a in result_proxy.cursor.description]\n",
    "    \n",
    "    arr = [names] + result_proxy.fetchall()\n",
    "    \n",
    "    array_to_sheet(arr, sheet, start_col=start_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def pretty_query(sql, params=None):\n",
    "    result_proxy = db.Session.execute(sql, params)\n",
    "    print(prettytable.from_db_cursor(result_proxy.cursor))\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sql1 = '''\n",
    "select * from \n",
    "spy_playlist_track join spy_playlist on playlist_spyid = spy_playlist.spyid \n",
    "where track_spyid = '4gipAzWetrklPX9RlJWO61' \n",
    "order by first_seen\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alice Merton"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+\n",
      "|         spyid          |\n",
      "+------------------------+\n",
      "| 7f0OLhGgBMX9fUjm1dcPip |\n",
      "+------------------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select spyid from spy_artists where name = 'Alice Merton'\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "w2 = de.add_worksheet('Test3', 100, 20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "alice_spyid = '7f0OLhGgBMX9fUjm1dcPip'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+------------------------+----------------------------+-------------------------------+\n",
      "|         spyid          |          name          |         first_seen         |             Label             |\n",
      "+------------------------+------------------------+----------------------------+-------------------------------+\n",
      "| 7GSuJlP3XN550RQD8t88mj |        No Roots        | 2016-12-05 08:24:12.167711 | 2016 Paper Plane Records Int. |\n",
      "| 1L6Sl0eq9rb4cZwEv9OWd8 | Hit The Ground Running | 2017-02-03 03:00:26.123265 |              None             |\n",
      "| 4bPWDXpN6MlTZBedxega3X |        No Roots        | 2017-04-29 03:00:11.571033 |              None             |\n",
      "+------------------------+------------------------+----------------------------+-------------------------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select spyid, name, first_seen, album_data->'copyrights'->0->'text' as \"Label\" from spy_tracks \n",
    "where primary_artist_spyid = '7f0OLhGgBMX9fUjm1dcPip'\n",
    "order by first_seen asc\n",
    "''')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alice Merton's first track was _'No Roots'_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "popularity_sql = '''\n",
    "select as_of, value from spy_track_popularity where track_spyid = :track_spyid\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+-------+\n",
      "|        as_of        | value |\n",
      "+---------------------+-------+\n",
      "| 2016-12-05 08:00:00 |   6   |\n",
      "| 2016-12-06 08:00:00 |   13  |\n",
      "| 2016-12-07 08:00:00 |   16  |\n",
      "| 2016-12-08 08:00:00 |   43  |\n",
      "| 2016-12-09 08:00:00 |   47  |\n",
      "| 2016-12-10 08:00:00 |   47  |\n",
      "| 2016-12-12 09:00:00 |   52  |\n",
      "| 2016-12-13 09:00:00 |   54  |\n",
      "| 2016-12-14 08:00:00 |   55  |\n",
      "| 2016-12-16 08:00:00 |   56  |\n",
      "| 2016-12-17 08:00:00 |   57  |\n",
      "| 2016-12-18 08:00:00 |   57  |\n",
      "| 2016-12-20 03:00:00 |   57  |\n",
      "| 2016-12-21 03:00:00 |   57  |\n",
      "| 2016-12-22 03:00:00 |   57  |\n",
      "| 2016-12-23 03:00:00 |   57  |\n",
      "| 2016-12-24 03:00:00 |   58  |\n",
      "| 2016-12-25 03:00:00 |   58  |\n",
      "| 2016-12-26 03:00:00 |   58  |\n",
      "| 2016-12-27 03:00:00 |   58  |\n",
      "| 2016-12-28 03:00:00 |   58  |\n",
      "| 2016-12-29 03:00:00 |   58  |\n",
      "| 2016-12-30 03:00:00 |   58  |\n",
      "| 2016-12-31 03:00:00 |   58  |\n",
      "| 2017-01-01 03:00:00 |   58  |\n",
      "| 2017-01-02 03:00:00 |   58  |\n",
      "| 2017-01-03 03:00:00 |   58  |\n",
      "| 2017-01-04 03:00:00 |   58  |\n",
      "| 2017-01-05 03:00:00 |   59  |\n",
      "| 2017-01-06 03:00:00 |   59  |\n",
      "| 2017-01-07 03:00:00 |   59  |\n",
      "| 2017-01-08 03:00:00 |   59  |\n",
      "| 2017-01-09 03:00:00 |   60  |\n",
      "| 2017-01-10 03:00:00 |   60  |\n",
      "| 2017-01-11 03:00:00 |   60  |\n",
      "| 2017-01-12 03:00:00 |   60  |\n",
      "| 2017-01-13 03:00:00 |   60  |\n",
      "| 2017-01-14 03:00:00 |   60  |\n",
      "| 2017-01-15 03:00:00 |   61  |\n",
      "| 2017-01-16 03:00:00 |   61  |\n",
      "| 2017-01-17 03:00:00 |   61  |\n",
      "| 2017-01-18 03:00:00 |   61  |\n",
      "| 2017-01-19 03:00:00 |   61  |\n",
      "| 2017-01-20 03:00:00 |   61  |\n",
      "| 2017-01-21 03:00:00 |   60  |\n",
      "| 2017-01-22 03:00:00 |   60  |\n",
      "| 2017-01-23 03:00:00 |   60  |\n",
      "| 2017-01-24 03:00:00 |   60  |\n",
      "| 2017-01-25 03:00:00 |   60  |\n",
      "| 2017-01-26 03:00:00 |   60  |\n",
      "| 2017-01-27 03:00:00 |   60  |\n",
      "| 2017-01-28 03:00:00 |   60  |\n",
      "| 2017-01-31 03:00:00 |   60  |\n",
      "| 2017-02-08 22:00:00 |   61  |\n",
      "| 2017-02-10 03:00:00 |   61  |\n",
      "| 2017-02-11 03:00:00 |   61  |\n",
      "| 2017-02-12 03:00:00 |   61  |\n",
      "| 2017-02-13 03:00:00 |   61  |\n",
      "| 2017-02-14 03:00:00 |   61  |\n",
      "| 2017-02-15 03:00:00 |   61  |\n",
      "| 2017-02-16 03:00:00 |   61  |\n",
      "| 2017-02-17 03:00:00 |   61  |\n",
      "| 2017-02-18 03:00:00 |   61  |\n",
      "| 2017-02-19 03:00:00 |   61  |\n",
      "| 2017-02-20 03:00:00 |   61  |\n",
      "| 2017-02-21 03:00:00 |   60  |\n",
      "| 2017-02-22 03:00:00 |   60  |\n",
      "| 2017-02-23 03:00:00 |   59  |\n",
      "| 2017-02-24 03:00:00 |   59  |\n",
      "| 2017-02-25 03:00:00 |   59  |\n",
      "| 2017-02-26 03:00:00 |   59  |\n",
      "| 2017-02-27 03:00:00 |   59  |\n",
      "| 2017-02-28 03:00:00 |   57  |\n",
      "| 2017-03-12 03:00:00 |   54  |\n",
      "| 2017-03-13 03:00:00 |   53  |\n",
      "| 2017-03-19 03:00:00 |   52  |\n",
      "| 2017-03-27 03:00:00 |   50  |\n",
      "| 2017-04-03 03:00:00 |   49  |\n",
      "| 2017-04-17 03:00:00 |   46  |\n",
      "| 2017-04-23 03:00:00 |   45  |\n",
      "| 2017-04-28 03:00:00 |   44  |\n",
      "| 2017-05-01 03:00:00 |   43  |\n",
      "| 2017-05-21 03:00:00 |   36  |\n",
      "| 2017-05-25 03:00:00 |   35  |\n",
      "| 2017-05-26 03:00:00 |   34  |\n",
      "| 2017-05-27 03:00:00 |   34  |\n",
      "| 2017-05-28 03:00:00 |   34  |\n",
      "| 2017-05-29 03:00:00 |   34  |\n",
      "+---------------------+-------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query(popularity_sql, params=dict(track_spyid='7GSuJlP3XN550RQD8t88mj'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_to_sheet(popularity_sql, de.add_worksheet('Alice Merton', 300, 30))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "playlists_sql = '''\n",
    "select \n",
    "    first_seen::date \"First Seen\", \n",
    "    last_seen::date \"Off List\", \n",
    "    last_seen::date-first_seen::date as \"days on list\", \n",
    "    first_position \"First Position\", \n",
    "    last_position \"Last Position\",\n",
    "    name \"Playlist\" \n",
    "from spy_playlist_track \n",
    "join spy_playlist on playlist_spyid = spy_playlist.spyid where track_spyid = :track_spyid\n",
    "order by first_seen\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_to_sheet(playlists_sql, de.add_worksheet('Alice Merton3', 300, 30))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Soundcloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sc_find_artist_sql = '''select scid, name from sc_users where name like :name_like '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------+\n",
      "|   scid   |     name     |\n",
      "+----------+--------------+\n",
      "| 12765548 | Alice Merton |\n",
      "+----------+--------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query(sc_find_artist_sql, params=dict(name_like='Alice Merton'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sc_tracks_sql = '''select scid, name from sc_tracks where artist_scid = :artist_scid '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----------+\n",
      "|    scid   |   name   |\n",
      "+-----------+----------+\n",
      "| 295580498 | No Roots |\n",
      "+-----------+----------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query(sc_tracks_sql, params=dict(artist_scid='12765548'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+-------------+----------------------------+----------------+\n",
      "| influencer_scid | artist_scid |          seen_at           |      name      |\n",
      "+-----------------+-------------+----------------------------+----------------+\n",
      "|     29399459    |   12765548  | 2016-11-08 01:00:08.818096 |  Mattia Villa  |\n",
      "|     3430403     |   12765548  | 2017-01-31 21:40:07.965930 |  Sam Maroste   |\n",
      "|     14114104    |   12765548  | 2017-06-07 22:33:31.166794 | Mirco Niemeier |\n",
      "+-----------------+-------------+----------------------------+----------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select \n",
    "sc_followings.*,\n",
    "sc_users.name\n",
    "from sc_followings join sc_users on influencer_scid = sc_users.scid where artist_scid = '12765548'\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------------+-----------------+------------+----------------------------+\n",
      "|  track   |    influencer   | influencer_scid | track_scid |          seen_at           |\n",
      "+----------+-----------------+-----------------+------------+----------------------------+\n",
      "| No Roots | New Music Talks |    110083093    | 295580498  | 2016-12-05 15:00:19.964230 |\n",
      "| No Roots |  Pause Musicale |     9913758     | 295580498  | 2016-12-11 14:59:41.930706 |\n",
      "+----------+-----------------+-----------------+------------+----------------------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select \n",
    "st.name track, \n",
    "sc_users.name influencer,\n",
    "sc_likes.*\n",
    "from sc_tracks st join sc_likes on sc_likes.track_scid = st.scid \n",
    "join sc_users on sc_users.scid = sc_likes.influencer_scid \n",
    "where st.artist_scid = '12765548'\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### San Holo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+\n",
      "|         spyid          |\n",
      "+------------------------+\n",
      "| 0jNDKefhfSbLR9sFvcPLHo |\n",
      "+------------------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select spyid from spy_artists where name = 'San Holo'\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------+--------------------------+----------------------------+--------------+\n",
      "|         spyid          |           name           |         first_seen         |    Label     |\n",
      "+------------------------+--------------------------+----------------------------+--------------+\n",
      "| 74Ru27B7Jx8mBt5MGvGLLv |          Light           | 2016-11-25 08:19:26.845269 | 2016 bitbird |\n",
      "| 6jq6rcOikCZAmjliAgAmfT |          Light           | 2017-02-10 03:00:31.093143 |     None     |\n",
      "| 2rkCYgvzyHp1AESIlJcqqY |   Light - GOSLO Remix    | 2017-04-22 03:00:05.578047 |     None     |\n",
      "| 2OEwpiyu1J0fwUtf7zc0Ou |   Light - Loosid Remix   | 2017-06-24 03:00:06.791492 |     None     |\n",
      "| 4eqlSNbFyPM8EuaqhcC39r |        The Future        | 2017-06-30 03:00:48.734875 |     None     |\n",
      "| 0TERWVCH2clPTfg0HCtsTc | Light - BeauDamian Remix | 2017-08-26 03:00:07.498055 |     None     |\n",
      "| 3R7Y4q7kECcZkT5LCn8QP5 |  I Still See Your Face   | 2017-09-15 03:00:05.304671 |     None     |\n",
      "+------------------------+--------------------------+----------------------------+--------------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query('''\n",
    "select spyid, name, first_seen, album_data->'copyrights'->0->'text' as \"Label\" from spy_tracks \n",
    "where primary_artist_spyid = '0jNDKefhfSbLR9sFvcPLHo'\n",
    "order by first_seen asc\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sh1 = de.add_worksheet('San Holo1', 400, 30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_to_sheet(popularity_sql, sheet=sh1, params=dict(track_spyid='74Ru27B7Jx8mBt5MGvGLLv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_to_sheet(playlists_sql, sheet=sh1, start_col=4, params=dict(track_spyid='74Ru27B7Jx8mBt5MGvGLLv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------+\n",
      "| scid | name |\n",
      "+------+------+\n",
      "+------+------+\n"
     ]
    }
   ],
   "source": [
    "pretty_query(sc_find_artist_sql, params=dict(name_like='sanholobeats'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}