const mariadb = require('mariadb');
let pool

function init(dbConfig){
    pool = mariadb.createPool({
        host: dbConfig.host,
        user: dbConfig.user,
        password: dbConfig.password,
        database: dbConfig.database,
        connectionLimit: 5,
    });

    return { 
        query, 
        getUser,
        getUserid,
        getUserAccess,
        getVMList,
        getVM,
        getVMInfo
    }
}

async function query(sql, params = []){
    let conn;
    try {
        conn = await pool.getConnection();
        const rows = await conn.query(sql, params);
        return rows
    } catch (err){
        console.error('DB query error:', err);
        throw err;
    } finally{
        if (conn) conn.release();
    }
};

async function getUser(username){
    const result = await query(`
        SELECT 1 
        FROM users 
        WHERE username = ?        
    `, [username]);
    return !!result;
};

async function getUserid(username) {
    const result = await query(`
        SELECT userid 
        FROM users 
        WHERE username = ?        
    `, [username]);
    return result[0]?.userid || null;
};

async function getVMList(userid) {
    const result = await query(`
      SELECT v.hostname
      FROM vms v
      JOIN user_access ua ON v.hostname = ua.hostname
      JOIN users u ON ua.userid = u.userid
      WHERE u.userid = ?
    `, [userid]);
    return result;
  }

async function getUserAccess(userid, hostname) {
    const result = await query(`
        SELECT 1
        FROM user_access
        WHERE userid = ? AND hostname = ?;
    `, [userid, hostname]);
    return result.length > 0;
};

async function getVM(hostname) {
    const result = await query(`
        SELECT * FROM vms 
        WHERE hostname = ?
    `, [hostname]);
    return result[0];
  }

async function getVMInfo(hostname){
    const result = await query(`
        SELECT *
        FROM vm_info
        WHERE hostname = ?
    `,[hostname]);
    return result[0];
};

module.exports = {
    init
};