caterium-app/ops/sql/SUPABASE-FRESH-PROMOS-AND-ADMIN.sql

-- Missing production RPC contracts reconstructed for the empty Caterium project.
create table public.caterium_trial_promos (
 id uuid primary key default gen_random_uuid(),code text not null unique,
 client_email text,trial_days integer not null default 14 check(trial_days between 1 and 365),
 plan_id text not null default 'full' references public.sun_plans(id),
 max_uses integer not null default 1 check(max_uses between 1 and 10000),use_count integer not null default 0,
 is_active boolean not null default true,valid_until timestamptz,note text,
 created_by uuid references auth.users(id) on delete set null,created_at timestamptz not null default now(),updated_at timestamptz not null default now()
);
create table public.caterium_trial_redemptions (
 id uuid primary key default gen_random_uuid(),promo_id uuid not null references public.caterium_trial_promos(id),
 workspace_id uuid not null unique references public.sun_workspaces(id) on delete cascade,
 user_id uuid not null unique references auth.users(id) on delete cascade,email text,trial_ends_at timestamptz,
 created_at timestamptz not null default now()
);
alter table public.caterium_trial_promos enable row level security;
alter table public.caterium_trial_redemptions enable row level security;
revoke all on public.caterium_trial_promos,public.caterium_trial_redemptions from anon,authenticated;
create function public.caterium_normalize_trial_code(p_code text) returns text language sql immutable set search_path=public as $$select upper(regexp_replace(trim(coalesce(p_code,'')),'[[:space:]]','','g'))$$;
create function public.caterium_trial_promo_preview(p_code text,p_email text default null)
returns jsonb language plpgsql stable security definer set search_path=public as $$
declare p public.caterium_trial_promos%rowtype;
begin
 select * into p from public.caterium_trial_promos where code=public.caterium_normalize_trial_code(p_code);
 if not found or not p.is_active or (p.valid_until is not null and p.valid_until<=now()) or p.use_count>=p.max_uses
 or (p.client_email is not null and p.client_email<>lower(trim(coalesce(p_email,'')))) then
  return jsonb_build_object('valid',false,'reason','Промокод недействителен для этого email или срок его действия истёк');
 end if;
 return jsonb_build_object('valid',true,'trial_days',p.trial_days,'plan',p.plan_id);
end $$;
create function public.sun_dev_create_trial_promo(p_code text default null,p_email text default null,p_trial_days integer default 14,p_valid_days integer default 7,p_max_uses integer default 1,p_plan text default 'full',p_note text default null)
returns jsonb language plpgsql security definer set search_path=public as $$
declare p public.caterium_trial_promos%rowtype; c text;
begin
 perform public.sun_require_platform_admin_aal2();
 c:=coalesce(nullif(public.caterium_normalize_trial_code(p_code),''),'CTM-'||upper(replace(gen_random_uuid()::text,'-',''))::varchar(16));
 if c !~ '^[A-Z0-9-]{3,32}$' then raise exception 'Некорректный промокод'; end if;
 if p_valid_days not between 1 and 365 then raise exception 'Некорректный срок'; end if;
 insert into public.caterium_trial_promos(code,client_email,trial_days,valid_until,max_uses,plan_id,note,created_by)
 values(c,nullif(lower(trim(p_email)),''),p_trial_days,now()+make_interval(days=>p_valid_days),p_max_uses,p_plan,p_note,auth.uid()) returning * into p;
 perform public.sun_platform_log_event('trial_promo.create',null,null,jsonb_build_object('promo_id',p.id));
 return jsonb_build_object('promo_id',p.id,'code',p.code,'trial_days',p.trial_days,'valid_until',p.valid_until);
end $$;
create function public.sun_dev_list_trial_promos(p_limit integer default 300)
returns table(promo_id uuid,code text,client_email text,trial_days integer,valid_until timestamptz,max_uses integer,use_count integer,is_active boolean,last_redeemed_at timestamptz,last_workspace_name text,last_redeemed_email text)
language plpgsql stable security definer set search_path=public as $$
begin
 perform public.sun_require_platform_admin_aal2();
 return query select p.id,p.code,p.client_email,p.trial_days,p.valid_until,p.max_uses,p.use_count,p.is_active,r.created_at,w.name,r.email
 from public.caterium_trial_promos p left join lateral (select x.* from public.caterium_trial_redemptions x where x.promo_id=p.id order by x.created_at desc limit 1) r on true
 left join public.sun_workspaces w on w.id=r.workspace_id order by p.created_at desc limit greatest(1,least(coalesce(p_limit,300),1000));
