"""SQLite checkpoint/outbox exercise; no Microsoft Graph or CRM calls."""
import json
import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory


def initialize(db):
    db.executescript('''
      CREATE TABLE checkpoints(folder TEXT PRIMARY KEY, token TEXT NOT NULL, phase TEXT NOT NULL);
      CREATE TABLE outbox(message_key TEXT PRIMARY KEY, payload TEXT NOT NULL, confirmed INTEGER NOT NULL DEFAULT 0);
      INSERT INTO checkpoints VALUES('ADV-INBOX', 'START', 'catchup');
    ''')


def accept_page(db, folder, request_token, items, next_token=None, delta_token=None, fail=False):
    if (next_token is None) == (delta_token is None):
        raise ValueError('Exactly one continuation kind required')
    with db:
        current = db.execute('SELECT token FROM checkpoints WHERE folder=?', (folder,)).fetchone()
        if current is None or current[0] != request_token:
            raise ValueError('Unknown folder or unexpected request token')
        for item in items:
            # Fictitious stable identity, not a Graph notification/event-ID algorithm.
            key = json.dumps([item['tenant'], item['mailbox'], item['message']], separators=(',', ':'))
            db.execute('INSERT OR IGNORE INTO outbox(message_key,payload) VALUES(?,?)',
                       (key, json.dumps(item, sort_keys=True)))
        if fail:
            raise RuntimeError('Injected failure before checkpoint update')
        db.execute('UPDATE checkpoints SET token=?,phase=? WHERE folder=?',
                   (next_token or delta_token, 'catchup' if next_token else 'source_round_complete', folder))


def snapshot(db):
    return (db.execute('SELECT * FROM checkpoints ORDER BY folder').fetchall(),
            db.execute('SELECT * FROM outbox ORDER BY message_key').fetchall())


def expect_error(kind, operation):
    try:
        operation()
    except kind:
        return
    raise AssertionError('Expected exception not raised')


def main():
    with TemporaryDirectory() as directory:
        path = Path(directory) / 'mail.sqlite'
        db = sqlite3.connect(path)
        initialize(db)
        a = {'tenant':'T-A', 'mailbox':'ADV', 'message':'IMM-A'}
        b = {'tenant':'T-A', 'mailbox':'ADV', 'message':'IMM-B'}
        accept_page(db, 'ADV-INBOX', 'START', [a], next_token='OPAQUE-NEXT-1')
        assert db.execute('SELECT token FROM checkpoints').fetchone()[0] == 'OPAQUE-NEXT-1'
        assert db.execute('SELECT COUNT(*) FROM outbox').fetchone()[0] == 1
        before = snapshot(db)
        expect_error(RuntimeError, lambda: accept_page(db,'ADV-INBOX','OPAQUE-NEXT-1',[a,b],delta_token='OPAQUE-DELTA-1',fail=True))
        assert snapshot(db) == before
        db.close()
        db = sqlite3.connect(path)
        assert snapshot(db) == before
        accept_page(db,'ADV-INBOX','OPAQUE-NEXT-1',[a,b],delta_token='OPAQUE-DELTA-1')
        assert db.execute('SELECT COUNT(*) FROM outbox').fetchone()[0] == 2
        assert db.execute('SELECT phase FROM checkpoints').fetchone()[0] == 'source_round_complete'
        assert db.execute('SELECT COUNT(*) FROM outbox WHERE confirmed=0').fetchone()[0] == 2
        after = snapshot(db)
        expect_error(ValueError, lambda: accept_page(db,'ADV-INBOX','START',[a],next_token='OPAQUE-NEXT-1'))
        assert snapshot(db) == after
        expect_error(ValueError, lambda: accept_page(db,'ANOTHER-FOLDER','OPAQUE-DELTA-1',[b],delta_token='OPAQUE-DELTA-2'))
        assert snapshot(db) == after
        expect_error(ValueError, lambda: accept_page(db,'ADV-INBOX','OPAQUE-DELTA-1',[b],next_token='N',delta_token='D'))
        assert snapshot(db) == after
        with db:
            db.execute("UPDATE checkpoints SET phase='resync_required' WHERE folder='ADV-INBOX'")
        assert db.execute('SELECT token FROM checkpoints').fetchone()[0] == 'OPAQUE-DELTA-1'
        assert db.execute('SELECT COUNT(*) FROM outbox').fetchone()[0] == 2
        assert db.execute('SELECT COUNT(*) FROM outbox WHERE confirmed=1').fetchone()[0] == 0
        db.close()
        print('8 scenarios; 17 checks passed. Two pending local link requests; no CRM object created.')


if __name__ == '__main__':
    main()