data.lua

verbana.data = {}

local data = verbana.data
local lib_asn = verbana.lib_asn
local lib_ip = verbana.lib_ip
local util = verbana.util
local log = verbana.log

local sql = verbana.sql
local db = verbana.db

data.version = 1

-- constants
data.player_status = {
    default={name='default', id=1, color='#FFF'},
    suspicious={name='suspicious', id=2, color='#FF0'},
    banned={name='banned', id=3, color='#F00'},
    whitelisted={name='whitelisted', id=4, color='#0F0'},
    unverified={name='unverified', id=5, color='#00F'},
    kicked={name='kicked', id=6, color='#F0F'},  -- for logging kicks
}
data.player_status_name = {}
data.player_status_color = {}
for _, value in pairs(data.player_status) do
    data.player_status_name[value.id] = value.name
    data.player_status_color[value.id] = value.color
end

data.ip_status = {
    default={name='default', id=1, color='#FFF'},
    suspicious={name='suspicious', id=2, color='#FF0'},
    blocked={name='blocked', id=3, color='#F00'},
    trusted={name='trusted', id=4, color='#0F0'},
}
data.ip_status_name = {}
data.ip_status_color = {}
for _, value in pairs(data.ip_status) do
    data.ip_status_name[value.id] = value.name
    data.ip_status_color[value.id] = value.color
end

data.asn_status = {
    default={name='default', id=1, color='#FFF'},
    suspicious={name='suspicious', id=2, color='#FF0'},
    blocked={name='blocked', id=3, color='#FF0'},
}
data.asn_status_name = {}
data.asn_status_color = {}
for _, value in pairs(data.asn_status) do
    data.asn_status_name[value.id] = value.name
    data.asn_status_color[value.id] = value.color
end

data.verbana_player = '!verbana!'
data.verbana_player_id = 1

-- wrap sqllite API to make error reporting less messy
local function check_description(description)
    return (
        type(description) == 'string' and
        description ~= ''
    )
end

local function execute(code, description)
    if not check_description(description) then
        log('error', 'bad description for execute: %q', tostring(description))
        return false
    end
    if db:exec(code) ~= sql.OK then
        log('error', 'executing %s %q: %s', description, code, db:errmsg())
        return false
    end
    return true
end

local function prepare(code, description)
    if not check_description(description) then
        log('error', 'bad description for prepare: %q', tostring(description))
        return
    end
    local statement = db:prepare(code)
    if not statement then
        log('error', 'preparing %s %q: %s', description, code, db:errmsg())
        return
    end
    return statement
end

local function bind(statement, description, ...)
    if not check_description(description) then
        log('error', 'bad description for bind: %q', tostring(description))
        return false
    end
    if statement:bind_values(...) ~= sql.OK then
        log('error', 'binding %s: %s %q', description, db:errmsg(), minetest.serialize({...}))
        return false
    end
    return true
end

local function bind_and_step(statement, description, ...)
    if not check_description(description) then
        log('error', 'bad description for bind_and_step: %q', tostring(description))
        return false
    end
    if not bind(statement, description, ...) then return false end
    if statement:step() ~= sql.DONE then
        log('error', 'stepping %s: %s %q', description, db:errmsg(), minetest.serialize({...}))
        return false
    end
    statement:reset()
    return true
end

local function finalize(statement, description)
    if not check_description(description) then
        log('error', 'bad description for finalize: %q', tostring(description))
        return false
    end
    if statement:finalize() ~= sql.OK then
        log('error', 'finalizing %s: %s', description, db:errmsg())
        return false
    end
    return true
end

local function execute_bind_one(code, description, ...)
    if not check_description(description) then
        log('error', 'bad description for execute_bind_one: %q', tostring(description))
        return false
    end
    local statement = prepare(code, description)
    if not statement then return false end
    if not bind_and_step(statement, description, ...) then return false end
    if not finalize(statement, description) then return false end
    return true
end

local function get_full_table(code, description, ...)
    if not check_description(description) then
        log('error', 'bad description for get_full_table: %q', tostring(description))
        return false
    end
    local statement = prepare(code, description)
    if not statement then return end
    if not bind(statement, description, ...) then return end
    local rows = {}
    for row in statement:rows() do
        table.insert(rows, row)
    end
    if not finalize(statement, description) then return end
    return rows
