ic-web/server/database.lua

-- ─────────────────────────────────────────────────────────────────────────────
--  ic-web – Datenzugriff
--  Saemtliches SQL steht hier. Der uebrige Code kennt keine Tabellennamen.
-- ─────────────────────────────────────────────────────────────────────────────

ICWeb = ICWeb or {}
ICWeb.DB = {}
local DB = ICWeb.DB

-- TINYINT(1) kommt je nach Treiberfassung als 1, als true oder als "1" an.
-- Ein Vergleich mit == 1 geht deshalb frueher oder spaeter schief – und zwar
-- still: aus "aktiv" wird "gesperrt", ohne Fehlermeldung. Deshalb laeuft jede
-- Ja/Nein-Spalte aus der Datenbank durch diese Funktion.
function ICWeb.Bool(value)
    if value == nil or value == false then return false end
    if value == true then return true end
    if type(value) == 'number' then return value ~= 0 end
    if type(value) == 'string' then
        return value ~= '0' and value ~= '' and value:lower() ~= 'false'
    end
    return true
end

-- ── Sites ───────────────────────────────────────────────────────────────────
function DB.GetSiteByDomain(domain)
    return MySQL.single.await('SELECT * FROM ic_web_sites WHERE domain = ?', { domain })
end

function DB.GetSiteById(id)
    return MySQL.single.await('SELECT * FROM ic_web_sites WHERE id = ?', { id })
end

function DB.GetSitesForOwner(ownerType, ownerId)
    return MySQL.query.await(
        'SELECT * FROM ic_web_sites WHERE owner_type = ? AND owner_id = ? ORDER BY domain',
        { ownerType, ownerId }) or {}
end

function DB.CountSitesForOwner(ownerType, ownerId)
    local row = MySQL.single.await(
        'SELECT COUNT(*) AS n FROM ic_web_sites WHERE owner_type = ? AND owner_id = ?',
        { ownerType, ownerId })
    return (row and tonumber(row.n)) or 0
end

function DB.GetAllSites()
    return MySQL.query.await('SELECT * FROM ic_web_sites ORDER BY domain') or {}
end

function DB.SetOwner(id, ownerType, ownerId)
    return MySQL.update.await(
        'UPDATE ic_web_sites SET owner_type = ?, owner_id = ? WHERE id = ?',
        { ownerType, ownerId, id })
end

function DB.CreateSite(data)
    return MySQL.insert.await(
        [[INSERT INTO ic_web_sites (domain, title, theme, owner_type, owner_id, published, created_by)
          VALUES (?,?,?,?,?,?,?)]],
        { data.domain, data.title, data.theme, data.owner_type, data.owner_id,
          data.published and 1 or 0, data.created_by })
end

function DB.UpdateSite(id, data)
    return MySQL.update.await(
        'UPDATE ic_web_sites SET title = ?, theme = ?, published = ? WHERE id = ?',
        { data.title, data.theme, data.published and 1 or 0, id })
end

function DB.SetBlocked(id, blocked)
    return MySQL.update.await('UPDATE ic_web_sites SET blocked = ? WHERE id = ?',
        { blocked and 1 or 0, id })
end

function DB.DeleteSite(id)
    return MySQL.update.await('DELETE FROM ic_web_sites WHERE id = ?', { id })
end

-- ── Seiten ──────────────────────────────────────────────────────────────────
function DB.GetPages(siteId)
    return MySQL.query.await(
        'SELECT * FROM ic_web_pages WHERE site_id = ? ORDER BY sort_order, id', { siteId }) or {}
end

function DB.GetPage(siteId, slug)
    return MySQL.single.await(
        'SELECT * FROM ic_web_pages WHERE site_id = ? AND slug = ?', { siteId, slug })
end

function DB.CountPages(siteId)
    local row = MySQL.single.await(
        'SELECT COUNT(*) AS n FROM ic_web_pages WHERE site_id = ?', { siteId })
    return (row and tonumber(row.n)) or 0
end

-- Anlegen oder ueberschreiben. Der eindeutige Schluessel ist (site_id, slug).
function DB.SavePage(siteId, slug, title, blocksJson, sortOrder)
    return MySQL.update.await(
        [[INSERT INTO ic_web_pages (site_id, slug, title, blocks, sort_order)
          VALUES (?,?,?,?,?)
          ON DUPLICATE KEY UPDATE title = VALUES(title), blocks = VALUES(blocks),
                                  sort_order = VALUES(sort_order)]],
        { siteId, slug, title, blocksJson, sortOrder or 0 })
end

function DB.DeletePage(siteId, slug)
    return MySQL.update.await(
        'DELETE FROM ic_web_pages WHERE site_id = ? AND slug = ?', { siteId, slug })
end

-- ── Zugaenge ────────────────────────────────────────────────────────────────
-- Das Passwort erscheint hier nur als Abfrageparameter. Gehasht wird in der
-- Datenbank, damit weder Klartext noch Hash in einer Lua-Variable landen.
local ACCOUNT_FIELDS =
    'id, username, domain, role, display_name, active, must_change, created_by, created_at, last_login'

