#include "Database.h"
#include "Core/Logger.h"
#include <sqlite3.h>
#include <stdexcept>

namespace Older {
bool Database::open(const std::string &path) {
    if (sqlite3_open(path.c_str(), &m_sqlite)) {
        LOG(error) << "Can't open database: " << sqlite3_errmsg(m_sqlite);
        return false;
    }
    initialize();
    return true;
}

void Database::upsertVisitRecord(const std::string &url, const std::string &visitorUuid, const std::string &userAgent,
                                 int64_t viewTime) {
    sqlite3_stmt *statement = nullptr;
    const char *query = "INSERT INTO visit_analysis (url, visitor_uuid, last_user_agent, last_view_time, page_view_count) "
                        "VALUES (?, ?, ?, ?, 1) "
                        "ON CONFLICT(url, visitor_uuid) DO UPDATE SET "
                        "last_user_agent = excluded.last_user_agent, "
                        "last_view_time = excluded.last_view_time, "
                        "page_view_count = page_view_count + 1;";

    if (sqlite3_prepare_v2(m_sqlite, query, -1, &statement, nullptr) == SQLITE_OK) {
        sqlite3_bind_text(statement, 1, url.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(statement, 2, visitorUuid.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(statement, 3, userAgent.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_int(statement, 4, viewTime);

        if (sqlite3_step(statement) != SQLITE_DONE) {
            LOG(error) << "Error inserting/updating record: " << sqlite3_errmsg(m_sqlite);
        }
    }
    sqlite3_finalize(statement);
}

VisitorStats Database::visitorStats(const std::string &url) {
    VisitorStats ret;
    ret.url = url;
    sqlite3_stmt *statement = nullptr;
    const char *query = "SELECT COUNT(DISTINCT visitor_uuid) AS visitor_count, SUM(page_view_count) AS total_views "
                        "FROM visit_analysis WHERE url = ?;";

    if (sqlite3_prepare_v2(m_sqlite, query, -1, &statement, nullptr) == SQLITE_OK) {
        sqlite3_bind_text(statement, 1, url.c_str(), -1, SQLITE_STATIC);

        if (sqlite3_step(statement) == SQLITE_ROW) {
            ret.visitorCount = sqlite3_column_int(statement, 0);
            ret.totalViews = sqlite3_column_int(statement, 1);
        }
    }
    sqlite3_finalize(statement);
    return ret;
}

std::list<VisitorStats> Database::mostViewedUrls(int n) {
    std::list<VisitorStats> ret;
    sqlite3_stmt *statement = nullptr;
    const char *query = R"(
        SELECT url, SUM(page_view_count) AS total_page_views 
        FROM visit_analysis 
        GROUP BY url 
        ORDER BY total_page_views DESC 
        LIMIT ?;
    )";
    
    if (sqlite3_prepare_v2(m_sqlite, query, -1, &statement, nullptr) == SQLITE_OK) {
        sqlite3_bind_int(statement, 1, n);

        while (sqlite3_step(statement) == SQLITE_ROW) {
            VisitorStats item;
            item.url = reinterpret_cast<const char *>(sqlite3_column_text(statement, 0));
            item.totalViews = sqlite3_column_int(statement, 1);
            ret.push_back(item);
        }
    }
    sqlite3_finalize(statement);
    return ret;
}
std::list<VisitorStats> Database::latestViewedUrls(int n) {
    std::list<VisitorStats> ret;
    sqlite3_stmt *statement = nullptr;
    const char *query = R"(
        SELECT url, MAX(last_view_time) AS latest_view_time 
        FROM visit_analysis 
        GROUP BY url 
        ORDER BY latest_view_time DESC 
        LIMIT ?;
    )";

    if (sqlite3_prepare_v2(m_sqlite, query, -1, &statement, nullptr) == SQLITE_OK) {
        sqlite3_bind_int(statement, 1, n);
        while (sqlite3_step(statement) == SQLITE_ROW) {
            VisitorStats item;
            item.url = reinterpret_cast<const char *>(sqlite3_column_text(statement, 0));
            item.lastViewTime = sqlite3_column_int64(statement, 1);
            ret.push_back(item);
        }
    }
    sqlite3_finalize(statement);
    return ret;
}

SiteStats Database::siteStats() {
    SiteStats ret;
    sqlite3_stmt *statement;
    const char *query = "SELECT COUNT(DISTINCT visitor_uuid) AS total_visitors, SUM(page_view_count) AS total_page_views "
                        "FROM visit_analysis;";

    if (sqlite3_prepare_v2(m_sqlite, query, -1, &statement, nullptr) == SQLITE_OK) {
        if (sqlite3_step(statement) == SQLITE_ROW) {
            ret.totalVisitors = sqlite3_column_int(statement, 0);
            ret.totalViews = sqlite3_column_int(statement, 1);
        }
    }
    sqlite3_finalize(statement);
    return ret;
}

void Database::createUser(const Account &account) {
    if (!Account::validateEmail(account.email)) {
        throw std::runtime_error("Invalid email format");
    }

    sqlite3_stmt *statement = nullptr;
    const char *sql = "INSERT INTO users (username, email, password_hash, salt, created_at) VALUES (?, ?, ?, ?, ?);";

    if (sqlite3_prepare_v2(m_sqlite, sql, -1, &statement, nullptr) != SQLITE_OK) {
        throw std::runtime_error(sqlite3_errmsg(m_sqlite));
    }

    sqlite3_bind_text(statement, 1, account.username.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 2, account.email.c_str(), -1, SQLITE_STATIC); // 新增绑定
    sqlite3_bind_blob(statement, 3, account.passwordHash.data(), account.passwordHash.size(), SQLITE_STATIC);
    sqlite3_bind_blob(statement, 4, account.salt.data(), account.salt.size(), SQLITE_STATIC);
    sqlite3_bind_int64(statement, 5, account.createdAt);

    int rc = sqlite3_step(statement);
    sqlite3_finalize(statement);

    if (rc != SQLITE_DONE) {
        const char *message = sqlite3_errmsg(m_sqlite);
        if (std::string(message).find("UNIQUE") != std::string::npos) {
            throw std::runtime_error("username or email already exists");
        }
        throw std::runtime_error(message);
    }
}

Account Database::user(const std::string &identifier) const {
    sqlite3_stmt *stmt;
    const char *sql = "SELECT id, username, email, password_hash, salt, created_at "
                      "FROM users WHERE username = ? OR email = ?;";

    if (sqlite3_prepare_v2(m_sqlite, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        throw std::runtime_error(sqlite3_errmsg(m_sqlite));
    }

    sqlite3_bind_text(stmt, 1, identifier.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, identifier.c_str(), -1, SQLITE_STATIC);

    if (sqlite3_step(stmt) != SQLITE_ROW) {
        sqlite3_finalize(stmt);
        throw std::runtime_error("User not found");
    }

    Account ret;

    ret.id = sqlite3_column_int(stmt, 0);

    ret.username = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 1));
    ret.email = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 2));

    const void *hash_blob = sqlite3_column_blob(stmt, 3);
    int hash_size = sqlite3_column_bytes(stmt, 3);
    ret.passwordHash.resize(hash_size);
    memcpy(ret.passwordHash.data(), hash_blob, hash_size);

    const void *salt_blob = sqlite3_column_blob(stmt, 4);
    int salt_size = sqlite3_column_bytes(stmt, 4);
    ret.salt.resize(salt_size);
    memcpy(ret.salt.data(), salt_blob, salt_size);

    ret.createdAt = sqlite3_column_int64(stmt, 5);

    sqlite3_finalize(stmt);
    return ret;
}

