select
  n.nspname as schema_name,
  p.proname as routine_name,
  case p.prokind
    when 'f' then 'function'
    when 'p' then 'procedure'
    when 'a' then 'aggregate'
    when 'w' then 'window'
  end as routine_kind,
  pg_get_function_identity_arguments(p.oid) as identity_args,
  pg_get_function_result(p.oid) as result_type,
  l.lanname as language,
  r.rolname as owner_name,
  p.prosecdef as security_definer,
  p.provolatile as volatility,
  p.proisstrict as strict,
  p.proparallel as parallel_safety,
  p.procost,
  p.prorows,
  p.proconfig
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
join pg_language l on l.oid = p.prolang
join pg_roles r on r.oid = p.proowner
where n.nspname not in ('pg_catalog', 'information_schema')
order by n.nspname, p.proname, pg_get_function_identity_arguments(p.oid);

create schema if not exists refactor_audit;

create table if not exists refactor_audit.routine_source_snapshot (
  snapshot_id bigserial primary key,
  captured_at timestamptz not null default clock_timestamp(),
  environment text not null default current_setting('server_version'),
  routine_oid oid not null,
  schema_name text not null,
  routine_name text not null,
  identity_args text not null,
  result_type text,
  source_hash text not null,
  source_sql text not null
);

insert into refactor_audit.routine_source_snapshot (
  routine_oid,
  schema_name,
  routine_name,
  identity_args,
  result_type,
  source_hash,
  source_sql
)
select
  p.oid,
  n.nspname,
  p.proname,
  pg_get_function_identity_arguments(p.oid),
  pg_get_function_result(p.oid),
  md5(pg_get_functiondef(p.oid)),
  pg_get_functiondef(p.oid)
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
join pg_language l on l.oid = p.prolang
where l.lanname = 'plpgsql'
  and n.nspname not in ('pg_catalog', 'information_schema');

with target as (
  select p.oid
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  where n.nspname = 'app'
    and p.proname = 'submit_case'
    and pg_get_function_identity_arguments(p.oid) = 'p_case_id bigint, p_actor_id bigint'
)
select
  pg_describe_object(d.classid, d.objid, d.objsubid) as dependent_object,
  pg_describe_object(d.refclassid, d.refobjid, d.refobjsubid) as referenced_object,
  d.deptype
from pg_depend d
join target t on t.oid = d.refobjid
order by dependent_object;

create or replace function app.count_open_cases()
returns bigint
language plpgsql
as $$
declare
  v_count bigint;
begin
  select count(*)
  into v_count
  from app.case_file
  where status = 'OPEN';

  return v_count;
end;
$$;

create or replace function app.normalize_reference(p_reference text)
returns text
language plpgsql
as $$
declare
  v_reference text;
begin
  v_reference := upper(trim(p_reference));
  v_reference := replace(v_reference, ' ', '-');
  return v_reference;
end;
$$;

create table test_case.normalize_reference_cases (
  input_reference text,
  expected_reference text,
  note text
);

insert into test_case.normalize_reference_cases values
  (' abc 123 ', 'ABC-123', 'normal trimming'),
  ('abc   123', 'ABC---123', 'legacy preserves repeated spaces as repeated dashes'),
  (null, null, 'legacy null behavior');

select
  input_reference,
  expected_reference,
  app.normalize_reference(input_reference) as actual_reference,
  app.normalize_reference(input_reference) is not distinct from expected_reference as passed,
  note
from test_case.normalize_reference_cases;

Routine: app.transition_case(bigint, text, bigint, text)
Kind: function
Security: SECURITY DEFINER
Search path: app, pg_temp
Volatility: VOLATILE
Null behavior: called on null input
Return: app.case_transition_result
Errors:
  P3001 invalid transition
  P3002 stale expected state
  P3003 permission denied
Writes:
  app.case_file
  app.case_event
  app.outbox_message
Locks:
  row lock on app.case_file
Audit:
  one case_event per accepted transition
Idempotency:
  no idempotency key in legacy version
Known callers:
  case-service
  escalation-worker
  manual-admin-console
  trigger none

