import numpy as np
from odf.opendocument import load
from odf.table import Table, TableRow, TableCell
from odf.text import P
from pathlib import Path

def get_cell_value(cell, isStr, isLast):
    # need to consider table:table-cell table:number-columns-repeated="10"
    #print (cell.attributes)
    noValue = True
    repetitions = cell.attributes.get(('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated'), '1')
    if not isStr and cell.attributes.get(('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value-type')) == 'float':
        cell_value = float(cell.attributes.get(('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')))
        noValue = False
    elif cell.attributes.get(('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value-type')) == "string":
        cell_value = ''
        texts_p = cell.getElementsByType(P)
        for text_p in texts_p:
            if text_p.parentNode.qname[1] == "annotation": # keine Kommentare
                continue
            cell_value += text_p.firstChild.__str__().strip()
        noValue = False
    if not isStr and noValue and not isLast:
        noValue = False
        cell_value = 0
    retList = []
    if not noValue:
        for i in range(int(repetitions)):
            retList.append(cell_value)
    return (retList, int(repetitions))

def readODS(file_path):
    doc = load(file_path)
    sheets = doc.getElementsByType(Table)
    # Dictionary to store each sheet as a numpy array
    sheet_arrays = {}
    # Process each sheet
    for sheet in sheets:
        try:
            # Get all rows in the sheet
            rows = sheet.getElementsByType(TableRow)
            if not rows:
                continue
            # Extract the column names from the first row (header)
            column_names = []
            header_row = rows[0]
            header_cells = header_row.getElementsByType(TableCell)
            for idx, cell in enumerate(header_cells):
                if idx == len(header_cells) - 1:
                    cellValue, nrCells = get_cell_value(cell, True, True)
                else:
                    cellValue, nrCells = get_cell_value(cell, True, False)
                if len(cellValue) > 0:
                    column_names += [val.strip() for val in cellValue]
            if not column_names: # empty sheet
                continue
            sheet_data = []
            # Iterate through each row in the sheet (skipping the first row, which is the header)
            for row in rows[1:]:
                cells = row.getElementsByType(TableCell)
                if not cells:
                    continue
                row_data = []
                parameter, nrCells = get_cell_value(cells[0], False, False)  # Extract the 'Parameter' (first column as string)
                if len(parameter) == 0:
                    continue
                row_data += parameter
                for i in range(1, len(cells)): # Skip the first column (Parameter)
                    if i < len(cells) - 1 : # fill data colums
                        cell_data, nrCells = get_cell_value(cells[i], False, False)
                        row_data += cell_data
                    elif i < len(cells):
                        cell_data, nrCells = get_cell_value(cells[i], False, True)
                        row_data += cell_data
                fullRowDiff = len(column_names) - len(row_data)
                if fullRowDiff > 0: # fill up missing row values
                    for i in range(fullRowDiff):
                        row_data.append(0)
                elif fullRowDiff < 0: # cut extra cells
                    row_data = row_data[:fullRowDiff]
                sheet_data.append(row_data)
            if sheet_data:
                # Convert the collected data to a numpy array with the specified dtype
                # The first column 'Parameter' is object, the others are float64 ('f8')
                dtype = [('Parameter', object)] + [(col, 'f8') for col in column_names[1:]]
                try:
                    sheet_arrays[sheet.getAttribute('name')] = np.array([tuple(x) for x in sheet_data], dtype=dtype)
                except Exception as e:
                    print(f"<pre>Debug - Failed to create array for sheet {sheet.getAttribute('name')}:\n")
                    print(f"dtype: {dtype}")
                    print(f"data sample: {sheet_data[:2] if sheet_data else 'empty'}")
                    print(f"error: {str(e)}</pre>")
                    raise

        except Exception as e:
            print(f"<pre>Debug - Error processing sheet {sheet.getAttribute('name')}: {str(e)}</pre>")
            raise
    return sheet_arrays

def saveODSdataToNPZ(filepath):
    dict = readODS(filepath)
    savefilepath = Path(filepath).with_suffix('.npz')
    print("saving data to " + str(savefilepath))
    np.savez(savefilepath, **dict)