import Foundation
import GRDB



let totalLevels = 3

struct AppDatabase {
    /// Creates an `AppDatabase`, and make sure the database schema is ready.
    init(_ dbWriter: any DatabaseWriter) throws {
        self.dbWriter = dbWriter
        try migrator.migrate(dbWriter)
    }

    /// Provides access to the database.
    ///
    /// Application can use a `DatabasePool`, while SwiftUI previews and tests
    /// can use a fast in-memory `DatabaseQueue`.
    ///
    /// See <https://swiftpackageindex.com/groue/grdb.swift/documentation/grdb/databaseconnections>
    private let dbWriter: any DatabaseWriter

    /// The DatabaseMigrator that defines the database schema.
    ///
    /// See <https://swiftpackageindex.com/groue/grdb.swift/documentation/grdb/migrations>
    private var migrator: DatabaseMigrator {
        var migrator = DatabaseMigrator()

        #if DEBUG
        // Speed up development by nuking the database when migrations change
        // See <https://swiftpackageindex.com/groue/grdb.swift/documentation/grdb/migrations>
        migrator.eraseDatabaseOnSchemaChange = true
        #endif

        migrator.registerMigration("createLine") { db in
            // Create a table
            // See <https://swiftpackageindex.com/groue/grdb.swift/documentation/grdb/databaseschema>
            try db.create(table: "Line") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("body", .text).notNull()
                t.column("chap", .integer).notNull()
                t.column("line_id", .integer).notNull()
                t.column("book", .text).notNull()
                t.column("verse", .integer)
            }

            try db.create(table: "Seg") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("seg_id", .integer).notNull()
                t.column("line_id", .integer).notNull()
                t.column("book", .text).notNull()
            }

            try db.create(table: "Ribbon") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("pos", .integer).notNull()
                t.column("groupId", .integer).notNull()
                t.column("undoLevel", .integer).notNull()

                t.column("currentLevel", .integer).notNull()
                t.column("minLevel", .integer).notNull()
                t.column("maxLevel", .integer).notNull()
.defaults(to: 1)
                t.column("title", .text).notNull()
                t.column("book", .text).notNull()
                t.column("scrollOffset", .integer).notNull()
                t.column("scrollId", .text)
            }

            try db.create(table: "SelectedRibbon") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("ribbonGroupId", .integer).notNull()
            }

            try db.create(table: "ScrollState") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("scrollId", .text).notNull()
                t.column("scrollOffset", .integer).notNull()
            }

            // change this to nuke/remake the database
            try db.create(table: "foo4") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("ribbonId", .integer).notNull()
            }
        }


        // Migrations for future application versions will be inserted here:
        // migrator.registerMigration(...) { db in
        //     ...
        // }

        return migrator
    }
}

// MARK: - Database Access: Writes


func load<T: Decodable>(_ filename: String) -> T {
    let data: Data

    guard let file = Bundle.main.url(forResource: filename, withExtension: nil)
        else {
            fatalError("Couldn't find \(filename) in main bundle.")
    }

    do {
        data = try Data(contentsOf: file)
    } catch {
        fatalError("Couldn't load \(filename) from main bundle:\n\(error)")
    }

    do {
        let decoder = JSONDecoder()
        return try decoder.decode(T.self, from: data)
    } catch {
        fatalError("Couldn't parse \(filename) as \(T.self):\n\(error)")
    }
}

extension AppDatabase {

    func getSelectedRibbon() async throws -> [Ribbon] {
        try await dbWriter.write { db in
            var sr = try Ribbon.fetchAll(db, sql: """
            SELECT Ribbon.* FROM SelectedRibbon \
            JOIN (select distinct r1.* from Ribbon r1 join Ribbon r2 ON \
                r1.undoLevel = r2.currentLevel AND r1.id = r2.id ORDER BY pos ASC) as Ribbon \
            ON SelectedRibbon.ribbonGroupId = Ribbon.groupId \
            WHERE SelectedRibbon.rowId = 1
            """)

            print("meow get selected ribbon \(sr)")
            return sr
        }
    }


