#! /usr/bin/env python
import sys
from getpass import getpass

import requests
import json

sys.path.insert(0, '.')

import os
from datetime import datetime, date

from tracker import sheet as ws, db, stream_collection

db.setup_session()

workbook = stream_collection.open_workbook()

today_iso = date.today().isoformat()


def add_title_row(sheet):
    header_cells = sheet.range('A1:E1')
    titles = ['As Of Date', 'Title', 'URI', 'Days Ago', 'Streams']
    for cell, title in zip(header_cells, titles):
        cell.value = title
    sheet.update_cells(header_cells)


def create_sheet_from_query(sheet_name=None):

    sql = '''
select
  t.spyid,
  t.name,
  st.as_of,
  st.streams,
  CURRENT_DATE - (t.album_data->>'release_date')::date released_days_ago,
  case (CURRENT_DATE - (t.album_data->>'release_date')::date)
    when 104 then 1
    when 103 then 1
    when 102 then 1
    when 101 then 1
    when 100 then 1
    when 99 then 1

    when 8 then 2
    when 7 then 2
    when 6 then 2
    when 5 then 2
    when 4 then 2

    when 3 then 3
    when 2 then 3
    when 1 then 3
    
    when 204 then 4
    when 203 then 4
    when 202 then 4
    when 201 then 4
    when 200 then 4

    when 199 then 5
    when 198 then 5
    when 197 then 5
    
    when 99 then 5
    when 98 then 5
    when 97 then 5
    when 96 then 5
    
    else 4
  end as priority
from spy_tracks t
left join spy_track_streams st
    on st.spyid = t.spyid
    and st.as_of - (t.album_data->>'release_date')::date in (
    -- 1,2,3,4,5,6,7,
    -- 17,18,19,20,21,22,23,
    -- 47,48,49,50,51,52,53,
    97,98,99,100,101,102,104
    -- 197,198,199,200,201,202,203
    )
where length(t.album_data->>'release_date') = 10
  and (CURRENT_DATE - (t.album_data->>'release_date')::date) in (
    -- 1,2,3,4,5,6,7,
    -- 17,18,19,20,21,22,23,
    -- 47,48,49,50,51,52,53,
    97,98,99,100,101,102,104
    -- 197,198,199,200,201,202,203
  )
  and (t.data->>'popularity')::int > 10
  and not t.tags ?| array['label-sony', 'label-warner', 'label-universal', 'label-other']
  and (t.album_data ->>'album_type' = 'single')
  and t.first_seen::date - (t.album_data->>'release_date')::date < 4
  and st.spyid is null
order by priority asc, spyid asc
    '''

    tracks = list(db.execute_to_dicts(sql))
    print(f"There are {len(tracks)} tracks for consideration")

    new_sheet = workbook.add_worksheet(sheet_name or today_iso, len(tracks) + 1, 5)

    add_title_row(new_sheet)
    matrix = new_sheet.range(f'A2:D{len(tracks)+1}')
    reshaped = list(zip(matrix[0::4], matrix[1::4], matrix[2::4], matrix[3::4]))

    for db_row, cells in zip(tracks, reshaped):
        cells[0].value = today_iso
        cells[1].value = db_row['name']
        cells[2].value = 'spotify:track:{}'.format(db_row['spyid'])
        cells[3].value = db_row['released_days_ago']
        print("Updated row:", cells)

    new_sheet.update_cells(matrix)


def mac_open_player(song_uri):
    print(f"Opening player to {song_uri}")
    os.system(f'''osascript -e 'open location "{song_uri}" ' ''')


def mac_record_streams(sheet_name=None):
    sheet = workbook.worksheet(sheet_name or today_iso)
    rows = stream_collection.get_tracks_from_sheet(sheet)

    if not rows:
        print("Looks like the sheet is completed.")
        return

    for title, uri, days_ago, streams in rows:
        if len(streams.value) > 0:
            print(f"Skipping {title.value} / {streams.value}")
            continue

        print(f"\nTrack: {title.value} released {days_ago.value} days ago.")
        mac_open_player(uri.value)
        recorded_streams = get_stream_count()
        streams.value = recorded_streams

        as_of = today_iso
        streams_count = streams.value
        stream_collection.save_in_db(as_of, streams_count, uri.value)
        db.Session.commit()
        sheet.update_cells([streams])

        print("Recorded.")

class ApiHelper():

    @classmethod
    def login(cls, domain='https://api.whtlst.in'):
        username = input("Username: ")
        password = getpass("Password: ")
        try:
            res = requests.get(f'{domain}/api/login', auth=(username, password))
            if res.status_code != 200:
                print(f"Those credentials didn't work. {res} \n{res.text}")
                return cls.login(domain=domain)

        except Exception as e:
            print(f"Those credentials didn't work: {e}. \nTry again...\n")
            return cls.login(domain=domain)
        return ApiHelper(username, password, domain)


    def __init__(self, username, password, domain):
        self.domain = domain
        self.session = requests.Session()
        self.session.auth = (username, password)
        self.session.headers = {'Content-Type': 'application/json'}

    def get_rows(self, sheet_name=None):
        sheet_name = sheet_name or 'current'
        return self.session.get(f'{self.domain}/api/admin/_spy_streams/{sheet_name}').json()

    def put_stream(self, uri, as_of, streams, cell_row, sheet_name=None):
        datadict = dict(uri=uri, as_of=as_of, streams=streams, cell_row=cell_row)
        print("Putting", datadict)
        sheet_name = sheet_name or 'current'
        res = self.session.put(
            f'{self.domain}/api/admin/_spy_streams/{sheet_name}',
            json=datadict,
        )
        if res.status_code != 204:
            print (f"Something went wrong!! \n {res.text}\n")
            return False
        return True


def begin_api_based_record(sheet_name=None, domain='https://api.whtlst.in'):
    api = ApiHelper.login(domain=domain)
    rows = api.get_rows(sheet_name=sheet_name)
    print (f"There are {len(rows)} rows to process")
    for rownum, row in enumerate(rows):
        title, uri, days_ago, streams, cell_num = row

        if len(streams.value) > 0:
            print(f"Skipping {title.value} / {streams.value}")
            continue

        print(f"\nTrack: {title.value} released {days_ago.value} days ago.")
        mac_open_player(uri.value)
        recorded_streams = get_stream_count()
        streams.value = recorded_streams

        as_of = today_iso
        streams_count = streams.value
        saved = api.put_stream(uri, as_of, streams_count, rownum)
        if saved:
            print("Recorded.")



def record_sheet_to_db(sheet_name=None):
    sheet = workbook.worksheet(sheet_name or today_iso)
    matrix = sheet.range('A{}:E{}'.format(2, sheet.row_count))
    reshaped = reshape(matrix, 5)
    for as_of, title, uri, days_ago, streams in reshaped:
        if len(streams.value) > 0:
            stream_collection.save_in_db(as_of.value, streams.value, uri.value)
            db.Session.commit()

def reshape(matrix, num_cols):
    return list(zip(*[matrix[i::num_cols] for i in range(num_cols)]))


def get_stream_count():
    num = input("Streams: ")
    if len(num) == 0:
        print('invalid count, try again')
        return get_stream_count()
    num = num.replace(',', '')
    mult = 1
    if num.lower().endswith('k'):
        mult = 1000
        num = num.lower().strip('k')
    try:
        return int(float(num) * mult)
    except ValueError:
        print('invalid count, try again')
        return get_stream_count()



import IPython

IPython.embed()