use std::sync::atomic::{AtomicU64, Ordering};
use std::time::{Duration, SystemTime, UNIX_EPOCH};

use remora_deletion::{
    DeleteIdentity, DeletionConfig, DeletionError, DeletionProblemClass, DeletionService,
    DeletionState, DrainOutcome, LifecycleMutation, TargetRegistry,
};
use remora_domain::{BucketName, OpaqueId, StorageScope, TargetPrefix, TargetStoreId};
use remora_durable::{PoolConfig, connect};
use remora_identity::Principal;
use remora_result::{PublicationInput, ResultInventory, ResultMember};
use remora_schema::migrate as migrate_schema;
use remora_target::{
    CreateObjectRequest, ObjectIdentity, TargetError, TargetStore, TargetStoreConfig, hash_bytes,
};
use serde_json::json;
use sqlx::PgPool;
use time::OffsetDateTime;

static NEXT_ID: AtomicU64 = AtomicU64::new(0);

struct Harness {
    pool: PgPool,
    target: TargetStore,
    registry: TargetRegistry,
    service: DeletionService,
    owner: Principal,
}

impl Harness {
    async fn new() -> Option<Self> {
        let Some(database_url) = required_env("DATABASE_URL") else {
            if std::env::var("REMORA_DELETION_REQUIRE").is_ok() {
                panic!("DATABASE_URL is required when REMORA_DELETION_REQUIRE is set");
            }
            return None;
        };
        let target_config = match TargetStoreConfig::from_env() {
            Ok(config) => config,
            Err(error) => {
                if std::env::var("REMORA_DELETION_REQUIRE").is_ok() {
                    panic!("target fixture is required: {error}");
                }
                return None;
            }
        };
        let pool = connect(
            &database_url,
            PoolConfig {
                max_connections: 16,
                acquire_timeout: Duration::from_secs(10),
                idle_timeout: Some(Duration::from_secs(30)),
            },
        )
        .await
        .expect("connect postgres");
        migrate_schema(&pool).await.expect("schema migration");
        sqlx::query("TRUNCATE TABLE jobs CASCADE")
            .execute(&pool)
            .await
            .expect("reset fixture data");
        let target = TargetStore::connect(target_config).expect("connect target");
        let registry = TargetRegistry::new([target.clone()]);
        let service = DeletionService::new(
            pool.clone(),
            registry.clone(),
            DeletionConfig {
                claim_ttl: Duration::from_secs(2),
            },
        )
        .expect("deletion service");
        let owner = principal(unique_id("owner"), &target, true, false);
        Some(Self {
            pool,
            target,
            registry,
            service,
            owner,
        })
    }

    async fn queued_job(&self) -> OpaqueId {
        insert_job(&self.pool, &self.owner.id, &self.target)
            .await
            .expect("insert job")
    }

    async fn terminal_job(&self, phase: &str) -> OpaqueId {
        let job_id = self.queued_job().await;
        set_terminal_without_result(&self.pool, &job_id, phase)
            .await
            .expect("set terminal");
        job_id
    }

    async fn succeeded_job(
        &self,
        members: usize,
    ) -> (OpaqueId, PublicationInput, Vec<ObjectIdentity>) {
        let job_id = self.queued_job().await;
        let attempt_id = insert_attempt(&self.pool, &job_id, "committed")
            .await
            .expect("insert attempt");
        let inventory = ResultInventory::new(self.pool.clone());
        inventory
            .create(&attempt_id)
            .await
            .expect("create inventory");
        let mut identities = Vec::with_capacity(members);
        for index in 0..members {
            let member_id = unique_id("member");
            let body = format!("member-{index}").into_bytes();
            let stored = self
                .target
                .put_create_only(
                    &CreateObjectRequest::for_attempt_object(&attempt_id, &member_id, None),
                    body,
                )
                .await
                .expect("store member");
            identities.push(stored.identity.clone());
            inventory
                .add_member(
                    &attempt_id,
                    &ResultMember::new(
                        member_id,
                        &format!("members/{index:05}.bin"),
                        stored.size,
                        stored.identity,
                        stored.content_sha256,
                    )
                    .expect("result member"),
                )
                .await
                .expect("add member");
        }
        let committed = inventory
            .create_manifest(&attempt_id, &unique_id("manifest"), &self.target)
            .await
            .expect("create manifest");
        set_succeeded(&self.pool, &job_id, &committed)
            .await
            .expect("publish result");
        (job_id, committed, identities)
    }
}

