"""Copy public Chinook and derive employee closure without changing original rows."""
from chapter22_support import *
import sqlite3
SOURCE=LAB/'data/Chinook_Sqlite.sqlite';DB=PROJECT/'data/Chinook.sqlite'
PROJECT.mkdir(exist_ok=True);DB.parent.mkdir(exist_ok=True)
before=sha(SOURCE)
source=sqlite3.connect(SOURCE.as_uri()+'?mode=ro',uri=True)
people=source.execute('SELECT EmployeeId,ReportsTo,FirstName,LastName,Title FROM Employee ORDER BY EmployeeId').fetchall()
parents={r[0]:r[1] for r in people}
def ancestors(node):
    seen=set();distance=0
    while node is not None:
        assert node in parents,'Orphan employee'
        assert node not in seen,'Employee cycle'
        seen.add(node);yield node,distance;node=parents[node];distance+=1
closure=[(ancestor,node,d) for node in parents for ancestor,d in ancestors(node)]
assert len(closure)==len(set((a,b) for a,b,d in closure))
assert sum(d==0 for a,b,d in closure)==len(people)==8
if DB.exists():
    manifest=load(PROJECT/'fixture.json');assert manifest['databaseSha256']==sha(DB) and manifest['sourceSha256']==before
else:
    dest=sqlite3.connect(DB);source.backup(dest)
    dest.executescript('CREATE TABLE EmployeeClosure(AncestorId INTEGER NOT NULL,EmployeeId INTEGER NOT NULL,distance INTEGER NOT NULL CHECK(distance>=0),PRIMARY KEY(AncestorId,EmployeeId));CREATE INDEX idx_EmployeeClosure_child ON EmployeeClosure(EmployeeId);')
    dest.executemany('INSERT INTO EmployeeClosure VALUES(?,?,?)',closure);dest.commit()
    for table in ['Employee','Customer','Invoice']:
        assert source.execute('SELECT * FROM '+table).fetchall()==dest.execute('SELECT * FROM '+table).fetchall()
    dest.close()
    save(PROJECT/'fixture.json',{'source':'Chinook v1.4.5 public teaching copy','sourceSha256':before,'databaseSha256':sha(DB),'addedTable':'EmployeeClosure','employeeCount':len(people),'closureRowCount':len(closure),'closureRows':closure,'employees':[{'id':r[0],'parentId':r[1],'name':r[2]+' '+r[3],'title':r[4]} for r in people],'meaning':'Current Customer.SupportRepId assignment; not historical sales attribution.'})
source.close();assert sha(SOURCE)==before
print(json.dumps({'database':str(DB),'employees':len(people),'closureRows':len(closure),'sourceUnchanged':True}))