#include "stdafx.h"
#include "SECSRuntimeManager.h"

// ³£Á¿
const std::string DATABASE_FILE = R"(SECSDataManager.db)";

// ¾²Ì¬³ÉÔ±³õʼ»¯
std::mutex SECSRuntimeManager::m_mutex;

// »ñÈ¡µ¥ÀýʵÀý
SECSRuntimeManager& SECSRuntimeManager::getInstance() {
    static SECSRuntimeManager instance;
    return instance;
}

// ¹¹Ô캯Êý
SECSRuntimeManager::SECSRuntimeManager() {
    m_pDB = new BL::SQLiteDatabase();
}

// Îö¹¹º¯Êý
SECSRuntimeManager::~SECSRuntimeManager() {
    termRuntimeSetting();

    if (m_pDB != nullptr) {
        delete m_pDB;
        m_pDB = nullptr;
    }
}

// ´ÓÊý¾Ý¿âÖлñÈ¡ÕûÊý
int SECSRuntimeManager::getIntFromDB(const std::string& query) {
    auto results = m_pDB->fetchResults(query);
    if (!results.empty() && !results[0].empty()) {
        // ת»»µÚÒ»¸ö²éѯ½á¹ûΪÕûÊý
        return std::stoi(results[0][0]);
    }

    return 0;
}

// ÅжÏVIDÊÇ·ñÖظ´
bool SECSRuntimeManager::isIDDuplicate(int nID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
		return false;
    }

    // ¶¨ÒåÒª¼ì²éµÄ±í
    std::vector<std::string> tables = { "SystemSV", "EqpSV", "SystemDV", "EqpDV", "SystemEC", "EqpEC" };

    // ±éÀú±í£¬¼ì²éÊÇ·ñÓÐÖظ´µÄ ID
    for (const auto& table : tables) {
        // ´´½¨ SQL ²éѯ
        std::string checkSQL = "SELECT COUNT(*) FROM " + table + " WHERE ID = " + std::to_string(nID) + ";";

        // Ö´Ðвéѯ
        auto results = m_pDB->fetchResults(checkSQL);
        int count = (!results.empty() && !results[0].empty()) ? std::stoi(results[0][0]) : 0;

        // Èç¹ûÕÒµ½ÁËÖظ´µÄ ID£¬Ôò·µ»Ø true
        if (count > 0) {
            return true;
        }
    }

    // Èç¹ûûÓÐÖظ´£¬·µ»Ø false
    return false;
}

// ÅжÏÃû³ÆÊÇ·ñÖظ´
bool SECSRuntimeManager::isNameDuplicate(const std::string& sName) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return false;
    }

    // ¶¨ÒåÒª¼ì²éµÄ±í
    std::vector<std::string> tables = { "SystemSV", "EqpSV", "SystemDV", "EqpDV", "SystemEC", "EqpEC" };

    // ±éÀú±í£¬¼ì²éÊÇ·ñÓÐÖظ´µÄ Name
    for (const auto& table : tables) {
        // ´´½¨ SQL ²éѯ
        std::string checkSQL = "SELECT COUNT(*) FROM " + table + " WHERE Name = '" + sName + "';";

        // Ö´Ðвéѯ
        auto results = m_pDB->fetchResults(checkSQL);
        int count = (!results.empty() && !results[0].empty()) ? std::stoi(results[0][0]) : 0;

        // Èç¹ûÕÒµ½ÁËÖظ´µÄ Name£¬Ôò·µ»Ø true
        if (count > 0) {
            return true;
        }
    }

    // Èç¹ûûÓÐÖظ´£¬·µ»Ø false
    return false;
}

// ÉèÖÃÊý¾Ý¿âÁ¬½Ó
void SECSRuntimeManager::setDatabase(BL::Database* db) {
    std::lock_guard<std::mutex> lock(m_mutex);
    m_pDB = db;
}

// ³õʼ»¯SECSÉèÖùÜÀí¿â
bool SECSRuntimeManager::initRuntimeSetting() {
    char path[MAX_PATH];
    GetModuleFileName(NULL, path, MAX_PATH);
    std::string exePath(path);
    std::string dbFileDir = exePath.substr(0, exePath.find_last_of("\\/")) + "\\DB";
    if (!CreateDirectory(dbFileDir.c_str(), NULL) && ERROR_ALREADY_EXISTS != GetLastError()) {
        throw std::runtime_error("Failed to create database directory.");
    }

    std::string dbFilePath = dbFileDir + "\\" + DATABASE_FILE;
    if (!m_pDB->connect(dbFilePath, true)) {
        return false;
    }

	// ³õʼ»¯ SystemSV ±í
	initSystemSVTable();

	// ³õʼ»¯ EqpSV ±í
	initEqpSVTable();

	// ³õʼ»¯ SystemDV ±í
    initSystemDVTable();

	// ³õʼ»¯ EqpDV ±í
	initEqpDVTable();

	// ³õʼ»¯ SystemEC ±í
	initSystemECTable();

	// ³õʼ»¯ EqpEC ±í
	initEqpECTable();

	// ³õʼ»¯ SystemECID ±í
	initSystemEventTable();

	// ³õʼ»¯ EqpECID ±í
	initEqpEventTable();

	// ³õʼ»¯ SystemEventLink ±í
	initEventLinkTable();

	// ³õʼ»¯ PPID ±í
	initPPIDTable();

	// ³õʼ»¯ RPTID ±í
	initRPTIDTable();

    return true;
}

