import pandas as pd
from siuba.tests.helpers import copy_to_sql
from siuba import *
from siuba.dply.vector import lag, desc, row_number
from siuba.dply.string import str_c
from siuba.sql import LazyTbl

data_url = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2019/2019-01-08/IMDb_Economist_tv_ratings.csv"
tv_ratings = pd.read_csv(data_url, parse_dates = ["date"])


db_uri = "postgresql://{user}:{password}@localhost:5433/{db}".format(
                user = "postgres",
                password = "",
                db = "postgres"
                )

# create tv_ratings table
tbl_ratings = copy_to_sql(tv_ratings, "tv_ratings", db_uri)

# can also access an existing table
tbl_ratings = LazyTbl(db_uri, "tv_ratings")

tbl_ratings


"""
Explanation: Querying SQL (advanced)
NOTE: THIS DOC IS CURRENTLY IN OUTLINE FORM
In this tutorial, we'll use a dataset of television ratings.

copying data in, and getting a table from SQL
filtering out rows, and aggregating data
looking at shifts in ratings between seasons
checking for abnormalities in the data

Setting up
End of explanation
"""


buffy = (tbl_ratings
  >> filter(_.title == "Buffy the Vampire Slayer")
  >> collect()
  )

buffy

buffy >> summarize(avg_rating = _.av_rating.mean())

"""
Explanation: Inspecting a single show
End of explanation
"""


avg_ratings = (tbl_ratings 
  >> group_by(_.title)
  >> summarize(
       avg_rating = _.av_rating.mean(),
       date_range = str_c(_.date.dt.year.max(), " - ", _.date.dt.year.min())
       )
  )

avg_ratings

"""
Explanation: Average rating per show, along with dates
End of explanation
"""


top_4_shifts = (tbl_ratings
  >> group_by(_.title)
  >> arrange(_.seasonNumber)
  >> mutate(rating_shift = _.av_rating - lag(_.av_rating))
  >> summarize(
       max_shift = _.rating_shift.max()
     )
  >> arrange(-_.max_shift)
  >> head(4)
  )

top_4_shifts

big_shift_series = (top_4_shifts
  >> select(_.title)
  >> inner_join(_, tbl_ratings, "title")
  >> collect()
  )

from plotnine import *

(big_shift_series
  >> ggplot(aes("seasonNumber", "av_rating"))
   + geom_point()
   + geom_line()
   + facet_wrap("~ title")
   + labs(
       title = "Seasons with Biggest Shifts in Ratings",
       y = "Average rating",
       x = "Season"
     )
  )

"""
Explanation: Biggest changes in ratings between two seasons
End of explanation
"""


mismatches = (tbl_ratings
  >> arrange(_.title, _.seasonNumber)
  >> group_by(_.title)
  >> mutate(
       row = row_number(_),
       mismatch = _.row != _.seasonNumber
     )
  >> filter(_.mismatch.any())
  >> ungroup()
  )


mismatches

mismatches >> distinct(_.title) >> count() >> collect()

"""
Explanation: Do we have full data for each season?
End of explanation
"""