    func updateRibbonPosition(_ ribbon: inout Ribbon, _ oldPos: Int, _ newPos: Int) async throws {
        try await dbWriter.write { [ribbon] db in

            // This is only for moving back rn

            print("MEOW HERE")
            print(oldPos)
            print(newPos)
            print(ribbon)
            print(ribbon.id!)
            print("MEOW HERE 2")


            // try db.execute(sql: """
            //                BEGIN TRANSACTION;
            //                """)
            do {

                if (newPos < oldPos) {

            try db.execute(sql: """
                           UPDATE Ribbon
                           SET pos =
                           CASE
                           WHEN pos = ? THEN ?
                           ELSE
                           pos + 1
                           END
                           WHERE (pos >= ? AND pos <= ?)
                           """, arguments: [oldPos, newPos, newPos, oldPos])
            } else {

                print("DIFFFFF")

            try db.execute(sql: """
                           UPDATE Ribbon
                           SET pos =
                           CASE
                           WHEN pos = ? THEN ?
                           ELSE
                           pos - 1
                           END
                           WHERE (pos >= ? AND pos <= ?)
                           """, arguments: [oldPos, newPos, oldPos, newPos])

            }

            // try db.execute(sql: """
            //                UPDATE Ribbon
            //                SET pos = ?
            //                WHERE (id = ?)
            //                """, arguments: [newPos, ribbon.id!])

        var ret =  try Ribbon.fetchAll(db, sql: "SELECT * FROM Ribbon ORDER BY pos ASC") // [Player]
        // print(ret)
            print("all")
         print(ret)
            } catch {
                print("Error info: \(error)")
            }

            // try ribbon.saved(db)

            // try db.execute(sql: """
            //                COMMIT;
            //                """)

        }

    }

    func saveRibbon(_ ribbon: inout Ribbon) async throws {
        // if ribbon.name.isEmpty {
        //     throw ValidationError.missingName
        // }
        ribbon = try await dbWriter.write { [ribbon] db in
            try ribbon.saved(db)
        }
    }

    func redoRibbon(_ ribbon: inout Ribbon) async throws {
        let currentLevel = ribbon.currentLevel
        let minLevel = ribbon.maxLevel

        if currentLevel == minLevel {
            print("no where to redo")
            return
        }

        let newCurrent = (ribbon.currentLevel + 1) %% totalLevels

        do {
            try await dbWriter.write { [ribbon] db in
                try db.execute(sql: """
                               UPDATE Ribbon  \
                               SET currentLevel = ? WHERE groupId = ?
                               """, arguments: [newCurrent, ribbon.groupId])
            }

        } catch {
            print("Redo Ribbon Error info: \(error)")
        }

    }

    // this sets the current undoLevel to the previous value
    // if you can go back
    func undoRibbon(_ ribbon: inout Ribbon) async throws {
        let currentLevel = ribbon.currentLevel
        let minLevel = ribbon.minLevel

        if currentLevel == minLevel {
            print("no where to undo")
            return
        }

        let newCurrent = (ribbon.currentLevel - 1) %% totalLevels

        do {
            try await dbWriter.write { [ribbon] db in
                try db.execute(sql: """
                               UPDATE Ribbon  \
                               SET currentLevel = ? WHERE groupId = ?
                               """, arguments: [newCurrent, ribbon.groupId])
            }

        } catch {
            print("Undo Ribbon Error info: \(error)")
        }

    }

    // deletes all undo steps above the current undo level
    // and adds new undo level at the new current level,
    // adjusts the minLevel and maxLevel
    func bumpRibbon(_ ribbon: inout Ribbon) async throws -> [Ribbon] {
        var level = ribbon.currentLevel
        let maxLevel = ribbon.maxLevel


        // gets all the levels from the current to the max
        // so they can be deleted
        var delLevels2 = [Int]()
        if level != maxLevel {
            repeat {
                level = (level + 1) %% totalLevels
                delLevels2.append(level)
            } while level != maxLevel
        }
        let delLevels = delLevels2

        let newMax = (ribbon.currentLevel + 1) %% totalLevels
        let newCurrent = newMax
        let newMin = newMax == ribbon.minLevel ? (ribbon.minLevel + 1) %% totalLevels
            : ribbon.minLevel

        ribbon.minLevel = newMin
        ribbon.maxLevel = newMax
        ribbon.undoLevel = newCurrent
        ribbon.currentLevel = newCurrent
        ribbon.id = nil

        do {
            try await dbWriter.write { [ribbon] db in
                for l in delLevels {
                    try db.execute(sql: """
                    DELETE FROM Ribbon \
                    WHERE groupId = ? \
                        AND undoLevel = ?
                    """, arguments: [ribbon.groupId, l])
                }

                try db.execute(sql: """
                UPDATE Ribbon  \
                SET minLevel = ?, maxLevel = ?, currentLevel = ? WHERE groupId = ?
                """, arguments: [newMin, newMax, newCurrent, ribbon.groupId])


                // upsert
                var ret = try Ribbon.fetchAll(db, sql: """
                    SELECT * from Ribbon WHERE groupId = ? AND undoLevel = ?
                """, arguments: [ribbon.groupId, ribbon.undoLevel])

                if ret.count == 0 {
                    // insert
                 _ = try ribbon.inserted(db)
                } else {
                    var updatedRibbon = ret[0]
                    updatedRibbon.minLevel = newMin
                    updatedRibbon.maxLevel = newMax
                    updatedRibbon.undoLevel = newCurrent
                    updatedRibbon.currentLevel = newCurrent
                    updatedRibbon.scrollId = ribbon.scrollId
                    updatedRibbon.scrollOffset = ribbon.scrollOffset
                    try updatedRibbon.update(db)
                }

                ret = try Ribbon.fetchAll(db, sql: """
                    SELECT * from Ribbon WHERE groupId = ? AND undoLevel = ?
                """, arguments: [ribbon.groupId, newCurrent])

                return ret
            }
        } catch {
            print("Error info: \(error)")
        }
        return []
    }

