"""
This module inserts data into DB for new combo spams. Old combo stuff is all handled by ShivaOldSpam module
"""
import server
import os, sys, logging
import MySQLdb as mdb
import ShivaConfig, ShivaTackleQueue
import time, datetime

#mailFields = {'to':'', 'from':'', 'subject':'', 'date':'', 'text':'', 'html':'', 'inlineFileName':[], 'inlineFile':[], 'inlineFileMd5':[], 'attachmentFileName':[], 'attachmentFile':[], 'attachmentFileMd5':[], 'links':[]}


dataBase = "spamPot09252012"


def pushIntoDB(mailFields, key, msgMailRequest, exeSql):
  logging.critical("[!] --------- Inside ShivaNewSpam module -----------------")
  
  #exe_sql = dbConnect.dbConnect()				# Call to dbConnect() module
  #exeSql = ShivaConfig.dbConnect()				# Call to dbConnect() module
  
  #logging.critical("[!] --------- Back to dbInsert module -----------------")
  #mailFields['date'] =  datetime.date.today()
  #mailFields['firstSeen'] =  datetime.datetime.now()
  #mailFields['lastSeen'] =  datetime.datetime.now()
  #mailFields['date'] =  datetime.datetime.now()

    
  # Step 1: Dump into DB &
  # Step 2: Increase totalCounter
  #insert_spam = "INSERT INTO spamPot.spam (`to`, `from`, `subject`, `date`, `textMessage`, `htmlMessage`, `totalCounter`) VALUES('"+str(mailFields['to'])+"', '"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['date'])+"', '"+str(mailFields['text'])+"', '"+str(mailFields['html'])+"', '1')"
  #insert_spam = "INSERT INTO spamPot.spam (`to`, `from`, `subject`, `firstSeen`, `lastSeen`, `textMessage`, `htmlMessage`, `totalCounter`) VALUES('"+str(mailFields['to'])+"', '"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['firstSeen'])+"', '"+str(mailFields['lastSeen'])+"', '"+str(mailFields['text'])+"', '"+str(mailFields['html'])+"', '1')"
  #logging.critical("from: %s", mailFields['from'])
  #logging.critical("subject: %s", mailFields['subject'])
  #logging.critical("from type: %s", type(mailFields['from']))
  #logging.critical("sub type: %s", type(mailFields['subject']))
  #logging.critical("text type: %s", type(mailFields['text']))
  #insert_spam = "INSERT INTO spamPot.spam (`to`, `from`, `subject`, `date`, `firstSeen`, `lastSeen`, `textMessage`, `htmlMessage`, `totalCounter`, `comboMD5`) VALUES('"+str(mailFields['to'])+"', '"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['firstSeen'])+"', '"+str(mailFields['lastSeen'])+"', '"+str(mailFields['text'])+"', '"+str(mailFields['html'])+"', '1', '"+str(mailFields['comboMD5'])+"')"
  #insert_spam = "INSERT INTO spamPot09252012.spam (`to`, `from`, `subject`, `textMessage`, `htmlMessage`, `totalCounter`, `id`) VALUES('"+str(mailFields['to'])+"', '"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['text'])+"', '"+str(mailFields['html'])+"', '1', '"+str(mailFields['spam_id'])+"')"
  
  insert_spam = "INSERT INTO spam (`to`, `from`, `subject`, `textMessage`, `htmlMessage`, `totalCounter`, `id`) VALUES('"+str(mailFields['to'])+"', '"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['text'])+"', '"+str(mailFields['html'])+"', '1', '"+str(mailFields['spam_id'])+"')"
  
  # lastSeen date comes from old combo 
  
  #logging.critical("insertspam: %s", insert_spam)
  #logging.critical("textMessage: %s", mailFields['text'])
  try:  
    exeSql.execute(insert_spam)
    #logging.critical("inserted_spam: ")
    #output = exe_sql.fetchall()
    #exe_sql.close()
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam - insert_spam) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  # Insert date
  #insert_sdate = "INSERT INTO spamPot09252012.sdate (`date`, `firstSeen`, `lastSeen`, `todaysCounter`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['firstSeen'])+"', '"+str(mailFields['lastSeen'])+"', '1')"
  insert_sdate = "INSERT INTO sdate (`date`, `firstSeen`, `lastSeen`, `todaysCounter`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['firstSeen'])+"', '"+str(mailFields['lastSeen'])+"', '1')"
  try:
    exeSql.execute(insert_sdate)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam: insert_sdate) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  # Insert into sdate_spam - table for many-to-many between spam and sdate tables
  #insert_sdate_spam = "INSERT INTO spamPot09252012.sdate_spam (`spam_id`, `date_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"')"
  insert_sdate_spam = "INSERT INTO sdate_spam (`spam_id`, `date_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"')"
  
  #logging.critical("insert_sdate_spam: %s", insert_sdate_spam)
  
  try:
    exeSql.execute(insert_sdate_spam)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam: insert_sdate_spam) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  # Insert source IP of spammer into table
  #insert_ip = "INSERT INTO spamPot.spam_ip (`from`, `subject`, `sourceIP`) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['sourceIP'])+"' )"
  #insert_ip = "INSERT INTO spamPot.spam_ip (`from`, `subject`, `date`, `sourceIP`, `comboMD5`) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['sourceIP'])+"', '"+str(mailFields['comboMD5'])+"' )"
  #insert_ip = "INSERT INTO spamPot09252012.ip (`date`, `sourceIP`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['sourceIP'])+"' )"
  insert_ip = "INSERT INTO ip (`date`, `sourceIP`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['sourceIP'])+"' )"
  try:
    exeSql.execute(insert_ip)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam: insert_ip) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  #insert_ip_spam = "INSERT INTO spamPot09252012.ip_spam (`spam_id`, `ip_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"' )"
  insert_ip_spam = "INSERT INTO ip_spam (`spam_id`, `ip_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"' )"
  try:
    exeSql.execute(insert_ip_spam)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam: insert_ip_spam) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
    
  # Insert Shiva sensor ID into table
  #insert_sensor = "INSERT INTO spamPot.spam_sensor (`from`, `subject`, `sensorID`) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['sensorID'])+"' )"
  #insert_sensor = "INSERT INTO spamPot.spam_sensor (`from`, `subject`, `date`, `sensorID`, `comboMD5`) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['sensorID'])+"', '"+str(mailFields['comboMD5'])+"' )"
  #insert_sensor = "INSERT INTO spamPot09252012.sensor (`date`, `sensorID`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['sensorID'])+"' )"
  insert_sensor = "INSERT INTO sensor (`date`, `sensorID`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['sensorID'])+"' )"
  try:
    exeSql.execute(insert_sensor)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam insert_sensor) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  #insert_sensor_spam = "INSERT INTO spamPot09252012.sensor_spam (`spam_id`, `sensor_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"' )"
  insert_sensor_spam = "INSERT INTO sensor_spam (`spam_id`, `sensor_id`) VALUES('"+str(mailFields['spam_id'])+"', '"+str(exeSql.lastrowid)+"' )"
  try:
    exeSql.execute(insert_sensor_spam)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam insert_sensor_spam) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  if len(mailFields['attachmentFile']) != 0:			# If attachment is present - insert into DB
    i = 0
    while i < len(mailFields['attachmentFile']):
      #insert_attachment = "INSERT INTO spamPot.spam_attachment (`from`, `subject`, `md5`, `attachment_file_name`, `attachment_file`, `attachment_file_type`) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['attachmentFileMd5'][i])+"', '"+str(mailFields['attachmentFileName'][i])+"', '"+str(mailFields['attachmentFile'][i])+"', '"+str(os.path.splitext(mailFields['attachmentFileName'][i])[1])+"')"
      #insert_attachment = "INSERT INTO spamPot.spam_attachment (`from`, `subject`, `date`, `md5`, `attachment_file_name`, `attachment_file`, `attachment_file_type`, `comboMD5`) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['attachmentFileMd5'][i])+"', '"+str(mailFields['attachmentFileName'][i])+"', '"+str(mailFields['attachmentFile'][i])+"', '"+str(os.path.splitext(mailFields['attachmentFileName'][i])[1])+"', '"+str(mailFields['comboMD5'])+"')"
      
      #insert_attachment = "INSERT INTO spamPot09252012.attachment (`date`, `md5`, `attachment_file_name`, `attachment_file`, `attachment_file_type`, `spam_id`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['attachmentFileMd5'][i])+"', '"+str(mailFields['attachmentFileName'][i])+"', '"+str(mailFields['attachmentFile'][i])+"', '"+str(os.path.splitext(mailFields['attachmentFileName'][i])[1])+"', '"+str(mailFields['spam_id'])+"')"
      insert_attachment = "INSERT INTO attachment (`date`, `md5`, `attachment_file_name`, `attachment_file`, `attachment_file_type`, `spam_id`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['attachmentFileMd5'][i])+"', '"+str(mailFields['attachmentFileName'][i])+"', '"+str(mailFields['attachmentFile'][i])+"', '"+str(os.path.splitext(mailFields['attachmentFileName'][i])[1])+"', '"+str(mailFields['spam_id'])+"')"
      i = i + 1
      try:  
	exeSql.execute(insert_attachment)
      except mdb.Error, e:
	logging.critical("[-] Error (ShivaNewSpam insert_attachment) - %d: %s" % (e.args[0], e.args[1]))
	ShivaConfig.errorHandling(key, msgMailRequest)
	return None
  
  if len(mailFields['inlineFile']) != 0:				# If inline file is present - insert into DB
    i = 0
    while i < len(mailFields['inlineFile']):
      #insert_inline = "INSERT INTO spamPot.spam_inline (`from`, `subject`, `md5`, `inline_file_name`, `inline_file` ) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['inlineFileMd5'][i])+"', '"+str(mailFields['inlineFileName'][i])+"', '"+str(mailFields['inlineFile'][i])+"')"
      #insert_inline = "INSERT INTO spamPot.spam_inline (`from`, `subject`, `date`, `md5`, `inline_file_name`, `inline_file` ) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['inlineFileMd5'][i])+"', '"+str(mailFields['inlineFileName'][i])+"', '"+str(mailFields['inlineFile'][i])+"')"
      #insert_inline = "INSERT INTO spamPot09252012.inline (`date`, `md5`, `inline_file_name`, `inline_file`, `spam_id` ) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['inlineFileMd5'][i])+"', '"+str(mailFields['inlineFileName'][i])+"', '"+str(mailFields['inlineFile'][i])+"', '"+str(mailFields['spam_id'])+"')"
      # 16th Nov, 2012
      insert_inline = "INSERT INTO inline (`date`, `md5`, `inline_file_name`, `inline_file`, `spam_id` ) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['inlineFileMd5'][i])+"', '"+str(mailFields['inlineFileName'][i])+"', '"+str(mailFields['inlineFile'][i])+"', '"+str(mailFields['spam_id'])+"')"
      i = i + 1
      try:  
	exeSql.execute(insert_inline)
      except mdb.Error, e:
	logging.critical("[-] Error (ShivaNewSpam - insert_inline) - %d: %s" % (e.args[0], e.args[1]))
	ShivaConfig.errorHandling(key, msgMailRequest)
	return None
  
  if len(mailFields['links']) != 0:					# If links are present - insert into DB
    logging.critical("Some links are there")
    i = 0
    while i < len(mailFields['links']):
      #insert_link = "INSERT INTO spamPot.spam_links (`from`, `subject`, `hyperLink` ) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['links'][i])+"')"
      #insert_link = "INSERT INTO spamPot.spam_links (`from`, `subject`, `date`, `hyperLink`, `comboMD5` ) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['links'][i])+"', '"+str(mailFields['comboMD5'])+"')"
      #insert_link = "INSERT INTO spamPot09252012.links (`date`, `hyperLink`, `spam_id` ) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['links'][i])+"', '"+str(mailFields['spam_id'])+"')"
      # 16th Nov, 2012
      insert_link = "INSERT INTO links (`date`, `hyperLink`, `spam_id` ) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['links'][i])+"', '"+str(mailFields['spam_id'])+"')"
      i = i + 1
      try:  
	exeSql.execute(insert_link)
      except mdb.Error, e:
	logging.critical("[-] Error (ShivaNewSpam - insert_link) - %d: %s" % (e.args[0], e.args[1]))
	ShivaConfig.errorHandling(key, msgMailRequest)
	return None
  
  # Step 3: check relayCounter for the day
  #checkCounter = "SELECT sum(spamPot09252012.relay.relayCounter) FROM spamPot09252012.relay"			# relayCounter has been 'Indexed' in DB
  # 10192012 - adding field sensoID in relay table
  #checkCounter = "SELECT sum(spamPot09252012.relay.relayCounter) FROM spamPot09252012.relay WHERE spamPot09252012.relay.sensorID = '"+ str(mailFields['sensorID'])+"'"
  # 16th Nov, 2012
  checkCounter = "SELECT sum(relay.relayCounter) FROM relay WHERE relay.sensorID = '"+ str(mailFields['sensorID'])+"'"
  try:
    exeSql.execute(checkCounter)
  except mdb.Error, e:
    logging.critical("[-] Error (ShivaNewSpam - checkCounter) - %d: %s" % (e.args[0], e.args[1]))
    ShivaConfig.errorHandling(key, msgMailRequest)
    return None
  
  relayCounter = exeSql.fetchone()
  #logging.critical("relay Counter")
  #logging.critical(relayCounter[0])
  #logging.critical("spamConfig.relayCounter")
  #logging.critical(spamConfig.relayCounter)

  # Check if limit has been reached for the day
  # If not, relay the spam and initialize relayCounter and totalRelayed for a particular new combo spam
  if relayCounter[0] < ShivaConfig.relayCounter:
    #mailFields['firstRelayed'] =  datetime.datetime.now()
    #mailFields['lastRelayed'] =  datetime.datetime.now()
    logging.critical("ShivaNewSpam: Relaying spam - Relay count for the day has not reached its limit yet")
    #exeSql = spamConfig.dbConnect()
    #insertRelay = "INSERT INTO spamPot.spam_relay (`from`, `subject`, `firstRelayed`, `lastRelayed`, `relayCounter`, `totalRelayed`) VALUES('"+str(mailFields['from'].replace("\\", "\\\\"))+"', '"+str(mailFields['subject'].replace("\\", "\\\\"))+"', '"+str(mailFields['firstRelayed'])+"', '"+str(mailFields['lastRelayed'])+"', '1', '1')"
    #insertRelay = "INSERT INTO spamPot.spam_relay (`from`, `subject`, `date`, `firstRelayed`, `lastRelayed`, `relayCounter`, `totalRelayed`, `comboMD5`) VALUES('"+str(mailFields['from'])+"', '"+str(mailFields['subject'])+"', '"+str(mailFields['date'])+"', '"+str(mailFields['firstRelayed'])+"', '"+str(mailFields['lastRelayed'])+"', '1', '1', '"+str(mailFields['comboMD5'])+"')"
    #insertRelay = "INSERT INTO spamPot09252012.relay (`date`, `firstRelayed`, `lastRelayed`, `relayCounter`, `totalRelayed`, `spam_id`, `sensorID`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['firstRelayed'])+"', '"+str(mailFields['lastRelayed'])+"', '1', '1', '"+str(mailFields['spam_id'])+"', '"+str(mailFields['sensorID'])+"')"
    
    # 16th Nov, 2012
    insertRelay = "INSERT INTO relay (`date`, `firstRelayed`, `lastRelayed`, `relayCounter`, `totalRelayed`, `spam_id`, `sensorID`) VALUES('"+str(mailFields['date'])+"', '"+str(mailFields['firstRelayed'])+"', '"+str(mailFields['lastRelayed'])+"', '1', '1', '"+str(mailFields['spam_id'])+"', '"+str(mailFields['sensorID'])+"')"
    #logging.critical(insertRelay)
    try:
      exeSql.execute(insertRelay)
    except mdb.Error, e:
      logging.critical("[-] Error (ShivaNewSpam - insertRelay) - %d: %s" % (e.args[0], e.args[1]))
      ShivaConfig.errorHandling(key, msgMailRequest)
      return None
      
    ShivaTackleQueue.process_message(msgMailRequest)  
    #ShivaConfig.sendRelayNotificationMail()
  
  # If limit already reached, don't relay, just remove the message from queue
  else:
    logging.critical("ShivaNewSpam: Relay count for the day has reached - not relaying")
    #removeMessage = server.QueueReceiver(queuePath)
    #removeMessage.remove_message(msgMailRequest)
    
    