PlateRecApp/src/auth_db.cpp

/*
 * auth_db.cpp - SQLite database layer for authentication
 */

#include "auth_db.h"
#include <sqlite3.h>
#include <cstring>
#include <cstdio>
#include <mutex>

namespace auth {

static std::mutex db_mutex;

AuthDB::AuthDB() {}

AuthDB::~AuthDB() {
    close();
}

bool AuthDB::exec(const std::string &sql) {
    if (!db_) return false;
    char *err = nullptr;
    int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &err);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "[auth_db] SQL error: %s (sql: %.100s...)\n",
                err ? err : "unknown", sql.c_str());
        sqlite3_free(err);
        return false;
    }
    return true;
}

bool AuthDB::init(const std::string &db_path) {
    std::lock_guard<std::mutex> lock(db_mutex);

    int rc = sqlite3_open(db_path.c_str(), &db_);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "[auth_db] Cannot open database: %s\n", db_path.c_str());
        return false;
    }

    // Set WAL mode for better concurrent performance
    exec("PRAGMA journal_mode=WAL");
    exec("PRAGMA busy_timeout=5000");

    // Create tables
    const char *schema = R"SQL(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            role INTEGER DEFAULT 0,
            enabled INTEGER DEFAULT 1,
            failed_attempts INTEGER DEFAULT 0,
            locked_until INTEGER DEFAULT 0,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS sessions (
            session_id TEXT PRIMARY KEY,
            csrf_token TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            username TEXT NOT NULL,
            remote_ip TEXT DEFAULT '',
            created_at INTEGER NOT NULL,
            last_active INTEGER NOT NULL,
            expires_at INTEGER NOT NULL,
            remember INTEGER DEFAULT 0,
            FOREIGN KEY (user_id) REFERENCES users(id)
        );

        CREATE TABLE IF NOT EXISTS audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp INTEGER NOT NULL,
            event_type TEXT NOT NULL,
            username TEXT DEFAULT '',
            remote_ip TEXT DEFAULT '',
            details TEXT DEFAULT '',
            success INTEGER DEFAULT 0
        );

        CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
        CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
        CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_log(timestamp);
        CREATE INDEX IF NOT EXISTS idx_audit_event ON audit_log(event_type);
    )SQL";

    if (!exec(schema)) {
        fprintf(stderr, "[auth_db] Failed to create schema\n");
        return false;
    }

    // Create default admin user if no users exist
    sqlite3_stmt *stmt;
    rc = sqlite3_prepare_v2(db_, "SELECT COUNT(*) FROM users", -1, &stmt, nullptr);
    if (rc == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int count = sqlite3_column_int(stmt, 0);
            sqlite3_finalize(stmt);

            if (count == 0) {
                // Default admin will be created by the caller
                // (they need to hash the password first)
                printf("[auth_db] No users found, admin setup needed\n");
            }
        } else {
            sqlite3_finalize(stmt);
        }
    }

    printf("[auth_db] Database initialized: %s\n", db_path.c_str());
    return true;
}

void AuthDB::close() {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (db_) {
        sqlite3_close(db_);
        db_ = nullptr;
    }
}

bool AuthDB::create_user(const std::string &username, const std::string &password_hash,
                          int role) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "INSERT INTO users (username, password_hash, role, enabled, "
                      "failed_attempts, locked_until, created_at, updated_at) "
                      "VALUES (?, ?, ?, 1, 0, 0, ?, ?)";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    time_t now = time(nullptr);
    sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, password_hash.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 3, role);
    sqlite3_bind_int64(stmt, 4, now);
    sqlite3_bind_int64(stmt, 5, now);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::get_user_by_name(const std::string &username, User &user) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "SELECT id, username, password_hash, role, enabled, "
                      "failed_attempts, locked_until, created_at, updated_at "
                      "FROM users WHERE username = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);

    bool found = false;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        user.id = sqlite3_column_int(stmt, 0);
        user.username = (const char*)sqlite3_column_text(stmt, 1);
        user.password_hash = (const char*)sqlite3_column_text(stmt, 2);
        user.role = sqlite3_column_int(stmt, 3);
        user.enabled = sqlite3_column_int(stmt, 4);
        user.failed_attempts = sqlite3_column_int(stmt, 5);
        user.locked_until = sqlite3_column_int64(stmt, 6);
        user.created_at = sqlite3_column_int64(stmt, 7);
        user.updated_at = sqlite3_column_int64(stmt, 8);
        found = true;
    }

    sqlite3_finalize(stmt);
    return found;
}

