package Chat;

import java.sql.*;

import Settings.*;
import org.apache.log4j.Logger;

public class GestioneDB {

    final static Logger logger = Logger.getLogger(GestioneDB.class);

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement preparedStmt = null;

    private String sql;

    private final String nomeDriver = "com.mysql.jdbc.Driver";
    private String serverURL = "";
    private String user = "";
    private String password = "";

    Settings Settings = new Settings();

    public GestioneDB(){

        serverURL = "jdbc:mysql://" + Settings.getIp_mysql() + ":" + Settings.getPort_mysql() + "/" + Settings.getDbName();

        user = Settings.getUser_mysql();

        password = Settings.getPwd_mysql();

        try {

            Class.forName(nomeDriver);

        } catch (ClassNotFoundException ex) {

            logger.fatal("Chat.GestioneDB.0: " + ex.getMessage());

        }

    }

    public String getMessage(String classe) throws SQLException{

        String chars_split_value = "~°~";
        String chars_split_message = "=_=";

        String messages = "";
        String message = "";

        try{

            conn = DriverManager.getConnection(serverURL, this.user, this.password);

            stmt = conn.createStatement();

            sql = "SELECT chat.mail, name, surname, message, data_ora FROM chat INNER JOIN accounts ON accounts.mail = chat.mail INNER JOIN classes ON chat.id_class = classes.id WHERE classes.class = '" + classe + "' ORDER BY data_ora";

            rs = stmt.executeQuery(sql);

            while(rs.next()){

                String mail = rs.getString("mail");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String data_ora = rs.getTimestamp("data_ora").toString();
                String mess = rs.getString("message");

                message = mail + chars_split_value + name + chars_split_value + surname + chars_split_value + data_ora + chars_split_value + mess;

                messages += message + chars_split_message;

            }

            if(messages != ""){

                messages = messages.substring(0, messages.length() - 3);

            }else{

                messages = "no-message";

            }

        } catch (SQLException ex) {

            logger.error("Chat.GestioneDB.1: " + ex.getMessage());

        } finally {

            if(conn != null){

                stmt.close();
                conn.close();

            }


        }

        return messages;

    }

    public String getMessage(String classe, String last_date) throws SQLException{

        String chars_split_value = "~°~";
        String chars_split_message = "=_=";

        String messages = "";
        String message = "";

        try{

            conn = DriverManager.getConnection(serverURL, this.user, this.password);

            stmt = conn.createStatement();

            sql = "SELECT chat.mail, name, surname, message, data_ora FROM chat INNER JOIN accounts ON accounts.mail = chat.mail INNER JOIN classes ON chat.id_class = classes.id WHERE classes.class = '" + classe + "' AND data_ora > '" + last_date + "' ORDER BY data_ora";

            rs = stmt.executeQuery(sql);

            while(rs.next()){

                String mail = rs.getString("mail");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String data_ora = rs.getTimestamp("data_ora").toString();
                String mess = rs.getString("message");

                message = mail + chars_split_value + name + chars_split_value + surname + chars_split_value + data_ora + chars_split_value + mess;

                messages += message + chars_split_message;

            }

            if(messages != ""){

                messages = messages.substring(0, messages.length() - 3);

            }else{

                messages = "no-message";

            }

        } catch (SQLException ex) {

            logger.error("Chat.GestioneDB.1: " + ex.getMessage());

        } finally {

            if(conn != null){

                stmt.close();
                conn.close();

            }


        }

        return messages;

    }

    public void sendMessage(String mail, String message, String classe) throws SQLException{

        try{

            conn = DriverManager.getConnection(serverURL, this.user, this.password);

            stmt = conn.createStatement();

            sql = "SELECT id FROM classes WHERE class ='" + classe + "'";

            rs = stmt.executeQuery(sql);

            rs.next();

            int id_class = rs.getInt("id");


            sql = "INSERT INTO chat(mail, message, id_class, data_ora) VALUES (?, ?, ?, NOW())";

            preparedStmt = conn.prepareStatement(sql);

            preparedStmt.setString(1, mail);
            preparedStmt.setString(2, message);
            preparedStmt.setInt(3, id_class);

            preparedStmt.execute();

        } catch (SQLException ex) {

            logger.error("Chat.GestioneDB.3: " + ex.getMessage());

        } finally {

            if(conn != null){

                stmt.close();
                preparedStmt.close();
                conn.close();

            }

        }

    }

    public boolean check_query(String sql){

        boolean ok = false;

        String c_t_e = "& < > ' / \\ ; , - _ ^ = ) ( % $ ! | # [ ] { } * ° ç § " + '"';

        String[] chars_to_escape = c_t_e.split(" ");

        if( ( !sql.equals("") ) ){

            ok = true;

        }

        for(String c : chars_to_escape){

            if(sql.contains((c))){

                ok = false;

                break;

            }

        }

        return ok;

    }

}