end

local function get_full_ntable(code, description, ...)
    if not check_description(description) then
        log('error', 'bad description for get_full_ntable: %q', tostring(description))
        return
    end
    local statement = prepare(code, description)
    if not statement then return end
    if not bind(statement, description, ...) then return end
    local rows = {}
    for row in statement:nrows() do
        table.insert(rows, row)
    end
    if not finalize(statement, description) then return end
    return rows
end

local function sort_status_table(status_table)
    local sortable = {}
    for _, value in pairs(status_table) do table.insert(sortable, value) end
    table.sort(sortable, function (a, b) return a.id < b.id end)
    return sortable
end

-- SCHEMA INITIALIZATION
local function get_current_schema_version()
    local code = [[
        SELECT name
          FROM sqlite_master
         WHERE type == 'table'
           AND name == ?;
    ]]
    local rows = get_full_ntable(code, 'does version table exist?', 'version')
    if not rows or #rows > 1 then
        log('error', 'error checking if version table exists')
        return
    end -- ERROR
    if #rows == 0 then return 0 end -- if version table doesn't exist, assume DB is version 1
    code = [[SELECT version FROM version]]
    rows = get_full_ntable(code, 'get current version')
    if not rows or #rows ~= 1 then
        log('error', 'error querying version table')
        return
    end -- ERROR
    return rows[1].version
end

local function set_current_schema_version(version)
    local code = [[UPDATE version SET version = ?]]
    execute_bind_one(code, 'set current schema version', version)
end

local function init_status_table(table_name, status_table)
    local status_sql = ('INSERT OR IGNORE INTO %s_status (id, name) VALUES (?, ?)'):format(table_name)
    local status_statement = prepare(status_sql, ('initialize %s_status'):format(table_name))
    if not status_statement then return false end
    for _, status in ipairs(sort_status_table(status_table)) do
        if not bind_and_step(status_statement, 'insert status', status.id, status.name) then
            return false
        end
    end
    if not finalize(status_statement, 'insert status') then
        return false
    end
    return true
end

local function intialize_schema()
    verbana.log('action', 'initializing schema')
    local schema = util.load_file(verbana.modpath .. '/schema.sql')
    if not schema then
        error(('[Verbana] Could not find Verbana schema at %q'):format(verbana.modpath .. '/schema.sql'))
    end
    if db:exec(schema) ~= sql.OK then
        error(('[Verbana] failed to initialize the database: %s'):format(db:error_message()))
    end
end

local function migrate_db(version)
    verbana.log('action', 'migrating DB to version %s', version)
    local filename = ('%s/migrations/%s.sql'):format(verbana.modpath, version)
    local schema = util.load_file(filename)
    if not schema then
        error(('[Verbana] Could not find Verbana migration schema at %q'):format(filename))
    end
    if db:exec(schema) ~= sql.OK then
        error(('[Verbana] failed to migrate the database to version %s: %s'):format(version, db:error_message()))
    end
end

local function initialize_static_data()
    verbana.log('action', 'initializing static data')
    if not init_status_table('player', data.player_status) then
        error('[Verbana] error initializing player_status: see server log')
    end
    if not init_status_table('ip', data.ip_status) then
        error('[Verbana] error initializing ip_status: see server log')
    end
    if not init_status_table('asn', data.asn_status) then
        error('[Verbana] error initializing asn_status: see server log')
    end
    local verbana_player_sql = 'INSERT OR IGNORE INTO player (name) VALUES (?)'
    if not execute_bind_one(verbana_player_sql, 'verbana player', data.verbana_player) then
        error('[Verbana] error initializing verbana internal player: see server log')
    end
end

local function clean_db()
    local code = [[
        PRAGMA writable_schema = 1;
        DELETE FROM sqlite_master WHERE type IN ('table', 'index', 'trigger');
        PRAGMA writable_schema = 0;
        VACUUM;
        PRAGMA INTEGRITY_CHECK;
    ]]
    return execute(code, 'erase current DB')
end

