-- Durable login evidence is created only when GoTrue creates a session.
-- Optional server enrichment is append-only and cannot select a user/session.
begin;
create table if not exists marketplace_private.customer_login_rollout(
 id boolean primary key default true check(id),started_at timestamptz not null default clock_timestamp()
);
insert into marketplace_private.customer_login_rollout(id) values(true) on conflict(id) do nothing;
create table if not exists marketplace_private.customer_login_ingest_config(
 id boolean primary key default true check(id),token_sha256 text check(token_sha256 ~ '^[a-f0-9]{64}$')
);
-- Provision the hash separately through the database owner. Never put a token
-- or a fixed runtime credential in migrations or expose this row in a public RPC.
insert into marketplace_private.customer_login_ingest_config(id) values(true) on conflict(id) do nothing;
create table if not exists marketplace_private.customer_login_sessions(
 session_id uuid primary key,user_id uuid not null references auth.users(id) on delete restrict,
 activity_id bigint not null unique references marketplace_private.customer_activity(id) on delete restrict,
 login_time timestamptz not null,upstream_user_agent text,upstream_ip_address inet,
 captured_at timestamptz not null default clock_timestamp()
);
-- There is intentionally no foreign key to auth.sessions: removing/revoking
-- a session must never remove the historical login evidence.
create table if not exists marketplace_private.customer_login_contexts(
 session_id uuid primary key references marketplace_private.customer_login_sessions(session_id) on delete restrict,
 device_type text not null check(device_type in('desktop','mobile','tablet','bot','unknown')),
 operating_system text not null,browser text not null,user_agent text not null,
 ip_address inet,ip_source text not null check(ip_source in('cloudflare','local_socket','unavailable')),
 location jsonb not null,recorded_at timestamptz not null default clock_timestamp()
);
create index if not exists customer_login_sessions_user_time on marketplace_private.customer_login_sessions(user_id,login_time desc);
do $$declare t text;begin
 foreach t in array array['customer_login_rollout','customer_login_ingest_config','customer_login_sessions','customer_login_contexts'] loop
  execute format('alter table marketplace_private.%I enable row level security',t);
  execute format('revoke all on marketplace_private.%I from public,anon,authenticated,service_role',t);
  if t<>'customer_login_ingest_config' then
   execute format('drop trigger if exists customer_login_retained on marketplace_private.%I',t);
   execute format('create trigger customer_login_retained before update or delete on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
   execute format('drop trigger if exists customer_login_truncate_retained on marketplace_private.%I',t);
   execute format('create trigger customer_login_truncate_retained before truncate on marketplace_private.%I for each statement execute function marketplace_private.customer_record_retained()',t);
  end if;
 end loop;
end$$;

create or replace function marketplace_private.customer_auth_session_login_capture() returns trigger
language plpgsql security definer set search_path='' as $$
declare row_data jsonb:=to_jsonb(new);ua text;original_ua text;upstream inet;event_id bigint;at_time timestamptz;
begin
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=new.user_id)
  or exists(select 1 from marketplace_private.members where user_id=new.user_id) then return null;end if;
 at_time:=new.created_at;
 if at_time is null or at_time<(select started_at from marketplace_private.customer_login_rollout where id)
  or at_time>clock_timestamp()+interval '30 seconds' then return null;end if;
 original_ua:=row_data->>'user_agent';
 -- Retain the original only in the private durable snapshot. The activity
 -- payload has a bounded, control-free representation for safe presentation.
 ua:=nullif(left(regexp_replace(coalesce(original_ua,''),'[[:cntrl:]]','','g'),512),'');
 begin upstream:=nullif(row_data->>'ip','')::inet;exception when invalid_text_representation then upstream:=null;end;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source,source_key,created_at)
 values(new.user_id,'session','login','auth.sessions',new.id::text,
  jsonb_build_object('login_time',at_time,'device_type','unknown','operating_system',null,'browser',null,'user_agent',null,
   'ip_address',null,'ip_source','unavailable','location',jsonb_build_object('country_code',null,'region',null,'city',null,'timezone',null,'source','unavailable'),
   'context_source','auth_session','context_status','pending','upstream_user_agent',ua,'upstream_ip_address',case when upstream is not null then host(upstream) end,'upstream_ip_source','supabase_auth','context_recorded_at',null),
  array['login_time','device_type','operating_system','browser','ip_address','location'],new.user_id,'customer','auth_session','auth-session-login:'||new.id::text,at_time)
 on conflict(source_key) do nothing returning id into event_id;
 if event_id is null then return null;end if;
 insert into marketplace_private.customer_login_sessions(session_id,user_id,activity_id,login_time,upstream_user_agent,upstream_ip_address)
 values(new.id,new.user_id,event_id,at_time,original_ua,upstream) on conflict(session_id) do nothing;
 return null;
