import redis
import gspread

redis_url = 'redis://default:4ez0NlquzsD0LSiAUSNy@containers-us-west-34.railway.app:7369'
r = redis.from_url(redis_url)

gc = gspread.service_account()

# Open a sheet from a spreadsheet in one for sound
wks = gc.open("Labels").get_worksheet(1)

rows = [['sound id', 'label id', 'author', 'author_id', 'role', 'submitted on', 'label at', 'label', 'confidence (0 = low, 1 = medium, 2 = high)', 'sound url', 'dashboard url']]

redis_keys = r.keys('label:sound:*')
for i_key in redis_keys:
    sound_data_bytes = r.hgetall(i_key)
    sound_data = {k.decode('utf-8'): v.decode('utf-8') for k, v in sound_data_bytes.items()}
    sound_id = i_key.decode().split(':')[-2]
    raw_sound_data_bytes = r.hgetall(f'sound:{sound_id}'.encode('utf-8'))
    raw_sound_data = {k.decode('utf-8'): v.decode('utf-8') for k, v in raw_sound_data_bytes.items()}
    path = f'https://gainforest-transparency-dashboard.s3.amazonaws.com/{raw_sound_data["awsCID"]}'
    dashboard_url = f'https://gainforest.app/observations/{raw_sound_data["uuid"]}'
    row = [sound_id, i_key.decode().split(':')[-1], sound_data['author'], sound_data['author_id'], sound_data['role'], sound_data['timestamp'], sound_data['label_at'], sound_data['label'], sound_data['confidence'], path, dashboard_url]
    rows.append(row)

# Update a range of cells using the top left corner address
wks.update('A1', rows, value_input_option='USER_ENTERED')
wks.format('A1:I1', {'textFormat': {'bold': True}})