mobile-everywhere/todo-swiftui/TodoSwiftUI/Models/DatabaseManager.swift

import Foundation
import SQLite3

// SQLITE_TRANSIENT tells SQLite to make its own copy of bound text data
private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

final class DatabaseManager {
    static let shared = DatabaseManager()

    private var db: OpaquePointer?

    private init() {
        openDatabase()
        createTable()
    }

    deinit {
        if db != nil {
            sqlite3_close(db)
        }
    }

    // MARK: - Database Setup

    private func openDatabase() {
        let fileURL = getDatabasePath()
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
            print("Error opening database: \(String(cString: sqlite3_errmsg(db)))")
            db = nil
        }
    }

    private func getDatabasePath() -> URL {
        let paths = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
        return paths[0].appendingPathComponent("todos.db")
    }

    private func createTable() {
        let sql = """
            CREATE TABLE IF NOT EXISTS todos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                completed INTEGER DEFAULT 0,
                category TEXT DEFAULT 'Personal',
                priority TEXT DEFAULT 'medium',
                created_at INTEGER DEFAULT (strftime('%s','now'))
            )
            """
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }

        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) != SQLITE_DONE {
                print("Error creating table: \(String(cString: sqlite3_errmsg(db)))")
            }
        } else {
            print("Error preparing create table: \(String(cString: sqlite3_errmsg(db)))")
        }
    }

    // MARK: - Helper

    private func bindText(_ statement: OpaquePointer?, index: Int32, value: String) {
        sqlite3_bind_text(statement, index, value, -1, SQLITE_TRANSIENT)
    }

    // MARK: - CRUD Operations

    func loadTodos() -> [Todo] {
        let sql = "SELECT id, title, completed, category, priority, created_at FROM todos ORDER BY created_at DESC"
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }
        var todos: [Todo] = []

        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
            while sqlite3_step(statement) == SQLITE_ROW {
                let id = sqlite3_column_int64(statement, 0)
                let title = String(cString: sqlite3_column_text(statement, 1))
                let completed = sqlite3_column_int(statement, 2) == 1
                let category = String(cString: sqlite3_column_text(statement, 3))
                let priorityStr = String(cString: sqlite3_column_text(statement, 4))
                let priority = Priority(rawValue: priorityStr) ?? .medium
                let createdAt = sqlite3_column_int64(statement, 5)

                let todo = Todo(
                    id: id,
                    title: title,
                    completed: completed,
                    category: category,
                    priority: priority,
                    createdAt: createdAt
                )
                todos.append(todo)
            }
        } else {
            print("Error loading todos: \(String(cString: sqlite3_errmsg(db)))")
        }
        return todos
    }

    @discardableResult
    func insertTodo(title: String, category: String, priority: Priority) -> Todo? {
        let sql = "INSERT INTO todos (title, category, priority, completed) VALUES (?, ?, ?, 0)"
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            print("Error preparing insert: \(String(cString: sqlite3_errmsg(db)))")
            return nil
        }

        bindText(statement, index: 1, value: title)
        bindText(statement, index: 2, value: category)
        bindText(statement, index: 3, value: priority.rawValue)

        guard sqlite3_step(statement) == SQLITE_DONE else {
            print("Error inserting todo: \(String(cString: sqlite3_errmsg(db)))")
            return nil
        }

        let id = sqlite3_last_insert_rowid(db)
        let createdAt = Int64(Date().timeIntervalSince1970)
        return Todo(
            id: id,
            title: title,
            completed: false,
            category: category,
            priority: priority,
            createdAt: createdAt
        )
    }

    func updateTodo(id: Int64, title: String, category: String, priority: Priority) {
        let sql = "UPDATE todos SET title = ?, category = ?, priority = ? WHERE id = ?"
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            print("Error preparing update: \(String(cString: sqlite3_errmsg(db)))")
            return
        }

        bindText(statement, index: 1, value: title)
        bindText(statement, index: 2, value: category)
        bindText(statement, index: 3, value: priority.rawValue)
        sqlite3_bind_int64(statement, 4, id)

        if sqlite3_step(statement) != SQLITE_DONE {
            print("Error updating todo: \(String(cString: sqlite3_errmsg(db)))")
        }
    }

    func toggleTodo(id: Int64, completed: Bool) {
        let sql = "UPDATE todos SET completed = ? WHERE id = ?"
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            print("Error preparing toggle: \(String(cString: sqlite3_errmsg(db)))")
            return
        }

        sqlite3_bind_int(statement, 1, completed ? 1 : 0)
        sqlite3_bind_int64(statement, 2, id)

        if sqlite3_step(statement) != SQLITE_DONE {
            print("Error toggling todo: \(String(cString: sqlite3_errmsg(db)))")
        }
    }

    func deleteTodo(id: Int64) {
        let sql = "DELETE FROM todos WHERE id = ?"
        var statement: OpaquePointer?
        defer { sqlite3_finalize(statement) }

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            print("Error preparing delete: \(String(cString: sqlite3_errmsg(db)))")
            return
        }

        sqlite3_bind_int64(statement, 1, id)

        if sqlite3_step(statement) != SQLITE_DONE {
            print("Error deleting todo: \(String(cString: sqlite3_errmsg(db)))")
        }
    }
}