eUi/include/eui_tools/sql/qSqlBase.h

#pragma once

#include <qfile.h>
#include <string>
#include <map>
#include <vector>
#include <unordered_map>
#include <mutex>
#include <qdatetime.h>
#include <memory>
#include <qtextstream.h>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QVariant>
#include <QDebug>
#include <QString>
#include <QList>
#include <QMap>
#include <QStringList>
#include <QFileInfo>
#include <qcoreapplication.h>
#include <qdir.h>
#include <qtimer.h>
#include <chrono>
#include <qobject.h>
#include <iostream>
#include <fstream>
#include <qelapsedtimer.h>
#include <QQueue>
#include <quuid.h>
#include <qthread.h>
#include <qmutex.h>
#include <QJsonObject>
#include <QJsonArray>
#include <QJsonDocument>

// 第二层：通用 SQLite 连接。一个实例只应在创建它的线程中使用。
class sqliteConnection {
public:
    static constexpr int maximumColumnCount = 1000;

    explicit sqliteConnection(const QString& dbPath)
        : dbPath(dbPath) {
        connect();
    }
    ~sqliteConnection() { disconnect(); };

    sqliteConnection(const sqliteConnection&) = delete;
    sqliteConnection& operator=(const sqliteConnection&) = delete;

    bool isOpen() const {
        return db.isOpen();
    }

    // 仅供 Qt 的只读模型在同一线程中使用，业务代码应优先使用 Repository 方法。
    QSqlDatabase connection() const {
        return db;
    }

    // 添加内部无锁版本，避免嵌套锁问题
    bool execute_unsafe(const QString& queryStr, const QVariantList& args = {}) {
        QSqlQuery query(db);
        if (!query.prepare(queryStr)) {
            qCritical() << "SQL prepare error:" << query.lastError().text();
            qCritical() << "Query:" << queryStr;
            return false;
        }

        for (int i = 0; i < args.size(); ++i) {
            query.bindValue(i, args[i]);
        }

        if (!query.exec()) {
            qCritical() << "SQL error:" << query.lastError().text();
            qCritical() << "Query:" << queryStr;
            return false;
        }
        return true;
    }

    QList<QMap<QString, QVariant>> query_unsafe(const QString& queryStr, const QVariantList& args = {}) {
        QList<QMap<QString, QVariant>> results;
        QSqlQuery query(db);
        if (!query.prepare(queryStr)) {
            qCritical() << "SQL prepare error:" << query.lastError().text();
            qCritical() << "Query:" << queryStr;
            return results;
        }

        for (int i = 0; i < args.size(); ++i) {
            query.bindValue(i, args[i]);
        }

        if (!query.exec()) {
            qCritical() << "SQL error:" << query.lastError().text();
            qCritical() << "Query:" << queryStr;
            return results;
        }

        // 先移动到第一行，确保 SQLite 驱动正确填充 record 信息（包括列数）
        bool hasData = query.next();
        if (hasData) {
            // 在首次 next() 之后获取 record，此时 SQLite 驱动已填充完整列信息
            QSqlRecord record = query.record();
            int columnCount = record.count();

            // 处理第一行
            QMap<QString, QVariant> row;
            for (int i = 0; i < columnCount; ++i) {
                row.insert(record.fieldName(i), query.value(i));
            }
            results.append(row);

            // 处理剩余行（使用缓存的 record，避免重复创建临时对象）
            while (query.next()) {
                QMap<QString, QVariant> row;
                for (int i = 0; i < columnCount; ++i) {
                    row.insert(record.fieldName(i), query.value(i));
                }
                results.append(row);
            }
        }

        return results;
    }

    // 公有方法保持加锁保护
    bool execute(const QString& queryStr, const QVariantList& args = {}) {
        QMutexLocker lock(&db_mutex);
        return execute_unsafe(queryStr, args);
    }

    QList<QMap<QString, QVariant>> query(const QString& queryStr, const QVariantList& args = {}) {
        QMutexLocker lock(&db_mutex);
        return query_unsafe(queryStr, args);
    }

