import java.io.IOException;
import java.sql.*;
import java.util.*;

class InputSeeker {
    private Scanner sc = new Scanner(System.in);
    private int userId = 0;
    private String name = null;
    private String mobileNumber = null;
    private float amountSpent = 0;
    private int groupId;
    private String ghostVar = null;

    // Name seeker
    public void nameSeeker() {
        System.out.print("Enter the Name: ");
        name = sc.next();
    }

    public String getName() {
        return name;
    }

    // id seeker
    public void idSeeker() {
        System.out.print("Assign the Participant an id : ");
        userId = sc.nextInt();
    }

    public int getId() {
        return userId;
    }

    // number seeker
    public void amountSpentSeeker() {
        System.out.println("Enter the amount: ");
        amountSpent = sc.nextFloat();
    }

    public float getAmountSeeker() {
        return amountSpent;
    }

    // mobile number
    public void mobileNumberSeeker() {
        System.out.println("Enter the mobile number: ");
        mobileNumber = sc.next();
    }

    public String getMobileNumber() {
        return mobileNumber;
    }

    // group id
    public void groupIdSeeker() {
        System.out.println("Assign Group id: ");
        groupId = sc.nextInt();
    }

    public int getGroupId() {
        return groupId;
    }

    public void ghosVartSeeker() {
        ghostVar = sc.next();
    }

    public String getghostVar() {
        return ghostVar;
    }
}

class Uservar {
    int userId;
    String userName;
    float amountSpent;
    String mobileNumber;
    int groupId;
    float finalDebt;
    int RowCount;
}

class UserDAO {
    Connection con = null;

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // while in developers mode use verify Server certificate = false && useSSL=true
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/markone?verifyServerCertificate=false&useSSL=true", "root",
                    "nishant97");
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public void addUser_catalogue(Uservar u) {
        String query = "insert into user_catalogue value(?,?,?,?)";

        try {

            PreparedStatement pst = con.prepareStatement(query);
            pst.setInt(1, u.userId);
            pst.setString(2, u.userName);
            pst.setInt(3, u.groupId);
            pst.setInt(4, 0);
            pst.executeUpdate();

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public void addUser_contact(Uservar u) {
        String query = "insert into user_contact value(?,?)";
        try {
            PreparedStatement pst = con.prepareStatement(query);
            pst.setString(1, u.userName);
            pst.setString(2, u.mobileNumber);
            pst.executeUpdate();

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public void addUser_expenditure(Uservar u) {
        String query = "insert into user_expenditure value(?,?)";
        try {
            PreparedStatement pst = con.prepareStatement(query);
            pst.setString(1, u.mobileNumber);
            pst.setFloat(2, u.amountSpent);
            pst.executeUpdate();

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public Uservar getUservar(String name) {
        try {
            String query = "select user_catalogue.User_name,user_catalogue.User_id,user_catalogue.group_id,user_catalogue.final_debt,user_contact.mobile_no,user_expenditure.amount_spent\r\n"
                    + "from user_catalogue,user_contact,user_expenditure\r\n" + "where user_catalogue.User_name='"
                    + name + "'" + "and user_contact.User_name= user_catalogue.User_name\r\n"
                    + "and user_contact.mobile_no= user_expenditure.mobile_no";
            String query2 = "select user_catalogue.User_name,user_catalogue.User_id,user_catalogue.group_id,user_catalogue.final_debt,user_contact.mobile_no,user_expenditure.amount_spent\r\n"
                    + "from user_catalogue,user_contact,user_expenditure\r\n"
                    + "where user_contact.User_name= user_catalogue.User_name\r\n"
                    + "and user_contact.mobile_no= user_expenditure.mobile_no order By user_expenditure.amount_spent";

            Uservar u = new Uservar();
            u.userName = name;
            Statement st = con.createStatement();
            String QUERY = query;

            if (name == "show_all") {
                QUERY = query2;
            }

            ResultSet rs = st.executeQuery(QUERY);

            while (rs.next()) {
                String nname = rs.getString(1);
                int id = rs.getInt(2);
                int groupiid = rs.getInt(3);
                float debt = rs.getFloat(4);
                String mobileNo = rs.getString(5);
                float amount = rs.getFloat(6);
                rs.last();
                int RowCount = rs.getRow();

                u.userId = id;
                u.userName = nname;
                u.groupId = groupiid;
                u.finalDebt = debt;
                u.mobileNumber = mobileNo;
                u.amountSpent = amount;
                u.RowCount = RowCount;

                return u;
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return null;
    }

}

class EntryShowDelete {

    public void addUser() {
        UserDAO dao = new UserDAO();
        Uservar uvar = new Uservar();
        InputSeeker is1 = new InputSeeker();

        is1.nameSeeker();
        is1.groupIdSeeker();
        is1.idSeeker();
        is1.mobileNumberSeeker();
        is1.amountSpentSeeker();
        dao.connect();

        uvar.userId = is1.getId();
        uvar.userName = is1.getName();
        uvar.groupId = is1.getGroupId();
        uvar.mobileNumber = is1.getMobileNumber();
        uvar.amountSpent = is1.getAmountSeeker();

        dao.addUser_catalogue(uvar);
        dao.addUser_contact(uvar);
        dao.addUser_expenditure(uvar);
    }

    public void showSeacrhedUser() {
        UserDAO dao1 = new UserDAO();
        dao1.connect();
        InputSeeker isvar1 = new InputSeeker();
        isvar1.nameSeeker();
        Uservar uvar2 = dao1.getUservar(isvar1.getName());
        String UserData = "User_id: " + uvar2.userId + " User_Name: " + uvar2.userName + " Group_id: " + uvar2.groupId
                + " Final_debt: " + uvar2.finalDebt + " mobile no.:" + uvar2.mobileNumber + " Amount Contributed: "
                + uvar2.amountSpent + "\n";
        System.out.println(UserData);
        System.out.println("Showing 1 out of " + uvar2.RowCount + " Rows");

    }

    public void AlgoDestilas() {

    }

}

class MiddleEnd {
    public void algo() {
        UserDAO dao2 = new UserDAO();
        dao2.getUservar("Show_all");
        // for(int i<)
    }
}

public class User {

    public static void main(String[] args) throws IOException {

        // EXPECTED MENU FORMAT
        @SuppressWarnings("resource")
        Scanner fcc = new Scanner(System.in);
        System.out.println("Enter one of the following commands:");
        System.out.println("1 - DataEntry");
        System.out.println("2 - Search For Entery(by name): ");
        System.out.println("3 - Print all users with their amount");
        System.out.println("4 - exit");
        System.out.println("Select option: ");
        int choiceentry = fcc.nextInt();

        do {
            switch (choiceentry) {
            case 1:
                EntryShowDelete esd = new EntryShowDelete();
                esd.addUser();
                break;
            case 2:
                EntryShowDelete esd1 = new EntryShowDelete();
                esd1.showSeacrhedUser();
                break;
            case 4:
                System.exit(0);

            case 3:
            }

            System.out.println("Select option: ");
            choiceentry = fcc.nextInt();
        } while (choiceentry != 3);
    }
}