#include "DatabaseManager.h"
#include <QCoreApplication>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
#include <QDir>
#include <QFileInfo>
#include <QSettings>
#include <QThread>
#include <QRegularExpression>

DatabaseManager::DatabaseManager() = default;

DatabaseManager::~DatabaseManager() {
    QMutexLocker locker(&mutex);

    // QSqlDatabase требует живого QCoreApplication — при завершении
    // статический синглтон может разрушаться после QApplication
    if (!QCoreApplication::instance()) {
        connectionPool.clear();
        return;
    }

    for (auto it = connectionPool.begin(); it != connectionPool.end(); ++it) {
        QSqlDatabase::removeDatabase(it.key());
    }
    connectionPool.clear();
    qDebug() << "Все соединения закрыты";
}

DatabaseManager &DatabaseManager::instance() {
    static DatabaseManager instance;
    return instance;
}

QSqlDatabase DatabaseManager::database() {
    QMutexLocker locker(&mutex);
    QString name = connectionName();

    // Создаем новое соединение, если его ещё нет
    if (!connectionPool.contains(name)) {
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", name);
        const QSettings settings("config.ini", QSettings::IniFormat);

        QString dbPath = settings.value("db/dbPath").toString();
        if (dbPath.startsWith("~")) {
            dbPath.replace(0, 1, QDir::homePath());
        }

        // Создаём директорию для БД, если её нет
        QDir().mkpath(QFileInfo(dbPath).absolutePath());

        db.setDatabaseName(dbPath);

        if (!db.open()) {
            qWarning() << "Ошибка открытия базы данных:" << db.lastError().text();
        }

        connectionPool[name] = db;

        // Автоматическое удаление соединения при завершении потока
        QObject::connect(QThread::currentThread(), &QThread::finished, [this, name]() {
            QMutexLocker cLocker(&mutex);
            if (connectionPool.contains(name)) {
                connectionPool.remove(name);
                QSqlDatabase::removeDatabase(name);
                qDebug() << "Соединение" << name << "удалено";
            }
        });
    }

    return connectionPool[name];
}

QString DatabaseManager::connectionName() {
    return QString("connection_%1").arg(reinterpret_cast<quintptr>(QThread::currentThreadId()));
}


static bool columnExists(QSqlQuery &q, const QString &table, const QString &column) {
    // Валидация: допускаем только [a-zA-Z0-9_] чтобы исключить SQL injection
    static const QRegularExpression valid("^[a-zA-Z_][a-zA-Z0-9_]*$");
    if (!valid.match(table).hasMatch() || !valid.match(column).hasMatch()) {
        qCritical() << "[DB] columnExists: invalid identifier" << table << column;
        return false;
    }

    q.exec(QString("SELECT COUNT(*) FROM pragma_table_info('%1') WHERE name='%2'")
        .arg(table, column));
    return q.next() && q.value(0).toInt() > 0;
}

void DatabaseManager::initSchema() {
    QSqlDatabase db = database();
    QSqlQuery q(db);

    // Читаем текущую версию схемы
    q.exec("PRAGMA user_version");
    const int version = q.next() ? q.value(0).toInt() : 0;

    // ── Версия 1: полная схема ─────────────────────────────────────────────────
    if (version < 1) {
        db.transaction();

        q.exec(R"(CREATE TABLE IF NOT EXISTS devices (
            id            TEXT PRIMARY KEY,
            api_id        TEXT,
            status        TEXT,
            name          TEXT,
            android       TEXT,
            screen_width  INTEGER,
            screen_height INTEGER,
            battery       INTEGER,
            update_time   DATETIME,
            timestamp     DATETIME DEFAULT (strftime('%d.%m.%Y %H:%M:%S', 'now')),
            image         BLOB
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS applications (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id        TEXT,
            pin_code         TEXT,
            pin_code_checked TEXT DEFAULT 'no',
            name             TEXT,
            code             TEXT,
            package          TEXT,
            status           TEXT DEFAULT 'off',
            install          TEXT DEFAULT 'no',
            comment          TEXT,
            phone            TEXT DEFAULT '',
            email            TEXT DEFAULT '',
            full_name        TEXT DEFAULT '',
            bank_profile_id  TEXT DEFAULT '',
            currency         TEXT DEFAULT '',
            amount           REAL DEFAULT 0,
            FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE,
            UNIQUE (device_id, code)
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS accounts (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id    TEXT,
            app_code     TEXT,
            last_numbers TEXT,
            description  TEXT,
            amount       REAL,
            currency     TEXT DEFAULT '',
            update_time  DATETIME DEFAULT (strftime('%d.%m.%Y %H:%M:%S', 'now')),
            status       TEXT DEFAULT 'active',
            card_number  TEXT DEFAULT '',
            material_id  TEXT DEFAULT '',
            FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE,
            UNIQUE (device_id, app_code, last_numbers)
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS transactions (
            id                        INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id                INTEGER,
            external_id               INTEGER,
            amount                    REAL,
            fee                       REAL,
            status                    TEXT,
            type                      TEXT,
            phone                     TEXT,
            name                      TEXT,
            short_description         TEXT,
            updated_short_description TEXT,
            description               TEXT,
            updated_description       TEXT,
            bank_name                 TEXT,
            bank_time                 TEXT,
            bank_tr_external_id       TEXT,
            update_time               DATETIME,
            complete_time             DATETIME,
            timestamp                 DATETIME DEFAULT CURRENT_TIMESTAMP,
            receipt_image             BLOB,
            FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS events (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id    INTEGER,
            external_id   TEXT,
            device_id     TEXT,
            amount        REAL,
            phone         TEXT,
            bank_name     TEXT,
            comment       TEXT,
            status        TEXT,
            type          TEXT,
            update_time   DATETIME,
            complete_time DATETIME,
            timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP,
            json          TEXT DEFAULT '',
            sent_to_server INTEGER DEFAULT 0,
            FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
            FOREIGN KEY (device_id)  REFERENCES devices (id)  ON DELETE CASCADE
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS banks (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            alias     TEXT UNIQUE,
            name      TEXT,
            nspk_name TEXT
        ))");

        q.exec(R"(CREATE TABLE IF NOT EXISTS app_logs (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            source     TEXT NOT NULL DEFAULT '',
            device_id  TEXT NOT NULL DEFAULT '',
            message    TEXT NOT NULL,
            screenshot BLOB,
            timestamp  TEXT NOT NULL
        ))");

        q.exec("CREATE INDEX IF NOT EXISTS idx_app_logs_ts ON app_logs (id DESC)");

        q.exec(R"(CREATE TABLE IF NOT EXISTS settings (
            key   TEXT PRIMARY KEY,
            value TEXT
        ))");

        db.commit();
        q.exec("PRAGMA user_version = 1");
        qDebug() << "[DB] Migration 1 applied";
    }

    // ── Версия 2: добавляем email в applications ─────────────────────────────
    if (version < 2) {
        db.transaction();
        q.exec("ALTER TABLE applications ADD COLUMN email TEXT DEFAULT ''");
        db.commit();
        q.exec("PRAGMA user_version = 2");
        qDebug() << "[DB] Migration 2 applied";
    }
}

void DatabaseManager::closeConnection() {
    QMutexLocker locker(&mutex);
    if (const QString name = connectionName(); connectionPool.contains(name)) {
        connectionPool.remove(name);
        QSqlDatabase::removeDatabase(name);
        qDebug() << "Соединение" << name << "удалено вручную";
    }
}