db.py

import sqlite3
from myHabits import Habit
import datetime
import time
from helpers import get_input
from analyse import progress_bar
from rich.table import Table
from rich.console import Console
import sys
current_time = datetime.datetime.now()


def get_db(name="main.db"):
    db = sqlite3.connect(name)
    return db


def check_db_exists(db):
    """
        Checks, if a database and a table "Habits" exists, so no technical error occurs, which might confuse the user
        :param db: initialized sqlite3 database connection
        """
    cur = db.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", ('habits',))
    if not cur.fetchone():
        print('')
        # End program/end process
        sys.exit()
def create_table(db):
    """
    Creates several tables, which will be needed to store information regarding the given Habit

    :param db: initialized sqlite3 database connection
    """
    cur = db.cursor()
    cur.execute('''CREATE TABLE IF NOT EXISTS habits (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            frequency TEXT,
            duration INTEGER,
            checked_status INTEGER NOT NULL,
            streak INTEGER,
            reason_failure_id INTEGER,
            avoidance_strategy_id INTEGER,
            check_date INTEGER,
            checked_timestamp DATETIME,
            FOREIGN KEY (reason_failure_id) REFERENCES reason_for_failure (id),
            FOREIGN KEY (avoidance_strategy_id) REFERENCES strategy_for_failure_elimination (id)
            )''')
    cur.execute('''CREATE TABLE IF NOT EXISTS reason_for_failure (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                reason TEXT,
                habit_id INTEGER,
                FOREIGN KEY (habit_id) REFERENCES habits (id)
                )''')
    cur.execute('''CREATE TABLE IF NOT EXISTS strategy_for_failure_elimination (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                strategy TEXT,
                habit_id INTEGER,
                FOREIGN KEY (habit_id) REFERENCES habits (id)
                )''')
    cur.execute('''CREATE TABLE IF NOT EXISTS meta_information (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    frequency TEXT,
                    duration INTEGER,
                    streak INTEGER,
                    habit_id INTEGER,
                    FOREIGN KEY (habit_id) REFERENCES habits (id)
                    )''')
    cur.execute('''CREATE TABLE IF NOT EXISTS habit_reasons_strategies (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    habit_id INTEGER,
                    reason_id INTEGER,
                    strategy_id INTEGER,
                    meta_information_id INTEGER,
                    FOREIGN KEY (habit_id) REFERENCES habits (id),
                    FOREIGN KEY (reason_id) REFERENCES reason_for_failure (id),
                    FOREIGN KEY (strategy_id) REFERENCES strategy_for_failure_elimination (id),
                    FOREIGN KEY (meta_information_id) REFERENCES meta_information (id)
                    )''')
    db.commit()


