import sqlite3


class db:
    # https://docs.python.org/3/library/sqlite3.html
    def __init__(self, dbfile):
        # final variable
        self.dbfile = dbfile
        try:
            open(dbfile)
        except FileNotFoundError:
            self.setup()

    def exec(self, cmd, param=None):
        """
        A function to execute a database command without having to
        create the cursor
        """
        con = sqlite3.connect(self.dbfile)
        cur = con.cursor()
        if param == None:
             cur.execute(cmd)
        else:
             cur.execute(cmd, param)
        con.commit()
        con.close()

    def setup(self):
        """
        Necessary when the database does not exist and the tables must be
        created for the first time, otherwise not needed to be run.
        """
        con = sqlite3.connect(self.dbfile)
        cur = con.cursor()
        cur.execute(
            """
        CREATE TABLE temperature (
            date        text not null, 
            time        integer not null, 
            temperature            real,
            target           real,
            PRIMARY KEY (date, time)
        );
                """
        )
        cur.execute(
            """
        CREATE TABLE weather (
            date        text not null,
            temperature real,
            wind        real,
            PRIMARY KEY (date),
            FOREIGN KEY (date) REFERENCES temperature(date)
        );
                """
        )
        cur.execute(
            """
        CREATE TABLE schedule (
            day        text not null, 
            time        integer not null, 
            temperature real,
            PRIMARY KEY (day, time)
        );
                """
        )
        cur.execute(
            """
        CREATE TABLE history (
            date        text not null,
            time        integer not null, 
            heating_on  integer,
            PRIMARY KEY (date, time),
            FOREIGN KEY (date) REFERENCES temperature(date),
            FOREIGN KEY (time) REFERENCES temperature(time)
        );
                """
        )
        con.commit()
        con.close()
        pass

    def export_csv(self):
        return 0