import sqlite3
import sys

assert sys.version_info[:3] == (3, 12, 12), "runtime"
assert sqlite3.sqlite_version == "3.51.2", "sqlite"
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE exports (id TEXT PRIMARY KEY, account TEXT, "
           "accepted_at INTEGER, state TEXT)")


def old_write(job, account, accepted_at, state="queued"):
    db.execute("INSERT INTO exports (id, account, accepted_at, state) "
               "VALUES (?, ?, ?, ?)", (job, account, accepted_at, state))


def old_read(job):
    return db.execute("SELECT state FROM exports WHERE id = ?", (job,)).fetchone()[0]


old_write("legacy", "cedar", -10, "complete")
db.execute("ALTER TABLE exports ADD COLUMN finished_at INTEGER")
assert old_read("legacy") == "complete", "old_reader"


def request(job, authenticated_account, requested_account, accepted_at):
    # Synthetic identity context; not an authentication implementation.
    if authenticated_account != requested_account:
        raise PermissionError("account")
    old_write(job, requested_account, accepted_at)


before = db.total_changes
try:
    request("denied", "cedar", "harbour", 0)
except PermissionError:
    pass
else:
    raise AssertionError("deny_cross_account")
assert db.total_changes == before, "deny_before_write"
for job, accepted_at in [("ready", 0), ("failed", 1), ("lost", 2), ("young", 15)]:
    request(job, "cedar", "cedar", accepted_at)
assert db.execute("SELECT finished_at FROM exports WHERE id='young'").fetchone() == (None,), "old_writer"
db.execute("UPDATE exports SET state='complete', finished_at=5 WHERE id='ready'")
db.execute("UPDATE exports SET state='failed', finished_at=4 WHERE id='failed'")
assert old_read("ready") == "complete", "new_write_old_read"


def outcome(accepted_at, state, finished_at, now, deadline=10):
    if accepted_at + deadline > now:
        return "not_due"
    if state == "failed":
        return "failed"
    if state == "complete":
        if finished_at is None:
            return "unknown"
        return "on_time" if accepted_at <= finished_at <= accepted_at + deadline else "late"
    return "overdue"


rows = db.execute("SELECT id, accepted_at, state, finished_at FROM exports ORDER BY id").fetchall()
observed = {job: outcome(accepted, state, finished, now=20) for job, accepted, state, finished in rows}
assert observed == {"failed": "failed", "legacy": "unknown", "lost": "overdue",
                    "ready": "on_time", "young": "not_due"}, "outcome_partition"
due = [value for value in observed.values() if value != "not_due"]
assert (due.count("on_time"), len(due)) == (1, 4), "completion_denominator"
assert outcome(0, "complete", 11, now=20) == "late", "late_completion"
assert outcome(0, "complete", -1, now=20) != "on_time", "invalid_timestamp"
assert outcome(10, "queued", None, now=20) == "overdue", "deadline_boundary"
assert db.execute("SELECT COUNT(*) FROM exports WHERE id='denied'").fetchone()[0] == 0, "denied_absent"
print("Python 3.12.12; SQLite 3.51.2")
print("due=4 on_time=1 failed=1 overdue=1 unknown=1 not_due=1")
print("old reader/writer compatible; cross-account write denied")
db.close()