// Ïú»ÙSECSÉèÖùÜÀí¿â
void SECSRuntimeManager::termRuntimeSetting() {
    if (m_pDB != nullptr) {
        m_pDB->disconnect();
    }
}

// ³õʼ»¯ SystemSV ±í
void SECSRuntimeManager::initSystemSVTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ SystemSV ±í£¨Èç¹û²»´æÔÚ£©
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS SystemSV ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "Length INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SystemID INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create SystemSV table.");
    }

    // Ô¤¶¨ÒåµÄ SV Êý¾Ý
    std::vector<std::tuple<int, std::string, std::string, int, std::string, std::string, int>> svData = {
        {1, "SYS_LICENSE_CODE", "ASCII", 0, "NULL", "License code (Formal; Evaluation; NoLicense)", 1},
        {2, "SYS_LICENSE_STATUS", "UINT_1", 0, "NULL", "License status(0:Unauthorized; 1:Authorized; 2:Evaluation; 3:Evaluation Expiring; 4:Trial; 5:Trial End)", 2},
        {3, "GEM_CLOCK", "ASCII", 0, "NULL", "System Clock", 3},
        {4, "SYS_SECS_COMM_MODE", "UINT_1", 0, "NULL", "SECS Communication Mode(0:HSMS Mode; 1:SECSI Mode)", 4},
        {5, "SYS_SECS_DRIVER_CONNECT_STATE", "UINT_1", 0, "NULL", "Initial SECS Driver Connect State(0:Stop; 1:Start)", 5}
    };

    for (const auto& entry : svData) {
        int nID, nLength, nSystemID;
        std::string sName, sDataType, sRemark, sUnit;
        std::tie(nID, sName, sDataType, nLength, sUnit, sRemark, nSystemID) = entry;

        // ¼ì²é Name ÊÇ·ñÒÑ´æÔÚ
        int count = getIntFromDB("SELECT COUNT(*) FROM SystemSV WHERE Name = '" + sName + "';");
        if (count == 0) {
            // ²åÈëÊý¾Ý
            std::string insertSQL = "INSERT INTO SystemSV (ID, Name, DataType, Length, Unit, Remark, SystemID) VALUES ("
                + std::to_string(nID) + ", '"
                + sName + "', '"
                + sDataType + "', "
                + (nLength > 0 ? std::to_string(nLength) : "NULL") + ", "
                + ((sUnit == "NULL") ? "NULL" : "'" + sUnit + "'") + ", '"
                + sRemark + "', "
                + std::to_string(nSystemID) + ");";

            if (!m_pDB->executeQuery(insertSQL)) {
                throw std::runtime_error("Failed to insert SystemSV data.");
            }
        }
    }
}

// Ìí¼Ó SystemSV Êý¾Ý
int SECSRuntimeManager::addSystemSV(int nID, const std::string& sName, const std::string& sDataType, int nLength, const std::string& sUnit, const std::string& sRemark, int nSystemID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    if (isIDDuplicate(nID)) {
		return 2;
    }

    if (isNameDuplicate(sName)) {
        return 3;
    }

    // Èç¹û Unit ÊÇ "NULL" ×Ö·û´®»òÕßΪ¿Õ£¬Ôò²åÈë NULL Öµ
    std::string insertSQL = "INSERT INTO SystemSV (ID, Name, DataType, Length, Unit, Remark, SystemID) VALUES ("
        + std::to_string(nID) + ", '"
        + sName + "', '"
        + sDataType + "', "
        + (nLength > 0 ? std::to_string(nLength) : "NULL") + ", "
        + ((sUnit == "NULL" || sUnit.empty()) ? "NULL" : "'" + sUnit + "'") + ", '"
        + sRemark + "', "
        + std::to_string(nSystemID) + ");";

    if (!m_pDB->executeQuery(insertSQL)) {
        return 4;
    }

    return 0;
}

