"""Local SQLite exercise. Does not call external APIs or send messages."""
import sqlite3
import sys


def record(connection, event_id, task):
    # The receipt and task are committed together or both rolled back.
    with connection:
        previous = connection.execute("SELECT task FROM events WHERE id = ?", (event_id,)).fetchone()
        if previous:
            if previous[0] != task:
                raise ValueError("Conflict: same event ID with a different task")
            return "duplicate"
        connection.execute("INSERT INTO events VALUES (?, ?)", (event_id, task))
        connection.execute("INSERT INTO tasks VALUES (?, ?)", (event_id, task))
    return "created"


def connect(path):
    connection = sqlite3.connect(path)
    connection.execute("CREATE TABLE IF NOT EXISTS events (id TEXT PRIMARY KEY, task TEXT NOT NULL)")
    connection.execute("CREATE TABLE IF NOT EXISTS tasks (event_id TEXT PRIMARY KEY, title TEXT NOT NULL)")
    return connection


if __name__ == "__main__":
    connection = connect(sys.argv[1] if len(sys.argv) > 1 else "exercise.db")
    try:
        for event in [("E-1", "Review draft"), ("E-1", "Review draft"), ("E-2", "Check source")]:
            print(event[0], record(connection, *event))
        print("tasks:", connection.execute("SELECT COUNT(*) FROM tasks").fetchone()[0])
    finally:
        connection.close()