"""Check generated content and export contact sheets for visual review.

Run: py scripts/verify_materials.py [--recalculated-workbook <path>]
"""
from pathlib import Path
from collections import Counter
import json
import re
import unicodedata
import argparse
import zipfile
import pymupdf
import openpyxl
from PIL import Image, ImageDraw
from pptx import Presentation

# Ожидаемые контрольные значения учебного набора.
ROWS_TOTAL = 100
LAST = ROWS_TOTAL + 1          # последняя строка данных листа «Реестр»
DEPTS_TOTAL = 6
FIRST_DATA = 2
STATIC_FIRST, STATIC_TOTAL = 4, 10          # строки статического эталона
REP_FIRST = 17                              # первая строка отчёта на формулах
REP_LAST = REP_FIRST + DEPTS_TOTAL - 1
REP_TOTAL = REP_LAST + 1
REP_CHECKS = (REP_TOTAL + 2, REP_TOTAL + 3)

ROOT = Path(__file__).resolve().parents[1]
parser = argparse.ArgumentParser()
parser.add_argument('--recalculated-workbook', type=Path,
                    help='Optional independent LibreOffice/Excel recalculation copy')
args = parser.parse_args()
OUT = ROOT / 'materials'
PREV = OUT / 'preview'
PREV.mkdir(exist_ok=True)
WORKBOOK = OUT / 'excel-practice.xlsx'

p = Presentation(OUT / 'excel-ai-lecture.pptx')
assert len(p.slides) == 26
manifest = json.loads((OUT / 'slides-manifest.json').read_text(encoding='utf-8'))
assert sum(s['seconds'] for s in manifest) == 3600
notes = (OUT / 'speaker-notes.md').read_text(encoding='utf-8')
durations = [int(v) for v in re.findall(r'\*\*Длительность:\*\* (\d+) секунд', notes)]
assert durations == [s['seconds'] for s in manifest], durations
assert all(s.notes_slide.notes_text_frame.text.strip() for s in p.slides)
for n, s in enumerate(p.slides, 1):
    for sh in s.shapes:
        assert sh.left >= 0 and sh.top >= 0, (n, sh.name, 'negative position')
        assert sh.left + sh.width <= p.slide_width + 10000, (n, sh.name, 'too wide')
        assert sh.top + sh.height <= p.slide_height + 10000, (n, sh.name, 'too tall')

book = openpyxl.load_workbook(WORKBOOK, data_only=True)
formulas = openpyxl.load_workbook(WORKBOOK, data_only=False)
rows = list(book['Реестр'].iter_rows(min_row=FIRST_DATA, max_row=LAST, values_only=True))
assert len(rows) == ROWS_TOTAL and len({r[0] for r in rows}) == ROWS_TOTAL
assert all(all(v is not None for v in r[:6]) for r in rows)
assert Counter(r[7] for r in rows) == {'Завершено':40,'Просрочено':30,'В срок':30}
assert sum(1 for r in rows if r[5] == 'Выполнено') == 40
assert sum(1 for r in rows if r[5] == 'Выполнено' and r[6] > r[4]) == 20
assert all(r[6] is None or r[6] <= book['Параметры']['B2'].value for r in rows)
assert all(r[3] < r[4] for r in rows), 'дата поступления должна быть раньше срока'
assert len({r[1] for r in rows}) == DEPTS_TOTAL
assert book['Проверка']['B2'].value == 100 and book['Проверка']['B3'].value == 40
assert book['Проверка']['B4'].value == 60 and book['Проверка']['B5'].value == 30
assert book['Проверка']['B6'].value == .5
assert len(book['Реестр'].tables) == 1
assert len(book['Старт'].tables) == 0 and len(book['Старт 2'].tables) == 0

# Teaching progression: simple IF on Старт, nested IF on Старт 2, identical to Реестр.
data_range = range(FIRST_DATA, LAST + 1)
simple = [book['Старт'].cell(r, 8).value for r in data_range]
nested = [book['Старт 2'].cell(r, 8).value for r in data_range]
assert simple == ['Закрыто' if r[5] == 'Выполнено' else 'В работе' for r in rows]
assert nested == [r[7] for r in rows]
for name in ('Реестр', 'Старт', 'Старт 2'):
    assert all(formulas[name].cell(r, 8).data_type == 'f' for r in data_range), name
assert 'IF(F2="Выполнено","Закрыто","В работе")' in formulas['Старт'].cell(2, 8).value
assert formulas['Старт 2'].cell(2, 8).value.count('IF(') == 2

# Dropdowns: every editable field is a pick list backed by a named range on Параметры.
statuses = [book['Параметры'].cell(r, 1).value for r in range(5, 8)]
depts = [book['Параметры'].cell(r, 1).value for r in range(10, 10 + DEPTS_TOTAL)]
cats = [book['Параметры'].cell(r, 1).value for r in range(17, 20)]
assert len(depts) == DEPTS_TOTAL and len(set(depts)) == DEPTS_TOTAL
expected_dv = {(sheet, col): name
               for sheet in ('Старт', 'Старт 2', 'Реестр')
               for col, name in (('B', 'AllowedDepartments'), ('C', 'AllowedCategories'), ('F', 'AllowedStatuses'))}