local function init_db()
    local initialized = false
    local current_version = get_current_schema_version()
    if not current_version then
        error('[Verbana] error getting current DB version; aborting.')
    elseif current_version > data.version then
        error('[Verbana] database version is more recent than code version; please upgrade code.')
    elseif current_version == 0 or verbana.settings.debug_mode then
        -- wipe any pre-existing copies of the schema
        if not clean_db() then
            error('[Verbana] error wiping existing DB')
        end
        intialize_schema()
        current_version = 1
        initialized = true
    elseif current_version == data.version then
        return -- everything is up to date
    end
    for i = current_version + 1, data.version do
        migrate_db(i)
        set_current_schema_version(i)
    end
    initialize_static_data()

    if current_version == 0 or verbana.settings.debug_mode then
        -- auto import sban on first boot or in debug mode
        local sban_path = minetest.get_worldpath() .. '/sban.sqlite'
        if util.file_exists(sban_path) then
            log('action', 'automatically importing existing sban DB')
            if not imports.sban.import(sban_path) then
                log('error', 'failed to import existing sban DB')
            end
        end
    end
end

init_db() -- initialize DB after registering import_from_sban

---- data API -----


local player_id_cache = {}
function data.get_player_id(name, create_if_new)
    local cached_id = player_id_cache[name]
    if cached_id then return unpack(cached_id) end
    if create_if_new then
        if not execute_bind_one('INSERT OR IGNORE INTO player (name) VALUES (?)', 'insert player', name) then
            log('warning', 'data.get_player_id: failed to create ID for player %s', name)
            return nil, nil
        end
    end
    local table = get_full_table('SELECT id, name FROM player WHERE LOWER(name) == LOWER(?) LIMIT 1', 'get player id', name)
    if not (table and table[1]) then
        log('warning', 'data.get_player_id: failed to retrieve ID for player %s; %s', name, create_if_new)
        return nil, nil
    end
    player_id_cache[name] = table[1]
    return unpack(table[1])
end

function data.flag_player(player_id, flag)
    local code = [[
        UPDATE player
           SET flagged = ?
         WHERE id = ?
    ]]
    if not flag then flag = true end
    return execute_bind_one(code, 'flag player', flag, player_id)
end

local player_status_cache = {}
function data.get_player_status(player_id, create_if_new)
    player_id = data.get_master(player_id) or player_id
    local cached_status = player_status_cache[player_id]
    if cached_status then return cached_status, false end
    local code = [[
        SELECT executor.id    executor_id
             , executor.name  executor_name
             , status.id      id
             , status.name    name
             , log.timestamp  timestamp
             , log.reason     reason
             , log.expires    expires
             , player.flagged flagged
          FROM player
          JOIN player_status_log log      ON player.current_status_id == log.id
          JOIN player_status     status   ON log.status_id == status.id
          JOIN player            executor ON log.executor_id == executor.id
         WHERE player.id == ?
         LIMIT 1
    ]]
    local table = get_full_ntable(code, 'get player status', player_id)
    if #table == 1 then
        player_status_cache[player_id] = table[1]
        return table[1], false
    elseif #table > 1 then
        log('error', 'somehow got more than 1 result when getting current player status for %s', player_id)
        return nil, false
    elseif not create_if_new then
        return nil, nil
    end
    if not data.set_player_status(player_id, data.verbana_player_id, data.player_status.default.id, 'creating initial player status') then
        log('error', 'failed to set initial player status')
        return nil, true
    end
    return data.get_player_status(player_id, false), true
end
function data.set_player_status(player_id, executor_id, status_id, reason, expires, no_update_current)
    player_id = data.get_master(player_id) or player_id
    player_status_cache[player_id] = nil
    local code = [[
        INSERT INTO player_status_log (player_id, executor_id, status_id, reason, expires, timestamp)
             VALUES                   (?,         ?,           ?,         ?,      ?,       ?)
    ]]
    local now = os.time()
    if not execute_bind_one(code, 'set player status', player_id, executor_id, status_id, reason, expires, now) then return false end
    if not no_update_current then
        local last_id = db:last_insert_rowid()
        code = 'UPDATE player SET current_status_id = ? WHERE id = ?'
        if not execute_bind_one(code, 'update player last status id', last_id, player_id) then return false end
    end
    if status_id ~= data.player_status.default.id and status_id ~= data.player_status.whitelisted.id then
        return data.flag_player(player_id, true)
    end
    return true
