package admin;

import java.sql.*;
import java.util.*;
import com.ConnDB1;

public class AdminDB extends ConnDB1{

    private ArrayList admin;
   
    public AdminDB ()
    throws Exception
    {
        //
    }

    //
    public boolean adminExist(String username){
      boolean occupied=true;
      try
      {
    	  ConnDB1.initialize();	// create database connection
    	  PreparedStatement preparedStatement = aConnection.prepareStatement (
		  "select adminId from admin where username=?");
          preparedStatement.setString (1, username);
          ResultSet resultSet = preparedStatement.executeQuery ();
          if(!resultSet.next())
             occupied=false;
             preparedStatement.close ();
             ConnDB1.terminate();
          }
          catch(SQLException e){
              e.printStackTrace();
              ConnDB1.terminate();
          }
      return occupied;
    }
    
    public boolean isValidAdmin (String username, String password)
    {
        boolean isValid=false;
    	try
        {
    		ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT username, password FROM admin WHERE username = ? and password= ?");
            preparedStatement.setString (1, username);
            preparedStatement.setString (2, password);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                isValid=true;
            	preparedStatement.close ();
            	ConnDB1.terminate();
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                //return isValid;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            //return isValid;
        }
        return isValid;
    }
    
    public Admin getAdmin (int id)
    {
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT adminId, username, password FROM admin WHERE adminId = ?");
            preparedStatement.setInt (1, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                Admin admins = new Admin
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3)
                );
                preparedStatement.close ();
                ConnDB1.terminate();
                return admins;
            }
            else
            {
                preparedStatement.close ();
                ConnDB1.terminate();
                return null;
            }
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return null;
        }
    }

    public int addAdmin (Admin admins)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("INSERT INTO Admin (username, password) VALUES (?, ?)");
            preparedStatement.setString (1, admins.getUsername ());
            preparedStatement.setString (2, admins.getPassword());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public int deleteAdmin (int id)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("DELETE FROM admin WHERE adminId = ?");
            preparedStatement.setInt (1, id);
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public int modifyAdmin (Admin admins)
    {
        int rowsAffected = 0;
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("UPDATE admin SET username=?, password=? WHERE adminId =?");
            preparedStatement.setString (1, admins.getUsername());
            preparedStatement.setString (2, admins.getPassword());
            preparedStatement.setInt (3, admins.getId ());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            ConnDB1.terminate();
        }
        catch (SQLException e)
        {
        	ConnDB1.terminate();
            return 0;
        }
        return rowsAffected;
    }

    public Collection getAdmin ()
    {
        admin = new ArrayList ();
        try
        {
        	ConnDB1.initialize();	// create database connection
            PreparedStatement preparedStatement = aConnection.prepareStatement ("SELECT adminId, username, password FROM admin");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
                Admin user = new Admin
                (
                    resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3)
                );
                admin.add(user);
            }
            preparedStatement.close ();
        }
        catch (SQLException e)
        {
            return null;
        }
        ConnDB1.terminate();
        //Collections.sort(admin);
        return admin;
    }
}