import json
import sys
import time
import csv
import datetime as dt
import pandas as pd
import math
import os

def loadJSON(inputfilename):
    try:
        dataInputFile=open(inputfilename,mode='r',encoding='utf-8')
        data=json.loads(dataInputFile.read())
        print('Input file {} loaded'.format(inputfilename))
        dataInputFile.close()
        return data
    except: 
        print('No existing input file, one will be created.')
        return 0

def saveJSON(outputfilename, data):
    dataOutputFile=open(outputfilename,mode='w',encoding='utf-8')
    dataOutputFile.write(json.dumps(data, indent=4)) #indent provides pretty JSON output
    dataOutputFile.close()
    print('Output file {} saved'.format(outputfilename))

def readCSV(inputFile,inputCol):
    rowTotal=0
    List=[]
    try:
        with open(inputFile) as csv_file:
            csv_reader = csv.reader(csv_file, delimiter=',')
            next(csv_reader, None)  # skip the headers
            for row in csv_reader:
                List.append(row[inputCol])
                rowTotal+=1
        print('{} input rows processed.'.format(rowTotal))
        return List
    except Exception as e:
        sys.exit('{} loading failed. {}'.format(inputFile, e))
        return None
    
def getTimeStamp():
    timenow=time.strftime('%Y%m%d-%H%M%S')
    return timenow

def getToday():
    today = time.strftime('%Y-%m-%d')
    return today

def getReportingMonth():
    today = dt.date.today()
    first = today.replace(day=1)
    last_month = (first - dt.timedelta(days=1)).replace(day=1)
    return last_month

def getReportingMonthObject(offset=-1):
    if offset == -1:
        last_month=getReportingMonth()
    else:
        last_month = dt.date.today().replace(day=1)
    reportingmonthobject = {
        'YYYY' : last_month.strftime("%Y"),
        'MM' : last_month.strftime("%m"),
        'YYYYMM' : last_month.strftime("%Y") + last_month.strftime("%m"),
        'MON' : last_month.strftime("%b").upper(),
        'Month' : last_month.strftime("%B")
        }
    return reportingmonthobject

def getFirstOfTheMonth(reportingmonthobject):
    first = dt.date(int(reportingmonthobject['YYYY']),int(reportingmonthobject['MM']),1)
    return first

def readsql(file, sep=';'):
    fd = open(file, 'r')
    sqlFile = fd.read()
    fd.close()
    
    # all SQL commands (split on ';', add only if not blank)
    sqlCommands = [x.strip() for x in sqlFile.split(sep) if len(x.strip()) > 0]

    return sqlCommands

def changedtype(df,col):
    df[col]=df[col].astype(str).str.strip()
    return df

def dfexceloutput(df,filename,interval=1000000):
#produce new publishing run Excel file >1M rows, interval sets the sheet row count
# create a excel writer object, loops through by intervals to each new sheet - this takes a while...
    
    # drops ending extension only if it is formatted as .xxxx (. + 4 chars)
    f = os.path.splitext(filename)
    if len(f[-1]) > 4:
        filename = ''.join(f)
    else:
        filename = ''.join(f[:-1])
    
    filename='{}-{}.xlsx'.format(filename, getTimeStamp())
    print('Processing {}...'.format(filename))

    if len(df)>0:
        print('Compiling file output {}'.format("{} ...".format(filename)))
        with pd.ExcelWriter(filename) as writer:
            # loop through each million rows
            startrow = 0
            sheet = 1
            sheetcount=math.ceil(len(df)/interval) #rounds up sheetcount
            #df[i:i+interval].to_excel(writer, sheet_name="DataSheet{}".format(i+1), index=False)
            
            while sheet <= sheetcount:
                endrow = startrow + interval
                if endrow > len(df):
                    endrow = len(df)
                
                # overlap row counts because iloc slice is start inclusive and end exclusive
                print('Processing data rows {} to {} for sheet {} out of {} ...'.format(startrow, endrow, sheet, sheetcount))
                df.iloc[startrow:endrow].to_excel(writer, sheet_name="DataSheet{}".format(sheet), index=False)
                print('Rows {} to {} were written to WS DataSheet{}; total WS Count: {}'.format(startrow, endrow,sheet,sheetcount))
                startrow += interval
                sheet += 1
        print('Excel Output process for {} completed.'.format(filename))
    else:
        print('Cannot output Excel as {} since there is no data.'.format(filename))
    
def dfcsvoutput(df, filename, encoding='utf-8'):
    
    # drops ending extension only if it is formatted as .xxxx (. + 4 chars)
    f = os.path.splitext(filename)
    if len(f[-1]) > 4:
        filename = ''.join(f)
    else:
        filename = ''.join(f[:-1])
        
    if len(df) > 0:
        filename='{}-{}.csv'.format(filename, getTimeStamp())
        print(f'Generating {filename} with {len(df)} rows.')
        df.to_csv(filename
                         , encoding='utf-8'
                         , index=False)    
        print('CSV Output process for {} completed.'.format(filename))
    else:
        print('Cannot output CSV as {} since there is no data.'.format(filename))
        
def currency(value,precision=2):
    value = '$ {:,.{}f}'.format(value,precision)
    return value

def ordinal(n: int):
    if 11 <= (n % 100) <= 13:
        suffix = 'th'
    else:
        suffix = ['th', 'st', 'nd', 'rd', 'th'][min(n % 10, 4)]
    return str(n) + suffix

class Textobj:
    def __init__(self, text=''):
        self.text = text
        
    def __str__(self):
        return self.text
        
    def add(self, addtext):
        addtext = str(addtext)
        if len(self.text) >1:
            addtext = '\n\n'  + addtext
        self.text += addtext
    
    def print(self):
        print(self.text)
        
    def save(self, filename):
        # add txt extension only if extension not specified
        if len(os.path.splitext(filename)[1]) == 0:
            filename += '.txt'
            
        with open(filename, "w", encoding="utf-8") as text_file:
            text_file.write(self.text)
        print(f'\nOutput completed to: \n {filename}')

class Htmlobj:
    def __init__(self, text=''):
        self.text = text
        
    def __str__(self):
        return self.text
        
    def add(self, addtext, formatting=''):
        addtext = str(addtext)
        if len(self.text) >1:
            
            # skips adding br if line is a table
            if addtext.startswith('<table>'):
                pass
            else: 
                addtext = '<br><br>'  + addtext
                addtext = addtext.replace('\n','<br>')
                if formatting == 'b':
                    addtext = '<b>' + addtext + '</b>'
                if formatting == 'i':
                    addtext = '<i>' + addtext + '</i>'
        self.text += addtext
    
    def print(self):
        print(self.text)
        
    def save(self, filename):
        filename = os.path.splitext(filename)[0]+'.html'
        with open(filename, "w", encoding="utf-8") as text_file:
            text_file.write(self.text)
        print(f'\nOutput completed to: \n {filename}')

def makedir(path, prompt=1):
    if not os.path.exists(path):
        os.makedirs(path)
        print(f'Directory {path} was created.')
    else:
        if prompt == 1:
            print(f'{path} already exists, nothing happened.')
        
def errorPrompt(text='', stars=15):
    text = '*'*stars + text + '*'*stars
    print(text)