function DB.GetAccountByUsername(username)
    return MySQL.single.await(
        'SELECT ' .. ACCOUNT_FIELDS .. ' FROM ic_web_accounts WHERE username = ?', { username })
end

function DB.GetAccountById(id)
    return MySQL.single.await(
        'SELECT ' .. ACCOUNT_FIELDS .. ' FROM ic_web_accounts WHERE id = ?', { id })
end

function DB.GetAccountsForDomain(domain)
    return MySQL.query.await(
        'SELECT ' .. ACCOUNT_FIELDS .. ' FROM ic_web_accounts WHERE domain = ? ORDER BY username',
        { domain }) or {}
end

function DB.CountAccountsForDomain(domain)
    local row = MySQL.single.await(
        'SELECT COUNT(*) AS n FROM ic_web_accounts WHERE domain = ?', { domain })
    return (row and tonumber(row.n)) or 0
end

function DB.GetAllAccounts()
    return MySQL.query.await(
        'SELECT ' .. ACCOUNT_FIELDS .. ' FROM ic_web_accounts ORDER BY domain, username') or {}
end

function DB.CreateAccount(data)
    return MySQL.insert.await(
        [[INSERT INTO ic_web_accounts
            (username, domain, role, display_name, salt, password_hash, must_change, created_by)
          VALUES (?,?,?,?,?, SHA2(CONCAT(?, ?), 256), ?, ?)]],
        { data.username, data.domain, data.role, data.display_name,
          data.salt, data.salt, data.password,
          data.must_change and 1 or 0, data.created_by })
end

-- Gibt den Zugang zurueck, wenn das Passwort stimmt, sonst nil.
function DB.CheckPassword(username, password)
    return MySQL.single.await(
        'SELECT ' .. ACCOUNT_FIELDS .. ' FROM ic_web_accounts'
        .. ' WHERE username = ? AND password_hash = SHA2(CONCAT(salt, ?), 256)',
        { username, password })
end

function DB.SetPassword(id, salt, password)
    return MySQL.update.await(
        'UPDATE ic_web_accounts SET salt = ?, password_hash = SHA2(CONCAT(?, ?), 256),'
        .. ' must_change = 0 WHERE id = ?',
        { salt, salt, password, id })
end

function DB.SetAccountActive(id, active)
    return MySQL.update.await('UPDATE ic_web_accounts SET active = ? WHERE id = ?',
        { active and 1 or 0, id })
end

function DB.SetAccountRole(id, role)
    return MySQL.update.await('UPDATE ic_web_accounts SET role = ? WHERE id = ?', { role, id })
end

function DB.DeleteAccount(id)
    return MySQL.update.await('DELETE FROM ic_web_accounts WHERE id = ?', { id })
end

function DB.TouchLogin(id)
    return MySQL.update.await('UPDATE ic_web_accounts SET last_login = NOW() WHERE id = ?', { id })
end

-- ── Fehlversuche ────────────────────────────────────────────────────────────
function DB.AddLoginFail(username, identifier)
    return MySQL.insert.await(
        'INSERT INTO ic_web_login_fails (username, identifier) VALUES (?,?)',
        { username, identifier })
end

function DB.CountLoginFails(username, withinSeconds)
    local row = MySQL.single.await(
        [[SELECT COUNT(*) AS n FROM ic_web_login_fails
          WHERE username = ? AND created_at > DATE_SUB(NOW(), INTERVAL ? SECOND)]],
        { username, withinSeconds })
    return (row and tonumber(row.n)) or 0
end

function DB.ClearLoginFails(username)
    return MySQL.update.await('DELETE FROM ic_web_login_fails WHERE username = ?', { username })
end

function DB.PruneLoginFails(olderThanSeconds)
    return MySQL.update.await(
        'DELETE FROM ic_web_login_fails WHERE created_at < DATE_SUB(NOW(), INTERVAL ? SECOND)',
        { olderThanSeconds })
end

-- ── Meldungen ───────────────────────────────────────────────────────────────
function DB.AddReport(siteId, pageSlug, reason, reportedBy)
    return MySQL.insert.await(
        'INSERT INTO ic_web_reports (site_id, page_slug, reason, reported_by) VALUES (?,?,?,?)',
        { siteId, pageSlug, reason, reportedBy })
end

function DB.RecentReportBy(siteId, identifier, withinSeconds)
    return MySQL.single.await(
        [[SELECT id FROM ic_web_reports
          WHERE site_id = ? AND reported_by = ?
            AND created_at > DATE_SUB(NOW(), INTERVAL ? SECOND) LIMIT 1]],
        { siteId, identifier, withinSeconds })
end

function DB.GetOpenReports()
    return MySQL.query.await(
        [[SELECT r.*, s.domain FROM ic_web_reports r
          JOIN ic_web_sites s ON s.id = r.site_id
          WHERE r.status = 'open' ORDER BY r.created_at DESC LIMIT 100]]) or {}
end

function DB.SetReportStatus(id, status)
    return MySQL.update.await('UPDATE ic_web_reports SET status = ? WHERE id = ?', { status, id })
end