// »ñÈ¡Ö¸¶¨ ID µÄ SystemSV Êý¾Ý
std::vector<std::vector<std::string>> SECSRuntimeManager::getSystemSVByID(int nID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return {};
    }

    std::string query = "SELECT * FROM SystemSV WHERE ID = " + std::to_string(nID) + ";";
    return m_pDB->fetchResults(query);
}

// »ñÈ¡ËùÓÐ SystemSV Êý¾Ý
std::vector<std::vector<std::string>> SECSRuntimeManager::getAllSystemSV() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return {};
    }

    std::string query = "SELECT * FROM SystemSV;";
    return m_pDB->fetchResults(query);
}

// ¸üÐÂÖ¸¶¨ ID µÄ SystemSV Êý¾Ý
int SECSRuntimeManager::updateIDSystemSV(int nID, int sNewID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²éÊÇ·ñ´æÔڸà ID
    if (!isIDDuplicate(nID)) {
        return 2;
    }

    if (isIDDuplicate(sNewID)) {
        return 3;
    }

    // ¹¹½¨¸üÐ嵀 SQL Óï¾ä
    std::string updateSQL = "UPDATE SystemSV SET ID = " + std::to_string(sNewID) + " WHERE ID = " + std::to_string(nID) + ";";
    if (!m_pDB->executeQuery(updateSQL)) {
        return 4;
    }

    return 0;
}

// ¸üÐÂËùÓÐ SystemSV Êý¾Ý
int SECSRuntimeManager::updateAllSystemSV(int nID, int sNewID, const std::string& sName, const std::string& sDataType, int nLength, const std::string& sUnit, const std::string& sRemark, int nSystemID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²éÊÇ·ñ´æÔڸà ID
    if (!isIDDuplicate(nID)) {
        return 2;
    }

    // ¼ì²éÐ嵀 ID ÊÇ·ñÒÑ´æÔÚ£¬Èç¹ûÒÑ´æÔÚ£¬Ôò·µ»Ø´íÎó´úÂë 3¡£
    if (isIDDuplicate(sNewID)) {
        return 3;
    }

    // ¹¹½¨¸üÐ嵀 SQL Óï¾ä
    std::string updateSQL = "UPDATE SystemSV SET ";

    bool firstField = true;

    // Èç¹ûÐ嵀 ID ±»Ìṩ£¬¸üРID
    if (sNewID > 0) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "ID = " + std::to_string(sNewID);
        firstField = false;
    }

    // ¸üРName
    if (!sName.empty()) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "Name = '" + sName + "'";
        firstField = false;
    }

    // ¸üРDataType
    if (!sDataType.empty()) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "DataType = '" + sDataType + "'";
        firstField = false;
    }

    // ¸üРLength
    if (nLength > 0) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "Length = " + std::to_string(nLength);
        firstField = false;
    }

    // ¸üРUnit
    if (sUnit != "NULL" && !sUnit.empty()) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "Unit = '" + sUnit + "'";
        firstField = false;
    }
    else if (sUnit == "NULL") {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "Unit = NULL";
        firstField = false;
    }

    // ¸üРRemark
    if (!sRemark.empty()) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "Remark = '" + sRemark + "'";
        firstField = false;
    }

    // ¸üРSystemID
    if (nSystemID > 0) {
        if (!firstField) {
            updateSQL += ", ";
        }
        updateSQL += "SystemID = " + std::to_string(nSystemID);
    }

    // Ìí¼Ó WHERE ×Ó¾äÀ´Ö¸¶¨¸üÐÂÄĸö¼Ç¼
    updateSQL += " WHERE ID = " + std::to_string(nID) + ";";

    // Ö´ÐиüвÙ×÷
    if (!m_pDB->executeQuery(updateSQL)) {
		return 4;
    }

    return 0;
}

// ɾ³ýÖ¸¶¨ ID µÄ SystemSV Êý¾Ý
int SECSRuntimeManager::deleteSystemSVByID(int nID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²éÊÇ·ñ´æÔڸà ID
    if (!isIDDuplicate(nID)) {
        return 2;
    }

    // ¹¹½¨É¾³ýµÄ SQL Óï¾ä
    std::string deleteSQL = "DELETE FROM SystemSV WHERE ID = " + std::to_string(nID) + ";";
    if (!m_pDB->executeQuery(deleteSQL)) {
        return 3;
    }

    return 0;
}

int SECSRuntimeManager::deleteAllSystemSV() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¹¹½¨É¾³ýËùÓÐÊý¾ÝµÄ SQL Óï¾ä
    std::string deleteSQL = "DELETE FROM SystemSV;";
    if (!m_pDB->executeQuery(deleteSQL)) {
        return 2;
    }

    return 0; // ɾ³ý³É¹¦£¬·µ»Ø 0 ±íʾ²Ù×÷³É¹¦Íê³É¡£
}

