public class MedlemPane extends GridPane {

    private Button btnOpdaterMedlem, btnOpretMedlem, btnHentMedlem, btnSletMedlem;

    private final Label lblTextOpret, lblIdOpret, lblNavnOpret, lblAdresseOpret, lblTelefonOpret;
    private final Label lblListeOverMedlemer;
    private final Label lblTextOpdater, lblIdOpdater, lblNavnOpdater, lblAdresseOpdater,
        lblTelefonOpdater;

    private TextField txfIdOpret, txfNavnOpret, txfAdresseOpret, txfTelefonOpret;
    private TextField txfIdOpdater, txfNavnOpdater, txfAdresseOpdater, txfTelefonOpdater;

    private ListView<String> lvwInstruktorliste;

    public MedlemPane() {

        setPadding(new Insets(20));
        setHgap(20);
        setVgap(10);
        setGridLinesVisible(false);

        lblTextOpret = new Label("Opret nyt medlem - indtast data");
        lblTextOpret.setFont(Font.font("Verdana", 14));
        this.add(lblTextOpret, 0, 0, 2, 1);

        lblIdOpret = new Label("Id:");
        this.add(lblIdOpret, 0, 1);

        lblNavnOpret = new Label("Navn:");
        lblNavnOpret.setPrefWidth(100);
        this.add(lblNavnOpret, 0, 2);

        lblAdresseOpret = new Label("Adresse:");
        this.add(lblAdresseOpret, 0, 3);

        lblTelefonOpret = new Label("Telefonnummer:");
        this.add(lblTelefonOpret, 0, 4);

        // Knap
        btnOpretMedlem = new Button("Opret");
        this.add(btnOpretMedlem, 0, 5);
        btnOpretMedlem.setOnAction(event -> OpretMedlem());

        lblListeOverMedlemer = new Label("Liste over medlemmer");
        lblListeOverMedlemer.setFont(Font.font("Verdana", 14));
        this.add(lblListeOverMedlemer, 2, 0, 1, 1);

        lvwInstruktorliste = new ListView<>();
        this.add(lvwInstruktorliste, 2, 1, 1, 5);
        lvwInstruktorliste.setPrefWidth(200);
        lvwInstruktorliste.setPrefHeight(100);
        lvwInstruktorliste.getItems().setAll(hentMedlemmer());

        txfIdOpret = new TextField();
        txfIdOpret.setPrefWidth(150);
        txfIdOpret.setEditable(true);
        this.add(txfIdOpret, 1, 1);

        txfNavnOpret = new TextField();
        txfNavnOpret.setPrefWidth(150);
        txfNavnOpret.setEditable(true);
        this.add(txfNavnOpret, 1, 2);

        txfAdresseOpret = new TextField();
        txfAdresseOpret.setPrefWidth(150);
        txfAdresseOpret.setEditable(true);
        this.add(txfAdresseOpret, 1, 3);

        txfTelefonOpret = new TextField();
        txfTelefonOpret.setPrefWidth(150);
        txfTelefonOpret.setEditable(true);
        this.add(txfTelefonOpret, 1, 4);

        lblTextOpdater = new Label("Opdater medlem - indtast id og tryk hent");
        lblTextOpdater.setFont(Font.font("Verdana", 14));
        this.add(lblTextOpdater, 3, 0, 2, 1);

        lblIdOpdater = new Label("Id:");
        this.add(lblIdOpdater, 3, 1);

        lblNavnOpdater = new Label("Navn:");
        lblNavnOpdater.setPrefWidth(100);
        this.add(lblNavnOpdater, 3, 2);

        lblAdresseOpdater = new Label("Adresse:");
        this.add(lblAdresseOpdater, 3, 3);

        lblTelefonOpdater = new Label("Telefonnummer:");
        this.add(lblTelefonOpdater, 3, 4);

        // Knap

        btnHentMedlem = new Button("Hent");
        this.add(btnHentMedlem, 3, 5);
        btnHentMedlem.setOnAction(event -> hentMedlem());

        btnOpdaterMedlem = new Button("Opdater");
        this.add(btnOpdaterMedlem, 3, 6);
        btnOpdaterMedlem.setOnAction(event -> OpdaterMedlem());

        btnSletMedlem = new Button("Slet");
        this.add(btnSletMedlem, 3, 7);
        btnSletMedlem.setOnAction(event -> sletMedlem());

        txfIdOpdater = new TextField();
        txfIdOpdater.setPrefWidth(150);
        txfIdOpdater.setEditable(true);
        this.add(txfIdOpdater, 4, 1);

        txfNavnOpdater = new TextField();
        txfNavnOpdater.setPrefWidth(150);
        txfNavnOpdater.setEditable(true);
        this.add(txfNavnOpdater, 4, 2);

        txfAdresseOpdater = new TextField();
        txfAdresseOpdater.setPrefWidth(150);
        txfAdresseOpdater.setEditable(true);
        this.add(txfAdresseOpdater, 4, 3);

        txfTelefonOpdater = new TextField();
        txfTelefonOpdater.setPrefWidth(150);
        txfTelefonOpdater.setEditable(true);
        this.add(txfTelefonOpdater, 4, 4);

    }

