/*
 * Eliezer Encarnacion
 * CSC-112-D01
 * Final Exam
 * Email: eeencarnacion0001@student.stcc.edu
 * ZipCode Translation DataBase Connection
 */

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

import javafx.scene.control.TextField;

public class ZipCodeDataProcess {

	public static final String dataBase = "silvestri";
	public static final String userName = "readonly";
	public static final String passWord = "readonly";
	static String driver = "com.mysql.jdbc.Driver";
	static String url = "jdbc:mysql://cs.stcc.edu/" + dataBase + "?user=" + userName + "&password=" + passWord +
            "&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

	public ZipCodeDataProcess() {

	}

	public String zipToCityProcess(String zipC, String columnLbl, TextField status) {
		String columnValue = null;
		PreparedStatement sqlState = null;
		try {
			Connection connection = DriverManager.getConnection(url);
			System.out.println("Connected to Database");
			String select = "SELECT * From silvestri.Zipcodes WHERE zipcode= ?";
			sqlState = connection.prepareStatement(select);
			sqlState.setString(1, zipC);
			ResultSet rows = sqlState.executeQuery();

			if (!rows.next())
				status.setText("Zip Code does not exist in Database");
			rows.beforeFirst();
			while (rows.next()) {
				columnValue = rows.getString(columnLbl);
			}
			connection.close();
			System.out.println("Closed Connection");

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnValue;
	}

	public ArrayList cityToZipProcess (String city, String state, TextField status) {
		ArrayList zipCodes = new ArrayList();
		PreparedStatement sqlState = null;
		try {
			Connection connection = DriverManager.getConnection(url);
			System.out.println("Connected to Database");
			String select = "SELECT * From silvestri.Zipcodes WHERE state= ? and city= ?";
			sqlState = connection.prepareStatement(select);
			sqlState.setString(1, state);
			sqlState.setString(2, city);
			ResultSet rows = sqlState.executeQuery();
			if (!rows.next())
				status.setText("Either state or city does not exist. Please check and try again");
			rows.beforeFirst();
			while (rows.next()) {
				zipCodes.add(rows.getString("zipcode"));
			}
			connection.close();
			System.out.println("Closed Connection");

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(e + "error");
		}
		return zipCodes;
	}
}