-- 1. Revoke staff access to bcrypt password hash column
REVOKE SELECT (portal_password_hash) ON public.customers FROM authenticated, anon;

-- 2. Atomic wallet transfer
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'referral_wallets_customer_id_key'
  ) THEN
    ALTER TABLE public.referral_wallets
      ADD CONSTRAINT referral_wallets_customer_id_key UNIQUE (customer_id);
  END IF;
END $$;

CREATE OR REPLACE FUNCTION public.portal_transfer_wallet_balance(
  p_sender_id uuid,
  p_recipient_id uuid,
  p_amount numeric
) RETURNS TABLE(sender_new_balance numeric, recipient_new_balance numeric)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_sender_new numeric;
  v_recipient_new numeric;
BEGIN
  IF p_amount IS NULL OR p_amount <= 0 THEN
    RAISE EXCEPTION 'Invalid amount';
  END IF;

  UPDATE public.referral_wallets
     SET balance = balance - p_amount, updated_at = now()
   WHERE customer_id = p_sender_id AND balance >= p_amount
  RETURNING balance INTO v_sender_new;

  IF v_sender_new IS NULL THEN
    RAISE EXCEPTION 'Insufficient balance';
  END IF;

  INSERT INTO public.referral_wallets (customer_id, balance)
  VALUES (p_recipient_id, 0)
  ON CONFLICT (customer_id) DO NOTHING;

  UPDATE public.referral_wallets
     SET balance = balance + p_amount, updated_at = now()
   WHERE customer_id = p_recipient_id
  RETURNING balance INTO v_recipient_new;

  sender_new_balance := v_sender_new;
  recipient_new_balance := v_recipient_new;
  RETURN NEXT;
END;
$$;

REVOKE ALL ON FUNCTION public.portal_transfer_wallet_balance(uuid, uuid, numeric) FROM PUBLIC;

-- 3. Realtime channel authorization (only if table exists in this project)
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname='realtime' AND tablename='messages') THEN
    EXECUTE 'ALTER TABLE realtime.messages ENABLE ROW LEVEL SECURITY';
    EXECUTE 'DROP POLICY IF EXISTS "Staff only realtime read" ON realtime.messages';
    EXECUTE 'CREATE POLICY "Staff only realtime read" ON realtime.messages FOR SELECT TO authenticated USING (public.is_staff(auth.uid()))';
  END IF;
END $$;

-- 4. Fix pending_referrals policy role
DROP POLICY IF EXISTS "Staff full access pending_referrals" ON public.pending_referrals;
CREATE POLICY "Staff full access pending_referrals"
  ON public.pending_referrals FOR ALL
  TO authenticated
  USING (public.is_staff(auth.uid()))
  WITH CHECK (public.is_staff(auth.uid()));

-- 5. Restrict sms_logs SELECT to admins
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname='public' AND tablename='sms_logs') THEN
    EXECUTE 'DROP POLICY IF EXISTS "Staff can view sms logs" ON public.sms_logs';
    EXECUTE 'DROP POLICY IF EXISTS "Staff can view SMS logs" ON public.sms_logs';
    EXECUTE 'DROP POLICY IF EXISTS "Admins can view sms logs" ON public.sms_logs';
    EXECUTE 'CREATE POLICY "Admins can view sms logs" ON public.sms_logs FOR SELECT TO authenticated USING (public.has_role(auth.uid(), ''admin''::app_role))';
  END IF;
END $$;

-- 6. Restrict staff_salary_payments SELECT to admins
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname='public' AND tablename='staff_salary_payments') THEN
    EXECUTE 'DROP POLICY IF EXISTS "Staff can view staff salary payments" ON public.staff_salary_payments';
    EXECUTE 'DROP POLICY IF EXISTS "Staff can view salary payments" ON public.staff_salary_payments';
    EXECUTE 'DROP POLICY IF EXISTS "Admins can view staff salary payments" ON public.staff_salary_payments';
    EXECUTE 'CREATE POLICY "Admins can view staff salary payments" ON public.staff_salary_payments FOR SELECT TO authenticated USING (public.has_role(auth.uid(), ''admin''::app_role))';
  END IF;
END $$;