package de.jeyp91.tippliga;

import de.jeyp91.StatusHolder;
import de.jeyp91.tippliga.TLWMatch;
import de.jeyp91.tippliga.TLWTeam;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import javax.xml.transform.Result;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TippligaSQLConnector {

    Connection con;

    private static final Logger logger = LogManager.getLogger(TippligaSQLConnector.class);

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.error("Unable to load MySQL Driver");
            StatusHolder.setError();
        }
    }

    public TippligaSQLConnector() {
        String jdbcUrlLocalhost = "jdbc:mysql://localhost/d0144ddb?user=root&password=&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
        String jdbcUrlProd = "jdbc:mysql://dd16124.kasserver.com/d0144ddb?user=d0144ddb&password=TippligaWuerzb_1&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
        try {
            con = DriverManager.getConnection(jdbcUrlProd);
        } catch (SQLException e) {
            /* TODO */
            e.printStackTrace();
        }
    }

    public ArrayList<TLWTeam> getTeams(String season, String league) {
        String queryString = "SELECT * FROM `phpbb_footb_teams` WHERE `season` = " + season + " AND `league` = " + league + ";";

        ArrayList<TLWTeam> teams = new ArrayList<>();
        ResultSet rset = executeQuery(queryString);
        try {
            while (rset.next()) {
                teams.add(new TLWTeam(rset));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return teams;
    }

    public ArrayList<TLWTeam> getTeams(String id) {
        String queryString = "SELECT * FROM `phpbb_footb_teams` WHERE `team_id` = " + id + " ORDER BY `season` DESC, `league` ASC;";

        ArrayList<TLWTeam> teams = new ArrayList<>();
        ResultSet rset = executeQuery(queryString);
        try {
            while (rset.next()) {
                teams.add(new TLWTeam(rset));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return teams;
    }

    private ArrayList<TLWMatch> getMatches(String queryString) {

        ArrayList<TLWMatch> matches = new ArrayList<>();
        ResultSet rset = executeQuery(queryString);
        try {
            while (rset.next()) {
                matches.add(new TLWMatch(rset));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return matches;
    }

    public ArrayList<TLWMatch> getMatches(String season, String league) {
        String queryString = "SELECT * FROM `phpbb_footb_matches` WHERE `season` = " + season + " AND `league` = " + league + ";";
        return getMatches(queryString);
    }

    public ArrayList<TLWMatch> getMatches(String season, String league, String matchday) {
        String queryString = "SELECT * FROM `phpbb_footb_matches` WHERE `season` = " + season + " AND `league` = " + league + " AND `matchday` = " + matchday + ";";
        return getMatches(queryString);
    }

    public ArrayList<TLWMatchday> getMatchdays(String season, String league) {
        String queryString = "SELECT * FROM `phpbb_footb_matchdays` WHERE `season` = " + season + " AND `league` = " + league + ";";

        ArrayList<TLWMatchday> matchdays = new ArrayList<>();
        ResultSet rset = executeQuery(queryString);
        try {
            while (rset.next()) {
                matchdays.add(new TLWMatchday(rset));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return matchdays;
    }

    public void updateMatchDateTime(String season, String league, String matchNo, String datetime) {
        String queryString = "UPDATE `phpbb_footb_matches` "
                + "SET match_datetime = " + datetime
                + " WHERE `season` = " + season
                + " AND `league` = " + league
                + " AND match_no = " + matchNo + ";";
        executeQuery(queryString);
    }

    public ResultSet executeQuery (String queryString){
        Statement stmt;
        ResultSet rset = null;
        try {
            stmt = con.createStatement();
            rset = stmt.executeQuery(queryString);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rset;
    }
}