end

function data.register_ip(ipint)
    local code = 'INSERT OR IGNORE INTO ip (ip) VALUES (?)'
    return execute_bind_one(code, 'register ip', ipint)
end

local ip_status_cache = {}
function data.get_ip_status(ipint, create_if_new)
    local cached_status = ip_status_cache[ipint]
    if cached_status then return cached_status end
    local code = [[
        SELECT executor.id   executor_id
             , executor.name executor_name
             , status.id     id
             , status.name   name
             , log.timestamp timestamp
             , log.reason    reason
             , log.expires   expires
          FROM ip
          JOIN ip_status_log log      ON ip.current_status_id == log.id
          JOIN ip_status     status   ON log.status_id == status.id
          JOIN player        executor ON log.executor_id == executor.id
         WHERE ip.ip == ?
         LIMIT 1
    ]]
    local table = get_full_ntable(code, 'get ip status', ipint)
    if #table == 1 then
        ip_status_cache[ipint] = table[1]
        return table[1]
    elseif #table > 1 then
        log('error', 'somehow got more than 1 result when getting current ip status for %s', ipint)
        return
    elseif not create_if_new then
        return
    end
    if not data.set_ip_status(ipint, data.verbana_player_id, data.ip_status.default.id, 'creating initial ip status') then
        log('error', 'failed to set initial ip status')
        return
    end
    return data.get_ip_status(ipint, false)
end
function data.set_ip_status(ipint, executor_id, status_id, reason, expires)
    ip_status_cache[ipint] = nil
    local code = [[
        INSERT INTO ip_status_log (ip, executor_id, status_id, reason, expires, timestamp)
             VALUES               (?,  ?,           ?,         ?,      ?,       ?)
    ]]
    local now = os.time()
    if not execute_bind_one(code, 'set ip status', ipint, executor_id, status_id, reason, expires, now) then return false end
    local last_id = db:last_insert_rowid()
    code = 'UPDATE ip SET current_status_id = ? WHERE ip = ?'
    if not execute_bind_one(code, 'update ip last status id', last_id, ipint) then return false end
    return true
end

function data.register_asn(asn)
    local code = 'INSERT OR IGNORE INTO asn (asn) VALUES (?)'
    return execute_bind_one(code, 'register asn', asn)
end

local asn_status_cache = {}
function data.get_asn_status(asn, create_if_new)
    local cached_status = asn_status_cache[asn]
    if cached_status then return cached_status end
    local code = [[
        SELECT executor.id   executor_id
             , executor.name executor_name
             , status.id     id
             , status.name   name
             , log.timestamp timestamp
             , log.reason    reason
             , log.expires   expires
          FROM asn
          JOIN asn_status_log log      ON asn.current_status_id == log.id
          JOIN asn_status     status   ON log.status_id == status.id
          JOIN player         executor ON log.executor_id == executor.id
         WHERE asn.asn == ?
         LIMIT 1
    ]]
    local table = get_full_ntable(code, 'get asn status', asn)
    if #table == 1 then
        asn_status_cache[asn] = table[1]
        return table[1]
    elseif #table > 1 then
        log('error', 'somehow got more than 1 result when getting current asn status for %s', asn)
        return
    elseif not create_if_new then
        return
    end
    if not data.set_asn_status(asn, data.verbana_player_id, data.asn_status.default.id, 'creating initial asn status') then
        log('error', 'failed to set initial asn status')
        return
    end
    return data.get_asn_status(asn, false)
end
function data.set_asn_status(asn, executor_id, status_id, reason, expires)
    asn_status_cache[asn] = nil
    local code = [[
        INSERT INTO asn_status_log (asn, executor_id, status_id, reason, expires, timestamp)
             VALUES                (?,   ?,           ?,         ?,      ?,       ?)
    ]]
    local now = os.time()
    if not execute_bind_one(code, 'set asn status', asn, executor_id, status_id, reason, expires, now) then return false end
    local last_id = db:last_insert_rowid()
    code = 'UPDATE asn SET current_status_id = ? WHERE asn = ?'
    if not execute_bind_one(code, 'update asn last status id', last_id, asn) then return false end
    return true
end