// ³õʼ»¯ EqpSV ±í
void SECSRuntimeManager::initEqpSVTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ EqpSV ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS EqpSV ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "Length INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SeqNo INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create EqpSV table.");
    }
}

// Ìí¼Ó EqpSV Êý¾Ý
int SECSRuntimeManager::addEqpSV(int nID, const std::string& sName, const std::string& sDataType, int nLength, const std::string& sUnit, const std::string& sRemark, int nSeqNo) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    if (isIDDuplicate(nID)) {
        return 2;
    }

    if (isNameDuplicate(sName)) {
        return 3;
    }

    // ¹¹½¨ SQL ²åÈëÓï¾ä£¬²åÈëÊý¾Ýµ½ EqpSV ±íÖС£
    std::string insertSQL = "INSERT INTO EqpSV (ID, Name, DataType, Length, Unit, Remark, SeqNo) VALUES ("
        + std::to_string(nID) + ", '"
        + sName + "', '"
        + sDataType + "', "
        + ((nLength <= 0) ? "NULL" : std::to_string(nLength))+", "
        + ((sUnit == "NULL" || sUnit.empty()) ? "NULL" : "'" + sUnit + "'")+", '"
        + sRemark + "', "
        + std::to_string(nSeqNo) + ");";

    // Ö´ÐвåÈë²Ù×÷£¬Èôʧ°ÜÔòÅ׳öÒì³£¡£
    if (!m_pDB->executeQuery(insertSQL)) {
        return 4;
    }

    return 0; // ²åÈë³É¹¦£¬·µ»Ø 0 ±íʾ²Ù×÷³É¹¦Íê³É¡£
}

// ²éÕÒÖ¸¶¨ ID µÄ EqpSV Êý¾Ý
std::vector<std::vector<std::string>> SECSRuntimeManager::getEqpSVByID(int nID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return {};
    }

    // ²éѯ SQL Óï¾ä
    std::string querySQL = "SELECT * FROM EqpSV WHERE ID = " + std::to_string(nID) + ";";
    return m_pDB->fetchResults(querySQL); // Ö±½Ó·µ»Ø²éѯ½á¹û
}

// ²éÕÒËùÓÐ EqpSV Êý¾Ý
std::vector<std::vector<std::string>> SECSRuntimeManager::getAllEqpSV() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return {};
    }

    // ²éѯ SQL Óï¾ä£¬»ñÈ¡ËùÓÐÊý¾Ý
    std::string querySQL = "SELECT * FROM EqpSV;";
    return m_pDB->fetchResults(querySQL); // Ö±½Ó·µ»Ø²éѯ½á¹û
}

// ¸üÐÂÖ¸¶¨ ID µÄ EqpSV Êý¾Ý
int SECSRuntimeManager::updateEqpSV(int nID, int nNewID, const std::string& sName, const std::string& sDataType, int nLength, const std::string& sUnit, const std::string& sRemark, int nSeqNo) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1; // Èç¹ûÊý¾Ý¿âδÁ¬½Ó£¬·µ»Ø 1
    }

    // ¼ì²éÊÇ·ñ´æÔڸà ID
    if (!isIDDuplicate(nID)) {
        return 2; // Èç¹û ID ²»´æÔÚ£¬·µ»Ø´íÎó´úÂë 2
    }

    // ¼ì²éÐ嵀 ID ÊÇ·ñÒÑ´æÔÚ
    if (isIDDuplicate(nNewID) && nNewID != nID) {
        return 3; // Èç¹ûРID ÒÑ´æÔÚ£¬·µ»Ø´íÎó´úÂë 3
    }

    // ¹¹½¨¸üРSQL Óï¾ä
    std::string updateSQL = "UPDATE EqpSV SET ";

    bool firstField = true;

    // Èç¹ûÐ嵀 ID ±»Ìṩ£¬¸üРID
    if (nNewID > 0 && nNewID != nID) {
        updateSQL += "ID = " + std::to_string(nNewID);
        firstField = false;
    }

    // ¸üРName
    if (!sName.empty()) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "Name = '" + sName + "'";
        firstField = false;
    }

    // ¸üРDataType
    if (!sDataType.empty()) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "DataType = '" + sDataType + "'";
        firstField = false;
    }

    // ¸üРLength
    if (nLength > 0) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "Length = " + std::to_string(nLength);
        firstField = false;
    }

    // ¸üРUnit
    if (sUnit != "NULL" && !sUnit.empty()) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "Unit = '" + sUnit + "'";
        firstField = false;
    }
    else if (sUnit == "NULL") {
        if (!firstField) updateSQL += ", ";
        updateSQL += "Unit = NULL";
        firstField = false;
    }

    // ¸üРRemark
    if (!sRemark.empty()) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "Remark = '" + sRemark + "'";
        firstField = false;
    }

    // ¸üРSeqNo
    if (nSeqNo > 0) {
        if (!firstField) updateSQL += ", ";
        updateSQL += "SeqNo = " + std::to_string(nSeqNo);
    }

    // Ìí¼Ó WHERE ×Ӿ䣬¸üÐÂÖ¸¶¨ ID µÄÊý¾Ý
    updateSQL += " WHERE ID = " + std::to_string(nID) + ";";

    // Ö´ÐиüвÙ×÷
    if (!m_pDB->executeQuery(updateSQL)) {
        return 4; // Èç¹û¸üÐÂʧ°Ü£¬·µ»Ø´íÎó´úÂë 4
    }

    return 0; // ¸üгɹ¦£¬·µ»Ø 0
}

