# Copyright 2021-2023 H2020 TeraFlow (https://www.teraflow-h2020.eu/)
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import sqlite3 as sl

class ManagementDB():
    def __init__(self, database):
        self.client = sl.connect(database, check_same_thread=False)
        self.create_monitoring_table()
        self.create_subscription_table()
        self.create_alarm_table()
        
    def create_monitoring_table(self):
        self.client.execute("""
            CREATE TABLE IF NOT EXISTS kpi(
                kpi_id INTEGER PRIMARY KEY AUTOINCREMENT,
                kpi_description TEXT,
                kpi_sample_type INTEGER,
                device_id INTEGER,
                endpoint_id INTEGER,
                service_id INTEGER
            );
        """)
    
    def create_subscription_table(self):
        self.client.execute("""
            CREATE TABLE IF NOT EXISTS subscription(
                subs_id INTEGER PRIMARY KEY AUTOINCREMENT,
                kpi_id INTEGER,
                subscriber TEXT,
                sampling_duration_s REAL,
                sampling_interval_s REAL,
                start_timestamp REAL,
                end_timestamp REAL
            );
        """)

    def create_alarm_table(self):
        self.client.execute("""
            CREATE TABLE IF NOT EXISTS alarm(
                alarm_id INTEGER PRIMARY KEY AUTOINCREMENT,
                alarm_description TEXT,
                alarm_name TEXT,
                kpi_id INTEGER,
                kpi_min_value REAL,
                kpi_max_value REAL,
                in_range INTEGER,
                include_min_value INTEGER,
                include_max_value INTEGER
            );
        """)

    def insert_KPI(self,kpi_description,kpi_sample_type,device_id,endpoint_id,service_id):
        c = self.client.cursor()
        c.execute("SELECT kpi_id FROM kpi WHERE device_id is ? AND kpi_sample_type is ? AND endpoint_id is ? AND service_id is ?",(device_id,kpi_sample_type,endpoint_id,service_id))
        data=c.fetchone()
        if data is None:
            c.execute("INSERT INTO kpi (kpi_description,kpi_sample_type,device_id,endpoint_id,service_id) VALUES (?,?,?,?,?)", (kpi_description,kpi_sample_type,device_id,endpoint_id,service_id))
            self.client.commit()
            return c.lastrowid
        else:
            return data[0]
            
    def insert_subscription(self,kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp):
        c = self.client.cursor()
        c.execute("SELECT subs_id FROM subscription WHERE kpi_id is ? AND subscriber is ? AND sampling_duration_s is ? AND sampling_interval_s is ? AND start_timestamp is ? AND end_timestamp is ?",(kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp))
        data=c.fetchone()
        if data is None:
            c.execute("INSERT INTO subscription (kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp) VALUES (?,?,?,?,?,?)", (kpi_id,subscriber,sampling_duration_s,sampling_interval_s,start_timestamp, end_timestamp))
            self.client.commit()
            return c.lastrowid
        else:
            print("already exists")
            return data[0]

    def insert_alarm(self,alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value):
        c = self.client.cursor()
        c.execute("SELECT alarm_id FROM alarm WHERE alarm_description is ? AND alarm_name is ? AND kpi_id is ? AND kpi_min_value is ? AND kpi_max_value is ? AND in_range is ? AND include_min_value is ? AND include_max_value is ?",(alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value))
        data=c.fetchone()
        if data is None:
            c.execute("INSERT INTO alarm (alarm_description, alarm_name, kpi_id, kpi_min_value, kpi_max_value, in_range, include_min_value, include_max_value) VALUES (?,?,?,?,?,?,?,?)", (alarm_description,alarm_name,kpi_id,kpi_min_value,kpi_max_value,in_range,include_min_value,include_max_value))
            self.client.commit()
            return c.lastrowid
        else:
            print("already exists")
            return data[0]

    def delete_KPI(self,kpi_id):
        c = self.client.cursor()
        c.execute("SELECT * FROM kpi WHERE kpi_id is ?",(kpi_id,))       
        data=c.fetchone()
        if data is None:
            return False
        else:
            c.execute("DELETE FROM kpi WHERE kpi_id is ?",(kpi_id,))
            self.client.commit()
            return True

    def delete_subscription(self,subs_id):
        c = self.client.cursor()
        c.execute("SELECT * FROM subscription WHERE subs_id is ?",(subs_id,))       
        data=c.fetchone()
        if data is None:
            return False
        else:
            c.execute("DELETE FROM subscription WHERE subs_id is ?",(subs_id,))
            self.client.commit()
            return True

    def delete_alarm(self,alarm_id):
        c = self.client.cursor()
        c.execute("SELECT * FROM alarm WHERE alarm_id is ?",(alarm_id,))       
        data=c.fetchone()
        if data is None:
            return False
        else:
            c.execute("DELETE FROM alarm WHERE alarm_id is ?",(alarm_id,))
            self.client.commit()
            return True

    def get_KPI(self,kpi_id):
        data = self.client.execute("SELECT * FROM kpi WHERE kpi_id is ?",(kpi_id,))
        return data.fetchone()

    def get_subscription(self,subs_id):
        data = self.client.execute("SELECT * FROM subscription WHERE subs_id is ?",(subs_id,))
        return data.fetchone()

    def get_alarm(self,alarm_id):
        data = self.client.execute("SELECT * FROM alarm WHERE alarm_id is ?",(alarm_id,))
        return data.fetchone()
        
    def get_KPIS(self):
        data = self.client.execute("SELECT * FROM kpi")
        return data.fetchall()

    def get_subscriptions(self):
        data = self.client.execute("SELECT * FROM subscription")
        return data.fetchall()

    def get_alarms(self):
        data = self.client.execute("SELECT * FROM alarm")
        return data.fetchall()