"""公開集計の独立再計算。元XLSXは読み取り専用、出力は当フォルダ内のみ。

期待値の出所: 公式表1/表3 A34:A45の費目順、表3 A4/A5の定義、
訂正版調査結果の概要PDF15頁表6・16頁表7・17頁表8-1/8-2。
XLSXのXMLを直接読んで、別経路artifact-tool抽出と全72数値を突合する。
"""
from pathlib import Path
from decimal import Decimal, ROUND_HALF_UP
import csv
import hashlib
import json
import unicodedata
import xml.etree.ElementTree as ET
import zipfile

HERE = Path(__file__).resolve().parent
SPEC = json.loads((HERE/'検査仕様.json').read_text())
D = Decimal
NS = {'s':'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
LABELS = ['学校外活動費','補助学習費','家庭内学習費','通信教育・家庭教師費','学習塾費','その他','その他の学校外活動費','体験活動・地域活動','芸術文化活動','スポーツ・レクリエーション活動','国際交流体験活動','教養・その他']
LEAF_ROWS = [36,37,38,39,41,42,43,44,45]
EN = ['Out-of-school activities','Supplementary learning','Home learning','Correspondence education and private tutors','Cram schools','Other','Other out-of-school activities','Experiential and community activities','Arts and cultural activities','Sports and recreation activities','International exchange activities','General learning and other activities']
SOURCE1 = '学校種別の学習費_20260116訂正.xlsx'
CONFIG = [
    ('公立小学校','Public elementary school','小学校','D','000040233665','2023kodo003-02',6627,34.3),
    ('公立中学校','Public junior high school','中学校','F','000040233666','2023kodo003-03',1327,49.1),
]
SOURCE_URL = {
    SOURCE1:'https://www.e-stat.go.jp/stat-search/file-download?statInfId=000040233659&fileKind=0',
    '調査の概要_20260116訂正.pdf':'https://www.mext.go.jp/content/20260116-mxt_chousa01-000039333_2.pdf',
    '調査結果の概要_20260116訂正.pdf':'https://www.mext.go.jp/content/20260116-mxt_chousa01-000039333_3.pdf',
    '復元推計方法について.pdf':'https://www.mext.go.jp/content/20250114-mxt_chousa01-100012576.pdf',
    '学習費支出状況_公式一覧.html':'https://www.e-stat.go.jp/stat-search/files?cycle=0&layout=datalist&page=1&tclass1=000001224200&tclass2=000001224201&tclass3=000001224328&tclass4val=0&toukei=00400201&tstat=000001012023',
}
for _,_,school,_,fileid,_,_,_ in CONFIG:
    SOURCE_URL[f'学習費支出状況_{school}_20260116訂正.xlsx'] = f'https://www.e-stat.go.jp/stat-search/file-download?statInfId={fileid}&fileKind=0'

def sha(p): return hashlib.sha256(p.read_bytes()).hexdigest()
def save(name,obj): (HERE/name).write_text(json.dumps(obj,ensure_ascii=False,indent=2)+'\n')
def raw_sheet(name):
    with zipfile.ZipFile(HERE/name) as z:
        strings=[]
        if 'xl/sharedStrings.xml' in z.namelist():
            for si in ET.fromstring(z.read('xl/sharedStrings.xml')).findall('s:si',NS):
                # rPh are phonetic guides, not visible source labels.
                strings.append(''.join(t.text or '' for t in si.findall('s:t',NS)+si.findall('s:r/s:t',NS)))
        cells={}
        for c in ET.fromstring(z.read('xl/worksheets/sheet1.xml')).findall('.//s:c',NS):
            v=c.find('s:v',NS)
            if c.get('t')=='inlineStr': value=''.join(t.text or '' for t in c.findall('s:is/s:t',NS))
            elif v is None: continue
            elif c.get('t')=='s': value=strings[int(v.text)]
            else: value=v.text
            cells[c.get('r')]=value
        return cells

extracted={x['file']:x for x in json.loads((HERE/'原表セル抽出.json').read_text())}
raw={name:raw_sheet(name) for name in SOURCE_URL if name.endswith('.xlsx')}
checks=[]
def check(name,truth,details=None):
    checks.append({'check':name,'status':'PASS' if truth else 'FAIL','details':details})
    if not truth: raise AssertionError(name+' '+str(details))

def val(file,cell):
    row=int(''.join(c for c in cell if c.isdigit()))
    col=0
    for c in ''.join(c for c in cell if c.isalpha()): col=col*26+ord(c)-64
    return extracted[file]['values'][row-1][col-1]

records=[]
numeric_audit=[]
for school,school_en,stage,col,fileid,sheet,n,response in CONFIG:
    file=f'学習費支出状況_{stage}_20260116訂正.xlsx'
    check(school+'列見出し',raw[file]['B8']==school and raw[file]['B11']=='支出率（％）' and raw[file]['C11']=='支出者平均額（千円）')
    check(school+'原表定義','各経費を支出した者の比率（％）' in raw[file]['A4'] and '各経費を支出した者の平均額（千円）' in raw[file]['A4'])
    check(school+'シート名',extracted[file]['sheet']==sheet)
    for row,label,en in zip(SPEC['source_rows'],LABELS,EN):
        check(school+f' {row} 費目名完全一致',raw[file][f'A{row}']==label==raw[SOURCE1][f'A{row}'])
        values=[]
        for src,cell in [(SOURCE1,f'{col}{row}'),(file,f'B{row}'),(file,f'C{row}')]:
            xml=D(raw[src][cell]); api=D(str(val(src,cell)))
            # XLSX B39等には19.100000000000001というIEEE754保存桁がある。
            # 浮動小数点の同値を確認し、公表書式（率は小数1桁）にのみ正規化。
            check(f'XMLと読取API {src} {cell}',float(xml)==float(api))
            quantum=D('0.1') if src!=SOURCE1 and cell.startswith('B') else D(1)
            published=xml.quantize(quantum,rounding=ROUND_HALF_UP)
            check(f'公表桁整合 {src} {cell}',published==api)
            numeric_audit.append({'file':src,'cell':cell,'xml':str(xml),'read_api':str(api),'published':str(published),'numeric_equivalence':'IEEE754値一致・公表桁一致'})
            values.append(published)
        mean,rate,payer=values
        product=rate/D(100)*payer*1000
        parent='学校外活動費' if row in [35,40] else ('補助学習費' if row in [36,37,38,39] else ('その他の学校外活動費' if row in [41,42,43,44,45] else None))
        records.append({
            'school':school,'school_en':school_en,'activity':label,'activity_en':en,'parent_activity':parent,
            'row_kind':'activity' if row in LEAF_ROWS else 'aggregate','source_row':row,
            'overall_mean_yen':int(mean),'spending_rate_pct':float(rate),
            'spender_mean_thousand_yen':int(payer),'spender_mean_yen_converted':int(payer*1000),
            'zero_expenditure_pct_complement':float(D(100)-rate),
            'published_product_yen':float(product),'overall_minus_published_product_yen':float(mean-product),
            'overall_source':{'file':SOURCE1,'sheet':'2023kodo001','cell':f'{col}{row}','url':SOURCE_URL[SOURCE1],'sha256':sha(HERE/SOURCE1)},
            'spending_source':{'file':file,'sheet':sheet,'rate_cell':f'B{row}','spender_mean_cell':f'C{row}','url':SOURCE_URL[file],'sha256':sha(HERE/file)},
        })
    this={r['source_row']:r for r in records if r['school']==school}
    for total,parts in [(34,[35,40]),(35,[36,37,38,39]),(40,[41,42,43,44,45])]:
        check(f'{school} 全体平均加法 {total}',this[total]['overall_mean_yen']==sum(this[x]['overall_mean_yen'] for x in parts))

# 独立期待値は公式概要PDF表6・7の公立小学校/公立中学校「平均」から転記。
pdf_means = {
    '公立小学校':[13361,21976,75194,1954,6331,33708,66529,1325,36111],
    '公立中学校':[12884,21248,230385,7057,2867,24077,33990,4305,19205],
}
for school,expected in pdf_means.items():
    actual=[r['overall_mean_yen'] for r in records if r['school']==school and r['row_kind']=='activity']
    check(school+' PDF表6/7 全体平均9値',actual==expected)
# 公式概要PDF17頁表8-1/8-2の0円行と年間1円以上支出者平均。
for school,row,zero,payer in [('公立小学校',37,'61.4',57),('公立中学校',37,'72.7',78),('公立小学校',38,'61.0',193),('公立中学校',38,'34.0',349)]:
    rec=next(r for r in records if r['school']==school and r['source_row']==row)
    check(school+f' PDF表8 行{row}',D(str(rec['zero_expenditure_pct_complement']))==D(zero) and rec['spender_mean_thousand_yen']==payer)

activities=[r for r in records if r['row_kind']=='activity']
check('対象数・順序',len(records)==len(CONFIG)*len(LABELS) and [r['school'] for r in records[::len(LABELS)]]==[c[0] for c in CONFIG])
check('活動レベル18行',len(activities)==len(CONFIG)*len(LEAF_ROWS))
check('同じ学校・費目の別名重複なし',len({(r['school'],r['parent_activity'],r['activity']) for r in records})==len(records))
index={(r['school'],r['source_row']):r for r in records}
comparisons=[]
for row in LEAF_ROWS:
    a,b=index['公立小学校',row],index['公立中学校',row]
    comparisons.append({'activity':a['activity'],'parent_activity':a['parent_activity'],
        'order':['公立小学校','公立中学校'],'difference_direction':'公立中学校－公立小学校',
        'overall_mean_difference_yen':b['overall_mean_yen']-a['overall_mean_yen'],
        'spending_rate_difference_pp':float(D(str(b['spending_rate_pct']))-D(str(a['spending_rate_pct']))),
        'spender_mean_difference_thousand_yen':b['spender_mean_thousand_yen']-a['spender_mean_thousand_yen'],
        'overall_mean_ratio':str(D(b['overall_mean_yen'])/D(a['overall_mean_yen'])),
        'spending_rate_ratio':str(D(str(b['spending_rate_pct']))/D(str(a['spending_rate_pct']))),
        'spender_mean_ratio':str(D(b['spender_mean_thousand_yen'])/D(a['spender_mean_thousand_yen']))})

metadata={
    'title':'学校外活動費2023_活動別の支出率と平均','analysis_date':'2026-08-31','survey_fiscal_year':2023,
    'period_start':'2023-04-01','period_end':'2024-03-31','corrected_date':'2026-01-16',
    'school_order':[x[0] for x in CONFIG],'activity_order':[LABELS[r-34] for r in LEAF_ROWS],
    'denominator':{'overall_mean':'該当学校区分の全児童・生徒。支出ゼロを含む。公式復元推計値。','spending_rate':'該当学校区分の全児童・生徒のうち当該費目に支出した者の比率。回答した保護者・世帯数そのものの割合として独自に算出しない。','spender_mean':'該当費目の年間支出がある児童・生徒のみ。費目ごとに分母が違う。'},
    'units':{'overall_mean_yen':'円／子供一人当たり年額','spending_rate_pct':'％／公表1桁小数','spender_mean_thousand_yen':'千円／支出した子供一人当たり年額／公表整数','spender_mean_yen_converted':'公表千円を1000倍した単位換算値。円単位で測定された支出者平均ではない。'},
    'rounding':'全体平均は円、支出率は0.1％刻み、支出者平均は千円。公表3値の積に完全一致を要求しない。原資料で丸めの端数処理方式は今回未確認。差を残し、再構成値で公表平均を置換しない。丸め境界・標本誤差・信頼区間を混同しない。',
    'zero_expenditure':'100－公表支出率で計算した補数。利用しなかった者の割合ではない。表3の「0」「0.0」は単位未満、「－」は計数なし、「…」は対象外等。これらの記号を無差別に0へ置換しない。今回採用24行の3数値に特殊記号はない。',
    'sampling':[{'school':x[0],'valid_responses':x[6],'valid_response_rate_pct':x[7],'source':'調査の概要_20260116訂正.pdf 1頁・5頁','meaning':'全費目共通の有効回答数。活動別支出者標本数、母集団人数、有効標本サイズではない。'} for x in CONFIG],
    'field_labels':{'school':'学校区分','activity':'原表費目名','parent_activity':'原表上位費目名','row_kind':'活動／集計費目','overall_mean_yen':'全体平均（円）','spending_rate_pct':'支出率（％）','spender_mean_thousand_yen':'支出者平均額（千円）','zero_expenditure_pct_complement':'支出率の補数（％）','published_product_yen':'公表支出率×公表支出者平均の積（円）','overall_minus_published_product_yen':'公表全体平均－公表値積（円）'},
    'unresolved':['丸め端数の具体的規則','活動別支出者の標本人数','活動別支出率・平均の標準誤差と共分散','個別ウェイト・個票','非回答バイアスの定量評価','利用・参加率、単価、回数、継続月数、無料利用、支出ゼロの理由','所得別標準誤差の既知不整合（本分析には未使用）'],
}
save('学校外活動費2023_分析結果.json',{'metadata':metadata,'records':records,'activity_comparisons':comparisons})
main_rows=[38,41,42,43]
save('主要4活動8区分_確定データ.json',{'metadata':metadata,'records':[r for r in records if r['source_row'] in main_rows],'activity_comparisons':[r for r in comparisons if r['activity'] in [LABELS[i-34] for i in main_rows]]})
a,b=index['公立小学校',38],index['公立中学校',38]
p0,p1=D(str(a['spending_rate_pct']))/100,D(str(b['spending_rate_pct']))/100
m0,m1=D(a['spender_mean_thousand_yen'])*1000,D(b['spender_mean_thousand_yen'])*1000
rate_term=(p1-p0)*(m1+m0)/2
mean_term=(m1-m0)*(p1+p0)/2
official_difference=D(b['overall_mean_yen'])-D(a['overall_mean_yen'])
residual=official_difference-rate_term-mean_term
check('学習塾費の対称分解の恒等式',rate_term+mean_term==p1*m1-p0*m0)
check('学習塾費の全体平均差へ残差込み一致',rate_term+mean_term+residual==official_difference)
save('学習塾費の差_対称算術分解.json',{'activity':'学習塾費','order':['公立小学校','公立中学校'],'direction':'公立中学校－公立小学校','inputs':{'p0':str(p0),'p1':str(p1),'m0_yen':int(m0),'m1_yen':int(m1),'overall0_yen':a['overall_mean_yen'],'overall1_yen':b['overall_mean_yen']},'rate_difference_term_yen':int(rate_term),'spender_mean_difference_term_yen':int(mean_term),'published_product_difference_yen':int(rate_term+mean_term),'overall_difference_yen':int(official_difference),'reconstruction_residual_yen':int(residual),'formula':'(p1-p0)*(m1+m0)/2 + (m1-m0)*(p1+p0)/2 = p1*m1-p0*m0','rounding_note':'pは公表支出率を100で割り、mは公表千円を円へ換算。公表値の積の差と公式全体平均差との差を再構成残差として残す。残差をいずれかの項へ配賦しない。','interpretation':'異なる在籍者集団の公表点推定値の対称的な算術分解。価格効果・参加効果・因果寄与ではない。分解方法は一意ではなく、二つの順序の平均で交差項を等分する方式。','source_records':[a,b]})
csv_fields=['school','parent_activity','activity','overall_mean_yen','spending_rate_pct','spender_mean_thousand_yen','zero_expenditure_pct_complement','overall_source_url','overall_source_cell','spending_source_url','spending_rate_cell','spender_mean_cell']
with (HERE/'学校外活動費2023_活動別分析用.csv').open('w',newline='',encoding='utf-8-sig') as f:
    writer=csv.DictWriter(f,fieldnames=csv_fields);writer.writeheader()
    for r in activities:
        flat={k:r[k] for k in csv_fields if k in r}
        flat.update(overall_source_url=r['overall_source']['url'],overall_source_cell=r['overall_source']['cell'],spending_source_url=r['spending_source']['url'],spending_rate_cell=r['spending_source']['rate_cell'],spender_mean_cell=r['spending_source']['spender_mean_cell'])
        writer.writerow(flat)
with (HERE/'学校外活動費2023_活動別分析用.csv').open(encoding='utf-8-sig',newline='') as f: csv_rows=list(csv.DictReader(f))
check('CSV数値と原表の一致',len(csv_rows)==len(activities) and all(D(row['overall_mean_yen'])==D(r['overall_mean_yen']) and D(row['spending_rate_pct'])==D(str(r['spending_rate_pct'])) and D(row['spender_mean_thousand_yen'])==D(r['spender_mean_thousand_yen']) for row,r in zip(csv_rows,activities)))

manifest=[{'file':name,'absolute_path':str(HERE/name),'url':url,'sha256':sha(HERE/name),'bytes':(HERE/name).stat().st_size,'acquired_or_verified_date':'2026-08-31','publication_update_date':'2026-01-16' if name.endswith('.xlsx') else None} for name,url in SOURCE_URL.items()]
save('原資料一覧_SHA256.json',manifest)
if (HERE/'content.json').exists():
    content=json.loads((HERE/'content.json').read_text())
    check('本文日英7節',len(content['sections'])==SPEC['content_counts']['sections'] and all(set(p)=={'ja','en'} for s in content['sections'] for p in s['paragraphs']))
    check('主張3件・図予定3件・用途3件',all(len(content[k])==v for k,v in SPEC['content_counts'].items()))
    check('主担当確定の図stemと順序',[x['stem'] for x in content['figures']]==SPEC['figure_stems'])
    content['table']['rows']=[[r['school'],r['activity'] if r['activity']!='その他' else r['parent_activity']+'／'+r['activity'],r['overall_mean_yen'],r['spending_rate_pct'],r['spender_mean_thousand_yen']] for r in activities]
    save('content.json',content)
    draft=[]
    for lang,label in [('ja','日本語原稿案'),('en','English draft')]:
        draft.extend(['# '+label,'','# '+content['title'][lang],'',content['lead'][lang],''])
        for s in content['sections']:
            draft.extend(['## '+s['title'][lang],''])
            for p in s['paragraphs']: draft.extend([p[lang],''])
    (HERE/'記事原稿案_日英7節.md').write_text('\n'.join(draft))
    comp={'title':{'ja':'学校区分を選び、活動別の三つの指標を比べる','en':'Select a school group and compare three activity-level measures'},'intro':{'ja':'全体平均・支出率・支出者平均は分母と単位が異なります。支出率は利用率ではありません。','en':'Overall means, spending rates and spender means have different denominators and units. Spending rates are not participation rates.'},'selector':{'ja':'学校区分','en':'School group'},'defaultId':'公立小学校','period':{'ja':'2023年度／2026年1月16日訂正版','en':'FY2023 / corrected 16 January 2026'},'headers':[{'label':{'ja':'全体平均（円）','en':'Overall mean (JPY)'},'decimals':0},{'label':{'ja':'支出率（％）','en':'Spending rate (%)'},'decimals':1},{'label':{'ja':'支出者平均（千円）','en':'Spender mean (JPY 1,000)'},'decimals':0}],'options':[],'note':content['table']['note']}
    for school,school_en,*_ in CONFIG:
        comp['options'].append({'id':school,'label':{'ja':school,'en':school_en},'context':{'ja':'子供一人当たり年額。全体平均は支出ゼロを含み、支出者平均は当該費目への支出者のみ。','en':'Annual per-child figures. Overall means include zero expenditure; spender means include only positive spenders on that item.'},'rows':[{'label':{'ja':r['activity'] if r['activity']!='その他' else '補助学習費／その他','en':r['activity_en'] if r['activity']!='その他' else 'Other supplementary learning'},'values':[r['overall_mean_yen'],r['spending_rate_pct'],r['spender_mean_thousand_yen']]} for r in activities if r['school']==school]})
    save('comparison.json',comp)
names=[p.name for p in HERE.iterdir() if p.is_file()]
check('NFC/NFD別名重複なし',len({unicodedata.normalize('NFC',n) for n in names})==len(names))
save('検証結果.json',{'status':'PASS','scope':'数値抽出・算術・CSV・本文スキーマ。原表・PDF目視は目視検証記録.md。サイト統合・図生成・公開は未実施。','check_count':len(checks),'checked_numeric_cells':len(numeric_audit),'activity_rows':len(activities),'aggregate_rows':len(records)-len(activities),'source_cell_checks':numeric_audit,'checks':checks,'max_absolute_published_product_residual_yen':max(abs(r['overall_minus_published_product_yen']) for r in records),'rounding_note':'公表桁の違いを残した記述。残差ゼロや四捨五入規則の検証を意味しない。','source_hashes':manifest,'result_sha256':sha(HERE/'学校外活動費2023_分析結果.json'),'csv_sha256':sha(HERE/'学校外活動費2023_活動別分析用.csv')})
print(json.dumps({'status':'PASS','checks':len(checks),'numeric_cells':len(numeric_audit),'activity_rows':len(activities),'max_published_product_residual_yen':max(abs(r['overall_minus_published_product_yen']) for r in records),'comparisons':comparisons},ensure_ascii=False,indent=2))
