package sample;

//Ashwin Randhir
//5/7/2018
//CSC 112
//Interm Java
//Final
//atrandhir0001@student.stcc.edu
//Gui for Final, with regex field check (P2)

import java.sql.*;

import javafx.application.Application;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.scene.paint.Color;
import javafx.scene.shape.Line;
import javafx.stage.Stage;

public class Main extends Application {

    private final TextField ZipCodeText = new TextField();
    private final TextField MiddleCityText = new TextField();
    private final TextField TopCityText = new TextField();
    private final TextField TopStateText = new TextField();
    private final TextField MiddleStateText = new TextField();
    private final Button ZiptoCityButton = new Button("Zip To City");
    private final Button CitytoZipButton = new Button("City to Zip");
    private final TextField ZipCodeField = new TextField("Zip Code");

    private PreparedStatement preparedStatement;
    Connection conn;

    @Override
    public void start(Stage primaryStage) {

    	StartDatabase();


        //--Build Pane
        BorderPane pane = new BorderPane();
        pane.setTop(TopRow());
        pane.setCenter(CenterRow());
        pane.setBottom(BottomRow());

        //--Lambda
        ZiptoCityButton.setOnAction(e -> CheckZiptoCity());
        CitytoZipButton.setOnAction(e -> CheckCitytoZip());

        //--Create scene
        Scene scene = new Scene(pane, 800, 300);
        primaryStage.setTitle("ZipCode Translation System");
        primaryStage.setScene(scene);
        primaryStage.show();
        primaryStage.setResizable(false);
    }

    //--TextStatus Field
    private final TextField StatusText = new TextField();


    //--Button Code
    //--Zip to City
    private void CheckZiptoCity() {
        if (ZipCodeText.getText().matches("(\\d{5})")) {
        	zipDatabase();
            StatusText.setText("Success - Valid ZipCode");
        } else {
            StatusText.setText("Error - Invalid ZipCode");

        }
    }

    //--City to Zip
    private void CheckCitytoZip() {
        if (MiddleCityText.getText().matches("^[a-zA-Z\\s-]+$") && MiddleStateText.getText().matches("^[A-Za-z]{2}")) {
        	cityDatabase();
            StatusText.setText("Success - Valid City / State");
        } else {
            StatusText.setText("Error - Invalid City / State");

        }
    }





    //--Top Row (Zip to City)
    private VBox TopRow() {

        HBox hbox = new HBox();
        hbox.setPadding(new Insets(15, 10, 15, 10));
        hbox.setSpacing(15);
        hbox.setAlignment(Pos.CENTER);

        //--VBox
        VBox vbox = new VBox();
        vbox.setAlignment(Pos.CENTER);

        //--Line (Divider)
        Line separator = new Line();
        separator.setStrokeWidth(5);
        separator.setStroke(Color.BLACK);
        separator.setEndX(800.0);
        separator.autosize();

        //--Labels
        Label ZipCode = new Label("Zip Code:");
        Label City = new Label("City:");
        Label State = new Label("State:");

        //--Properties
        ZipCodeText.setPrefColumnCount(5);
        ZipCodeText.setPrefWidth(80);

        //
        TopCityText.setPrefColumnCount(25);
        TopCityText.setPrefWidth(200);
        TopCityText.setEditable(false);
        TopCityText.setMouseTransparent(true);
        TopCityText.setFocusTraversable(false);

        //
        TopStateText.setPrefColumnCount(2);
        TopStateText.setEditable(false);
        TopStateText.setMouseTransparent(true);
        TopStateText.setFocusTraversable(false);
        TopStateText.setPrefWidth(50);

        //--Add to hbox
        hbox.getChildren().addAll(ZipCode, ZipCodeText, ZiptoCityButton, City, TopCityText, State, TopStateText);
        vbox.getChildren().addAll(hbox, separator);
        return vbox;
    }