// ɾ³ýÖ¸¶¨ ID µÄ EqpSV Êý¾Ý
int SECSRuntimeManager::deleteEqpSVByID(int nID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²éÊÇ·ñ´æÔڸà ID
    if (!isIDDuplicate(nID)) {
        return 2; // Èç¹û ID ²»´æÔÚ£¬·µ»Ø´íÎó´úÂë 2
    }

    // ¹¹½¨É¾³ý SQL Óï¾ä
    std::string deleteSQL = "DELETE FROM EqpSV WHERE ID = " + std::to_string(nID) + ";";
    if (!m_pDB->executeQuery(deleteSQL)) {
        return 3; // Èç¹ûɾ³ýʧ°Ü£¬·µ»Ø´íÎó´úÂë 3
    }

    return 0; // ɾ³ý³É¹¦£¬·µ»Ø 0
}

// ɾ³ý EqpSV ±íÖеÄËùÓÐÊý¾Ý
int SECSRuntimeManager::deleteAllEqpSV() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¹¹½¨É¾³ýËùÓÐÊý¾ÝµÄ SQL Óï¾ä
    std::string deleteSQL = "DELETE FROM EqpSV;";
    if (!m_pDB->executeQuery(deleteSQL)) {
        return 2; // Èç¹ûɾ³ýʧ°Ü£¬·µ»Ø´íÎó´úÂë 2
    }

    return 0; // ɾ³ý³É¹¦£¬·µ»Ø 0
}

// ³õʼ»¯ SystemDV ±í
void SECSRuntimeManager::initSystemDVTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ SystemDV ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS SystemDV ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "Length INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SystemID INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create SystemDV table.");
    }
}

// ³õʼ»¯ EqpDV ±í
void SECSRuntimeManager::initEqpDVTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ EqpDV ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS EqpDV ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "Length INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SeqNo INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create EqpDV table.");
    }
}

// ³õʼ»¯ SystemEC ±í
void SECSRuntimeManager::initSystemECTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ SystemEC ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS SystemEC ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "MinValue INTEGER NULL, "
        "MaxValue INTEGER NULL, "
        "DefaultVal INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SystemID INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create SystemEC table.");
    }
}

// ³õʼ»¯ EqpEC ±í
void SECSRuntimeManager::initEqpECTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ EqpEC ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS EqpEC ("
        "ID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "DataType TEXT NOT NULL, "
        "MinValue INTEGER NULL, "
        "MaxValue INTEGER NULL, "
        "DefaultValue INTEGER NULL, "
        "Unit TEXT NULL, "
        "Remark TEXT, "
        "SeqNo INTEGER NOT NULL, "
        "Length INTEGER NOT NULL, "
        "CanUpdateByHost INTEGER NOT NULL);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create EqpEC table.");
    }
}

// ³õʼ»¯ SystemEvent ±í
void SECSRuntimeManager::initSystemEventTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ SystemEvent ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS SystemEvent ("
        "CEID INTEGER PRIMARY KEY, "
        "Name TEXT UNIQUE NOT NULL, "
        "Remark TEXT, "
        "SystemID INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create SystemEvent table.");
    }
}

// ³õʼ»¯ EqpEvent ±í
void SECSRuntimeManager::initEqpEventTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ EqpEvent ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS EqpEvent ("
        "CEID INTEGER PRIMARY KEY AUTOINCREMENT, "
        "Name TEXT UNIQUE NOT NULL, "
        "Remark TEXT, "
        "BitNo INTEGER);";

    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create EqpEvent table.");
    }
}

