-- Admins request delivery; only the recipient can choose a new password.
-- No passwords, Auth links, access tokens or refresh tokens are persisted here.
begin;
create table if not exists marketplace_private.customer_password_recovery(
 id uuid primary key default gen_random_uuid(),
 user_id uuid not null references auth.users(id) on delete restrict,
 actor_id uuid not null references auth.users(id) on delete restrict,
 email text not null,
 status text not null default 'requested' check(status in('requested','accepted','failed','unknown')),
 error_code text check(error_code is null or error_code in('network_error','rate_limited','email_not_authorized','email_disabled','provider_error')),
 created_at timestamptz not null default now(),finished_at timestamptz,
 claimed_at timestamptz,claimed_session_id uuid
);
create index if not exists customer_password_recovery_user_time on marketplace_private.customer_password_recovery(user_id,created_at desc);
create index if not exists customer_password_recovery_actor_time on marketplace_private.customer_password_recovery(actor_id,created_at desc);
alter table marketplace_private.customer_password_recovery enable row level security;
revoke all on marketplace_private.customer_password_recovery from public,anon,authenticated,service_role;
drop trigger if exists customer_recovery_retained on marketplace_private.customer_password_recovery;
create trigger customer_recovery_retained before delete on marketplace_private.customer_password_recovery for each row execute function marketplace_private.customer_record_retained();
drop trigger if exists customer_recovery_truncate_retained on marketplace_private.customer_password_recovery;
create trigger customer_recovery_truncate_retained before truncate on marketplace_private.customer_password_recovery for each statement execute function marketplace_private.customer_record_retained();

create or replace function public.marketplace_admin_password_recovery_begin(p_user uuid) returns jsonb
language plpgsql security definer set search_path='' as $$
declare destination text;confirmed timestamptz;attempt marketplace_private.customer_password_recovery;actor uuid:=auth.uid();
begin
 if public.marketplace_role() is distinct from 'owner' or actor is null then raise exception 'not_authorized' using errcode='42501';end if;
 if p_user is null or not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 perform pg_advisory_xact_lock(hashtextextended('password-recovery-actor:'||actor::text,0));
 perform pg_advisory_xact_lock(hashtextextended('password-recovery-user:'||p_user::text,0));
 select u.email,u.email_confirmed_at into destination,confirmed from auth.users u where u.id=p_user for share;
 if destination is null or destination='' or position('@' in destination)=0 or confirmed is null then raise exception 'recovery_email_unverified';end if;
 if exists(select 1 from marketplace_private.customer_password_recovery where user_id=p_user and created_at>now()-interval '60 seconds') then raise exception 'recovery_retry_later';end if;
 if (select count(*) from marketplace_private.customer_password_recovery where user_id=p_user and created_at>now()-interval '24 hours')>=3
 or (select count(*) from marketplace_private.customer_password_recovery where actor_id=actor and created_at>now()-interval '24 hours')>=20 then raise exception 'recovery_daily_limit';end if;
 insert into marketplace_private.customer_password_recovery(user_id,actor_id,email) values(p_user,actor,destination) returning * into attempt;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source_key)
 values(p_user,'security','password_recovery_requested','customer_password_recovery',attempt.id::text,jsonb_build_object('status','requested','channel','email'),array['status'],actor,'admin','password-recovery:requested:'||attempt.id::text);
 return jsonb_build_object('id',attempt.id,'email',destination);
end$$;

create or replace function public.marketplace_admin_password_recovery_finish(p_id uuid,p_status text,p_error text default null) returns jsonb
language plpgsql security definer set search_path='' as $$
declare attempt marketplace_private.customer_password_recovery;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_status is null or p_status not in('accepted','failed','unknown') or (p_error is not null and p_error not in('network_error','rate_limited','email_not_authorized','email_disabled','provider_error'))
 or (p_status='accepted' and p_error is not null) or (p_status<>'accepted' and p_error is null) then raise exception 'recovery_attempt_invalid';end if;
 select * into attempt from marketplace_private.customer_password_recovery where id=p_id and actor_id=auth.uid() for update;
 if attempt.id is null then raise exception 'recovery_attempt_invalid';end if;
 if attempt.status=p_status and attempt.error_code is not distinct from p_error then return jsonb_build_object('status',attempt.status);end if;
 if attempt.status<>'requested' then raise exception 'recovery_attempt_invalid';end if;
 update marketplace_private.customer_password_recovery set status=p_status,error_code=p_error,finished_at=now() where id=p_id;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source_key)
 values(attempt.user_id,'security','password_recovery_'||p_status,'customer_password_recovery',p_id::text,jsonb_strip_nulls(jsonb_build_object('status',p_status,'channel','email','error_code',p_error)),array['status'],auth.uid(),'admin','password-recovery:result:'||p_id::text);
 return jsonb_build_object('status',p_status);
