import mysql.connector
import os
import sys
import time
import datetime
import xlsxwriter    

import vimsmail

def load_dotenv_simple(path):
    env = {}
    with open(path, "r", encoding="utf-8") as f:
        for raw in f:
            line = raw.strip()
            if not line or line.startswith("#"):
                continue
            if "=" not in line:
                continue
            key, val = line.split("=", 1)
            key = key.strip()
            val = val.strip()
            # remove optional surrounding quotes
            if (val.startswith('"') and val.endswith('"')) or (val.startswith("'") and val.endswith("'")):
                val = val[1:-1]
            env[key] = val
#            print("env=",key,val)
    return env

def strip_quotes(v):
    if not v:
        return v
    v = v.strip()
    if (v.startswith('"') and v.endswith('"')) or (v.startswith("'") and v.endswith("'")):
        return v[1:-1]
    return v

def container(data,book):
    try:
        sheet1 = book.add_worksheet("Container")
        for col in range(20):
            sheet1.set_column(col, col, 20)
            sheet1.write(0,0,"Field 1")
            sheet1.write(0,1,"Field 2")
            sheet1.write(0,2,"Field 3")	
            sheet1.write(0,3,"Field 4")	
            sheet1.write(0,4,"Field 5")	
            sheet1.write(0,5,"Field 6")
# for each line split out fields.
            i = 0
            r = 1
            for row in data:
                row = row.replace('\n','')
                cols = row.split("|")            
                c = 0
                i = 0
                for col in cols:
                    sheet1.write(r,c,col)
                    c=c+1
                r = r+1
    except Exception as e:
        print("FAIL-",str(e))
    finally:
        pass
    return

if __name__ == '__main__':
# check for filter
    if (len(sys.argv)>1):
        filter = sys.argv[1]
    else:
        filter = ""

    reportpath = "f:/kukfiles/container/"
    sender = "vimsreport.gb@logisteed.com"

    env = load_dotenv_simple('f:/webroot/kukupload/.env')
    db_host = env.get("DB_HOST") or env.get("db_host")
    db_name = env.get("DB_DATABASE") or env.get("db_database")
    db_user = env.get("DB_USERNAME") or env.get("db_username")
    db_pass = env.get("DB_PASSWORD") or env.get("db_password")
    db_port = env.get("DB_PORT")
    db_host = strip_quotes(db_host)
    db_name = strip_quotes(db_name)
    db_user = strip_quotes(db_user)
    db_pass = strip_quotes(db_pass)

    defaults = {}
    defaults['dbhost']=db_host
    defaults['dbuser']=db_user
    defaults['dbpwd']=db_pass
    defaults['dbase']=db_name

    cnx = mysql.connector.connect(
        host=db_host,
        database=db_name,
        user=db_user,
        password=db_pass
#        port=db_port
    )


    tablename = "VIMS_container"
    book = xlsxwriter.Workbook(reportpath+"/"+tablename+".xlsx")
    book.set_calc_mode('automatic')
    fmt_datetime = book.add_format({'num_format': 'yyyy/mm/dd hh:mm'})
    fmt_date = book.add_format({'num_format': 'YYYY-MM-DD'})
    fmt_time = book.add_format({'num_format': 'hh:mm'})
    fmt_int = book.add_format({"num_format": "0"})

    file_path_map = "f:/kukfiles/adhoc/container"
    containerInput = open(file_path_map,"r")
    container(containerInput,book)
    containerInput.close()

    book.close()
    cnx.close()

    recipients = vimsmail.getRecipients(defaults,"CONTAINER")
#    recipients = "geoff.thorpe@vanteceurope.com"
    vimsmail.createMail(reportpath, sender, recipients,"")