//
//  SpeakDayEveryDB.swift
//  SpeakEasyLearnEnglish
//
//  Created by edy on 2025/7/28.
//

import UIKit
import SQLite3


let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

class SpeakDayDatabase {
    private var db: OpaquePointer?
    
    init() {
        let fileURL =  "SpeakCache".documentURL().appendingPathComponent("speakday.db")
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
            Print("无法打开数据库")
            return
        }
        createTable()
    }
    
    deinit {
        sqlite3_close(db)
    }
    
    // 创建表
    private func createTable() {
        let createTableQuery = """
        CREATE TABLE IF NOT EXISTS SpeakDayModel (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT,
            PracticeFreeNum INTEGER,
            studyFreeTm INTEGER,
            studytime INTEGER
        );
        """
        
        var createTableStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, createTableQuery, -1, &createTableStmt, nil) == SQLITE_OK {
            if sqlite3_step(createTableStmt) == SQLITE_DONE {
                print("表创建成功")
            } else {
                print("表创建失败: \(String(cString: sqlite3_errmsg(db)!))")
            }
        } else {
            print("预处理语句失败: \(String(cString: sqlite3_errmsg(db)!))")
        }
        
        sqlite3_finalize(createTableStmt)
    }
    
    // 插入数据
    @discardableResult
    func insert(model: SpeakDayModel) -> Bool {
        let insertQuery = """
        INSERT INTO SpeakDayModel (date, PracticeFreeNum, studyFreeTm, studytime)
        VALUES (?, ?, ?, ?);
        """
        
        var insertStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, insertQuery, -1, &insertStmt, nil) != SQLITE_OK {
            print("插入预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return false
        }
        
        // 绑定参数
        if let date = model.date {
            sqlite3_bind_text(insertStmt, 1, date, -1, SQLITE_TRANSIENT)
        } else {
            sqlite3_bind_null(insertStmt, 1)
        }
        
        if let practiceFreeNum = model.PracticeFreeNum {
            sqlite3_bind_int(insertStmt, 2, Int32(practiceFreeNum))
        } else {
            sqlite3_bind_null(insertStmt, 2)
        }
        
        if let studyFreeTm = model.studyFreeTm {
            sqlite3_bind_int(insertStmt, 3, Int32(studyFreeTm))
        } else {
            sqlite3_bind_null(insertStmt, 3)
        }
        
        if let studytime = model.studytime {
            sqlite3_bind_int(insertStmt, 4, Int32(studytime))
        }
        
        // 执行插入
        if sqlite3_step(insertStmt) != SQLITE_DONE {
            print("插入失败: \(String(cString: sqlite3_errmsg(db)!))")
            sqlite3_finalize(insertStmt)
            return false
        }
        
        sqlite3_finalize(insertStmt)
        return true
    }
    
    func deleteTab() -> Bool {
        let deleteQuery = "DELETE FROM SpeakDayModel;"
        
        var deleteStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, deleteQuery, -1, &deleteStmt, nil) != SQLITE_OK {
            print("删除预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return false
        }
        if sqlite3_step(deleteStmt) != SQLITE_DONE {
            print("删除失败: \(String(cString: sqlite3_errmsg(db)!))")
            sqlite3_finalize(deleteStmt)
            return false
        }
        sqlite3_finalize(deleteStmt)
        return true
    }
    
    // 根据ID删除数据
    func delete(id: Int) -> Bool {
        let deleteQuery = "DELETE FROM SpeakDayModel WHERE id = ?;"
        
        var deleteStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, deleteQuery, -1, &deleteStmt, nil) != SQLITE_OK {
            print("删除预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return false
        }
        
        sqlite3_bind_int(deleteStmt, 1, Int32(id))
        
        if sqlite3_step(deleteStmt) != SQLITE_DONE {
            print("删除失败: \(String(cString: sqlite3_errmsg(db)!))")
            sqlite3_finalize(deleteStmt)
            return false
        }
        
        sqlite3_finalize(deleteStmt)
        return true
    }
    
    // 更新数据
    @discardableResult
    func update(model: SpeakDayModel) -> Bool {
        guard let id = model.id else {
            print("更新失败：ID不能为空")
            return false
        }
        
        let updateQuery = """
        UPDATE SpeakDayModel
        SET date = ?, PracticeFreeNum = ?, 
        studyFreeTm = ?, studytime = ?
        WHERE id = ?;
        """
        
        var updateStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, updateQuery, -1, &updateStmt, nil) != SQLITE_OK {
            print("更新预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return false
        }
        
        // 绑定参数
        if let date = model.date {
            sqlite3_bind_text(updateStmt, 1, date, -1, SQLITE_TRANSIENT)
        } else {
            sqlite3_bind_null(updateStmt, 1)
        }
        
        if let practiceFreeNum = model.PracticeFreeNum {
            sqlite3_bind_int(updateStmt, 2, Int32(practiceFreeNum))
        } else {
            sqlite3_bind_null(updateStmt, 2)
        }
        
        if let studyFreeTm = model.studyFreeTm {
            sqlite3_bind_int(updateStmt, 3, Int32(studyFreeTm))
        } else {
            sqlite3_bind_null(updateStmt, 3)
        }
        if let studytime = model.studytime{
            sqlite3_bind_int(updateStmt, 4, Int32(studytime))
        }
        
        sqlite3_bind_int(updateStmt, 5, Int32(id))
        
        if sqlite3_step(updateStmt) != SQLITE_DONE {
            print("更新失败: \(String(cString: sqlite3_errmsg(db)!))")
            sqlite3_finalize(updateStmt)
            return false
        }
        
        sqlite3_finalize(updateStmt)
        return true
    }
    
    // 查询所有数据
    func getAll() -> [SpeakDayModel] {
        let query = "SELECT * FROM SpeakDayModel;"
        var models: [SpeakDayModel] = []
        
        var selectStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, query, -1, &selectStmt, nil) != SQLITE_OK {
            print("查询预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return models
        }
        
        while sqlite3_step(selectStmt) == SQLITE_ROW {
            var model = SpeakDayModel()
            
            // 获取ID
            let id = sqlite3_column_int(selectStmt, 0)
            model.id = Int(id)
            
            // 获取date
            if let dateCString = sqlite3_column_text(selectStmt, 1) {
                model.date = String(cString: dateCString)
            }
            
            // 获取PracticeFreeNum
            if sqlite3_column_type(selectStmt, 2) != SQLITE_NULL {
                model.PracticeFreeNum = Int(sqlite3_column_int(selectStmt, 2))
            }
            
            // 获取studyFreeTm
            if sqlite3_column_type(selectStmt, 3) != SQLITE_NULL {
                model.studyFreeTm = Int(sqlite3_column_int(selectStmt, 3))
            }
            
            if sqlite3_column_type(selectStmt, 4) != SQLITE_NULL {
                model.studytime = Int(sqlite3_column_int(selectStmt, 4))
            }
            
            models.append(model)
        }
        
        sqlite3_finalize(selectStmt)
        return models
    }
    
    // 根据日期查询数据
    func getByDate(date: String) -> SpeakDayModel? {
        let query = "SELECT * FROM SpeakDayModel WHERE date = ?;"
        
        var selectStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, query, -1, &selectStmt, nil) != SQLITE_OK {
            print("查询预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return nil
        }
        
        if let dateCString = date.cString(using: .utf8) {
            sqlite3_bind_text(selectStmt, 1, dateCString, -1, SQLITE_TRANSIENT)
        }
        if sqlite3_step(selectStmt) == SQLITE_ROW {
            var model = SpeakDayModel()
            
            // 获取ID
            model.id = Int(sqlite3_column_int(selectStmt, 0))
            
            // 获取date
            if let dateCString = sqlite3_column_text(selectStmt, 1) {
                model.date = String(cString: dateCString)
            }
            
            // 获取PracticeFreeNum
            if sqlite3_column_type(selectStmt, 2) != SQLITE_NULL {
                model.PracticeFreeNum = Int(sqlite3_column_int(selectStmt, 2))
            }
            
            // 获取studyFreeTm
            if sqlite3_column_type(selectStmt, 3) != SQLITE_NULL {
                model.studyFreeTm = Int(sqlite3_column_int(selectStmt, 3))
            }
            if sqlite3_column_type(selectStmt, 4) != SQLITE_NULL {
                model.studytime = Int(sqlite3_column_int(selectStmt, 4))
            }
            sqlite3_finalize(selectStmt)
            return model
        }
        
        sqlite3_finalize(selectStmt)
        return nil
    }
    
    
    func GetByDates(dates: [String]) -> [SpeakDayModel]? {
        let placeholders = dates.map { _ in "?" }.joined(separator: ", ")
        let query = "SELECT * FROM SpeakDayModel WHERE date IN (\(placeholders)) ORDER BY date ASC;"
        var selectStmt: OpaquePointer?
        if sqlite3_prepare_v2(db, query, -1, &selectStmt, nil) != SQLITE_OK {
            print("查询预处理失败: \(String(cString: sqlite3_errmsg(db)!))")
            return nil
        }
        
        // 绑定所有日期参数
        for (index, date) in dates.enumerated() {
            if let dateCString = date.cString(using: .utf8) {
                // 参数索引从1开始
                sqlite3_bind_text(selectStmt, Int32(index + 1), dateCString, -1, SQLITE_TRANSIENT)
            }
        }
        var results = [SpeakDayModel]()
        while sqlite3_step(selectStmt) == SQLITE_ROW {
            var model = SpeakDayModel()
            model.id = Int(sqlite3_column_int(selectStmt, 0))
            if let dateCString = sqlite3_column_text(selectStmt, 1) {
                model.date = String(cString: dateCString)
            }
//            if sqlite3_column_type(selectStmt, 2) != SQLITE_NULL {
//                model.PracticeFreeNum = Int(sqlite3_column_int(selectStmt, 2))
//            }
//        
//            if sqlite3_column_type(selectStmt, 3) != SQLITE_NULL {
//                model.studyFreeTm = Int(sqlite3_column_int(selectStmt, 3))
//            }
//            
            // 获取studytime
            if sqlite3_column_type(selectStmt, 4) != SQLITE_NULL {
                model.studytime = Int(sqlite3_column_int(selectStmt, 4))
            }
            results.append(model)
        }
        sqlite3_finalize(selectStmt)
        return results
    }
    
}