Account Database::user(int64_t id) const {
    sqlite3_stmt *stmt;
    const char *sql = "SELECT id, username, email, password_hash, salt, created_at "
                      "FROM users WHERE id = ?;";

    if (sqlite3_prepare_v2(m_sqlite, sql, -1, &stmt, nullptr) != SQLITE_OK) {
        throw std::runtime_error(sqlite3_errmsg(m_sqlite));
    }

    sqlite3_bind_int64(stmt, 1, id);

    if (sqlite3_step(stmt) != SQLITE_ROW) {
        sqlite3_finalize(stmt);
        throw std::runtime_error("User not found");
    }

    Account ret;

    ret.id = sqlite3_column_int(stmt, 0);

    ret.username = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 1));
    ret.email = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 2));

    const void *hash_blob = sqlite3_column_blob(stmt, 3);
    int hash_size = sqlite3_column_bytes(stmt, 3);
    ret.passwordHash.resize(hash_size);
    memcpy(ret.passwordHash.data(), hash_blob, hash_size);

    const void *salt_blob = sqlite3_column_blob(stmt, 4);
    int salt_size = sqlite3_column_bytes(stmt, 4);
    ret.salt.resize(salt_size);
    memcpy(ret.salt.data(), salt_blob, salt_size);

    ret.createdAt = sqlite3_column_int64(stmt, 5);

    sqlite3_finalize(stmt);
    return ret;
}

void Database::initialize() {
    createVisitAnalysisTable();
    createUsersTable();
}

void Database::createVisitAnalysisTable() {
    const char *createTableQuery = R"(
        CREATE TABLE IF NOT EXISTS visit_analysis (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url TEXT NOT NULL,                 
            visitor_uuid TEXT NOT NULL,        
            last_user_agent TEXT NOT NULL,      
            last_view_time INTEGER NOT NULL,    
            page_view_count INTEGER NOT NULL,  
            UNIQUE(url, visitor_uuid)          
        );
    )";

    char *message = nullptr;
    int rc = sqlite3_exec(m_sqlite, createTableQuery, nullptr, nullptr, &message);
    if (rc != SQLITE_OK) {
        LOG(error) << "Error creating table: " << message;
        sqlite3_free(message);
    }
}

void Database::createUsersTable() {
    const char *sql = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password_hash BLOB NOT NULL,
            salt BLOB NOT NULL,
            created_at INTEGER NOT NULL
        );
    )";
    char *message = nullptr;
    int rc = sqlite3_exec(m_sqlite, sql, nullptr, nullptr, &message);
    if (rc != SQLITE_OK) {
        LOG(error) << "Error creating table: " << message;
        sqlite3_free(message);
    }
}
} // namespace Older