#[tokio::test]
async fn soft_delete_restore_and_authorization_are_terminal_and_idempotent() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let active = harness.queued_job().await;
    assert!(matches!(
        harness.service.soft_delete(&harness.owner, &active).await,
        Err(DeletionError::NotTerminal)
    ));

    let job_id = harness.terminal_job("failed").await;
    let unauthorized = principal(harness.owner.id.clone(), &harness.target, false, false);
    assert!(matches!(
        harness.service.soft_delete(&unauthorized, &job_id).await,
        Err(DeletionError::NotFound)
    ));
    let other_owner = principal(unique_id("other"), &harness.target, true, false);
    assert!(matches!(
        harness.service.soft_delete(&other_owner, &job_id).await,
        Err(DeletionError::NotFound)
    ));

    assert_eq!(
        harness
            .service
            .soft_delete(&harness.owner, &job_id)
            .await
            .unwrap(),
        LifecycleMutation::Changed
    );
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "soft_deleted");
    assert_eq!(
        harness
            .service
            .soft_delete(&harness.owner, &job_id)
            .await
            .unwrap(),
        LifecycleMutation::Unchanged
    );
    assert_eq!(
        harness
            .service
            .restore(&harness.owner, &job_id)
            .await
            .unwrap(),
        LifecycleMutation::Changed
    );
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "visible");
    assert_eq!(
        harness
            .service
            .restore(&harness.owner, &job_id)
            .await
            .unwrap(),
        LifecycleMutation::Unchanged
    );
}

#[tokio::test]
async fn concurrent_hard_delete_is_irreversible_exact_and_manifest_last() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let (job_id, committed, mut members) = harness.succeeded_job(8).await;
    harness
        .target
        .delete_exact(&members.remove(0))
        .await
        .expect("pre-delete member");

    let mut callers = Vec::new();
    for _ in 0..8 {
        let service = harness.service.clone();
        let principal = harness.owner.clone();
        let job_id = job_id.clone();
        callers.push(tokio::spawn(async move {
            service.hard_delete(&principal, &job_id).await.unwrap()
        }));
    }
    let mut outcomes = Vec::new();
    for caller in callers {
        outcomes.push(caller.await.unwrap());
    }
    let operation_id = outcomes[0].operation.id.clone();
    assert!(
        outcomes
            .iter()
            .all(|outcome| outcome.operation.id == operation_id)
    );
    assert_eq!(
        outcomes
            .iter()
            .filter(|outcome| outcome.mutation == LifecycleMutation::Changed)
            .count(),
        1
    );
    assert!(matches!(
        harness.service.restore(&harness.owner, &job_id).await,
        Err(DeletionError::HardDeleteStarted)
    ));
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleting");

    drain_concurrently(&harness.service, &operation_id, 8).await;
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleted");
    let phase: String = sqlx::query_scalar("SELECT phase FROM jobs WHERE job_id = $1")
        .bind(job_id.as_str())
        .fetch_one(&harness.pool)
        .await
        .unwrap();
    assert_eq!(phase, "succeeded");
    for identity in &members {
        assert!(matches!(
            harness.target.read_exact(identity).await,
            Err(TargetError::NotFound)
        ));
    }
    assert!(matches!(
        harness
            .target
            .read_exact(&committed.manifest.identity)
            .await,
        Err(TargetError::NotFound)
    ));
    let ordering: bool = sqlx::query_scalar(
        "SELECT manifest.completed_at >= max(member.completed_at)
         FROM deletion_items manifest
         JOIN deletion_items member ON member.operation_id = manifest.operation_id
         WHERE manifest.operation_id = $1
           AND manifest.kind = 'manifest' AND member.kind <> 'manifest'
         GROUP BY manifest.completed_at",
    )
    .bind(operation_id.as_str())
    .fetch_one(&harness.pool)
    .await
    .unwrap();
    assert!(ordering);

    let replay = harness
        .service
        .hard_delete(&harness.owner, &job_id)
        .await
        .unwrap();
    assert_eq!(replay.mutation, LifecycleMutation::Unchanged);
    assert_eq!(replay.operation.id, operation_id);
    assert_eq!(replay.operation.state, DeletionState::Completed);

    let lost_scope = principal(harness.owner.id.clone(), &harness.target, false, false);
    assert!(matches!(
        harness.service.operation(&lost_scope, &operation_id).await,
        Err(DeletionError::NotFound)
    ));
    let administrator = principal(unique_id("admin"), &harness.target, false, true);
    assert_eq!(
        harness
            .service
            .operation(&administrator, &operation_id)
            .await
            .unwrap()
            .job_id,
        job_id
    );
    let tombstone: (
        String,
        String,
        String,
        String,
        Option<String>,
        Option<OffsetDateTime>,
    ) = sqlx::query_as(
        "SELECT owner_id, target_store, target_bucket, phase,
                    deletion_operation_id, hard_delete_started_at
             FROM jobs WHERE job_id = $1",
    )
    .bind(job_id.as_str())
    .fetch_one(&harness.pool)
    .await
    .unwrap();
    assert_eq!(tombstone.0, harness.owner.id.as_str());
    assert_eq!(tombstone.1, harness.target.config().target_store_id);
    assert_eq!(tombstone.2, harness.target.config().bucket);
    assert_eq!(tombstone.3, "succeeded");
    assert_eq!(tombstone.4.as_deref(), Some(operation_id.as_str()));
    assert!(tombstone.5.is_some());
}

