#!/usr/bin/python

import psycopg2
import psycopg2.extras
from datetime import datetime, date, timedelta

hostname = 'localhost'
username = 'julie'
password = 'julie'
database = 'vagrant'
myConnection = psycopg2.connect(host=hostname, user=username, password=password, dbname=database)
myConnection.autocommit = True

def create_table(conn):
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute(
    """
    CREATE TABLE transactions_transformed (
    email text,
    today boolean,
    yesterday boolean,
    this_week boolean,
    medium text,
    campaign text,
    source text,
    content text,
    term text,
    mailing_id bigint,
    amount_dollars double precision,
    first_contrib boolean,
    source_group text
    );
    """)

def retrieve(conn):
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("""
	SELECT k.transaction_email, k.transaction_timestamp, k.utm -> 'medium' as medium,
	k.utm -> 'campaign' as campaign,
	k.utm -> 'source' as source,
	k.utm -> 'content' as content,
	k.utm -> 'term' as term,k.is_group1,k.is_group2,
	k.amount,
	t.earliest
	FROM public.transactions k
	join (select transaction_email, min(transaction_timestamp) as earliest
	from public.transactions
	group by transaction_email) t on t.transaction_email = k.transaction_email;
	""")
    return cur.fetchall()

create_table(myConnection)
results = retrieve(myConnection)

for row in results:
    email = row['transaction_email']
    email = email.lower()
    transaction_time = row['transaction_timestamp']
    if transaction_time.date() == datetime.today().date():
        today = True
    else:
        today = False
    if transaction_time.date() == date.today() - timedelta(1):
        yesterday = True
    else:
        yesterday = False
    if transaction_time.date() > date.today() - timedelta(7):
        past_week = True
    else:
        past_week = False
    medium = row['medium']
    source = row['source']
    campaign = row['campaign']
    content = row['content']
    if medium == 'email':
        mailing_id = content.split('-')[0]
    else:
        mailing_id = None
    term = row['term']
    group1 = row['is_group1']
    group2 = row['is_group2']
    if group1:
        group = 'group1'
    elif group2:
        group = 'group2'
    else:
        group = None
    amount = row['amount']
    earliest_transaction = row['earliest']
    if transaction_time == earliest_transaction:
        first_transaction = True
    else:
        first_transaction = False
    print email
    print today
    print yesterday
    cur = myConnection.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("""
    insert into public.transactions_transformed
    (email,today,yesterday,this_week,medium,campaign,source,content,term,
    mailing_id,amount_dollars,first_contrib,source_group)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
    """,(email,
    today,
    yesterday,
    past_week,
    medium,
    campaign,
    source,
    content,
    term,
    mailing_id,
    amount,
    first_transaction,
    group))


myConnection.close()