"""Load and inspect the FCTE connected sample with Python's standard library."""
from pathlib import Path
import argparse, csv, hashlib, json, sqlite3

def run(folder, citizen):
    manifest = json.loads((folder / 'manifest.json').read_text(encoding='utf-8'))
    for item in manifest['files']:
        path = folder / item['path']
        if hashlib.sha256(path.read_bytes()).hexdigest() != item['sha256']:
            raise ValueError('Package file changed: ' + item['path'])
    sample = json.loads((folder / 'sample.json').read_text(encoding='utf-8'))
    if citizen not in sample['selectedIds']:
        raise ValueError('Choose one of: ' + ', '.join(sample['selectedIds']))
    # Build in memory first. Source CSVs and JSON remain untouched.
    db = sqlite3.connect(':memory:')
    db.row_factory = sqlite3.Row
    db.executescript((folder / 'schema.sql').read_text(encoding='utf-8'))
    counts = {}
    for table, fields in sample['tableFields'].items():
        with (folder / (table + '.csv')).open(encoding='utf-8', newline='') as f:
            reader = csv.DictReader(f)
            if reader.fieldnames != fields:
                raise ValueError('Unexpected columns in ' + table)
            rows = [[row[k] if row[k] != '' else None for k in fields] for row in reader]
        placeholders = ','.join('?' for _ in fields)
        db.executemany('INSERT INTO ' + table + ' VALUES (' + placeholders + ')', rows)
        counts[table] = db.execute('SELECT COUNT(*) FROM ' + table).fetchone()[0]
    queries = (folder / 'queries.sql').read_text(encoding='utf-8').split(';')
    queries = [q.strip() for q in queries if q.strip()]
    summary = [dict(r) for r in db.execute(queries[0])]
    timeline = [dict(r) for r in db.execute(queries[1], {'citizen': citizen})]
    chronology = [dict(r) for r in db.execute(queries[2])]
    foreign_keys = [list(r) for r in db.execute('PRAGMA foreign_key_check')]
    checks = {'sampleId': sample['sampleId'], 'websiteRelease': sample['release'],
              'counts': counts, 'sourceFilesVerified': len(manifest['files']),
              'foreignKeyViolations': foreign_keys, 'personDateExceptions': chronology,
              'scope': 'Local normalized sample only; no city database or empirical validation.'}
    if foreign_keys or chronology or counts != sample['counts']:
        raise ValueError('Sample validation failed: ' + json.dumps(checks))
    # Export an exact, reproducible record-coverage result without multiplying joins.
    with (folder / 'resident-coverage.csv').open('w', encoding='utf-8', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=list(summary[0]))
        writer.writeheader(); writer.writerows(summary)
    with (folder / 'expected/resident-coverage.csv').open(encoding='utf-8', newline='') as f:
        expected = list(csv.DictReader(f))
    actual = [{key: str(value) for key, value in row.items()} for row in summary]
    if actual != expected:
        raise ValueError('Record coverage differs from the packaged expected result.')
    checks['expectedCoverageMatches'] = True
    db.commit()
    with sqlite3.connect(folder / 'connected.sqlite') as dest:
        db.backup(dest)
    with (folder / 'timeline.csv').open('w', encoding='utf-8', newline='') as f:
        writer = csv.DictWriter(f, fieldnames=list(timeline[0])); writer.writeheader(); writer.writerows(timeline)
    (folder / 'checks.json').write_text(json.dumps(checks, indent=2) + '\n', encoding='utf-8')
    print('FCTE Nairobi connected sample | release ' + sample['release'])
    for row in summary:
        print(row['citizen_id'], '|', row['name'], '|', row['enrollments'], 'enrollments |', row['contracts'], 'contracts |', row['visits'], 'visits')
    print('\nSelected timeline:', citizen)
    for row in timeline:
        print(row['event_date'], '|', row['domain'], '|', row['event_label'])
    print('\nNo foreign-key violations or person-date exceptions in this sample.')
    print('Created connected.sqlite, resident-coverage.csv, timeline.csv, checks.json. See README.md for scope.')
    return checks

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument('--citizen', default='3414569')
    args = parser.parse_args()
    try:
        run(Path(__file__).resolve().parent, args.citizen)
    except (ValueError, OSError, sqlite3.Error) as error:
        parser.exit(1, str(error) + '\n')