"""Build only the new teaching database; refuse to overwrite an existing one."""
from time_series_support import *
import sqlite3
from datetime import date, timedelta

def prepare():
    db=PROJECT/'data/time-business.sqlite'; manifest=PROJECT/'fixture.json'
    if db.exists():
        prior=json.loads(manifest.read_text(encoding='utf-8'))
        assert hashlib.sha256(db.read_bytes()).hexdigest()==prior['sha256'], 'Existing fixture changed; inspect instead of replacing.'
        return db
    db.parent.mkdir(parents=True,exist_ok=True)
    c=sqlite3.connect(db)
    c.executescript('''
      CREATE TABLE StockEntity(EntityId TEXT PRIMARY KEY, Label TEXT NOT NULL);
      CREATE TABLE StockSnapshot(RowId INTEGER PRIMARY KEY, EntityId TEXT NOT NULL, ValidDate DATE NOT NULL, Quantity INTEGER NOT NULL, UNIQUE(EntityId,ValidDate));
      CREATE TABLE StockMovement(RowId INTEGER PRIMARY KEY, EntityId TEXT NOT NULL, EventDate DATE NOT NULL, Delta INTEGER NOT NULL);
      CREATE TABLE StockDaily(RowId INTEGER PRIMARY KEY, EntityId TEXT NOT NULL, CalendarDate DATE NOT NULL, Quantity INTEGER, UNIQUE(EntityId,CalendarDate));
      CREATE TABLE WorkTicket(TicketId INTEGER PRIMARY KEY, StartedAt DATETIME, FinishedAt DATETIME, AnalysisAt DATETIME NOT NULL);
      CREATE TABLE Purchase(InvoiceId INTEGER PRIMARY KEY, CustomerId INTEGER NOT NULL, InvoiceDate DATETIME NOT NULL, InvoiceMonth TEXT NOT NULL, MonthIndex INTEGER NOT NULL, Amount NUMERIC NOT NULL);
    ''')
    c.executemany('INSERT INTO StockEntity VALUES (?,?)',[(x,x+'商品') for x in 'ABCD'])
    snapshots=[('A','2024-01-01',100),('A','2024-01-03',80),('A','2024-01-05',120),('B','2024-01-01',50),('B','2024-01-04',40),('C','2024-01-04',30)]
    c.executemany('INSERT INTO StockSnapshot(EntityId,ValidDate,Quantity) VALUES (?,?,?)',snapshots)
    c.executemany('INSERT INTO StockMovement(EntityId,EventDate,Delta) VALUES (?,?,?)',[('A','2024-01-03',-20),('A','2024-01-05',40),('B','2024-01-04',-10)])
    for entity in 'ABCD':
        for n in range(5):
            day=(date(2024,1,1)+timedelta(days=n)).isoformat()
            prior=[r for r in snapshots if r[0]==entity and r[1]<=day]
            qty=max(prior,key=lambda r:r[1])[2] if prior else None
            c.execute('INSERT INTO StockDaily(EntityId,CalendarDate,Quantity) VALUES (?,?,?)',(entity,day,qty))
    tickets=[(1,'2024-02-01 08:00:00','2024-02-01 10:00:00'),(2,'2024-01-31 20:00:00','2024-02-01 08:00:00'),(3,'2024-02-01 12:00:00','2024-02-02 12:00:00'),(4,'2024-02-01 10:00:00','2024-02-02 11:00:00'),(5,'2024-02-01 08:00:00',None),(6,'2024-02-02 10:00:00',None),(7,'2024-02-01 14:00:00','2024-02-02 14:00:00'),(8,'2024-02-01 12:00:00','2024-02-01 11:00:00'),(9,None,None),(10,'2024-02-03 00:00:00',None),(11,'2024-01-30 12:00:00','2024-02-01 12:00:00')]
    c.executemany('INSERT INTO WorkTicket VALUES (?,?,?,?)',[(*r,'2024-02-02 12:00:00') for r in tickets])
    src=sqlite3.connect('file:'+str(LAB/'data/Chinook_Sqlite.sqlite').replace('\\','/')+'?mode=ro',uri=True)
    purchases=src.execute('SELECT InvoiceId,CustomerId,InvoiceDate,Total FROM Invoice').fetchall();src.close()
    c.executemany('INSERT INTO Purchase VALUES (?,?,?,?,?,?)',[(i,u,d,d[:7],int(d[:4])*12+int(d[5:7])-1,a) for i,u,d,a in purchases])
    c.commit()
    counts={t:c.execute('SELECT count(*) FROM '+t).fetchone()[0] for t in ['StockEntity','StockSnapshot','StockMovement','StockDaily','WorkTicket','Purchase']}
    c.close()
    save(manifest,{'sha256':hashlib.sha256(db.read_bytes()).hexdigest(),'tables':counts,'source':'Stock and tickets are synthetic teaching records. Purchase is a read-only Chinook v1.4.5 Invoice copy plus month attributes. StockDaily is preparation-layer carry-forward, not a native engine feature.','ticketAnalysisAt':'2024-02-02 12:00:00','stockDailyRange':'[2024-01-01,2024-01-06)','purchaseObservationEndExclusive':'2014-01-01','purchaseLastObservedDate':'2013-12-22','purchaseCompleteness':'The teaching case assumes the supplied sample is complete for calendar months through 2013-12; production completeness needs separate evidence.'})
    return db

if __name__=='__main__': print(prepare())