begin;
create schema if not exists marketplace_private;
revoke all on schema marketplace_private from public, anon, authenticated;
create table marketplace_private.members (
 user_id uuid primary key references auth.users(id) on delete cascade,
 role text not null check (role in ('owner','publisher','editor')),
 created_at timestamptz not null default now()
);
alter table marketplace_private.members enable row level security;
revoke all on marketplace_private.members from public, anon, authenticated;
create or replace function public.marketplace_role() returns text language sql stable security definer set search_path = '' as $$
 select role from marketplace_private.members where user_id=(select auth.uid()) and (select auth.jwt()->>'aal')='aal2'
$$;
revoke all on function public.marketplace_role() from public,anon;
grant execute on function public.marketplace_role() to authenticated;
create table public.projects (
 id uuid primary key default gen_random_uuid(),
 slug text unique not null check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$' and length(slug)<=100),
 category text not null check (category in ('tangible','intangible','investment')),
 status text not null default 'draft' check (status in ('draft','published','archived')),
 title jsonb not null check (jsonb_typeof(title)='object' and coalesce(jsonb_typeof(title->'fa')='string' and length(title->>'fa')>0,false)),
 subtitle jsonb not null default '{}' check(jsonb_typeof(subtitle)='object'), description jsonb not null default '{}' check(jsonb_typeof(description)='object'), location jsonb not null default '{}' check(jsonb_typeof(location)='object'),
 image_url text not null default '' check (image_url='' or image_url ~ '^https://' or image_url ~ '^/images/'),
 current_return numeric(8,2) not null default 0 check (current_return>=-100 and current_return<=100000),
 unit_price_rial bigint not null check (unit_price_rial>0 and unit_price_rial<=900000000000000),
 duration_months int not null check (duration_months between 1 and 600),
 total_units bigint not null check (total_units>0 and total_units<=9000000000),
 sold_units bigint not null default 0 check (sold_units>=0 and sold_units<=total_units),
 risk jsonb not null default '{}' check(jsonb_typeof(risk)='object'),symbol text not null check (symbol ~ '^[A-Z0-9]{2,16}$'),
 documents jsonb not null default '[]' check (jsonb_typeof(documents)='array'),
 sort_order int not null default 0, updated_at timestamptz not null default now(),created_at timestamptz not null default now(),
 constraint published_content_complete check (status<>'published' or coalesce((length(description->>'fa')>=30 and length(risk->>'fa')>=20),false))
);
create index projects_published_order on public.projects(status,sort_order);
alter table public.projects enable row level security;
revoke all on public.projects from anon,authenticated;
grant select on public.projects to anon,authenticated;
grant insert,update,delete on public.projects to authenticated;
create policy projects_published_read on public.projects for select to anon,authenticated using(status='published');
create policy projects_staff_read on public.projects for select to authenticated using ((select public.marketplace_role()) in ('owner','publisher','editor'));
create policy projects_staff_insert on public.projects for insert to authenticated with check ((select public.marketplace_role()) in ('owner','publisher') or ((select public.marketplace_role())='editor' and status='draft'));
create policy projects_staff_update on public.projects for update to authenticated using ((select public.marketplace_role()) in ('owner','publisher') or ((select public.marketplace_role())='editor' and status='draft')) with check ((select public.marketplace_role()) in ('owner','publisher') or ((select public.marketplace_role())='editor' and status='draft'));
create policy projects_owner_delete on public.projects for delete to authenticated using ((select public.marketplace_role())='owner');
create table public.site_content(key text primary key check (key='site'),data jsonb not null check(jsonb_typeof(data)='object'),updated_at timestamptz not null default now());
alter table public.site_content enable row level security;
revoke all on public.site_content from anon,authenticated;
grant select on public.site_content to anon,authenticated;
grant insert,update on public.site_content to authenticated;
create policy site_content_read on public.site_content for select to anon,authenticated using (true);
create policy site_content_insert on public.site_content for insert to authenticated with check((select public.marketplace_role()) in ('owner','publisher'));
create policy site_content_update on public.site_content for update to authenticated using((select public.marketplace_role()) in ('owner','publisher')) with check((select public.marketplace_role()) in ('owner','publisher'));
create table marketplace_private.audit_log(id bigint generated always as identity primary key,actor uuid,entity text not null,record_id text,operation text not null,before_data jsonb,after_data jsonb,occurred_at timestamptz not null default now());
alter table marketplace_private.audit_log enable row level security;
revoke all on marketplace_private.audit_log from public,anon,authenticated;
create function marketplace_private.touch_updated_at() returns trigger language plpgsql set search_path='' as $$begin new.updated_at=clock_timestamp();return new;end$$;
create function marketplace_private.audit_change() returns trigger language plpgsql security definer set search_path='' as $$
begin
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,before_data,after_data)
 values(auth.uid(),tg_table_name,coalesce(to_jsonb(new)->>'id',to_jsonb(old)->>'id',to_jsonb(new)->>'key',to_jsonb(old)->>'key'),tg_op,case when tg_op<>'INSERT' then to_jsonb(old) end,case when tg_op<>'DELETE' then to_jsonb(new) end);
 return coalesce(new,old);
end$$;
create trigger projects_updated_at before update on public.projects for each row execute function marketplace_private.touch_updated_at();
create trigger content_updated_at before update on public.site_content for each row execute function marketplace_private.touch_updated_at();
create trigger projects_audit after insert or update or delete on public.projects for each row execute function marketplace_private.audit_change();
create trigger content_audit after insert or update on public.site_content for each row execute function marketplace_private.audit_change();
create function public.marketplace_audit() returns table(id bigint,actor uuid,entity text,record_id text,operation text,occurred_at timestamptz) language sql stable security definer set search_path='' as $$
 select a.id,a.actor,a.entity,a.record_id,a.operation,a.occurred_at from marketplace_private.audit_log a where public.marketplace_role() in ('owner','publisher') order by occurred_at desc limit 100
$$;
revoke all on function public.marketplace_audit() from public,anon;
grant execute on function public.marketplace_audit() to authenticated;
insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('marketplace-media','marketplace-media',true,26214400,array['image/jpeg','image/png','image/webp','video/mp4']) on conflict(id) do nothing;
create policy marketplace_media_insert on storage.objects for insert to authenticated with check (bucket_id='marketplace-media' and (select public.marketplace_role()) in ('owner','publisher','editor'));
create policy marketplace_media_delete on storage.objects for delete to authenticated using (bucket_id='marketplace-media' and (select public.marketplace_role())='owner');
-- Initial enrollment reveals only whether the authenticated user is invited.
create function public.marketplace_mfa_enrollment_allowed() returns boolean language sql stable security definer set search_path='' as $$select exists(select 1 from marketplace_private.members where user_id=(select auth.uid()))$$;
revoke all on function public.marketplace_mfa_enrollment_allowed() from public,anon;
grant execute on function public.marketplace_mfa_enrollment_allowed() to authenticated;
commit;