import sqlite3
import configfile
from os import listdir
from os.path import isfile, join
from os import remove
from json import loads
import threading
import time

class Sqlite3_DB():
    def __init__(self): #constructor to create database connection

        conf = configfile.Configuration()
        self.dbname=conf.db_file_name
        self.conn = sqlite3.connect(self.dbname)
        self.cursor = self.conn.cursor()
        self.tablename = conf.table_name
        self.days= conf.days_archive
        try: #create table if still not exists
            self.cursor.execute("CREATE TABLE %s(time_date datetime,sensor_data json)"%(conf.table_name))
            self.conn.commit()
        except:
           self.tablecreated=1;
        self.path=conf.path
        self.count = 0;

    #function to count the number of rows in SQL database
    def sql_count(self):
        self.cursor.execute("SELECT COUNT(sensor_data) FROM %s;"%(self.tablename))
        numberOfRows = self.cursor.fetchone()[0]
        return   numberOfRows
    
    #function to select the oldest files of the database
    def select_oldest(self,number):
        oldest_data= list()
        for row in self.cursor.execute("SELECT sensor_data FROM %s LIMIT %d;"%(self.tablename,number)): #DESC FOR NEWES, ASC FOR OLDEST        
                oldest_data.append(row);
        return(oldest_data)
        
    #function to select the newest files of the database   
    def select_newest(self,number):
        newest_data= list()
        for row in self.cursor.execute("SELECT sensor_data FROM %s ORDER BY rowid DESC LIMIT %d;"%(self.tablename,number)): #DESC FOR NEWES, ASC FOR OLDEST        
            newest_data.append(row);
        return(newest_data)
   
   
    def remove_oldests_from_db(self,number):#remove number of old lines from DB
        while(self.conn.in_transaction):#wait for previous transactions to be over
            continue;
        try:    
            self.cursor.execute("DELETE FROM %s LIMIT %d;"%(self.tablename,number)) # remove by order the oldest ones
            i=0
            self.conn.commit()
            while(self.conn.in_transaction):
                time.sleep(0.0001)

        except:
            print("Error on Deleting")
            return;
        
    def remove_newest_from_db(self,number):#remove number of new lines from DB
        while(self.conn.in_transaction):#wait for previous transactions to be over
            continue;
        try:    
            self.cursor.execute("DELETE FROM %s ORDER BY rowid DESC LIMIT %d;"%(self.tablename,number)) # remove by order the newest ones
            i=0
            self.conn.commit()
            while(self.conn.in_transaction):
                time.sleep(0.001)

        except:
            print("Error on Deleting")
            return;