CREATE TABLE IF NOT EXISTS public.investor_withdrawals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  investor_id uuid NOT NULL REFERENCES public.investors(id) ON DELETE CASCADE,
  amount numeric NOT NULL CHECK (amount > 0),
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','paid')),
  requested_at timestamptz NOT NULL DEFAULT now(),
  approved_by uuid,
  approved_at timestamptz,
  paid_at timestamptz,
  note text,
  rejection_reason text,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_iw_investor ON public.investor_withdrawals(investor_id);
CREATE INDEX IF NOT EXISTS idx_iw_status ON public.investor_withdrawals(status);

ALTER TABLE public.investor_withdrawals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS staff_all_iwd ON public.investor_withdrawals;
CREATE POLICY staff_all_iwd ON public.investor_withdrawals
  FOR ALL TO authenticated
  USING (is_staff(auth.uid()))
  WITH CHECK (is_staff(auth.uid()));

CREATE TRIGGER trg_iwd_updated_at
  BEFORE UPDATE ON public.investor_withdrawals
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE OR REPLACE FUNCTION public.approve_investor_withdrawal(p_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_w public.investor_withdrawals%ROWTYPE;
  v_balance numeric;
BEGIN
  IF NOT is_staff(auth.uid()) THEN RAISE EXCEPTION 'Not authorized'; END IF;
  SELECT * INTO v_w FROM public.investor_withdrawals WHERE id = p_id FOR UPDATE;
  IF v_w.id IS NULL THEN RAISE EXCEPTION 'Request not found'; END IF;
  IF v_w.status <> 'pending' THEN RAISE EXCEPTION 'Only pending requests can be approved'; END IF;

  SELECT COALESCE(balance,0) INTO v_balance FROM public.investor_wallets WHERE investor_id = v_w.investor_id;
  IF v_balance < v_w.amount THEN RAISE EXCEPTION 'Insufficient wallet balance'; END IF;

  UPDATE public.investor_wallets
    SET balance = balance - v_w.amount,
        total_withdrawn = total_withdrawn + v_w.amount,
        updated_at = now()
    WHERE investor_id = v_w.investor_id;

  UPDATE public.investor_withdrawals
    SET status = 'approved', approved_by = auth.uid(), approved_at = now()
    WHERE id = p_id;

  RETURN jsonb_build_object('ok', true);
END;
$$;

CREATE OR REPLACE FUNCTION public.reject_investor_withdrawal(p_id uuid, p_reason text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_w public.investor_withdrawals%ROWTYPE;
BEGIN
  IF NOT is_staff(auth.uid()) THEN RAISE EXCEPTION 'Not authorized'; END IF;
  SELECT * INTO v_w FROM public.investor_withdrawals WHERE id = p_id FOR UPDATE;
  IF v_w.id IS NULL THEN RAISE EXCEPTION 'Request not found'; END IF;
  IF v_w.status <> 'pending' THEN RAISE EXCEPTION 'Only pending requests can be rejected'; END IF;

  UPDATE public.investor_withdrawals
    SET status = 'rejected', approved_by = auth.uid(), approved_at = now(), rejection_reason = p_reason
    WHERE id = p_id;
  RETURN jsonb_build_object('ok', true);
END;
$$;

CREATE OR REPLACE FUNCTION public.mark_paid_investor_withdrawal(p_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_w public.investor_withdrawals%ROWTYPE;
BEGIN
  IF NOT is_staff(auth.uid()) THEN RAISE EXCEPTION 'Not authorized'; END IF;
  SELECT * INTO v_w FROM public.investor_withdrawals WHERE id = p_id FOR UPDATE;
  IF v_w.id IS NULL THEN RAISE EXCEPTION 'Request not found'; END IF;
  IF v_w.status <> 'approved' THEN RAISE EXCEPTION 'Only approved requests can be marked paid'; END IF;

  UPDATE public.investor_withdrawals
    SET status = 'paid', paid_at = now()
    WHERE id = p_id;
  RETURN jsonb_build_object('ok', true);
END;
$$;