package sample;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Vector;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import model.City;

public class CityController {


     static Vector<StringBuffer> searching= new Vector<StringBuffer>(1);

     Vector<Integer> Cityy= new Vector<Integer>(1);
      Vector<Integer> Countryy= new Vector<Integer>(1);

       StringBuffer  abc=null ;

     public Button Add;

        public TableView<City> a;

        public TextField CityID, CityName, Country;

        static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        static final String DB_URL = "jdbc:mysql://mysql.agh.edu.pl:3306/gaszlar1";
        static final String USER = "gaszlar1";
        static final String PASS = "G5C9WwjovojKkH0T";

         public TableColumn<City, String> colCityID;
            public TableColumn<City, String> colCityName;
            public TableColumn<City, String> colCountry;




            @FXML
            public void Add()
            {

                City city = new City (CityID.getText(),CityName.getText(),Country.getText());
                a.getItems().add(city);
                String aa=CityID.getText();
                String aaa=CityName.getText();
                String aaaa=Country.getText();


                        CityID.clear();
                        CityName.clear();
                        Country.clear();

                   Connection conn = null;
                   Statement stmt = null;
                   PreparedStatement stmt1 = null;
                   try{
                      Class.forName("com.mysql.jdbc.Driver");
                      conn = DriverManager.getConnection(DB_URL, USER, PASS);
                      stmt = conn.createStatement();
                        stmt.executeUpdate("INSERT INTO `gaszlar1`.`City` (`CityID`, `CityName`, `Country`) VALUES ('"+aa+"', '"+aaa+"', '"+aaaa+"');");

                      stmt.close();
                   }catch(SQLException se){

                      se.printStackTrace();
                   }catch(Exception e){

                      e.printStackTrace();
                   }finally{

                      try{
                         if(stmt!=null)
                            stmt.close();
                      }catch(SQLException se2){
                      }// nothing we can do
                      try{
                         if(conn!=null)
                            conn.close();
                      }catch(SQLException se){
                         se.printStackTrace();
        }}}

            public static Vector<StringBuffer> GetCity() {

               Connection conn = null;
               Statement stmt = null;
               try{
                  Class.forName("com.mysql.jdbc.Driver");
                  conn = DriverManager.getConnection(DB_URL, USER, PASS);
                  stmt = conn.createStatement();
                  ResultSet rs = stmt.executeQuery("SELECT * FROM City");

                  while(rs.next()) {
                      String abcd1=rs.getString("CityID");
                      String abcd2=rs.getString("CityName");
                      Integer x=abcd2.length();
                      Controller.Cityy.addElement(x);

                      String abcd3=rs.getString("Country");
                      Integer xx=abcd3.length();
                      Controller.Countryy.addElement(xx);


                      Controller.abc=new StringBuffer(abcd1+abcd2+abcd3);

                      Controller.searching.addElement(abc);

                  }

                  rs.close();
                  stmt.close();
               }catch(SQLException se){
                  //Handle errors for JDBC
                  se.printStackTrace();
               }catch(Exception e){
                  //Handle errors for Class.forName
                  e.printStackTrace();
               }finally{
                  //finally block used to close resources
                  try{
                     if(stmt!=null)
                        stmt.close();
                  }catch(SQLException se2){
                  }// nothing we can do
                  try{
                     if(conn!=null)
                        conn.close();
                  }catch(SQLException se){
                     se.printStackTrace();
                  }//end finally try
               }//end try
            return Controller.searching;
            }


            @Override
            public void initialize(URL url, ResourceBundle rb)
                {
            ObservableList<City> o = FXCollections.observableArrayList();
            colCityID.setCellValueFactory(new PropertyValueFactory<>("CityID"));
            colCityName.setCellValueFactory(new PropertyValueFactory<>("CityName"));
            colCountry.setCellValueFactory(new PropertyValueFactory<>("Country"));
            a.setItems(o);


            Controller.GetCity();
            for(int i=0; i<(Controller.searching.size());i++)
                {
                String abca= searching.get(i).toString();
                String from=abca.substring(0, 3);

                Integer aa= Controller.Cityy.get(i);
                String where=abca.substring(3, 3+aa);

                Integer aaa= Controller.Countryy.get(i);
                String fl=abca.substring(3+aa,3+ aa + aaa);

                o.add(new City(from,where,fl));

                }
                }

}