export function relayPurgeCompletionAuditId(purgeId: string): string {
  return `audit_${purgeId.slice("purge_".length)}`;
}

export function relayPurgeRecordCanReturn(
  state: string,
  completedProofValid: boolean,
): boolean {
  return state !== "completed" || completedProofValid;
}

export const COMPLETED_RELAY_PURGE_PROOF_SQL = `
SELECT EXISTS (
  SELECT 1 FROM relay_purge_jobs AS jobs
  INNER JOIN audit_events AS audits
    ON audits.id = ?2
   AND audits.actor_id = jobs.relay_node_id
   AND audits.action = 'relay.purge.completed'
   AND audits.target_type = 'relay_purge'
   AND audits.target_id = jobs.id
   AND json_extract(audits.after_json, '$.evidence_sha256') = jobs.evidence_sha256
  WHERE jobs.id = ?1 AND jobs.state = 'completed'
    AND jobs.evidence_sha256 = ?3
    AND EXISTS (
      SELECT 1 FROM tenant_authorization_state
      WHERE tenant_id = jobs.tenant_id AND status = 'deleted'
    )
    AND EXISTS (
      SELECT 1 FROM tenant_placements
      WHERE tenant_id = jobs.tenant_id AND relay_node_id IS NULL
        AND generation = jobs.placement_generation + 1 AND state = 'deleted'
    )
    AND EXISTS (
      SELECT 1 FROM tenant_instances
      WHERE id = jobs.tenant_id AND lifecycle = 'deleted'
        AND desired_state = 'deleted' AND observed_state = 'deleted'
    )
    AND EXISTS (
      SELECT 1 FROM account_deletion_requests
      WHERE id = jobs.deletion_request_id AND status = 'relay_purged'
    )
    AND EXISTS (
      SELECT 1 FROM accounts
      WHERE id = (
        SELECT account_id FROM account_deletion_requests
        WHERE id = jobs.deletion_request_id
      ) AND status = 'relay_purged'
    )
    AND NOT EXISTS (
      SELECT 1 FROM device_credentials
      WHERE host_id IN (
        SELECT hosts.id FROM hosts
        INNER JOIN tenant_instances AS tenants ON tenants.account_id = hosts.account_id
        WHERE tenants.id = jobs.tenant_id
      )
    )
    AND NOT EXISTS (SELECT 1 FROM phone_route_handles WHERE tenant_id = jobs.tenant_id)
    AND NOT EXISTS (SELECT 1 FROM relay_phone_principals WHERE tenant_id = jobs.tenant_id)
    AND NOT EXISTS (SELECT 1 FROM phone_handoff_tickets WHERE tenant_id = jobs.tenant_id)
    AND NOT EXISTS (
      SELECT 1 FROM hosts
      WHERE account_id = (
        SELECT account_id FROM tenant_instances WHERE id = jobs.tenant_id
      )
        AND (COALESCE(lifecycle, '') != 'deactivated'
          OR COALESCE(slot_state, '') != 'released')
    )
) AS proof_valid`;