import sqlite3
import datetime
 
class database():
    def __init__(self, path):
        self.path = path
        self.create()
 
    def create(self):
        conn = sqlite3.connect(self.path, isolation_level='EXCLUSIVE')
        try:
            try:
                conn.execute("create table if not exists record (id integer primary key, item_id integer, assessment integer ,comment text ,date text, time text);")
                conn.commit()
            except Exception as e:
                print(e)
                conn.rollback()
            try:
                conn.execute("create table if not exists item (item_id integer primary key, item_name text, date real);")
                conn.commit()
            except Exception as e:
                print(e)
                conn.rollback()
        finally:
            conn.close() 

    def insert_record(self, item_id, assessment, comment):
        conn = sqlite3.connect(self.path, isolation_level='DEFERRED')
        dt = datetime.datetime.today()  
        try:
            conn.execute("insert into record(item_id, assessment, comment, date, time) values(?, ?, ?, ?, ?);", \
                (item_id, assessment, comment, str(dt.date()), str(dt.time().strftime('%X'))))
                # (item_id, assessment, comment, time.time()))
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()
        finally:
            conn.close()

    def get(self):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            result = [x for x in conn.execute("select id, message, date from entry;")]
        finally:
            conn.close()
 
        return result

    def get_record(self):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            sql =  "select id, item_id, assessment, comment, date, time"
            sql += " from record"
            sql += ";"
            result = [x for x in conn.execute(sql)]
        finally:
            conn.close()
 
        return result

    def get_all_record(self, page):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            sql =  "select i.name, r.assessment, r.comment, r.date, r.time"
            sql += " from record r, item i"
            sql += " where r.item_id = i.id"
            sql += " order by r.date desc, r.time desc"
            sql += " limit " + str(((int(page) - 1 ) * 10)) + ", 10" # limit 開始, 取得行数
            sql += ";"

            result = [x for x in conn.execute(sql)]
        finally:
            conn.close()
 
        return result

    def get_all_record_count(self):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            sql =  "select count(id) from record;"
            result = [x for x in conn.execute(sql)]
        finally:
            conn.close()

        return result[0][0] #リストの最初のデータのみ戻す

    def get_regcord_totalization(self):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            sql =  "select i.name"
            sql += " ,round(avg(r.assessment) , 2) as r_avg"
            sql += " ,count(r.assessment)  as r_cnt"
            sql += " from record r,  item i"
            sql += " where r.item_id = i.id"
            # sql += " and r.date <= '2022-05-30'"
            # sql += " and r.date >= '2022-05-29'"
            sql += " group by r.item_id"
            sql += " order by r_avg desc"
            sql += ";"
            result = [x for x in conn.execute(sql)]
        finally:
            conn.close()
 
        return result

    def get_all_item(self):
        result = []
        conn = sqlite3.connect(self.path)
        try:
            sql =  " select item_id, item_name"
            sql += " from item;"
            result = [x for x in conn.execute(sql)]
        finally:
            conn.close()
 
        return result

    def delete_all_record(self):
        conn = sqlite3.connect(self.path, isolation_level='DEFERRED')
        try:
            conn.execute("delete from record;")
            conn.commit()
            return "全てのデータを削除しました。"
        except Exception as e:
            print(e)
            conn.rollback()
            return "データの削除に失敗しました。" + e
        finally:
            conn.close()