create or replace function public.auth_uid()
returns uuid
language sql
stable
as $$
  select nullif(current_setting('app.user_id', true), '')::uuid
$$;

create or replace function public.is_member(ws uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.workspace_members m
    where m.workspace_id = ws and m.user_id = public.auth_uid()
  )
$$;

create or replace function public.member_role(ws uuid)
returns text
language sql
stable
security definer
set search_path = public
as $$
  select m.role from public.workspace_members m
  where m.workspace_id = ws and m.user_id = public.auth_uid()
$$;

create or replace function public.module_enabled(ws uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select coalesce((
    select f.enabled from public.workspace_module_flags f
    where f.workspace_id = ws and f.module_key = 'spreadsheet_adapter'
  ), false)
$$;

create or replace function public.can_manage_adapter(ws uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select public.module_enabled(ws) and public.member_role(ws) in ('owner', 'admin')
$$;

create or replace function public.can_upload_model(ws uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select public.module_enabled(ws) and (
    public.member_role(ws) in ('owner', 'admin')
    or (
      public.member_role(ws) in ('gestor', 'agent')
      and coalesce((
        select s.allow_non_admin_model_upload
        from public.spreadsheet_adapter_settings s
        where s.workspace_id = ws
      ), false)
    )
  )
$$;

create or replace function public.shares_workspace(other_user uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.workspace_members a
    join public.workspace_members b on a.workspace_id = b.workspace_id
    where a.user_id = public.auth_uid() and b.user_id = other_user
  )
$$;

create or replace function public.model_has_active_job(model uuid)
returns boolean
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select exists (
    select 1
    from public.spreadsheet_jobs j
    join public.spreadsheet_models m on m.id = j.model_id
    where j.model_id = model
      and j.status in ('validating', 'ready', 'queued', 'processing')
      and public.is_member(m.workspace_id)
  )
$$;

revoke all on function public.auth_uid() from public;
revoke all on function public.is_member(uuid) from public;
revoke all on function public.member_role(uuid) from public;
revoke all on function public.module_enabled(uuid) from public;
revoke all on function public.can_manage_adapter(uuid) from public;
revoke all on function public.can_upload_model(uuid) from public;
revoke all on function public.shares_workspace(uuid) from public;
revoke all on function public.model_has_active_job(uuid) from public;

grant execute on function public.auth_uid() to app_user;
grant execute on function public.is_member(uuid) to app_user;
grant execute on function public.member_role(uuid) to app_user;
grant execute on function public.module_enabled(uuid) to app_user;
grant execute on function public.can_manage_adapter(uuid) to app_user;
grant execute on function public.can_upload_model(uuid) to app_user;
grant execute on function public.shares_workspace(uuid) to app_user;
grant execute on function public.model_has_active_job(uuid) to app_user;

alter table public.profiles enable row level security;
alter table public.workspaces enable row level security;
alter table public.workspace_members enable row level security;
alter table public.workspace_module_flags enable row level security;
alter table public.audit_log enable row level security;
alter table public.spreadsheet_adapter_settings enable row level security;
alter table public.spreadsheet_models enable row level security;
alter table public.spreadsheet_jobs enable row level security;
alter table public.spreadsheet_job_sources enable row level security;
alter table public.spreadsheet_job_mappings enable row level security;

alter table public.profiles force row level security;
alter table public.workspaces force row level security;
alter table public.workspace_members force row level security;
alter table public.workspace_module_flags force row level security;
alter table public.audit_log force row level security;
alter table public.spreadsheet_adapter_settings force row level security;
alter table public.spreadsheet_models force row level security;
alter table public.spreadsheet_jobs force row level security;
alter table public.spreadsheet_job_sources force row level security;
alter table public.spreadsheet_job_mappings force row level security;

drop policy if exists profiles_select on public.profiles;
create policy profiles_select on public.profiles
  for select to app_user
  using (id = public.auth_uid() or public.shares_workspace(id));

drop policy if exists workspaces_select on public.workspaces;
create policy workspaces_select on public.workspaces
  for select to app_user
  using (public.is_member(id));

drop policy if exists members_select on public.workspace_members;
create policy members_select on public.workspace_members
  for select to app_user
  using (user_id = public.auth_uid() or public.is_member(workspace_id));

drop policy if exists flags_select on public.workspace_module_flags;
create policy flags_select on public.workspace_module_flags
  for select to app_user
  using (public.is_member(workspace_id));

drop policy if exists audit_insert on public.audit_log;
create policy audit_insert on public.audit_log
  for insert to app_user
  with check (actor_id = public.auth_uid() and (workspace_id is null or public.is_member(workspace_id)));

drop policy if exists audit_select on public.audit_log;
create policy audit_select on public.audit_log
  for select to app_user
  using (public.can_manage_adapter(workspace_id));

drop policy if exists settings_select on public.spreadsheet_adapter_settings;
create policy settings_select on public.spreadsheet_adapter_settings
  for select to app_user
  using (public.is_member(workspace_id) and public.module_enabled(workspace_id));

drop policy if exists settings_insert on public.spreadsheet_adapter_settings;
create policy settings_insert on public.spreadsheet_adapter_settings
  for insert to app_user
  with check (public.can_manage_adapter(workspace_id));

drop policy if exists settings_update on public.spreadsheet_adapter_settings;
create policy settings_update on public.spreadsheet_adapter_settings
  for update to app_user
  using (public.can_manage_adapter(workspace_id))
  with check (public.can_manage_adapter(workspace_id));

drop policy if exists models_select on public.spreadsheet_models;
create policy models_select on public.spreadsheet_models
  for select to app_user
  using (public.is_member(workspace_id) and public.module_enabled(workspace_id));

drop policy if exists models_insert on public.spreadsheet_models;
create policy models_insert on public.spreadsheet_models
  for insert to app_user
  with check (
    public.can_upload_model(workspace_id)
    and created_by = public.auth_uid()
  );

drop policy if exists models_update on public.spreadsheet_models;
create policy models_update on public.spreadsheet_models
  for update to app_user
  using (public.can_manage_adapter(workspace_id))
  with check (public.can_manage_adapter(workspace_id));

drop policy if exists jobs_all on public.spreadsheet_jobs;
drop policy if exists jobs_select on public.spreadsheet_jobs;
create policy jobs_select on public.spreadsheet_jobs
  for select to app_user
  using (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));

drop policy if exists jobs_insert on public.spreadsheet_jobs;
create policy jobs_insert on public.spreadsheet_jobs
  for insert to app_user
  with check (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));

drop policy if exists jobs_update on public.spreadsheet_jobs;
create policy jobs_update on public.spreadsheet_jobs
  for update to app_user
  using (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id))
  with check (user_id = public.auth_uid() and public.is_member(workspace_id) and public.module_enabled(workspace_id));

