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

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Insets;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TableColumn.CellDataFeatures;
import javafx.util.Callback;

public class JDBC {

    private static boolean dbDriverLoaded = false;
    private static Connection conn = null;
    public Connection getDBConnection() {
        String dbConnString = "jdbc:sqlserver://steliosepl342.database.windows.net;databaseName=epl342;user=adamospp;password=7HSQ6XSG%PDN;";

        if (!dbDriverLoaded)
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                dbDriverLoaded = true;
            } catch (ClassNotFoundException e) {
                System.out.println("Cannot load DB driver!");
                return null;
            }

        try {
            if (conn == null)
                conn = DriverManager.getConnection(dbConnString);
            else if (conn.isClosed())
                conn = DriverManager.getConnection(dbConnString);
        } catch (SQLException e) {
            System.out.print("Cannot connect to the DB!\nGot error: ");
            System.out.print(e.getErrorCode());
            System.out.print("\nSQL State: ");
            System.out.println(e.getSQLState());
            System.out.println(e.getMessage());
        }
        return conn;
    }
    public ResultSet Querry(String q)
    {
        Statement stmt;
        ResultSet srs;
        try {
            stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            srs = stmt.executeQuery(q);
            return srs;
        }catch(Exception e) {
            System.out.println("Guys we have an error on the SQL QUERRY");
            e.printStackTrace(System.out);
            return null;
        }
    }
    private static void InsertRow(String table, String Col, String val) {
        String insert="INSERT INTO "+table+" "+Col+" VALUES "+val;
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(insert);
        } catch (SQLException e) {
            System.out.println("Guys we have an error on the SQL Insert");
            e.printStackTrace(System.out);
        }
    }
    private static void Update(String table, String Col, int val, int row) {
        Statement stmt;
        ResultSet srs;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            srs = stmt.executeQuery("SELECT "+Col+" FROM "+table);
            for(int i=1; i<=row; i++)
                srs.next();
            srs.updateInt(Col, val);
            srs.updateRow();
        }catch(Exception e) {
            System.out.println("Guys we have an error on the SQL QUERRY");
            e.printStackTrace(System.out);
        }
    }
    private static void PrintTable(ResultSet table) {
        try {
            int collumns=table.getMetaData().getColumnCount();
            table.beforeFirst();
            for(int i=1; i<=collumns; i++) {
                if (i > 1) System.out.print("\t");
                String columnName = table.getMetaData().getColumnName(i);
                System.out.print(columnName);
            }
            System.out.println();
            while(table.next()) {
                for(int i=1; i<=collumns; i++) {
                    if (i > 1) System.out.print("\t");
                    String columnValue = table.getString(i);
                    System.out.print(columnValue);
                }
                System.out.println();
            }
        }catch(Exception e) {
            System.out.println("Guys we have an error on the SQL Table Print");
        }
    }
    //m dbo.trains_not_stop_at_station station string
    public TableView trains_not_stop_at_station(String station) {
        String sol="EXEC dbo.trains_not_stop_at_station @station="+station;
        return getTheTable(sol);
    }
    //n dbo.routes_that_stop_at_least
    public TableView routes_that_stop_at_least(String Percentage) {
        String sol="EXEC dbo.routes_that_stop_at_least @percentage="+Percentage;
        return getTheTable(sol);
    }
    //o dbo.display_schedule_of_route selectRoute int
    public TableView display_schedule_of_route(String selectRoute) {
        String sol="EXEC dbo.display_schedule_of_route @selectRoute="+selectRoute;
        return getTheTable(sol);
    }
    //p avail_route_each_stop_and_dayday_and_time day string, time string
    public TableView avail_route_each_stop_and_dayday_and_time(String day, String time) {
        String sol="EXEC dbo.avail_route_each_stop_and_dayday_and_time @day='"+day+"', @time='"+time+"';";
        return getTheTable(sol);
    }
    //i dbo.train_passes_station_day_time @desiredStation int, @day String, @desiredTime time
    public TableView train_passes_station_day_time(String desiredStation, String day, String desiredTime) {
        String sol="EXEC dbo.train_passes_station_day_time @desiredStationID="+desiredStation+", @day='"+day+"', @desiredTime='"+desiredTime+"';";
        return getTheTable(sol);
    }


    public TableView getTheTable(String query)
    {
        try{
            ResultSet table=Querry(query);
            int collumns=table.getMetaData().getColumnCount();
            TableView results=new TableView();
            results.setPadding(new Insets(5, 5, 5, 5));
            ObservableList<ObservableList> data=FXCollections.observableArrayList();
            for(int i=0; i<table.getMetaData().getColumnCount(); i++) {
                final int j=i;
                TableColumn col=new TableColumn(table.getMetaData().getColumnName(i+1));
                col.setCellValueFactory(new Callback<CellDataFeatures<ObservableList,String>, ObservableValue<String> >(){

                    @Override
                    public ObservableValue<String> call(CellDataFeatures<ObservableList, String> param) {

                        return new SimpleStringProperty(param.getValue().get(j).toString());
                    }

                });
                results.getColumns().add(col);
            }
            while(table.next()){
                //Iterate Row
                ObservableList<String> row = FXCollections.observableArrayList();
                for(int i=1 ; i<=table.getMetaData().getColumnCount(); i++){
                    //Iterate Column
                    row.add(table.getString(i));
                }
                data.add(row);
            }
            results.setItems(data);
            return results;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
    private static void ExecuteStoredProccess(String Procedure, ArrayList<SQLType> types) {
        try {
            CallableStatement cstmt = conn.prepareCall(Procedure);
            for(int i=1; i<=types.size(); i++) {
                cstmt.registerOutParameter(i, types.get(i-1));
            }
            cstmt.execute();
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        JDBC test=new JDBC();
        test.getDBConnection();//ONE TIME!!!!

        String testQuerry="SELECT * FROM dbo.RAIL";
        PrintTable(test.Querry(testQuerry));
        /*test.Update("RAIL", "speed_limit", 5000, 1);
        PrintTable(test.Querry(testQuerry));
        test.InsertRow("RAIL", "(rail_id,speed_limit)", "(18,69)");
        PrintTable(test.Querry(testQuerry));*/

    }

}