end$$;

-- The email callback is customer-bound and accepts only a fresh OTP/recovery
-- session issued after this request. A generic password session is insufficient.
-- This grants a short recovery form, never an application login session.
create or replace function public.marketplace_customer_password_recovery_claim(p_id uuid) returns jsonb
language plpgsql security definer set search_path='' as $$
declare attempt marketplace_private.customer_password_recovery;claims jsonb:=auth.jwt();sid uuid;proof_time numeric;current_email text;confirmed timestamptz;
begin
 if auth.uid() is null then raise exception 'recovery_link_invalid' using errcode='42501';end if;
 select * into attempt from marketplace_private.customer_password_recovery where id=p_id and user_id=auth.uid() for update;
 if attempt.id is null or attempt.status='failed' or attempt.created_at<now()-interval '24 hours' then raise exception 'recovery_link_invalid' using errcode='42501';end if;
 select u.email,u.email_confirmed_at into current_email,confirmed from auth.users u where u.id=auth.uid();
 if confirmed is null or current_email is distinct from attempt.email then raise exception 'recovery_link_invalid' using errcode='42501';end if;
 begin
  sid:=(claims->>'session_id')::uuid;
  select max((proof->>'timestamp')::numeric) into proof_time from jsonb_array_elements(claims->'amr') proof where proof->>'method' in('otp','recovery');
 exception when others then raise exception 'recovery_link_invalid' using errcode='42501';end;
 if sid is null or proof_time is null or proof_time<extract(epoch from attempt.created_at)-2 or proof_time<extract(epoch from now())-600 or proof_time>extract(epoch from now())+30 then raise exception 'recovery_link_invalid' using errcode='42501';end if;
 if attempt.claimed_session_id is not null and attempt.claimed_session_id<>sid then raise exception 'recovery_link_invalid' using errcode='42501';end if;
 update marketplace_private.customer_password_recovery set claimed_at=coalesce(claimed_at,now()),claimed_session_id=sid where id=p_id;
 return jsonb_build_object('email',current_email,'user_id',auth.uid());
end$$;

revoke all on function public.marketplace_admin_password_recovery_begin(uuid),public.marketplace_admin_password_recovery_finish(uuid,text,text),public.marketplace_customer_password_recovery_claim(uuid) from public,anon,service_role;
grant execute on function public.marketplace_admin_password_recovery_begin(uuid),public.marketplace_admin_password_recovery_finish(uuid,text,text),public.marketplace_customer_password_recovery_claim(uuid) to authenticated;

-- Preserve all deployed title additions (including deposits) while making the
-- new, append-only events searchable by their Persian titles.
do $$
declare definition text;marker text:='select coalesce(case lower(p_action)';
begin
 select pg_get_functiondef('marketplace_private.customer_activity_title(text,text)'::regprocedure) into definition;
 if position('password_recovery_requested' in definition)=0 then
  if position(marker in definition)=0 then raise exception 'customer_activity_title_contract_missing';end if;
  definition:=replace(definition,marker,marker||$titles$
 when 'password_recovery_requested' then 'درخواست بازیابی رمز عبور توسط مدیر'
 when 'password_recovery_accepted' then 'پذیرش ارسال بازیابی رمز توسط سرویس ایمیل'
 when 'password_recovery_failed' then 'ناموفق بودن ارسال بازیابی رمز عبور'
 when 'password_recovery_unknown' then 'نامشخص بودن نتیجه ارسال بازیابی رمز عبور'
 $titles$);
  execute definition;
 end if;
end$$;
notify pgrst,'reload schema';
commit;