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

import objects.Staff;

import java.sql.*;

/**
 * This class for connects with DB
 */
public class CollectionListStaff implements ListStaff{

    private ObservableList<Staff> staffList =
            FXCollections.observableArrayList();

    /**
     *Add new staff in DB
     */
    @Override
    public void add(Staff staff) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/office_datas?useUnicode=true&characterEncoding=utf-8";
            String login = "root";
            String password = "";
            Connection con = DriverManager.getConnection(url, login, password);

                Statement stmt = con.createStatement();
                String sql_query = getQuery(0, staff);
                System.out.println(sql_query);
                stmt.executeUpdate(sql_query, Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = stmt.getGeneratedKeys();
                String lastId = null;
                while (rs.next()) {
                    lastId = rs.getString(1);
                    System.out.println(rs.getString(1));
                }
                sql_query = getQuery(1, null) + lastId;
                System.out.println(sql_query);
                rs = stmt.executeQuery(sql_query);
                for (int i =0; i<100; i++) System.out.print("-");
                processAnswer(rs);
                rs.close();
                    stmt.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    /**
     * Add staf in List
     * @param staff
     */
    public void addS(Staff staff){
        staffList.add(staff);
    }

    /**
     * Update staff's select in DB
     * @param staff
     */
    @Override
    public void update(Staff staff){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/office_datas?useUnicode=true&characterEncoding=utf-8";
            String login = "root";
            String password = "";

            try (Connection con = DriverManager.getConnection(url, login, password)) {
                Statement stmt = con.createStatement();
                String sql_query = null;
                sql_query = getQuery(2, staff);
                System.out.println(sql_query);
                int rs = stmt.executeUpdate(sql_query);
                System.out.println(rs);
                stmt.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Deleted select of staff from db and list
     * @param staff
     */
    @Override
    public void delete(Staff staff) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/office_datas?useUnicode=true&characterEncoding=utf-8";
            String login = "root";
            String password = "";
            Connection con = DriverManager.getConnection(url, login, password);

            try {
                Statement stmt = con.createStatement();
                String sql_query = getQuery(3, null) +staff.getID_staff();
                System.out.println(sql_query);
                int rs = stmt.executeUpdate(sql_query);
                System.out.println(rs);
                stmt.close();
            } finally {
                con.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        staffList.remove(staff);
    }

    public void clearList(){ staffList.clear(); }

    public void deleteSelectFromList(Staff staff){ staffList.remove(staff);}

    public ObservableList<Staff> getStaffList() {
     return staffList;
    }

    /**
     * Download data from DB
     */
    public void filltestData()
    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/office_datas";
            String login = "root";
            String password = "";
            Connection con = DriverManager.getConnection(url, login, password);
            try {
                Statement stmt = con.createStatement();
                String sql_query = getQuery(4, null);
                System.out.println(sql_query);
                ResultSet rs = stmt.executeQuery(sql_query);
                for (int i =0; i<100; i++)  System.out.print("-");
                processAnswer(rs);
                rs.close();
                stmt.close();
            } finally {
                con.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Get text of query for to do query in DB
     * @param mNumber
     * @param staff
     * @return
     */
    private String getQuery(int mNumber, Staff staff){
        switch (mNumber){
            case 0:
                return "INSERT INTO `staff` (`id_staff`, `surname`, `name`, `father_name`, " +
                        "`d_of_birth`, `num_passp`, `passp_private_num`, " +
                        "`address`, `tel_1`, `tel_2`, `add_info`, `type_work`, `position`) VALUES(NULL,"
                        + "'" + staff.getSurname() + "', "
                        + "'" + staff.getName() + "', "
                        + "'" + staff.getFathName() + "', "
                        + "'" + staff.getDateOfBirth() + "', "
                        + "'" + staff.getNummPass() + "', "
                        + "'" + staff.getNumPrivate() + "', "
                        + "'" + staff.getAddress() + "', "
                        + "'" + staff.getTel1() + "', "
                        + "'" + staff.getTel2() + "', "
                        + "'" + staff.getAddInfo() + "', "
                        + "'" + staff.getTypeWork() + "', "
                        + "'" + staff.getPosition() + "')";
            case 1:
                return "SELECT * FROM `staff` WHERE `id_staff` = ";
            case 2:
                return "UPDATE `staff` SET "
                        + "`surname` =  " + "'" + staff.getSurname() + "',"
                        + "`name` =  " + "'" + staff.getName() + "',"
                        + "`father_name` =  " + "'" + staff.getFathName() + "',"
                        + "`num_passp` =  " + "'" + staff.getNummPass() + "',"
                        + "`passp_private_num` =  " + "'" + staff.getNumPrivate() + "',"
                        + "`address` =  " + "'" + staff.getAddress() + "',"
                        + "`tel_1` =  " + "'" + staff.getTel1() + "',"
                        + "`tel_2` =  " + "'" + staff.getTel2() + "',"
                        + "`add_info` =  " + "'" + staff.getAddInfo() + "',"
                        + "`d_of_birth` =  " + "'" + staff.getDateOfBirth() + "',"
                        + "`type_work` =  " + "'" + staff.getTypeWork() + "',"
                        + "`position` =  " + "'" + staff.getPosition() + "'"
                        + " WHERE `staff`.`id_staff` = " + staff.getID_staff();
            case 3:
                return "DELETE FROM `staff`"
                        + " WHERE `staff`.`id_staff` = ";
            case 4:
                return "SELECT * FROM `staff`";
        }
        return null;
    }

    /**
     * Processing answer from DB
     * @param rs
     * @throws SQLException
     */
    private void processAnswer(ResultSet rs) throws SQLException {
        while (rs.next()) {
            String str = "{ id_staff = " + rs.getString("id_staff")
                    + " || surname = " + rs.getString("surname")
                    + " || name = " + rs.getString("name")
                    + " || father_name = " + rs.getString("father_name")
                    + " || td_of_birth = " + rs.getString("d_of_birth")
                    + " || num_passp = " + rs.getString("num_passp")
                    + " || passp_private_num = " + rs.getString("passp_private_num")
                    + " || address = " + rs.getString("address")
                    + " || tel_1 = " + rs.getString("tel_1")
                    + " || tel_2 = " + rs.getString("tel_2")
                    + " || add_info = " + rs.getString("add_info")
                    + " || type_work = " + rs.getString("type_work")
                    + " || position = " + rs.getString("position") + "; }";
            System.out.println("info: " + str);

            staffList.add(new Staff(
                    Integer.parseInt(rs.getString("id_staff")),
                    rs.getString("surname"),
                    rs.getString("name"),
                    rs.getString("father_name"),
                    rs.getString("num_passp"),
                    rs.getString("passp_private_num"),
                    rs.getString("address"),
                    rs.getString("tel_1"),
                    rs.getString("tel_2"),
                    rs.getString("add_info"),
                    Integer.parseInt(rs.getString("type_work")),
                    rs.getString("position"),
                    rs.getString("d_of_birth")));
                    for (int i =0; i<100; i++) System.out.print("-");        }
    }
}