    bool createTable(const QString& tableName, const QList<std::pair<QString, QString>>& columns) {
        QMutexLocker lock(&db_mutex);
        if (!isUsableIdentifier(tableName) || columns.empty()) {
            qDebug() << "Create table failed: invalid table name or empty columns.";
            return false;
        }
        if (columns.size() > maximumColumnCount) {
            qWarning() << "Create table failed: column count exceeds the configured limit:"
                       << maximumColumnCount;
            return false;
        }

        QStringList columnDefinitions;
        for (const auto& pair : columns) {
            if (!isUsableIdentifier(pair.first) || pair.second.isEmpty()) {
                qWarning() << "Create table failed: invalid column definition.";
                return false;
            }
            columnDefinitions << QString("%1 %2").arg(quoteIdentifier(pair.first), pair.second);
        }

        QString sql = QString("CREATE TABLE IF NOT EXISTS %1 (%2)")
            .arg(quoteIdentifier(tableName))
            .arg(columnDefinitions.join(", "));

        return execute_unsafe(sql);
    }

    std::vector<QString> getTables() {
        QMutexLocker lock(&db_mutex);
        std::vector<QString> tables;
        QString sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";

        auto result = query_unsafe(sql);
        for (const auto& row : result) {
            tables.push_back(row.value("name").toString());
        }
        return tables;
    }

    bool dropTable(const QString& tableName) {
        QMutexLocker lock(&db_mutex);
        QString sql = QString("DROP TABLE IF EXISTS %1").arg(quoteIdentifier(tableName));
        QSqlQuery query(db);
        if (!query.exec(sql)) {
            qCritical() << "SQL error:" << query.lastError().text();
            qCritical() << "Query:" << sql;
            return false;
        }
        return true;
    }

    bool addColumn(const QString& tableName, const QString& columnName, const QString& columnType) {
        QMutexLocker lock(&db_mutex);
        if (!isUsableIdentifier(tableName) || !isUsableIdentifier(columnName) || columnType.isEmpty()) {
            qDebug() << "Add column failed: invalid table name, column name, or empty column type.";
            return false;
        }
        if (tableColumns(tableName).size() >= maximumColumnCount) {
            qWarning() << "Add column failed: column count exceeds the configured limit:"
                       << maximumColumnCount;
            return false;
        }

        QString sql = QString("ALTER TABLE %1 ADD COLUMN %2 %3")
            .arg(quoteIdentifier(tableName), quoteIdentifier(columnName), columnType);

        if (!execute_unsafe(sql)) {
            qDebug() << "Add column failed";
            return false;
        }

        qDebug() << "Column" << columnName << "added to table" << tableName;
        return true;
    }

    bool tableExists(const QString& tableName) {
        QMutexLocker lock(&db_mutex);
        QString sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?;";

        QList<QMap<QString, QVariant>> result = query_unsafe(sql, { tableName });
        return !result.isEmpty();
    }

    const QString& databasePath() const {
        return dbPath;
    }

    QList<QString> tableColumns(const QString& tableName) {
        QMutexLocker lock(&db_mutex);
        QList<QString> columns;
        if (!isUsableIdentifier(tableName)) {
            qWarning() << "Read table columns failed: invalid table name.";
            return columns;
        }

        const auto result = query_unsafe(QString("PRAGMA table_info(%1)").arg(quoteIdentifier(tableName)));
        for (const auto& row : result) {
            columns.append(row.value("name").toString());
        }
        return columns;
    }

    // 添加批量操作支持，减少锁竞争
    bool batchInsert(const QString& tableName, const std::vector<std::map<QString, QVariant>>& dataList) {
        if (dataList.empty()) {
            qDebug() << "Batch insert failed: data is empty.";
            return false;
        }

        QMutexLocker lock(&db_mutex);

        // 开始事务
        if (!execute_unsafe("BEGIN TRANSACTION;")) {
            return false;
        }

        bool allSuccess = true;
        for (const auto& data : dataList) {
            if (!_insert_unsafe(tableName, data)) {
                allSuccess = false;
                break;
            }
        }

        if (allSuccess) {
            execute_unsafe("COMMIT;");
        } else {
            execute_unsafe("ROLLBACK;");
        }

        return allSuccess;
    }