#[tokio::test]
async fn blocked_target_configuration_resumes_without_restoring_visibility() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let (job_id, _, _) = harness.succeeded_job(2).await;
    let outcome = harness
        .service
        .hard_delete(&harness.owner, &job_id)
        .await
        .unwrap();
    let removed = harness
        .registry
        .remove(
            &harness.target.config().target_store_id,
            &harness.target.config().bucket,
        )
        .expect("remove target");
    assert_eq!(
        harness
            .service
            .process_next(&outcome.operation.id)
            .await
            .unwrap(),
        DrainOutcome::Blocked
    );
    let blocked = harness
        .service
        .operation(&harness.owner, &outcome.operation.id)
        .await
        .unwrap();
    assert_eq!(blocked.state, DeletionState::Blocked);
    assert_eq!(
        blocked.problem.unwrap().class,
        DeletionProblemClass::Configuration
    );
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleting");
    assert!(matches!(
        harness.service.restore(&harness.owner, &job_id).await,
        Err(DeletionError::HardDeleteStarted)
    ));

    let mut unavailable_config = removed.config().clone();
    unavailable_config.endpoint = "http://127.0.0.1:1".into();
    harness.registry.register(
        TargetStore::connect(unavailable_config).expect("connect lazy unavailable target"),
    );
    assert_eq!(
        harness
            .service
            .process_next(&outcome.operation.id)
            .await
            .unwrap(),
        DrainOutcome::Blocked
    );
    let unavailable = harness
        .service
        .operation(&harness.owner, &outcome.operation.id)
        .await
        .unwrap();
    assert_eq!(
        unavailable.problem.unwrap().class,
        DeletionProblemClass::TargetUnavailable
    );
    harness.registry.register(removed);
    drain(&harness.service, &outcome.operation.id).await;
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleted");
}

