"""Create public Chinook file fixtures and a clearly synthetic target fixture."""
from files_support import *
from decimal import Decimal
import sqlite3,csv

source=LAB/'data/Chinook_Sqlite.sqlite'
protected=[source,LAB/'runtime/work/runtime-bundle-registry.json',LAB/'runtime/work/runtime-datasource-registry.json',LAB/'runtime/launcher-start-current.json',*list((LAB/'composition/models').rglob('*'))]
save(PROJECT/'protected-before.json',{str(p):sha(p) for p in protected if p.is_file()})
with sqlite3.connect(source.as_uri()+'?mode=ro',uri=True) as db:
    rows=[{'invoice_id':str(iid),'customer_code':str(cid).zfill(5),'invoice_date':date,'billing_country':country,'invoice_amount':str(Decimal(str(amount)))} for iid,cid,date,country,amount in db.execute('select InvoiceId,CustomerId,InvoiceDate,BillingCountry,Total from Invoice order by InvoiceId') if '2012-01-01'<=date<'2013-01-01']
headers=['账单编号','客户编号','账单日期','账单国家','账单金额']
assert len(rows)==83 and sum(Decimal(r['invoice_amount']) for r in rows)==Decimal('477.53')
save(PROJECT/'inputs/source-records.json',{'headers':headers,'rows':rows,'source':'Chinook v1.4.5 Invoice; 2012 calendar year','sourceSha256':sha(source),'customerCodeRule':'CustomerId formatted as five-digit text for this teaching export; not an original Chinook code','amountUnit':'unchanged sample unit; currency unspecified','dateMeaning':'InvoiceDate, no timezone conversion, fixed values not formulas'})
with (PROJECT/'inputs/ChinookInvoices2012.csv').open('w',encoding='utf-8-sig',newline='') as out:
    writer=csv.writer(out);writer.writerow(headers)
    writer.writerows([[r['invoice_id'],r['customer_code'],r['invoice_date'],r['billing_country'],r['invoice_amount']] for r in rows])
targets=[{'customer_code':'00013','period':'2012','target_amount':'20.00'},{'customer_code':'00026','period':'2012','target_amount':'30.00'},{'customer_code':'00034','period':'2012','target_amount':'25.00'},{'customer_code':'00999','period':'2012','target_amount':'10.00'}]
save(PROJECT/'inputs/teaching-targets.json',{'source':'synthetic teaching targets, NOT Chinook original business records','unit':'same sample amount unit as invoice export','rows':targets})
save(PROJECT/'fixture.json',{'sourceDatabase':str(source),'sourceSha256':sha(source),'rowCount':83,'customers':len({r['customer_code'] for r in rows}),'amount':'477.53','namespace':NS,'datasource':'files-sqlite','database':str(PROJECT/'data/files.sqlite'),'modelsDirectory':str(PROJECT/'models'),'sourceWorkbook':'ChinookInvoices2012.xlsx'})
print(json.dumps({'rows':83,'customers':47,'amount':'477.53'},ensure_ascii=True))