package API;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import DTO.customer;

public class sqlConnector {
private String user = "";
private String password = "";
private String serverName = "localhost";
private String databaseName = "";
private Connection con = null;

public sqlConnector() {

}

//C
public void createCustomer(customer cust) {
	con = null;
	PreparedStatement stmt = null;
	SQLServerDataSource ds = new SQLServerDataSource();
	try {
		java.util.Date utilDate = new Date();
		java.sql.Date now = new java.sql.Date(utilDate.getTime());
		ds = setSQLServerDataSource();
		con = ds.getConnection();
		stmt = con.prepareStatement("Insert into customer (firstName, lastName, created, customerLogin, customerPassword) values (?,?,?,?,?)");
		stmt.setString(1, cust.getFirstName());
		stmt.setString(2, cust.getLastName());
		stmt.setDate(3, now);
		stmt.setString(4, cust.getCustomerLogin());
		stmt.setString(5, cust.getCustomerPassword());
		stmt.executeUpdate();
		con.close();
	}
	catch(Exception e) {
		e.printStackTrace();
	}
}

//R
public customer getCustomer(int id) {
	customer cust = new customer();
	try {
		ResultSet rs = getResultSet("Select * from customer where cId="+id);
		rs.next();
		cust = custData(cust, rs);
		con.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return cust;
}

//RA
public ArrayList<customer> getAllCustomers(){
	ArrayList<customer> list = new ArrayList<customer>();
	try {
		ResultSet rs = getResultSet("Select * from customer");
		while(rs.next()) {
			customer cust = new customer();
			cust = custData(cust, rs);
			list.add(cust);
		}
		con.close();
	}
	catch (Exception e) {
		e.printStackTrace();
	}
	return list;
}

//U
public void updateCustomer(customer cust) {
	con = null;
	PreparedStatement stmt = null;
	SQLServerDataSource ds = new SQLServerDataSource();
	try {
		ds = setSQLServerDataSource();
		con = ds.getConnection();
		stmt = con.prepareStatement("Update customer set firstName=?, lastName=?, customerLogin=?, customerPassword=? where cId=?");
		stmt.setString(1, cust.getFirstName());
		stmt.setString(2, cust.getLastName());
		stmt.setString(3, cust.getCustomerLogin());
		stmt.setString(4, cust.getCustomerPassword());
		stmt.setInt(5,  cust.getcId());
		stmt.executeUpdate();
		con.close();
	}
	catch(Exception e) {
		e.printStackTrace();
	}
}


//D
public void deleteCustomer(int cId) {
	con = null;
	PreparedStatement stmt = null;
	SQLServerDataSource ds = new SQLServerDataSource();
	try {
		ds = setSQLServerDataSource();
		con = ds.getConnection();
		stmt = con.prepareStatement("delete from customer where cId=?");
		stmt.setInt(1, cId);
		stmt.executeUpdate();
		con.close();
	}
	catch(Exception e) {
		e.printStackTrace();
	}
}

//Other
//Gets a result set
private ResultSet getResultSet(String query) {
	ResultSet rs = null;
	con = null;
	Statement stmt = null;
	SQLServerDataSource ds = new SQLServerDataSource();
	try {
		ds = setSQLServerDataSource();
		con = ds.getConnection();
		stmt = con.createStatement();
		rs = stmt.executeQuery(query);
	}
	catch(Exception e) {
		e.printStackTrace();
	}
	return rs;
}
//Sets datasource for connection
private SQLServerDataSource setSQLServerDataSource() {
	SQLServerDataSource ds = new SQLServerDataSource();
	ds.setUser(user);
	ds.setPassword(password);
	ds.setServerName(serverName);
	ds.setDatabaseName(databaseName);
	return ds;
}

//Filling data from a resultset into a customer DTO
private customer custData(customer cust, ResultSet rs) {
	try {
		cust.setcId(rs.getInt("cId"));
		cust.setFirstName(rs.getString("firstName"));
		cust.setLastName(rs.getString("lastName"));
		cust.setCustomerLogin(rs.getString("customerLogin"));
		cust.setCustomerPassword(rs.getString("customerPassword"));
		cust.setCreated(rs.getDate("created"));
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return cust;
}

}