import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;

import com.irene.database.Entities.*;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ChoiceBox;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;

import javafx.stage.Stage;


public class Controller {

    @FXML
    private ResourceBundle resources;

    @FXML
    private URL location;

    @FXML
    private TableView table;

    @FXML
    private ChoiceBox<String> choice;

    @FXML
    private Button addButton;

    @FXML
    private Button deleteButton;

    @FXML
    void initialize() {
        ObservableList<String> tableNames = FXCollections.observableArrayList(getTableName());
        choice.setItems(tableNames);
        choice.setValue(getTableName().get(0));


//        col1.setCellValueFactory(new PropertyValueFactory<>("id_cinema"));
//        col2.setCellValueFactory(new PropertyValueFactory<>("name_cinema"));
//        col3.setCellValueFactory(new PropertyValueFactory<>("id_owner"));
//        table.getColumns().clear();
//        table.getColumns().add(col1);
//        table.getColumns().add(col2);
//        table.getColumns().add(col3);
//
//
//        buildData();

        buildData(choice);
        choice.setOnAction(event -> buildData(choice));
        System.out.println(choice.getValue());

        deleteButton.setOnAction(event -> {
            DB db = new DB();
            PreparedStatement st;
            Connection connection = db.getConnection();
            try {

                if (choice.getValue().equals("Кинотеатры")) {
                    Cinema selectedItem = (Cinema) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.cinema where id_cinema = " + selectedItem.getId_cinema());
                    st.executeUpdate();
                } else if (choice.getValue().equals("Фильмы")) {
                    Movie selectedItem = (Movie) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.movie where id_movie = " + selectedItem.getId_movie());
                    st.executeUpdate();
                } else if (choice.getValue().equals("Дистрибьютор")) {
                    Distributor selectedItem = (Distributor) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.distributor where id_distributor = " + selectedItem.getId_distributor());
                    st.executeUpdate();
                } else if (choice.getValue().equals("Залы")) {
                    Hall selectedItem = (Hall) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.hall where id_hall = " + selectedItem.getId_hall());
                    st.executeUpdate();
                } else if (choice.getValue().equals("Владельцы кинотеатров")) {
                    Owner selectedItem = (Owner) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.owner where id_owner = " + selectedItem.getId_owner());
                    st.executeUpdate();
                }
                if (choice.getValue().equals("Сеансы")) {
                    Session selectedItem = (Session) table.getSelectionModel().getSelectedItem();
                    table.getItems().removeAll(selectedItem);
                    st = connection.prepareStatement("delete from mydbtest.session where id_session = " + selectedItem.getId_session());
                    st.executeUpdate();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });

        addButton.setOnAction(event -> {
            try {
                FXMLLoader fxmlLoader = new FXMLLoader(getClass().getResource("/delete.fxml"));
                Parent root1 = (Parent) fxmlLoader.load();
                Stage stage = new Stage();
                stage.setScene(new Scene(root1));
                stage.show();
            } catch (Exception e) {
                e.printStackTrace();
            }
        });

    }


    ArrayList<String> getTableName() {
        DB db = new DB();

        ArrayList<String> tableNames = new ArrayList<>();
        try {
            ResultSet rs = null;
            DatabaseMetaData meta = db.connection.getMetaData();
            rs = meta.getTables(null, null, null, new String[]{"TABLE"});

            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                if (tableName.equals("cinema")) {
                    tableNames.add("Кинотеатры");
                } else if (tableName.equals("distributor")) {
                    tableNames.add("Дистрибьютор");
                } else if (tableName.equals("hall")) {
                    tableNames.add("Залы");
                } else if (tableName.equals("movie")) {
                    tableNames.add("Фильмы");
                } else if (tableName.equals("owner_cinema")) {
                    tableNames.add("Владельцы кинотеатров");
                } else if (tableName.equals("session")) {
                    tableNames.add("Сеансы");
                }

//                || !tableName.equals("sys_config")
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        }

        return tableNames;
    }

    private ObservableList data;


    public void buildData(ChoiceBox choice) {

        data = FXCollections.observableArrayList();
        try {

            if (choice.getValue().equals("Кинотеатры")) {

                TableColumn col1 = new TableColumn("id_cinema");
                TableColumn col2 = new TableColumn("name_cinema");
                TableColumn col3 = new TableColumn("id_owner");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_cinema"));
                col2.setCellValueFactory(new PropertyValueFactory<>("name_cinema"));
                col3.setCellValueFactory(new PropertyValueFactory<>("id_owner"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);
                table.getColumns().add(col3);

                String SQL = "Select * from cinema";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_cinema = rs.getInt("id_cinema");
                    String name_cinema = rs.getString("name_cinema");
                    Integer id_owner = rs.getInt("id_cinema");
                    data.add(new Cinema(id_cinema, name_cinema, id_owner));

                }
            } else if (choice.getValue().equals("Залы")) {

                TableColumn col1 = new TableColumn("id_hall");
                TableColumn col2 = new TableColumn("id_cinema");
                TableColumn col3 = new TableColumn("seats");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_hall"));
                col2.setCellValueFactory(new PropertyValueFactory<>("id_cinema"));
                col3.setCellValueFactory(new PropertyValueFactory<>("seats"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);
                table.getColumns().add(col3);

                String SQL = "Select * from hall";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_hall = rs.getInt("id_hall");
                    String id_cinema = rs.getString("id_cinema");
                    String seats = rs.getString("seats");
                    data.add(new Hall(id_hall, id_cinema, seats));

                }
            } else if (choice.getValue().equals("Дистрибьютор")) {
                TableColumn col1 = new TableColumn("id_distributor");
                TableColumn col2 = new TableColumn("title");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_distributor"));
                col2.setCellValueFactory(new PropertyValueFactory<>("title"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);

                String SQL = "Select * from distributor";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_distributor = rs.getInt("id_distributor");
                    String title = rs.getString("title");
                    data.add(new Distributor(id_distributor, title));

                }
            } else if (choice.getValue().equals("Фильмы")) {
                TableColumn col1 = new TableColumn("id_movie");
                TableColumn col2 = new TableColumn("title");
                TableColumn col3 = new TableColumn("id_distributor");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_movie"));
                col2.setCellValueFactory(new PropertyValueFactory<>("title"));
                col3.setCellValueFactory(new PropertyValueFactory<>("id_distributor"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);
                table.getColumns().add(col3);

                String SQL = "Select * from movie";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_movie = rs.getInt("id_movie");
                    String title = rs.getString("title");
                    String id_distributor = rs.getString("id_distibutor");
                    data.add(new Movie(id_movie, title, id_distributor));
                }
            } else if (choice.getValue().equals("Владельцы кинотеатров")) {
                TableColumn col1 = new TableColumn("id_owner");
                TableColumn col2 = new TableColumn("fio");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_owner"));
                col2.setCellValueFactory(new PropertyValueFactory<>("fio"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);

                String SQL = "Select * from owner_cinema";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_owner = rs.getInt("id_owner");
                    String fio = rs.getString("fio");
                    data.add(new Owner(id_owner, fio));
                }
            } else if (choice.getValue().equals("Сеансы")) {
                TableColumn col1 = new TableColumn("id_session");
                TableColumn col2 = new TableColumn("id_hall");
                TableColumn col3 = new TableColumn("id_movie");

                col1.setCellValueFactory(new PropertyValueFactory<>("id_session"));
                col2.setCellValueFactory(new PropertyValueFactory<>("id_hall"));
                col3.setCellValueFactory(new PropertyValueFactory<>("id_movie"));
                table.getColumns().clear();
                table.getColumns().add(col1);
                table.getColumns().add(col2);
                table.getColumns().add(col3);

                String SQL = "Select * from mydbtest.session";
                ResultSet rs = (new DB()).connection.createStatement().executeQuery(SQL);
                while (rs.next()) {
                    Integer id_session = rs.getInt("id_session");
                    String id_hall = rs.getString("id_hall");
                    String id_movie = rs.getString("id_movie");
                    data.add(new Session(id_session, id_hall, id_movie));
                }
            }

            table.setItems(data);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Error on Building Data");
        }
    }
}

public class DB {

    final String URL = "jdbc:mysql://localhost:3306/mydbtest?useUnicode=true&useSSL=true&useJDBCCompliantTimezoneShift=true" +
            "&useLegacyDatetimeCode=false&serverTimezone=UTC";

    final String USER = "root";
    final String PASS = "DLW";

    public Connection connection;

    public DB() {
        try {
            connection = DriverManager.getConnection(URL, USER, PASS);
            if (!connection.isClosed()) {
                System.out.println("Соединение с БД установлено");
            }
        } catch (SQLException e) {
            System.out.println("");
        }
    }

    public Statement getStatement() {
        try {
            return connection.createStatement();
        } catch (SQLException e) {
            System.err.println("Failed to get statement");
            e.printStackTrace();
            return null;
        }
    }

    public Connection getConnection() {
        return connection;
    }

}