function data.log(player_id, ipint, asn, success)
    local code = [[
        INSERT INTO connection_log (player_id, ip, asn, success, timestamp)
             VALUES                 (?,         ?,  ?,   ?,       ?)
    ]]
    local now = os.time()
    if not execute_bind_one(code, 'log connection', player_id, ipint, asn, success, now) then
        return false
    end
    if success then
        local last_login_id = db:last_insert_rowid()
        code = [[
            UPDATE player
               SET last_login_id = ?
             WHERE id = ?
        ]]
        if not execute_bind_one(code, 'set last login', last_login_id, player_id) then
            return false
        end
    end
    return true
end

function data.assoc(player_id, ipint, asn)
    player_id = data.get_master(player_id) or player_id
    local insert_code = [[
        INSERT OR IGNORE INTO assoc (player_id, ip, asn, first_seen, last_seen)
                             VALUES (?,         ?,  ?,   ?,          ?)
    ]]
    local now = os.time()
    if not execute_bind_one(insert_code, 'insert assoc', player_id, ipint, asn, now, now) then return false end
    local update_code = [[
        UPDATE assoc
           SET last_seen = ?
         WHERE player_id == ?
           AND ip == ?
           AND asn == ?
    ]]
    if not execute_bind_one(update_code, 'update assoc', now, player_id, ipint, asn) then return false end
    return true
end
function data.has_asn_assoc(player_id, asn)
    player_id = data.get_master(player_id) or player_id
    local code = 'SELECT 1 FROM assoc WHERE player_id = ? AND asn == ? LIMIT 1'
    local table = get_full_table(code, 'find player asn assoc', player_id, asn)
    return #table == 1
end
function data.has_ip_assoc(player_id, ipint)
    player_id = data.get_master(player_id) or player_id
    local code = 'SELECT 1 FROM assoc WHERE player_id = ? AND ip == ? LIMIT 1'
    local table = get_full_table(code, 'find player asn assoc', player_id, ipint)
    return #table == 1
end

function data.get_player_status_log(player_id)
    player_id = data.get_master(player_id) or player_id
    local code = [[
        SELECT executor.name executor_name
             , log.status_id status_id
             , log.timestamp timestamp
             , log.reason    reason
             , log.expires   expires
          FROM player_status_log log
          JOIN player                     ON log.player_id   == player.id
          JOIN player            executor ON log.executor_id == executor.id
         WHERE player.id == ?
      ORDER BY log.timestamp
    ]]
    return get_full_ntable(code, 'player status log', player_id)
end
function data.get_ip_status_log(ipint)
    local code = [[
        SELECT executor.name executor_name
             , log.status_id status_id
             , log.timestamp timestamp
             , log.reason    reason
             , log.expires   expires
          FROM ip_status_log log
          JOIN player        executor ON log.executor_id == executor.id
         WHERE log.ip == ?
      ORDER BY log.timestamp
    ]]
    return get_full_ntable(code, 'ip status log', ipint)
end
function data.get_asn_status_log(asn)
    local code = [[
        SELECT executor.name executor_name
             , log.status_id status_id
             , log.timestamp timestamp
             , log.reason    reason
             , log.expires   expires
          FROM asn_status_log log
          JOIN player         executor ON log.executor_id == executor.id
         WHERE log.asn == ?
      ORDER BY log.timestamp
    ]]
    return get_full_ntable(code, 'asn status log', asn)
end

function data.get_first_login(player_id)
    local code = [[
        SELECT timestamp
          FROM connection_log
         WHERE player_id == ?
      ORDER BY timestamp
         LIMIT 1
    ]]
    return get_full_ntable(code, 'first login', player_id)
end
function data.get_player_connection_log(player_id, limit)
    local code = [[
        SELECT log.ip                   ipint
             , log.asn                  asn
             , log.success              success
             , log.timestamp            timestamp
             , ip_status_log.status_id  ip_status_id
             , asn_status_log.status_id asn_status_id
          FROM connection_log log
          JOIN player         ON player.id         == log.player_id
          JOIN ip             ON ip.ip             == log.ip
     LEFT JOIN ip_status_log  ON ip_status_log.id  == ip.current_status_id
          JOIN asn            ON asn.asn           == log.asn
     LEFT JOIN asn_status_log ON asn_status_log.id == asn.current_status_id
         WHERE player.id == ?
      ORDER BY timestamp DESC
         LIMIT ?
    ]]
    if not limit or type(limit) ~= 'number' or limit < 0 then
        limit = 20
    end
    local t = get_full_ntable(code, 'player connection log', player_id, limit)
    return util.table_reversed(t)
