from tracker.bangers import (
    run_frunches,
    get_or_create_google_sheet,
    share_and_email,
    move_settings_tab_to_end,
)
from tracker.logger import get_logger
from datetime import date
import os

logger = get_logger(__name__)

logger.info("Starting R+H Charts")

logger.info("Running data update.")

FULL_UPDATE = True

from tracker.bangers import randh

if FULL_UPDATE:
    randh.update_all()

logger.info("Update complete creating sheet")

template_url = "https://docs.google.com/spreadsheets/d/1_zKqJPlsMmp-5n2uJgb0etpW63V1XwucgGw2SLPDloM"

logger.info(f"Creating copy of template sheet")

ss = get_or_create_google_sheet(
    template_url, title_prefix="R+H Charts", make_copy_of_existing_sheet=True
)

created_sheet_url = ss.url


from tracker.bangers import randh, sc_charts, query_to_sheet
from pycommon.genius.sc_charts import genres
from pycommon.database import db as cdb


cdb.setup_randh_session()


def do_sc_charts():
    for genre in genres:
        sc_charts.add_chart_to_sheet(ss, genre)


def do_genius(as_of=None):
    as_of = as_of or date.today()

    last_stats_date = cdb.select_single_value(
        """ select max(as_of) from genius_song_chart_positions where as_of < :as_of """,
        params=dict(as_of=as_of),
    )

    days_between = (as_of - last_stats_date).days

    logger.info("Most recent genius stats on ")

    result_proxy = cdb.RandHSession.execute(
        f"""
with climbers as (
  select
    b.genius_id as genius_id,
    b.position as current,
    a.position as previous,
    a.position - b.position as change
  from genius_song_chart_positions b
  left join genius_song_chart_positions a
    on b.genius_id = a.genius_id
    and a.as_of = :last_stats_date
  where b.as_of = :as_of_date
  )
select
--   genius_id,
  '=HYPERLINK("' || g.url || '", "genius.com")' as "Link",
--   first_seen,
  g.title "Title",
  g.primary_artist_name "Artist",
  g.last_chart_position "Position",
  case when c.change is null then 'New' else c.change::text end "{days_between} Day Change",
  g.last_pyongs_count "Shares",
  g.last_page_views "Page Views",
  g.last_annotation_count "Annotation Count"
from genius_songs g
join climbers c using (genius_id)
where primary_artist_name not in
  ('Genius Romanizations', 'Genius English Translations')
  and primary_artist_name not in (select name from well_known_artist_names)
  and (lang1 is null or lang1 = 'en' or lang1score < 0.9)
  and classify_lang_script(json_data ->> 'full_title') = 0
order by c.change desc nulls first
""",
        params=dict(as_of_date=as_of, last_stats_date=last_stats_date),
    )

    col_names = [a.name for a in result_proxy.cursor.description]
    csv = [col_names] + [list(row) for row in result_proxy.cursor]
    query_to_sheet.csv_to_sheet(
        csv, spreadsheet=ss, sheet_name="Genius", insert_at_beginning=False
    )


logger.info("Doing genius")
do_genius()

logger.info("Doing sc charts")
do_sc_charts()

logger.info("Moving settings")
move_settings_tab_to_end(ss)

logger.info("Sharing to the team")
if FULL_UPDATE:
    share_and_email(ss)