"""Independently verify every exported count against SQLite's JSON functions."""
import csv
import hashlib
import json
import sqlite3
from pathlib import Path

directory = Path(__file__).resolve().parent
snapshot = (directory / 'source-snapshot.json').read_bytes()
summary = json.loads((directory / 'summary.json').read_text())
db = sqlite3.connect(':memory:')
db.row_factory = sqlite3.Row
try:
    db.execute("SELECT json_extract('{}', '$')")
except sqlite3.OperationalError:
    raise SystemExit('The optional independent verifier requires SQLite with JSON functions enabled.')
db.execute('CREATE TABLE raw (document TEXT NOT NULL)')
db.execute('INSERT INTO raw VALUES (?)', (snapshot.decode(),))
db.executescript("""
    CREATE VIEW sample AS
        SELECT a.value AS document, json_extract(a.value, '$.protocol') AS protocol
        FROM raw, json_each(raw.document, '$.samples') AS a;
    CREATE VIEW report AS
        SELECT b.value AS document, s.protocol, json_extract(b.value, '$.reference') AS reference
        FROM sample AS s, json_each(s.document, '$.reports') AS b;
    CREATE VIEW result AS
        SELECT c.value AS document, r.protocol, r.reference
        FROM report AS r, json_each(r.document, '$.results') AS c;
    CREATE VIEW result_fields AS
        SELECT protocol, reference, json_extract(document, '$.measurand') AS measurand,
               json_extract(document, '$.value') AS value, json_extract(document, '$.unit') AS unit,
               json_extract(document, '$.comparator') AS comparator, json_extract(document, '$.verdict') AS verdict,
               json_extract(document, '$.uncertainty') AS uncertainty, json_extract(document, '$.method') AS method,
               json_type(document, '$.value') IN ('integer', 'real') AS numeric
        FROM result;
    CREATE VIEW result_status AS
        SELECT *, CASE
            WHEN numeric AND comparator IN ('eq','lt','gt') THEN 'numeric_' || comparator
            WHEN numeric THEN 'numeric_other_comparator'
            WHEN verdict = 'not_detected' THEN 'not_detected_without_number'
            WHEN verdict = 'pass' THEN 'pass_without_number'
            WHEN verdict IS NOT NULL AND trim(verdict) <> '' THEN 'other_verdict_without_number'
            ELSE 'no_numeric_value_or_verdict' END AS status
        FROM result_fields;
""")

def one(sql, parameters=()):
    return db.execute(sql, parameters).fetchone()[0]

def csv_rows(filename):
    with (directory / filename).open(newline='', encoding='utf-8') as handle:
        return [{key: int(value) if value.isdigit() else value for key, value in row.items()}
                for row in csv.DictReader(handle)]

assert hashlib.sha256(snapshot).hexdigest() == summary['source']['sha256']
assert len(snapshot) == summary['source']['bytes']
cohort = summary['cohort']
assert [cohort[f'{level}s'] for level in ('sample', 'report', 'result')] == [28, 104, 216]
for level in ('sample', 'report', 'result'):
    assert cohort[f'{level}s'] == one(f'SELECT count(*) FROM {level}')
assert one('SELECT count(DISTINCT protocol) FROM sample') == cohort['samples']
assert one('SELECT count(DISTINCT reference) FROM report') == cohort['reports']
assert one("SELECT count(DISTINCT json_extract(document, '$.report_url')) FROM report") == cohort['distinct_report_urls']
assert one("SELECT count(DISTINCT json_extract(document, '$.vendor_slug')) FROM sample") == cohort['distinct_vendor_slugs']
assert one("SELECT max(n) FROM (SELECT count(*) AS n FROM sample GROUP BY json_extract(document, '$.vendor_slug'))") == cohort['largest_vendor_sample_count']
assert one("SELECT count(DISTINCT json_extract(document, '$.laboratory')) FROM sample") == cohort['distinct_laboratory_names']
for level, key, array in [('sample', 'reports_per_sample_distribution', 'reports'),
                          ('report', 'results_per_report_distribution', 'results')]:
    groups = db.execute(f"SELECT json_array_length(document, '$.{array}'), count(*) FROM {level} GROUP BY 1")
    assert dict((str(size), rows) for size, rows in groups) == cohort[key]

for level, field in [('sample', 'analysed_at'), ('report', 'tested_at')]:
    dates = db.execute(f"SELECT min(json_extract(document, '$.{field}')), max(json_extract(document, '$.{field}')) FROM {level}").fetchone()
    assert {'first': dates[0], 'last': dates[1]} == cohort[f'{level}_{field}']