end
function data.get_ip_connection_log(ipint, limit)
    local code = [[
        SELECT player.name                 player_name
             , player.id                   player_id
             , log.asn                     asn
             , log.success                 success
             , log.timestamp               timestamp
             , player_status_log.status_id player_status_id
             , asn_status_log.status_id    asn_status_id
          FROM connection_log log
          JOIN player            ON player.id == log.player_id
     LEFT JOIN player_status_log ON player_status_log.id == player.current_status_id
          JOIN asn               ON asn.asn == log.asn
     LEFT JOIN asn_status_log    ON asn.current_status_id == asn_status_log.id
         WHERE log.ip == ?
      ORDER BY timestamp DESC
         LIMIT ?
    ]]
    if not limit or type(limit) ~= 'number' or limit < 0 then
        limit = 20
    end
    local t = get_full_ntable(code, 'ip connection log', ipint, limit)
    return util.table_reversed(t)
end
function data.get_asn_connection_log(asn, limit)
    local code = [[
        SELECT player.name                 player_name
             , player.id                   player_id
             , log.ip                      ipint
             , log.success                 success
             , log.timestamp               timestamp
             , player_status_log.status_id player_status_id
             , ip.current_status_id        ip_status_id
          FROM connection_log log
          JOIN player            ON player.id == log.player_id
     LEFT JOIN player_status_log ON player_status_log.id == player.current_status_id
          JOIN ip                ON ip.ip == log.ip
     LEFT JOIN ip_status_log     ON ip.current_status_id == ip_status_log.id
         WHERE log.asn == ?
      ORDER BY timestamp DESC
         LIMIT ?
    ]]
    if not limit or type(limit) ~= 'number' or limit < 0 then
        limit = 20
    end
    local t = get_full_ntable(code, 'asn connection log', asn, limit)
    return util.table_reversed(t)
end

function data.get_network_connection_log(asn, limit)
    local code = [[
        SELECT player.name                 player_name
             , player.id                   player_id
             , log.ip                      ipint
             , log.success                 success
             , log.timestamp               timestamp
             , player_status_log.status_id player_status_id
             , ip.current_status_id        ip_status_id
          FROM connection_log log
          JOIN player            ON player.id == log.player_id
     LEFT JOIN player_status_log ON player_status_log.id == player.current_status_id
          JOIN ip                ON ip.ip == log.ip
     LEFT JOIN ip_status_log     ON ip.current_status_id == ip_status_log.id
         WHERE log.asn == ?
      ORDER BY timestamp DESC
         LIMIT ?
    ]]
    if not limit or type(limit) ~= 'number' or limit < 0 then
        limit = 20
    end
    local t = get_full_ntable(code, 'asn connection log', asn, limit)
    return util.table_reversed(t)
end

function data.get_player_associations(player_id)
    local code = [[
        SELECT assoc.ip                 ipint
             , assoc.asn                asn
             , ip_status_log.status_id  ip_status_id
             , asn_status_log.status_id asn_status_id
          FROM assoc
          JOIN player         ON player.id == assoc.player_id
          JOIN ip             ON ip.ip == assoc.ip
     LEFT JOIN ip_status_log  ON ip.current_status_id == ip_status_log.id
          JOIN asn            ON asn.asn == assoc.asn
     LEFT JOIN asn_status_log ON asn.current_status_id == asn_status_log.id
         WHERE player.id == ?
      ORDER BY assoc.asn, assoc.ip
    ]]
    return get_full_ntable(code, 'player associations', player_id)
end
function data.get_ip_associations(ipint, from_time)
    local code = [[
        SELECT
      DISTINCT player.name                 player_name
             , player_status_log.status_id player_status_id
          FROM assoc
          JOIN connection_log USING (ip, asn)
          JOIN player            ON player.id == assoc.player_id
     LEFT JOIN player_status_log ON player_status_log.id == player.current_status_id
         WHERE assoc.ip == ?
           AND connection_log.timestamp >= ?
      ORDER BY LOWER(player.name)
    ]]
    return get_full_ntable(code, 'ip associations', ipint, from_time)