for (sheet, column), name in expected_dv.items():
    found = [dv for dv in formulas[sheet].data_validations.dataValidation
             if f'{column}2:{column}1000' == str(dv.sqref)]
    assert found, (sheet, column, 'нет выпадающего списка')
    assert found[0].type == 'list' and found[0].formula1 == name, (sheet, column, found[0].formula1)
assert all(r[1] in depts for r in rows), 'подразделение вне списка'
assert all(r[2] in cats for r in rows), 'категория вне списка'
assert all(r[5] in statuses for r in rows), 'статус вне списка'
assert set(r[1] for r in rows) == set(depts), 'все подразделения из списка должны встречаться в реестре'

# Статический эталон и отчёт на формулах должны сходиться между собой.
rep = book['Эталон отчёта']
static = {rep.cell(r, 1).value: (rep.cell(r, 2).value, rep.cell(r, 3).value, rep.cell(r, 4).value, rep.cell(r, 5).value)
          for r in range(STATIC_FIRST, STATIC_TOTAL)}
assert len(static) == DEPTS_TOTAL, static
assert (rep.cell(STATIC_TOTAL, 2).value, rep.cell(STATIC_TOTAL, 3).value,
        rep.cell(STATIC_TOTAL, 4).value, rep.cell(STATIC_TOTAL, 5).value) == (40, 30, 30, 100)
for r in range(REP_FIRST, REP_TOTAL):
    dept = rep.cell(r, 1).value
    done, late, soon, total = static[dept]
    assert (rep.cell(r, 2).value, rep.cell(r, 3).value, rep.cell(r, 4).value) == (total, done, late), (dept, 'отчёт на формулах')
    assert rep.cell(r, 5).value == soon, (dept, 'в срок')
    assert abs(rep.cell(r, 6).value - late / (late + soon)) < 1e-9, (dept, 'доля просроченных среди открытых')
assert (rep.cell(REP_TOTAL, 2).value, rep.cell(REP_TOTAL, 3).value,
        rep.cell(REP_TOTAL, 4).value, rep.cell(REP_TOTAL, 5).value) == (100, 40, 30, 30)
assert abs(rep.cell(REP_TOTAL, 6).value - .5) < 1e-9
assert all(rep.cell(r, 2).value == 'Совпадает' for r in REP_CHECKS), 'обе строки сверки должны совпадать'
report_formulas = formulas['Эталон отчёта']
assert all(report_formulas.cell(r, 2).data_type == 'f'
           for r in list(range(REP_FIRST, REP_TOTAL + 1)) + list(REP_CHECKS))
assert 'COUNTIFS' in report_formulas.cell(REP_FIRST, 3).value
assert f'$B${LAST}' in report_formulas.cell(REP_FIRST, 3).value, 'диапазон должен охватывать весь реестр'

# Лист с диаграммами включается флагом --with-chart-sheet. Проверяем его только если он есть.
chart_sheet = book['Диаграмма'] if 'Диаграмма' in book.sheetnames else None
if chart_sheet is None:
    chart_status = 'лист «Диаграмма» не включён (ожидаемо: диаграммы строятся на лекции вживую)'
    with zipfile.ZipFile(WORKBOOK) as archive:
        assert not [n for n in archive.namelist() if n.startswith('xl/charts/')], 'найдены диаграммы без листа'
else:
    chart_last = 1 + DEPTS_TOTAL
    for n, dept in enumerate(static):
        r = 2 + n
        done, late, soon, total = static[dept]
        assert chart_sheet.cell(r, 1).value == dept
        assert (chart_sheet.cell(r, 2).value, chart_sheet.cell(r, 3).value,
                chart_sheet.cell(r, 4).value) == (done, late, soon), (dept, 'блок диаграммы')
    assert (chart_sheet.cell(chart_last + 1, 2).value, chart_sheet.cell(chart_last + 1, 3).value,
            chart_sheet.cell(chart_last + 1, 4).value) == (40, 30, 30)
    with zipfile.ZipFile(WORKBOOK) as archive:
        chart_parts = [n for n in archive.namelist() if n.startswith('xl/charts/chart') and n.endswith('.xml')]
        assert len(chart_parts) == 2, chart_parts
        blob = ''.join(archive.read(n).decode('utf-8') for n in chart_parts)
    assert 'Диаграмма!' in blob and 'Реестр!' not in blob, 'диаграммы должны ссылаться на лист Диаграмма'
    assert f'Диаграмма!$A$2:$A${chart_last + 1}' in blob, 'нет категорий по подразделениям'
    assert f'Диаграмма!$B${chart_last + 1}:$D${chart_last + 1}' in blob, 'круговая диаграмма должна брать строку ИТОГО'
    chart_status = 'лист «Диаграмма»: блок данных и две диаграммы совпадают с эталоном'

