Jyotisha/frontend/supabase/migrations/20260813040000_allow_parallel_rectification_cases.sql

begin;

-- BUG-177: starting birth-time rectification from the homepage is an explicit
-- create action. Existing unfinished cases remain resumable through their
-- exact history/session entry, but no longer block a separate new case.
drop index if exists public.agentic_rectification_cases_one_resumable_per_user;

create or replace function public.open_agentic_rectification_case(
  p_user_id uuid,
  p_request_id uuid,
  p_intent text,
  p_session_id uuid,
  p_skill_name text,
  p_skill_version text,
  p_baseline_profile_fingerprint text,
  p_baseline_birth_snapshot jsonb,
  p_candidate_range jsonb
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_session public.chat_sessions%rowtype;
  v_session_id uuid;
begin
  if p_user_id is null or p_request_id is null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if p_intent not in ('homepage', 'session', 'new') then
    raise exception 'agentic_rectification_invalid_intent' using errcode = 'P0001';
  end if;
  if length(btrim(p_skill_name)) = 0 or length(btrim(p_skill_version)) = 0
    or length(btrim(coalesce(p_baseline_profile_fingerprint, ''))) = 0 then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  -- Serialize same-user opens so a repeated requestId is deterministic. A
  -- separate click carries a separate requestId and is allowed to create.
  perform pg_catalog.pg_advisory_xact_lock(
    pg_catalog.hashtext('agentic_rectification_open:' || p_user_id::text)
  );

  select c.* into v_case
  from public.agentic_rectification_cases c
  join public.agentic_rectification_open_ledger l
    on l.case_id = c.id and l.session_id = c.session_id
  where l.user_id = p_user_id and l.request_id = p_request_id
  limit 1;

  if found then
    return jsonb_build_object(
      'disposition', case
        when v_case.status = any (public.agentic_rectification_resumable_statuses()) then 'resumed'
        else 'readonly'
      end,
      'case_id', v_case.id,
      'session_id', v_case.session_id,
      'status', v_case.status,
      'should_start_opening', false,
      'skill_version', v_case.skill_version
    );
  end if;

  if p_intent = 'session' then
    if p_session_id is null then
      raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
    end if;
    select * into v_session
    from public.chat_sessions
    where id = p_session_id and user_id = p_user_id;
    if not found then
      raise exception 'agentic_rectification_session_not_found' using errcode = 'P0001';
    end if;
    if v_session.session_type <> 'birth_time_rectification' then
      raise exception 'agentic_rectification_session_not_rectification' using errcode = 'P0001';
    end if;
    select * into v_case
    from public.agentic_rectification_cases
    where session_id = p_session_id;
    if not found then
      raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
    end if;
    return jsonb_build_object(
      'disposition', case
        when v_case.status = any (public.agentic_rectification_resumable_statuses()) then 'resumed'
        else 'readonly'
      end,
      'case_id', v_case.id,
      'session_id', v_case.session_id,
      'status', v_case.status,
      'should_start_opening', false,
      'skill_version', v_case.skill_version
    );
  end if;

  if p_baseline_birth_snapshot is null or jsonb_typeof(p_baseline_birth_snapshot) <> 'object'
    or p_baseline_birth_snapshot ->> 'birth_date' is null
    or p_baseline_birth_snapshot ->> 'latitude' is null
    or p_baseline_birth_snapshot ->> 'longitude' is null
    or p_baseline_birth_snapshot ->> 'timezone_offset' is null
    or length(btrim(coalesce(p_baseline_birth_snapshot ->> 'birth_time_source', ''))) = 0 then
    raise exception 'agentic_rectification_profile_incomplete' using errcode = 'P0001';
  end if;
  if p_candidate_range is null or jsonb_typeof(p_candidate_range) <> 'object'
    or not (p_candidate_range ->> 'start_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'
    or not (p_candidate_range ->> 'end_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$' then
    raise exception 'agentic_rectification_invalid_range' using errcode = 'P0001';
  end if;

  insert into public.chat_sessions (user_id, title, theme, session_type, messages)
  values (p_user_id, '生时校正', 'general', 'birth_time_rectification', '[]'::jsonb)
  returning id into v_session_id;

  insert into public.agentic_rectification_cases (
    user_id, session_id, status, skill_name, skill_version,
    baseline_profile_fingerprint, baseline_birth_snapshot, candidate_range
  ) values (
    p_user_id, v_session_id, 'draft', p_skill_name, p_skill_version,
    p_baseline_profile_fingerprint, p_baseline_birth_snapshot, p_candidate_range
  ) returning * into v_case;

  insert into public.agentic_rectification_open_ledger (
    request_id, user_id, case_id, session_id, intent
  ) values (
    p_request_id, p_user_id, v_case.id, v_session_id, p_intent
  );

  return jsonb_build_object(
    'disposition', 'created',
    'case_id', v_case.id,
    'session_id', v_session_id,
    'status', v_case.status,
    'should_start_opening', true,
    'skill_version', v_case.skill_version
  );
end;
$$;

revoke all on function public.open_agentic_rectification_case(uuid, uuid, text, uuid, text, text, text, jsonb, jsonb)
  from public, anon, authenticated;
grant execute on function public.open_agentic_rectification_case(uuid, uuid, text, uuid, text, text, text, jsonb, jsonb)
  to service_role;

commit;