BEGIN;
ALTER TABLE public.server_probe_retired_sources
 DROP CONSTRAINT server_probe_retired_sources_receipt_id_fkey,
 ADD CONSTRAINT server_probe_retired_sources_receipt_id_fkey
  FOREIGN KEY(receipt_id) REFERENCES public.server_probe_storage_admissions(receipt_id) ON DELETE CASCADE;

CREATE FUNCTION public.server_probe_retired_gc_allowed(target_receipt uuid,current_retention integer) RETURNS boolean
LANGUAGE plpgsql SET TimeZone='UTC' SET search_path=pg_catalog,public AS $$
DECLARE activity timestamptz; retained_days integer; safety integer;
BEGIN
 IF current_setting('mm.probe_storage_gc',true) IS DISTINCT FROM 'on'
  OR current_setting('mm.probe_retired_gc_receipt',true) IS DISTINCT FROM target_receipt::text THEN RETURN false; END IF;
 SELECT (r.source->>'activityAt')::timestamptz,a.retention_days,a.safety_ms
 INTO activity,retained_days,safety
 FROM public.server_probe_retired_sources r
 JOIN public.server_probe_storage_admissions a ON a.receipt_id=r.receipt_id AND a.execution_id=r.operation_id
 JOIN public.mcp_operation_replay_tombstones t ON t.operation_id=r.operation_id
 JOIN public.mcp_connections c ON c.id=t.connection_id AND c.owner_id=a.owner_id
 WHERE r.receipt_id=target_receipt AND r.proof=current_setting('mm.probe_retired_gc_proof',true)
  AND a.identity_kind='mcp' AND a.cancelled_at IS NULL AND a.claim_token IS NULL AND isfinite(a.claim_attempted_at)
  AND t.action_id='servers.probes.run' AND NOT t.target_global
  AND r.source->>'planId'=t.plan_id::text AND r.source->>'connectionId'=c.id::text
  AND r.source->'serverIds'=to_jsonb(t.target_server_ids) AND r.source->>'ownerId'=a.owner_id
  AND r.source->>'bindingProof'=a.binding_proof
  AND (r.source->>'authorityExpiresAt')::timestamptz=a.authority_expires_at
  AND (r.source->>'gcNotBefore')::timestamptz=a.gc_not_before
  AND (r.source->>'retentionDays')::int=a.retention_days AND (r.source->>'safetyMs')::int=a.safety_ms
  AND isfinite(a.authority_expires_at) AND isfinite(a.gc_not_before)
  AND a.authority_expires_at<clock_timestamp() AND a.gc_not_before<clock_timestamp()
  AND NOT EXISTS(SELECT 1 FROM public.mcp_operations WHERE id=t.operation_id)
  AND NOT EXISTS(SELECT 1 FROM public.mcp_plans WHERE id=t.plan_id)
  AND NOT EXISTS(SELECT 1 FROM public.mcp_operation_steps WHERE operation_id=t.operation_id)
  AND NOT EXISTS(SELECT 1 FROM public.mcp_operation_checkpoints WHERE operation_id=t.operation_id)
  AND NOT EXISTS(SELECT 1 FROM public.mcp_diagnostic_cancellations WHERE operation_id=t.operation_id)
  AND NOT EXISTS(SELECT 1 FROM public.server_probe_execution_quarantines q
   WHERE q.receipt_id=r.receipt_id OR(q.identity_kind='mcp' AND q.execution_id=r.operation_id));
 IF NOT FOUND OR activity IS NULL OR NOT isfinite(activity)
  OR current_retention IS NULL OR current_retention NOT BETWEEN 1 AND 365 THEN RETURN false; END IF;
 IF EXISTS(SELECT 1 FROM public.server_probe_executions j WHERE j.receipt_id=target_receipt
   AND (j.finished_at IS NULL OR j.finished_at>activity))
  OR EXISTS(SELECT 1 FROM public.server_probe_execution_stages s WHERE s.receipt_id=target_receipt
   AND GREATEST(s.created_at,COALESCE(s.received_at,s.created_at))>activity)
 THEN RETURN false; END IF;
 RETURN activity+make_interval(days=>GREATEST(retained_days,current_retention))
   +GREATEST(safety,60000)*interval '1 millisecond'<clock_timestamp();
END $$;

