import json
from pathlib import Path
import openpyxl

root = Path(__file__).parent
source = root / 'RevScan-Assessment-Working-2026-09-10.xlsx'
book = openpyxl.load_workbook(source, data_only=True)
context = {r[0]: r for r in list(book['All rows'].values)[1:] if isinstance(r[0], (int, float))}
items = []
for excel_row, r in enumerate(list(book['Core Managed drafts'].values)[2:], 3):
    c = context.get(r[2], [None]*10)
    items.append(dict(priority=r[0], artifact=r[1], row=r[2], number=r[3], question=r[4], kind=r[5], draft=r[6], decision=r[7], notes=r[8], section=c[2], evidence=c[9], excelRow=excel_row, owner=c[4], status=c[5], sourceSheet='Core Managed drafts'))
for excel_row, r in enumerate(list(book['All rows'].values)[1:], 2):
    if not isinstance(r[0], (int, float)) or r[4] == 'Core Managed':
        continue
    items.append(dict(priority=r[6], artifact=r[7], row=r[0], number=r[1], question=r[3], kind=None, draft=r[8], decision=None, notes=None, section=r[2], evidence=r[9], excelRow=excel_row, owner=r[4], status=r[5], sourceSheet='All rows'))
data = json.dumps(items, ensure_ascii=False).replace('<', '\\u003c').replace('&', '\\u0026')
template = (root / 'reader-template.html').read_text(encoding='utf-8')
(root / 'core-managed-review.html').write_text(template.replace('__DATA__', data), encoding='utf-8')
print(f'Created reader with {len(items)} responses across all owners')