"""Build an independent, reproducible calendar teaching fixture; never edit Chinook."""
from pathlib import Path
from datetime import date, datetime, timedelta
from decimal import Decimal
import hashlib, json, sqlite3

LAB = Path(__file__).resolve().parents[1]
PROJECT = LAB / 'chapter-18'
SOURCE = LAB / 'data/Chinook_Sqlite.sqlite'
DB = PROJECT / 'data/calendar-teaching.sqlite'
CUTOFF = date(2012, 11, 16)  # exclusive; a simulated reporting cutoff, not source freshness

def digest(path):
    return hashlib.sha256(path.read_bytes()).hexdigest()

def prepare():
    PROJECT.mkdir(exist_ok=True)
    DB.parent.mkdir(exist_ok=True)
    before = digest(SOURCE)
    source = sqlite3.connect(SOURCE.as_uri() + '?mode=ro', uri=True)
    invoices = source.execute('select InvoiceId, InvoiceDate, Total from Invoice order by InvoiceId').fetchall()
    source.close()
    # Refuse to replace a potentially open or independently changed database.
    if DB.exists():
        manifest = json.loads((PROJECT / 'fixture.json').read_text(encoding='utf-8'))
        assert manifest['sourceSha256'] == before and manifest['databaseSha256'] == digest(DB)
        print('Existing fixture verified:', DB)
        return
    with sqlite3.connect(DB) as con:
        con.executescript('''
        CREATE TABLE TeachingInvoice (
          InvoiceId INTEGER PRIMARY KEY, InvoiceDate DATETIME NOT NULL,
          InvoiceDay DATE NOT NULL, InvoiceMonth TEXT NOT NULL, InvoiceDayOfMonth INTEGER NOT NULL,
          LoadedAt DATETIME NOT NULL, LoadedMonth TEXT NOT NULL, Total DECIMAL(12,2) NOT NULL);
        CREATE TABLE ReportingCalendar (
          CalendarDate DATE PRIMARY KEY, YearMonth TEXT NOT NULL, DayOfMonth INTEGER NOT NULL,
          WeekStart DATE NOT NULL, IsoWeekYear INTEGER NOT NULL, IsoWeek INTEGER NOT NULL,
          IsWorkday INTEGER NOT NULL, WorkdayIndex INTEGER,
          DataComplete INTEGER NOT NULL);
        ''')
        records = []
        for key, timestamp, amount in invoices:
            event = datetime.fromisoformat(timestamp)
            loaded = event + timedelta(days=2)
            records.append((key, timestamp, event.date().isoformat(), event.strftime('%Y-%m'), event.day,
                            loaded.strftime('%Y-%m-%d %H:%M:%S'), loaded.strftime('%Y-%m'), str(amount)))
        con.executemany('insert into TeachingInvoice values (?,?,?,?,?,?,?,?)', records)
        days = []
        d = date(2011, 12, 26)
        while d < date(2013, 1, 7):
            ym = d.strftime('%Y-%m')
            working = int(d.weekday() < 5)  # teaching calendar, NOT an official holiday calendar
            # Count from the actual first day of the month, even if this fixture starts mid-month.
            month_start = d.replace(day=1)
            workday_index = sum((month_start+timedelta(days=i)).weekday() < 5 for i in range(d.day))
            iso = d.isocalendar()
            days.append((d.isoformat(), ym, d.day, (d-timedelta(days=d.weekday())).isoformat(),
                         iso.year, iso.week, working, workday_index if working else None, int(d < CUTOFF)))
            d += timedelta(days=1)
        con.executemany('insert into ReportingCalendar values (?,?,?,?,?,?,?,?,?)', days)
        con.executescript('CREATE INDEX ix_invoice_day ON TeachingInvoice(InvoiceDay);')
    assert digest(SOURCE) == before
    manifest = {
        'source': str(SOURCE), 'sourceSha256': before, 'database': str(DB),
        'databaseSha256': digest(DB), 'invoiceRows': len(records), 'calendarRows': len(days),
        'reportCutoffExclusive': CUTOFF.isoformat(),
        'originalFields': ['InvoiceId', 'InvoiceDate', 'Total'],
        'teachingExtensions': {
            'LoadedAt': 'InvoiceDate + 2 natural days; synthetic technical ingestion time, NOT payment time',
            'calendar': 'Complete civil dates; Monday-Friday workdays; ignores statutory holidays',
            'DataComplete': 'Teaching declaration: complete before 2012-11-16, unconfirmed on/after it; not inferred from rows'
        },
        'originalDataUnchanged': True
    }
    (PROJECT / 'fixture.json').write_text(json.dumps(manifest, ensure_ascii=False, indent=2), encoding='utf-8')
    print(json.dumps(manifest, ensure_ascii=True, indent=2))

if __name__ == '__main__':
    prepare()