drop policy if exists sources_select on public.spreadsheet_job_sources;
create policy sources_select on public.spreadsheet_job_sources
  for select to app_user
  using (exists (
    select 1 from public.spreadsheet_jobs j
    where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
  ));

drop policy if exists sources_insert on public.spreadsheet_job_sources;
create policy sources_insert on public.spreadsheet_job_sources
  for insert to app_user
  with check (exists (
    select 1 from public.spreadsheet_jobs j
    where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
  ));

drop policy if exists sources_update on public.spreadsheet_job_sources;
create policy sources_update on public.spreadsheet_job_sources
  for update to app_user
  using (exists (
    select 1 from public.spreadsheet_jobs j
    where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
  ))
  with check (exists (
    select 1 from public.spreadsheet_jobs j
    where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
  ));

drop policy if exists sources_delete on public.spreadsheet_job_sources;
create policy sources_delete on public.spreadsheet_job_sources
  for delete to app_user
  using (exists (
    select 1 from public.spreadsheet_jobs j
    where j.id = job_id and j.user_id = public.auth_uid() and public.module_enabled(j.workspace_id)
  ));

drop policy if exists mappings_select on public.spreadsheet_job_mappings;
create policy mappings_select on public.spreadsheet_job_mappings
  for select to app_user
  using (exists (
    select 1 from public.spreadsheet_job_sources s
    join public.spreadsheet_jobs j on j.id = s.job_id
    where s.id = job_source_id and j.user_id = public.auth_uid()
  ));

drop policy if exists mappings_insert on public.spreadsheet_job_mappings;
create policy mappings_insert on public.spreadsheet_job_mappings
  for insert to app_user
  with check (exists (
    select 1 from public.spreadsheet_job_sources s
    join public.spreadsheet_jobs j on j.id = s.job_id
    where s.id = job_source_id and j.user_id = public.auth_uid()
  ));

drop policy if exists mappings_delete on public.spreadsheet_job_mappings;
create policy mappings_delete on public.spreadsheet_job_mappings
  for delete to app_user
  using (exists (
    select 1 from public.spreadsheet_job_sources s
    join public.spreadsheet_jobs j on j.id = s.job_id
    where s.id = job_source_id and j.user_id = public.auth_uid()
  ));

grant usage on schema public to app_user;
grant select, insert, update, delete on all tables in schema public to app_user;
grant usage, select on all sequences in schema public to app_user;

-- schema_migrations não tem RLS. A escrita fica só com o dono que aplica as migrations.
revoke insert, update, delete, truncate, references, trigger on table public.schema_migrations from public, app_user;

do $priv$
declare
  versao int := current_setting('server_version_num')::int;
  escrita text := 'insert, update, delete, truncate, references, trigger';
  papel text;
begin
  if versao >= 170000 then
    escrita := escrita || ', maintain';
    execute 'revoke maintain on table public.schema_migrations from public, app_user';
  end if;
  foreach papel in array array['anon', 'authenticated', 'service_role', 'adaptador_web', 'adaptador_worker']
  loop
    if exists (select 1 from pg_catalog.pg_roles where rolname = papel) then
      execute format(
        'revoke %s on table public.profiles, public.workspaces, public.workspace_members, public.workspace_module_flags, public.audit_log, public.spreadsheet_adapter_settings, public.spreadsheet_models, public.spreadsheet_jobs, public.spreadsheet_job_sources, public.spreadsheet_job_mappings, public.schema_migrations from %I',
        escrita,
        papel
      );
      if papel in ('anon', 'authenticated', 'service_role') then
        execute format('revoke select on table public.schema_migrations from %I', papel);
        execute format(
          'alter default privileges in schema public revoke %s on tables from %I',
          escrita,
          papel
        );
      end if;
    end if;
  end loop;
end
$priv$;