// ³õʼ»¯ EventLink ±í
void SECSRuntimeManager::initEventLinkTable() {
	std::lock_guard<std::mutex> lock(m_mutex);
	if (m_pDB == nullptr) {
		throw std::runtime_error("Database not connected.");
	}

    // ´´½¨ EventLink ±í
    std::string createEventLinkSQL =
        "CREATE TABLE IF NOT EXISTS EventLink ("
        "CEID INTEGER, "
        "RPTID INTEGER, "
        "FOREIGN KEY (CEID) REFERENCES EqpEvent(CEID));";

    if (!m_pDB->executeQuery(createEventLinkSQL)) {
        throw std::runtime_error("Failed to create EventLink table.");
    }
}

// ³õʼ»¯ PPID ±í
void SECSRuntimeManager::initPPIDTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ EqpPPID ±í
    std::string createTableSQL =
        "CREATE TABLE IF NOT EXISTS EqpPPID ("
        "BitNo INTEGER PRIMARY KEY AUTOINCREMENT, "
        "PPID INTEGER NULL);";
    if (!m_pDB->executeQuery(createTableSQL)) {
        throw std::runtime_error("Failed to create EqpPPID table.");
    }

    // Ïȼì²é±íÊÇ·ñΪ¿Õ
    int nCount = getIntFromDB("SELECT COUNT(*) FROM EqpPPID;");
    if (nCount == 0) {
        // ²åÈë³õʼÊý¾Ý£¨512 ÐУ©
        for (int nBitNo = 0; nBitNo < 512; ++nBitNo) {
            std::string insertSQL = "INSERT INTO EqpPPID (BitNo) VALUES (" + std::to_string(nBitNo) + ");";
            if (!m_pDB->executeQuery(insertSQL)) {
                throw std::runtime_error("Failed to insert data into EqpPPID table.");
            }
        }
    }
}

// ³õʼ»¯ RPTID ±í
void SECSRuntimeManager::initRPTIDTable() {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ´´½¨ Report ±í
    std::string createReportTableSQL =
        "CREATE TABLE IF NOT EXISTS Report ("
        "RPTID INTEGER PRIMARY KEY);";

    // ´´½¨ ReportVIDs ±í£¬±£Ö¤Í¬Ò»¸ö RPTID Ï嵀 VID Ψһ
    std::string createReportVIDsTableSQL =
        "CREATE TABLE IF NOT EXISTS ReportVIDs ("
        "RPTID INTEGER NOT NULL, "
        "VID INTEGER NOT NULL, "
        "FOREIGN KEY (RPTID) REFERENCES Report(RPTID), "
        "UNIQUE(RPTID, VID));";

    // Ö´Ðд´½¨±í²Ù×÷
    if (!m_pDB->executeQuery(createReportTableSQL)) {
        throw std::runtime_error("Failed to create Report table.");
    }
    if (!m_pDB->executeQuery(createReportVIDsTableSQL)) {
        throw std::runtime_error("Failed to create ReportVIDs table.");
    }
}

// Ìí¼Ó RPTID Êý¾Ý
int SECSRuntimeManager::addRPTIDVID(int nRPTID, int nVID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²é RPTID ºÍ VID µÄ×éºÏÊÇ·ñÒѾ­´æÔÚ
    std::string checkExistSQL = "SELECT COUNT(*) FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nVID) + ";";
    int count = getIntFromDB(checkExistSQL);
    if (count > 0) {
        return 2;  // Èç¹û¹ØϵÒÑ´æÔÚ£¬·µ»Ø´íÎó´úÂë 2
    }

    // ¼ì²é Report ±íÖÐÊÇ·ñ´æÔڸà RPTID
    std::string checkReportExistSQL = "SELECT COUNT(*) FROM Report WHERE RPTID = " + std::to_string(nRPTID) + ";";
    int reportCount = getIntFromDB(checkReportExistSQL);

    if (reportCount == 0) {
        // Èç¹û RPTID ²»´æÔÚ£¬²åÈë RPTID
        std::string insertReportSQL = "INSERT INTO Report (RPTID) VALUES (" + std::to_string(nRPTID) + ");";
        if (!m_pDB->executeQuery(insertReportSQL)) {
            return 3;  // ²åÈëʧ°Ü£¬·µ»Ø´íÎó´úÂë 3
        }
    }

    // ¹¹Ôì²åÈë SQL Óï¾ä
    std::string insertSQL = "INSERT INTO ReportVIDs (RPTID, VID) VALUES ("
        + std::to_string(nRPTID) + ", "
        + std::to_string(nVID) + ");";

    // Ö´ÐвåÈë²Ù×÷
    if (!m_pDB->executeQuery(insertSQL)) {
        return 4;  // ²åÈëʧ°Ü£¬·µ»Ø´íÎó´úÂë 4
    }

    return 0;  // ²åÈë³É¹¦£¬·µ»Ø 0
}

