#################################################################################
#
# P4U Script for converting Baskets from Digikey / Mouser to P4U format
# Created by Jonas Lingua & Noah Piqué
# Date: 02.10.2025
# Version 1.6
#
################################################################################


from xlrd import open_workbook
from openpyxl import load_workbook
import sys
import csv
import os

printout = ""
def prints(string):
    global printout
    printout += str(string) + "<br />"
    print(string)

# converting mouser xls file to array
def load_mouser(file_mouser, fast=False):
    try:
        wb = open_workbook(file_mouser)
    except:
        prints("Error(Mouser): could not open the file")
        return 0

    prints('Reading File Succeded')

    sh = wb.sheet_by_index(0)

    col = sh.col_values(0)
    offset_mouser = col.index(1)
    if offset_mouser == -1:
        prints('Error(Mouser): did not find a part')
        return 0

    nparts = col.index('', offset_mouser) - offset_mouser
    if nparts <= -1:
        prints('Error(Mouser): did not the end of the file')
        return 0

    vertrag = 'Mouser Electronics, Elsenheimerstrasse 11, 80687, München, DE (EUR)'
    if fast:
        vertrag = 'Mouser Electronics (EUR) (Fasttrack)'
    part_mouser = ['', '', vertrag, 'Mouser Electronics', '', '', '', '', '', 820, 0, 'Stück', '', '', '', '', '', '8.1', 0, 'EUR']
    parts_mouser = []

    for part in range(nparts):
        parts_mouser.append(part_mouser.copy())

    for x, y in enumerate(range(offset_mouser, offset_mouser + nparts)):
        parts_old = sh.row_values(y, 0, 11)
        parts_mouser[x][0] = str(int(parts_old[0]))
        parts_mouser[x][1] = parts_old[1]
        parts_mouser[x][4] = parts_old[3]
        parts_mouser[x][5] = parts_old[2]
        parts_mouser[x][6] = parts_old[3] + " " + parts_old[2]
        parts_mouser[x][7] = parts_old[5]
        parts_mouser[x][10] = int(parts_old[8])
        w = parts_old[9][-1]
        if w != '€':
            prints('Error: please change mouser currency to euro and retry')
            return 0
        try:
            p = float(parts_old[9][0:-2].replace(',', '.'))
        except ValueError:
            prints("Error: mouser price is not in right format")
            return 0
        parts_mouser[x][18] = p

    #prints(parts_mouser)

    return parts_mouser


# converting digikey csv file to array
def load_digikey(file_digikey, fast=False):
    try:
        # Use utf-8-sig to tolerate a BOM and newline='' for csv correctness
        with open(file_digikey, 'r', newline='', encoding='utf-8-sig') as CSV:
            read_csv = csv.reader(CSV, delimiter=",")
            read_csv = list(read_csv)
    except Exception as e:
        prints("Error: could not open the file: " + str(e))
        return 0

    prints('Reading File Succeded')

    offset_digikey = -1
    for i, row in enumerate(read_csv):
        # guard against empty rows
        if row and row[0] == '1':
            offset_digikey = i
            break

    if offset_digikey <= -1:
        prints('Error(Digikey): did not find the start of the file')
        return 0

    # Collect data rows after the offset, skipping empty/trailing blank rows
    data_rows = [r for r in read_csv[offset_digikey:] if r and any((c.strip() if isinstance(c, str) else True) for c in r)]
    nparts = len(data_rows)

    vertrag = 'Digi-Key Corporation, Brooks Avenue South 701, 56701, Thief River Falls, US (CHF)'
    if fast:
        vertrag = 'Digi-Key Corporation (CHF) (Fasttrack)'
    # Index,"Menge","Teilenummer","Hersteller-Teilenummer","Beschreibung","Kundenreferenz","Verfügbar","Lieferrückstände","Stückpreis","Gesamtpreis EUR"
    # Position	Artikelnummer	Vertrag 	Lieferant	Hersteller	Herstellernummer	Kurztext	Langtext	Materialnummer	Standardwarengruppe	Menge	Einheiten ppppp	MwSt-Satz	Preis	Währung
    part_digikey = ['', '', vertrag, 'Digi-Key Corporation', '', '', '', '', '', 820, 0, 'Stück', '', '', '', '', '', '8.1', 0, 'CHF']
    parts_digikey = []

    for part in range(nparts):
        parts_digikey.append(part_digikey.copy())

    for x, parts_old in enumerate(data_rows):
        # basic validation: ensure we have enough columns expected by the parser
        if len(parts_old) < 10:
            prints(f"Error(Digikey): malformed data row {x} (expected >=10 columns), got {len(parts_old)}")
            return 0

        try:
            parts_digikey[x][0] = str(int(parts_old[0]))
        except Exception:
            prints(f"Error(Digikey): invalid index value on data row {x}: '{parts_old[0]}'")
            return 0

        parts_digikey[x][1] = parts_old[2]
        parts_digikey[x][5] = parts_old[3]
        parts_digikey[x][6] = parts_old[3]
        parts_digikey[x][7] = parts_old[4]

        try:
            parts_digikey[x][10] = int(parts_old[1])
        except Exception:
            prints(f"Error(Digikey): invalid quantity on data row {x}: '{parts_old[1]}'")
            return 0

        w = parts_old[9][-3:]
        if w != 'Fr.':
            prints('Error: please change digikey currency to chf and retry')
            return 0

        try:
            p = float(parts_old[8].replace(',', '.'))
        except ValueError:
            prints(f"Error: digikey price is not in right format on data row {x}: '{parts_old[8]}'")
            return 0
        parts_digikey[x][18] = p

    # prints(parts_digikey)

    return parts_digikey

