package dynamic.degrees.old_database_sql_based;

import dynamic.common.ArrayListHelper;
import dynamic.common.Parser;
import dynamic.common.SqlExtractor;

import java.io.IOException;
import java.util.ArrayList;

public class OldDegreeInfoGetter {

    public static ArrayList<ArrayList<String>> GetDegreeInfo(String degree_code) throws IOException {

        String sql = "SELECT A.id, A.degreeid, A.yr, A.degree, A.degreeyr, A.major, A.majoryr, A.unused, A.examclass, A.letter, A.letteryr, X.required, X.examcode, X.popregistered, X.poplastyear, B.id, B.title, B.code, B.term, B.lecturehours, B.tutorialhours, B.labhours, B.weeklyhours, B.popestimate, B.popregistered, B.poplastyear, B.classes, B.helperstotal, B.url, B.notes FROM class as A, xcourseclass as X, course as B WHERE A.validfrom<=now() and A.validto >= now() and X.validfrom<=now() and X.validto >= now() and B.validfrom<=now() and B.validto >= now() and A.id = X.classid and B.id = X.courseid";
        sql += " and degree = '" + degree_code + "' ";

        String res = SqlExtractor.dbRequest(sql);
        return Parser.GetData(res);

    }


    public static ArrayList<Integer> GetDegreeYears(String degree_code) throws IOException {
        String sql = "SELECT distinct yr FROM class WHERE validfrom<=now() and validto >= now()";
        sql += " and degree = '" + degree_code + "' ";
        sql += " order by yr";

        String res = SqlExtractor.dbRequest(sql);
        ArrayList<ArrayList<String>> data = Parser.GetData(res);
        ArrayList<String> strings = ArrayListHelper.FlattenArrayList(data);
        return ArrayListHelper.ConvertStringToInteger(strings);

    }

    public static String GetDegreeTitle(String degree_code) throws IOException {

        String sql = "SELECT title FROM degree WHERE validfrom<=now() and validto >= now()";
        sql += " and code = '" + degree_code + "' ";

        String res = SqlExtractor.dbRequest(sql);
        ArrayList<ArrayList<String>> data = Parser.GetData(res);
        return ArrayListHelper.FlattenArrayList(data).get(0);

    }


    private static ArrayList<ArrayList<String>> GetCoursesByCompulsion(String degree_code, int year, String compulsion) throws IOException {


        String sql = "SELECT B.code, B.title, B.term FROM class as A, xcourseclass as X, course as B WHERE A.validfrom<=now() and A.validto >= now() and X.validfrom<=now() and X.validto >= now() and B.validfrom<=now() and B.validto >= now() and A.id = X.classid and B.id = X.courseid";
        sql += " and degree = '" + degree_code + "' and yr = '" + year + "' and required = '" + compulsion + "' order by B.code";

        String res = SqlExtractor.dbRequest(sql);
        return Parser.GetData(res);

    }

    public static ArrayList<ArrayList<String>> GetCompulsoryCourses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Required");
    }

    public static ArrayList<ArrayList<String>> GetOptionalCourses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Optional");
    }

    public static ArrayList<ArrayList<String>> GetOptional1Courses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Optional1");
    }

    public static ArrayList<ArrayList<String>> GetOptional2Courses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Optional2");
    }

    public static ArrayList<ArrayList<String>> GetAllOptionalCourses(String degree_code, int year) throws IOException {
        ArrayList<ArrayList<String>> all = new ArrayList<ArrayList<String>>();
        all.addAll(GetOptionalCourses(degree_code, year));
        all.addAll(GetOptional1Courses(degree_code, year));
        all.addAll(GetOptional2Courses(degree_code, year));
        ArrayListHelper.SortBasedOnFirstElement(all);
        return all;
    }


    public static ArrayList<ArrayList<String>> GetSelectiveCourses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Selective");
    }

    public static ArrayList<ArrayList<String>> GetSelective1Courses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Selective1");
    }

    public static ArrayList<ArrayList<String>> GetSelective2Courses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Selective2");
    }

    public static ArrayList<ArrayList<String>> GetSelective3Courses(String degree_code, int year) throws IOException {
        return GetCoursesByCompulsion(degree_code, year, "Selective3");
    }

    public static ArrayList<ArrayList<String>> GetAllSelectiveCourses(String degree_code, int year) throws IOException {
        ArrayList<ArrayList<String>> all = new ArrayList<ArrayList<String>>();
        all.addAll(GetSelectiveCourses(degree_code, year));
        all.addAll(GetSelective1Courses(degree_code, year));
        all.addAll(GetSelective2Courses(degree_code, year));
        ArrayListHelper.SortBasedOnFirstElement(all);
        return all;
    }

}