CREATE OR REPLACE FUNCTION public.server_probe_retired_source_immutable() RETURNS trigger
LANGUAGE plpgsql SET search_path=pg_catalog,public AS $$
BEGIN
 IF TG_OP='DELETE' THEN
  IF current_setting('mm.probe_storage_gc',true)='on'
  AND current_setting('mm.probe_retired_gc_receipt',true)=OLD.receipt_id::text
  AND current_setting('mm.probe_retired_gc_proof',true)=OLD.proof
  AND NOT EXISTS(SELECT 1 FROM public.server_probe_storage_admissions WHERE receipt_id=OLD.receipt_id)
  AND NOT EXISTS(SELECT 1 FROM public.server_probe_executions WHERE receipt_id=OLD.receipt_id)
  AND NOT EXISTS(SELECT 1 FROM public.server_probe_execution_payloads WHERE receipt_id=OLD.receipt_id)
  AND NOT EXISTS(SELECT 1 FROM public.server_probe_execution_stages WHERE receipt_id=OLD.receipt_id)
  THEN RETURN OLD; END IF;
 END IF;
 RAISE EXCEPTION 'retired diagnostic source is immutable';
END $$;

CREATE OR REPLACE FUNCTION public.server_probe_storage_gc_allowed(id uuid) RETURNS boolean
LANGUAGE plpgsql SET TimeZone='UTC' AS $$
DECLARE a server_probe_storage_admissions%ROWTYPE; activity timestamptz; current_retention integer; terminal_at timestamptz;
BEGIN
 IF current_setting('mm.probe_storage_gc',true) IS DISTINCT FROM 'on' THEN RETURN false; END IF;
 PERFORM pg_advisory_xact_lock(hashtextextended('server-probe-storage-v2',0));
 SELECT * INTO a FROM server_probe_storage_admissions WHERE receipt_id=id FOR UPDATE;
 IF NOT FOUND OR a.gc_not_before>=clock_timestamp() OR a.authority_expires_at>=clock_timestamp() THEN RETURN false; END IF;
 SELECT settings.requests_retention_days INTO current_retention FROM mcp_settings settings WHERE settings.id=1;
 IF current_retention IS NULL OR current_retention NOT BETWEEN 1 AND 365 THEN RETURN false; END IF;
 IF EXISTS(SELECT 1 FROM public.server_probe_retired_sources WHERE receipt_id=id) THEN
  RETURN public.server_probe_retired_gc_allowed(id,current_retention);
 END IF;
 terminal_at:=a.created_at;
 IF a.identity_kind='mcp' THEN
  SELECT GREATEST(o.finished_at,p.expires_at,COALESCE(cancellation.created_at,o.finished_at)) INTO terminal_at
   FROM mcp_operations o JOIN mcp_plans p ON p.id=o.plan_id AND p.connection_id=o.connection_id
   JOIN mcp_connections owner_connection ON owner_connection.id=o.connection_id
   LEFT JOIN mcp_diagnostic_cancellations cancellation ON cancellation.operation_id=o.id
   WHERE o.id=a.execution_id AND owner_connection.owner_id=a.owner_id AND p.operation_id='servers.probes.run'
    AND o.state IN ('applied','failed','unknown','rejected','partial','cancelled') AND o.finished_at IS NOT NULL
    AND (cancellation.operation_id IS NULL OR(cancellation.connection_id=o.connection_id AND cancellation.actor_id=a.owner_id))
   FOR UPDATE OF o,p,owner_connection;
  IF NOT FOUND OR terminal_at IS NULL THEN RETURN false; END IF;
 END IF;
 SELECT GREATEST(a.created_at,a.authority_expires_at,terminal_at,COALESCE(a.cancelled_at,a.created_at),
   COALESCE((SELECT finished_at FROM server_probe_executions WHERE receipt_id=id),a.created_at),
   COALESCE((SELECT created_at FROM server_probe_execution_quarantines WHERE receipt_id=id),a.created_at),
   COALESCE((SELECT max(GREATEST(created_at,COALESCE(received_at,created_at))) FROM server_probe_execution_stages WHERE receipt_id=id),a.created_at)) INTO activity;
 RETURN activity+make_interval(days=>GREATEST(a.retention_days,current_retention))+a.safety_ms*interval '1 millisecond'<clock_timestamp();
END $$;
INSERT INTO public.schema_migrations(id) VALUES ('0052_server_probe_retired_gc') ON CONFLICT(id) DO NOTHING;
COMMIT;