// »ñÈ¡Ö¸¶¨ RPTID µÄËùÓÐ VID
int SECSRuntimeManager::addRPTIDVIDs(int nRPTID, const std::vector<int>& vecVID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;
    }

    // ¼ì²é Report ±íÖÐÊÇ·ñ´æÔڸà RPTID
    std::string checkReportExistSQL = "SELECT COUNT(*) FROM Report WHERE RPTID = " + std::to_string(nRPTID) + ";";
    int reportCount = getIntFromDB(checkReportExistSQL);

    if (reportCount == 0) {
        // Èç¹û RPTID ²»´æÔÚ£¬²åÈë RPTID
        std::string insertReportSQL = "INSERT INTO Report (RPTID) VALUES (" + std::to_string(nRPTID) + ");";
        if (!m_pDB->executeQuery(insertReportSQL)) {
            return 2;  // ²åÈëʧ°Ü£¬·µ»Ø´íÎó´úÂë 3
        }
    }

    // ÅúÁ¿²åÈë VID ºÍ RPTID ¹Øϵ
    for (int nVID : vecVID) {
        // ¼ì²éµ±Ç° RPTID ºÍ VID µÄ×éºÏÊÇ·ñÒѾ­´æÔÚ
        std::string checkExistSQL = "SELECT COUNT(*) FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nVID) + ";";
        int count = getIntFromDB(checkExistSQL);
        if (count == 0) {
            // Èç¹û¹Øϵ²»´æÔÚ£¬²åÈë
            std::string insertSQL = "INSERT INTO ReportVIDs (RPTID, VID) VALUES ("
                + std::to_string(nRPTID) + ", "
                + std::to_string(nVID) + ");";

            if (!m_pDB->executeQuery(insertSQL)) {
                return 3;  // ²åÈëʧ°Ü£¬·µ»Ø´íÎó´úÂë 3
            }
        }
    }

    return 0;  // ²åÈë³É¹¦£¬·µ»Ø 0
}

// »ñÈ¡Ö¸¶¨ RPTID µÄËùÓÐ VID
int SECSRuntimeManager::deleteRPTIDVID(int nRPTID, int nVID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;  // Êý¾Ý¿âδÁ¬½Ó
    }

    // ¼ì²é ReportVIDs ±íÖÐÊÇ·ñ´æÔڸà RPTID ºÍ VID ×éºÏ
    std::string checkExistSQL = "SELECT COUNT(*) FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nVID) + ";";
    int count = getIntFromDB(checkExistSQL);
    if (count == 0) {
        return 2;  // ¼Ç¼²»´æÔÚ
    }

    // ɾ³ý ReportVIDs ±íÖÐÖ¸¶¨ RPTID ºÍ VID µÄ¹Øϵ
    std::string deleteSQL = "DELETE FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nVID) + ";";
    if (!m_pDB->executeQuery(deleteSQL)) {
        return 3;  // ɾ³ýʧ°Ü
    }

    return 0;  // ɾ³ý³É¹¦
}

// ɾ³ýÖ¸¶¨ RPTID µÄËùÓÐ VID6
int SECSRuntimeManager::deleteRPTID(int nRPTID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;  // Êý¾Ý¿âδÁ¬½Ó
    }

    // ɾ³ý ReportVIDs ±íÖÐËùÓÐÓë¸Ã RPTID Ïà¹ØµÄ¼Ç¼
    std::string deleteReportVIDsSQL = "DELETE FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + ";";
    if (!m_pDB->executeQuery(deleteReportVIDsSQL)) {
        return 2;  // ɾ³ýʧ°Ü
    }

    // ¿ÉÑ¡ÔñÊÇ·ñɾ³ý Report ±íÖÐµÄ RPTID£¬ÒÔÏÂÊÇɾ³ý Report ±íÖÐµÄ RPTID µÄ SQL
    std::string deleteReportSQL = "DELETE FROM Report WHERE RPTID = " + std::to_string(nRPTID) + ";";
    if (!m_pDB->executeQuery(deleteReportSQL)) {
        return 3;  // ɾ³ýʧ°Ü
    }

    return 0;  // ɾ³ý³É¹¦
}