end$$;
revoke all on function marketplace_private.customer_auth_session_login_capture() from public,anon,authenticated,service_role;
drop trigger if exists customer_auth_session_login_capture on auth.sessions;
create trigger customer_auth_session_login_capture after insert on auth.sessions for each row execute function marketplace_private.customer_auth_session_login_capture();

-- Keep all profile/password capture. Only the previous last_sign_in_at branch
-- is removed; existing events are never rewritten or backfilled.
do $$declare definition text;branch text:=$branch$if a->>'last_sign_in_at' is not null and a->>'last_sign_in_at' is distinct from b->>'last_sign_in_at' then
  insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,actor_id,actor_kind,source_key)
  values(new.id,'session','login','auth.users',new.id::text,new.id,'customer','auth-login:'||new.id||':'||(a->>'last_sign_in_at')) on conflict(source_key) do nothing;
 end if;$branch$;
begin
 select pg_get_functiondef('marketplace_private.customer_auth_activity_capture()'::regprocedure) into definition;
 if position('auth-login:' in definition)>0 then
  if position(branch in definition)=0 then raise exception 'login_activity_capture_contract_missing';end if;
  execute replace(definition,branch,'-- Login is recorded by the durable auth.sessions INSERT trigger.');
 end if;
end$$;

create or replace function marketplace_private.customer_login_context_normalize(p_context jsonb) returns jsonb
language plpgsql immutable set search_path='' as $$
declare k text;v jsonb;loc jsonb;normalized jsonb:='{}';normalized_location jsonb:='{}';ip inet;raw_ip text;source text;value text;
begin
 if jsonb_typeof(p_context) is distinct from 'object' or octet_length(p_context::text)>8192
  or (select array_agg(key order by key) from jsonb_object_keys(p_context) key) is distinct from array['browser','device_type','ip_address','ip_source','location','operating_system','user_agent']::text[] then raise exception 'invalid_login_context' using errcode='22023';end if;
 if jsonb_typeof(p_context->'device_type') is distinct from 'string' or p_context->>'device_type' not in('desktop','mobile','tablet','bot','unknown') then raise exception 'invalid_login_context' using errcode='22023';end if;
 normalized:=jsonb_build_object('device_type',p_context->>'device_type');
 foreach k in array array['operating_system','browser','user_agent'] loop
  v:=p_context->k;
  if jsonb_typeof(v) is distinct from 'string' or length(v#>>'{}')>(case when k='user_agent' then 512 else 80 end) or (v#>>'{}') ~ '[[:cntrl:]]' then raise exception 'invalid_login_context' using errcode='22023';end if;
  value:=trim(v#>>'{}');normalized:=normalized||jsonb_build_object(k,value);
 end loop;
 if jsonb_typeof(p_context->'ip_source') is distinct from 'string' or p_context->>'ip_source' not in('cloudflare','local_socket','unavailable') then raise exception 'invalid_login_context' using errcode='22023';end if;
 source:=p_context->>'ip_source';
 if p_context->'ip_address'<>'null'::jsonb then
  if jsonb_typeof(p_context->'ip_address') is distinct from 'string' then raise exception 'invalid_login_context' using errcode='22023';end if;
  raw_ip:=p_context->>'ip_address';
  if length(raw_ip)>45 or not(raw_ip ~ '^([0-9]{1,3}\.){3}[0-9]{1,3}$' or (position(':' in raw_ip)>0 and raw_ip ~ '^[0-9A-Fa-f:.]+$')) then raise exception 'invalid_login_context' using errcode='22023';end if;
  begin ip:=raw_ip::inet;exception when invalid_text_representation then raise exception 'invalid_login_context' using errcode='22023';end;
 end if;
 if (source='unavailable') is distinct from (ip is null) then raise exception 'invalid_login_context' using errcode='22023';end if;
 normalized:=normalized||jsonb_build_object('ip_address',case when ip is not null then host(ip) end,'ip_source',source);
 loc:=p_context->'location';
 if jsonb_typeof(loc) is distinct from 'object' or (select array_agg(key order by key) from jsonb_object_keys(loc) key) is distinct from array['city','country_code','region','source','timezone']::text[] then raise exception 'invalid_login_context' using errcode='22023';end if;
 if jsonb_typeof(loc->'source') is distinct from 'string' or loc->>'source' not in('cloudflare','local','unavailable') then raise exception 'invalid_login_context' using errcode='22023';end if;
 foreach k in array array['country_code','region','city','timezone'] loop
  v:=loc->k;value:=null;
  if v<>'null'::jsonb then
   if jsonb_typeof(v) is distinct from 'string' then raise exception 'invalid_login_context' using errcode='22023';end if;
   value:=trim(v#>>'{}');
   if length(value)>(case k when 'country_code' then 2 when 'timezone' then 80 else 100 end) or length(value)=0 or value ~ '[[:cntrl:]]' then raise exception 'invalid_login_context' using errcode='22023';end if;
   if k='country_code' then if value!~'^[A-Za-z]{2}$' then raise exception 'invalid_login_context' using errcode='22023';end if;value:=upper(value);end if;
  end if;
  normalized_location:=normalized_location||jsonb_build_object(k,value);
 end loop;
 if loc->>'source'='unavailable' and exists(select 1 from jsonb_each(normalized_location) e where e.value<>'null'::jsonb) then raise exception 'invalid_login_context' using errcode='22023';end if;
 return normalized||jsonb_build_object('location',normalized_location||jsonb_build_object('source',loc->>'source'));
end$$;
revoke all on function marketplace_private.customer_login_context_normalize(jsonb) from public,anon,authenticated,service_role;

create or replace function public.marketplace_record_login_context(p_context jsonb,p_token text) returns jsonb
language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();sid uuid;session_data jsonb;baseline marketplace_private.customer_login_sessions;context jsonb;stored uuid;expected text;at_time timestamptz;device_fa text;device_en text;device_ar text;date_text text;fa_date text;os text;browser_name text;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 select token_sha256 into expected from marketplace_private.customer_login_ingest_config where id;
 if expected is null then raise exception 'login_context_unavailable';end if;
 if p_token is null or length(p_token) not between 32 and 512 or encode(sha256(convert_to(p_token,'UTF8')),'hex') is distinct from expected then raise exception 'login_context_not_authorized' using errcode='42501';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=uid) or exists(select 1 from marketplace_private.members where user_id=uid) then raise exception 'login_context_not_authorized' using errcode='42501';end if;
 perform marketplace_private.customer_access_assert(uid);
 begin sid:=nullif(auth.jwt()->>'session_id','')::uuid;exception when invalid_text_representation then raise exception 'login_context_session_invalid' using errcode='42501';end;
 if sid is null then raise exception 'login_context_session_invalid' using errcode='42501';end if;
 perform pg_advisory_xact_lock(hashtextextended('login-context:'||sid::text,0));
 select to_jsonb(s) into session_data from auth.sessions s where s.id=sid and s.user_id=uid for share;
 if session_data is null then raise exception 'login_context_session_invalid' using errcode='42501';end if;
 at_time:=(session_data->>'created_at')::timestamptz;
 if at_time is null or at_time<clock_timestamp()-interval '10 minutes' or at_time>clock_timestamp()+interval '30 seconds'
  or at_time<(select started_at from marketplace_private.customer_login_rollout where id)
  or (nullif(session_data->>'not_after','') is not null and (session_data->>'not_after')::timestamptz<=clock_timestamp()) then raise exception 'login_context_session_invalid' using errcode='42501';end if;
 select * into baseline from marketplace_private.customer_login_sessions where session_id=sid and user_id=uid;
 if baseline.session_id is null or baseline.login_time is distinct from at_time then raise exception 'login_context_session_invalid' using errcode='42501';end if;
 context:=marketplace_private.customer_login_context_normalize(p_context);
 insert into marketplace_private.customer_login_contexts(session_id,device_type,operating_system,browser,user_agent,ip_address,ip_source,location)
 values(sid,context->>'device_type',context->>'operating_system',context->>'browser',context->>'user_agent',nullif(context->>'ip_address','')::inet,context->>'ip_source',context->'location')
 on conflict(session_id) do nothing returning session_id into stored;
 if stored is null then return jsonb_build_object('recorded',true,'already_recorded',true);end if;
 device_en:=case context->>'device_type' when 'desktop' then 'computer' when 'mobile' then 'mobile phone' when 'tablet' then 'tablet' when 'bot' then 'automated client' else 'unknown device' end;
 device_fa:=case context->>'device_type' when 'desktop' then 'رایانه' when 'mobile' then 'تلفن همراه' when 'tablet' then 'تبلت' when 'bot' then 'کارخواه خودکار' else 'دستگاه ناشناخته' end;
 device_ar:=case context->>'device_type' when 'desktop' then 'حاسوب' when 'mobile' then 'هاتف محمول' when 'tablet' then 'جهاز لوحي' when 'bot' then 'عميل آلي' else 'جهاز غير معروف' end;
 date_text:=to_char(at_time at time zone 'Asia/Tehran','YYYY-MM-DD HH24:MI');fa_date:=translate(date_text,'0123456789','۰۱۲۳۴۵۶۷۸۹');
 os:=coalesce(nullif(context->>'operating_system',''),'Unknown');browser_name:=coalesce(nullif(context->>'browser',''),'Unknown');
 insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url,sender_name)
 values(uid,'login:'||sid::text,jsonb_build_object('fa','ورود جدید به حساب پلانک','en','New sign-in to your Planck account','ar','تسجيل دخول جديد إلى حساب بلانك'),
  jsonb_build_object('fa','ورود به حساب شما با '||device_fa||'، سیستم‌عامل '||os||' و مرورگر '||browser_name||' در '||fa_date||' (میلادی، ساعت تهران) ثبت شد. اگر این ورود را انجام نداده‌اید، رمز عبور خود را تغییر دهید و با پشتیبانی تماس بگیرید.',
   'en','Your account was signed in on a '||device_en||', using '||os||' and '||browser_name||', at '||date_text||' (Tehran time). If this was not you, change your password and contact support.',
   'ar','تم تسجيل الدخول إلى حسابك من '||device_ar||' باستخدام '||os||' و'||browser_name||' في '||date_text||' (بتوقيت طهران). إذا لم تكن أنت، غيّر كلمة المرور وتواصل مع الدعم.'),
  'account','info','/account/notifications','امنیت حساب پلانک')
 on conflict(user_id,event_key) where event_key is not null do nothing;
 return jsonb_build_object('recorded',true,'already_recorded',false);
end$$;
revoke all on function public.marketplace_record_login_context(jsonb,text) from public,anon,service_role;
grant execute on function public.marketplace_record_login_context(jsonb,text) to authenticated;

-- Join append-only enrichment at read time. Neither the original login event
-- nor its review/history is edited, and no second login activity is generated.
create or replace function marketplace_private.customer_login_activity_data(p_id bigint,p_fallback jsonb) returns jsonb
language sql stable security definer set search_path='' as $$
 select case when c.session_id is null then p_fallback else p_fallback||jsonb_build_object(
  'device_type',c.device_type,'operating_system',c.operating_system,'browser',c.browser,'user_agent',c.user_agent,
  'ip_address',case when c.ip_address is not null then host(c.ip_address) end,'ip_source',c.ip_source,'location',c.location,
  'context_source','trusted_server','context_status','recorded','context_recorded_at',c.recorded_at) end
 from (select 1) one left join marketplace_private.customer_login_sessions s on s.activity_id=p_id
 left join marketplace_private.customer_login_contexts c on c.session_id=s.session_id;
$$;
revoke all on function marketplace_private.customer_login_activity_data(bigint,jsonb) from public,anon,authenticated,service_role;
do $$declare definition text;marker text:=$marker$'after',e.after_data,$marker$;begin
 select pg_get_functiondef('marketplace_private.customer_activity_json(bigint)'::regprocedure) into definition;
 if position('marketplace_private.customer_login_activity_data(e.id,e.after_data)' in definition)=0 then
  if position(marker in definition)=0 then raise exception 'login_activity_json_contract_missing';end if;
  definition:=replace(definition,marker,$marker$'after',marketplace_private.customer_login_activity_data(e.id,e.after_data),$marker$);execute definition;
 end if;
end$$;
notify pgrst,'reload schema';
commit;