    func saveSelectedRibbon(_ selectedRibbon: inout SelectedRibbon) async throws {
        // if ribbon.name.isEmpty {
        //     throw ValidationError.missingName
        // }
        try await dbWriter.write { [selectedRibbon] db in
            try selectedRibbon.update(db)
        }
    }

    func saveScrollState(_ scrollState: inout ScrollState) async throws {
        // if ribbon.name.isEmpty {
        //     throw ValidationError.missingName
        // }
        try await dbWriter.write { [scrollState] db in
            try scrollState.update(db)
        }
    }

    func importJson(_ filename: String, _ db: Database) throws {
        let importJson: JsonImport = load(filename)

        var x = 0
        // if try Line.all().isEmpty(db) {
        for l in importJson.lines {
            // print("importing Lines")
            if x < 5 {
                print(l)
                x += 1
            }
            _ = try l.inserted(db)
        }

        x = 0
        for l in importJson.segs {
            // print("importing SEGS")

            if x < 5 {
                print(l)
                x += 1
            }
            _ = try l.inserted(db)
        }
    }

    /// Create random Lines if the database is empty.
    func initDatabase() throws {
        do {
            try dbWriter.write { db in
                if try Line.all().isEmpty(db)
                {
                    try importJson("john_export.json", db)
                    try importJson("mark_export.json", db)
                    try importJson("acts_export.json", db)
                    _ = try Ribbon(id: 1,
                                   groupId: 1,
                                   pos: 1,
                                   undoLevel: 0,
                                   currentLevel: 0,
                                   minLevel: 0,
                                   maxLevel: 0,
                                   title: "Gospel of John",
                                   book: "bible.john",
                                   scrollId: "1",
                                   scrollOffset: 0).inserted(db)

                    _ = try Ribbon(id: 2,
                                   groupId: 2,
                                   pos: 2,
                                   undoLevel: 0,
                                   currentLevel: 0,
                                   minLevel: 0,
                                   maxLevel: 0,
                                   title: "Gospel according to Mark",
                                   book: "bible.mark",
                                   scrollId: "1",
                                   scrollOffset: 300).inserted(db)

                    /////

                    _ = try Ribbon(id: 3,
                                   groupId: 3,
                                   pos: 3,
                                   undoLevel: 0,
                                   currentLevel: 2,
                                   minLevel: 0,
                                   maxLevel: 2,
                                   title: "Acts",
                                   book: "bible.acts",
                                   scrollId: "1",
                                   scrollOffset: 0).inserted(db)

                    _ = try Ribbon(id: 4,
                                   groupId: 3,
                                   pos: 3,
                                   undoLevel: 1,
                                   currentLevel: 2,
                                   minLevel: 0,
                                   maxLevel: 2,
                                   title: "Acts",
                                   book: "bible.acts",
                                   scrollId: "1",
                                   scrollOffset: 0).inserted(db)

                    _ = try Ribbon(id: 5,
                                   groupId: 3,
                                   pos: 3,
                                   undoLevel: 2,
                                   currentLevel: 2,
                                   minLevel: 0,
                                   maxLevel: 2,
                                   title: "Acts",
                                   book: "bible.acts",
                                   scrollId: "1",
                                   scrollOffset: 0).inserted(db)

                _ = try SelectedRibbon(id: 1, ribbonGroupId: 1).inserted(db)
            }
        }
        } catch {
            print("Error info: \(error)")
        }
    }

}

// MARK: - Database Access: Reads

// This demo app does not provide any specific reading method, and insteadKK
// gives an unrestricted read-only access to the rest of the application.
// In your app, you are free to choose another path, and define focused
// reading methods.
extension AppDatabase {
    /// Provides a read-only access to the database
    var reader: DatabaseReader {
        dbWriter
    }
}