"""SQLite teaching core: sequential transition checks, no HTTP or authentication server."""
import json
import sqlite3
import tempfile
from pathlib import Path

class Jobs:
    def __init__(self, path):
        self.db = sqlite3.connect(path)
        self.db.execute("CREATE TABLE IF NOT EXISTS jobs (id TEXT PRIMARY KEY, tenant TEXT NOT NULL, state TEXT NOT NULL, result TEXT)")
        self.db.commit()

    def create(self, job, tenant):
        with self.db:
            self.db.execute("INSERT INTO jobs VALUES (?, ?, 'queued', NULL)", (job, tenant))

    def change(self, job, tenant, old, new, result=None):
        allowed = {("queued", "running"), ("running", "succeeded"), ("running", "failed"), ("cancel_requested", "canceled")}
        if (old, new) not in allowed:
            raise ValueError("Illegal transition")
        with self.db:
            cursor = self.db.execute("UPDATE jobs SET state=?, result=? WHERE id=? AND tenant=? AND state=?", (new, result, job, tenant, old))
        return cursor.rowcount == 1

    def read(self, job, tenant):
        row = self.db.execute("SELECT state,result FROM jobs WHERE id=? AND tenant=?", (job, tenant)).fetchone()
        return dict(state=row[0], result=row[1]) if row else None

    def cancel(self, job, tenant):
        with self.db:
            self.db.execute("UPDATE jobs SET state=CASE state WHEN 'queued' THEN 'canceled' ELSE 'cancel_requested' END WHERE id=? AND tenant=? AND state IN ('queued','running')", (job, tenant))
        return self.read(job, tenant)

def run():
    observations = {}
    with tempfile.TemporaryDirectory(prefix="async-job-example-") as tmp:
        path = Path(tmp)/"jobs.db"
        jobs = Jobs(path)
        jobs.create("job-a", "tenant-a")
        jobs.db.close()
        jobs = Jobs(path)
        observations["reopened_queued_job"] = jobs.read("job-a", "tenant-a")
        assert observations["reopened_queued_job"]["state"] == "queued"
        assert jobs.change("job-a", "tenant-a", "queued", "running")
        observations["second_claim"] = jobs.change("job-a", "tenant-a", "queued", "running")
        assert observations["second_claim"] is False
        jobs.cancel("job-a", "tenant-a")
        observations["success_after_cancel_requested"] = jobs.change("job-a", "tenant-a", "running", "succeeded", "report-a")
        assert observations["success_after_cancel_requested"] is False
        assert jobs.change("job-a", "tenant-a", "cancel_requested", "canceled")
        observations["cancel_first_final"] = jobs.read("job-a", "tenant-a")
        jobs.create("job-b", "tenant-a")
        assert jobs.change("job-b", "tenant-a", "queued", "running")
        assert jobs.change("job-b", "tenant-a", "running", "succeeded", "report-b")
        observations["success_first_final"] = jobs.cancel("job-b", "tenant-a")
        assert observations["success_first_final"] == dict(state="succeeded", result="report-b")
        observations["different_tenant_lookup"] = jobs.read("job-b", "tenant-b")
        assert observations["different_tenant_lookup"] is None
        jobs.create("job-c", "tenant-a")
        assert jobs.cancel("job-c", "tenant-a")["state"] == "canceled"
        observations["claim_after_queued_cancel"] = jobs.change("job-c", "tenant-a", "queued", "running")
        assert observations["claim_after_queued_cancel"] is False
        jobs.db.close()
    Path("checkpoints.json").write_text(json.dumps(dict(scope="Sequential local SQLite state core; trusted tenant fixture; no HTTP, power-loss or distributed concurrency test",checkpoints=observations),indent=2)+"\n")
    print(json.dumps(observations,indent=2))

if __name__ == "__main__":
    run()