#[tokio::test]
async fn expired_claim_recovers_after_effect_without_duplicate_authority() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let (job_id, _, _) = harness.succeeded_job(1).await;
    let operation = harness
        .service
        .hard_delete(&harness.owner, &job_id)
        .await
        .unwrap()
        .operation;
    let short_lease = DeletionService::new(
        harness.pool.clone(),
        harness.registry.clone(),
        DeletionConfig {
            claim_ttl: Duration::from_millis(80),
        },
    )
    .unwrap();
    let claim = short_lease
        .claim_next(&operation.id)
        .await
        .unwrap()
        .expect("claim");
    match &claim.identity {
        DeleteIdentity::Object(identity) => {
            harness.target.delete_exact(identity).await.unwrap();
        }
        other => panic!("member must precede manifest: {other:?}"),
    }
    tokio::time::sleep(Duration::from_millis(120)).await;
    let restarted = DeletionService::new(
        harness.pool.clone(),
        harness.registry.clone(),
        DeletionConfig {
            claim_ttl: Duration::from_millis(80),
        },
    )
    .unwrap();
    drain(&restarted, &operation.id).await;
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleted");
}

#[tokio::test]
async fn corrupt_committed_inventory_fails_before_tombstone_or_obligation() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let (job_id, committed, _) = harness.succeeded_job(2).await;
    sqlx::query(
        "UPDATE result_inventory_members SET size = size + 1
         WHERE attempt_id = $1 AND member_id = (
             SELECT member_id FROM result_inventory_members
             WHERE attempt_id = $1 ORDER BY member_id LIMIT 1
         )",
    )
    .bind(committed.attempt_id.as_str())
    .execute(&harness.pool)
    .await
    .unwrap();
    assert!(matches!(
        harness.service.hard_delete(&harness.owner, &job_id).await,
        Err(DeletionError::CorruptInventory)
    ));
    let state: (String, Option<String>) =
        sqlx::query_as("SELECT lifecycle, deletion_operation_id FROM jobs WHERE job_id = $1")
            .bind(job_id.as_str())
            .fetch_one(&harness.pool)
            .await
            .unwrap();
    assert_eq!(state, ("visible".into(), None));
}

#[tokio::test]
async fn poisoned_member_identity_cannot_delete_another_jobs_object() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let (first_job, first_result, _) = harness.succeeded_job(1).await;
    let (_, second_result, second_members) = harness.succeeded_job(1).await;
    let protected = &second_members[0];
    sqlx::query(
        "UPDATE result_inventory_members
         SET object_key = $2, object_version_id = $3
         WHERE attempt_id = $1",
    )
    .bind(first_result.attempt_id.as_str())
    .bind(&protected.key)
    .bind(&protected.version_id)
    .execute(&harness.pool)
    .await
    .unwrap();

    assert!(matches!(
        harness
            .service
            .hard_delete(&harness.owner, &first_job)
            .await,
        Err(DeletionError::CorruptInventory)
    ));
    assert_eq!(
        harness.target.read_exact(protected).await.unwrap(),
        b"member-0".as_slice()
    );
    assert_eq!(job_lifecycle(&harness.pool, &first_job).await, "visible");
    assert!(
        harness
            .target
            .read_exact(&second_result.manifest.identity)
            .await
            .is_ok()
    );
}

#[tokio::test]
async fn unresolved_write_intent_and_multipart_are_exact_recoverable_work() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let job_id = harness.queued_job().await;
    let attempt_id = insert_attempt(&harness.pool, &job_id, "stopped")
        .await
        .unwrap();
    let object_id = unique_id("object");
    let request = CreateObjectRequest::for_attempt_object(&attempt_id, &object_id, None);
    let object = harness
        .target
        .put_create_only(&request, b"orphan".as_slice())
        .await
        .unwrap();
    insert_cleanup(
        &harness.pool,
        &attempt_id,
        "write_intent",
        &object.identity.key,
        None,
    )
    .await;
    let multipart_request =
        CreateObjectRequest::for_attempt_object(&attempt_id, &unique_id("multipart"), None);
    let multipart = harness
        .target
        .begin_multipart(&multipart_request)
        .await
        .unwrap()
        .identity()
        .clone();
    insert_cleanup(
        &harness.pool,
        &attempt_id,
        "multipart",
        &multipart.key,
        Some(&multipart.upload_id),
    )
    .await;
    set_terminal_without_result(&harness.pool, &job_id, "failed")
        .await
        .unwrap();

    let operation = harness
        .service
        .hard_delete(&harness.owner, &job_id)
        .await
        .unwrap()
        .operation;
    drain(&harness.service, &operation.id).await;
    assert!(matches!(
        harness.target.read_exact(&object.identity).await,
        Err(TargetError::NotFound)
    ));
    let discovered = harness
        .target
        .discover_staging_write(&multipart.key)
        .await
        .unwrap();
    assert!(discovered.multiparts.is_empty());
    assert_eq!(job_lifecycle(&harness.pool, &job_id).await, "hard_deleted");
}

