-- WA Conversion Tracker: fungsi server untuk rotator dan token CAPI
-- Jalankan setelah 02_rls.sql. Semua fungsi di sini hanya boleh dipanggil service_role.

-- Penghitung rotasi per nomor CS
alter table cs_numbers
  add column if not exists assigned_count bigint not null default 0,
  add column if not exists last_assigned_at timestamptz;

-- Pilih nomor CS berikutnya: yang paling sedikit menerima chat relatif terhadap bobotnya.
-- Dikunci per baris supaya klik bersamaan tidak memilih nomor yang sama berulang.
create or replace function pick_cs_number(p_rotator uuid)
returns table (cs_number_id uuid, wa text)
language plpgsql security definer set search_path = public as $$
declare
  v_id uuid;
begin
  select c.id into v_id
  from cs_numbers c
  where c.rotator_id = p_rotator and c.is_active and c.weight > 0
  order by (c.assigned_count::numeric / c.weight), c.last_assigned_at nulls first
  limit 1
  for update;

  if v_id is null then
    return;
  end if;

  update cs_numbers
     set assigned_count = assigned_count + 1, last_assigned_at = now()
   where cs_numbers.id = v_id;

  return query select c.id, c.wa_number from cs_numbers c where c.id = v_id;
end $$;

-- Baca token CAPI dari Supabase Vault
create or replace function get_capi_token(p_org uuid) returns text
language sql stable security definer set search_path = public, vault as $$
  select s.decrypted_secret
  from meta_connections m
  join vault.decrypted_secrets s on s.id = m.capi_token_secret_id
  where m.org_id = p_org
$$;

-- Simpan atau ganti token CAPI (dipanggil dari halaman pengaturan, lewat server)
create or replace function set_capi_token(p_org uuid, p_token text) returns void
language plpgsql security definer set search_path = public, vault as $$
declare
  v_secret uuid;
begin
  select capi_token_secret_id into v_secret from meta_connections where org_id = p_org;
  if not found then
    raise exception 'meta_connections untuk organisasi % belum dibuat', p_org;
  end if;
  if v_secret is null then
    v_secret := vault.create_secret(p_token, 'capi_token_' || p_org::text);
    update meta_connections
       set capi_token_secret_id = v_secret, updated_at = now()
     where org_id = p_org;
  else
    perform vault.update_secret(v_secret, p_token);
  end if;
end $$;

revoke all on function pick_cs_number(uuid) from public, anon, authenticated;
revoke all on function get_capi_token(uuid) from public, anon, authenticated;
revoke all on function set_capi_token(uuid, text) from public, anon, authenticated;
grant execute on function pick_cs_number(uuid) to service_role;
grant execute on function get_capi_token(uuid) to service_role;
grant execute on function set_capi_token(uuid, text) to service_role;