// ¸üÐÂÖ¸¶¨ RPTID µÄ VID
int SECSRuntimeManager::updateRPTIDVID(int nRPTID, int nOldVID, int nNewVID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;  // Êý¾Ý¿âδÁ¬½Ó
    }

    // ¼ì²éÖ¸¶¨µÄ RPTID ºÍ OldVID ÊÇ·ñ´æÔÚ
    std::string checkExistSQL = "SELECT COUNT(*) FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nOldVID) + ";";
    int count = getIntFromDB(checkExistSQL);
    if (count == 0) {
        return 2;  // ¼Ç¼²»´æÔÚ£¬²»ÄܸüÐÂ
    }

    // ¼ì²éÐ嵀 VID ÊÇ·ñÒѾ­´æÔÚÓÚÏàͬµÄ RPTID Ï£¬·ÀÖ¹Öظ´
    std::string checkNewVIDExistSQL = "SELECT COUNT(*) FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nNewVID) + ";";
    int newVIDCount = getIntFromDB(checkNewVIDExistSQL);
    if (newVIDCount > 0) {
        return 3;  // Ð嵀 VID ÒѾ­´æÔÚ£¬²»ÄÜÖظ´
    }

    // Ö´ÐиüвÙ×÷£¬Ìæ»» OldVID Ϊ NewVID
    std::string updateSQL = "UPDATE ReportVIDs SET VID = " + std::to_string(nNewVID) + " WHERE RPTID = " + std::to_string(nRPTID) + " AND VID = " + std::to_string(nOldVID) + ";";
    if (!m_pDB->executeQuery(updateSQL)) {
        return 4;  // ¸üÐÂʧ°Ü
    }

    return 0;  // ¸üгɹ¦
}

// ¸üÐÂÖ¸¶¨ RPTID µÄËùÓÐ VID
int SECSRuntimeManager::updateRPTIDVIDs(int nRPTID, const std::vector<int>& vecVID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    if (m_pDB == nullptr) {
        return 1;  // Êý¾Ý¿âδÁ¬½Ó
    }

    // 1. ÏÈɾ³ý RPTID ÏÂËùÓÐµÄ VID ¹ØÁª¼Ç¼
    int deleteResult = deleteRPTID(nRPTID);
    if (deleteResult != 0) {
        return deleteResult;  // ɾ³ýʧ°Ü£¬·µ»Øɾ³ýµÄ´íÎóÂë
    }

    // 2. Ìí¼ÓÐ嵀 VID ¹ØÁª¼Ç¼
    int addResult = addRPTIDVIDs(nRPTID, vecVID);
    if (addResult != 0) {
        return 3;  // ²åÈëʧ°Ü
    }

    return 0;  // ɾ³ý²¢Ìí¼Ó³É¹¦
}

// »ñÈ¡Ö¸¶¨ RPTID µÄËùÓÐ VID
std::vector<int> SECSRuntimeManager::getVIDsByRPTID(int nRPTID) {
    std::lock_guard<std::mutex> lock(m_mutex);
    std::vector<int> vecVIDs;

    if (m_pDB == nullptr) {
        return {};
    }

    // ¹¹½¨²éѯ SQL£¬²éѯָ¶¨ RPTID ϵÄËùÓÐ VID
    std::string querySQL = "SELECT VID FROM ReportVIDs WHERE RPTID = " + std::to_string(nRPTID) + ";";

    // Ö´Ðвéѯ
    std::vector<std::vector<std::string>> results = m_pDB->fetchResults(querySQL);

    // ´¦Àí²éѯ½á¹û²¢Ìî³äµ½ vecVIDs ÖÐ
    for (const auto& row : results) {
        if (!row.empty()) {
            int nVID = std::stoi(row[0]);  // ½«×Ö·û´®ÀàÐ굀 VID ת»»Îª int
            vecVIDs.push_back(nVID);        // ½« VID ¼ÓÈë½á¹ûÏòÁ¿
        }
    }

    return vecVIDs;
}

// »ñÈ¡ËùÓÐ RPTID
std::vector<int> SECSRuntimeManager::getAllRPTIDs() {
    std::lock_guard<std::mutex> lock(m_mutex);
    std::vector<int> vecRPTIDs;

    if (m_pDB == nullptr) {
        throw std::runtime_error("Database not connected.");
    }

    // ¹¹½¨²éѯ SQL£¬²éѯ Report ±íÖеÄËùÓÐ RPTID
    std::string querySQL = "SELECT RPTID FROM Report;";

    // Ö´Ðвéѯ
    std::vector<std::vector<std::string>> results = m_pDB->fetchResults(querySQL);

    // ´¦Àí²éѯ½á¹û²¢Ìî³äµ½ vecRPTIDs ÖÐ
    for (const auto& row : results) {
        if (!row.empty()) {
            int nRPTID = std::stoi(row[0]);  // ½«×Ö·û´®ÀàÐ굀 RPTID ת»»Îª int
            vecRPTIDs.push_back(nRPTID);     // ½« RPTID ¼ÓÈë½á¹ûÏòÁ¿
        }
    }

    return vecRPTIDs;
}