#[tokio::test]
async fn hard_delete_materializes_large_inventory_inside_postgres() {
    let Some(harness) = Harness::new().await else {
        return;
    };
    let job_id = harness.queued_job().await;
    let attempt_id = insert_attempt(&harness.pool, &job_id, "committed")
        .await
        .unwrap();
    let manifest_id = unique_id("manifest");
    let manifest_key = remora_target::attempt_manifest_key(&attempt_id, &manifest_id);
    let digest = hash_bytes(b"").as_str().to_owned();
    sqlx::query(
        "INSERT INTO result_inventories (
            attempt_id, state, manifest_schema_version, manifest_id, manifest_store,
            manifest_bucket, manifest_key, manifest_sha256, manifest_size,
            member_count, total_bytes, staging_origin_attempt_id, target_prefix
         ) VALUES ($1, 'sealed', 1, $2, $3, $4, $5, $6, 0, 5000, 5000, $1, '')",
    )
    .bind(attempt_id.as_str())
    .bind(manifest_id.as_str())
    .bind(&harness.target.config().target_store_id)
    .bind(&harness.target.config().bucket)
    .bind(&manifest_key)
    .bind(&digest)
    .execute(&harness.pool)
    .await
    .unwrap();
    sqlx::query(
        "INSERT INTO result_inventory_members (
            attempt_id, member_id, path, size, object_store, object_bucket,
            object_key, object_sha256
         )
         SELECT $1, 'member-' || lpad(value::text, 8, '0'),
                'bulk/' || lpad(value::text, 8, '0'), 1, $2, $3,
                '_remora/a/' || $1 || '/o/object-' || lpad(value::text, 8, '0'), $4
         FROM generate_series(1, 5000) value",
    )
    .bind(attempt_id.as_str())
    .bind(&harness.target.config().target_store_id)
    .bind(&harness.target.config().bucket)
    .bind(&digest)
    .execute(&harness.pool)
    .await
    .unwrap();
    let committed = json!({
        "attempt_id": attempt_id,
        "manifest_id": manifest_id,
        "schema_version": 1,
        "manifest": {
            "store": harness.target.config().target_store_id,
            "bucket": harness.target.config().bucket,
            "key": manifest_key,
            "version_id": null,
            "sha256": digest,
            "size": 0
        },
        "member_count": 5000,
        "total_bytes": 5000
    });
    sqlx::query(
        "UPDATE jobs
         SET phase = 'succeeded', committed_result = $2, terminal_at = now(),
             dispatch_needed = FALSE, updated_at = now()
         WHERE job_id = $1",
    )
    .bind(job_id.as_str())
    .bind(committed)
    .execute(&harness.pool)
    .await
    .unwrap();
    let operation = harness
        .service
        .hard_delete(&harness.owner, &job_id)
        .await
        .unwrap()
        .operation;
    let count: i64 =
        sqlx::query_scalar("SELECT count(*) FROM deletion_items WHERE operation_id = $1")
            .bind(operation.id.as_str())
            .fetch_one(&harness.pool)
            .await
            .unwrap();
    assert_eq!(count, 5001);
}

