import Foundation
import SQLite3

enum SleepRecordTypes: Int {
    case wentToSleep = 0
    case confusionalArousal = 1
    case wokeUp = 2
    case note = 3
}

class DatabaseManager {
    static let shared = DatabaseManager()
    private var db: OpaquePointer?
    private let dbName = "SleepJournal.sqlite"

    private init() {
        openDatabase()
        createTableIfNeeded()
    }

    private func getDocumentsDirectory() -> URL {
        FileManager.default.urls(for: .documentDirectory, in: .userDomainMask).first!
    }

    private func openDatabase() {
        let fileURL = getDocumentsDirectory().appendingPathComponent(dbName)

        if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
            print("Successfully opened connection to database at \(fileURL.path)")
        } else {
            print("Unable to open database")
        }
    }

    private func createTableIfNeeded() {
        let createTableString = """
            CREATE TABLE IF NOT EXISTS records (
                record_id TEXT PRIMARY KEY,
                record_type INTEGER CHECK (record_type IN (0, 1, 2, 3)),
                time_user_entered TIME,
                created_at DATETIME,
                updated_at DATETIME,
                remote_id TEXT,
                note TEXT,
                is_note_only BOOLEAN
            );
        """

        var createTableStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("Records table created successfully")
            } else {
                print("Records table could not be created")
            }
        } else {
            print("CREATE TABLE statement could not be prepared")
        }
        sqlite3_finalize(createTableStatement)
    }

    func addSleepRecord(type: SleepRecordTypes, datetimeUserEntered: Date, note: String? = nil, isNoteOnly: Bool = false) {
        let insertString = """
            INSERT INTO records (record_id, record_type, time_user_entered, created_at, updated_at, remote_id, note, is_note_only)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
        """

        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertString, -1, &insertStatement, nil) == SQLITE_OK {
            let recordId = UUID().uuidString
            let currentDate = Date()
            let dateFormatter = DateFormatter()
            dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"

            sqlite3_bind_text(insertStatement, 1, recordId, -1, nil)
            sqlite3_bind_int(insertStatement, 2, Int32(type.rawValue))
            sqlite3_bind_text(insertStatement, 3, dateFormatter.string(from: datetimeUserEntered), -1, nil)
            sqlite3_bind_text(insertStatement, 4, dateFormatter.string(from: currentDate), -1, nil)
            sqlite3_bind_text(insertStatement, 5, dateFormatter.string(from: currentDate), -1, nil)
            sqlite3_bind_null(insertStatement, 6)

            if let note = note {
                sqlite3_bind_text(insertStatement, 7, note, -1, nil)
            } else {
                sqlite3_bind_null(insertStatement, 7)
            }

            sqlite3_bind_int(insertStatement, 8, isNoteOnly ? 1 : 0)

            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted record")
            } else {
                print("Could not insert record")
            }
        } else {
            print("INSERT statement could not be prepared")
        }
        sqlite3_finalize(insertStatement)
    }

    func deleteSleepRecord(record_id: UUID) -> Bool {
        let deleteString = "DELETE FROM records WHERE record_id = ?;"

        var deleteStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, deleteString, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(deleteStatement, 1, record_id.uuidString, -1, nil)

            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                sqlite3_finalize(deleteStatement)
                return true
            }
        }
        sqlite3_finalize(deleteStatement)
        return false
    }

    func updateSleepRecord(record_id: UUID, record_type: SleepRecordTypes? = nil, time_user_entered: Date? = nil, remote_id: UUID? = nil, note: String? = nil) {
        var updateFields: [String] = ["updated_at = ?"]
        var paramIndex = 2

        if record_type != nil {
            updateFields.append("record_type = ?")
        }
        if time_user_entered != nil {
            updateFields.append("time_user_entered = ?")
        }
        if remote_id != nil {
            updateFields.append("remote_id = ?")
        }
        if note != nil {
            updateFields.append("note = ?")
        }

        let updateString = "UPDATE records SET \(updateFields.joined(separator: ", ")) WHERE record_id = ?;"

        var updateStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, updateString, -1, &updateStatement, nil) == SQLITE_OK {
            let dateFormatter = DateFormatter()
            dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"

            sqlite3_bind_text(updateStatement, 1, dateFormatter.string(from: Date()), -1, nil)

            if let recordType = record_type {
                sqlite3_bind_int(updateStatement, Int32(paramIndex), Int32(recordType.rawValue))
                paramIndex += 1
            }

            if let timeUserEntered = time_user_entered {
                sqlite3_bind_text(updateStatement, Int32(paramIndex), dateFormatter.string(from: timeUserEntered), -1, nil)
                paramIndex += 1
            }

            if let remoteId = remote_id {
                sqlite3_bind_text(updateStatement, Int32(paramIndex), remoteId.uuidString, -1, nil)
                paramIndex += 1
            }

            if let note = note {
                sqlite3_bind_text(updateStatement, Int32(paramIndex), note, -1, nil)
                paramIndex += 1
            }

            sqlite3_bind_text(updateStatement, Int32(paramIndex), record_id.uuidString, -1, nil)

            if sqlite3_step(updateStatement) == SQLITE_DONE {
                print("Successfully updated record")
            } else {
                print("Could not update record")
            }
        } else {
            print("UPDATE statement could not be prepared")
        }
        sqlite3_finalize(updateStatement)
    }

    func wipeDatabase() {
        let dropTableString = "DROP TABLE IF EXISTS records;"

        var dropTableStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, dropTableString, -1, &dropTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(dropTableStatement) == SQLITE_DONE {
                print("Successfully wiped database")
                createTableIfNeeded()
            } else {
                print("Could not wipe database")
            }
        } else {
            print("DROP TABLE statement could not be prepared")
        }
        sqlite3_finalize(dropTableStatement)
    }

    deinit {
        sqlite3_close(db)
    }
}