package classes;

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

import java.sql.*;

public class DB {

    public static ObservableList<Employee> data = FXCollections.observableArrayList();

    private final String ip;
    private final String username;
    private final String password;
    private final String port;
    private final String database_name;
    private Connection connection;

    public DB() {
        this.ip = "192.168.99.100";
        this.username = "sa";
        this.password = "nastenjka26";
        this.port = "1433";
        this.database_name = "DBEmployee";
    }

    public void connect() throws Exception {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        connection = DriverManager.getConnection("jdbc:sqlserver://" + this.ip + ":" + this.port + ";databaseName=" + this.database_name, this.username, this.password);
    }

    public ObservableList<Employee> readAllRows() throws Exception {
        connect();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM tblEmployee");
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            data.add(new Employee(
                    rs.getInt("fldId"),
                    rs.getString("fldName"),
                    rs.getString("fldDepartment"),
                    rs.getInt("fldWeeklyHours"),
                    rs.getDate("fldDateOfEmployment")
            ));

        }
        rs.close();
        ps.close();

        return data;
    }

    public void update(Employee newEmployee) throws Exception {
        connect();
        String sql = "UPDATE tblEmployee SET fldName = ? ,"
                + "fldDepartment = ? ,"
                + "fldWeeklyHours = ? ,"
                + "fldDateOfEmployment = ? "
                + "WHERE fldId = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {

            pstmt.setString(1, newEmployee.getName());
            pstmt.setString(2, newEmployee.getDepartment());
            pstmt.setDouble(3, newEmployee.getWeeklyHours());
            pstmt.setDate(4, newEmployee.getDateOfEmployment());
            pstmt.setInt(5, newEmployee.getId());

            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }

    }

    public void updateRows() throws Exception {
        connect();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM tblEmployee WHERE fldId = ?");
        Employee oldEmployee = null;

        for (Employee newEmployee : data) {
            ps.setInt(1, newEmployee.getId());

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {

                oldEmployee = new Employee(
                        rs.getInt("fldId"),
                        rs.getString("fldName"),
                        rs.getString("fldDepartment"),
                        rs.getInt("fldWeeklyHours"),
                        rs.getDate("fldDateOfEmployment")
                );

            }
            rs.close();


            if (oldEmployee.getName() != newEmployee.getName() ||
                    oldEmployee.getDepartment() != newEmployee.getDepartment() ||
                    oldEmployee.getWeeklyHours() != newEmployee.getWeeklyHours() ||
                    oldEmployee.getDateOfEmployment() != newEmployee.getDateOfEmployment()
                    ) {
                update(newEmployee);
            }


        }

    }


    public int insertNewRow(Employee employee) throws Exception {
        connect();
        PreparedStatement ps = connection.prepareStatement("INSERT INTO tblEmployee(fldName, fldDepartment, fldWeeklyHours,fldDateOfEmployment) VALUES (?,?,?,?);");

        ps.setString(1, employee.getName());
        ps.setString(2, employee.getDepartment());
        ps.setDouble(3, employee.getWeeklyHours());
        ps.setDate(4, employee.getDateOfEmployment());

        int row = ps.executeUpdate();
        ps.close();


        return row;
    }


}