path: root/src/com.example.portfolio2/com/example/portfolio2/Database.java

// 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");
    }
}