#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
import getopt

from datetime import datetime
from sqlalchemy import create_engine

import pandas as pd


if __name__ == "__main__":
	unixOptions = "s:e"
	gnuOptions = ["start_dt=", "end_dt="]

	fullCmdArguments = sys.argv
	argumentList = fullCmdArguments[1:]

	try:
		arguments, values = getopt.getopt(argumentList, unixOptions, gnuOptions)
	except getopt.error as err:
		print (str(err))
		sys.exit(2)

	start_dt = ''
	end_dt = ''
	for currentArgument, currentValue in arguments:
		if currentArgument in ("-s", "--start_dt"):
			start_dt = currentValue
		elif currentArgument in ("-e", "--end_dt"):
			end_dt = currentValue

	db_config = {'user': 'my_user','pwd': 'my_user_password','host': 'localhost','port': 5432,'db': 'zen'}

	connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
															db_config['pwd'],
															db_config['host'],
															db_config['port'],
															db_config['db'])

	engine = create_engine(connection_string)

	query = '''SELECT event_id, age_segment, event, item_id, item_topic,item_type, source_id, source_topic, source_type,TO_TIMESTAMP(ts / 1000) AT TIME ZONE 'Etc/UTC' as dt, user_id
				FROM log_raw
				WHERE TO_TIMESTAMP(ts / 1000) AT TIME ZONE 'Etc/UTC' BETWEEN '{}'::TIMESTAMP AND '{}'::TIMESTAMP
			'''.format(start_dt, end_dt)

	log_raw = pd.io.sql.read_sql(query, con = engine, index_col = 'event_id')

	print(log_raw.head(5))

	log_raw['dt'] = pd.to_datetime(log_raw['dt']).dt.round('min')

	dash_visits = log_raw.groupby(['item_topic','source_topic','age_segment','dt']).agg({'user_id':'count'})

	dash_visits = dash_visits.rename(columns={'user_id':'visits'})

	print(dash_visits.head(5))

	dash_engagement = log_raw.groupby(['dt','item_topic','event','age_segment']).agg({'user_id':'nunique'})

	dash_engagement = dash_engagement.rename(columns={'user_id':'unique_users'})

	print(dash_engagement.head(5))

	tables = {'dash_visits': dash_visits,
			  'dash_engagement': dash_engagement}

	for table_name, table_data in tables.items():
		query = '''
				DELETE FROM {} WHERE dt BETWEEN '{}'::TIMESTAMP AND '{}'::TIMESTAMP
                '''.format(table_name, start_dt, end_dt)
		engine.execute(query)
		table_data.to_sql(name = table_name, con = engine, if_exists = 'append', index = False)