    public void updateControls() {

    }

    public void OpretMedlem() {
        try {
            Connection minConnection;
            minConnection = DriverManager
                .getConnection(
                    "jdbc:sqlserver://LAPTOP-3LA06A4H\\SQLEXPRESS;databaseName=YodaFitness;user=sa;password=123123");

            Statement stmt = minConnection.createStatement();

            int id = Integer.parseInt(txfIdOpret.getText().trim());
            String navn = txfNavnOpret.getText();
            String adresse = txfAdresseOpret.getText();
            int tlf = Integer.parseInt(txfTelefonOpret.getText());

            String sql =
                "exec opretMedlem" + " " + id + ", '" + navn + "', " + "'" + adresse + "', " + tlf;

            stmt.execute(sql);

            if (stmt != null) {
                stmt.close();
            }
            if (minConnection != null) {
                minConnection.close();
            }

        }
        catch (Exception e) {
            System.out.println("fejl:  " + e.getMessage());
        }
    }

    public void OpdaterMedlem() {
        try {
            Connection minConnection;
            minConnection = DriverManager
                .getConnection(
                    "jdbc:sqlserver://LAPTOP-3LA06A4H\\SQLEXPRESS;databaseName=YodaFitness;user=sa;password=123123");

            Statement stmt = minConnection.createStatement();

            int id = Integer.parseInt(txfIdOpdater.getText().trim());
            String navn = txfNavnOpdater.getText();
            String adresse = txfAdresseOpdater.getText();
            int tlf = Integer.parseInt(txfTelefonOpdater.getText().trim());

            String sql =
                "exec updateMedlem" + " " + id + ", '" + navn + "', " + "'" + adresse + "', " + tlf;
            System.out.println(sql);

            stmt.executeUpdate(sql);

            if (stmt != null) {
                stmt.close();
            }
            if (minConnection != null) {
                minConnection.close();
            }

        }
        catch (Exception e) {
            System.out.println("fejl:  " + e.getMessage());
        }
    }

    public void sletMedlem() {
        try {
            Connection minConnection;
            minConnection = DriverManager
                .getConnection(
                    "jdbc:sqlserver://LAPTOP-3LA06A4H\\SQLEXPRESS;databaseName=YodaFitness;user=sa;password=123123");

            Statement stmt = minConnection.createStatement();

            int id = Integer.parseInt(txfIdOpdater.getText().trim());

            String sql =
                "exec deleteMedlem" + " " + id;
            System.out.println(sql);

            stmt.executeUpdate(sql);

            if (stmt != null) {
                stmt.close();
            }
            if (minConnection != null) {
                minConnection.close();
            }

        }
        catch (Exception e) {
            System.out.println("fejl:  " + e.getMessage());
        }
    }

    public void hentMedlem() {
        try {
            Connection minConnection;
            minConnection = DriverManager
                .getConnection(
                    "jdbc:sqlserver://LAPTOP-3LA06A4H\\SQLEXPRESS;databaseName=YodaFitness;user=sa;password=123123");

            Statement stmt = minConnection.createStatement();

            int id = Integer.parseInt(txfIdOpdater.getText().trim());

            ResultSet res = stmt
                .executeQuery("exec hentMedlem " + id);
            while (res.next()) {
                System.out
                    .println(res.getString(1) + "\t" + "\t" + res.getString(2) + "\t" + "\t"
                        + res.getString(3));
                txfNavnOpdater.setText(res.getString(2));
                txfAdresseOpdater.setText(res.getString(3));
                txfTelefonOpdater.setText(res.getString(4));
            }

            if (res != null) {
                res.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (minConnection != null) {
                minConnection.close();
            }

        }
        catch (Exception e) {
            System.out.println("fejl:  " + e.getMessage());
        }

    }

    public ArrayList<String> hentMedlemmer() {
        ArrayList<String> medlemmer = new ArrayList<>();
        try {
            Connection minConnection;
            minConnection = DriverManager
                .getConnection(
                    "jdbc:sqlserver://LAPTOP-3LA06A4H\\SQLEXPRESS;databaseName=YodaFitness;user=sa;password=123123");

            Statement stmt = minConnection.createStatement();

            ResultSet res = stmt
                .executeQuery("select * from Medlem_tbl");
            while (res.next()) {
                medlemmer.add((res.getString(1) + "\t" + res.getString(2)));
            }

            if (res != null) {
                res.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (minConnection != null) {
                minConnection.close();
            }

        }
        catch (Exception e) {
            System.out.println("fejl:  " + e.getMessage());
        }

        return medlemmer;
    }
}