"""Reproduce checkout freight burden and unit-price variation from public datasets."""
import argparse
from collections import Counter, defaultdict
import csv
from datetime import date, datetime
from decimal import Decimal, ROUND_HALF_EVEN
from io import BytesIO, TextIOWrapper
import json
import re
from statistics import median
from xml.etree.ElementTree import iterparse
from zipfile import ZipFile


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


def csv_rows(book, name):
    with book.open(f'olist_{name}_dataset.csv') as stream:
        yield from csv.DictReader(TextIOWrapper(stream,encoding='utf-8-sig'))


def freight(path):
    start,end=datetime(2017,1,1),datetime(2018,8,1)
    with ZipFile(path) as book:
        orders={r['order_id'] for r in csv_rows(book,'orders') if r['order_status']=='delivered'
                and start<=datetime.fromisoformat(r['order_purchase_timestamp'])<end}
        totals=defaultdict(lambda:[Decimal(0),Decimal(0)])
        for row in csv_rows(book,'order_items'):
            order=row['order_id']
            if order in orders:
                totals[order][0]+=Decimal(row['price'])
                totals[order][1]+=Decimal(row['freight_value'])
    bands=defaultdict(list)
    for price,shipping in totals.values():
        if price<=0:
            continue
        band='under_50' if price<50 else '50_99' if price<100 else '100_199' if price<200 else '200_plus'
        for group in ('all',band):
            bands[group].append((price,shipping))
    return {k:{'orders':len(v),'zero':sum(s==0 for p,s in v),'at_least_quarter':sum(s*4>=p for p,s in v),
               'at_least_half':sum(s*2>=p for p,s in v),
               'ratio_median':float(median(s/p for p,s in v)*100),'price_median':float(median(p for p,s in v)),
               'freight_median':float(median(s for p,s in v))}
            for k,v in bands.items()}


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 unit_price(path):
    start=(date(2011,1,1)-ORIGIN).days
    end=(date(2011,12,1)-ORIGIN).days
    codes=defaultdict(Counter)
    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['F']=='UnitPrice'
        for row in rows:
            code=row['B'].strip().upper()
            if (row['H']!='United Kingdom' or row['A'].upper().startswith('C') or float(row['D'])<=0
                    or not start<=int(float(row['E']))<end or not SKU.fullmatch(code)):
                continue
            price=Decimal(row['F'])
            if price>0:
                pennies=int((price*100).quantize(Decimal('1'),rounding=ROUND_HALF_EVEN))
                codes[code][pennies]+=1
    result=defaultdict(Counter)
    for prices in codes.values():
        n=sum(prices.values())
        if n<20:
            continue
        mode=min(prices,key=lambda price:(-prices[price],price))
        group='20_49' if n<50 else '50_199' if n<200 else '200_plus'
        for k in ('all',group):
            c=result[k]
            c['codes']+=1
            c['lines']+=n
            c['one_price']+=len(prices)==1
            c['three_prices']+=len(prices)>=3
            c['modal']+=prices[mode]
            for price,count in prices.items():
                if price<mode:
                    c['below']+=count
                    c['below_10']+=count*(10*price<=9*mode)
                if price>mode:
                    c['above']+=count
                    c['above_10']+=count*(10*price>=11*mode)
    return {k:dict(v) for k,v in result.items()}


if __name__=='__main__':
    parser=argparse.ArgumentParser(description=__doc__)
    parser.add_argument('method',choices=['freight','unit_price'])
    parser.add_argument('archive')
    args=parser.parse_args()
    print(json.dumps({'freight':freight,'unit_price':unit_price}[args.method](args.archive),indent=2))