end
function data.get_asn_associations(asn, from_time)
    local code = [[
        SELECT
      DISTINCT player.name                 player_name
             , player_status_log.status_id player_status_id
             , last_log.ip                 ipint
             , last_log.asn                asn
          FROM assoc
          JOIN connection_log       USING (ip, asn)
          JOIN player                  ON player.id == assoc.player_id
     LEFT JOIN player_status_log       ON player_status_log.id == player.current_status_id
     LEFT JOIN connection_log last_log ON last_log.id == player.last_login_id
         WHERE assoc.asn == ?
           AND connection_log.timestamp >= ?
           AND player.flagged == 1
      ORDER BY LOWER(player.name)
    ]]
    return get_full_ntable(code, 'asn associations', asn, from_time)
end

function data.get_player_cluster(player_id)
    local code = [[
        SELECT
      DISTINCT other.name                  player_name
             , player_status_log.status_id player_status_id
             , connection_log.ip           ipint
             , connection_log.asn          asn
          FROM player
          JOIN assoc player_assoc ON player_assoc.player_id == player.id
          JOIN assoc other_assoc  ON other_assoc.ip == player_assoc.ip
          JOIN player other       ON other.id == other_assoc.player_id
     LEFT JOIN player_status_log  ON player_status_log.id == other.current_status_id
     LEFT JOIN connection_log     ON connection_log.id == player.last_login_id
         WHERE player.id == ?
           AND player.id != other_assoc.player_id
      ORDER BY LOWER(other.name)
    ]]
    return get_full_ntable(code, 'player cluster', player_id)
end

function data.get_all_banned_players()
    local code = [[
        SELECT player.name                 player_name
             , player_status_log.status_id player_status_id
             , player_status_log.reason    reason
             , player_status_log.expires   expires
          FROM player
     LEFT JOIN player_status_log ON player.id == player_status_log.player_id
         WHERE player_status_log.status_id == ?
    ]]
    return get_full_ntable(code, 'all banned',
        data.player_status.banned
    )
end

function data.fumble_about_for_an_ip(name, player_id)
    -- for some reason, get_player_ip is unreliable during register_on_newplayer
    local ipstr = minetest.get_player_ip(name)
    if not ipstr then
        local info = minetest.get_player_information(name)
        if info then
            ipstr = info.address
        end
    end
    if not ipstr then
        if not player_id then player_id = data.get_player_id(name) end
        local connection_log = data.get_player_connection_log(player_id, 1)
        if not connection_log or #connection_log ~= 1 then
            log('warning', 'player %s exists but has no connection log?', player_id)
        else
            local last_login = connection_log[1]
            ipstr = lib_ip.ipint_to_ipstr(last_login.ipint)
        end
    end
    return ipstr
end

function data.get_ban_log(limit)
    local code = [[
        SELECT player.name                 player_name
             , executor.name               executor_name
             , player_status_log.status_id status_id
             , player_status_log.timestamp timestamp
             , player_status_log.reason    reason
             , player_status_log.expires   expires
          FROM player_status_log
          JOIN player          ON player.id        == player_status_log.player_id
          JOIN player executor ON executor.id      == player_status_log.executor_id
         WHERE executor.id != ?
         ORDER BY player_status_log.timestamp DESC
         LIMIT ?
    ]]
    if not limit or type(limit) ~= 'number' or limit < 0 then limit = 20 end
    return get_full_ntable(code, 'ban log',
        data.verbana_player_id,
        limit
    )
end

function data.add_report(reporter_id, report)
    local code = [[
        INSERT INTO report
                    (reporter_id, report, timestamp)
             VALUES (?          , ?     , ?        )
    ]]
    local now = os.time()
    return execute_bind_one(code, 'add report', reporter_id, report, now)
end

function data.get_reports(from_time)
    local code = [[
        SELECT player.name      reporter
             , report.report    report
             , report.timestamp timestamp
          FROM report
          JOIN player ON player.id == report.reporter_id
         WHERE report.timestamp >= ?
    ]]
    return get_full_ntable(code, 'get reports', from_time)
