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.fxml.Initializable;
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);

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

     static  StringBuffer  abc=null ;

     public Button Add;

        public static 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 static TableColumn<City, String> colCityID;
            public static TableColumn<City, String> colCityName;
            public static 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();
                      CityController.Cityy.addElement(x);

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


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

                    CityController.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 CityController.searching;
            }




}