package sample;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import model.City;
import model.Airport;
import java.util.ResourceBundle;
import java.net.URL;
import javafx.fxml.FXML;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.fxml.Initializable;
import javafx.scene.control.*;import javafx.fxml.FXMLLoader;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

import java.awt.event.ActionEvent;
import java.io.IOException;


import javafx.collections.FXCollections;
import javafx.collections.ObservableList;


public class Controller implements Initializable {

    public Button Add, Add1;
    public TableView<City> a;
    public TableView<Airport> s;
    public TextField CityID, CityName, Country, asd, asdd, asddd;

    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;
    public TableColumn<Airport, String> colAirportID;
    public TableColumn<Airport, String> colAirportName;
    public TableColumn<Airport, String> colCityid;

    @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();
}}}


@FXML
    public void Add1()
    {

        Airport airport = new Airport (asd.getText(),asdd.getText(),asddd.getText());
        s.getItems().add(airport);
        String aa=asd.getText();
        String aaa=asdd.getText();
        String aaaa=asddd.getText();


        asd.clear();
        asdd.clear();
        asddd.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`.`Airport` (`AirportID`, `AirportName`, `CityID`) 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();
}}}



    @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);

        ObservableList<Airport> z = FXCollections.observableArrayList();
       // colAirportID.setCellValueFactory(new PropertyValueFactory<>("AirportID"));
      //  colAirportName.setCellValueFactory(new PropertyValueFactory<>("AirportName"));
      //  colCityid.setCellValueFactory(new PropertyValueFactory<>("Cityid"));
        s.setItems(z);

    }


}