97 lines
2.6 KiB
JavaScript
97 lines
2.6 KiB
JavaScript
// db.js
|
|
const mysql = require('mysql2/promise');
|
|
const path = require('path');
|
|
require('dotenv').config({ path: path.join(__dirname, '.env') });
|
|
|
|
const {
|
|
DB_HOST = '127.0.0.1',
|
|
DB_PORT = '3306',
|
|
DB_USER = 'eaglertiers',
|
|
DB_PASSWORD = 'eagler_local_dev_2026',
|
|
DB_NAME = 'eaglertiers',
|
|
} = process.env;
|
|
|
|
// Create a connection pool (used by the app)
|
|
const pool = mysql.createPool({
|
|
host: DB_HOST,
|
|
port: Number(DB_PORT),
|
|
user: DB_USER,
|
|
password: DB_PASSWORD,
|
|
database: DB_NAME,
|
|
waitForConnections: true,
|
|
connectionLimit: 10,
|
|
queueLimit: 0,
|
|
enableKeepAlive: true,
|
|
keepAliveInitialDelay: 0,
|
|
});
|
|
|
|
/**
|
|
* Ensures the database and required tables exist.
|
|
* Call this once during server startup.
|
|
*/
|
|
async function ensureDatabase() {
|
|
// First, connect without database to create it if necessary
|
|
const rootPool = mysql.createPool({
|
|
host: DB_HOST,
|
|
port: Number(DB_PORT),
|
|
user: DB_USER,
|
|
password: DB_PASSWORD,
|
|
connectionLimit: 1,
|
|
});
|
|
|
|
try {
|
|
// Create database if it doesn't exist
|
|
await rootPool.query(`CREATE DATABASE IF NOT EXISTS \`${DB_NAME}\``);
|
|
console.log(`Database "${DB_NAME}" ensured.`);
|
|
|
|
// Now use the main pool (with database selected) to create tables
|
|
const connection = await pool.getConnection();
|
|
|
|
// Create players table
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS players (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(16) UNIQUE NOT NULL,
|
|
region VARCHAR(2) NOT NULL DEFAULT 'NA',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Create player_ranks table
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS player_ranks (
|
|
player_id INT NOT NULL,
|
|
gamemode VARCHAR(20) NOT NULL,
|
|
tier VARCHAR(3) NOT NULL,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (player_id, gamemode),
|
|
FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Create admin_users table (if you haven't already)
|
|
await connection.query(`
|
|
CREATE TABLE IF NOT EXISTS admin_users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','superadmin') DEFAULT 'admin',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
connection.release();
|
|
console.log('All tables ensured.');
|
|
} catch (err) {
|
|
console.error('Failed to ensure database:', err);
|
|
throw err;
|
|
} finally {
|
|
await rootPool.end();
|
|
}
|
|
}
|
|
|
|
module.exports = {
|
|
pool,
|
|
ensureDatabase,
|
|
};
|