    bool _insert(const QString& tableName, const std::map<QString, QVariant>& data) {
        QMutexLocker lock(&db_mutex);
        return _insert_unsafe(tableName, data);
    }

private:
    bool _insert_unsafe(const QString& tableName, const std::map<QString, QVariant>& data) {
        if (data.empty()) {
            qDebug() << "Insert failed: data is empty.";
            return false;
        }

        if (!isUsableIdentifier(tableName)) {
            qDebug() << "Insert failed: invalid table name.";
            return false;
        }

        QStringList columns, placeholders;
        QVariantList values;

        for (const auto& pair : data) {
            if (!isUsableIdentifier(pair.first)) {
                qWarning() << "Insert failed: invalid column name.";
                return false;
            }
            columns << quoteIdentifier(pair.first);
            placeholders << "?";
            values << pair.second;
        }

        QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
            .arg(quoteIdentifier(tableName))
            .arg(columns.join(", "))
            .arg(placeholders.join(", "));

        return execute_unsafe(sql, values);
    }

public:
    bool _update(const QString& tableName,
        const std::map<QString, QVariant>& data,
        const std::map<QString, QVariant>& conditions) {
        QMutexLocker lock(&db_mutex);
        return _update_unsafe(tableName, data, conditions);
    }

private:
    bool _update_unsafe(const QString& tableName,
        const std::map<QString, QVariant>& data,
        const std::map<QString, QVariant>& conditions) {
        if (!isUsableIdentifier(tableName) || data.empty() || conditions.empty()) {
            qDebug() << "Update failed: invalid table name, data, or conditions.";
            return false;
        }

        QStringList setClauses, whereClauses;
        QVariantList values;

        for (const auto& pair : data) {
            if (!isUsableIdentifier(pair.first)) {
                qWarning() << "Update failed: invalid data column name.";
                return false;
            }
            setClauses << QString("%1 = ?").arg(quoteIdentifier(pair.first));
            values << pair.second;
        }

        for (const auto& pair : conditions) {
            if (!isUsableIdentifier(pair.first)) {
                qWarning() << "Update failed: invalid condition column name.";
                return false;
            }
            whereClauses << QString("%1 = ?").arg(quoteIdentifier(pair.first));
            values << pair.second;
        }

        QString sql = QString("UPDATE %1 SET %2 WHERE %3")
            .arg(quoteIdentifier(tableName))
            .arg(setClauses.join(", "))
            .arg(whereClauses.join(" AND "));

        return execute_unsafe(sql, values);
    }

public:
    bool _delete(const QString& tableName, const std::map<QString, QVariant>& conditions) {
        QMutexLocker lock(&db_mutex);
        return _delete_unsafe(tableName, conditions);
    }

private:
    bool _delete_unsafe(const QString& tableName, const std::map<QString, QVariant>& conditions) {
        if (!isUsableIdentifier(tableName) || conditions.empty()) {
            qDebug() << "Delete failed: invalid table name or empty conditions.";
            return false;
        }

        QStringList whereClauses;
        QVariantList values;

        for (const auto& pair : conditions) {
            if (!isUsableIdentifier(pair.first)) {
                qWarning() << "Delete failed: invalid condition column name.";
                return false;
            }
            whereClauses << QString("%1 = ?").arg(quoteIdentifier(pair.first));
            values << pair.second;
        }

        QString sql = QString("DELETE FROM %1 WHERE %2")
            .arg(quoteIdentifier(tableName))
            .arg(whereClauses.join(" AND "));

        return execute_unsafe(sql, values);
    }

public:
    std::vector<std::map<QString, QVariant>> _search(const QString& tableName,
        const QStringList& columns,
        const std::map<QString, QVariant>& conditions = {}) {
        QMutexLocker lock(&db_mutex);
        return _search_unsafe(tableName, columns, conditions);
    }

private:
    std::vector<std::map<QString, QVariant>> _search_unsafe(const QString& tableName,
        const QStringList& columns,
        const std::map<QString, QVariant>& conditions = {}) {

        if (!isUsableIdentifier(tableName)) {
            qWarning() << "Search failed: invalid table name.";
            return {};
        }

        QStringList selectColumns;
        if (columns.isEmpty()) {
            // Avoid SELECT * after an ALTER TABLE. The SQLite driver can retain
            // stale result metadata for a wildcard query on a reused connection.
            const auto tableInfo = query_unsafe(
                QString("PRAGMA table_info(%1)").arg(quoteIdentifier(tableName)));
            for (const auto& row : tableInfo) {
                const QString columnName = row.value("name").toString();
                if (!isUsableIdentifier(columnName)) {
                    qWarning() << "Search failed: invalid table column name.";
                    return {};
                }
                selectColumns << quoteIdentifier(columnName);
            }
            if (selectColumns.isEmpty()) {
                qWarning() << "Search failed: table has no columns:" << tableName;
                return {};
            }
        } else {
            for (const auto& column : columns) {
                if (!isUsableIdentifier(column)) {
                    qWarning() << "Search failed: invalid selected column name.";
                    return {};
                }
                selectColumns << quoteIdentifier(column);
            }
        }
        QString sql = QString("SELECT %1 FROM %2").arg(selectColumns.join(", "), quoteIdentifier(tableName));

        QVariantList values;
        if (!conditions.empty()) {
            QStringList whereClauses;
            for (const auto& pair : conditions) {
                if (!isUsableIdentifier(pair.first)) {
                    qWarning() << "Search failed: invalid condition column name.";
                    return {};
                }
                whereClauses << QString("%1 = ?").arg(quoteIdentifier(pair.first));
                values << pair.second;
            }
            sql += " WHERE " + whereClauses.join(" AND ");
        }

        auto result = query_unsafe(sql, values);
        std::vector<std::map<QString, QVariant>> results;

        for (const auto& row : result) {
            std::map<QString, QVariant> rowMap;
            for (auto it = row.begin(); it != row.end(); ++it) {
                rowMap[it.key()] = it.value();
            }
            results.push_back(rowMap);
        }

        return results;
    }

public:
    QList<QMap<QString, QVariant>> queryTabelData(const QString& tableName) {
        QMutexLocker lock(&db_mutex);
        QString sql = QString("SELECT * FROM %1").arg(quoteIdentifier(tableName));
        return query_unsafe(sql);
    }

