package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataBase {

    private static String databaseUserName = "root"; // MySql user name
    private static String databasePassword = "root"; // MySQL password
    private static String URL = "jdbc:mysql://localhost:3306/sdms";

    private static String databaseDriver = "com.mysql.jdbc.Driver";

    private static Connection con = null;
    private static Statement st = null;

    public DataBase() {
    }

    static Connection getConnection() throws SQLException {

        if (con == null) {
            try {
                Class.forName(databaseDriver);
                con = DriverManager.getConnection(URL,databaseUserName,databasePassword);


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

            }
        }
        return con;
    }

    static boolean authenticateUser(String s, String s1) {

        try {
            con = getConnection();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {



            st = con.createStatement();

            String query = "select * from sdms.signup where empid = '" + s + "' and  Password = '" + s1 + "'";
            ResultSet rs = st.executeQuery(query);
            if(rs.next()){
                return true;
            }


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

    }

    public static String getUsername(String userid) {
        ResultSet rs = null;
        try {

            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("select first_name from sdms.signup where empid = '" + userid + "'");
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if (rs.next()) {

                return rs.getString("first_name");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;

    }
     public static String[] getEmailIDPassword(String userid) {
        ResultSet rs = null;
        
        String[]details=new String[4];
        try {

            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("select email,password from sdms.signup where empid = '" + userid + "'");
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            if (rs.next()) {

                details[0]=rs.getString("email");
                details[1]=rs.getString("password");
                return details;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;

    }
     
    
    public static ArrayList getMeetingStatus(String agenda,String userid) {
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ResultSet rs4 = null;
        ArrayList list = new ArrayList();
        ArrayList rejvals = new ArrayList();
        ArrayList totallist = new ArrayList();
        ArrayList namelist2 = new ArrayList();
        String[]details =null;
        try {
            con=getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("SELECT meetingid,agenda,empid,startDateTime,endDateTime" +
                    ",count(status) from scheduledmeetings where agenda IN " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status=5)" +
                    "and status=1 group by meetingid");
            System.out.println("SELECT meetingid,agenda,empid,startDateTime,endDateTime" +
                    ",count(status) from scheduledmeetings where agenda IN " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status=5)" +
                    "and status=1 group by meetingid");
            System.out.println("Query Executed");
            while (rs.next()) {
                details=new String[5];
                details[0]=rs.getString("meetingid");
                details[1]=rs.getString("Agenda");
                System.out.println("Agenda :"+details[1]);
                details[2]=rs.getInt("count(status)")+"";
                details[3]=rs.getTimestamp("startDateTime")+"";
                details[4]=rs.getTimestamp("endDateTime")+"";
                list.add(details);
                
            }
            
            rs2 = st.executeQuery("SELECT count(status) from scheduledmeetings " +
                    "where agenda IN (select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status =5) " +
                    "group by status having status='2'");
            System.out.println("SELECT count(status) from scheduledmeetings " +
                    "where agenda IN (select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status =5) " +
                    "group by status having status='2'");
            System.out.println("Query 2 Executed");
            while (rs2.next()) {
                details=new String[3];
                details[0]=rs2.getInt("count(status)")+"";
                rejvals.add(details);
            }
            
            rs4 = st.executeQuery("SELECT login.first_name from scheduledmeetings s,signup login " +
                    "where s.agenda IN (select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status=5 ) " +
                    "and s.status=0 and s.empid=login.empid");
            System.out.println("SELECT login.first_name from scheduledmeetings s,signup login " +
                    "where s.agenda IN (select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='"+ agenda +"' and status=5 ) " +
                    "and s.status=0 and s.empid=login.empid");
            System.out.println("Query 3 Executed");
            while (rs4.next()) {
                details=new String[3];
                details[0]=rs4.getString("login.first_name");
                namelist2.add(details);
            }
        /*
         *   0 - invited
         *   1 - accepted
         *   2 - rejected
         *   3 - proposed
         *   4 - tentative
         */
           totallist.add(list) ;
           totallist.add(rejvals) ;
           totallist.add(namelist2) ;
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return totallist;
        
    }
    

    public static ArrayList getMeetingDetails(String agenda, String userid) {
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ResultSet rs4 = null;
        ArrayList equipvals = new ArrayList();
        ArrayList totallist = new ArrayList();
        ArrayList list2 = new ArrayList();
        String[] val = null;
        try {
            con = getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("SELECT distinct login.first_name,s.status,s.startDateTime,s.endDateTime,s.is_active,s.meetingid,s.empid " +
                    "from signup login ,scheduledmeetings s where s.agenda IN " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and agenda ='" + agenda + "' and status=5) " +
                    "and s.status!=0 and s.empid=login.empid order by s.empid");
            System.out.println("SELECT distinct login.first_name,s.status,s.startDateTime,s.endDateTime,s.meetingid," +
                    "equipmentname from equipmentlist ,equipreq e ,signup login ,scheduledmeetings s " +
                    "where equipmentId in (SELECT equipid FROM sdms.equipreq r where r.meetingid = s.meetingid and empid ='" + userid + "')" +
                    "and s.startDateTime IN (select startDateTime from scheduledmeetings where empid='" + userid + "' and agenda ='" + agenda + "') " +
                    "and s.status!=0 and s.empid=login.empid");
            System.out.println("Query Executed");
            while (rs.next()) {
                val = new String[10];
                val[0] = rs.getString("first_name");
                val[1] = rs.getInt("status") + "";
                val[2] = rs.getTimestamp("startDateTime") + "";
                val[3] = rs.getTimestamp("endDateTime") + "";
                val[4] = rs.getInt("is_active") + "";
                val[5] = rs.getString("empid");
                list2.add(val);
            }

            /*  rs1 = st.executeQuery("select distinct empid from scheduledmeetings where agenda in (select agenda from scheduledmeetings where empid='" + userid + "')");
            while (rs1.next()) 
            {
            String temp = rs1.getString("empid");
            userlist.add(temp);
            }
             *if(!userlist.isEmpty())
            {
            String id = null;
            for(int i=0;i<userlist.size();i++)
            {
            id = (String)userlist.get(i);
             */

            rs2 = st.executeQuery("select empid,equipmentlist.equipmentName from equipreq,equipmentlist where " +
                    "meetingid IN(select distinct meetingid from scheduledmeetings s where agenda ='" + agenda + "' and " +
                    "empid IN (select distinct empid from scheduledmeetings where agenda in (select agenda from scheduledmeetings where empid='" + userid + "'))" +
                    "order by s.empid) and equipreq.equipid = equipmentlist.equipmentId");


            System.out.println("select empid,equipmentlist.equipmentName from equipreq,equipmentlist where " +
                    "meetingid IN(select distinct meetingid from scheduledmeetings s where agenda ='" + agenda + "' and " +
                    "empid IN (select distinct empid from scheduledmeetings where agenda in (select agenda from scheduledmeetings where empid='" + userid + "'))" +
                    "order by s.empid) and equipreq.equipid = equipmentlist.equipmentId");
            System.out.println("Query 2 Executed");
            while (rs2.next()) {
                val = new String[10];
                val[0] = rs2.getString("empid");
                val[1] = rs2.getString("equipmentName");
                equipvals.add(val);
            }


        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }

        totallist.add(list2);
        totallist.add(equipvals);
        return totallist;

    }

    public static ArrayList getRenegotiationDetails(String agenda) {
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ResultSet rs4 = null;
        ArrayList equipvals = new ArrayList();
        ArrayList totallist = new ArrayList();
        ArrayList list2 = new ArrayList();
        String[] val = null;
        try {
            con = getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("select scheduledmeetings.roomid,name,startDateTime,endDateTime,agenda from scheduledmeetings , roomlist where " +
                    "agenda ='" + agenda + "' and status != 5 and scheduledmeetings.roomid = roomlist.roomid");
            System.out.println("select name,scheduledmeetings.roomid,startDateTime,endDateTime from scheduledmeetings , roomlist where " +
                    "agenda ='" + agenda + "' and status != 5 and scheduledmeetings.roomid = roomlist.roomid");

            System.out.println("Query Executed");
            while (rs.next()) {
                val = new String[10];
                val[0] = rs.getInt("roomid") + "";
                val[1] = rs.getString("name");

                Timestamp startdatetime = rs.getTimestamp("startDateTime");
                Timestamp enddatetime = rs.getTimestamp("endDateTime");
                SimpleDateFormat sdf = new SimpleDateFormat("EEE, MMM d, yyyy [HH:mm]");
                val[2] = sdf.format(startdatetime);
                val[3] = sdf.format(enddatetime);
                val[4] = rs.getString("agenda");
                list2.add(val);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list2;

    }
    public static ArrayList<ArrayList<String[]>> updateRenogiationDetails(String agenda,String userid) {
        ResultSet rs = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        ArrayList meetingids = new ArrayList();
        ArrayList totallist = new ArrayList();
        ArrayList isactivelist = new ArrayList();
        ArrayList equiplist = new ArrayList();
        String[]val =null;
        try {
            con=getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            st = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs = st.executeQuery("select distinct meetingid from scheduledmeetings where agenda in " +
                    "(select agenda from scheduledmeetings where empid ='" + userid + "' and status=5 )");
            while (rs.next()) {
                val=new String[10];
                val[0]=rs.getInt("meetingid")+"";
                meetingids.add(val);
            }
            rs2 = st.executeQuery("select distinct empid,is_active from scheduledmeetings where agenda in " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "'and status=5 and agenda='" + agenda + "')");
            System.out.println("Query 2 Executed");
            
            while (rs2.next()) {
                val=new String[10];
                val[0]=rs2.getString("empid");
                val[1]=rs2.getInt("is_active")+"";
                
                isactivelist.add(val);
            }
            System.out.println("Query 2 :"+"select empid,equipid,meetingid from equipreq where " +
                    "meetingid IN(select meetingid from scheduledmeetings s where agenda ='" + agenda + "' and " +
                    "empid IN (select empid from scheduledmeetings where agenda in " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and status=5)) order by s.empid))");
            rs3 = st.executeQuery("select distinct empid,equipid from equipreq where " +
                    "meetingid IN(select meetingid from scheduledmeetings s where agenda ='" + agenda + "' and " +
                    "empid IN (select empid from scheduledmeetings where agenda in " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and status=5)) order by s.empid)");
            System.out.println("select distinct empid,equipid from equipreq where " +
                    "meetingid IN(select meetingid from scheduledmeetings s where agenda ='" + agenda + "' and " +
                    "empid IN (select empid from scheduledmeetings where agenda in " +
                    "(select agenda from scheduledmeetings where empid='" + userid + "' and status=5)) order by s.empid)");
            System.out.println("Query 3 Executed");
            while (rs3.next()) {
                val=new String[10];
                val[0]=rs3.getString("empid");
                val[1]=rs3.getInt("equipid")+"";
                //val[2]=rs3.getInt("meetingid")+"";
                equiplist.add(val);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        totallist.add(meetingids);
        totallist.add(isactivelist);
        totallist.add(equiplist);
        
        return totallist;
        
    }

    
 }
