# -*- coding: utf-8 -*-
"""NIQ 맥주 스캐너(MALT, 가정용) → 월별 국산/수입 정돈 + 월별 2재화 AIDS (한치록 완전재현)
 입력: 'NIQ) MALT Sales_JAN17-AUG25.xlsx' (3시트, 브랜드×월 Sales Value(MM)·Sales Volume(10LIT))
 분류: MANUFACTURER에 'IMP' 포함 → 수입, 그 외 → 국산
 산출: 공개데이터/닐슨/beer_niq_dom_imp_monthly.csv + 월별 AIDS 탄력성
"""
import sys,io,json,re
sys.stdout=io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')
import numpy as np, pandas as pd, openpyxl, statsmodels.formula.api as smf
import os
XL='C:/Temp/주류세/NIQ) MALT Sales_JAN17-AUG25.xlsx'
OUT='C:/Temp/주류세/공개데이터/닐슨'; os.makedirs(OUT,exist_ok=True)
MON={m:i+1 for i,m in enumerate(['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'])}
def ym(lbl):
    m=re.match(r'([A-Z]{3})(\d{2})',str(lbl).strip())
    if not m: return None
    return (2000+int(m.group(2)))*100+MON[m.group(1)]

wb=openpyxl.load_workbook(XL,read_only=True,data_only=True)
recs=[]
for ws in wb.worksheets:
    rows=list(ws.iter_rows(values_only=True))
    metric_row=rows[1]; head_row=rows[2]
    colmap={}
    for j,(mt,hd) in enumerate(zip(metric_row,head_row)):
        y=ym(hd)
        if y and mt:
            met='value' if 'Value' in str(mt) else ('volume' if 'Volume' in str(mt) else None)
            if met: colmap[j]=(met,y)
    for r in rows[3:]:
        man=r[2]; brand=r[3]
        if not man: continue
        imp='IMP' in str(man).upper()
        for j,(met,y) in colmap.items():
            v=r[j]
            if v in (None,'NA','',' '): continue
            try: v=float(v)
            except: continue
            recs.append((y,'수입' if imp else '국산',met,v))
wb.close()
df=pd.DataFrame(recs,columns=['ym','grp','met','v'])
agg=df.groupby(['ym','grp','met']).v.sum().unstack('met').reset_index()
# 단위: value=백만원(MM), volume=10리터 → 금액(원)=value*1e6, 리터=volume*10
agg['sales']=agg['value']*1e6; agg['liters']=agg['volume']*10
agg['price']=agg['sales']/agg['liters']   # 원/리터
wide=agg.pivot(index='ym',columns='grp',values=['sales','liters','price'])
wide.columns=[f'{a}_{b}' for a,b in wide.columns]; wide=wide.reset_index().dropna()
wide['X']=wide['sales_국산']+wide['sales_수입']
wide['w_imp']=wide['sales_수입']/wide['X']; wide['w_dom']=wide['sales_국산']/wide['X']
wide.to_csv(OUT+'/beer_niq_dom_imp_monthly.csv',index=False,encoding='utf-8-sig')
print('월별 관측:',len(wide),'기간',int(wide.ym.min()),'~',int(wide.ym.max()))
print('수입 지출몫 w_imp: 처음 %.3f / 끝 %.3f / 평균 %.3f'%(wide.w_imp.iloc[0],wide.w_imp.iloc[-1],wide.w_imp.mean()))
print('가격(원/L) 국산 평균 %.0f · 수입 평균 %.0f'%(wide['price_국산'].mean(),wide['price_수입'].mean()))

# ---------- 월별 2재화 LA-AIDS ----------
d=wide.copy()
d['lnP']=d.w_imp*np.log(d['price_수입'])+d.w_dom*np.log(d['price_국산'])
d['ln_XP']=np.log(d.X)-d.lnP
d['ln_rel']=np.log(d['price_수입']/d['price_국산'])
d['month']=d.ym%100; d['t']=np.arange(len(d))
d['covid']=(d.ym//100).isin([2020,2021]).astype(int)
def aids(df,ctrl):
    f='w_imp ~ ln_rel + ln_XP'+ctrl
    m=smf.ols(f,data=df).fit(cov_type='HAC',cov_kwds={'maxlags':6})
    g=m.params['ln_rel']; b=m.params['ln_XP']; wi=df.w_imp.mean(); wd=df.w_dom.mean()
    return m,dict(gamma=round(g,3),tg=round(m.tvalues['ln_rel'],2),beta=round(b,3),
      e_imp=round(-1+(g-b*wi)/wi,2), e_dom=round(-1+(g+b*wd)/wd,2),
      cross_imp_on_dom=round((-g+b*wi)/wd,2), cross_dom_on_imp=round((-g-b*wd)/wi,2),
      eta_imp=round(1+b/wi,2), eta_dom=round(1-b/wd,2), wi=round(wi,3), n=int(m.nobs))
res={}
for lab,ctrl in [('기본(계절)',' + C(month)'),('계절+추세+코로나',' + C(month) + t + covid')]:
    m,r=aids(d,ctrl); res[lab]=r
    print(f'\n===== NIQ 월별 맥주 AIDS [{lab}] n={r["n"]} =====')
    print(f'  γ={r["gamma"]}(t={r["tg"]}) β={r["beta"]}')
    print(f'  자체가격탄력성: 수입 {r["e_imp"]:+} | 국산 {r["e_dom"]:+}')
    print(f'  교차: 국산가↑→수입 {r["cross_imp_on_dom"]:+} · 수입가↑→국산 {r["cross_dom_on_imp"]:+}')
    print(f'  지출탄력성: 수입 {r["eta_imp"]:+} | 국산 {r["eta_dom"]:+} (수입몫 {r["wi"]})')
json.dump(res,open(OUT+'/beer_niq_aids.json','w',encoding='utf-8'),ensure_ascii=False,indent=2)
print('\n[대조] 원논문 수입프리미엄 -4.8~-6.0/국산코어 -1.8~-1.9 · 국세접합 수입 -3.6/국산 -1.1')
print('저장: 닐슨/beer_niq_dom_imp_monthly.csv, beer_niq_aids.json')