//
//  Database.swift
//  Need-to-pump-up
//
//  Created by Дмитрий Собин on 24.01.2021.
//  Copyright © 2021 Дмитрий Собин. All rights reserved.
//

import Foundation
import SQLite3

class DBHelper {
    init() {
    
        db = openDatabase()
    }
    
    var db: OpaquePointer?
    
    func openDatabase() -> OpaquePointer? {
        var db: OpaquePointer?
        
        let pathDb = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.libraryDirectory,
                                                         FileManager.SearchPathDomainMask.userDomainMask, true)[0] + "/oom.sqlite"
        
        print(pathDb)
        
        if sqlite3_open(pathDb, &db) != SQLITE_OK {
            print("error opening database")
            
            return nil
        } else {
            print("Successfully opened connection to database at ")
            return db
        }
    }
    
    func createTable(queryStr: String) {
        //        let createTableString = "CREATE TABLE IF NOT EXISTS person(Id INTEGER PRIMARY KEY,name TEXT,age INTEGER);"
        let createTableString = queryStr
        var createTableStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("table created.")
            } else {
                print("table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }
    
    func insertProgram(name: String, introdaction: String, backpic: String) -> Int64 {
        let insertStatementString = "INSERT INTO Program (name, introdaction, backpic) VALUES (?, ?, ?);"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            //            sqlite3_bind_int(insertStatement, 1, Int32(idProgram))
            sqlite3_bind_text(insertStatement, 1, (name as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (introdaction as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, (backpic as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
        
        let rowid = sqlite3_last_insert_rowid(db)
        print("program rowid \(rowid)")
        return rowid
    }
    func insertLevel(name: String, idProgram: Int64) -> Int64 {
        let insertStatementString = "INSERT INTO Level (name, idProgram) VALUES (?, ?);"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            //            sqlite3_bind_int(insertStatement, 1, Int32(idLevel))
            sqlite3_bind_text(insertStatement, 1, (name as NSString).utf8String, -1, nil)
            sqlite3_bind_int64(insertStatement, 2, idProgram)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
        
        let rowid = sqlite3_last_insert_rowid(db)
        print("Level rowid \(rowid)")
        return rowid
    }
    
    func insertDay(timeRest: String, timeRestSet: String, idLevel: Int64, done: Int, numDay: Int ) -> Int64 {
        let insertStatementString = "INSERT INTO Day (timeRest, timeRestSet, idLevel, done, numDay) VALUES (?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            //            sqlite3_bind_int(insertStatement, 1, Int32  (idDay))
            sqlite3_bind_text(insertStatement, 1, (timeRest as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (timeRestSet as NSString).utf8String, -1, nil)
            sqlite3_bind_int64(insertStatement, 3, idLevel)
            sqlite3_bind_int(insertStatement, 4, Int32(done))
            sqlite3_bind_int(insertStatement, 5, Int32(numDay))
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
        
        let rowid = sqlite3_last_insert_rowid(db)
        print("Day rowid \(rowid)")
        return rowid
    }
    func insertSet(value: String, idDay: Int64) {
        let insertStatementString = "INSERT INTO Sett (value, idDay) VALUES (?, ?);"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            //            sqlite3_bind_int(insertStatement, 1, Int32(idSet))
            sqlite3_bind_text(insertStatement, 1, (value as NSString).utf8String, -1, nil)
            sqlite3_bind_int64(insertStatement, 2, idDay)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    func dropTable( _ name: String) {
        let dropStatementString = "drop table \(name);"
        var dropStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, dropStatementString, -1, &dropStatement, nil) == SQLITE_OK {
            
            if sqlite3_step(dropStatement) == SQLITE_DONE {
                print("Successfully drop table.")
            } else {
                print("Could not drop table.")
            }
        } else {
            print("DROP statement could not be prepared.")
        }
        sqlite3_finalize(dropStatement)
    }
    
    // MARK: - INIT TABLES
    // init env tables for programs
    func initEnvTable() {
        
        dropTable("Program")
        dropTable("Level")
        dropTable("Day")
        dropTable("Sett")
        
        let tProgram = "CREATE TABLE IF NOT EXISTS Program(idProgram INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,introdaction TEXT, backpic TEXT);"
        let tLevel = "CREATE TABLE IF NOT EXISTS Level(idLevel INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, idProgram INTEGER);"
        let tDay = "CREATE TABLE IF NOT EXISTS Day(idDay INTEGER PRIMARY KEY AUTOINCREMENT, timeRest TEXT, timeRestSet TEXT, idLevel INTEGER, done INTEGER,numDay INTEGER);"
        let tSet = "CREATE TABLE IF NOT EXISTS Sett(idSet INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT, idDay INTEGER);" // LOL Set in sqlite busy word
        
        createTable(queryStr: tProgram)
        createTable(queryStr: tLevel)
        createTable(queryStr: tDay)
        createTable(queryStr: tSet)
        
        insertProgramData(prog: Model.shared.allProg[0])
        insertProgramData(prog: Model.shared.allProg[1])
        insertProgramData(prog: Model.shared.allProg[2])
        insertProgramData(prog: Model.shared.allProg[3])
        
        //progress
        dropTable("Progress")
        
        let tProgress = "CREATE TABLE IF NOT EXISTS Progress(idProgress INTEGER PRIMARY KEY AUTOINCREMENT, numObj INTEGER, idProgram INTEGER, idLevel INTEGER, idDay INTEGER, dateDone TEXT,percent TEXT);"
        
        createTable(queryStr: tProgress)
        
        //z        insertProgress(idProgram: 1, idLevel: 1, idDay: 1, percent: "1%")
        //z        insertProgress(idProgram: 1, idLevel: 1, idDay: 2, percent: "2%")
        
        //        insertProgress(idProgram: 2, idLevel: 1, idDay: 1, percent: "1%")
        //        insertProgress(idProgram: 3, idLevel: 1, idDay: 1, percent: "1%")
        //        insertProgress(idProgram: 3, idLevel: 1, idDay: 2, percent: "2%")
        //        insertProgress(idProgram: 3, idLevel: 1, idDay: 3, percent: "3%")
    }
    
    // MARK: - PROGRAM
    
    // convert struct structProgram to dbProgram
    func insertProgramData(prog: Program) {
        let progid = insertProgram(name: prog.name, introdaction: prog.introduction, backpic: prog.backpic!)
        
        if prog.lvls != nil {
            for lvl in prog.lvls! {
                let lvlid = insertLevel(name: lvl.name, idProgram: progid)
                
                for day in lvl.days {
                    
                    let dayid = insertDay(timeRest: day.timeRest, timeRestSet: day.timeRestSet, idLevel: lvlid, done: day.done, numDay: day.numDay)
                    for set in day.sets! {
                        insertSet(value: set, idDay: dayid)
                    }
                }
            }
        }
    }
    
    // read dbProgram to structProgram
    func selectProgram(idProgram: Int64) -> Program? {
        let queryStatementString = "select P.idProgram, P.name, P.introdaction, P.backpic, L.idLevel, L.name, D.idDay, D.done, D.timeRest, D.timeRestSet, D.numDay, S.value from Program P join Level L on P.idProgram = L.idProgram join Day D on L.idLevel = D.idLevel join Sett S on D.idDay = S.idDay where P.idProgram = \(idProgram) order by P.idProgram, L.idLevel, D.idDay, S.idSet;"
        
        var queryStatement: OpaquePointer?
        //        var prog : Program?
        
        var prog: Program = Program(id: 0, name: "", introduction: "", backpic: "", lvls: [], numProg: 0)
        var day: Days = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
        var lvl: LvlsProgram = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            var currentLvl: Int64 = 0
            var currentDay: Int64 = 0
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let idProg = sqlite3_column_int64(queryStatement, 0)
                let nameProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let introdactionProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let backpicProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let idLvl = sqlite3_column_int64(queryStatement, 4)
                let nameLvl = String(describing: String(cString: sqlite3_column_text(queryStatement, 5)))
                let idDay = sqlite3_column_int64(queryStatement, 6)
                let doneDay = sqlite3_column_int(queryStatement, 7)
                let timeRestDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 8)))
                let timeRestSetDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 9)))
                let numDay = sqlite3_column_int(queryStatement, 10)
                let valueSet = String(describing: String(cString: sqlite3_column_text(queryStatement, 11)))
                
                //определяем начало программы, создаем программу, уровень, день.
                if prog.name == "" {
                    prog.id = idProg
                    prog.name = nameProg
                    prog.introduction = introdactionProg
                    prog.backpic = backpicProg
                    
                    currentLvl = idLvl
                    currentDay = idDay
                }
                //проверяем уровень, он текущий тогда продолжаем заполнять дни
                //или он новый, тогда вставляем уровень в программу и заполняем новый
                if idLvl == currentLvl {
                    lvl.name = nameLvl
                    lvl.id = idLvl
                    //проверяем день, он текущий тогда вставляем день в программу
                    //или он новый, тогда вставляем день в программу и заполняем новый
                    if idDay == currentDay {
                        day.id = idDay
                        day.sets?.append(valueSet)
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                    } else {
                        
                        lvl.days.append(day)
                        day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                        day.id = idDay
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                        day.sets?.append(valueSet)
                        
                        currentDay = idDay
                    }
                    continue
                    
                } else {
                    
                    lvl.days.append(day)
                    
                    prog.lvls?.append(lvl)
                    
                    lvl = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
                    lvl.id = idLvl
                    lvl.name = nameLvl
                    
                    day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                    day.id = idDay
                    day.timeRest = timeRestDay
                    day.timeRestSet = timeRestSetDay
                    day.done = Int(doneDay)
                    day.numDay = Int(numDay)
                    day.sets?.append(valueSet)
                    
                    currentDay = idDay
                    currentLvl = idLvl
                    
                    continue
                }
            }
            // the last one
            lvl.days.append(day)
            prog.lvls?.append(lvl)
            
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return prog
    }
    
    func selectNextProgram(idProgram: Int64, idLevel: Int64, idDay: Int64) -> Program {
        let queryStatementString = "select P.idProgram, P.name, P.introdaction, P.backpic, L.idLevel, L.name, D.idDay, D.done, D.timeRest, D.timeRestSet, D.numDay, S.value from Program P join Level L on P.idProgram = L.idProgram join Day D on L.idLevel = D.idLevel join Sett S on D.idDay = S.idDay where P.idProgram = \(idProgram) and L.idLevel = \(idLevel) and D.idDay =\(idDay + 1)  order by P.idProgram, L.idLevel, D.idDay, S.idSet;"
        
        var queryStatement: OpaquePointer?
        //        var prog : Program?
        
        var prog: Program = Program(id: 0, name: "", introduction: "", backpic: "", lvls: [], numProg: 0)
        var day: Days = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
        var lvl: LvlsProgram = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            var currentLvl: Int64 = 0
            var currentDay: Int64 = 0
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let idProg = sqlite3_column_int64(queryStatement, 0)
                let nameProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let introdactionProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let backpicProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let idLvl = sqlite3_column_int64(queryStatement, 4)
                let nameLvl = String(describing: String(cString: sqlite3_column_text(queryStatement, 5)))
                let idDay = sqlite3_column_int64(queryStatement, 6)
                let doneDay = sqlite3_column_int(queryStatement, 7)
                let timeRestDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 8)))
                let timeRestSetDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 9)))
                let numDay = sqlite3_column_int(queryStatement, 10)
                let valueSet = String(describing: String(cString: sqlite3_column_text(queryStatement, 11)))
                
                //определяем начало программы, создаем программу, уровень, день.
                if prog.name == "" {
                    prog.id = idProg
                    prog.name = nameProg
                    prog.introduction = introdactionProg
                    prog.backpic = backpicProg
                    
                    currentLvl = idLvl
                    currentDay = idDay
                }
                //проверяем уровень, он текущий тогда продолжаем заполнять дни
                //или он новый, тогда вставляем уровень в программу и заполняем новый
                if idLvl == currentLvl {
                    lvl.id = idLvl
                    lvl.name = nameLvl
                    
                    //проверяем день, он текущий тогда вставляем день в программу
                    //или он новый, тогда вставляем день в программу и заполняем новый
                    if idDay == currentDay {
                        day.id = idDay
                        day.sets?.append(valueSet)
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                    } else {
                        
                        lvl.days.append(day)
                        day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                        day.id = idDay
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                        
                        day.sets?.append(valueSet)
                        
                        currentDay = idDay
                    }
                    continue
                    
                } else {
                    
                    lvl.days.append(day)
                    
                    prog.lvls?.append(lvl)
                    
                    lvl = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
                    lvl.name = nameLvl
                    lvl.id = idLevel
                    
                    day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                    day.id = idDay
                    
                    day.timeRest = timeRestDay
                    day.timeRestSet = timeRestSetDay
                    day.done = Int(doneDay)
                    day.numDay = Int(numDay)
                    
                    day.sets?.append(valueSet)
                    
                    currentDay = idDay
                    currentLvl = idLvl
                    
                    continue
                }
            }
            // the last one
            lvl.days.append(day)
            prog.lvls?.append(lvl)
            
        } else {
            print("SELECT statement could not be prepared 11111111111")
        }
        sqlite3_finalize(queryStatement)
        
        if prog.id == 0 {
            // берем следующий уровень и первый день
            prog = selectFirstDayForLevelProg(idProgram: idProgram, idLevel: idLevel + 1)
        }
        
        if prog.id == 0 {
        //если дошли сюда значит либо ExtraLvl либо что-то сломано :/
//            prog.id = idProgram
//            lvl.id = idLevel
//            day.id = idDay
//
//            lvl.days.append(day)
//            prog.lvls?.append(lvl)
            
            prog.id = idProgram
            prog.lvls![0].id = idLevel
            prog.lvls![0].days[0].id = idDay
        }
        
        return prog
    }
    func selectFirstDayForLevelProg(idProgram: Int64, idLevel: Int64) -> Program {
        
        let queryStatementString = "select P.idProgram, P.name, P.introdaction, P.backpic, L.idLevel, L.name, D.idDay, D.done, D.timeRest, D.timeRestSet, D.numDay, S.value from Program P join Level L on P.idProgram = L.idProgram join Day D on L.idLevel = D.idLevel join Sett S on D.idDay = S.idDay where P.idProgram = \(idProgram) and L.idLevel = \(idLevel) and D.idDay = (select D.idDay from Program P join Level L on P.idProgram = L.idProgram join Day D on L.idLevel = D.idLevel join Sett S on D.idDay = S.idDay where P.idProgram = \(idProgram) and L.idLevel = \(idLevel) order by P.idProgram, L.idLevel, D.idDay, S.idSet limit 1) order by P.idProgram, L.idLevel, D.idDay, S.idSet;"
        
        var queryStatement: OpaquePointer?
        //        var prog : Program?
        
        var prog: Program = Program(id: 0, name: "", introduction: "", backpic: "", lvls: [], numProg: 0)
        var day: Days = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
        var lvl: LvlsProgram = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            var currentLvl: Int64 = 0
            var currentDay: Int64 = 0
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let idProg = sqlite3_column_int64(queryStatement, 0)
                let nameProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let introdactionProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let backpicProg = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let idLvl = sqlite3_column_int64(queryStatement, 4)
                let nameLvl = String(describing: String(cString: sqlite3_column_text(queryStatement, 5)))
                let idDay = sqlite3_column_int64(queryStatement, 6)
                let doneDay = sqlite3_column_int(queryStatement, 7)
                let timeRestDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 8)))
                let timeRestSetDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 9)))
                let numDay = sqlite3_column_int(queryStatement, 10)
                let valueSet = String(describing: String(cString: sqlite3_column_text(queryStatement, 11)))
                
                //определяем начало программы, создаем программу, уровень, день.
                if prog.name == "" {
                    prog.id = idProg
                    prog.name = nameProg
                    prog.introduction = introdactionProg
                    prog.backpic = backpicProg
                    
                    currentLvl = idLvl
                    currentDay = idDay
                }
                //проверяем уровень, он текущий тогда продолжаем заполнять дни
                //или он новый, тогда вставляем уровень в программу и заполняем новый
                if idLvl == currentLvl {
                    lvl.name = nameLvl
                    lvl.id = idLvl
                    //проверяем день, он текущий тогда вставляем день в программу
                    //или он новый, тогда вставляем день в программу и заполняем новый
                    if idDay == currentDay {
                        day.id = idDay
                        day.sets?.append(valueSet)
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                    } else {
                        
                        lvl.days.append(day)
                        day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                        day.id = idDay
                        day.timeRest = timeRestDay
                        day.timeRestSet = timeRestSetDay
                        day.done = Int(doneDay)
                        day.numDay = Int(numDay)
                        
                        day.sets?.append(valueSet)
                        
                        currentDay = idDay
                    }
                    continue
                    
                } else {
                    
                    lvl.days.append(day)
                    
                    prog.lvls?.append(lvl)
                    
                    lvl = LvlsProgram(id: 0, name: "", days: [], numLevel: 0)
                    lvl.id = idLvl
                    lvl.name = nameLvl
                    
                    day = Days(id: 0, timeRest: "", timeRestSet: "", sets: [], done: 0, numDay: 0)
                    day.id = idDay
                    day.timeRest = timeRestDay
                    day.timeRestSet = timeRestSetDay
                    day.done = Int(doneDay)
                    day.numDay = Int(numDay)
                    
                    day.sets?.append(valueSet)
                    
                    currentDay = idDay
                    currentLvl = idLvl
                    
                    continue
                }
            }
            // the last one
            lvl.days.append(day)
            prog.lvls?.append(lvl)
            
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return prog
    }
    
    // MARK: - PROGRESS
    
    // update progress
    // TO DO: drop progress for selected LEVEL and DAY
    
    //change position progress in row
    func updatePositionProgress(numUpperObj: Int64, numLowerObj: Int64) {
        let updateStatementStirng = "UPDATE Progress SET numObj = CASE numObj WHEN ? THEN ? WHEN ? THEN ? ELSE numObj END;"
        var updateStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, updateStatementStirng, -1, &updateStatement, nil) == SQLITE_OK {
            sqlite3_bind_int64(updateStatement, 1, numUpperObj)
            sqlite3_bind_int64(updateStatement, 2, numLowerObj)
            sqlite3_bind_int64(updateStatement, 3, numLowerObj)
            sqlite3_bind_int64(updateStatement, 4, numUpperObj)
            if sqlite3_step(updateStatement) == SQLITE_DONE {
                print("Successfully update row.")
            } else {
                print("Could not update row.")
            }
        } else {
            print("UPDATE statement could not be prepared")
        }
        sqlite3_finalize(updateStatement)
    }
    //    delete whole progress
    func deleteObjProgress(numObj: Int64) {
        let deleteStatementStirng = "DELETE FROM Progress WHERE numObj = ?;"
        var deleteStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_int64(deleteStatement, 1, numObj)
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("Successfully deleted row.")
            } else {
                print("Could not delete row.")
            }
        } else {
            print("DELETE statement could not be prepared")
        }
        sqlite3_finalize(deleteStatement)
        
        let updateStatementStirng = "UPDATE Progress SET numObj = numObj - 1 where numObj > ?;"
        var updateStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, updateStatementStirng, -1, &updateStatement, nil) == SQLITE_OK {
            sqlite3_bind_int64(updateStatement, 1, numObj)
            if sqlite3_step(updateStatement) == SQLITE_DONE {
                print("Successfully update row.")
            } else {
                print("Could not update row.")
            }
        } else {
            print("UPDATE statement could not be prepared")
        }
        sqlite3_finalize(updateStatement)
    }
    //insert progress
    func insertProgress(numObj: Int64, idProgram: Int64, idLevel: Int64, idDay: Int64, percent: String) {
        
        let insertStatementString = "INSERT INTO Progress (numObj,idProgram,idLevel,idDay,dateDone,percent) VALUES (?,?,?,?,datetime('now'),?);"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_int64(insertStatement, 1, numObj)
            sqlite3_bind_int64(insertStatement, 2, idProgram)
            sqlite3_bind_int64(insertStatement, 3, idLevel)
            sqlite3_bind_int64(insertStatement, 4, idDay)
            sqlite3_bind_text(insertStatement, 5, (percent as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    // read progress
    func selectProgresssesForNumObj(numObj: Int64) -> [Progress] {
        let queryStatementString = "select idProgress,numObj,idProgram,idLevel,idDay,dateDone,case cast (strftime('%w', dateDone) as integer) when 0 then 'Sunday' when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thursday' when 5 then 'Friday' else 'Saturday' end as servdayofweek, percent from Progress where numObj = \(numObj) and dateDone > (SELECT DATETIME('now', '-7 day'));"
        
        var queryStatement: OpaquePointer?
        //        var prog : Program?
        
        var progress: [Progress] = []
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let idProgress = sqlite3_column_int64(queryStatement, 0)
                let numObj = sqlite3_column_int64(queryStatement, 1)
                let idProgram = sqlite3_column_int64(queryStatement, 2)
                let idLevel = sqlite3_column_int64(queryStatement, 3)
                let idDay = sqlite3_column_int64(queryStatement, 4)
                let dateDone = String(describing: String(cString: sqlite3_column_text(queryStatement, 5)))
                let dayWeek = String(describing: String(cString: sqlite3_column_text(queryStatement, 6)))
                let percent = String(describing: String(cString: sqlite3_column_text(queryStatement, 7)))
                
                let progressSingle = Progress(id: idProgress, numObj: numObj, idProgram: idProgram, idLevel: idLevel, idDay: idDay, date: dateDone, dayWeek: dayWeek, restDay: "", percent: percent, historyProgress: [])
                
                progress.append(progressSingle)
            }
            
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return progress
    }
    
    func selectAllActuallyProgress() -> [Progress] {
        let queryStatementString = "select idProgress,numObj,idProgram,Progress.idLevel,Progress.idDay,timeRest,percent from Progress join Day D on Progress.idDay = D.idDay where Progress.idProgress in (SELECT MAX(idProgress) from Progress group by numObj) order by numObj;"
        
        var queryStatement: OpaquePointer?
        //        var prog : Program?
        
        var progress: [Progress] = []
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let idProgress = sqlite3_column_int64(queryStatement, 0)
                let numObj = sqlite3_column_int64(queryStatement, 1)
                let idProgram = sqlite3_column_int64(queryStatement, 2)
                let idLevel = sqlite3_column_int64(queryStatement, 3)
                let idDay = sqlite3_column_int64(queryStatement, 4)
                let restDay = String(describing: String(cString: sqlite3_column_text(queryStatement, 5)))
                let percent = String(describing: String(cString: sqlite3_column_text(queryStatement, 6)))
                let history = selectProgresssesForNumObj(numObj: numObj)
                let progressSingle = Progress(id: idProgress, numObj: numObj, idProgram: idProgram, idLevel: idLevel, idDay: idDay, date: "", dayWeek: "", restDay: restDay, percent: percent, historyProgress: history)
                
                progress.append(progressSingle)
            }
            
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return progress
    }
    func selectCurrentDayWeek() -> Int64 {
        
        //        let queryStatementString = "select case cast (strftime('%w', date('now')) as integer) when 0 then 'Sunday' when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thursday' when 5 then 'Friday' else 'Saturday' end as servdayofweek;"
        let queryStatementString = "select cast (strftime('%w', date('now')) as integer);"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let intWeek = sqlite3_column_int64(queryStatement, 0)
                return intWeek
            }
            
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return -1
    }
    
}