import os, sqlite3

if os.name != 'nt':
    fn = '/opt/domoticz/userdata/domoticz.db'
else:
    fn = 't:/domoticz.db'
conn = sqlite3.connect(fn)
cursor = conn.cursor()
print("Successfully Connected to %s" % fn)

sql = '''select DeviceRowId, Value, Counter, Date
from Meter_Calendar order by DeviceRowId,  Date'''
cursor.execute(sql)
rs = list(cursor.fetchall())
for i, (DeviceRowId, Value, Counter, Date) in enumerate(rs):

    if i > 0 and i+2 < len(rs):
        DeviceRowId1, Value1, Counter1, Date1 = rs[i+1]
        DeviceRowId2, Value2, Counter2, Date2 = rs[i+2]
        if Value == 0 or Value1 == 0 or Value2 == 0:
            # these rows dont look sane, ignore
            continue

        bad_value = False
        if (DeviceRowId, DeviceRowId) == (DeviceRowId1, DeviceRowId2):
            # if the next two values are smaller but increasing
            if Counter > Counter1 and Counter > Counter2 and Counter1 < Counter2:
                bad_value = True
        if bad_value:
            DeviceRowId0, Value0, Counter0, Date0 = rs[i-1]
            if DeviceRowId == DeviceRowId0:
                new_counter = Counter1 - Value1
                new_value = new_counter - Counter0
                if new_value > 0:
                    print('fix device %s at %s: from %s to %s (%s)' % (DeviceRowId, Date, Counter, new_counter, new_value))
                    sql = '''update Meter_Calendar set value = %s, counter = %s
                    where DeviceRowId = %s and Date = '%s' '''
                    cursor.execute(sql % (new_value, new_counter, DeviceRowId, Date))

conn.commit()
cursor.close()