"""Synthetic local request boundary; caller supplies a trusted account, not HTTP input."""
import sqlite3


def connect(path):
    db = sqlite3.connect(path, isolation_level=None, timeout=3)
    db.execute("PRAGMA foreign_keys=ON")
    return db


def create_schema(db):
    db.executescript("""
        CREATE TABLE requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account TEXT NOT NULL,
            submission_key TEXT NOT NULL,
            body TEXT NOT NULL,
            UNIQUE(account, submission_key)
        );
        CREATE TABLE notifications (
            request_id INTEGER PRIMARY KEY REFERENCES requests(id) ON DELETE CASCADE,
            state TEXT NOT NULL DEFAULT 'pending'
        );
    """)


def submit(db, account, key, body, checkpoint=lambda: None):
    for label, value, limit in [("account", account, 64), ("key", key, 64), ("body", body, 1000)]:
        if not isinstance(value, str) or not value.strip() or len(value) > limit:
            raise ValueError(label)
    db.execute("BEGIN IMMEDIATE")
    try:
        prior = db.execute(
            "SELECT id, body FROM requests WHERE account=? AND submission_key=?",
            (account, key),
        ).fetchone()
        if prior:
            if prior[1] != body:
                raise ValueError("submission key reused with different body")
            db.execute("COMMIT")
            return prior[0]
        reference = db.execute(
            "INSERT INTO requests(account, submission_key, body) VALUES (?, ?, ?)",
            (account, key, body),
        ).lastrowid
        checkpoint()
        db.execute("INSERT INTO notifications(request_id) VALUES (?)", (reference,))
        db.execute("COMMIT")
        return reference
    except BaseException:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise


def lookup(db, account, key):
    return db.execute(
        "SELECT id, body FROM requests WHERE account=? AND submission_key=?",
        (account, key),
    ).fetchone()


def retrieve(db, account, reference):
    return db.execute(
        "SELECT id, body FROM requests WHERE account=? AND id=?",
        (account, reference),
    ).fetchone()