for r in rows:
    expected = 'Завершено' if r[5] == 'Выполнено' else ('Просрочено' if r[4] < book['Параметры']['B2'].value else 'В срок')
    assert expected == r[7]
assert all(book['Тесты'].cell(n, 4).value == book['Тесты'].cell(n, 5).value for n in range(2, 6))

recalc_status = 'not performed in this check'
if args.recalculated_workbook:
    recalculated = openpyxl.load_workbook(args.recalculated_workbook, data_only=True)
    assert list(recalculated['Реестр'].iter_rows(min_row=FIRST_DATA, max_row=LAST, values_only=True)) == rows
    for n in range(2, 10):
        assert recalculated['Проверка'].cell(n, 2).value == book['Проверка'].cell(n, 2).value, n
    assert all(recalculated['Тесты'].cell(n, 4).value == recalculated['Тесты'].cell(n, 5).value for n in range(2, 6))
    assert [recalculated['Старт'].cell(r, 8).value for r in data_range] == simple
    assert [recalculated['Старт 2'].cell(r, 8).value for r in data_range] == nested
    recalc_report = recalculated['Эталон отчёта']
    for r in list(range(REP_FIRST, REP_TOTAL + 1)) + list(REP_CHECKS):
        for col in range(2, 7):
            a, b = recalc_report.cell(r, col).value, rep.cell(r, col).value
            same = abs(a - b) < 1e-9 if isinstance(a, (int, float)) and isinstance(b, (int, float)) else a == b
            assert same, (r, col, a, b)
    if chart_sheet is not None:
        recalc_charts = recalculated['Диаграмма']
        for r in range(2, chart_last + 2):
            for col in range(2, 5):
                assert recalc_charts.cell(r, col).value == chart_sheet.cell(r, col).value, (r, col)
    recalc_status = ('independent recalculation matches 100 rows, both teaching sheets, '
                     'formula report, totals and boundary tests')

pdf = pymupdf.open(OUT / 'excel-ai-lecture.pdf')
assert len(pdf) == 26


def norm(t):
    return ''.join(c for c in unicodedata.normalize('NFKC', t).lower() if c.isalnum())


warnings = []
for i, s in enumerate(p.slides):
    page = pdf[i]
    pdf_text = norm(page.get_text())
    for sh in s.shapes:
        if not sh.has_text_frame:
            continue
        for para in sh.text_frame.paragraphs:
            expected = norm(para.text)
            if expected and expected not in pdf_text:
                warnings.append(f'Slide {i+1}: text not found intact: {para.text}')
    for block in page.get_text('dict')['blocks']:
        if 'lines' not in block:
            continue
        for line in block['lines']:
            for span in line['spans']:
                x0, y0, x1, y1 = span['bbox']
                if min(x0, y0) < -1 or x1 > page.rect.width + 1 or y1 > page.rect.height + 1:
                    warnings.append(f'Slide {i+1}: text exceeds page: {span["text"]}')
    page.get_pixmap(matrix=pymupdf.Matrix(1.4, 1.4)).save(PREV / f'slide-{i+1:02}.png')

for start in range(0, 26, 8):
    ids = list(range(start, min(start + 8, 26)))
    sheet = Image.new('RGB', (1200, 360 * ((len(ids) + 1) // 2)), '#dedee8')
    for k, i in enumerate(ids):
        pm = pdf[i].get_pixmap(matrix=pymupdf.Matrix(.625, .625))
        im = Image.frombytes('RGB', (pm.width, pm.height), pm.samples)
        x = (k % 2) * 600
        y = (k // 2) * 360
        sheet.paste(im, (x, y))
        ImageDraw.Draw(sheet).text((x + 12, y + 339), f'SLIDE {i+1:02}', fill='black')
    sheet.save(PREV / f'contact-{start//8+1}.png')

try:
    import zxingcpp
    result = zxingcpp.read_barcode(Image.open(OUT / 'telegram-qr.png'))
    assert result and result.text == 'https://t.me/kirill_bouko_it'
    # Verify QR survives PDF export, not only its standalone source image.
    result = zxingcpp.read_barcode(Image.open(PREV / 'slide-25.png'))
    assert result and result.text == 'https://t.me/kirill_bouko_it'
    qr_status = 'decoded from PNG and PDF slide 25'
except ImportError:
    qr_status = 'decoder unavailable; image generated from exact URL'

report = {
    'slides':26, 'total_seconds':3600, 'notes_embedded':True, 'pdf_pages':len(pdf),
    'synthetic_rows':ROWS_TOTAL, 'departments':DEPTS_TOTAL, 'completed':40, 'open':60,
    'overdue_open':30, 'overdue_share_open':.5, 'completed_late':20,
    'qr':qr_status, 'render_text_warnings':warnings, 'chart_sheet':chart_status,
    'recalculated_workbook':recalc_status,
}
(OUT / 'validation.json').write_text(json.dumps(report, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
print(json.dumps(report, ensure_ascii=False, indent=2))
assert not warnings, 'Inspect PDF text warnings before delivering'