import Foundation
import SQLite3



struct Item {
    var id: Int
    var name: String
    var status: String
    var address: String
    var email: String
}
class DatabaseManager{
    
    let myTable = "Person"
    let myDatabase = "TestDatabase1.db"
    
    private var db: OpaquePointer?
    init() {
        openDatabase()
        createTable()
    }
    func openDatabase() {
        if db == nil {
            if let documentDirectory = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask).first {
                let fileURL = documentDirectory.appendingPathComponent(myDatabase)
                if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
                    print("Database is connected.")
                    print("Your path: \(fileURL.path)")
                } else {
                    print("Unable to open database.")
                }
            }
        }
    }
    
    func createTable() {
        let createTableString = """
           CREATE TABLE IF NOT EXISTS \(myTable)(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            status TEXT,
            address TEXT,
            email TEXT
           );
           """
        
        var createTableStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("Tabulka je vytvořena.")
            } else {
                print("Table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
        }
    
    // Metoda pro čtení dat z tabulky a její výpis do konzole
       func readData() {
           let query = "SELECT * FROM \(myTable);"
           var statement: OpaquePointer?
           print("reading from table: \(myTable)")
           if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
               while sqlite3_step(statement) == SQLITE_ROW {
                   let id = Int(sqlite3_column_int(statement, 0))
                   let name = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                   let status = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                   let address = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                   let email = String(describing: String(cString: sqlite3_column_text(statement, 4)))
                    print("ID: \(id), Name: \(name), Status: \(status), Address: \(address), Email: \(email)")
                   
                   }
              
           } else {
               let errmsg = String(cString: sqlite3_errmsg(db)!)
               print("error preparing select: \(errmsg)")
           }
           
           sqlite3_finalize(statement)
       }
    func insertItem(name: String, status: String, address: String, email: String) {
        let insertStatementString = "INSERT INTO \(myTable) (name, status, address, email) VALUES (:name, :status, :address, :email);"
        print("Received in DatabaseManager: \(name), \(status), \(address), \(email)")
        var insertStatement: OpaquePointer? = nil
        
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            // Přiřazení hodnot parametrům pomocí pojmenovaných parametrů
            sqlite3_bind_text(insertStatement, sqlite3_bind_parameter_index(insertStatement, ":name"), name, -1, nil)
            sqlite3_bind_text(insertStatement, sqlite3_bind_parameter_index(insertStatement, ":status"), status, -1, nil)
            sqlite3_bind_text(insertStatement, sqlite3_bind_parameter_index(insertStatement, ":address"), address, -1, nil)
            sqlite3_bind_text(insertStatement, sqlite3_bind_parameter_index(insertStatement, ":email"), email, -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)
    }
    
    func deleteAllItems() {
            let deleteStatementString = "DELETE FROM \(myTable);"
            var deleteStatement: OpaquePointer? = nil
            if sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) == SQLITE_OK {
                if sqlite3_step(deleteStatement) == SQLITE_DONE {
                    print("All items deleted successfully.")
                } else {
                    print("Could not delete all items.")
                }
            } else {
                print("DELETE statement could not be prepared.")
            }
            sqlite3_finalize(deleteStatement)
        }
    func printColumnNames(ofTable tableName: String) {
        let queryStatementString = "PRAGMA table_info(\(myTable));"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let columnName = String(cString: sqlite3_column_text(queryStatement, 1))
                print("Column name: \(columnName)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        
        sqlite3_finalize(queryStatement)
    }
    func printAllTables() {
        var statement: OpaquePointer?
        
        let query = "SELECT name FROM sqlite_master WHERE type='table';"
        
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
            while sqlite3_step(statement) == SQLITE_ROW {
                if let tableName = sqlite3_column_text(statement, 0) {
                    let name = String(cString: tableName)
                    print("Table: \(name)")
                }
            }
            sqlite3_finalize(statement)
        } else {
            print("Chyba při přípravě dotazu.")
        }
    }

   

    func pathDatabase(){
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent(myDatabase)
        let databasePath = fileURL.path
        print("Cesta k souboru databáze: \(databasePath)")

    }

    

      
    
}