// SPDX-FileCopyrightText: 2025 Jonas Smedegaard <dr@jones.dk>
// SPDX-FileCopyrightText: 2025 <Alexander Marthin Klemensen stud-marthin@ruc.dk>
// SPDX-FileCopyrightText: 2025 <Ian Valentin Christensen stud-ianc@ruc.dk>
// SPDX-FileCopyrightText: 2025 <Zahed Noos zahed@ruc.dk>
// SPDX-License-Identifier: GPL-3.0-or-later

package com.example.portfolio2;

import java.util.ArrayList;
import java.util.List;

/// Bachelorizer - database model
///
/// This model handles all interaction with the database.
class Database {

	/// database singleton
	private MyDB db = new MyDB();

	/// default constructor
	// (declared explicitly only to silence javadoc)
	Database() { }

	/// clear the participation database at program launch
	void initialize() {
		clearParticipation();
	}

	/// resolve activity index from name
	///
	/// @param name  activity name
	/// @return      index of activity as integer
	int getActivityIndeks(final String name) {
		if (name == null) {
			return -1;
		}
		ArrayList<String> result = db.query(
			"SELECT indeks FROM activity"
			+ " WHERE name IS '" + name + "';",
			"indeks");

		return Integer.parseInt(result.getFirst());
	}

	/// insert activity into participation
	///
	/// @param activityIndex  index of activity
	void addParticipation(final int activityIndex) {
		db.cmd("INSERT INTO participation VALUES(123, "
			+ activityIndex + ");");
	}

	/// list currently participating activities
	///
	/// @param program  programme name
	/// @return         names of participating activities
	ArrayList<String> getParticipation(final String program) {
		return db.query(
			"SELECT name FROM participation p"
			+ " INNER JOIN activity a ON p.indeks = a.indeks"
			+ " WHERE program IS '" + program + "';",
			"name");
	}

	/// purge participation database
	void clearParticipation() {
		db.cmd("DELETE FROM participation");
	}

	/// list activities within a program
	///
	/// @param program  programme name
	/// @return         names of contained activities
	ArrayList<String> selectProgram(final String program) {
		return db.query(
			"SELECT name FROM activity"
			+ " WHERE program IS '" + program + "';",
			"name");
	}

	/// sum of ECTS points under the given category
	///
	/// @param program  programme name
	/// @return         ECTS points as String
	String getSumEcts(final String program) {
		if (program == null) {
			return "0";
		}
		ArrayList<String> result = db.query(
			"SELECT SUM(activity.ects)"
			+ " AS total_ects,student.name"
			+ " FROM student LEFT OUTER JOIN participation"
			+ " ON student.studid = participation.studid"
			+ " INNER JOIN activity"
			+ " ON participation.indeks = activity.indeks"
			+ " WHERE program IS '" + program + "'"
			+ " GROUP BY student.studid ;",
			"total_ects");
		if (result.isEmpty()) {
			return "0";
		}

		return result.getFirst();
	}

	/// list of available subject modules
	///
	/// @return  names of all subject modules as list of strings
	List<String> getAllSubjects() {
		return db.query(
			"SELECT DISTINCT program FROM activity"
			+ " WHERE program NOT IN ("
			+ "   SELECT program from activity"
			+ "   WHERE name LIKE 'BP1 %')",
			"program");
	}

	/// list of available activities
	///
	/// @return  names of all activities as list of strings
	ArrayList<String> getAllActivities() {
		return db.query("SELECT name FROM activity;", "name");
	}
}