BEGIN;

ALTER TABLE dealerships ADD COLUMN IF NOT EXISTS legacy_payload jsonb;
ALTER TABLE customer_requests ADD COLUMN IF NOT EXISTS legacy_payload jsonb;

CREATE OR REPLACE FUNCTION claim_background_jobs_by_type(
  p_worker_id text,
  p_job_types text[],
  p_limit integer DEFAULT 25
) RETURNS SETOF background_jobs
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH candidates AS (
    SELECT id
    FROM background_jobs
    WHERE status = 'pending'
      AND available_at <= now()
      AND job_type = ANY(p_job_types)
    ORDER BY priority ASC, available_at ASC, created_at ASC
    FOR UPDATE SKIP LOCKED
    LIMIT LEAST(GREATEST(p_limit, 1), 100)
  )
  UPDATE background_jobs job
  SET status = 'processing', locked_at = now(), locked_by = p_worker_id, attempts = attempts + 1
  FROM candidates
  WHERE job.id = candidates.id
  RETURNING job.*;
END;
$$;

CREATE OR REPLACE FUNCTION recover_stale_background_jobs()
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE recovered integer;
BEGIN
  UPDATE background_jobs
  SET status = CASE WHEN attempts >= max_attempts THEN 'dead_letter' ELSE 'pending' END,
      available_at = now(), locked_at = null, locked_by = null,
      last_error = coalesce(last_error, 'Worker visibility timeout expired.')
  WHERE status = 'processing' AND locked_at < now() - interval '5 minutes';
  GET DIAGNOSTICS recovered = ROW_COUNT;
  RETURN recovered;
END;
$$;

COMMIT;