package com.reallinkbilling.bulksmsgateway;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.db.GetDBConnection;

import com.reallinkbilling.infrainfotechsms.Root;
import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class AddBulkSMSGatewayResponseToDB {
private HttpSession session;
	
	public Map<String,String> addBulkSMSGatewayResponseToDB(SessionMap<String, Object> sessionMap, String resp, String mobileNumber, String service_provider, String senderId, String message, String messageReason)
	{
		
		Map<String, String > sendResponseDetails = new HashMap<String,String>();
		this.setSession(ServletActionContext.getRequest().getSession(false)); 
		
        if(this.getSession()==null || this.getSession().getAttribute("login")==null){ 
        
        	
        }  
        else{
        
        	
        	String shopId = (String) sessionMap.get("shopId");
    		String shopCode = (String) sessionMap.get("shopCode");
    	
        
       
		ObjectMapper mapper = new ObjectMapper();
	     
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) this.getSession().getAttribute("db");
		
	     try {
	    	 
	    	 BulkSMSGatewayMessageReplyPojo messageReplyPojo = mapper.readValue(resp,BulkSMSGatewayMessageReplyPojo.class);
	    	 
	    	 Response response = messageReplyPojo.getResponse()[0];
	    		
				connection = GetDBConnection.getDBConnection(db);
				connection.setAutoCommit(false);
				
	    	 String messageBalance = messageReplyPojo.getRemainingcredits();
	    	 
	    	 sql = "UPDATE "
	    	 		+ "shop_settings SET setting_value = ? "
	 				+ "WHERE "
	 				+ "setting_name = 'messagebalance' "
	 				+ "AND "
	 				+ "shop_id = ? "
	 				+ "AND "
	 				+ "shop_code = ?";
	    	
	    	 preparedStatement = connection.prepareStatement(sql);
	    	 
	    	 preparedStatement.setString(1, messageBalance);
	    	 preparedStatement.setString(2, shopId);
	    	 preparedStatement.setString(3, shopCode);
	    	 preparedStatement.executeUpdate();   	 
	    	
	         /*System.out.println("Balance is :"+messageReplyPojo.getBalance());
	         System.out.println("Cost is : "+messageReplyPojo.getCost());
	         System.out.println("Status : "+messageReplyPojo.getStatus());
	         System.out.println("Batch ID : "+messageReplyPojo.getBatch_id());
	         System.out.println("Sender ID : "+messageReplyPojo.getMessageObject().getSender());*/
	    	 
	    	 preparedStatement.close();
	         
	         Date messageDateTime = new Date();
	     	SimpleDateFormat messageDateFormat = new SimpleDateFormat("dd-MM-yyyy");
	     	
	     	  java.util.Date date = new java.util.Date();
    	      long t = date.getTime();	        	     
    	      java.sql.Time time = new java.sql.Time(t);
	     	
	     	//System.out.println(messageDateFormat.format(messageDateTime));
	         
	         sql = "INSERT "
	         		+ "INTO "
	         		+ "shop_message_details "
	         		+ "("
	         		+ "shop_id,"
	         		+ "shop_code,"
	         		+ "message_date,"
	         		+ "message_time,"
	         		+ "mobile_number,"
	         		+ "sender_id,"
	         		+ "message_status,"
	         		+ "message_batch_id,"
	         		+ "message_cost,"
	         		+ "message_content,"
	         		+ "message_type,"
	         		+ "service_provider) "
	         		+ "VALUES (?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?,?,?,?,?)";
	         
	         
	         
	         preparedStatement = connection.prepareStatement(sql);
	       
	         preparedStatement.setString(1, shopId);
	         preparedStatement.setString(2, shopCode);
	         preparedStatement.setString(3, messageDateFormat.format(messageDateTime));
	         preparedStatement.setTime(4, time);
	         preparedStatement.setString(5, mobileNumber);
	         preparedStatement.setString(6, senderId);
	         preparedStatement.setString(7, messageReplyPojo.getStatus());
	         preparedStatement.setString(8, response.getUnique_id());
	         preparedStatement.setString(9, messageReplyPojo.getMsgcount());
	         preparedStatement.setString(10, message);
	         preparedStatement.setString(11, messageReason);
	         preparedStatement.setString(12, service_provider);
	         
	        
	         int i =  preparedStatement.executeUpdate();
	         
	         if(i > 0)
	         {
	        	 connection.commit();
	        	 sendResponseDetails.put("DBADDED", "TRUE");
	        	 
	        	 if(messageReason.contains("BIILING"))
	        	 {
	        	 sendResponseDetails.put("MESSAGE", "Biiling message sent to "+mobileNumber+". "
	        			 			+ " Message Cost : "+messageReplyPojo.getMsgcount()
	        			 			+ ". Message Balance : "+messageReplyPojo.getRemainingcredits());
	        	 }else if(messageReason.contains("NEW-CUSTOMER"))
	        	 {
	        		 sendResponseDetails.put("MESSAGE", "Welcome message sent to "+mobileNumber+". "
     			 			+ " Message Cost : "+messageReplyPojo.getMsgcount()
     			 			+ ". Message Balance : "+messageReplyPojo.getRemainingcredits());
	        		 
	        	 }
	        	 else if(messageReason.contains("OUTSTANDING"))
	        	 {
	        		 sendResponseDetails.put("MESSAGE", "Outsanding message sent to "+mobileNumber+". "
     			 			+ " Message Cost : "+messageReplyPojo.getMsgcount()
     			 			+ ". Message Balance : "+messageReplyPojo.getRemainingcredits());
	        		 
	        	 }
	        	 else 
	        	 {
	        		 sendResponseDetails.put("MESSAGE", "Message sent to "+mobileNumber+". "
	     			 			+ " Message Cost : "+messageReplyPojo.getMsgcount()
	     			 			+ ". Message Balance : "+messageReplyPojo.getRemainingcredits());
	        		 
	        	 }
	         }

	     }catch(SQLException sqe)
	     {
	    	 sqe.printStackTrace();
	    	 sendResponseDetails.put("DBADDED", "FALSE");
	    	 sendResponseDetails.put("MESSAGE",sqe.getMessage());
	     }
	     catch (IOException e) {
	        
	    	 sendResponseDetails.put("DBADDED", "FALSE");
	    	 sendResponseDetails.put("MESSAGE",e.getMessage());
	     }finally {
	    	 if(resultSet !=null)
				{
					try {resultSet.close(); resultSet = null;} catch (SQLException e) {}
				}
				if(preparedStatement != null)
				{
					try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
				}
				if(connection != null)
				{
					
					try {connection.close();
					 connection = null;
					}catch (SQLException e) {}
				}
				
			}
		
        }
		return sendResponseDetails;
	}

	public HttpSession getSession() {
		return session;
	}

	public void setSession(HttpSession session) {
		this.session = session;
	}
	
	public static Map<String,String> addInfraInfotechSMSResponseToDB(SessionMap<String, Object> session, String resp, String shopId, String shopCode, String mobileNumber, String service_provider, String senderId, String message, String messageReason)
	{
		
		Map<String, String > sendResponseDetails = new HashMap<String,String>();
	  if(session==null || session.get("login")==null){ 
        
        	
        }  
        else{
        	
         	
	     	  java.util.Date date = new java.util.Date();
  	      long t = date.getTime();	        	     
  	      java.sql.Time time = new java.sql.Time(t);
  	      
        	ObjectMapper mapper = new ObjectMapper();
		   
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) session.get("db");
		
	     try {
	    	 
	    	 connection = GetDBConnection.getDBConnection(db);
	    	  	 
	    	 Root messageReplyPojo = mapper.readValue(resp,Root.class);
	    	 
	    	
	         
	         Date messageDateTime = new Date();
	     	SimpleDateFormat messageDateFormat = new SimpleDateFormat("dd-MM-yyyy");
	     	
	     	//System.out.println(messageDateFormat.format(messageDateTime));
	         
	     	  sql = "INSERT "
		         		+ "INTO "
		         		+ "shop_message_details "
		         		+ "("
		         		+ "shop_id,"
		         		+ "shop_code,"
		         		+ "message_date,"
		         		+ "message_time,"
		         		+ "mobile_number,"
		         		+ "sender_id,"
		         		+ "message_status,"
		         		+ "message_batch_id,"
		         		+ "message_cost,"
		         		+ "message_content,"
		         		+ "message_type,"
		         		+ "service_provider) "
		         		+ "VALUES (?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?,?,?,?,?)";
		         
		         
		         
		         preparedStatement = connection.prepareStatement(sql);
		       
		         preparedStatement.setString(1, shopId);
		         preparedStatement.setString(2, shopCode);
		         preparedStatement.setString(3, messageDateFormat.format(messageDateTime));
		         preparedStatement.setTime(4, time);
		         preparedStatement.setString(5, mobileNumber);
		         preparedStatement.setString(6, senderId);
		         preparedStatement.setString(7, ""+messageReplyPojo.getJob_status());
		         preparedStatement.setString(8, ""+messageReplyPojo.getJob_id());
		         preparedStatement.setString(9, ""+messageReplyPojo.getSms_count());
		         preparedStatement.setString(10, message);
		         preparedStatement.setString(11, messageReason);
		         preparedStatement.setString(12, service_provider);
	         
	        int i =  preparedStatement.executeUpdate();
	         
	         if(i > 0)
	         {
	        	
	        	 sendResponseDetails.put("DBADDED", "TRUE");
	        	 
	        	 if(messageReason.contains("COLLECTION"))
	        	 {
	        	 sendResponseDetails.put("MESSAGE", "Colletion message sent to "+mobileNumber+". "
	        			 			+ " Message Cost : "+messageReplyPojo.getSms_count()
	        			 			+ ". Message Balance : "+messageReplyPojo.getCredit_balance());
	        	 }
	        	 else
	        	 {
	        		 sendResponseDetails.put("MESSAGE", "Message sent to "+mobileNumber+". "
	     			 			+ " Message Cost : "+messageReplyPojo.getSms_count()
	     			 			+ ". Message Balance : "+messageReplyPojo.getCredit_balance());
	        		 
	        	 }
	         }

	     }catch(SQLException sqe)
	     {
	    	//sqe.printStackTrace();
	    	 sendResponseDetails.put("DBADDED", "FALSE");
	    	 sendResponseDetails.put("MESSAGE",sqe.getMessage());
	     } catch (JsonParseException e) {
	    	 sendResponseDetails.put("DBADDED", "FALSE");
	    	 sendResponseDetails.put("MESSAGE", e.getMessage());
		} catch (JsonMappingException e) {
			sendResponseDetails.put("DBADDED", "FALSE");
			sendResponseDetails.put("MESSAGE", e.getMessage());
		} catch (IOException e) {
			sendResponseDetails.put("DBADDED", "FALSE");
			sendResponseDetails.put("MESSAGE", e.getMessage());
		}
	   finally {
	    	 if(resultSet !=null)
				{
					try {resultSet.close(); resultSet = null;} catch (SQLException e) {}
				}
				if(preparedStatement != null)
				{
					try {preparedStatement.close(); preparedStatement = null;} catch (SQLException e) {}
				}
				if(connection != null)
				{
					
					try {connection.close();
					 connection = null;
					}catch (SQLException e) {}
				}
				
			}
		
        }
	 // System.out.println(sendResponseDetails);
		return sendResponseDetails;
	}


}