bool AuthDB::update_user_password(int user_id, const std::string &new_hash) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "UPDATE users SET password_hash = ?, updated_at = ?, "
                      "failed_attempts = 0, locked_until = 0 WHERE id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, new_hash.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int64(stmt, 2, time(nullptr));
    sqlite3_bind_int(stmt, 3, user_id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::update_failed_attempts(int user_id, int count, time_t locked_until) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "UPDATE users SET failed_attempts = ?, locked_until = ?, "
                      "updated_at = ? WHERE id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_int(stmt, 1, count);
    sqlite3_bind_int64(stmt, 2, locked_until);
    sqlite3_bind_int64(stmt, 3, time(nullptr));
    sqlite3_bind_int(stmt, 4, user_id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::reset_failed_attempts(int user_id) {
    return update_failed_attempts(user_id, 0, 0);
}

bool AuthDB::create_session(const Session &sess) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "INSERT OR REPLACE INTO sessions "
                      "(session_id, csrf_token, user_id, username, remote_ip, "
                      "created_at, last_active, expires_at, remember) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, sess.session_id.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sess.csrf_token.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 3, sess.user_id);
    sqlite3_bind_text(stmt, 4, sess.username.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sess.remote_ip.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int64(stmt, 6, sess.created_at);
    sqlite3_bind_int64(stmt, 7, sess.last_active);
    sqlite3_bind_int64(stmt, 8, sess.expires_at);
    sqlite3_bind_int(stmt, 9, sess.remember);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::get_session(const std::string &session_id, Session &sess) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "SELECT session_id, csrf_token, user_id, username, remote_ip, "
                      "created_at, last_active, expires_at, remember "
                      "FROM sessions WHERE session_id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, session_id.c_str(), -1, SQLITE_TRANSIENT);

    bool found = false;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        sess.session_id = (const char*)sqlite3_column_text(stmt, 0);
        sess.csrf_token = (const char*)sqlite3_column_text(stmt, 1);
        sess.user_id = sqlite3_column_int(stmt, 2);
        sess.username = (const char*)sqlite3_column_text(stmt, 3);
        sess.remote_ip = (const char*)sqlite3_column_text(stmt, 4);
        sess.created_at = sqlite3_column_int64(stmt, 5);
        sess.last_active = sqlite3_column_int64(stmt, 6);
        sess.expires_at = sqlite3_column_int64(stmt, 7);
        sess.remember = sqlite3_column_int(stmt, 8);
        found = true;
    }

    sqlite3_finalize(stmt);
    return found;
}

bool AuthDB::update_session_activity(const std::string &session_id, time_t now) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "UPDATE sessions SET last_active = ? WHERE session_id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_int64(stmt, 1, now);
    sqlite3_bind_text(stmt, 2, session_id.c_str(), -1, SQLITE_TRANSIENT);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::delete_session(const std::string &session_id) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "DELETE FROM sessions WHERE session_id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_text(stmt, 1, session_id.c_str(), -1, SQLITE_TRANSIENT);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::delete_user_sessions(int user_id) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "DELETE FROM sessions WHERE user_id = ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_int(stmt, 1, user_id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

bool AuthDB::cleanup_expired_sessions(time_t now) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;
    char sql[128];
    snprintf(sql, sizeof(sql), "DELETE FROM sessions WHERE expires_at < %ld", (long)now);
    return exec(sql);
}

bool AuthDB::add_audit_log(const AuditEntry &entry) {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return false;

    sqlite3_stmt *stmt;
    const char *sql = "INSERT INTO audit_log (timestamp, event_type, username, "
                      "remote_ip, details, success) VALUES (?, ?, ?, ?, ?, ?)";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return false;

    sqlite3_bind_int64(stmt, 1, entry.timestamp);
    sqlite3_bind_text(stmt, 2, entry.event_type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, entry.username.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, entry.remote_ip.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, entry.details.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt, 6, entry.success);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc == SQLITE_DONE;
}

std::vector<AuditEntry> AuthDB::get_audit_logs(int limit, int offset) {
    std::lock_guard<std::mutex> lock(db_mutex);
    std::vector<AuditEntry> result;
    if (!db_) return result;

    sqlite3_stmt *stmt;
    const char *sql = "SELECT id, timestamp, event_type, username, remote_ip, "
                      "details, success FROM audit_log ORDER BY timestamp DESC "
                      "LIMIT ? OFFSET ?";
    int rc = sqlite3_prepare_v2(db_, sql, -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return result;

    sqlite3_bind_int(stmt, 1, limit);
    sqlite3_bind_int(stmt, 2, offset);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        AuditEntry e;
        e.id = sqlite3_column_int(stmt, 0);
        e.timestamp = sqlite3_column_int64(stmt, 1);
        e.event_type = (const char*)sqlite3_column_text(stmt, 2);
        e.username = (const char*)sqlite3_column_text(stmt, 3);
        e.remote_ip = (const char*)sqlite3_column_text(stmt, 4);
        e.details = (const char*)sqlite3_column_text(stmt, 5);
        e.success = sqlite3_column_int(stmt, 6);
        result.push_back(e);
    }

    sqlite3_finalize(stmt);
    return result;
}

int AuthDB::get_audit_log_count() {
    std::lock_guard<std::mutex> lock(db_mutex);
    if (!db_) return 0;

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_, "SELECT COUNT(*) FROM audit_log", -1, &stmt, nullptr);
    if (rc != SQLITE_OK) return 0;

    int count = 0;
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        count = sqlite3_column_int(stmt, 0);
    }
    sqlite3_finalize(stmt);
    return count;
}

} // namespace auth