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

import os
import sys
import csv
import shutil
import requests
import scraperwiki

# Below as a helper for namespaces.
# Looks like a horrible hack.
dir = os.path.split(os.path.split(os.path.realpath(__file__))[0])[0]
sys.path.append(dir)

from calc_collect import calc
from ga_collect import datecalc
from ga_collect import ga_collect

from app_config import load as L
from utilities import prompt_format as I
from utilities import store_records as S
from datetime import date, timedelta, datetime

def create_tables():
    '''Creating the tables of the new database.'''

    sql_statements = {
        'funnel': 'CREATE TABLE IF NOT EXISTS funnel(metricid TEXT, period TEXT, period_start_date TEXT, period_end_date TEXT, period_type TEXT, value REAL, PRIMARY KEY(metricid,period))',
        'metrics': 'CREATE TABLE IF NOT EXISTS metrics(metricid TEXT, calculated INTEGER, name TEXT, description TEXT, operand1metricid TEXT, operand1periodoffset TEXT, operand2metricid TEXT, operand2periodoffset TEXT, operation TEXT, calcsortorder TEXT)'
        # "_log": 'CREATE TABLE IF NOT EXISTS _log(date TEXT, script TEXT, metricid TEXT, success TEXT, log_file TEXT)'
    }

    for table in sql_statements:
        try:
            query = scraperwiki.sqlite.execute(sql_statements[table])
            print "%s Table `%s` created." % (I.item('prompt_bullet'), str(table))

        except Exception as e:
            print e
            return False


    print "%s Database created successfully.\n" % I.item('prompt_success')
    return True



def sync_metrics():
    '''Sync the metrics metadata with the new database.'''

    # Download
    data_dir = os.path.split(dir)[0]
    path = os.path.join(data_dir, "temp", "metrics.csv")

    gdocs_url = "https://docs.google.com/spreadsheets/d/14kZ2Cj_IaBP1J4KZqhHTOb67HmymC5otSSV_3ji_Ssg/export?format=csv"

    r = requests.get(gdocs_url, stream = True)

    if r.status_code == 200:
        with open(path, 'wb') as f:
            r.raw.decode_content = True
            shutil.copyfileobj(r.raw, f)

    # Read file and store in database.
    try:
        with open(path) as csvfile:
            reader = csv.DictReader(csvfile)
            records = []
            for row in reader:
                    records.append(row)

    except Exception as e:
        print e
        return False

    try:
        print "%s Syncing `metrics` table." % I.item('prompt_bullet')
        S.StoreRecords(records, table = "metrics")
        print "%s successfully synced `metrics` table.\n" % I.item('prompt_success')
        return True

    except Exception as e:
        print e
        print "%s failed to sync `metrics` table." % I.item('prompt_error')
        return False



def collect_previous_ga_data(verbose = False, test_data = False):
    '''Collecting historical Google Analytics data with the new database.'''

    counter = 0
    period_date = date.today()

    # Google Analytics only has data available
    # from 2014-05-25, not earlier.
    while period_date > date(2014, 5, 25):
        period_date = date.today() - timedelta(weeks=counter)
        counter += 1

        try:
            print "%s collecting data for week %s of %s" % (I.item('prompt_bullet'), period_date.isocalendar()[1], period_date.isocalendar()[0])
            records = ga_collect.collect_ga_data(period_date)
            S.StoreRecords(data = records, table = "funnel")

            if test_data is True and counter > 1:
                return records

        except Exception as e:
            if verbose:
                print e
                return False

            print "%s Google Analytics failed to run." % I.item('prompt_error')