"""Read source OpenDocument cells without altering the workbook."""
import json
import xml.etree.ElementTree as ET
import zipfile
from pathlib import Path

ROOT = Path(__file__).resolve().parent
NS = {'t': 'urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'o': 'urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'x': 'urn:oasis:names:tc:opendocument:xmlns:text:1.0'}

def colname(number):
    value = ''
    while number:
        number, rem = divmod(number - 1, 26)
        value = chr(65 + rem) + value
    return value

with zipfile.ZipFile(ROOT / 'sources/libraries-2025-26.ods') as archive:
    tree = ET.fromstring(archive.read('content.xml'))

sheets = []
for table in tree.findall('.//t:table', NS):
    if table.attrib['{' + NS['t'] + '}name'] not in ('K3', 'K6'):
        continue
    sheet = {'name': table.attrib['{' + NS['t'] + '}name'], 'rows': []}
    ri = 1
    for row in table.findall('.//t:table-row', NS):
        repeat_rows = int(row.attrib.get('{' + NS['t'] + '}number-rows-repeated', '1'))
        ci, cells = 1, []
        for cell in list(row):
            if cell.tag.rsplit('}', 1)[-1] not in ['table-cell', 'covered-table-cell']:
                continue
            repeat_cols = int(cell.attrib.get('{' + NS['t'] + '}number-columns-repeated', '1'))
            text = '\n'.join(''.join(p.itertext()) for p in cell.findall('x:p', NS))
            kind = cell.attrib.get('{' + NS['o'] + '}value-type')
            value = cell.attrib.get('{' + NS['o'] + '}value')
            if text or value is not None:
                assert repeat_rows <= 100 and repeat_cols <= 100, 'Unexpected repeated meaningful cells'
                for offset in range(repeat_cols):
                    cells.append({'column': ci + offset, 'cell': colname(ci + offset) + str(ri), 'type': kind, 'value_raw': value, 'text': text})
            ci += repeat_cols
        if cells:
            for offset in range(repeat_rows):
                sheet['rows'].append({'row': ri + offset, 'cells': cells})
        ri += repeat_rows
    sheets.append(sheet)

(ROOT / 'workbook-sparse.json').write_text(json.dumps(sheets, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
print(json.dumps([{'name': sheet['name'], 'meaningful_rows': len(sheet['rows']), 'first_rows': [[c['text'] for c in row['cells']][:4] for row in sheet['rows'][:4]]} for sheet in sheets], ensure_ascii=False, indent=2))