# -*- coding: utf-8 -*-

import sqlite

class WorkoutSQL:
    """SQLite database for storing workout"""
    def __init__(self, filename):
        # Is there exercise database if not create it
        try:
            open(filename, 'r+')
        except:
            self.create_database(filename)
        # Connect to the exercise database
        self.cx = sqlite.connect(filename)
        self.cu = self.cx.cursor()

    def create_database(self, filename):
        """Create empty database"""
        self.cx = sqlite.connect(filename)
        self.cu = self.cx.cursor()

        command = """
            create table workout_time (
                id integer primary key,
                start_time varchar(5),
                finish_time varchar(5),
                date_id varchar(10)
                )"""

        self.cu.execute(command)
        
        command = """
            create table workout (
                id integer primary key,
                exercise varchar(128),
                reps varchar(2),
                weight varchar(6),
                date_id varchar(10)
                )"""

        self.cu.execute(command)
        self.cx.commit()

    def load_workout(self, date_id):
        """Load workout sets by date_id"""
        # Load exercise, reps, weight by date_id
        self.cu.execute('select id, exercise, reps, weight from workout where date_id=%s', date_id)
        self.workout = []
        for row in self.cu:
            set = [row[0], unicode(row[1], 'utf-8'), row[2], row[3]]
            self.workout.append(set)
        return self.workout

    def load_workout_days(self):
        """Load workout days"""
        self.cu.execute('select date_id from workout')
        self.workout_days = []
        for row in self.cu:
            if row[0] not in self.workout_days:
                self.workout_days.append(row[0])
        self.workout_days.sort()
        return self.workout_days

    def load_workout_time(self, date_id):
        """Load workout start/finish time"""
        self.cu.execute('select start_time, finish_time from workout_time where date_id=%s', date_id)
        return self.cu.fetchall()
        
    def insert_set(self, exercise, reps, weight, date_id):
        """Insert set into database"""
        # Insert exercise, reps, weight and date_id
        self.cu.execute('insert into workout (exercise, reps, weight, date_id) values (%s, %s, %s, %s)',
                        exercise.encode('utf-8'), reps, weight, date_id)
        self.cx.commit()

    def insert_time(self, start_time, finish_time, date_id):
        """Insert start and finish time into database"""
        self.cu.execute('insert into workout_time (start_time, finish_time, date_id) values (%s, %s, %s)',
                        start_time, finish_time, date_id)
        self.cx.commit()

    def update_set(self, id, exercise, reps, weight, date_id):
        """Update selected set"""
        # Insert exercise, reps, weight and date_id
        self.cu.execute('update workout set exercise=%s, reps=%s, weight=%s where date_id=%s and id=%s',
                        exercise.encode('utf-8'), reps, weight, date_id, id)
        self.cx.commit()

    def update_time(self, start_time, finish_time, date_id):
        """Update start/finish time"""
        self.cu.execute('update workout_time set start_time=%s, finish_time=%s where date_id=%s',
                        start_time, finish_time, date_id)
        self.cx.commit()

    def delete_set(self, id, date_id):
        """Delete set from database"""
        command = "delete from workout where date_id=%s and id=%s"
        self.cu.execute(command, date_id, id)
        self.cx.commit()

    def delete_workout(self, date_id):
        """Delete selected workout"""
        command = "delete from workout_time where date_id=%s"
        self.cu.execute(command, date_id)
        command = "delete from workout where date_id=%s"
        self.cu.execute(command, date_id)
        self.cx.commit()

    def _is_workout_exist(self, date_id):
        """Check if there workout on this day"""
        self.cu.execute('select COUNT(*) from workout where date_id=%s',
                        date_id)
        # Very ugly solution, I hate this one!
        for exist in self.cu:
            if exist[0] == (0.0):
                return False
            else:
                return True