import sys, time
import glob
import logging
import logging.handlers
import sqlite3
from datetime import datetime
from sbLog import *

daySecs = 24*3600

def midNight(ofDay):
    tup = time.localtime(ofDay and ofDay or time.time())
    return time.mktime((tup.tm_year, tup.tm_mon, tup.tm_mday, 0, 0, 0, tup.tm_wday,tup.tm_yday, tup.tm_isdst))

def strTime(etime):
    return time.asctime(time.localtime(etime))

class InverterDb(object):
    def __init__(self, db):
        global logger
        self.invList = dict()
        self.conn = sqlite3.connect(db)
        self.lastMode = None
    def saveData(self, data):
        global logger
        if data[5] != 'Mpp' and self.lastMode == data[5]:
            print"skipping save for mode", data[5]
            return
        self.lastMode = data[5]
        tries = 3
        while tries > 0:
            tries = tries - 1
            try:
                self.conn.execute('insert into pvData values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', data)
                self.conn.commit()
                return
            except:
                logger.info( 'db insert failed: ' + sys.exc_info()[1].message)
                time.sleep(1)
    def buildStats(self, field, fromT, toT):
        #print(time.asctime(time.localtime(toT)))
        sql = 'select MAX(%s) - MIN(%s) from pvData where time > %d and  time < %d' %\
            (field, field, fromT, toT)
        #print('bldStats', strTime(fromT), strTime(toT),sql)
        cur = self.conn.cursor()
        cur.execute(sql)
        return cur.fetchone()
    def firstDate(self):
        cur = self.conn.cursor()
        cur.execute('select time from pvData limit 1')
        return cur.fetchone()[0]

class InverterSummaryDb(object):
    def __init__(self, dbSumary, dataDb):
        self.conn = sqlite3.connect(dbSumary)
        #self.create()
        self.dataDb = dataDb
        self.baseFields = { 'E_Total': 'E_Day', 'h_Total':'H_Day', 'Pac':'PmaxDay'}
        self.nextSumDueAt = self.lastSummaryAt() or self.dataDb.firstDate()
    def buildSummaryForDay(self, date):
        global logger
        toT = date+daySecs
        for liveFld, sumFld in self.baseFields.iteritems():
            summary = self.dataDb.buildStats(liveFld, date, toT)
            #print liveFld, summary[0]
            #continue
            try:
                sql = "insert into pvSummary values (?, ?, ?)"
                self.conn.execute(sql, (date, sumFld, summary[0]))
            except sqlite3.IntegrityError:
                sql = "update pvSummary set value = (?) where (time = ? and field = ?)"
                self.conn.execute(sql, (summary[0], date, sumFld))
            except:
                print( 'db Summary insert failed: ' + sys.exc_info()[1].message)
                logger.info( 'db Summary insert failed: ' + sys.exc_info()[1].message)
        self.conn.commit()
        return toT
    def buildSummaryAll(self):
        while self.nextSumDueAt < time.time() - daySecs:
            self.nextSumDueAt = midNight(self.buildSummaryForDay(self.nextSumDueAt))
            #print('Next Summary due', strTime(self.nextSumDueAt))
        return self.nextSumDueAt
    def lastSummaryAt(self):
        c = self.conn.cursor()
        c.execute('select time from pvSummary order by time desc limit 1')
        rslt = c.fetchone()
        return rslt and rslt[0] or midNight(None)
    def summaryAt(self, day):
        c = self.conn.cursor()
        fromT = midNight(day)
        c.execute('select * from pvSummary where (time >= ? and time <= ?)', (fromT, fromT + daySecs))
        rslt = {'day': strTime(fromT)}
        data = c.fetchall()
        for row in data:
            rslt[row[1]] = row[2]
        return rslt
    def create(self):
        self.conn.execute(''' drop table pvSummary ''')
        self.conn.execute(''' create table if not exists pvSummary (time numeric KEY, field text, value real) ''')
        self.conn.execute(''' create unique index if not exists idx_time_fld on pvSummary (time, field) ''')

testSummary = None
if testSummary:
    setupLogger('sblog.txt')
    db = InverterDb('k:/sb/sb.db')
    dbSummary = 'k:/sb/sbSummary.db'
    sumDb = InverterSummaryDb(dbSummary, db)
    midnight = midNight(time.time())
    last = sumDb.lastSummaryAt()
    print(time.asctime(time.localtime(last)))
    #
    sumDb.buildSummaryAll()
    print sumDb.summaryAt(midnight-daySecs)