"""Summarize repeat-item and count-variance metrics from the UCI Online Retail ZIP."""
from collections import Counter, defaultdict
from datetime import date
from hashlib import sha256
from io import BytesIO
import json
from pathlib import Path
import re
from statistics import mean, variance
import sys
from xml.etree.ElementTree import iterparse
from zipfile import ZipFile

NS='{http://schemas.openxmlformats.org/spreadsheetml/2006/main}'
ORIGIN=date(1899,12,30)
FIRST=(date(2011,1,1)-ORIGIN).days
LAST=(date(2011,4,30)-ORIGIN).days
SKU=re.compile(r'[0-9]{5}[A-Z]?')


def workbook_rows(book):
    with book.open('xl/sharedStrings.xml') as stream:
        strings=[''.join(e.itertext()) for _,e in iterparse(stream) if e.tag==NS+'si']
    with book.open('xl/worksheets/sheet1.xml') as stream:
        for event,e in iterparse(stream,events=('start','end')):
            if event=='start' and e.tag==NS+'sheetData':
                parent=e
            if event=='end' and e.tag==NS+'row':
                values={}
                for cell in e:
                    v=cell.findtext(NS+'v')
                    if v is not None:
                        values[cell.get('r')[0]]=strings[int(v)] if cell.get('t')=='s' else v
                yield values
                parent.remove(e)


def classify(days):
    first,*later=sorted(days)
    return {str(limit):{'returns':any(day<=first+limit for day in later),
                       'same_code':any(day<=first+limit and days[first]&days[day] for day in later)}
            for limit in (30,90,180)}


def analyze(path):
    toy={1:{'11111'},30:{'22222'},50:{'11111'}}
    assert classify(toy)['30']=={'returns':True,'same_code':False}
    assert classify(toy)['90']=={'returns':True,'same_code':True}
    histories=defaultdict(lambda:defaultdict(set))
    lines=0
    with ZipFile(path) as source, ZipFile(BytesIO(source.read('Online Retail.xlsx'))) as book:
        rows=workbook_rows(book)
        header=next(rows)
        assert header['B']=='StockCode' and header['G']=='CustomerID'
        for r in rows:
            lines+=1
            if 'G' not in r or r['A'].upper().startswith('C') or float(r['D'])<=0 or float(r['F'])<=0:
                continue
            codes=histories[r['G']][int(float(r['E']))]
            code=r['B'].strip().upper()
            if SKU.fullmatch(code):
                codes.add(code)
    assert lines==541909
    cohort=[d for d in histories.values() if FIRST<=min(d)<=LAST]
    assert len(cohort)==1549
    time={k:Counter() for k in ('30','90','180')}
    first_code_count=0
    repeat_days=[]
    for days in cohort:
        first,*later=sorted(days)
        repeat_days.append(sum(day<=first+90 for day in later))
        if not days[first]:
            continue
        first_code_count+=1
        for window, result in classify(days).items():
            time[window].update({k:int(v) for k,v in result.items()})
    counts=Counter(repeat_days)
    assert sum(counts.values())==1549 and sum(n for day,n in counts.items() if day)>0
    assert sum(n for day,n in counts.items() if day)==657
    return {
        'sha256':sha256(Path(path).read_bytes()).hexdigest(),'rows':lines,
        'cohort':len(cohort),'cohort_first_product_code':first_code_count,
        'same_code_windows':time,
        'repeat_days_90':{'total':sum(repeat_days),'mean':mean(repeat_days),'variance':variance(repeat_days),
                          'distribution':dict(sorted(counts.items()))}
    }


if __name__=='__main__':
    print(json.dumps(analyze(sys.argv[1]),indent=2))