    //--Center of program (City to Zip)
    private VBox CenterRow() {

        HBox hbox = new HBox();
        hbox.setPadding(new Insets(15, 10, 15, 10));
        hbox.setSpacing(15);
        hbox.setAlignment(Pos.CENTER);

        //--VBox
        VBox vbox = new VBox();
        vbox.setAlignment(Pos.CENTER);

        //--Line
        Line separator = new Line();
        separator.setStrokeWidth(5);
        separator.setStroke(Color.BLACK);
        separator.setEndX(800.0);
        separator.autosize();

        //--Labels
        Label City = new Label("City:");
        Label State = new Label("State:");

        //--Properties
        MiddleStateText.setPrefColumnCount(2);
        MiddleStateText.setPrefWidth(50);

        //
        ZipCodeField.setPrefColumnCount(5);
        ZipCodeField.setEditable(false);
        ZipCodeField.setMouseTransparent(true);
        ZipCodeField.setFocusTraversable(false);
        ZipCodeField.setPrefHeight(100);
        ZipCodeField.setPrefWidth(140);

        //--Add to hbox
        hbox.getChildren().addAll(City, MiddleCityText, State, MiddleStateText, CitytoZipButton, ZipCodeField);
        vbox.getChildren().addAll(hbox, separator);

        return vbox;
    }

    //--Status Row
    private HBox BottomRow() {

        HBox hbox = new HBox();
        hbox.setPadding(new Insets(15, 10, 15, 10));
        hbox.setAlignment(Pos.CENTER);

        //--Label
        Label Status = new Label("Status: ");

        //--Properties
        StatusText.setEditable(false);
        StatusText.setMouseTransparent(true);
        StatusText.setFocusTraversable(false);
        StatusText.setPrefWidth(500);

        //--Add to hbox
        hbox.getChildren().addAll(Status, StatusText);

        return hbox;
    }


    private void StartDatabase() {
        String DATABASE = "silvestri";
        String USERNAME = "readonly";
        String PASSWORD = "readonly";
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://cs.stcc.edu/" + DATABASE + "?user=" + USERNAME + "&password=" + PASSWORD;
        String zip = ZipCodeText.getText();
        String state = MiddleStateText.getText();
        String city = MiddleCityText.getText();
        try {
            Class.forName(driver);
            System.out.println("Driver loaded successfully");

            Connection connection = DriverManager.getConnection(url);
            System.out.println("Database Connected");
            String zipCode = ("Select city, state FROM Zipcodes WHERE zipcode= " + zip);
            String City = ("Select zipcode from Zipcodes where state ='" + state + "' && city ='" + city + "';");

            preparedStatement = connection.prepareStatement(zipCode);
            preparedStatement = connection.prepareStatement(City);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //ZipCode to Database
    private void zipDatabase() {
        try {
            String zip = ZipCodeText.getText();
            String zipCode = ("Select city, state FROM Zipcodes WHERE zipcode= " + zip);
            ResultSet rset = preparedStatement.executeQuery(zipCode);
            if (rset.next()) {
                TopCityText.setText(rset.getString(1));
                TopStateText.setText(rset.getString(2));
                StatusText.setText("City and State were retrieved from Database");
            } else {
                StatusText.setText("Error Database Connection Fail");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    //City & State to Database
    private void cityDatabase() {
        try {
            String state = MiddleStateText.getText();
            String city = MiddleCityText.getText();
            String City = ("Select zipcode from Zipcodes where state ='" + state + "' && city ='" + city + "';");
            ResultSet rset = preparedStatement.executeQuery(City);
            if (rset.next()) {
            	String zipCode = rset.getString("zipcode");
                ZipCodeField.setText(zipCode + ", " + System.lineSeparator());
                while (rset.next()) {
                    zipCode = rset.getString("zipcode");
                    ZipCodeField.setText(ZipCodeField.getText() + zipCode + ", " + System.lineSeparator());}
            } else {
                StatusText.setText("Error Invalid City or State");
                ZipCodeField.setText("No Zip Codes Found");
            }
        } catch (Exception ex) {
            ex.getMessage();
        }
    }
    public static void main(String[] args) {
        launch(args);
    }
}