import os
import sys
import time
import datetime
import mysql.connector
import hashlib
from xlrd import open_workbook

import tkinter as tk
from tkinter import filedialog

import default_setting

# Create a Tkinter root window (it won't be shown)
root = tk.Tk()
root.withdraw()  # Hide the root window

# Get Current Path
dir_path = os.path.dirname(os.path.realpath(__file__))

def formatDate(date):
    if ((date == None) or (len(date) < 14)):
        date = "0001-01-01 00:00:01"
    else:
        dd = date[0:2]
        mm = date[3:5]
        yy = date[6:8]
        tm = date[9:14]
        date = '20'+yy+"-"+mm+"-"+dd+" "+tm+":00"
    return date

# Function to generate a SHA-256 hash limited to 6 characters
def generate_short_hash(input_string):
# Create a new sha256 hash object
    sha256_hash = hashlib.sha256()
    
# Update the hash object with the bytes of the input string
    sha256_hash.update(input_string.encode())
    
# Get the hexadecimal representation of the full digest
    full_hash = sha256_hash.hexdigest()
    
# Return the first 6 characters of the hash
    return full_hash[:6].upper()
        
def location(connection,data):
    global csv_path
    global csv_header
    
    hashCode = generate_short_hash(data[0].strip())
    try:
        cursor = connection.cursor(dictionary=True)
        maxWeight = 0
        multiPart = 1
        invStatusId = 1
        if (len(data)>3):
            maxWeight = data[3]
        if (len(data)>4):
            if (data[4].lower() == "no"):
                multiPart = 0
            else:
                multipart = 1
        if (len(data)>5):
            dataQuery = "SELECT id FROM inventory_status WHERE inventory_status_code='"+data[5].strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                invStatusId = row['id']
        try:
            locSubTypeId = None
            dataQuery = "SELECT id FROM location_sub_type WHERE location_sub_type_code='"+data[2].strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                locSubTypeId = row['id']
            else:
                dataQuery = "INSERT INTO location_sub_type (id,version,active,date_created,created_by,last_updated,last_updated_by,location_sub_type_code,location_sub_type_description)"
                dataQuery += " VALUES(DEFAULT,0,1,now(),'update',now(),'update','"+data[2].strip()+"','"+data[2].strip()+"')"
                cursor.execute(dataQuery)
                connection.commit()
                locSubTypeId = cursor.lastrowid
        except Exception as e:
            print ("Insert Error:",str(e),data[2])
            
        try:
            locTypeId = None
            dataQuery = "SELECT id FROM location_type WHERE location_type_code='"+data[1].strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                locTypeId = row['id']
            else:
                dataQuery = "INSERT INTO location_type (id,version,active,default_inventory_status_id,loose_parts,date_created,created_by,last_updated,last_updated_by,location_type_code,location_type_description)"
                dataQuery += " VALUES(DEFAULT,0,1,1,0,now(),'update',now(),'update','"+data[1].strip()+"','"+data[1].strip()+"')"
                cursor.execute(dataQuery)
                connection.commit()
                locTypeId = cursor.lastrowid
        except Exception as e:
            print ("Insert Error:",str(e),dataQuery,data[1])
            
        try:
            dataQuery = "SELECT id FROM location WHERE location_code='"+data[0].strip()+"' LIMIT 1"
            cursor.execute(dataQuery) 
            row = cursor.fetchone()
            if (row):
                locId = row['id']
                dataQuery = "UPDATE location SET last_updated=now(),last_updated_by='update',location_sub_type_id="+str(locSubTypeId)+", location_type_id="+str(locTypeId)
                if (len(data)>3):
                    dataQuery +=", max_weight="+str(maxWeight)
                if (len(data)>4):
                    dataQuery +=", multi_part_location="+str(multiPart)
                if (len(data)>5):
                    dataQuery +=", inventory_status_id="+str(invStatusId)
                dataQuery +=" WHERE id="+str(locId)
                dataQuery +=" LIMIT 1"
                cursor.execute(dataQuery)
                connection.commit()
            else:
                dataQuery = "INSERT INTO location (id,version,active,location_closed,multi_vendor_location,filling_status_id,date_created,created_by,last_updated,last_updated_by,location_code,location_code_description,location_hash,location_sub_type_id,location_type_id,max_weight,multi_part_location,inventory_status_id)"
                dataQuery += " VALUES(DEFAULT,0,1,0,1,1.now(),'update',now(),'update','"+data[0].strip()+"','"+data[0].strip()+"','"+hashCode+"',"+str(locSubTypeId)+","+str(locTypeId)+","+str(maxWeight)+","+str(multiPart)+","+str(invStatusId)+")"
                cursor.execute(dataQuery)
                connection.commit()
                locId = cursor.lastrowid
        except Exception as e:
            print ('Update Error',str(e),dataQuery)
    except Exception as e:
        print("FAIL-",str(e),dataQuery)
    finally:
       cursor.close()
#    print("TEST4")
    return
    
# timestamp value   
def getts():
    ts = time.time()
    ts = datetime.datetime.fromtimestamp(ts).strftime('_%Y%m%d_%H%M%S')
    return ts

def connection():
    dbhost = ""
    if (len(sys.argv) > 1):
        dbhost = sys.argv[1]
    if (len(sys.argv) > 2):
        dbuser = sys.argv[2]
    if (len(sys.argv) > 3):
        dbpwd = sys.argv[3]
    if (len(sys.argv) > 4):
        dbase = sys.argv[4]
# mySql Connector
    if (len(sys.argv) > 1):
        connection = mysql.connector.connect(user=dbuser, password=dbpwd,host=dbhost,database=dbase)
    else:
# Default Connection / System Settings
        defaults = default_setting.defaultSettings()
        connection = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
        
    return connection

def main():
    try:
        connect = connection()
        
        if (len(sys.argv) > 5):
            file_path = sys.argv[5]
        else:
            file_path = filedialog.askopenfilename(title="Select a file")
# Check if a file was selected
            if file_path:
                print(f"Selected file: {file_path}")
            else:
                print("No file selected.")
                file_path = "F:/kukfiles/adhoc/location upload.xlsx"
        
        file_path_xls = file_path
        if os.path.isfile(file_path_xls):
            wb = open_workbook(file_path_xls)
    
    # Loop through the Sheets
            for s in wb.sheets():
                print('Sheet:', s.name)
                data = []  # Initialize data for each sheet
                
                # Loop through the Sheet Rows
                for row in range(s.nrows):
                    print("Row:", row)
                    row_data = []
                    
                    for col in range(s.ncols):  # Loop through columns
                        try:
                            value = s.cell(row, col).value
                            if isinstance(value, (int, float)):  # Check if value is a number
                                row_data.append(str(int(value)))
                            else:
                                row_data.append(str(value))  # Convert to string directly
                        except ValueError:
                            row_data.append("")  # Handle conversion errors
                        except Exception as e:
                            print(f"Error processing cell ({row}, {col}): {e}")
                    
                    try:
                        location(connect,row_data)
                    except Exception as e:
                        print("data error:",str(row_data))
                        pass
        else:
            print ("FAIL-upload File "+file_path_xls+" not found")
            return
        print ("OK  -Upload Complete "+file_path)
    except Exception as e:
        print ("FAIL-1-",str(e))
        return
            
if __name__ == '__main__':
    main()