import java.sql.*;

public class MySql {
    private boolean programulMerge;
    private boolean userlogat = false;
    private boolean userDublat = true;
    private int idnew_table;
    private String asignatLui;
    private Connection conexiuneBazaDate;
    private  final String ANSI_RESET = "\u001B[0m";
    private  final String ANSI_PURPLE = "\u001B[35m";



    public MySql() throws SQLException {
        conexiuneBazaDate = DriverManager.getConnection("jdbc:mysql://localhost:3306/bazadedate",
               "root", "admin");
    }


    public boolean registerUser(User userNou) {
        String sqlCmd = "INSERT INTO userlogare (nume, parola, mail)" + "Values (" +
                "'" + userNou.getUsername() + "'," + "'" + userNou.getParola() + "'," + " ' "
                + userNou.getMail() + "')";
        try {
            Statement statement = conexiuneBazaDate.createStatement();
            return statement.execute(sqlCmd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return programulMerge = true;
    }


    public boolean login(String user, String parola) {
        String sqlLog = " SELECT nume, parola FROM userlogare WHERE nume = '" + user
                + "' AND  parola = '" + parola + "'";
        try {
            Statement statement = conexiuneBazaDate.createStatement();
            ResultSet resultSet = statement.executeQuery(sqlLog);
            if (resultSet.next() == true) {
                String usernameFromDB = resultSet.getString("nume");
                String passwordFromDB = resultSet.getString("parola");

                if ((usernameFromDB.equalsIgnoreCase(user)) && (passwordFromDB.equalsIgnoreCase(parola))) {
                    System.out.println("Te-ai logat! ");
                    userlogat = true;
                }
                }
            else {
                System.out.println("Username sau paroala gresita");
                userlogat = false;
            }
                return userlogat;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  userlogat;
    }


    public void creareTichet(String titlu, String descriere, String creator) {
        String sqlTichet = "INSERT INTO tichet (titlu, descriere, creator)" + "Values (" +
                "'" + titlu + "'," + "'" + descriere + "'," + "'"
                + creator + "')";
        try {
            Statement statement = conexiuneBazaDate.createStatement();
            statement.execute(sqlTichet);
            idnew_table++;
            System.out.println (" Tichetul a fost incarcat in baza de date si va fi atasat unui tehnician.");
            programulMerge = true;
            asignareLa(idnew_table);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println ("Tichetul nu a putut fi adaugat. Te rog incearca mai tarziu");
            programulMerge = false;
        }
    }


    public void asignareLa(int idTichet) {
            String sqlAsig =   "select asignat_la, count(*) from tichet GROUP BY asignat_la ORDER BY asignat_la desc LIMIT 1";
            try {
                Statement statement = conexiuneBazaDate.createStatement();
                ResultSet resultSet = statement.executeQuery(sqlAsig);
                if (resultSet.next() == true) {
                     asignatLui = resultSet.getString("asignat_la");
                }
                } catch (SQLException e){
                    e.printStackTrace();
                }
                 sqlAsig =  "UPDATE tichet SET asignat_la = '" + asignatLui + "' WHERE idnew_table = '" + idTichet + "'";
            try {
                Statement statement = conexiuneBazaDate.createStatement();
                statement.execute(sqlAsig);
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }


    public String verificaTichet (String user){
        String sqlVer = "select * from tichet where creator ='" + user +"'";
        try {
            Statement statement = conexiuneBazaDate.createStatement();
            ResultSet resultSet = statement.executeQuery(sqlVer);
            String raspuns = "";

            while (resultSet.next() != false) {
                String titluTichetFromDB = resultSet.getString("titlu");
                String descriereTichetFromDB = resultSet.getString("descriere");
                String statusFromDB = resultSet.getString("asignat_la");
                if (statusFromDB == null) {
                    statusFromDB = "Tichetul dumneavoastra nu a fost inca asignat.";
                }
                    int nr = 1;
                    String raspuns2;
                    String raspuns1 = nr++ + "." + ANSI_PURPLE + "Titlu: " + ANSI_RESET + titluTichetFromDB + ANSI_PURPLE + " Descriere: " + ANSI_RESET
                            + descriereTichetFromDB + ". ";
                    if (statusFromDB.contains("Tichetul")) {
                        raspuns2 = statusFromDB;
                    } else {
                        raspuns2 = "Tichetul dumneavoastra este asigat lui " + statusFromDB + ".";
                    }
                    raspuns = raspuns + raspuns1 + raspuns2 + "\n";
                }

            return raspuns;
        }
        catch (SQLException e) {
                e.printStackTrace();
            String con  = "A aparut o eroare, va rugam incercati mai tarziu";
            return  con;
            }
    }


    public void userValid (String user) {
        String sqlVld = "select nume from userlogare";
        try {
            Statement statement = conexiuneBazaDate.createStatement();
            ResultSet resultSet = statement.executeQuery(sqlVld);
            String verificare = "";
            while (resultSet.next() == true) {
                    verificare = resultSet.getString("nume");
                    if (verificare.equalsIgnoreCase(user)) {
                        System.out.print("Userul " + verificare + " este deja inregistrat. ");
                        return;
                    }

                }
                userDublat = false;


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


    public boolean isProgramulMerge() {
        return programulMerge;
    }


    public boolean isUserlogat() {
        return userlogat;
    }

    public boolean isUserValid() {
        return userDublat;
    }
}