"""Rollback the 70 templated Balcano descriptions written in this session.
Clears xlsx cols 104, 105 for rows 3436-3505, updates _progress.json.
"""
import openpyxl, json, os, time

BASE = os.path.dirname(os.path.abspath(__file__))
DST = os.path.join(BASE, 'merged_master_v2_updated.xlsx')
PROGRESS = os.path.join(BASE, '_progress.json')

LEADERS = [
    "341242BC9642","341243BC8845","341243BC8850","341243BC9645","341245BC1145","341245BC1155","341245BC1160","341245BC8845","341245BC8850","341245BC8855",
    "341245BC9645","341245BC9650","341247BC1145","341247BC1150","341247BC1155","341247BC1160","341247BC8850","341247BC8855","341247BC8860","341253BC1142",
    "341253BC8842","341253BC9642","341262BC8842","341262BC9642","341287BC8840","341287BC8845","341287BC9640","341287BC9645","341287BC9650","341288BC9650",
    "341291BC8842","341291BC9600","341292BC8842","341292BC9642","341293BC8850","341293BC8855","341312BC1150","341312BC1155","341312BC1160","341312BC8845",
    "341312BC8850","341312BC8860","341312BC9650","341312BC9660","341313BC1145","341313BC1150","341313BC1155","341313BC1160","341313BC8850","341313BC8855",
    "341313BC8860","341313BC9660","341315BC1145","341315BC1150","341315BC1155","341315BC1160","341315BC8850","341315BC8855","341315BC8860","341338BC8845",
    "341338BC8850","341338BC9640","341338BC9645","341338BC9650","341356BC8850","341394BC8842","341394BC9642","341417BC8850","341417BC8855","341417BC8860",
]
ROWS = list(range(3436, 3506))
assert len(LEADERS) == 70 and len(ROWS) == 70

# Clear xlsx cells
wb = openpyxl.load_workbook(DST)
ws = wb['Sheet1']
for r in ROWS:
    ws.cell(row=r, column=104, value=None)
    ws.cell(row=r, column=105, value=None)
tmp = DST + '.tmp'
wb.save(tmp); wb.close()
for i in range(30):
    try:
        os.replace(tmp, DST)
        break
    except PermissionError:
        if i == 0: print('File locked, retrying...')
        time.sleep(2)
else:
    raise PermissionError('Could not replace xlsx')
print('Cleared xlsx cells for rows', ROWS[0], '..', ROWS[-1])

# Update progress
with open(PROGRESS, encoding='utf-8') as f:
    prog = json.load(f)
done_leaders = set(prog['done_leaders']) - set(LEADERS)
done_cikks = set(prog['done_cikks']) - set(LEADERS)
prog['done_leaders'] = list(done_leaders)
prog['done_cikks'] = list(done_cikks)
prog['next_index'] = max(0, prog.get('next_index', 0) - 70)
tmp = PROGRESS + '.tmp'
with open(tmp, 'w', encoding='utf-8') as f:
    json.dump(prog, f, ensure_ascii=False, indent=1)
os.replace(tmp, PROGRESS)
print(f'Progress: next_index={prog["next_index"]}, done_leaders={len(done_leaders)}, done_cikks={len(done_cikks)}')