/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseassignment;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import databaseassignment.Team;

public class DataAccessObjectImpl implements DataAccessObject {
    private static final boolean DEBUG = true;
    public DBConnector connector = null;

    public DataAccessObjectImpl(){
        try {
            this.connector = new DBConnector();
        } catch (Exception ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to connect to DB!");
            System.exit(0);
        }
    }

    // User
    @Override
    public ArrayList<User> getUsers(){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `user`;");
            ArrayList<User> users = new ArrayList<>();
            while(res.next()){
                int id = res.getInt("user_id");
                String username = res.getString("username");
                String password = res.getString("password");
                boolean admin = res.getBoolean("admin");
                User user = new User(id, username, password, admin);
                users.add(user);
            }
            return users;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }
    @Override
    public User getUser(int id){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `user` WHERE `user_id` = "+id+";");
            User user = null;
            if(res.next()){
                String username = res.getString("username");
                String password = res.getString("password");
                boolean admin = res.getBoolean("admin");
                user = new User(id, username, password, admin);
            }
            return user;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }
    @Override
    public User getUser(String username){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `user` WHERE `username` = '"+username+"';");
            User user = null;
            if(res.next()){
                int id = res.getInt("user_id");
                String password = res.getString("password");
                boolean admin = res.getBoolean("admin");
                user = new User(id, username, password, admin);
            }
            return user;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }

    // Team
    @Override
    public ArrayList<Team> getTeams(){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `team`;");
            ArrayList<Team> teams = new ArrayList<>();
            while(res.next()){
                int id = res.getInt("team_id");
                String teamname = res.getString("teamname");
                ArrayList<User> members = getTeamMembers(id);
                Team team = new Team(id, teamname, members);
                teams.add(team);
            }
            return teams;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }
    @Override
    public Team getTeam(int id){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `team` WHERE `team_id` = '"+id+"';");
            Team team = null;
            if(res.next()){
                String teamname = res.getString("teamname");
                ArrayList<User> members = getTeamMembers(id);
                team = new Team(id, teamname, members);
            }
            return team;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }

    public Team getTeam(String teamname){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `team` WHERE `teamname` = '"+teamname+"';");
            Team team = null;
            if(res.next()){
                int id = res.getInt("team_id");
                ArrayList<User> members = getTeamMembers(id);
                team = new Team(id, teamname, members);
            }
            return team;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }
    @Override
    public ArrayList<User> getTeamMembers(int team_id){
        try {
            Statement statement = connector.getConnection().createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM `team_member` NATURAL JOIN `user` WHERE `team_id` = '"+team_id+"';");
            ArrayList<User> users = new ArrayList<>();
            while(res.next()){
                int id = res.getInt("user_id");
                String username = res.getString("username");
                String password = res.getString("password");
                boolean admin = res.getBoolean("admin");
                User user = new User(id, username, password, admin);
                users.add(user);
            }
            return users;
        } catch (SQLException ex) {
            if(DEBUG) ex.printStackTrace();
            System.out.println("Unable to retrieve data from DB!");
            System.exit(0);
            return null;
        }
    }


}