top_dates = db.execute("SELECT json_extract(document, '$.first_analysed'), json_extract(document, '$.last_analysed') FROM raw").fetchone()
assert dict(zip(('first', 'last'), top_dates)) == cohort['source_top_level_analysed_range']
assert (cohort['source_top_level_analysed_range'] == cohort['sample_analysed_at']) == cohort['source_range_matches_sample_analysed_dates']
assert (cohort['source_top_level_analysed_range'] == cohort['report_tested_at']) == cohort['source_range_matches_report_tested_dates']

availability = csv_rows('field-availability.csv')
assert availability == summary['field_availability']
assert len(availability) == 31
for row in availability:
    sql = f"""
        SELECT count(*),
               sum(CASE WHEN kind IS NULL OR kind = 'null' OR
                     (kind = 'text' AND trim(value) = '') OR
                     (kind IN ('object','array') AND NOT EXISTS (SELECT 1 FROM json_each(extracted.value)))
                   THEN 0 ELSE 1 END),
               sum(CASE WHEN kind = 'null' THEN 1 ELSE 0 END),
               sum(CASE WHEN kind IS NULL THEN 1 ELSE 0 END),
               sum(CASE WHEN (kind = 'text' AND trim(value) = '') OR
                     (kind IN ('object','array') AND NOT EXISTS (SELECT 1 FROM json_each(extracted.value)))
                   THEN 1 ELSE 0 END),
               sum(CASE WHEN kind IN ('integer','real') AND value = 0 THEN 1 ELSE 0 END)
        FROM (SELECT json_type(document, ?) AS kind, json_extract(document, ?) AS value FROM {row['level']}) AS extracted
    """
    key = '$.' + row['field']
    independent = db.execute(sql, (key, key)).fetchone()
    expected = tuple(row[field] for field in ('records', 'populated', 'json_null', 'missing_key', 'empty_string_or_container', 'numeric_zero'))
    assert tuple(independent) == expected, (row, tuple(independent))

statuses = csv_rows('result-status.csv')
assert statuses == summary['result_statuses']
assert [row['records'] for row in statuses] == [68, 13, 1, 0, 108, 22, 0, 4]
for row in statuses:
    assert one('SELECT count(*) FROM result_status WHERE status = ?', (row['status'],)) == row['records']
    assert one('SELECT count(*) FROM result') == row['denominator']

coverage = csv_rows('measurand-coverage.csv')
assert coverage == summary['measurand_coverage']
for row in coverage:
    measurand = None if row['measurand'] == 'JSON null' else row['measurand']
    rows = db.execute("""
        SELECT count(DISTINCT protocol), count(DISTINCT reference), count(*),
               sum(numeric), sum(value IS NULL), sum(unit IS NOT NULL), sum(uncertainty IS NOT NULL)
        FROM result_status WHERE measurand IS ?
    """, (measurand,)).fetchone()
    assert tuple(rows) == tuple(row[key] for key in ('samples_with_category', 'reports_with_category', 'results',
                                                    'numeric_value', 'json_null_value', 'unit_populated', 'uncertainty_populated'))
    for status in statuses:
        label = status['status']
        assert one('SELECT count(*) FROM result_status WHERE measurand IS ? AND status = ?', (measurand, label)) == row[label]

numeric = summary['numeric_values']
counts = db.execute("""
    SELECT count(*), count(DISTINCT protocol), count(DISTINCT reference), sum(unit IS NOT NULL), sum(value = 0)
    FROM result_fields WHERE numeric
""").fetchone()
assert tuple(counts) == tuple(numeric[key] for key in ('results', 'samples_with_any', 'reports_with_any',
                                                       'with_populated_unit', 'explicit_zero'))
assert dict(db.execute('SELECT comparator, count(*) FROM result_fields WHERE numeric GROUP BY comparator')) == numeric['comparators']
assert dict(db.execute('SELECT comparator, count(*) FROM result_fields GROUP BY comparator')) == summary['comparators_all_results']
assert one("""
    SELECT count(*) FROM result_fields AS x JOIN report AS r USING (reference)
    WHERE x.method <> (json_extract(r.document, '$.method'))
""") == summary['result_method_differs_from_report_method_when_populated']
assert one("""
    SELECT count(*) FROM report AS r JOIN sample AS s USING (protocol)
    WHERE (json_extract(r.document, '$.tested_at')) <> (json_extract(s.document, '$.analysed_at'))
""") == summary['reports_tested_on_different_day_from_sample_analysed']
print('Independent SQLite verification passed for the snapshot, cohort, dates and every aggregate CSV cell.')