package database;
import java.sql.*;
import java.util.ArrayList;

public class DatabaseController {
	/** VARIABLE DECLARATIONS *************************/
private Connection dbConnection = null;
private String userName = null; private String password = null;
private String databaseName = null;
private String databaseServerURL = null;
private String driverClass = null;
private String portNumber = null;
private static DatabaseController DBController = null;
private DatabaseMetaData dbMetaData = null;
/** GETTERS AND SETTERS *************************/
	private Connection getDbConnection(){
		if (dbConnection == null){
			dbConnection = createDatabaseConnection();
		}
		return dbConnection;
		}

	private String getUserName() {
		if (userName == null){
		userName = "sa";
		}
		return userName;
	}
		private String getPassword() {
			if (password == null){
				password = "sesame";
			}
			return password;
		}
public String getDatabaseName() {
	if (databaseName == null){
		databaseName = "AutoDealer";
	}
		return databaseName;
	}

	private String getDatabaseServerURL() {
		if (databaseServerURL == null){
			databaseServerURL = "localhost";
		}
		return databaseServerURL;
	}

	private String getDriverClass() {
		if (driverClass == null){ driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; }
		return driverClass;
		}

	private String getPortNumber() {
		if (portNumber == null){
			portNumber = "50719";
	}
		return portNumber;
	}

	public void resetDbConnection() {
		this.dbConnection = null;
	}

	public void setUserName(String userName){
		this.userName = userName;
	}

	public void setPassword(String password){
		this.password = password;
	}

	public void setDatabaseName(String databaseName) {
		this.databaseName = databaseName;
	}

	public void setDatabaseServerURL(String databaseServerURL) {
		this.databaseServerURL = databaseServerURL;
	}

	public void setDriverClass(String driverClass) {
		this.driverClass = driverClass;
	}
	public void setPortNumber(String portNumber) {
		this.portNumber = portNumber;
	}
	public static DatabaseController getDBController() {
		if (DBController == null){
			DBController = new DatabaseController();
		}
	return DBController;
	}
	private DatabaseMetaData getDBMetaData(){
		try {
			dbMetaData = getDbConnection().getMetaData();
		}
		catch (SQLException e) {
			System.out.println(e);
		}
		return dbMetaData;
	}

//page 3
	/** CONSTRUCTORS ***/
	private DatabaseController(){
	/*** no code lol **/
	}

	/** Database connection code **/
	private Connection createDatabaseConnection(){
		Connection conn = null;
		String connectString = buildConnectionString();
		//check driver exists
		try{
			Class.forName(getDriverClass());
		} catch (java.lang.ClassNotFoundException e){
			StringBuffer buf = new StringBuffer();
			buf.append("No driver class found for: ");
			buf.append(getDriverClass());
			System.out.println(buf.toString());
			System.exit(0);
		}
		//driver class exists try open connection
		try{
			conn=DriverManager.getConnection(connectString);
		} catch (SQLException e){
		StringBuffer buf = new StringBuffer();
		buf.append("There was a problem with the following connection string: ");
		buf.append(connectString);
		buf.append("\n\nHere is the exceptio:\n");
		buf.append(e.toString());
		System.out.println(buf.toString());
		System.exit(0);
		}
		return conn;
	}
	private String buildConnectionString(){
		StringBuffer buf = new StringBuffer();
		buf.append("jdbc:sqlserver://");
		//page 4
		buf.append(getDatabaseServerURL());
		buf.append(":");
		buf.append(getPortNumber());
		buf.append(";databaseName=");
		buf.append(getDatabaseName());
		buf.append(";user=");
		buf.append(getUserName());
		buf.append(";password=");
		buf.append(getPassword());
		return buf.toString();
	}
		/** UTILITIES ******************************************/
	public ArrayList<String> getDatabaseNames(){
		ArrayList<String> databases = new ArrayList<String>();
		ResultSet res;
		try {
			res = getDBMetaData().getCatalogs();
			while (res.next()) {
				databases.add(res.getString("TABLE_CAT"));
			}
			res.close();
		}
		catch (SQLException e) {
			System.out.println(e);
			e. printStackTrace();
		}
		return databases;
		}