create or replace function app.assign_case(case_id bigint, owner_id bigint)
returns void
language plpgsql
as $$
begin
  update app.case_file
  set owner_id = owner_id
  where case_id = case_id;
end;
$$;

create or replace function app.assign_case(
  p_case_id bigint,
  p_owner_id bigint
)
returns void
language plpgsql
as $$
begin
  update app.case_file cf
  set owner_id = p_owner_id
  where cf.case_id = p_case_id;
end;
$$;

create or replace function app.close_case(p_case_id bigint)
returns boolean
language plpgsql
as $$
begin
  update app.case_file
  set status = 'CLOSED'
  where case_id = p_case_id
    and status = 'OPEN';

  return found;
end;
$$;

create or replace function app.close_case(p_case_id bigint)
returns boolean
language plpgsql
as $$
declare
  v_rows_updated integer;
begin
  update app.case_file cf
  set status = 'CLOSED'
  where cf.case_id = p_case_id
    and cf.status = 'OPEN';

  get diagnostics v_rows_updated = row_count;

  return v_rows_updated = 1;
end;
$$;

create type app.case_transition_decision as (
  allowed boolean,
  denial_code text,
  denial_message text
);

create or replace function app.evaluate_case_transition(
  p_current_status text,
  p_requested_status text,
  p_actor_role text
)
returns app.case_transition_decision
language plpgsql
stable
as $$
begin
  if p_current_status = p_requested_status then
    return (true, null, null)::app.case_transition_decision;
  end if;

  if p_current_status = 'CLOSED' then
    return (false, 'CASE_ALREADY_CLOSED', 'Closed case cannot transition')::app.case_transition_decision;
  end if;

  if p_requested_status = 'APPROVED' and p_actor_role <> 'SUPERVISOR' then
    return (false, 'SUPERVISOR_REQUIRED', 'Only supervisor can approve')::app.case_transition_decision;
  end if;

  return (true, null, null)::app.case_transition_decision;
end;
$$;

returns boolean

create type app.close_case_result as (
  accepted boolean,
  case_id bigint,
  previous_status text,
  current_status text,
  failure_code text,
  failure_message text
);

create or replace function app.close_case_v2(p_case_id bigint)
returns app.close_case_result
language plpgsql
as $$
declare
  r_case app.case_file%rowtype;
begin
  select *
  into r_case
  from app.case_file cf
  where cf.case_id = p_case_id
  for update;

  if not found then
    return (false, p_case_id, null, null, 'NOT_FOUND', 'Case does not exist')::app.close_case_result;
  end if;

  if r_case.status = 'CLOSED' then
    return (false, p_case_id, r_case.status, r_case.status, 'ALREADY_CLOSED', 'Case is already closed')::app.close_case_result;
  end if;

  update app.case_file cf
  set status = 'CLOSED'
  where cf.case_id = p_case_id;

  return (true, p_case_id, r_case.status, 'CLOSED', null, null)::app.close_case_result;
end;
$$;

create or replace function app.close_case(p_case_id bigint)
returns boolean
language plpgsql
as $$
declare
  r_result app.close_case_result;
begin
  r_result := app.close_case_v2(p_case_id);
  return r_result.accepted;
end;
$$;

exception
  when others then
    return false;

exception
  when unique_violation then
    raise exception using
      errcode = 'P2301',
      message = 'Duplicate external reference',
      detail = format('external_reference=%s', p_external_reference),
      hint = 'Use idempotency key or load existing case';

  when serialization_failure or deadlock_detected then
    raise;

  when others then
    raise exception using
      errcode = 'P9999',
      message = 'Unexpected failure while submitting case',
      detail = sqlerrm;
end;

