-- WA Conversion Tracker: login, role, dan isolasi data (Row Level Security)
-- Jalankan setelah 01_schema.sql.
--
-- Catatan: tabel clicks, lead_scores, meta_events, dan audit_logs HANYA ditulis oleh
-- server (service role): endpoint rotator, worker AI, dan worker CAPI. Karena itu tidak
-- ada policy insert/update untuk role authenticated di tabel tersebut.

-- HELPER --------------------------------------------------------------------
create or replace function is_super_admin() returns boolean
language sql stable security definer set search_path = public as $$
  select coalesce((select is_super_admin from profiles where id = auth.uid()), false)
$$;

create or replace function is_org_member(p_org uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select is_super_admin() or exists (
    select 1 from memberships
    where org_id = p_org and user_id = auth.uid() and is_active
  )
$$;

create or replace function has_org_role(p_org uuid, p_roles app_role[]) returns boolean
language sql stable security definer set search_path = public as $$
  select is_super_admin() or exists (
    select 1 from memberships
    where org_id = p_org and user_id = auth.uid() and is_active and role = any (p_roles)
  )
$$;

create or replace function shares_org_with(p_user uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select exists (
    select 1
    from memberships a
    join memberships b on a.org_id = b.org_id
    where a.user_id = auth.uid() and a.is_active and b.user_id = p_user
  )
$$;

-- Baca lead: owner, marketing, supervisor, viewer (semua); CS hanya lead miliknya
create or replace function can_access_lead(p_org uuid, p_assigned uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select has_org_role(p_org, array['owner', 'marketing', 'cs_supervisor', 'viewer']::app_role[])
      or (p_assigned = auth.uid() and has_org_role(p_org, array['cs']::app_role[]))
$$;

-- Ubah lead / lihat chat mentah: owner, supervisor (semua); CS hanya lead miliknya
create or replace function can_edit_lead(p_org uuid, p_assigned uuid) returns boolean
language sql stable security definer set search_path = public as $$
  select has_org_role(p_org, array['owner', 'cs_supervisor']::app_role[])
      or (p_assigned = auth.uid() and has_org_role(p_org, array['cs']::app_role[]))
$$;

-- Profil otomatis dibuat saat user baru mendaftar (login)
create or replace function handle_new_user() returns trigger
language plpgsql security definer set search_path = public as $$
begin
  insert into profiles (id, full_name)
  values (new.id, new.raw_user_meta_data ->> 'full_name')
  on conflict (id) do nothing;
  return new;
end $$;

create trigger on_auth_user_created after insert on auth.users
  for each row execute function handle_new_user();

-- AKTIFKAN RLS --------------------------------------------------------------
alter table organizations enable row level security;
alter table profiles enable row level security;
alter table memberships enable row level security;
alter table meta_connections enable row level security;
alter table products enable row level security;
alter table rotators enable row level security;
alter table cs_numbers enable row level security;
alter table clicks enable row level security;
alter table leads enable row level security;
alter table lead_stage_history enable row level security;
alter table chat_imports enable row level security;
alter table lead_scores enable row level security;
alter table orders enable row level security;
alter table meta_events enable row level security;
alter table audit_logs enable row level security;

-- ORGANISASI, PROFIL, MEMBERSHIP -----------------------------------------------
create policy org_select on organizations for select using (is_org_member(id));
create policy org_insert on organizations for insert with check (is_super_admin());
create policy org_update on organizations for update
  using (has_org_role(id, array['owner']::app_role[]));
create policy org_delete on organizations for delete using (is_super_admin());

create policy profiles_select on profiles for select
  using (id = auth.uid() or is_super_admin() or shares_org_with(id));
create policy profiles_update_self on profiles for update
  using (id = auth.uid()) with check (id = auth.uid());
-- user hanya boleh mengubah nama; is_super_admin tidak bisa diubah dari client
revoke update on profiles from authenticated;
grant update (full_name) on profiles to authenticated;

create policy memberships_select on memberships for select
  using (user_id = auth.uid()
         or has_org_role(org_id, array['owner', 'marketing', 'cs_supervisor']::app_role[]));
create policy memberships_write on memberships for all
  using (has_org_role(org_id, array['owner']::app_role[]))
  with check (has_org_role(org_id, array['owner']::app_role[]));

-- KONFIGURASI: owner & marketing ------------------------------------------------
create policy meta_conn_all on meta_connections for all
  using (has_org_role(org_id, array['owner', 'marketing']::app_role[]))
  with check (has_org_role(org_id, array['owner', 'marketing']::app_role[]));

create policy products_select on products for select using (is_org_member(org_id));
create policy products_write on products for all
  using (has_org_role(org_id, array['owner', 'marketing']::app_role[]))
  with check (has_org_role(org_id, array['owner', 'marketing']::app_role[]));

create policy rotators_select on rotators for select using (is_org_member(org_id));
create policy rotators_write on rotators for all
  using (has_org_role(org_id, array['owner', 'marketing']::app_role[]))
  with check (has_org_role(org_id, array['owner', 'marketing']::app_role[]));

create policy cs_numbers_select on cs_numbers for select using (is_org_member(org_id));
create policy cs_numbers_write on cs_numbers for all
  using (has_org_role(org_id, array['owner', 'marketing']::app_role[]))
  with check (has_org_role(org_id, array['owner', 'marketing']::app_role[]));

-- ATRIBUSI: CS tidak melihat data klik (IP, fbc, dll); pencocokan Ref lewat server --
create policy clicks_select on clicks for select
  using (has_org_role(org_id, array['owner', 'marketing', 'cs_supervisor', 'viewer']::app_role[]));

-- LEAD & TURUNANNYA -------------------------------------------------------------
create policy leads_select on leads for select using (can_access_lead(org_id, assigned_to));
create policy leads_insert on leads for insert with check (can_edit_lead(org_id, assigned_to));
create policy leads_update on leads for update
  using (can_edit_lead(org_id, assigned_to))
  with check (can_edit_lead(org_id, assigned_to));
create policy leads_delete on leads for delete
  using (has_org_role(org_id, array['owner']::app_role[]));

-- turunan lead mengikuti akses lead (subquery ke leads ikut kena RLS leads)
create policy stage_history_select on lead_stage_history for select
  using (exists (select 1 from leads l where l.id = lead_id));
create policy stage_history_insert on lead_stage_history for insert
  with check (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));

create policy lead_scores_select on lead_scores for select
  using (exists (select 1 from leads l where l.id = lead_id));

-- chat mentah: tidak bisa dibaca marketing/viewer
create policy chat_imports_select on chat_imports for select
  using (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));
create policy chat_imports_insert on chat_imports for insert
  with check (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));
create policy chat_imports_delete on chat_imports for delete
  using (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));

create policy orders_select on orders for select
  using (exists (select 1 from leads l where l.id = lead_id));
create policy orders_insert on orders for insert
  with check (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));
create policy orders_update on orders for update
  using (exists (select 1 from leads l where l.id = lead_id and can_edit_lead(l.org_id, l.assigned_to)));

-- LOG EVENT META & AUDIT ----------------------------------------------------------
create policy meta_events_select on meta_events for select
  using (has_org_role(org_id, array['owner', 'marketing', 'cs_supervisor', 'viewer']::app_role[]));
create policy audit_logs_select on audit_logs for select
  using (has_org_role(org_id, array['owner']::app_role[]));