practice-tracker-api-node/server.js

const express = require('express')
const mysql = require('mysql-await');
const app = express()
const cors = require('cors');

app.use(cors());
app.use(express.json());

const mysqlConnection = mysql.createConnection({
	host: process.env.DB_HOST,
	user: process.env.DB_USER,
	password: process.env.DB_PWD,
	database: process.env.DB_NAME
});
mysqlConnection.connect()

app.get('/songs', async (req, res)=>{
	const results = await mysqlConnection.awaitQuery("SELECT * FROM songs WHERE learned=True ORDER BY title ASC");
	res.json(results)
})

app.get('/instruments', async (req, res)=>{
	const results = await mysqlConnection.awaitQuery("SELECT * FROM instruments");
	res.json(results)
})

app.get('/categories', async (req, res)=>{
	const results = await mysqlConnection.awaitQuery("SELECT practice_categories.id, practice_categories.name AS category, instruments.name AS instrument FROM practice_categories JOIN instruments ON practice_categories.instrument_id = instruments.id ORDER BY practice_categories.id ASC");
	res.json(results)
})

app.get('/summary', async (req, res)=>{
	const results = await mysqlConnection.awaitQuery("SELECT * FROM summary ORDER BY chunks_practiced ASC");
	res.json(results)
})

app.get('/status', async (req, res)=>{
	const results = await mysqlConnection.awaitQuery("SELECT * FROM practice_status");
	res.json({
		seconds_practiced:results[0].seconds_practiced_today || 0,
		seconds_left_to_practice_today:results[0].seconds_left || 0,
		seconds_left_to_get_ahead:results[0].seconds_left_to_get_ahead || 0
	})
})

app.get('/show-category/:id', async (req, res) => {

	const query = `SELECT
			practice_sessions.id,
			practice_sessions.description,
			seconds,
			songs.id AS song_id,
			songs.title,
			comments,
			created_at,
			practice_categories.name AS category,
			instruments.name AS instrument
		 FROM practice_sessions
			JOIN practice_categories ON practice_category_id = practice_categories.id
			JOIN instruments ON instrument_id = instruments.id
			LEFT JOIN songs_practiced ON practice_sessions.id = songs_practiced.practice_session_id
			LEFT JOIN songs ON songs_practiced.song_id = songs.id
		 WHERE practice_category_id = ?
		 ORDER BY practice_sessions.id DESC`;

	const results = await mysqlConnection.awaitQuery(query, [req.params.id])

	const entries = [];
	let previousSessionId = 0;

	results.forEach(row => {
		if (row.id !== previousSessionId) {
			entries.push({
				id:row.id,
				description:row.description,
				seconds:row.seconds,
				comments:row.comments,
				created_at:row.created_at,
				category:row.category,
				instrument:row.instrument,
				songs:[]
			})
			previousSessionId = row.id
		}
		if (row.song_id !== null) {
			entries[entries.length-1].songs.push({
				id: row.song_id,
				title: row.title
			});
		}

	});

	res.json(entries);
});

app.post('/sessions', async (req, res) => {
	const { description, seconds, comments, practice_category_id, songs } = req.body;

	let query, params;

	if (comments) {
		query = 'INSERT INTO practice_sessions (description, seconds, comments, practice_category_id) VALUES (?, ?, ?, ?)';
		params = [description, seconds, comments, practice_category_id];
	} else {
		query = 'INSERT INTO practice_sessions (description, seconds, practice_category_id) VALUES (?, ?, ?)';
		params = [description, seconds, practice_category_id];
	}

	const results = await mysqlConnection.awaitQuery(query, params)
	const newSessionId = results.insertId;

	if (songs && songs.length > 0) {
		const songPromises = songs.map(song => {
			return new Promise((resolve, reject) => {
				const songQuery = 'INSERT INTO songs_practiced (practice_session_id, song_id) VALUES (?, ?)';
				mysqlConnection.query(songQuery, [newSessionId, song.id], (err) => {
					if (err) reject(err);
					else resolve();
				});
			});
		});

		Promise.all(songPromises)
			.then(() => {
				res.json({ status: 'success' });
			})
			.catch(err => {
				res.status(500).json({ error: err.message });
			});
	} else {
		res.json({ status: 'success' });
	}
});

app.listen(18080)