v_sql := 'select count(*) from ' || p_table_name || ' where status = ''' || p_status || '''';
execute v_sql into v_count;

create or replace function admin.count_rows_by_status(
  p_table regclass,
  p_status text
)
returns bigint
language plpgsql
security definer
set search_path = admin, pg_temp
as $$
declare
  v_count bigint;
  v_allowed boolean;
begin
  select exists (
    select 1
    from admin.allowed_status_table ast
    where ast.table_oid = p_table
  )
  into v_allowed;

  if not v_allowed then
    raise exception using
      errcode = 'P5001',
      message = 'Table is not allow-listed for status counting';
  end if;

  execute format(
    'select count(*) from %s where status = $1',
    p_table
  )
  into v_count
  using p_status;

  return v_count;
end;
$$;

select
  n.nspname as schema_name,
  c.relname as table_name,
  t.tgname as trigger_name,
  pg_get_triggerdef(t.oid) as trigger_def,
  p.proname as function_name,
  pg_get_function_identity_arguments(p.oid) as function_args
from pg_trigger t
join pg_class c on c.oid = t.tgrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_proc p on p.oid = t.tgfoid
where not t.tgisinternal
order by n.nspname, c.relname, t.tgname;

create or replace function app.derive_case_priority(
  p_case_type text,
  p_risk_score numeric
)
returns text
language plpgsql
immutable
as $$
begin
  if p_risk_score >= 90 then
    return 'CRITICAL';
  elsif p_case_type = 'ENFORCEMENT' and p_risk_score >= 70 then
    return 'HIGH';
  elsif p_risk_score >= 40 then
    return 'MEDIUM';
  else
    return 'LOW';
  end if;
end;
$$;

create or replace function app.case_file_set_priority_trg()
returns trigger
language plpgsql
as $$
begin
  new.priority := app.derive_case_priority(new.case_type, new.risk_score);
  return new;
end;
$$;

create or replace function admin.do_thing(p_table text)
returns void
language plpgsql
security definer
as $$
begin
  execute 'delete from ' || p_table;
end;
$$;

create or replace function admin.purge_approved_staging_rows(
  p_batch_id bigint
)
returns integer
language plpgsql
security definer
set search_path = admin, staging, pg_temp
as $$
declare
  v_deleted integer;
begin
  delete from staging.case_import_row r
  where r.batch_id = p_batch_id
    and r.row_status = 'APPLIED';

  get diagnostics v_deleted = row_count;
  return v_deleted;
end;
$$;

revoke all on function admin.purge_approved_staging_rows(bigint) from public;
grant execute on function admin.purge_approved_staging_rows(bigint) to app_maintenance_role;

v_actor_id := current_setting('app.actor_id')::bigint;

create or replace function app.current_actor_id_required()
returns bigint
language plpgsql
stable
as $$
declare
  v_actor_id text;
begin
  v_actor_id := nullif(current_setting('app.actor_id', true), '');

  if v_actor_id is null then
    raise exception using
      errcode = 'P4001',
      message = 'Missing app.actor_id session setting';
  end if;

  return v_actor_id::bigint;
end;
$$;

v_actor_id := app.current_actor_id_required();

returns bigint
-- null means not found, not allowed, not applicable, or error swallowed

create type app.lookup_case_result as (
  found boolean,
  allowed boolean,
  case_id bigint,
  failure_code text
);

create or replace function app.lookup_case_id_legacy(p_reference text)
returns bigint
language plpgsql
as $$
declare
  r_result app.lookup_case_result;
begin
  r_result := app.lookup_case_id_v2(p_reference);

  if not r_result.found or not r_result.allowed then
    return null;
  end if;

  return r_result.case_id;
end;
$$;

create or replace function app.calculate_penalty(
  p_case_id bigint
)
returns numeric
language plpgsql
as $$
declare
  v_use_new_engine boolean;
begin
  select coalesce(ff.enabled, false)
  into v_use_new_engine
  from app.feature_flag ff
  where ff.flag_name = 'penalty_engine_v2';

  if v_use_new_engine then
    return app.calculate_penalty_v2(p_case_id);
  end if;

  return app.calculate_penalty_v1(p_case_id);
end;
$$;

for r_case in
  select case_id from app.case_file where status = 'PENDING_REVIEW'
loop
  update app.case_file
  set status = 'REVIEW_EXPIRED'
  where case_id = r_case.case_id;
end loop;

update app.case_file cf
set status = 'REVIEW_EXPIRED'
where cf.status = 'PENDING_REVIEW'
  and cf.review_due_at < clock_timestamp();

with changed as (
  update app.case_file cf
  set status = 'REVIEW_EXPIRED'
  where cf.status = 'PENDING_REVIEW'
    and cf.review_due_at < clock_timestamp()
  returning cf.case_id, cf.status
)
insert into app.case_event(case_id, event_type, event_payload, created_at)
select
  c.case_id,
  'REVIEW_EXPIRED',
  jsonb_build_object('new_status', c.status),
  clock_timestamp()
from changed c;

select
  old.schema_name,
  old.routine_name,
  old.identity_args,
  old.source_hash as old_hash,
  md5(pg_get_functiondef(p.oid)) as new_hash,
  old.source_hash = md5(pg_get_functiondef(p.oid)) as unchanged
from refactor_audit.routine_source_snapshot old
join pg_proc p on p.oid = old.routine_oid
where old.snapshot_id = (
  select max(snapshot_id)
  from refactor_audit.routine_source_snapshot s
  where s.routine_oid = old.routine_oid
);

1. Capture source snapshot.
2. Inventory routine properties and grants.
3. Identify visible dependencies.
4. Search function bodies and application code for callers.
5. Create characterization cases from production-like examples.
6. Add tests for nulls, duplicates, missing rows, stale state, and errors.
7. Make one mechanical refactor.
8. Run tests.
9. Compare plans for critical SQL.
10. Deploy with CREATE OR REPLACE only if signature and return type are unchanged.
11. Observe logs, error rates, latency, and row-count outcomes.
12. Remove temporary instrumentation after confidence window.

create or replace function app.change_status(id bigint, st text)
returns boolean
language plpgsql
security definer
as $$
begin
  update app.case_file set status = st where case_id = id;
  insert into app.case_event(case_id, event_type) values (id, 'STATUS_CHANGED');
  return true;
exception when others then
  return false;
end;
$$;

create type app.change_status_result as (
  accepted boolean,
  case_id bigint,
  previous_status text,
  new_status text,
  failure_code text,
  event_id bigint
);

create or replace function app.change_status_v2(
  p_case_id bigint,
  p_expected_status text,
  p_requested_status text,
  p_actor_id bigint,
  p_reason_code text
)
returns app.change_status_result
language plpgsql
security definer
set search_path = app, pg_temp
as $$
declare
  r_case app.case_file%rowtype;
  v_event_id bigint;
begin
  select *
  into r_case
  from app.case_file cf
  where cf.case_id = p_case_id
  for update;

  if not found then
    return (false, p_case_id, null, null, 'NOT_FOUND', null)::app.change_status_result;
  end if;

  if r_case.status is distinct from p_expected_status then
    return (false, p_case_id, r_case.status, r_case.status, 'STALE_STATE', null)::app.change_status_result;
  end if;

  if not exists (
    select 1
    from app.case_status_transition_rule rule
    where rule.from_status = r_case.status
      and rule.to_status = p_requested_status
      and rule.enabled
  ) then
    return (false, p_case_id, r_case.status, r_case.status, 'INVALID_TRANSITION', null)::app.change_status_result;
  end if;

  update app.case_file cf
  set status = p_requested_status,
      updated_at = clock_timestamp(),
      updated_by = p_actor_id
  where cf.case_id = p_case_id;

  insert into app.case_event(
    case_id,
    event_type,
    actor_id,
    reason_code,
    event_payload,
    created_at
  ) values (
    p_case_id,
    'STATUS_CHANGED',
    p_actor_id,
    p_reason_code,
    jsonb_build_object(
      'previous_status', r_case.status,
      'new_status', p_requested_status
    ),
    clock_timestamp()
  )
  returning case_event_id into v_event_id;

  return (true, p_case_id, r_case.status, p_requested_status, null, v_event_id)::app.change_status_result;
end;
$$;

create or replace function app.change_status(id bigint, st text)
returns boolean
language plpgsql
security definer
set search_path = app, pg_temp
as $$
declare
  r_case app.case_file%rowtype;
  r_result app.change_status_result;
begin
  select *
  into r_case
  from app.case_file cf
  where cf.case_id = id;

  if not found then
    return false;
  end if;

  r_result := app.change_status_v2(
    id,
    r_case.status,
    st,
    app.current_actor_id_required(),
    'LEGACY_CALLER'
  );

  return r_result.accepted;
end;
$$;