#!/usr/bin/python
# -*- coding: utf-8 -*-
import sys
import getopt
from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine

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 = '2019-09-24 18:00:00'
end_dt = '2019-09-25 19:00:00'
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)

# Формируем sql-запрос.
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')
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'})
dash_visits = dash_visits.fillna(0).reset_index()
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'})
dash_engagement = dash_engagement.fillna(0).reset_index()
tables = {'dash_visits': dash_visits,'dash_engagement': dash_engagement}
for sql_table, df in tables.items():
    query = '''DELETE FROM {} WHERE dt BETWEEN '{}'::TIMESTAMP AND '{}'::TIMESTAMP'''.format(sql_table, start_dt, end_dt)
    engine.execute(query)
    df.to_sql(name = sql_table, con = engine, if_exists = 'append', index = False)
print('awesome!')