-- SECURITY FIX: add_credits must only be callable by service_role (payment webhook)
-- or admin users. Regular authenticated users must not be able to grant themselves credits.
 
CREATE OR REPLACE FUNCTION add_credits(
  p_provider_id UUID,
  p_amount INTEGER,
  p_description TEXT DEFAULT 'Credit purchase'
)
RETURNS JSON AS $$
DECLARE
  v_current_balance INTEGER;
  v_new_balance INTEGER;
  v_transaction_id UUID;
BEGIN
  -- SECURITY: Only service_role or admin may add credits
  IF auth.role() != 'service_role' THEN
    IF NOT EXISTS (
      SELECT 1 FROM profiles
      WHERE id = auth.uid() AND role = 'admin'
    ) THEN
      RAISE EXCEPTION 'Unauthorized: only service_role or admin can add credits';
    END IF;
  END IF;
 
  -- Validate amount
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be positive';
  END IF;
 
  -- Get or create wallet
  INSERT INTO provider_wallets (provider_id, credit_balance)
  VALUES (p_provider_id, 0)
  ON CONFLICT (provider_id) DO NOTHING;
 
  -- Get current balance with row lock
  SELECT credit_balance INTO v_current_balance
  FROM provider_wallets
  WHERE provider_id = p_provider_id
  FOR UPDATE;
 
  -- Add credits
  UPDATE provider_wallets
    SET credit_balance = credit_balance + p_amount,
        updated_at = NOW()
  WHERE provider_id = p_provider_id
  RETURNING credit_balance INTO v_new_balance;
 
  -- Record transaction
  INSERT INTO credit_transactions (
    provider_id, amount, transaction_type, description, balance_after
  ) VALUES (
    p_provider_id, p_amount, 'credit_purchase', p_description, v_new_balance
  ) RETURNING id INTO v_transaction_id;
 
  RETURN json_build_object(
    'success', true,
    'transaction_id', v_transaction_id,
    'new_balance', v_new_balance
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
 
-- Only service_role should call this directly; revoke from regular authenticated users
REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) FROM authenticated;
GRANT  EXECUTE ON FUNCTION add_credits(UUID, INTEGER, TEXT) TO service_role;