    // 移除外部 mutex 参数，避免锁顺序问题
    bool exportTableToCSV(const QString& tableName, const QString& filePath) {
        QMutexLocker lock(&db_mutex);
        using namespace std::chrono;

        auto start = high_resolution_clock::now();
        QString sql = QString("SELECT * FROM %1").arg(quoteIdentifier(tableName));

        QSqlQuery query(db);
        if (!query.exec(sql)) {
            qCritical() << "Failed to query table" << tableName << ":" << query.lastError().text();
            return false;
        }

        if (!query.next()) {
            if (query.lastError().isValid()) {
                qCritical() << "Failed to read table" << tableName << ":" << query.lastError().text();
            }
            else {
                qDebug() << "No data found in table" << tableName;
            }
            return false;
        }

        const QSqlRecord record = query.record();
        if (record.isEmpty()) {
            qDebug() << "No data found in table" << tableName;
            return false;
        }

        QFile file(filePath);
        if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
            qCritical() << "Failed to open file" << filePath;
            return false;
        }

        bool writeSucceeded = false;
        {
            QTextStream out(&file);
            out.setCodec("UTF-8");

            QStringList columns;
            for (int i = 0; i < record.count(); ++i) {
                columns << record.fieldName(i);
            }

            QStringList escapedColumns;
            for (const auto& column : columns) {
                escapedColumns << csvEscape(column);
            }
            out << escapedColumns.join(",") << "\n";

            bool querySucceeded = true;
            do {
                QStringList values;
                for (int i = 0; i < record.count(); ++i) {
                    values << csvEscape(query.value(i).toString());
                }
                out << values.join(",") << "\n";
                if (out.status() != QTextStream::Ok) {
                    break;
                }
                if (!query.next()) {
                    querySucceeded = !query.lastError().isValid();
                    break;
                }
            } while (true);

            out.flush();
            writeSucceeded = querySucceeded
                && out.status() == QTextStream::Ok
                && file.flush()
                && file.error() == QFileDevice::NoError;
        }

        file.close();
        if (!writeSucceeded) {
            qCritical() << "Failed to write CSV file" << filePath;
            return false;
        }

        auto end = high_resolution_clock::now();
        auto duration = duration_cast<milliseconds>(end - start);
        qDebug() << filePath << "EXPORT CSV COST: " << duration.count() << "ms";
        return true;
    }

    bool setWALMode(bool enableWAL = true, int busyTimeoutMs = 3000) {
        QMutexLocker lock(&db_mutex);
        if (!db.isOpen()) {
            qWarning() << "Database is not open, cannot set WAL mode.";
            return false;
        }

        QString mode = enableWAL ? "WAL" : "DELETE";
        if (!execute_unsafe(QString("PRAGMA journal_mode=%1;").arg(mode))) {
            qWarning() << "Failed to set journal_mode";
            return false;
        }
        qDebug() << "SQLite journal_mode set to" << mode;

        if (enableWAL) {
            execute_unsafe("PRAGMA synchronous=NORMAL;");
        }

        execute_unsafe(QString("PRAGMA busy_timeout=%1;").arg(busyTimeoutMs));

        return true;
    }

