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

package com.example.portfolio2;

import java.util.ArrayList;

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

	/// database singleton
	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(String name) {
		if(name ==null) return -1;
		ArrayList<String> result = db.query("select indeks from activity a where name is '"+name+"';", "indeks");
		return Integer.parseInt(result.getFirst());

	}

	/// insert activity into participation
	///
	/// @param activityIndex  index of activity
	void addParticipation(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(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(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(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();
	}

	/// sum of ECTS points under the given category
	///
	/// @return         names of all activities as list of String
	ArrayList<String> getAllActivities() {
		return db.query("select name from activity;", "name");
	}
}