database.js

const mysql = require("mysql2/promise");
require("dotenv").config();

// Creo la configurazione per poter connettermi al processo MySql che gira sul PC
const config = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT
};

const dbName = "gioco_sparatoria";
let conn = null;

// Crea la connessione con il server

async function initDatabase(){

    conn = await mysql.createConnection(config);
    console.log("Connesso al server MySql");

    const [rows] = await conn.query("SHOW DATABASES LIKE ?", [dbName]);
    if(rows.length == 0){
        console.log("Il database "+dbName+" non esiste, lo creo...");
        await conn.query(`CREATE DATABASE ${dbName}`);
        console.log("Database "+dbName+" creato");
    } 
    else console.log("Database "+dbName+" già esistente ");

    await conn.changeUser({ database: dbName });

    let createTable = `
      CREATE TABLE IF NOT EXISTS accounts (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(30) UNIQUE NOT NULL,
          password VARCHAR(10) UNIQUE NOT NULL
      );`;
    await conn.query(createTable);
    createTable = `
      CREATE TABLE IF NOT EXISTS games (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name_game VARCHAR(30) UNIQUE NOT NULL,
            state INTEGER NOT NULL,
            result INTEGER NOT NULL,
            team_win VARCHAR(20),
            team_lose VARCHAR(20),
            id_account INT,
            FOREIGN KEY (id_account) REFERENCES accounts(id)
      );`;
    console.log("Tabelle create o già esistenti");
    await conn.query(createTable);

}

// Inserisce un nuovo record nella tabella accounts

async function createAccount(username, password){ 
    
    const sql = "INSERT INTO accounts (username, password) VALUES (?, ?)";
    const [result] = await conn.query(sql, [username, password]);
    console.log("Account creato con successo");
    return await findAccountById(result.insertId);

}

// Cerca account per id

async function findAccountById(id){

    const sql = "SELECT * FROM accounts WHERE id = ?";
    const [row] = await conn.query(sql, [id]);
    return row[0];


}

// Legge e restituisce tutti i record della tabella accounts

async function readAccounts(){

    const [rows] = await conn.query("SELECT * FROM accounts");
    return rows;

}

// Creo un record nella tabella games

async function createGame(nameGame, idAccount){

    const sql = "INSERT INTO games(name_game, state, result, id_account) VALUES (?, ?, ?, ?)";
    const [result] = await conn.query(sql, [nameGame, -1, 0, idAccount]);
    console.log("Creazione della partita");
    return await findGameById(result.insertId);

}

// Aggiorno un record della tabella games per id

async function updateGameById(id, stato, risultato, teamWin, teamLose){

    let sql = "UPDATE games SET state = ?";
    let values = [stato, id];
    if(risultato != "" && teamWin != "" && teamLose !=""){
        sql += ", result = ?, team_win = ?, team_lose = ?";
        values = [stato, risultato, teamWin, teamLose, id];
    }
    sql += " WHERE id = ?";
    await conn.execute(sql, values);

}

// Cerca game per id

async function findGameById(id){

    const sql = "SELECT * FROM games WHERE id = ?";
    const [row] = await conn.query(sql, [id]);
    return row[0];

}

// Legge e restituisce tutti i record della tabella games

async function readGames(){

    const [rows] = await conn.query("SELECT * FROM games");
    return rows;

}

// Elimina un record per id nella tabella games

function deleteGame(id){

    const sql = "DELETE FROM games WHERE id = ?";
    conn.query(sql, [id]);

}

module.exports = {initDatabase, createAccount, readAccounts, createGame, updateGameById, readGames, deleteGame};