# DB TimeBlock Management
# TimeBlock-Generate.py
#
# @author Jack Rogerson (jrogerson@clarku.edu) (jrogerson.com)
# @version Spring 2022
# PHYS 243 Spring 2022 - Professor Charles Agosta
#
# Previous Versions:
#
# About:
#   This script adds the 1440 time blocks that belong to each day (00:00:00 ---> 23:59:00). The blocks are added to the
#   system seven days in advance. This script should be run once a day, ideally close to and post-midnight.
#
#   Basic email functionality will alert regarding invalid row counts.
#   However, if a DB error occurs during execution, the program may exit before an email notification can send.
#   'Operation successful' emails will be sent (assuming email is enabled in .env).
#
#   Invoke from cron, and save output as desired.

import os
from dotenv import load_dotenv
import mysql.connector
import calendar
import datetime
from datetime import time
from shlex import quote

def sendEmailAlert(programError, additionalInformation):
    """
    Sends an email with the specified parameters in the .env file using OS level systems.

    :param programError: The primary error resulting in email communication.
    :param additionalInformation: Additional information that will be sent in the email message within the email body.
    :return: None. At return, the email notification will be sent at the OS level.
    """

    if (os.environ.get('EMAILENABLED')):                                                                            #If email functionality is enabled in the .env file.
        subject = quote(os.environ.get('EMAILSUBJECT'))                                                             #Get the subject from the .env file.
        body = str(os.environ.get('EMAILBODY') + "\n \n \n Program Error Message: \n \n \n" + str(programError) + " \n \n \n Additional Information: \n \n \n" + str(additionalInformation))        #Construct email body.
        sender = os.environ.get('EMAILSENDER')                                                                      #Get the sender from the .env file.
        recipientsCSV = quote(os.environ.get('EMAILRECIPIENTSCSV'))                                                 #Get all recipients, by CSV, from the .env file.


        #As a result of excessively long email messages, write data to the filesystem first.
        emailTemporaryFilename = "email-TimeBlock-Generate" + ".temp"                   #We need unique email files.
        with open(emailTemporaryFilename,"w") as tempFile:                              #Open the temporary email file for writing. Write at the start of the file and erase all previous content.
            tempFile.write(body)                                                        #Write email content.
        tempFile.close()                                                                #Close OS file with email content.
        os.system("cat " + emailTemporaryFilename + " | mail -s " + subject + " -aFrom:" + sender + " " + recipientsCSV)            #Send the email using mail at the OS level.

        print("Email Notification Sent.")

    return


if __name__ == "__main__":

    load_dotenv()                                                   #Load the DB info/credentials from the .env file.

    microgridDBInstance = mysql.connector.connect(                      #Login to the database.
      host = os.environ.get('DBHOST'),                                        #From .env File.
      user = os.environ.get('DBUSER'),                                        #From .env File.
      password = os.environ.get('DBPASSWORD'),                                #From .env File.
      database = os.environ.get('DBDATABASENAME')                             #From .env File.
    )

    microgridDBCursor = microgridDBInstance.cursor()                    #Establish DB cursor to execute commands.

    dateTimeNow = datetime.datetime.now()                                           #Identify the current time.
    dateTimePlus7 = dateTimeNow + datetime.timedelta(days = 7)                      #Calculate time delta points for +7 day.
    dateTimeStartOfDayPlus7 = dateTimePlus7.combine(dateTimePlus7, time.min)        #Calculate the earliest moment of day.
    dateTimeStartOfDayPlus7GMTAdj = dateTimeStartOfDayPlus7 + datetime.timedelta(hours = 4)     #Adjust for GMT -4 Offset.

    runningRowCount = 0                                             #Track number of rows affected for all operations.

    #Calculate and add necessary Time Blocks:
    for timeOffset in range(1440):                     #1440 minutes per day. Range is endpoint exclusive, but we want 1439.
        timeBlockMinute = dateTimeStartOfDayPlus7GMTAdj + datetime.timedelta(minutes = timeOffset)  #Calculate time delta points for today.
        utcTimeTimeBlockMinute = calendar.timegm(timeBlockMinute.utctimetuple())                    #Get UTC of calculated timeBlockMinute.

        print("Attempting insert for: " + str(timeBlockMinute) + " GMT ---> " + str(utcTimeTimeBlockMinute))

        #Run the DB insert. Do not commit until all inserts are successful.
        microgridDBCursor.execute(
            "INSERT INTO Time_Block_ID (Time_Block_ID, time_stamp) VALUES (%s, %s)", [utcTimeTimeBlockMinute, utcTimeTimeBlockMinute]
        )

        runningRowCount = runningRowCount + microgridDBCursor.rowcount          #Increment rows affected by this execution.

    #Determine if operations are successful:
    if (runningRowCount == 1440):                    #Quick sanity check to make sure we have all expected rows.
        microgridDBInstance.commit()                                      #At this point, safe to commit new Time_Block_IDs.
        microgridDBInstance.close()                                                     #Close and exit DB connection.
        print("Operation was successful. Added " + str(runningRowCount) + " rows!")

        if (os.environ.get('EMAILENABLED')):                                    #If email enabled, send email to confirm script finished successfully.
            sender = os.environ.get('EMAILSENDER')                              #Get the sender from the .env file.
            subject = quote("TimeBlock-Generate.py Successful!")                #Set a custom subject.
            recipientsCSV = quote(os.environ.get('EMAILRECIPIENTSCSV'))         #Get all recipients, by CSV, from the .env file.
            os.system("echo " + "TimeBlock-Generate.py exit 0" + " | mail -s " + subject + " -aFrom:" + sender + " " + recipientsCSV)       #Send the email using mail at the OS level.

        exit(0)                                                                     #Clean exit. All set here.

    else:
        microgridDBInstance.rollback()                                          #Something is not right, rollback and fail.
        microgridDBInstance.close()                                                     #Close and exit DB connection.
        print("All expected DBCursor rows are not present! DB has not been modified!")
        reportInfo = "This error has occurred during standard daily TimeBlock input!"
        sendEmailAlert("All expected DBCursor rows are not present! DB has not been modified!", reportInfo)     #Send email report with failed notification.
        raise Exception("All expected DBCursor rows are not present! DB has not been modified!")    #Exit with error code.