async fn drain(service: &DeletionService, operation_id: &OpaqueId) {
    for _ in 0..20_000 {
        match service.process_next(operation_id).await.unwrap() {
            DrainOutcome::Completed => return,
            DrainOutcome::Idle => tokio::task::yield_now().await,
            DrainOutcome::ItemCompleted | DrainOutcome::Blocked => {}
        }
    }
    panic!("deletion operation did not complete");
}

async fn drain_concurrently(service: &DeletionService, operation_id: &OpaqueId, workers: usize) {
    let mut tasks = Vec::with_capacity(workers);
    for _ in 0..workers {
        let service = service.clone();
        let operation_id = operation_id.clone();
        tasks.push(tokio::spawn(async move {
            drain(&service, &operation_id).await;
        }));
    }
    for task in tasks {
        task.await.unwrap();
    }
}

async fn insert_job(
    pool: &PgPool,
    owner_id: &OpaqueId,
    target: &TargetStore,
) -> Result<OpaqueId, sqlx::Error> {
    let job_id = unique_id("job");
    let grant_id = unique_id("grant");
    let source_id = unique_id("source");
    let now = OffsetDateTime::now_utc();
    let mut tx = pool.begin().await?;
    sqlx::query("SET CONSTRAINTS ALL DEFERRED")
        .execute(tx.as_mut())
        .await?;
    sqlx::query(
        "INSERT INTO sensitive_source_inputs (sensitive_source_ref, source_input, created_at)
         VALUES ($1, '{}'::jsonb, $2)",
    )
    .bind(source_id.as_str())
    .bind(now)
    .execute(tx.as_mut())
    .await?;
    sqlx::query(
        "INSERT INTO execution_grants (
            grant_id, owner_id, target_store, target_bucket, target_prefix,
            policy_version, status, created_at
         ) VALUES ($1, $2, $3, $4, '[]'::jsonb, 'test-policy', 'active', $5)",
    )
    .bind(grant_id.as_str())
    .bind(owner_id.as_str())
    .bind(&target.config().target_store_id)
    .bind(&target.config().bucket)
    .bind(now)
    .execute(tx.as_mut())
    .await?;
    sqlx::query(
        "INSERT INTO jobs (
            job_id, owner_id, revision, phase, lifecycle, source_projection,
            sensitive_source_ref, target_store, target_bucket, target_prefix,
            execution_grant_id, dispatch_needed, created_at, updated_at
         ) VALUES (
            $1, $2, 1, 'queued', 'visible', '{\"kind\":\"http\"}'::jsonb,
            $3, $4, $5, '[]'::jsonb, $6, TRUE, $7, $7
         )",
    )
    .bind(job_id.as_str())
    .bind(owner_id.as_str())
    .bind(source_id.as_str())
    .bind(&target.config().target_store_id)
    .bind(&target.config().bucket)
    .bind(grant_id.as_str())
    .bind(now)
    .execute(tx.as_mut())
    .await?;
    tx.commit().await?;
    Ok(job_id)
}

async fn insert_attempt(
    pool: &PgPool,
    job_id: &OpaqueId,
    state: &str,
) -> Result<OpaqueId, sqlx::Error> {
    let attempt_id = unique_id("attempt");
    let now = OffsetDateTime::now_utc();
    let (executing_at, stopped_at, committed_at) = match state {
        "committed" => (Some(now), None, Some(now)),
        "stopped" => (Some(now), Some(now), None),
        _ => panic!("unsupported test attempt state"),
    };
    sqlx::query(
        "INSERT INTO attempts (
            attempt_id, job_id, fence, worker_incarnation, state,
            authorized_at, executing_at, stopped_at, committed_at,
            staging_origin_attempt_id
         ) VALUES ($1, $2, 1, $3, $4, $5, $6, $7, $8, $1)",
    )
    .bind(attempt_id.as_str())
    .bind(job_id.as_str())
    .bind(unique_id("worker").as_str())
    .bind(state)
    .bind(now)
    .bind(executing_at)
    .bind(stopped_at)
    .bind(committed_at)
    .execute(pool)
    .await?;
    Ok(attempt_id)
}