end $$;
create function public.sun_dev_set_trial_promo_active(p_promo uuid,p_active boolean)
returns void language plpgsql security definer set search_path=public as $$
begin
 perform public.sun_require_platform_admin_aal2();
 update public.caterium_trial_promos set is_active=p_active,updated_at=now() where id=p_promo;
 if not found then raise exception 'Промокод не найден'; end if;
 perform public.sun_platform_log_event('trial_promo.set_active',null,null,jsonb_build_object('promo_id',p_promo,'active',p_active));
end $$;

create function public.caterium_platform_create_company(p_name text,p_owner_email text default null,p_plan text default 'full',p_days integer default 30,p_mode text default 'empty')
returns jsonb language plpgsql security definer set search_path=public,auth as $$
declare v_ws uuid; v_owner uuid; v_email text:=nullif(lower(trim(p_owner_email)),''); v_token uuid;
begin
 perform public.sun_require_platform_admin_aal2();
 if p_days not between 1 and 3650 then raise exception 'Invalid subscription duration'; end if;
 select id into v_owner from auth.users where lower(email)=v_email and email_confirmed_at is not null;
 insert into public.sun_workspaces(name,created_by) values(coalesce(nullif(trim(p_name),''),'Новая компания'),coalesce(v_owner,auth.uid())) returning id into v_ws;
 if v_owner is not null then
  insert into public.sun_workspace_members(workspace_id,user_id,role,is_active,permissions,display_name)
  values(v_ws,v_owner,'admin',true,public.sun_role_default_permissions('admin'),split_part(v_email,'@',1));
 elsif v_email is not null then
  insert into public.caterium_company_owner_invites(workspace_id,email) values(v_ws,v_email) returning token into v_token;
 end if;
 insert into public.sun_app_state(workspace_id,client_id) values(v_ws,'platform-bootstrap');
 insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,current_period_start,current_period_end,grace_until,source)
 values(v_ws,p_plan,'active',now(),now()+make_interval(days=>p_days),now()+make_interval(days=>p_days+7),'platform');
 return jsonb_build_object('workspace_id',v_ws,'owner_user_id',v_owner,'owner_email',v_email,'owner_invite_token',v_token);
end $$;

-- Recover the sun_dev_* names used by the client, preserving the retained
-- server implementation and requiring AAL2 at every platform boundary.
do $recovery$
declare entry record; f record; call_args text; command text;
begin
 for entry in select * from (values
 ('sun_dev_dashboard','sun_platform_dashboard'),('sun_dev_list_activity','sun_platform_list_activity'),
 ('sun_dev_list_companies','sun_platform_list_companies_v22'),('sun_dev_list_users','sun_platform_list_users_v22'),
 ('sun_dev_list_errors','sun_platform_list_errors_v22'),('sun_dev_support_snapshot','sun_platform_support_snapshot'),
 ('sun_dev_workspace_diagnostics','sun_platform_workspace_diagnostics'),('sun_dev_list_workspace_features','sun_platform_list_workspace_features'),
 ('sun_dev_log_event','sun_platform_log_event'),('sun_dev_set_plan_feature','sun_platform_set_plan_feature'),
 ('sun_dev_set_plan_max_members','sun_platform_set_plan_max_members'),('sun_dev_seed_workspace_catalog','sun_platform_seed_workspace_catalog'),
 ('sun_dev_reset_feature_override','sun_platform_reset_feature_override'),('sun_dev_create_company','sun_platform_create_company_v22'),
 ('sun_dev_set_subscription','sun_platform_set_subscription'),('sun_dev_set_feature_override','sun_platform_set_feature_override')
 ) names(alias_name,source_name) loop
  select p.*,pg_get_function_arguments(p.oid) as args,pg_get_function_result(p.oid) as result into strict f
  from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname=entry.source_name;
  select coalesce(string_agg('$'||i,',' order by i),'') into call_args from generate_series(1,f.pronargs) i;
  command:=case when f.proretset then 'return query select * from' when f.prorettype='void'::regtype then 'perform' else 'return' end;
  execute format('create function public.%I(%s) returns %s language plpgsql security definer set search_path=public,auth as $body$ begin perform public.sun_require_platform_admin_aal2(); %s public.%I(%s); end $body$',entry.alias_name,f.args,f.result,command,entry.source_name,call_args);
 end loop;
end $recovery$;

revoke all on function public.caterium_normalize_trial_code(text),public.caterium_trial_promo_preview(text,text),public.caterium_platform_create_company(text,text,text,integer,text) from public,anon,authenticated;
grant execute on function public.caterium_trial_promo_preview(text,text) to anon,authenticated;
do $$declare f record;begin
 for f in select p.oid::regprocedure as signature from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname like 'sun_dev_%' loop
  execute format('revoke all on function %s from public,anon',f.signature);
  execute format('grant execute on function %s to authenticated',f.signature);
 end loop;
end $$;