private:
    QSqlDatabase db;
    // 仅用于 Repository 内部的嵌套调用；不能把连接变成跨线程共享资源。
    mutable QRecursiveMutex db_mutex;
    static bool isUsableIdentifier(const QString& identifier) {
        return !identifier.isEmpty() && !identifier.contains(QChar::Null);
    }

    static QString quoteIdentifier(QString identifier) {
        identifier.replace("\"", "\"\"");
        return "\"" + identifier + "\"";
    }

    static QString csvEscape(const QString& value) {
        QString escaped = value;
        if (!escaped.isEmpty()) {
            const QChar firstChar = escaped.at(0);
            bool numericLiteral = false;
            if (firstChar == QChar('+') || firstChar == QChar('-')) {
                bool ok = false;
                value.toDouble(&ok);
                numericLiteral = ok;
            }
            if (firstChar == QChar('=') || firstChar == QChar('@')
                || ((firstChar == QChar('+') || firstChar == QChar('-')) && !numericLiteral)) {
                // Excel treats the apostrophe as a text marker. The original
                // external value remains in the CSV after this marker.
                escaped.prepend(QChar('\''));
            }
        }
        escaped.replace("\"", "\"\"");
        if (escaped.contains(",") || escaped.contains("\"") || escaped.contains("\n") || escaped.contains("\r")) {
            escaped = "\"" + escaped + "\"";
        }
        return escaped;
    }

    bool connect() {
        m_connectionName = QUuid::createUuid().toString();

        if (!QFile::exists(dbPath)) {
            QFileInfo fi(dbPath);
            QDir().mkpath(fi.absolutePath());
            qDebug() << "Database file does not exist. Creating a new database:" << dbPath;
        }

        db = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
        db.setDatabaseName(dbPath);

        if (!db.open()) {
            qCritical() << "Failed to open or create database:" << db.lastError().text();
            return false;
        }

        qDebug() << "Connected to SQLite database:" << dbPath;

        // 设置 WAL 模式和其他 PRAGMA
        if (!execute_unsafe("PRAGMA journal_mode=WAL;")) {
            qWarning() << "Failed to enable WAL mode";
        } else {
            qDebug() << "SQLite WAL mode enabled.";
        }
        execute_unsafe("PRAGMA synchronous=NORMAL;");
        execute_unsafe("PRAGMA busy_timeout=3000;");

        createTable("setting", {
            {"id","INTEGER PRIMARY KEY AUTOINCREMENT"}
        });

        return true;
    }

    void disconnect() {
        if (m_connectionName.isEmpty()) {
            return;
        }
        if (db.isOpen()) {
            db.close();
            qDebug() << "Database closed.";
        }
        db = QSqlDatabase();
        QSqlDatabase::removeDatabase(m_connectionName);
        m_connectionName.clear();
    }

    QString m_connectionName;
    QString dbPath;
};
// 第一层：数据库上下文与连接工厂。
// sqliteDB 可由应用持有，但绝不跨线程共享 sqliteConnection/QSqlDatabase。
class sqliteDB {
public:
    explicit sqliteDB(QString databasePath)
        : m_databasePath(std::move(databasePath))
        , m_ownerThread(QThread::currentThread()) {
    }

    sqliteDB(const sqliteDB&) = delete;
    sqliteDB& operator=(const sqliteDB&) = delete;

    const QString& databasePath() const {
        return m_databasePath;
    }

    // 返回由调用方持有的连接。调用方必须在同一线程中销毁该连接。
    std::unique_ptr<sqliteConnection> createThreadConnection() const {
        return std::make_unique<sqliteConnection>(m_databasePath);
    }

    // 主线程专属连接，供 GUI、设置缓存和主线程维护任务使用。
    sqliteConnection* mainConnection() {
        if (QThread::currentThread() != m_ownerThread) {
            qCritical() << "sqliteDB: mainConnection accessed from a non-owner thread.";
            return nullptr;
        }
        if (!m_mainConnection) {
            m_mainConnection = createThreadConnection();
        }
        return m_mainConnection.get();
    }

private:
    QString m_databasePath;
    QThread* m_ownerThread = nullptr;
    std::unique_ptr<sqliteConnection> m_mainConnection;
};