-- 1. Restrict financial investor tables to admins only
DROP POLICY IF EXISTS "staff_all_company_profits" ON public.company_profits;
CREATE POLICY "admin_all_company_profits" ON public.company_profits
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "staff_all_investments" ON public.investments;
CREATE POLICY "admin_all_investments" ON public.investments
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "staff_all_ipd" ON public.investor_profit_distributions;
CREATE POLICY "admin_all_ipd" ON public.investor_profit_distributions
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "staff_all_iw" ON public.investor_wallets;
CREATE POLICY "admin_all_iw" ON public.investor_wallets
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "staff_all_iwd" ON public.investor_withdrawals;
CREATE POLICY "admin_all_iwd" ON public.investor_withdrawals
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- Also restrict investors table to admins for parity with related tables
DROP POLICY IF EXISTS "staff_all_investors" ON public.investors;
CREATE POLICY "admin_all_investors" ON public.investors
  FOR ALL TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role))
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- 2. Revoke column-level access to portal_password_hash for investors
REVOKE SELECT (portal_password_hash) ON public.investors FROM authenticated, anon;
REVOKE UPDATE (portal_password_hash) ON public.investors FROM authenticated, anon;
REVOKE INSERT (portal_password_hash) ON public.investors FROM authenticated, anon;

-- 3. Storage: split investor-files SELECT policy.
-- Documents are staff-only; photos remain publicly readable so investor portal can show them.
DROP POLICY IF EXISTS "Public can view investor files" ON storage.objects;
DROP POLICY IF EXISTS "Public can view investor photos" ON storage.objects;
DROP POLICY IF EXISTS "Staff can view investor documents" ON storage.objects;

CREATE POLICY "Public can view investor photos"
  ON storage.objects FOR SELECT TO public
  USING (
    bucket_id = 'investor-files'
    AND (storage.foldername(name))[1] = 'photos'
  );

CREATE POLICY "Staff can view investor documents"
  ON storage.objects FOR SELECT TO authenticated
  USING (
    bucket_id = 'investor-files'
    AND is_staff(auth.uid())
  );