def insert_habit(db, name, frequency, duration, checked_status, streak, check_date, checked_timestamp):
    """
        Insert Habit with attributes into the db.

        :param db: initialized sqlite3 database connection
        :param name: Name of the given Habit
        :param frequency: Frequency of the given Habit (daily, weekly, monthly)
        :param duration: Duration of the given Habit (in minutes)
        :param checked_status: Status for Habit, "1" means it's still unchecked, "2" means it's done
        :param streak: The streak that got reached during it's tracking
        :param check_date: Date (daily = integer of weekday, weekly = integer of iso-calender week, monthly = iso-calender integer week + integer year
        :param checked_timestamp: Timestamp a Habit got checked off

        """
    cur = db.cursor()
    cur.execute(''' SELECT name FROM habits WHERE name =? ''', (name,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO habits(name, frequency, duration, checked_status, streak, check_date, checked_timestamp) VALUES (?, ?, ?, ?, ?, ?, ?) ''',
                (name, frequency, duration, checked_status, streak, check_date, checked_timestamp))
        show_habits(db)
    else:
        print("You already have a Habit with exactly that name")
        return

    db.commit()



def modify_habit(db):
    """
    Modify a given Habit after its creation
    :param db: initialized sqlite3 database connection
    :param new_name: New name for the given Habit
    :param new_duration: New duration for the given Habit
    """
    cur = db.cursor()
    choose_changement = str.lower(get_input('Do you want to change the name or to change the duration of an existing Habit?'
                                        ' Type in "name" or "duration"'))
    if choose_changement == "name":
        choose_habit = get_input('Which Habit do you want to rename?')
        cur.execute('''SELECT name FROM habits WHERE name=?''', (choose_habit,))
        habit = cur.fetchone()
        if habit is None:
            print('The Habit does not exist.')
        else:
            new_name = get_input('How do you want to call it instead?')
            cur.execute('''UPDATE habits SET name=? WHERE name=?''', (new_name, choose_habit,))
    elif choose_changement == "duration":
        choose_habit = get_input('For which Habit do you want to change the duration?')
        cur.execute('''SELECT duration FROM habits WHERE name=?''', (choose_habit,))
        habit = cur.fetchone()
        if habit is None:
            print('The Habit does not exist.')
        else:
            new_duration = get_input('Whats the new duration you want to set for the Habit? ')
            cur.execute('''UPDATE habits SET duration=? WHERE name=?''', (new_duration, choose_habit,))
    db.commit()


def del_habit(db):
    """
    Delete a given Habit
    :param db: initialized sqlite3 database connection
    """
    cur = db.cursor()
    choose_habit = get_input('Which Habit do you want to delete?')
    cur.execute('''SELECT name FROM habits WHERE name=?''', (choose_habit,))
    habit = cur.fetchone()
    if habit is None:
        print('The Habit does not exist.')
    else:
        cur.execute('''DELETE FROM habits WHERE name=?''', (choose_habit,))
        print(f'You deleted {habit} successfully')
    db.commit()


def show_habits(db):
    """
    Shows all Habits in a table that got created with the modul "rich"

    :param db: initialized sqlite3 database connection
    :return: In case someone wants to show habits but haven't created one before
    """
    cur = db.cursor()
    cur.execute('''SELECT id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp FROM habits''')
    rows = cur.fetchall()
    if len(rows) == 0:
        print("No Habits found")
        return
    else:
        # creates a progress bar for cosmetic reason.
        with progress_bar as p:
            for i in p.track(range(len(rows))):
                time.sleep(0.08)
                pass
        table = Table(title=f"[bold][red]A list of all your Habits", style="blue")
        table.add_column("id")
        table.add_column("Name", justify="center", style="bold cyan")
        table.add_column("Frequency", justify="right", style="red")
        table.add_column("Duration\n(min)", justify="right")
        table.add_column("Current streak", justify="right", style="bold green")
        table.add_column("Status", justify="right")
        table.add_column("Last Checkoff", justify="right")
        for row in rows:
            id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp = row
            if checked_timestamp == "":
                pass
            else:
                checked_timestamp = datetime.datetime.strptime(row[7], "%Y-%m-%d %H:%M:%S.%f")
                checked_timestamp = checked_timestamp.strftime("%Y-%m-%d %H:%M")
            if checked_status == 1:
                checked_status = "not done yet"
            elif checked_status == 2:
                checked_status = "Done"
            table.add_row(str(id), name, frequency, str(duration), str(streak), checked_status, checked_timestamp)
        console = Console()
        console.print(table)

    db.commit()


def check_off_habit(db):

    """
    Function to check off a Habit after the user accomplished it. User needs to input the given periodicity
    for the habit to check it off.

    :param db: initialized sqlite3 database connection
    :return: In case user wants to check a habit but haven't instantiated one before
    """
    cur = db.cursor()
    choose_frequency = str.lower(get_input('Is it a daily, weekly or monthly Habit you want to check off?'))
    if choose_frequency == "d" or choose_frequency == "daily":
        choose_frequency = "d"
    elif choose_frequency == "w" or choose_frequency == "weekly":
        choose_frequency = "w"
    elif choose_frequency == "m" or choose_frequency == "monthly":
        choose_frequency = "m"
    else:
        print("Invalid frequency")
        return
    cur.execute('''SELECT name, frequency, duration, checked_status, streak, check_date, checked_timestamp FROM 
    habits WHERE frequency = ? AND checked_status = 1''', choose_frequency)
    rows = cur.fetchall()
    if len(rows) == 0:
        print("No Habits found")
        return
    else:
        for row in rows:
            name, frequency, duration, checked_status, streak, check_date, checked_timestamp = row
            print(f"Name: {name}, Frequency: {frequency}, Duration: {duration} minutes,"
                  f" Streak: {streak}x in a row.")
    choose_habit = get_input('Which Habit you want to check off?')
    habit_found = False
    for row in rows:
        name, frequency, duration, checked_status, streak, check_date, checked_timestamp= row
        if name == choose_habit:
            habit_found = True
            if checked_status == 1:
                habit = Habit()
                habit_checked_timestamp = habit.get_habit_checked_timestamp()
                if frequency == "d":
                    current_day = current_time.isoweekday()
                    if check_date == current_day:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = streak + 1, checked_timestamp = ? WHERE name = ?''',
                                    (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off.")
                    else:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = 0, checked_timestamp = ? WHERE name = ?''',
                                      (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off. Unfortunately you didn't keep up with your Schedule, thus your Streak will start at 0 again.")
                elif choose_frequency == "w":
                    current_week = current_time.isocalendar().week
                    if check_date == current_week:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = streak + 1, checked_timestamp = ? WHERE name = ?''',
                                    (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off.")
                    else:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = 0, checked_timestamp = ? WHERE name = ?''',
                                    (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off. Unfortunately you didn't keep up with your Schedule, thus your Streak will start at 0 again.")
                elif choose_frequency == "m":
                    current_month = (current_time.year + current_time.month)
                    if check_date == current_month:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = streak + 1, checked_timestamp = ? WHERE name = ?''',
                                    (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off.")
                    else:
                        cur.execute('''UPDATE habits SET checked_status = 2, streak = 0, checked_timestamp = ? WHERE name = ?''',
                                    (habit_checked_timestamp, choose_habit,))
                        print(f"Habit '{choose_habit}' has been checked off. Unfortunately you didn't keep up with your Schedule, thus your Streak will start at 0 again.")
            elif checked_status == 2:
                print(f"You already Checked Off '{name}'")

    if not habit_found:
        print("Habit not found")

    db.commit()


def check_habit_in_schedule(db):
    """
    A Function that get called after starting the app. Its purpose is to check, if the user didn't accomplish
    one of its Habits and asks why he didn't reach his goal to check if off and what given strategy might lead
    to more success or less failure
    :param db: initialized sqlite3 database connection
    """

    # Gets connection to database and select all habits with a frequency of "d" for "daily".
    try:
        cur = db.cursor()
        cur.execute('''SELECT id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, 
        reason_failure_id, avoidance_strategy_id FROM habits WHERE frequency='d' ''')
        rows = cur.fetchall()
        if len(rows) == 0:
            return
        for row in rows:
            id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, reason_failure_id, avoidance_strategy_id = row
            # When check_status = 1 (thus it's not checked off), it compares the actual date with the check_date
            if checked_status == 1:
                current_day = current_time.isoweekday()
                if check_date != current_day:
                    # If that's not the case, it sets a new check_date for the habit based on the current time, because
                    # periodicity-rule is not met
                    check_date = current_day
                    # prints a message in case habit didn't get checked off according its scheduled periodicity.
                    print(f"Oh, it seems like you weren't able to accomplish your habit as scheduled, "
                          f"because '{name}' is still unchecked")
                    # set up a variable called "habit" which is an instance of Habit to call its methods
                    habit = Habit()
                    # calls two methods of the class Habit and saves the input of the user, which will be generated.
                    # The input will be saved in a variable called "reason", which describes the reason for failure.
                    # The second input will be saved in a variable called "strategy" which describes how to avoid
                    # further failure regarding the given habit.
                    reason = habit.get_habit_reason_for_failure()
                    strategy = habit.get_habit_failure_avoidance_strategy()
                    # inserts or updates the db with the new values.
                    cur.execute('''INSERT INTO meta_information(frequency, duration, streak, habit_id) VALUES
                                                (?, ?, ?, ?) ''', (frequency, duration, streak, id,))
                    meta_information_id = cur.lastrowid
                    cur.execute('''INSERT INTO reason_for_failure(reason, habit_id) VALUES
                                                (?, ?) ''',
                                (reason, id))
                    reason_failure_id = cur.lastrowid
                    cur.execute('''INSERT INTO strategy_for_failure_elimination(strategy, habit_id) VALUES
                                (?, ?) ''', (strategy, id))
                    avoidance_strategy_id = cur.lastrowid
                    cur.execute('''INSERT INTO habit_reasons_strategies(habit_id, reason_id, strategy_id, 
                    meta_information_id) VALUES (?, ?, ?, ?)''', (id, reason_failure_id, avoidance_strategy_id, meta_information_id))
                    # setback of the streak and update of check_date.
                    cur.execute(
                        '''UPDATE habits SET streak = 0, check_date =?, reason_failure_id=?, avoidance_strategy_id=? 
                        WHERE frequency ='d' AND name=? ''', (check_date, reason_failure_id, avoidance_strategy_id, name))
            elif checked_status == 2:
                # received checked_status = 2 and sets a new check_date.
                # streak won't be increased, since increment is handled in check-off-function or when habit-instance
                # gets created with checked_status = 2
                current_day = current_time.isoweekday()
                if check_date != current_day:
                    check_date = current_day
                    cur.execute('''UPDATE habits SET checked_status = 1, check_date =? WHERE frequency ='d' ''', (check_date,))
        cur.execute('''SELECT id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, 
        reason_failure_id, avoidance_strategy_id FROM habits WHERE frequency='w' ''')
        rows = cur.fetchall()
        for row in rows:
            id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, reason_failure_id, avoidance_strategy_id = row
            if checked_status == 1:
                current_week = current_time.isocalendar().week
                if check_date != current_week:
                    check_date = current_week
                    print(f"Oh, it seems like you weren't able to accomplish your habit as scheduled, "
                          f"because '{name}' is still unchecked")
                    habit = Habit()
                    reason = habit.get_habit_reason_for_failure()
                    strategy = habit.get_habit_failure_avoidance_strategy()
                    cur.execute('''INSERT INTO meta_information(habit_id, frequency, duration, streak) VALUES
                                                                    (?, ?, ?, ?) ''', (id, frequency, duration, streak))
                    meta_information_id = cur.lastrowid
                    cur.execute('''INSERT INTO reason_for_failure(reason, habit_id) VALUES
                                                                    (?, ?) ''',
                                (reason, id))
                    reason_failure_id = cur.lastrowid
                    cur.execute('''INSERT INTO strategy_for_failure_elimination(strategy, habit_id) VALUES
                                                    (?, ?) ''', (strategy, id))
                    avoidance_strategy_id = cur.lastrowid
                    cur.execute('''INSERT INTO habit_reasons_strategies(habit_id, reason_id, strategy_id, 
                                        meta_information_id) VALUES (?, ?, ?, ?)''',
                                (id, reason_failure_id, avoidance_strategy_id, meta_information_id))
                    # setback of the streak and update of check_date.
                    cur.execute(
                        '''UPDATE habits SET streak = 0, check_date =?, reason_failure_id=?, avoidance_strategy_id=? 
                        WHERE frequency ='w' AND name=? ''',
                        (check_date, reason_failure_id, avoidance_strategy_id, name))
            elif checked_status == 2:
                current_week = current_time.isocalendar().week
                if check_date != current_week:
                    check_date = current_week
                    cur.execute('''UPDATE habits SET checked_status = 1, check_date =? WHERE frequency ='w' ''', check_date)

        cur.execute('''SELECT id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, 
        reason_failure_id, avoidance_strategy_id FROM habits WHERE frequency='m' ''')
        rows = cur.fetchall()
        for row in rows:
            id, name, frequency, duration, checked_status, streak, check_date, checked_timestamp, reason_failure_id, avoidance_strategy_id = row
            if checked_status == 1:
                current_month = (current_time.month + current_time.year)
                if check_date != current_month:
                    check_date = current_month
                    print(f"Oh, it seems like you weren't able to accomplish your habit as scheduled, "
                          f"because '{name}' is still unchecked")
                    habit = Habit()
                    reason = habit.get_habit_reason_for_failure()
                    strategy = habit.get_habit_failure_avoidance_strategy()
                    cur.execute('''INSERT INTO meta_information(habit_id, frequency, duration, streak) VALUES
                                                                    (?, ?, ?, ?) ''', (id, frequency, duration, streak))
                    meta_information_id = cur.lastrowid
                    cur.execute('''INSERT INTO reason_for_failure(reason, habit_id) VALUES
                                                                    (?, ?) ''',
                                (reason, id))
                    reason_failure_id = cur.lastrowid
                    cur.execute('''INSERT INTO strategy_for_failure_elimination(strategy, habit_id) VALUES
                                                    (?, ?) ''', (strategy, id))
                    avoidance_strategy_id = cur.lastrowid
                    cur.execute('''INSERT INTO habit_reasons_strategies(habit_id, reason_id, strategy_id, 
                                        meta_information_id) VALUES (?, ?, ?, ?)''',
                                (id, reason_failure_id, avoidance_strategy_id, meta_information_id))
                    # setback of the streak and update of check_date.
                    cur.execute(
                        '''UPDATE habits SET streak = 0, check_date =?, reason_failure_id=?, avoidance_strategy_id=? 
                        WHERE frequency ='m' AND name=? ''',
                        (check_date, reason_failure_id, avoidance_strategy_id, name))
            elif checked_status == 2:
                current_month = (current_time.month + current_time.year)
                if check_date != current_month:
                    check_date = current_month
                    cur.execute('''UPDATE habits SET checked_status = 1, check_date =? WHERE frequency ='m' ''', check_date)

    except Exception as e:
        print("Hey there! It seems like you are here for the first time. You haven't added any Habit to your List so far. Type in '--help' or 'create-habit' to get started. Enjoy!")
    db.commit()