load_functions = {'mouser': load_mouser, 'digikey': load_digikey}
fasttrack = ['mouser', 'digikey']

def p4u(args):
    global printout
    printout = ""

    if len(args) <= 2:
        prints("Error: please specify a type(mouser/digikey) and a file")
        return 0
    else:
        file_type = args[1]
        filename = os.path.basename(args[2])
        file = os.path.join('uploads', filename)
        fast = ''
        if len(args) == 4:
            fast = args[3]

        if not os.path.exists(file):
            prints('Error: could not open the file. Tried: ' + file + '.')
            prints('Please upload the file through the website so it appears in the uploads/ folder.')
            return 0

    parts = []

    if file_type in load_functions.keys():
        prints("Found load function for " + file_type)
        # Call the loader and validate its return value instead of blindly
        # extending, which raises when the loader returns 0 (an int).
        try:
            if fast == 'fasttrack' and file_type in fasttrack:
                prints('Fasttrack Order found')
                result = load_functions[file_type](file, True)
            else:
                prints('No Fasttrack')
                result = load_functions[file_type](file)
        except Exception as e:
            prints("Error: loader raised an exception: " + str(e))
            return 0

        if not result:
            prints('Error: loader returned no data (check earlier messages)')
            return 0

        if not isinstance(result, list):
            prints('Error: loader returned unexpected type: ' + str(type(result)))
            return 0

        parts.extend(result)
    else:
        prints("Error: wrong type of file(mouser/digikey)")
        return 0

    if not parts:
        prints('Error: no data recieved (empty files?)(wrong filename?)')
        return 0

    new_file = './Warenkorb_' + file_type + '.xlsx'
    prints('Writing to new File: ' + new_file)

    try:
        wb_neu = load_workbook('./templates/Warenkorb.xlsx')
    except:
        prints("Error: template Warenkorb.xlsx is missing, please put it back in the templates folder")
        return 0

    sh_neu = wb_neu.active

    offset = 0

    for i, row in enumerate(sh_neu.values):
        if (row[0]) == 'Position':
            offset = i + 1

    for i in range(len(parts)):
        for j in range(len(parts[i])):
            sh_neu.cell(offset + i + 1, j + 1, parts[i][j])

    wb_neu.save('./exports/' + new_file)

    prints('Finished')
    return new_file

if __name__ == '__main__':

    if(not p4u(sys.argv)):
        print(printout)
        sys.exit(1)
    print("Done")
    #print(printout)