end

function data.get_asn_stats(asn)
    local code = [[
        SELECT COALESCE(player_status_log.status_id, ?)        player_status_id
             , COUNT(COALESCE(player_status_log.status_id, ?)) count
          FROM (SELECT DISTINCT player_id id FROM assoc WHERE asn = ?) asn_player
          JOIN player            ON player.id            == asn_player.id
     LEFT JOIN player_status_log ON player_status_log.id == player.current_status_id
      GROUP BY COALESCE(player_status_log.status_id, ?)
      ORDER BY COALESCE(player_status_log.status_id, ?)
    ]]
    return get_full_ntable(code, 'asn stats',
        data.player_status.default.id,
        data.player_status.default.id,
        asn,
        data.player_status.default.id,
        data.player_status.default.id
    )
end

function data.get_master(player_id)
    local code = [[
        SELECT master.id   id
             , master.name name
          FROM player
     LEFT JOIN player master ON master.id == player.master_id
         WHERE player.id = ?
    ]]
    local rows = get_full_ntable(code, 'get_master', player_id)
    if rows and #rows > 0 then
        return rows[1].id, rows[1].name
    end
end

function data.set_master(player_id, master_id)
    --[[
        case 1: master has no master
                just set player's master
        case 2: master has a master
                subcase A: player and master's master are different
                    set player's master to master's master
                subcase B: player == master's master
                    swap player and master
        if other players have player as their master, update their master to player's new master.
        loops can not be created this way, because we've ensured that a "true" master can't have
        a master of its own.
    ]]
    local master_master_id = data.get_master(master_id)
    if master_master_id == player_id then
        return data.swap_master(player_id, master_id)
    elseif master_master_id then
        master_id = master_master_id
    end
    local code = [[
        UPDATE player
           SET master_id = ?
         WHERE id = ?
    ]]
    if not execute_bind_one(code, 'set master 1', master_id, player_id) then
        return false, 'error'
    end
    code = [[
        UPDATE player
           SET master_id = ?
         WHERE master_id = ?
    ]]
    if not execute_bind_one(code, 'set master 2', master_id, player_id) then
        return false, 'error'
    end
    return true
end

function data.swap_master(player_id, master_id)
    if data.get_master(player_id) ~= master_id then
        return false, 'not player\'s master'
    end
    local code = [[
        UPDATE player
           SET master_id = ?
         WHERE id = ?
    ]]
    if not execute_bind_one(code, 'swap master 1', nil, player_id) then
        return false, 'error'
    end
    if not execute_bind_one(code, 'swap master 2', player_id, master_id) then
        return false, 'error'
    end
    code = [[
        UPDATE player
           SET master_id = ?
         WHERE master_id = ?
    ]]
    if not execute_bind_one(code, 'swap master 3', player_id, master_id) then
        return false, 'error'
    end
    return true
end

function data.unset_master(player_id)
    local code = [[
        UPDATE player
           SET master_id = NULL
         WHERE id = ?
    ]]
    return execute_bind_one(code, 'unset master', player_id)
end

function data.get_alts(player_id)
    local code = [[
        SELECT master.name name
          FROM player master
         WHERE master.id == ?
         UNION
        SELECT alt.name
          FROM player master
          JOIN player alt    ON alt.master_id == master.id
         WHERE master.id == ?
    ]]
    local master_id = data.get_master(player_id) or player_id
    local rows = get_full_ntable(code, 'get alts', master_id, master_id)
    if rows then
        local alts = {}
        for _, row in ipairs(rows) do
            table.insert(alts, row.name)
        end
        return alts
    end
end

function data.grep_player(pattern, limit)
    local code = [[
        SELECT player.name                 name
             , player_status_log.status_id player_status_id
             , last_log.ip                 ipint
             , last_log.asn                asn
          FROM player
     LEFT JOIN player_status_log       ON player_status_log.id == player.current_status_id
     LEFT JOIN connection_log last_log ON last_log.id == player.last_login_id
         WHERE LOWER(player.name) GLOB LOWER(?)
      ORDER BY LOWER(player.name)
         LIMIT ?
    ]]
    return get_full_ntable(code, 'grep player', pattern, limit)
end