async fn set_terminal_without_result(
    pool: &PgPool,
    job_id: &OpaqueId,
    phase: &str,
) -> Result<(), sqlx::Error> {
    let failure = (phase == "failed").then(|| {
        json!({
            "class": "internal",
            "disposition": {"kind": "terminal"}
        })
    });
    sqlx::query(
        "UPDATE jobs
         SET phase = $2, failure = $3, terminal_at = now(),
             dispatch_needed = FALSE, updated_at = now()
         WHERE job_id = $1",
    )
    .bind(job_id.as_str())
    .bind(phase)
    .bind(failure)
    .execute(pool)
    .await?;
    Ok(())
}

async fn set_succeeded(
    pool: &PgPool,
    job_id: &OpaqueId,
    committed: &PublicationInput,
) -> Result<(), sqlx::Error> {
    let result = json!({
        "attempt_id": committed.attempt_id,
        "manifest_id": committed.manifest_id,
        "schema_version": committed.schema_version,
        "manifest": {
            "store": committed.manifest.identity.target_store_id,
            "bucket": committed.manifest.identity.bucket,
            "key": committed.manifest.identity.key,
            "version_id": committed.manifest.identity.version_id,
            "sha256": committed.manifest.content_sha256.as_str(),
            "size": committed.manifest.size
        },
        "member_count": committed.member_count,
        "total_bytes": committed.total_bytes
    });
    sqlx::query(
        "UPDATE jobs
         SET phase = 'succeeded', committed_result = $2, terminal_at = now(),
             dispatch_needed = FALSE, updated_at = now()
         WHERE job_id = $1",
    )
    .bind(job_id.as_str())
    .bind(result)
    .execute(pool)
    .await?;
    Ok(())
}

async fn insert_cleanup(
    pool: &PgPool,
    attempt_id: &OpaqueId,
    kind: &str,
    key: &str,
    upload_id: Option<&str>,
) {
    let target: (String, String) = sqlx::query_as(
        "SELECT j.target_store, j.target_bucket
         FROM attempts a JOIN jobs j ON j.job_id = a.job_id
         WHERE a.attempt_id = $1",
    )
    .bind(attempt_id.as_str())
    .fetch_one(pool)
    .await
    .unwrap();
    sqlx::query(
        "INSERT INTO attempt_staging_cleanup (
            attempt_id, kind, target_store, bucket, object_key, upload_id, created_at
         ) VALUES ($1, $2, $3, $4, $5, $6, now())",
    )
    .bind(attempt_id.as_str())
    .bind(kind)
    .bind(target.0)
    .bind(target.1)
    .bind(key)
    .bind(upload_id)
    .execute(pool)
    .await
    .unwrap();
}

async fn job_lifecycle(pool: &PgPool, job_id: &OpaqueId) -> String {
    sqlx::query_scalar("SELECT lifecycle FROM jobs WHERE job_id = $1")
        .bind(job_id.as_str())
        .fetch_one(pool)
        .await
        .unwrap()
}

fn principal(
    id: OpaqueId,
    target: &TargetStore,
    authorized: bool,
    administrator: bool,
) -> Principal {
    Principal {
        id,
        issuer: "https://issuer.example".into(),
        subject: "subject".into(),
        administrator,
        scopes: authorized
            .then(|| StorageScope {
                store: TargetStoreId::parse(&target.config().target_store_id).unwrap(),
                bucket: BucketName::parse(&target.config().bucket).unwrap(),
                prefix: TargetPrefix::parse("", std::iter::empty::<&str>()).unwrap(),
            })
            .into_iter()
            .collect(),
    }
}

fn required_env(name: &str) -> Option<String> {
    std::env::var(name).ok().filter(|value| !value.is_empty())
}

fn unique_id(prefix: &str) -> OpaqueId {
    let nanos = SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .expect("system time")
        .as_nanos();
    let sequence = NEXT_ID.fetch_add(1, Ordering::Relaxed);
    OpaqueId::parse(format!("{prefix}-{nanos:x}-{sequence:x}")).expect("opaque id")
}