import pytz
import datetime
import psycopg2

conn_string = "host='localhost' dbname='voxsnap' user='crate' port='5442' password=''"

cursor.execute(
  """SELECT DISTINCT CASE WHEN "os" IN (%s, %s, %s, %s, %s, %s, %s, %s) THEN %s WHEN "os" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s) THEN %s WHEN "os" IN (%s) THEN %s WHEN ("os" IS NULL OR "os" IN (%s, %s, %s, %s, %s, %s)) THEN %s ELSE NULL END AS "device_type", COUNT(*) AS "no_of_plays" FROM "voxsnap"."events_2018" WHERE ("os" IS NOT NULL AND CAST("event_time" as timestamp) >= %s AND CAST("event_time" as timestamp) < %s AND "event_type" IN (%s, %s, %s)) GROUP BY CASE WHEN "os" IN (%s, %s, %s, %s, %s, %s, %s, %s) THEN %s WHEN "os" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s) THEN %s WHEN "os" IN (%s) THEN %s WHEN ("os" IS NULL OR "os" IN (%s, %s, %s, %s, %s, %s)) THEN %s ELSE NULL END ORDER BY "no_of_plays" DESC  LIMIT 1""",
  ('Android', 'BlackBerry OS', 'Firefox OS', 'Maemo', 'Symbian OS', 'Tizen', 'Windows Phone', 'iOS', 'mobile', 'Linux', 'Fedora', 'Mac OS X', 'FreeBSD', 'NetBSD', 'OpenBSD', 'Solaris', 'Ubuntu', 'Windows', 'desktop', 'Chrome OS', 'tablet', '', None, 'null', 'NULL', 'other', 'Other', 'other', pytz.utc.localize(datetime.datetime(2018, 12, 20, 0, 0)), pytz.utc.localize(datetime.datetime(2019, 1, 19, 0, 0)), 'play', 'podcastplay', 'alexaplay', 'Android', 'BlackBerry OS', 'Firefox OS', 'Maemo', 'Symbian OS', 'Tizen', 'Windows Phone', 'iOS', 'mobile', 'Linux', 'Fedora', 'Mac OS X', 'FreeBSD', 'NetBSD', 'OpenBSD', 'Solaris', 'Ubuntu', 'Windows', 'desktop', 'Chrome OS', 'tablet', '', None, 'null', 'NULL', 'other', 'Other', 'other')
)