		public ArrayList<String> getTableNames(){
			ArrayList<String> tables = new ArrayList<String>();
			DatabaseMetaData dbmd = getDBMetaData();
			if (dbmd != null){
				try {
					ResultSet rs = dbmd.getTables(null, null, "%", null);
					while(rs.next()){
						if (rs.getString(2).equals("dbo")){
							tables.add(rs.getString(3));
							}
					}
				} catch (SQLException e) {
					System.out.println(e); e.printStackTrace();
				}
			}
				return tables;
				}

		public void printResultSet(ResultSet rs){
			try{
				boolean columnHeadingsPrinted = false;
				while (rs.next()){
					if(! columnHeadingsPrinted){
						for(int i=1; i<=rs.getMetaData().getColumnCount(); i++){
							System.out.print(rs.getMetaData().getColumnLabel(i));
							System.out.print(":\t");
							}
						System.out.println();
						columnHeadingsPrinted = true;
					}
				 for(int i=1; i<=rs.getMetaData().getColumnCount(); i++){
				System.out.print(rs.getString(i));
				System.out.print("\t");
				}
				 	System.out.println();
					}
				} catch(SQLException e) { // TODO Auto-generated catch block
					e.printStackTrace();
					}
				}

			public void printQueryResults(String query) {
				try {
					Statement s = getDbConnection().createStatement();
					ResultSet rs = s.executeQuery(query);
//page 6

					printResultSet(rs);
					} catch (SQLException e) { // TODO Auto-generated catch block
						e.printStackTrace();
					}
					}

			public static void main(String[] args) {
				DatabaseController dbc = DatabaseController.getDBController();
				ArrayList<String> dbNames = dbc.getDatabaseNames();
				StringBuffer buf = new StringBuffer();
				buf.append("The database server has the following databases:\n");
				for (String dbName : dbNames){
					buf. append(dbName);
					buf.append("\n");
				}
				System.out.println(buf.toString());
				ArrayList<String> tables = dbc.getTableNames();
				buf = new StringBuffer(); buf.append("Database ");
				buf.append(dbc.getDatabaseName());
				buf.append(" has the following tables:\n");
				for (String tableName : tables){
					buf.append(tableName);
					buf.append("\n");
				}
				System.out.println(buf.toString());
			}
			public ResultSet getCustomerBylD(String customerlD){
					StringBuffer buf = new StringBuffer();
					buf.append("SELECT * FROM Customers WHERE Customers.ID .");
					buf. append(customerlD);
					return executeQuery(buf.toString());
				}
			public ResultSet executeQuery(String query){
					Statement s = null;
					ResultSet rs = null;
						try {
						s = getDbConnection().createStatement();
						rs = s.executeQuery(query);
						} catch (SQLException e) {
// page 7
						e.printStackTrace(); }
					return rs;
					}
			public ResultSet getSalesPersonByID(String salesPersonID){
				StringBuffer buf = new StringBuffer();
				buf.append("SELECT * FROM Salespeople WHERE SalesPeople.ID =");
				buf.append(salesPersonID);
				return executeQuery(buf.toString());
			}
			public ResultSet executeStoredProcedure(String procName, ArrayList<String> params){
				ResultSet rs = null;
				StringBuffer buf = new StringBuffer();
				buf.append("{ call ");
				buf.append(procName);
				buf.append("(");
				for (int i=0; i<params.size(); i++){
					if (i > 0)
						buf.append(", ");
						buf.append("?");
				}
					buf.append(") }");
					String sql = buf.toString();
				try {
					CallableStatement cs = getDbConnection().prepareCall(sql);
					for (int i=0; i<params.size(); i++){
						cs.setString(i+1, params.get(i));
					}
					rs = cs.executeQuery();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				return rs;
			}
			public ResultSet getCarByID(String carID){
				ResultSet rs = null;
				ArrayList<String> params= new ArrayList<String>();
				params.add(carID);
				rs=executeStoredProcedure("procGetCarByID", params);
				return rs;
			}
}